数据库作业答疑,向小岩 刘沾沾 2006年5月,第一次作业,P18 1.3、1.5、1.7 P36 2.4、2.5、2.6 1.3 参见课本P19 1.5 参见课本P9 1.7 参见课本P20 2.4 参见课本P29 2.5 参见课本P27如果用户和用户的应用程序能相对于数据库物理结构的改变而保 持不变，则实现了物理独立性；如果用户和用户的应用程序能相 对于数据库逻辑结构的改变而保持不变，则实现了逻辑独立性。 2.6 参见课本P30 “数据字典”相关内容,第二次作业,P52 3.2、3.3 3.3 见P.54参考答案 3.2TABLE,第二次作业,COLUMN,第三次作业,讲义Chapter 4 SQL(1)第90面 题目：Write the following queries, based on the following database example：Movie (title, year, length, inColor, studioName, producerC#)StarsIn (movieTitle, movieYear, strName) MovieStar (name, address, gender, birthdate)MovieExec (name, address, cert#, netWorth)Studio (name, address, presC#)Classes (class, type, country, numGuns, bore, displacement)Ships (name, class, launched)Battles (name, date)Outcomes (ship, battle,result),第三次作业,In SQL.1. Find Sandra Bullocks birthdate2. Find all executives worth at least $10,000,0003. Find all the stars who either are male or live in Malibu 4.Which stars appeared in movies produced by MGM in 1995?5. Who is the president of MGM studio?6. Find the countries whose ships had the largest number of guns.7. Find the names of the ship with 16-inch bore.8. Find the average number of guns of battleship classes.,第三次作业,1select birthdatefrom MovieStarwhere name = Sandra Bullock; 2.select namefrom MovieExecwhere netWorth = 10000000; 3.select namefrom MovieStarwhere gender = male or address = Malibu; 4.select distinct StarsIn.starNamefrom StarsIn, Moviewhere StarsIn.movieTitle = Movie.titleand Movie.year = 1995and Movie.studioNmae = MGM;,第三次作业,5.select MovieExec.namefrom MovieExec, Studiowhere MovieExec.cert# = Studio.presC#and Studio.name = MGM; 6.select countryfrom ( select country, sum (numGuns) sumGunsfrom Classesgroup by country )where sumGuns = max (sumGuns);另一种方式：select countryfrom Classeswhere numGuns = max (numGuns); 7.select Ships.namefrom Ships, Classeswhere Ships.Classes.bore = 16;,第三次作业,8.select avg (numGuns) from Classeswhere type = battleship;,第四次作业,P67 4.5 见教材P73 补充题：讲义Chapter 4 SQL(2) function of SQL (oracle) P34第3题 Find all departmentsname which have at least five employees whose salary grater than $5000.表头：DEPT (DEPT#, DNAME, BUDGET)EMP (EMP#, ENAME, DEPT#, SALARY),第四次作业,SELECT DNAMEFROM DEPTWHERE DEPT# IN ( SELECT DEPT#FROM EMP#WHERE SALARY 5000GROUP BY DEPT#HAVING COUNT (EMP#) = 5 );,第五次作业,P130 6.13 6.15 P163 7.3 7.12 7.13.13 7.13.15 6.13 参见教材P136答案 6.15 参见教材P136答案 7.3 参见教材P168答案 7.12 查询：找出供应零件P2 的供应商名 （参见教材P170答案） Select Distinct S.snameFrom S, SPWhere S.s# = SP.s# AND SP.p# = P2; Select Distinct S.snameFrom SWhere S.s# IN ( Select SP.s#From SPWhere SP.p# = P2 ); Select Distinct T.snameFrom ( S Join SP ON S.s# = SP.s# AND SP.p# = P2 ) AS T;,第五次作业,7.13使用元组演算重写6.13.13和6.13.15题 7.13.13、7.13.15 参见教材P171答案,第六次作业,P194 8.1 8.9 8.1 （要求使用SQL语句） （也可参考教材P207面8.14题的答案） b. create domain s# char(s)constraint valid_s#check ( substr (the_s#(s#),1,1) = s andcast_as_integer (substr(the_s#(s#),2) =0 andcast_as_integer (substr(the_s#(s#),2) JY.J# and JX.city = JY.city);,第六次作业,g. create assertion G check (not exists ( select * from ( select * from Swhere s.status = ( select min(status) from S) S1,( select * from Swhere s.status = ( select max(status) from S) S2where S1.city = S2.city and S1.s# S2.s# );j. create assertion AJ check( not exists (select * from P) or exists ( select * from Pwhere P.color = red);,第六次作业,m. create assertion M check( not exists ( select * from P where color = red) orexists ( select * from Pwhere color = red and weight ( select sum(SPJ.qty) from SPJ,Swhere SPJ.s# = S.s# and S.city = PARIS); q. （SQL一般不支持）（可参见课本P202代数形式的答案）8.9 见课本答案,第七次作业,P228 9.2 9.5 9.6 P387 16.1 9.2 方法一：参见教材答案 方法二：CREATE VIEW NOT_COLOCATEDAS SELECT S#, P#FROM S, PWHERE S.CITY P.CITY; 方法三：CREATE VIEW NOT_COLOCATEDAS SELECT S.S#, P.P#FROM S, P, SPWHERE S.S# = SP.S#AND SP.P# = P.P#AND S.CITY P.CITY;,第七次作业,9.5 （注意：并不要求P1由S1提供！） 方法一：参见教材答案 方法二：CREATE VIEW JCAS SELECT J#, CITYFROM JWHERE J# IN ( SELECT J# FROM SPJ WHERE S# = S1)AND J# IN ( SELECT J# FROM SPJ WHERE P# = P1);,第七次作业,9.6 （要求使用SQL语句） (a)SELECT P#, WEIGHT WT, COLOR COLFROM PWHERE WEIGHT 14.0AND COLOR = Green; (b)SELECT P#, (WEIGHT + 5.3) WTPFROM PWHERE WEIGHT 14.0;,第七次作业,(c)UPDATE PSET COLOR = WhiteWHERE WEIGHT = 18; (d)DELETE FROM PWHERE WEIGHT 14.0 AND WEIGHT 14.0;违反谓词，执行失败。16.1参见教材P390答案,第八次作业,P246 10.8 10.11 P271 11.4 11.5 参见教材答案,第九次作业,P296 12.2 12.3 P321 13.6 13.8 补充题：讲义Chapter 13 semantic model P94第1题12.2 12.3 参见教材答案,第九次作业,13.6 假设： 在任一时刻下 （1）一个办公室只属于一个部门 （2）一个雇员至多属于一个办公室，至多领导一个部门 （3）一个项目只属于一个部门 （4）一个雇员参加至多一个项目 （5）部门号、办公室号、项目号、雇员号、电话号码都是全局唯一的 （6）不考虑部门之间以及项目之间的从属、包含关系,第九次作业,13.6 参考答案一,第九次作业,13.6 参考答案二,第九次作业,13.8 附加两个约束： C1： APR AREA#, PROD# =AREA AREA# TIMES PROD PROD# C2： REP# - AREA# | PROD#,