数据库系统课后习题

山东科技大学信息学院数据库教学团队崔宾阁副教授访问密码4be4供应商表：S(SNO,SNAME,STATUS,CITY);零件表：P(PNO,PNAME,COLOR,WEIGHT);工程项目表：J(JNO,JNAME,CITY);供应情况表：SPJ(SNO,PNO,JNO,QTY);（1）求供应工程J1零件的供应商号码SNO；（2）求供应工程J1零件P1的供应商号码SNO；（3）求供应工程J1零件为红色的供应商号码SNO；（4）求没有使用天津供应商生产的红色零件的工程号JNO；（5）求至少用了供应商S1所供应的全部零件的工程号JNO。（1）求供应工程J1零件的供应商号码SNO；◦关系代数：◦ALPHA：GETW(SPJ.SNO):SPJ.JNO=‘J1’（2）求供应工程J1零件P1的供应商号码SNO；◦关系代数：◦ALPHA：GETW(SPJ.SNO):SPJ.JNO=‘J1’∧SPJ.PNO=‘P1’（3）求供应工程J1零件为红色的供应商号码SNO；◦关系代数：◦ALPHA：RANGEPPX◦GETW(SPJ.SNO):SPJ.JNO=‘J1’∧◦∃PX(PX.PNO=SPJ.PNO∧PX.COLOR=‘红’)（4）求没有使用天津供应商生产的红色零件的工程号JNO；◦关系代数：◦ALPHA语言：◦RANGESSX◦RANGEPPX◦RANGESPJSPJX◦GETW(J.JNO):SPJX(SPJX.JNO=J.JNO∧◦SX(SX.SNO=SPJX.SNO∧SX.CITY=‘天津’)∧◦PX(PX.PNO=SPJX.PNO∧PX.COLOR=‘红’))（5）求至少用了供应商S1所供应的全部零件的工程号JNO。◦关系代数：◦ALPHA语言：◦RANGESPJSPJX◦RANGESPJSPJY◦RANGEPPX◦GETW(J.JNO):◦PX(SPJX(SPJX.SNO=‘S1’∧SPJX.PNO=PX.PNO)◦SPJY(SPJY.JNO=J.JNO∧SPJY.PNO=PX.PNO))有两个关系S(A,B,C,D)和T(C,D,E,F)，写出与下列查询等价的SQL表达式：（1）𝜎𝐴=10𝑆；◦SELECT*FROMSWHEREA=10（2）𝜋𝐴,𝐵𝑆；◦SELECTA,BFROMS（3）◦SELECTA,B,S.C,S.D,E,F◦FROMS,T◦WHERES.C=T.CANDS.D=T.D（4）◦SELECTS.*,T.*◦FROMS,T◦WHERES.C=T.C（5）◦SELECTS.*,T.*◦FROMS,T◦WHEREAE（6）◦SELECTS.C,S.D,T.*FROMS,T（1）求供应工程J1零件的供应商号码SNO；（2）求供应工程J1零件P1的供应商号码SNO；（3）求供应工程J1零件为红色的供应商号码SNO；（4）求没有使用天津供应商生产的红色零件的工程号JNO；（5）求至少用了供应商S1所供应的全部零件的工程号JNO。创建供应商表S：◦CREATETABLES(◦SNOCHAR(2)PRIMARYKEY,◦SNAMEVARCHAR(10),◦STATUSCHAR(2),◦CITYVARCHAR(10));创建零件表P：◦CREATETABLEP(◦PNOCHAR(2)PRIMARYKEY,◦PNAMEVARCHAR(10),◦COLORCHAR(2),◦WEIGHTINT);创建工程项目表J：◦CREATETABLEJ(◦JNOCHAR(2)PRIMARYKEY,◦JNAMEVARCHAR(10),◦CITYVARCHAR(10)◦);创建供应情况表SPJ：◦CREATETABLESPJ(◦SNOCHAR(2),◦PNOCHAR(2),◦JNOCHAR(2),◦QTYINT,◦PRIMARYKEY(SNO,PNO,JNO),◦FOREIGNKEY(SNO)REFERENCESS(SNO),◦FOREIGNKEY(PNO)REFERENCESP(PNO),◦FOREIGNKEY(JNO)REFERENCESJ(JNO)◦);（1）求供应工程J1零件的供应商号码SNO；◦SELECTSNO◦FROMS◦WHEREJNO='J1'（2）求供应工程J1零件P1的供应商号码SNO；◦SELECTSNO◦FROMS◦WHEREJNO='J1'ANDPNO='P1'第（3）-（5）题的答案参照“SQL难题解疑”。（1）查询所有供应商的姓名和所在城市；◦SELECTSNAME,CITY◦FROMS（2）查询所有零件的名称、颜色和重量；◦SELECTPNAME,COLOR,WEIGHT◦FROMP（3）查询使用供应商S1所供应零件的工程号码；◦SELECTJNO◦FROMSPJ◦WHERESNO='S1'（4）查询工程项目J2使用的零件的名称及其数量；◦SELECTPNAME,QTY◦FROMSPJ,P◦WHERESPJ.PNO=P.PNOANDSPJ.JNO='J2'（5）找出上海厂商供应的所有零件号码；◦SELECTDISTINCTPNO◦FROMSPJ,S◦WHERESPJ.SNO=S.SNOANDS.CITY='上海'（6）找出使用上海产的零件的工程名称；◦SELECTDISTINCTJNAME◦FROMSPJ,S,J◦WHERESPJ.SNO=S.SNOANDSPJ.JNO=J.JNOANDS.CITY='上海'（7）找出没有使用天津产的零件的工程号码；◦SELECTJNO◦FROMJ◦WHEREJNONOTIN(◦SELECTJNO◦FROMSPJ,S◦WHERESPJ.SNO=S.SNOANDS.CITY='天津')（8）把全部红色零件的颜色改成蓝色；◦UPDATEP◦SETCOLOR='蓝'◦WHERECOLOR='红'（9）将供应商S5供给工程J4的零件P6改成由供应商S3供应，请做必要的修改；◦UPDATESPJ◦SETSNO='S3'◦WHERESNO='S5'ANDJNO='J4'ANDPNO='P6'（10）从供应商关系中删除S2的记录，并从供应情况关系中删除相应的记录；◦DELETEFROMSPJWHERESNO='S2';◦DELETEFROMSWHERESNO='S2';（11）将（S2,J6,P4,200）插入SPJ表中。◦INSERT◦INTOSPJ(SNO,JNO,PNO,QTY)◦VALUES('S2','J6','P4',200);请为“三建”工程项目建立一个供应情况的视图，包括供应商代码（SNO）、零件代码（PNO）、供应数量（QTY）。◦CREATEVIEWSANJIAN_SPJ(SNO,PNO,QTY)◦AS◦SELECTSNO,PNO,QTY◦FROMSPJ,J◦WHERESPJ.JNO=J.JNOANDJ.JNAME='三建'针对该视图完成下列查询：（1）找出“三建”工程项目使用的各种零件代码及其数量；1.SELECTPNO,QTYFROMSANJIAN_SPJ;2.SELECTPNO,SUM(QTY)ASQTY◦FROMSANJIAN_SPJ◦GROUPBYPNO;（2）找出供应商S1的供应情况。◦SELECT*◦FROMSANJIAN_SPJ◦WHERESNO='S1'（1）授予用户U1对两个表的所有权限，并可给其他用户授权。◦GRANTALLPRIVILEGES◦ONTABLE学生,班级◦TOU1◦WITHGRANTOPTION（2）授予用户U2对学生表具有查询权限，对家庭住址具有更新权限。◦GRANTSELECT,UPDATE(家庭住址)◦ONTABLE学生◦TOU2（3）将对班级表的查询权限授予所有用户。◦GRANTSELECT◦ONTABLE班级◦TOPUBLIC（4）将对学生表的查询、更新权限授予角色R1。◦GRANTSELECT,UPDATE◦ONTABLE学生◦TOR1（5）将角色R1授予用户U1，U1可继续授权给其他用户。◦GRANTR1◦TOU1◦WITHADMINOPTION（1）用户王明对两个表有SELECT权限。◦GRANTSELECT◦ONTABLE部门,职工◦TO王明（2）用户李勇对两个表有INSERT和DELETE权限。◦GRANTINSERT,DELETE◦ONTABLE部门,职工◦TO李勇（3）每个职工只对自己的记录有SELECT权限。◦无答案，仅仅使用GRANT语句无法做到（4）用户刘星对职工表有SELECT权限，对工资字段有更新权限。◦GRANTSELECT,UPDATE(工资)◦ONTABLE职工◦TO刘星（5）用户张新具有修改这两个表的结构的权限。◦GRANTALTER◦ONTABLE职工,部门◦TO张新（6）用户周平具有对这两个表的所有权限（读、插、改、删数据），并具有给其他用户授权的权限；◦GRANTSELECT,INSERT,UPDATE,DELETE◦ONTABLE职工,部门◦TO周平◦WITHGRANTOPTION（7）用户杨兰具有查询每个部门职工的最高工资、最低工资、平均工资的权限，他不能查看每个人的工资；◦CREATEVIEW部门工资统计(部门号,部门名,最高工资,最低工资,平均工资)◦AS◦SELECT部门.部门号,部门名,MAX(工资),MIN(工资),AVG(工资)◦FROM部门,职工◦WHERE部门.部门号=职工.部门号◦GROUPBY部门.部门号,部门名◦GRANTSELECT◦ON部门工资统计◦TO杨兰假设有下面两个关系模式：职工（职工号，姓名，年龄，职务，工资，部门号），其中职工号为主码；部门（部门号，部门名，经理名，电话），部门号为主码。用SQL语言定义这两个关系模式，要求：1）定义每个关系模式的主码；2）定义参照完整性约束；3）定义职工年龄不得超过60岁。CREATETABLE部门(部门号CHAR(4)PRIMARYKEY,部门名VARCHAR(40),经理名VARCHAR(10),电话VARCHAR(11));CREATETABLE职工(职工号CHAR(10)PRIMARYKEY,姓名VARCHAR(10),年龄INTCHECK(年龄=60),职务VARCHAR(10),工资FLOAT,部门号CHAR(4),FOREIGNKEY(部门号)REFERENCES部门(部门号));某单位想举行一个小型的联谊会，关系Male记录注册的男宾信息，关系Female记录注册的女宾信息。建立一个断言，将来宾的人数限制在50人以内。◦CREATEASSERTION人数限制◦CHECK(50=SELECTCOUNT(*)◦FROM(SELECT*FROMMale◦UNION◦SELECT*FROMFemale)◦ASGuest◦);◦学生（学号，姓名，出生年月，系名，班号，宿舍区）◦班级（班号，专业名，系名，人数，入校年份）◦系（系名，洗好，系办公室地点，人数）◦学会（学会名，成立年份，地点，人数）函数依赖：◦学号姓名，学号出生年月，学号系名，学号班号，学号宿舍区。◦班号专业名，班号系名，班号人数，班号入校年份。◦系名系号，系号系名，系名办公地点，系名人数。◦学会名成立年份，学会名地点，学会名人数。◦专业名系名，（专业名，入校年份）班号，◦系名宿舍区，（学号，学会名）入会年份学生关系模式的极小函数依赖集为：◦学号姓名，学号出生年月，学号班号，班号系名，系名宿舍区。◦∵学号班号，班号系名，∴存在学号系名的传递函数依赖。◦∵学号系名，系名宿舍区，∴存在学号宿舍区的传递函数依赖。◦∵班号系名，系名宿舍区，∴存在班号宿舍区的传递函数依赖。候选码：学号，外部码：班号，系名。班级关系模式的极小函数