数据库综合练习

数据库综合练习三习一现有一个局部应用，包括两个实体：“出版社”和“作者”，这两个实体是多对多的联系，请适当的属性，画出E-R图，再将其转换为关系模型（包括关系名、属性名、码和完整性约束条件）。请设计一个图书馆数据库，此数据库中对每个借阅者保存的包括：读者号，姓名，地址，性别，年龄，单位。对每本书保存有：书号，书名，作者，出版社。对每本被借出的书保存有读者号、借出日期和应还日期。要求：给出该图书馆数据库的E-R图，再将其转换为关系模型。图4-1是某个教务管理数据库的E-R图，请把它们转换为关系模型（图中关系、属性和联系的含义，已在它旁边用汉字标出）。图4-1教学管理数据库图4-2是一个销售业务管理的E-R图，请把它转换成关系模型。设有一家百货商店，已知信息有：）每个职工的数据是职工号、姓名、地址和他所在的商品部。）每一商品部的数据有：它的职工，经理和它经销的商品。）每种经销的商品数有：商品名、生产厂家、价格、型号（厂家定的）和内部商品代号（商店规定的）4）关于每个生产厂家的数据有：厂名、地址、向商店提供的商品价格。请设计该百货商店的概念模型，再将概念模型转换为关系模型。注意某些信息可用属性示，其他信息可用联系表示。下列有关E-R模型向关系模型转换的叙述中。不正确的是。一个实体类型转换为一个关系模式—个1:1联系可以转换为一个独立的关系模式，也可以与联系的任意一端实体所对应的关系模式合并一个1:n联系可以转换为一个独立的关系模式，也可以与联系的任意一端实体所对应的关系模式合并一个mn联系转换为一个关系模式习题一解答答：转换后的关系模型如图6所示。出版社（出版社名，地址，邮政编码）;作者（姓名，性别，年龄，证件号码，单位）出版（出版社名，作者姓名，出书数量，联系方式）图6答:该图书馆数据库的E—R图如图7图7其中：读者：读者号，姓名，地址，性别，年龄，单位；图书•书号，书名，作者，出版社。转换后的关系模型为：借阅者（读者号，姓名，地址，性别，年龄，单位）书籍（书号，书名，作者，出版社）;借阅（读者号，书号，借出日期，应还日期）。3答：转换后的关系模型如下：Student（SNOSNSDSA）;Teacher(TNO,TN,TD,TG；Course(CNQCNPCNO;ST（SNQTNQ;SC（SNQCNQG）;TC（TNQCNQ）4•答：转换后的关系模型如下工厂（厂名，厂长，地址）；产品（编号，型号，单价）；用户（姓名，地址，电话）；工厂-产品-用户（厂名，编号，姓名）。5.答：概念模型如图8所示。关系模型为：职工（职工号，姓名，住址，工作商品部）；商品部（商品部号，名称，经理职工号，经理名）商品（商品代号，价格，型号，出厂价格）生产厂家（厂名，地址）;销售（商品代号，商品部号）;生产（厂名，商品代号）。图8百货商店E-R图模型图中：职工：职工号，姓名，住址；商品部：商品部号，名称；商品：商品代号，价格，型号，出厂价格;生产厂家：厂名，地址。6.答：D习题二设职工社团数据库有三个基本表:职工（职工号，姓名，年龄，性别）；社会团体（编号，名称，负责人，活动地点）参加（职工号，编号，参加日期）。其中:）职工表的主码为职工号）社会团体表的主码为编号；外码为负责人，被参照表为职工表，对应属性为职工号。）参加表的职工号和编号为主码；职工号为外码，其被参照表为职工表，对应属性为职工号；编号为外码，其被参照表为社会团体表，对应属性为编号。试用SQL语句表达下列操作：）定义职工表、社会团体表和参加表，并说明其主码和参照关系。）建立下列两个视图。社团负责人（编号，名称，负责人职工号，负责人姓名，负责人性别）；参加人情况（职工号，姓名，社团编号，社团名称，参加日期））查找参加唱歌队或篮球队的职工号和姓名。）查找没有参加任何社会团体的职工情况。）查找参加了全部社会团体的职工情况。）查找参加了职工号为“1001”的职工所参加的全部社会团体的职工号。）求每个社会团体的参加人数。）求参加人数最多的社会团体的名称和参加人数。）求参加人数超过100人的社会团体的名称和负责人10）把对社会团体和参加两个表的数据查看、插入和删除数据的权力赋给用户李平，并允许他再将此权力授予其他用户。SQL语言集数据查询、数据操作、数据定义和数据控制功能于一体，语句INSERT、DELETEUPDAT实现下列哪类功能。数据查询B.数据操纵C.数据定义D.数据控制下面列出的关于视图（View）的条目中，不正确的是。A.视图是外模式视图是虚表使用视图可以加快查询语句的执行速度使用视图可以简化查询语句的编写在SQL语言的SELECT吾句中，能实现投影操作的是。A.SELECTB.FROMC.WHERED.GROUPBYALTERSQL吾言集数据查询、数据操纵、数据定义和数据控制功能于一体，吾句TABLE实现哪类功能oA.数据查询B.数据操纵C.数据定义D.数据控制在关系数据库系统中，为了简化用户的查询操作，而又不增加数据的存储空间，常用的方法是创建oA.另一个表B.游标C.视图D.索引设要建立学生选课数据库，库中包括学生、课程和选课3个表，其表结构为：学生（学号，姓名，性别，年龄，所在系）；课程（课程号，课程名，先行课），选课（学号，课程号，成绩）o用Transact-SQL完成下列操作。）建立学生选课库。）建立学生、课程和选课表。）建立各表以主码为索引项的索引）建立学生性别只能为“男”或“女”的规则，性别为“男”的缺省，并将它们绑定在学生表上。）建立在对选课表输入或更改数据时，必须服从参照完整性约束的INSERT和UPDATE虫发器。）建立在删除学生记录时，同时也要把相应的选课记录删除的触发器。）查询各系及学生数，最后来出共有多少系和多少学生。）将学生表和选课表进行内连接、左外连接和右外连接。）列出学生学号、姓名及学习情况。学习情况用好、较好、一般或较差表示。当平均成绩大于85分时，学习情况为好；当平均成绩在70〜85分之间，学习情况为较好；当平均成绩在60〜70分之间，学习成绩为一般；平均成绩在60分以下的为学习情况较差。习题二解答1．答：1）CREATTABL职工（职工号CHAR（10）NOTNULLUNIQUJE姓名CHAR（8）NOTNULL，年龄SMALLINT，性别CHAR（2）,CONSTRAINTC1CHECK性另0IN（'男’，’女’）））；CREATTABLED会团体（编号CHAR8）NOTNULLUNIQUE名称CHAR12）NOTNUL，L负责人CHAR（8），活动地点VARCHAR50），CONSTRAINC2FOREIGNKEY（负责人）REFERENC职工（职工号））;CREATTABL参加（职工号CHAR（8）,编号CHAR（8），参加日期DATECONSTRAINTC3PRIMARYKEYR工号，编号）,CONSTRAIC3FOREIGNKEY（职工号）REFERENC职工（职工号））;2）CREATVIEW社团负责人（编号，名称，负责人职工号，负责人姓名，负责人性另）ASSELECTS号，名称，负责人，姓名，性别FROMi会团体，职工WHER社会团体，负责人二职工.职工号；CREATVIEW参加人情况（职工号，姓名，社团编号，社团名称，参加日期）ASSELECT参加.职工号，姓名，社会团体编号，名称，参加日期FROM只工，社会团体，参加WHER职工.职工号二参加.职工号AND参加.编号二社会团体.编号；SELECT职工号，姓名FROM职工，社会团体，参加WHER职工.职工号二参加.职工号AND参加.编号二社会团体.编号AND社会团体.名称IN('唱歌队’，’篮球队’)SELECT*FROM职工WHERENOTEXISTSSELECT*FROM参加WHER参加.职工号二职工.职工号)；SELECT*FROh职工WHERENOTEXISTSSELECT*FROM参加WHERENOTEXISTS(SELECT*FROMtt会团体WHER参加.职工号二职工.职工号AND参加．编号=社会团体．编号））；SELECT职工号FROMRXWHERENOTEXIST（SSELECT*FROM^力口参加1WHER参加1.职工号二’1001'ANDNOTEXISTS（SELECT*FROM^力口参加2WHER参力口2.编号二参加1.编号AND参加2.职工号一职工.职工号））；7）SELECTTCOUNT职工号）FROM参加GROUPB编号；答：BTOC\o"1-5"\h\z答：C答：A答：C答：C答：l)CREATEDATABASE生选课库ONPRIMARYNANE学生选课库，FILENAM些’C:\学生选课.mdf'SIZE=4MB，MAXSIZ&6MBFILEROWHT=2M)BGOCREATTABLED生(学号CHAR10PRIMARYKEYCLUSTERED姓名CHARY8)，性别CHARY2)，年龄SMALLINT所在系VARCHAYR50))GOCREATTABLE?程(课程号CHAR(10)PRIMARYKEYCLUSTERED课程名VARCHAYR50)，先行课VARCHA（R50））GOCREATETABL选课（学号CHAR（10）,课程号VARCHA（R50），成绩SMALLINT，CONSTRAINTC1PRIMARYKEY学号，课程号）,CONSTRAINTC2FOREIGNKEY学号）REFRENCE学生（学号），CONSTRAINTFOREIGNKEY?程号）REFERENCES程（课程号））GO3）CREATEINDEXstudent_indON学生（学号）GOREATE＊I＊巨class_indON课程（课程号）GOCREATEINDEXselect_indON选课（学号，课程号）GOCREATRULEvalue_ruleAS@VALUEIN('男'，'女')GOCREATDEFAUL性别缺省AS'男’GOEXECsp_bindrule'value_rule'，'学生．性别'GOEXECSp_binddefault'性别缺省'，'学生．性别'GOCREATETRIGGERsc_insertON选课FORINSERTASIF(SELECTCOLJN(T*)FROM学生，inserted，课程WHER学生.学号二inserted.学号AND课程.课程号二inserted.课程号)=0ROLLBACKTRANSACTIONGOCREATETRIGGERsc_updat6ON选课FORUPDATEASIF（SELECTCOUN（T*）FROM学生，updated，课程WHER学生.学号二updated.学号AND课程.课程号二updated.课程号）=0ROLLBACKTRANSACTIONGO6）CREATETRIGGERdelete_allON学生FORDELETEASDELETEFROM选课WHER选课.学号二deleted.学号GOSELECT所在系，COUNT学号)学习情况=CASE乡刑•韦煮'合煮®^L0313S(609a>JI0Pd3丄no丄H9心韦訥ONT』0313S09SlNiorda丄no丄dan韦訥obt¥丄0313S093®NI0rd3NNI韦訥ONT』0313S（809玄丑均人日（帥noom丄ndi/\ioo（（玄丑均）丄onllsicni^inoom丄ndi/\ioo玄丑均-^adnodoWHENAVG成绩）＞=85THEN'好’WHENAVG（绩）＞=75ANDAVG成绩）＜85THEN'较好’WHENAVG（绩）v60THEN'较差’ENDFROM学生选课WHER学生.学号二选课.学号GROUPB选课.学号GO习题三设关系模式R＜AB,C,D＞,函数依赖集F={AtC,A,B^AC,D^ACBD^A。）求出R的候选码。）求出F的最小函数依赖集。）将R分解为3NF使其既具有无损连接性又具有函数依赖保持性设关系模式R,函数依赖集F={ASE,ASF,AXB,BtC,C^D}o）证明ABACAD均是候选关键宇。）证明主属性C部分依赖于关键字AB,传递依赖于AQ同时证明主属性D部分依赖于关键字AC,传递依赖于关键字ABo设关系模式R,函数依赖集F={AB^E,BCTD,BE^C,CD^B,CLAF,CFtBD,CtA,DTEF}，求F的最小函数依赖集。判断下面的关系模式是不是BCNF为什么）任何一个二元关系。）关系模式选课（学号，课程号，成绩），函数依赖集F={（学号，课程号）T成绩}o）关系模式R（A,B,C,D,E,F）,函数依赖集F={AtBCBCTA,BCTEF,ETC}o设关系模式R（ABCDEF）函数依赖集F={ATBCTFETACETA}将R分解为P={ABECDEFo判断p是否是无损连接。设关系模式R{BOISQ．D}函数依赖集F={STDITSISTQBTQ}ol）找出R的主码2)把R分解为BCNF且具有无损连接性7.在关系模式选课(学号,课程号,成绩)中,“学号tt课程号”正确吗为什么8.设有关系模式R(A,B,C),数据依赖集F={ABtC,CttA},R属于第几范式为什么9.设有关系模式R(A,B,C,D),数据依赖集F={AtB,BtA,ACtD,BCtDAD^C,BD^C,CD,BnCD｝。)求R的主码。)R是否为第4范式为什么)R是否是BCNF为什么)R是否是3NF为什么习题三解答1．答：R的候选码为BD。①将F中的函数依赖都分解为右部为单属性的函数依赖。F={2C,SA,BtaBtC,D^A,D^C,BD^A}②去掉F中冗余的函数依赖判断A-C是否冗余设：G1={C—A，B—A，B—C，D—A，D—C，BD—A}，得：(A)G1=A•/C(a)g1・・A—C不冗余判断C—A是不冗余。设：G2={A—C，B—A，B—C，D—A，D—C，BD—A}，得：(C)G2=CA(C)gi/.C—A不冗余判断B-A是否冗余。设：G3={A—C,C—A,B—C,D—A,D—C,BD^A},得：(B)g3=bcaA(B)g3/.B—A冗余判断B—C是否冗余。设：G4{A—C,C—A,D—AD—C,BD^A},得：(B)G4=BC(B)g4二B—C不冗余判断D—A是否冗余设：G5={A^C,C—A,BfC,“C,BDHA},得：(D)g5二DCA•••A(D)g5DfA不冗余判断DfC是否冗余设：G6={AfC,CfA,BfC,BDfA},得：(D)G6=DC(D)g6DfC不冗余判断BDfA是否冗余。设：G7={AfC,CfA,BfC,D^C},得：(BD)G7=BDCA•••A(BD)g7BD^A冗余F={AfC,CfA,BfC,DfC}③由于各函数依赖在部都为单属性．故：Fm={AfC,CfA,BfC,DfC}。T={AC,BC,DC,BD}2．答：.AB为码1)v(AB)f二ABECDFABCDEF(AB)f•••(AC)f=ABECDFABCDEF(AC)F•••AC为码•••(AD)f=ABECDFABCDEF(AD)F•••AD为码2)vBfC部分•••ABCBfC传递C•/AD^B,AD部分•/CfD•••ACC•••BfC,CfD传递CAB传递3．答：①将F中的函数依赖都分解为右部为单属性的函数依赖。F={ALE,BSD,BiC,CD^B,A,F,dB,CiD,SA,DfE,D^F}②去掉F中冗余的函数依赖。判断A4E是否冗余。设：G1={BCfD,BE^C,CD^B,C「A,CE^F,CFfB,CFfD,CfA,fE，DfF}得：(AB)G1=AB•••E(A)giA4E不冗余判断BC－D是否冗余。设：G2={ABtE,BiC,CXB,A,dF,CiB,CiD,A,Dte,DTF}得：(BC)G2=BCAEFDd€(Bc)g2BCTD冗余判断BiC是否冗余。设：G3={ABTE,CDTB,CETA,CETF,CFTB,CFTD,CTA,DTE,DTF}得：(BE)G3=BE•/C(BE)G3.betc不冗余判断CD-B是否冗余。设：G4={ABTE,BETC,CETA,CETF,CFTB,CFTD,CTA,DTE,DTF}得：(CD)G4=CDAEFB