数据库复习题

3．设有如下图所示的关系R和S，计算：ABCabABCabcbabcfdABCbdaafd⑵R2＝R∪S⑶R3＝R∩S⑷R4＝R×S解：本题各小题的成果如下图所示。ABCacbbcdABCabABCacbbcdABCabcdbabacfddABCbafABCA’B’C’aabbccbbaabbccffddbdbdbdaaaaaafdfdfd4．设有如下图所示的关系R，S，计算：ABCa1ABCa1a1a2b1b2b2c1c2c1ABCa1a2a1b2b2b3c2c1c2⑵R2＝R∪S⑶R3＝R∩S⑷R4＝解：本题各小题的成果如下图所示。ABABCa1b1c1ABCa1a1a2a1b1b2b2b3c1c2c1c2ABCa1a2b2b2c2c1ABa1b15．设有如下图所示的关系R，S和Ｔ，计算：ABabcdeABabcdecABdbdaacBCbcbbcd⑵R2＝R－S⑶R3＝RＴ⑷R4＝Ｔ⑸R5＝（R）⑹R6＝（R×Ｔ）解：本题各小题的成果如图2.13所示。ABaABabcdbddecaacAabcABCcccABB’CaaabbcdddeecbcbcbbbcdcddABabcdecABB’CbcecbcbcABABC347465247733CDE374253⑴R1＝RSRS⑵R2＝⑶R3＝解：本题各小题的成果如下图所示。R1R2R3AABCDE34746524773322443355ABCC’DE723345ABCC’DE74243373243511．设有关系模式：SB（SN，SNAME，CITY）其中：SB表达供应商，SN为供应商代号，SNAME为供应商名字，CITY为供应商所在都市，主关键字为SN。PB（PN，PNAME，COLOR，WEIGHT）其中：P表达零件，PN为零件代号，PNAME为零件名字，COLOR为零件颜色，WEIGHT为零件重量，主关键字为PN。JB（JN，JNAME，CITY）其中：JB表达工程，JN为工程编号，JNAME为工程名字，CITY为工程式所在都市，主关键字为JN。SPJB（SN，PN，JN，QTY）其中：SPJ表达供应关系，SN是为指定工程提供零件的供应商代号，PN为所提供的零件代号，JN为工程编号，QTY表达提供的零件数量，主关键字为SN，PN，JN，外关键字为SN，PN，JN。如图所示表达供应商（S）—零件（P）—工程（J）数据库表，写出实现如下各题功能的SQL语句：（1）取出所有工程的所有细节：SELECT*FROMJB（2）取出所在都市为上海的所有工程的所有细节；SELECT*FROMJBWHERECETY=“上海”（3）取出重量最轻的零件代号；SELECTPNFROMPBWHERHWEIGHT=（SELECTMIN（WEIGHT）FROMPB）（4）取出为工程式Ｊ1提供零件的供应商代号；SELECTSNFROMSPJBWHEREJN=“J1”（5）取出为工程J１提供P１的供应商代号；SELECTSNFROMSPJBWHEREJN=“J1”ANDPN=“P1（6）取出由供应商S１提供零件的工程名称；SELECTJB．JNAMEFROMJB，SPJBWHEREJB．JN=SPJB．JNANDSPJB．SN=“S1”（7）取出供应商S1提供的零件的颜色；SELECTDISTINCTPB．COLORFROMPB，SPJBWHEREPB．PN=SPJB．PNANDSPJB．SN=“1”（8）取出为工程J１或J２提供零件的供应商代号；SELECTDISTINCTSNFROMSPJBWHEREJN=“J1”ORJN=“J2（9）取出为工程Ｊ１提供红色零件的供应商代号；SELECTDISTINCTSPJB．SNFROMSPJB，PBWHEREPB．PN=SPJB．PNANDSPJB．JN=“J1”ANDPB．COLOR=“红”（10）取出为所在都市为上海的工程提供零件的供应商代号；SELECTDISTINCTSPJB．SNFROMSPJB，JBWHERESPJB．JN=JB．JNANDJB．CITY=“上海”（11）取出为所在都市为上海或北京的工程提供红色零件的供应商代号；SELECTSPJB．SNFROMPB，JB，SPJBWHERESPJB．PN=PB．PNANDJB．JN=SPJB．JNANDPB．COLOR=‘红’AND（JB．CITY=“上海”ORJB．CITY=“北京”（12）取出供应商与工程所在都市相似的供应商提供的零件代号；SELECTDISTINCTSPJB．PNFROMSB，JB，SPJBWHERESB．SN=SPJB．SNANDJB．JN=SPJB．JNANDSB．CITY=JB．CITYTOSCREEN；（13）取出上海的供应商提供应上海的任一工程的零件的代号；SELECTSPJB．PNFROMSB，JB，SPJBWHERESB．SN=SPJB．SNANDJB．JN=SPJB．JNANDSB．CITY=“上海”ANDJB．CITY=“上海”（14）取出至少由一种和工程式不在同一都市的供应商提供零件的工程代号；SELECTDISTINCTSPJB．JNFROMSB，JB，SPJBWHERESB．SN=SPJB．SNANDJB．JN=SPJB．JNANDSB．CITY<>JB．CITY（15）取出上海供应商不提供任何零件的工程的代号SELECTDISTINCTJNFROMSPJBWHEREJNNOTIN（SELECTDISTINCTSPJB．JNFROMSB，SPJBWHERESB．SN=SPJB．SNANDSB．CITY=“上海”）（16）取出这样某些供应商代号，它们可以提供至少一种由红色零件的供应商提供的零件；SELECTDISTINCTSPJB．SNFROMPB，SPJBWHERESPJB．PNIN（SELECTSPJB．PNFROMSPJB，SB，PBWHERESB．SN=SPJB．SNANDPB．PN=SPJB．PNANDPB．COLOR=“红”（17）取出由供应商S1提供零件的工程的代号：SELECTDISTINCTSPJB．JNFROMSB，PB，SPJBWHIERESB．SN=SPJB．SNANDPB．PN=SPJB．PNANDSB．SN=“S1”（18）取出所有这样的某些<CITY，CITY>二元组，使得第1个都市的供应商为第2个都市的工程提供零件；SELECTDISTINCTSB．CITY，JB．CITYFROMSB，JB，SPJBWHERESB．SN=SPJB．SNANDJB．JN=SPJB．JN（19）取出所有这样的三元组<CITY，PN，CITY>，使得第1个都市的供应商为第2个都市的工程提供指定的零件；SELECTDISTINCTSB．CITY，SPJB．PN，JB．CITYFROMSB，JB，SPJBWHERESB．SN=SPJB．SNANDJB．JN=SPJB．JN（20）反复（19）题，但不检索两个CITY值相似的三元组。SELECTDISTINCTSB．CITY，SPJB．PN，JB．CITYFROMSB，JB，SPJBWHERESB．SN=SPJB．SNANDJB．JN=SPJB．JNANDSB．CITY<>JB．CITY12．有样本表student、teacher、course和score（如图），写出实现如下各题功能的SQL语句，并给出执行成果。Studentscore-------------------------------------------------------------------------------------NONAMESEXBIRTHDAYCLASSNOCNODEGREE108曾华男09/01/77950331033-24586105匡明男10/02/75950311053-24575107王丽女01/23/76950331093-24568101李军男02/20/76950331033-10592109王芳女02/10/75950311053-10588103陆军男06/03/74950311093-105761013-105641013-105911013-105781016-166851016-166791016-16681teachercourse--------------------------------------------------------------------------------------------------NONAMESEXBRITHDAYPROFDEPARTCNOCNAMETNO804李诚男12/02/58副专家计算机3-103计算机导论825856张旭男03/12/69讲师电子3-245操作系统825825王萍女05/05/72助教计算机6-166数字电路825831刘冰女08/14/77助教电子9-888高等数学825列出至少有2名男生的班号。SELECTclassFROMstudentWHEREsex=“男”GROUPBYclassHAVINGCOUNT（*）>=2屏幕显示student表中不姓“王”的同学记录。SELECT*FROMstudenWHEREnamenotlike“王%”屏幕显示student表达每个学生的姓名和年龄。SELECTnameas“姓名”，year（date（））-year（birthday）as“年龄”FROMstudent屏幕显示student表中最大和最小的birthday日期值。SELECTname，max（birthday），min（birthday）FROMstudent屏幕显示“男”教师及其所上的课程。SELECTx．name，y．cnameFROMteacherx，courseyWHEREx．no=y．tnoANDx．sex=“男”在屏幕上列出最高分同学的no、cno和degree列。SELECTno，cno，degreeFROMscoreWHEREdegree=（SELECTmax（degree）FROMscore）在屏幕上列出和“李军”同性别并同班的同学name。SELECTnameFROMstudentWHEREsex=（SELECTsexFROMstudentWHEREname=“李军”）ANDclass=（SELECTclassFROMstudentWHEREname=“李军”）在屏幕上列出所有选修“计算机导论”课程的“男”同学的成绩表。SELECTno，cno，degreeFROMscoreWHEREnoIN（SELECTnoFROMstudentWHEREsex=‘男’）ANDcno=（SELECTcnoFROMcourseWHEREcname=“计算机导论”）-----------------------------------------------**************************************************************************设有一教学管理数据库，其属性为：学号（S#），课程号（C#），成绩（G），任课教师（TN），教师所在的系（D）。这些数据有下列语义：·学号和课程号分别与其代表的学生和课程——对应；·一种学生所修的每门课程至多只能修一次，且均有一种成绩；·每门课程只有一位任课教师，但每位教师可以有多门课程；·教师中没有重名，每个教师只属于一种系。（1）试根据上述语义确定函数依赖集。（2）假如用上面所有属性构成一种关系模式，那么该关系模式为何模式？并举例阐明在进行增、删操作时的异常现象。（3）分解为具有依赖保持和无损连接的3NF。解：（1）F={（S#，C#）→G，C#→TN，TN→D}（2）关系模式为1NF，由于该关系模式的候选关键字为（S#，C#），则非主属性有G、TN和D。又∵F中有C#→TN，∴存在非主属性TN对候选关键字（S#，C#）的部分依赖，即：（S#，C#）p→TN。若新增设一门课程而临时还没有学生选修时，则因缺乏关键字S#值而不能进行插入操作。若某个教师调离学校要删除其有关信息时，会将不该删除的课程（C#）信息删除。（3）∵F=Fm={（S#，C#）→G，C#→TN，TN→D}∴ρ={R1，R2，R3}其中：R1=（S#，C#，G）R2=（C#，TN）R3=（TN，D）下图给出的关系R为第几范式？与否存在操作异常？若存在，则将其分解为高一级范式。分解完毕的高级范式中与否可以防止分解前关系中存在的操作异常？工程号材料号数量动工日期竣工日期价格P111498059902250P112698059902300P1131598059902180P211698119912250P2141898119912350解：它为1NF。由于该关系的候选关键字为（工程号，材料号），而非主属性动工日期和竣工日期部分函数依赖于候选关键字的子集工程号，即：（工程号，材料号）p→动工日期（工程号，材料号）p→竣工日期∴它不是2NF。它存在操作异常，假如工程项目确定后，若临时未用到材料，则该工程的数据因缺乏关键字的一部分（材料号）而不能进入到数据库中，出现插入异常。若某工程下马，则删去该工程的操作也也许丢失材料方面的信息。将其中的部分函数依赖分解为一种独立的关系，则产生如下图所示的两个2NF关系子模式：R1R2工程号材料号数量价格P1114250P1126300P11315180P2116250P21418350工程号动工日期竣工日期P198059902P298119912分解后，新工程确定后，尽管尚未用到材料，该工程数据可在关系R2中插入。某工程数据删除时，仅对关系R2操作，也不会丢失材料方面的信息。如下图给出一数据集，请判断它与否可直接作为关系数据库中的关系，若不行，则改导致为尽量好的并能作为关系数据库中关系的形式，同步阐明进行这种改造的理由。系名课程名教师名计算机系DB李军，刘强机械系CAD金山，宋海造船系CAM王华自控系CTY张红，曾键解：由于关系模式至少是1NF关系，即不包括反复组并且不存在嵌套构造，给出的数据集显然不可直接作为关系数据库中的关系，改造为1NF的关系如下图所示。系名课程名教师名计算机系DB李军计算机系DB刘强机械系CAD金山机械系CAD宋海造船系CAM王华自控系CTY张红自控系CTY曾键D-E单位D-E单位单位号职工地址 单位号姓名电话职工号性别年龄职务1n本题的E－R图转换为如下关系模式：单位（单位号，地址，电话）职工（职工号，姓名，性别，年龄，职务，单位号）假定一种部门的数据库包括如下的信息：职工的信息：职工号、姓名、住址和所在部门。部门的信息：部门所有职工、经理和销售的产品。产品的信息：产品名、制造商、价格、型号及产品内部编号。制造商的信息：制造商名称、地址、生产的产品名和价格。完毕如下设计：（1）设计该计算机管理系统的E－R图；（2）写出关系模式集，并用下划线和波浪线标识出每个模式的主码和外码。（3）在上述数据库中，写出主码和外码的总数这两个数字。．解：本题对应的E-R图如图所示。产品名经理部门名地址姓名职工号产品名经理部门名地址姓名职工号产品内部编号nm1附属ong产品部门职工n销售ong产品内部编号nm1附属ong产品部门职工n销售ong型号n型号n生产生产价格m价格m制造商制造商地址名称地址名称（2）根据ER图和转换规则，4个实体类型转换成4个关系模式，2个M:N联络转换成1个关系模式因此，该ER图可转换成6个关系模式，如下：职工（职工号，姓名，地址，部门号）部门（部门号，部门名称，经理）产品（产品内部编号，产品名）制造商（制造商编号，名称，地址）销售（部门号，产品号）生产（产品号，制造商编号，型号，价格）（3）在上述数据库中，主键的总数为6个，外键的总数为5个。假定一种部门的数据库包括如下的信息：·职工的信息：职工号、姓名、住址的所有部门。·部门的信息：部门所有职工、经理和销售的产品。·产品的信息：产品名、制造商、价格、型号及产品内部编号。·制造商的信息：制造商名称、地址、生产的产品名和价格。试画出这个数据库的E－R图。本题对应的E－R图如下图所示。4．设有商业销售记账数据库。一种顾客（顾客姓名，单位，电话号码）可以买多种商品，一种商品（商品名称，型号，单价）供应多种顾客。试画出对应的E－R图。本题对应的E－R图如下图所示。某医院病房计算机管理中需要如下信息：科室：科名，科地址，科电话，医生姓名病房：病房号，床位号，所属科室名医生：姓名，职称，所属科室名，年龄，工作证号病人：病历号，姓名，性别，诊断，产管医生，病房号其中，一种科室有多种病房、多种医生，一种病房只能属于一种科室，一种医生只属于一种科室，但可负责多种病人的诊治，一种病人的主管医生只有一种。完毕如下设计：⑴设计该计算机管理系统的E－R图；⑵将该E－R图转换为关系模型构造；⑶指出转换成果中每个关系模式的候选码。解：⑴本题的E－R图如下图所示。⑵对应的关系模型构造如下：科室（科名，科地址，科电话）病房（病房号，床位号，科室名）医生（工作证号，姓名，职称，科室名，年龄）病人（病历号，姓名，性别，诊治，主管医生，病房号）⑶每个关系模式的候选码如下：科室的候选码是科名；病房的候选码是科室名＋病房号；医生的候选码是工作证号；病人的候选码是病历号。设有如下实体：学生：学号、单位、姓名、性别、年龄、选修课程号课程：编号、课程号、开课单位、任课教师号教师：教师号、姓名、性别、职称、讲讲课程编号单位：单位名称、电话、教师号、教师名上述实体中存在如下联络：⑴一种学生可选修多门课程，一门课程可为多种学生选修；⑵一种教师可讲授多门课程，一门课程可为多种教师讲授；⑶一种单位可有多种教师，一种教师只能属于一种单位。试完毕如下工作：⑴分别设计学生选课和教师任课两个局部信息的构造E－R图。⑵将上述设计完毕的E－R图合并成一种全局E—R图。⑶将该全局E－R转换为等价的关系模型表达的数据库逻辑构造。解：⑴学生选课局部E－R图如图所示，教师任课局部E－R图如图所示。⑵合并的全局E－R图如图所示。为防止图形复杂，下面给出各实体