课后作业答案.doc

产品数据库Product(maker, model, type)PC(model, speed, ram, hd, price)Laptop(model, speed, ram, hd, screen, price) Printer(model, color, type, price)关系Product给出了各种产品的制造厂商、型号和产品类型（PC、笔记本电脑或者打印机）。为了简单起见，假设所有产品的型号都唯一，而不管它是由哪个制造商生产的。关系PC对于不同型号给出了如下属性：速度（处理器的速度，单位是GHz）、RAM的大小（单位是MB）、硬盘的容量（单位是GB）以及价格。关系Laptop与关系PC类似，它在PC的基础上增加了属性screen，即显示器的尺寸（以英寸为单位）。关系Printer对于每种型号有如下属性：是否彩色（如果是的话，这个值是true）、处理类型（激光的还是喷墨的）以及价格。关系Product的数据MakermodeltypeA1001pcA1002pcA1003pcA2004laptopA2005laptopA2006laptopB1004pcB1005pcB1006pcB2007laptopC1007pcD1008pcD1009pcD1010pcD3004printerD3005printerE1011pcE1012pcE1013pcE2001laptopE2002laptopE2003laptopE3001printerE3002printerE3003printerF2008laptopF2009laptopG2010laptopH3006printerH3007printer关系PC的数据modelspeedramhdprice10012.661024250211410022.1051225099510031.425128047810042.80102425064910053.2051225063010063.201024320104910072.20102420051010082.20204825077010092.00102425065010102.80204830077010111.86204816095910122.80102416064910133.0651280529关系Laptop的数据modelspeedramhdscreenprice20012.00204824020.1367320021.7310248017.094920031.805126015.454920042.005126013.3115020052.16102412017.0250020062.0020488015.4170020071.83102412013.3142920081.60102410015.490020091.605128014.168020102.00204816015.42300关系Printer的数据modelcolortypeprice3001trueink-jet993002falselaser2393003truelaser8993004trueink-jet1203005falselaser1203006trueink-jet1003007truelaser200二战中的大型舰船数据库Classes(class, type, country, numGuns, bore, displacement)Ships(name, class, launched)Battles(name, date)Outcomes(ship, battle, result)相同设计的舰船组成一个“类”，类别的名称通常就是这个类的第一艘船的名字。关系Classes记录了“类”的名字、型号（bb代表主力舰，bc代表巡洋舰）、生产国家、火炮的门数、火炮的尺寸（口径，单位是英寸）和排水量（重量，单位是吨）。关系Ships记录了舰船的名字、舰船类属名字、开始服役的日期。关系Battles给出了这些舰船参加的战役的时间。关系Outcomes给出了各个舰船在各场战役中的结果（是沉没，还是受伤，或者完好）关系Classes的数据classtypecountrynumGunsboredisplacementBismarckbbGermany81542000IowabbUSA91646000KongobcJapan81432000North CarolinabbUSA91637000RenownbcGt. Britain61532000RevengebbGt. Britain81529000TennesseebbUSA121432000YamatobbJapan91865000关系Ships的数据nameclasslaunchedCaliforniaTennessee1921HarunaKongo1915HieiKongo1914IowaIowa1943KirishimaKongo1915KongoKongo1913MissouriIowa1944MusashiYamato1942New JerseyIowa1943North CarolinaNorth Carolina1941RamilliesRevenge1917RenownRenown1916RepulseRenown1916ResolutionRevenge1916RevengeRevenge1916Royal OakRevenge1916Royal SovereignRevenge1916TennesseeTennessee1920WashingtonNorth Carolina1941WisconsinIowa1944YamatoYamato1941关系Battles的数据namedateDenmark Strait5/24-27/41Guadalcanal11/15/42North Cape12/26/43Surigao Strait10/25/44关系Outcomes的数据shipbattleresultArizonaPearl HarborsunkBismarckDenmark StraitsunkCaliforniaSurigao StraitokDuke of YorkNorth CapeokFusoSurigao StraitsunkHoodDenmark StraitsunkKing George VDenmark StraitokKirishimaGuadalcanalsunkPrince of WalesDenmark StraitdamagedRodneyDenmark StraitokScharnhorstNorth CapesunkSouth DakotaGuadalcanaldamagedTennesseeSurigao StraitokWashingtonGuadalcanalokWest VirginiaSurigao StraitokYamashiroSurigao Straitsunk需要注意的是：这个数据库中存在着“悬浮元组”，比如，在关系Outcomes中出现的船只可能在关系Ships中查不到。作业 一：习题1.11 对于产品数据库中的4个关系，写出它们的关系模式定义语句。CREATE TABLE Product (maker char(3),model char(4) primary key,type char(10);CREATE TABLE PC (model char(4) primary key,speed decimal(3,2),ram int,hd int,price int);CREATE TABLE Laptop (model char(4) primary key,speed decimal(3,2),ram int,hd int,screen decimal(3,1),price int);CREATE TABLE Printer (model char(4) primary key,color char(5),type char(10),price int);2 修改关系Printer，删掉属性color。ALTER TABLE Printer DROP COLUMN color;3 修改关系Laptop，增加属性od（光驱类型，比如CD、DVD）。如果某个笔记本电脑没有光驱，则该属性的默认值为none。ALTER TABLE Laptop ADD od char(10) DEFAULT none;习题1.2 选做1 对于二战中的大型舰船数据库中的4个关系，写出它们的关系模式定义语句。CREATE TABLE Classes (class char(20) primary key,type char(2),country char (20),numGuns smallint,bore smallint,displacement int);CREATE TABLE Ships (name char(20) primary key,class char(20),launched int);CREATE TABLE Battles (name char(20) primary key,date datetime);CREATE TABLE Outcomes (ship char(20),battle char(20),result char(7),primary key (ship, battle);2 修改关系Classes，删掉属性bore。ALTER TABLE Classes DROP COLUMN bore;3 修改关系Ships，增加属性yard，它给出了该船的船坞。ALTER TABLE Ships ADD yard char(30);作业二：（写关系代数表达式）习题2.1 对于产品数据库，试写出下列查询的关系代数表达式。1 哪种PC型号具有最少3.00的速度？pmodel(sspeed=3.00(PC)2 哪个生产厂商的笔记本电脑的硬盘容量至少100GB？pmaker (Product (shd=100 (Laptop)3 查询厂商B生产的所有产品的型号和价格。pmodel, price(smaker=B (Product) PC) pmodel, price(smaker=B (Product) Laptop) pmodel, price(smaker=B (Product) Printer)4 查询所有彩色激光打印机的型号。pmodel (scolor=truetype=laser (Printer)5 查询那些只生产笔记本电脑，不生产PC的厂商。pmaker(stype=laptop(Product) - pmaker(stype=pc(Product)习题2.2 （关系代数计算）已知关系R(A,B):(0,1), (2,3), (0,1), (2,4), (3,4)S(B,C):(0,1), (2,4), (2,5), (3,4), (0,2), (3,4)计算下面的表达式：1. pB+1, C-1(S)2. tB,A(R)3. d(R)4. gB,AVG(C)(S)5. gA,AVG(C)(RS) (注：此处代表自然连接)6. R与S的自然左外连接7. R与S的自然右外连接答：1. (1,0), (3,3), (3,4), (4,3), (1,1), (4,3)2. (0,1), (0,1), (2,3), (2,4), (3,4)3. (0,1), (2,3), (2,4), (3,4)4. (0,1.5), (2,4.5), (3,4)5. (2, 4)6. (2,3,4), (2,3,4), (0,1,NULL), (0,1,NULL),(2,4,NULL),(3,4,NULL)7. (2,3,4), (2,3,4), (NULL,0,1), (NULL,2,4), (NUL,2,5), (NULL,0,2)习题2.3 选做 对于二战中的大型舰船数据库，试写出下列查询的关系代数表达式。1. 查询那些火炮口径大于16英寸的舰船类属和生产国。pclass,country(sbore16 (Classes)2. 查询在丹麦海峡（Denmark Strait）战役中沉没的舰船。pship (sbattle=Denmark Straitresult=sunk(Outcomes)3. 1921年签署的华盛顿条约禁止制造超过35000吨的大型军舰，请列出那些违背华盛顿条约的军舰。pname(sdisplacement35000 (Classes) slaunched1921(Ships)4. 列出参加了瓜达康纳尔岛（Guadalcanal）海战的战舰的名称、排水量及火炮的数目。pname,displacement,numGuns(sbattle=Guadalcanal(Outcomes) sOutcomes.ship=(Ships) Classes)5. 列出那些既有主力舰又有巡洋舰的国家。pcountry(stype=bb (Classes) pcountry(stype=bc (Classes)作业三：（写SQL语句）习题3.1 对于产品数据库，用SQL写出下面的查询：以下4小题要求使用连接查询1 查询硬盘容量至少30G的笔记本电脑制造商及该电脑的速度。SELECT maker,speedFROM Product, LaptopWHERE Product.model=Laptop.model and hd=30; 2 查询制造商B生产的任意类型的所有产品的型号和价格。(SELECT Product.model, price FROM Product, PC WHERE Product.model = PC.model AND maker = B)UNION (SELECT Product.model, price FROM Product, Laptop WHERE Product.model = Laptop.model AND maker = B)UNION (SELECT Product.model, price FROM Product, Printer WHERE Product.model = Printer.model AND maker = B);3 查询出现在两种或两种以上PC中的硬盘大小。SELECT DISTINCT X.hdFROM PC X, PC YWHERE X.hd=Y.hd and X.modelY.model;4 查询每对具有相同速度和RAM的PC的型号。每一对只能列出一次；例如，若(i, j)已被列出，则(j, i)就不能再被列出。SELECT X.model,Y.modelFROM PC X, PC YWHERE X.speed=Y.speed and X.ram=Y.ram and X.model=3.0);6 找出价格最高的打印机。SELECT modelFROM PrinterWHERE price=ALL (SELECT price FROM Printer);7 找出速度比任何一台PC都慢的笔记本电脑。SELECT modelFROM LaptopWHERE speedALL (SELECT speedFROM PC);8 找出价格最低的彩色打印机的制造商SELECT makerFROM ProductWHERE model IN (SELECT model FROM PrinterWHERE color = true AND price =3;习题3.2 对于产品数据库，用SQL写出下面的更新：1 通过两条INSERT语句在数据库中添加如下信息：厂商C生产的型号为1100的PC，其速度为3.2，RAM容量大小为1024，硬盘容量大小为180，售价为$2499。INSERT INTO Product VALUES(C, 1100, pc);INSERT INTO PC VALUES(1100, 3.2, 1024, 180, 2499);2 删除所有硬盘容量低于100GB的PC。DELETE FROM PCWHERE hd=10000000;5找出是演员同时资产至少有$50 000 000的电影公司经理的名字。SELECT StudioPFROM StudioPress, ExecutiveStarWHERE StudioP= ExecutiveS and StudioPress.address= ExecutiveStar.addressand netWorth=50000000;为下面的属性或属性组声明索引：6 StudioNameCREATE INDEX nameIndex ON Movies(studioName);7 genre和lengthCREATE INDEX genreIndex ON Movies(genre,length);作业四：对于电影数据库，对所有关系都定义了主键，如下所示：Movies (title, year, length, genre, studioName, producerC#) StarsIn (movieTitle, movieYear, starName) MovieStar (name, address, gender, birthdate) MovieExec (name, address, cert#, netWorth)Studio (name, address, presC#)习题4.1 声明如下引用完整性约束：1 电影的制片人必须是MovieExec中的某个制片人。任何对MovieExec的更新，若违反此约束则拒绝该操作。FOREIGN KEY (producerC#) REFERENCES MovieExec(cert#)2 重复（1），但是当违反约束时，将Movies中的producerC#置为NULL。FOREIGN KEY (producerC#) REFERENCES MovieExec(cert#)ON UPDATE SET NULL3 重复（1），但是当违反约束时，Movies中违反约束的元组被删除或修改。FOREIGN KEY (producerC#) REFERENCES MovieExec(cert#)ON UPDATE CASCADEON DELETE CASCADE习题4.2 声明关于Movies的属性的约束：1 年份不能是1915年以前。CHECK (year =1915)2 长度不能少于60也不能多于250。CHECK (length=60 and length=250)3 电影公司的名字只能是Disney、Fox、MGM、ParamountCHECK (studioName IN (Disney, Fox, MGM, Paramount)作业五：（计算）习题5.1 针对下列关系模式和FD集合，找出关系的所有候选码。1 R(A, B, C, D)，FD：A BC，CD，DA候选码：(A, B), (B, C), (B, D)2 S(A, B, C, D)，FD：A B，BC，BD候选码：A习题5.2 对于下列关系模式和FD集合，找出所有的3NF违例，如有必要，将关系分解为一系列属于3NF的关系。1. R(A, B, C, D)，FD：A BC，BCD，CDA，ADB候选码：(A, B), (B, C), (C, D), (A, D)不存在3NF违例2. S(A, B, C, D)，FD：A B，BC，CD，DA候选码：(A ), (B), (C), (D)不存在3NF违例习题5.3 考虑关系Courses(C, T, H, R, S, G)，其属性可以理解为课程、教师、时间、教室、学生和成绩。设Courses上的FD有CT，HRC，HTR，HSR，CSG。直观上表示：一门课程有唯一的一个教师；在一个给定的时间和教室，只能有一门课程；在给定的时间里一个教师只能在一个教室；在给定的时间里一个学生只能在一个教室；一个学生在一门课程中只能得到一个成绩。1 给出Courses的所有候选码。候选码：(H, S)2 证明给定的FD本身就是它们的一个最小基本集。略。3 将关系Courses分解为多个3NF关系，分解后的关系中是否有不属于BCNF的关系？R1(C, T), R2(H, R, C), R3(H, T, R), R4(H, S, R), R5(C, S, G)分解后的关