《数据库系统基础教程第七章答案.pdf》由会员分享，可在线阅读，更多相关《数据库系统基础教程第七章答案.pdf（17页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、SolutionsChapter 77.1.17.1.1a)CREATE TABLE Movies (titleCHAR(100),yearINT,lengthINT,genreCHAR(10),studioNameCHAR(30),producerC#INT,PRIMARY KEY (title, year),FOREIGN KEY (producerC#) REFERENCES MovieExec(cert#);orCREATE TABLE Movies (titleCHAR(100),yearINT,lengthINT,genreCHAR(10),studioNameCHAR(30),p
2、roducerC#INTREFERENCES MovieExec(cert#),PRIMARY KEY (title, year);b)CREATE TABLE Movies (titleCHAR(100),yearINT,lengthINT,genreCHAR(10),studioNameCHAR(30),producerC#INTREFERENCES MovieExec(cert#)ON DELETE SET NULLON UPDATE SET NULL,PRIMARY KEY (title, year);c)CREATE TABLE Movies (titleCHAR(100),year
3、INT,lengthINT,genreCHAR(10),studioNameCHAR(30),producerC#INTREFERENCES MovieExec(cert#)ON DELETE CASCADEON UPDATE CASCADE,PRIMARY KEY (title, year);d)CREATE TABLE StarsIn (movieTitleCHAR(100) REFERENCES Movie(title),movieYearINT,starNameCHAR(30),PRIMARY KEY (movieTItle, movieYear, starName);e)CREATE
4、 TABLE StarsIn (movieTitleCHAR(100) REFERENCES Movie(title)ON DELETE CASCADE,movieYearINT,starNameCHAR(30),PRIMARY KEY (movieTItle, movieYear, starName);7.1.27.1.2Todeclaresuchaforeign-keyconstraintbetweentherelationsMovieandStarsIn,valuesofthereferencingattributesinMovieshouldappearinMovieStarasuni
5、quevalues.However,basedonprimarykeydeclaration in relation StarIn, the uniqueness of movies is guaranteedwithmovieTitle,movieYear,andstarNameattributes.Evenwithtitleandyearasreferencingattributesthereisnowayofreferencingunique movie from StarsIn without starName information. Therefore, sucha constra
6、int can not be expressed using a foreign-key constraint.7.1.37.1.3ALTER TABLE ProductADD PRIMARY KEY (model);ALTER TABLE PCADD FOREIGN KEY (model) REFERENCES Product (model);ALTER TABLE LaptopADD FOREIGN KEY (model) REFERENCES Product(model);ALTER TABLE PrinterADD FOREIGN KEY (model) REFERENCES Prod
7、uct (model);7.1.47.1.4ALTER TABLE ClassesADD PRIMARY KEY (class);ALTER TABLE ShipsADD PRIMARY KEY (name);ALTER TABLE ShipsADD FOREIGN KEY (class) REFERENCES Classes (calss);ALTER TABLE BattlesADD PRIMARY KEY (name);ALTER TABLE OutcomesADD FOREIGN KEY (ship) REFERENCES Ships (name);ALTER TABLE Outcom
8、esADD FOREIGN KEY (battle) REFERENCES Battles (name);7.1.5a)ALTER TABLE ShipsADD FOREIGN KEY (class) REFERENCES Classes (class) ON DELETE SET NULL ON UPDATE SET NULL;In addition to the above declaration, class must be declared theprimary key for Classes.b)ALTER TABLE OutcomeADD FOREIGN KEY (battle)
9、REFERENCES Battles (name) ON DELETE SET NULL ON UPDATE SET NULL;c)ALTER TABLE OutcomesADD FOREIGN KEY (ship) REFERENCES Ships (name) ON DELETE SET NULL ON UPDATE SET NULL;7.2.17.2.1a)yearINTCHECK (year = 1915)b)lengthINTCHECK (length = 60 AND length = 2.0);b)CREATE TABLE Printer ( typeVARCHAR(10) CH
10、ECK (type IN (laser, ink-jet, bubble-jet);c)CREATE TABLE Product ( typeVARCHAR(10) CHECK (type IN(pc, laptop, printer);d)CREATE TABLE Product ( modelCHAR(4) CHECK (model IN (SELECT model FROM PCUNION ALL SELECT model FROM laptopUNION ALL SELECT model FROM printer);* note this doesnt check the attrib
11、ute constraint violation caused bydeletions from PC, laptop, or printer7.2.37.2.3a)CREATE TABLE StarsIn ( starName CHAR(30) CHECK (starName IN (SELECT name FROM MovieStar WHERE YEAR(birthdate) movieYear);b)CREATE TABLE Studio ( address CHAR(255)CHECK (address IS UNIQUE);c)CREATE TABLE MovieStar ( na
12、me CHAR(30)CHECK (name NOT IN (SELECT name FROM MovieExec);d)CREATE TABLE Studio (Name CHAR(30)CHECK (name IN (SELECT studioName FROM Movies);e)CREATE TABLE Movies (CHECK (producerC# NOT IN (SELECT presC# FROM Studio) OR studioName IN (SELECT name FROM Studio WHERE presC# = producerC#);7.2.47.2.4a)C
13、HECK (speed = 2.0 OR price = 15 OR hd = 40 OR price 16)b)CHECK (class NOT IN (SELECT class FROM ClassesWHERE numGuns 9 AND bore 14)c) CHECK (ship IN (SELECT s.name FROM Ships s, Battles b, Outcomes o WHERE s.name = o.ship AND b.name = o.battle AND s.launched YEAR(b.date)7.2.6The constraint in Exampl
14、e 7.6 does not allow NULL value for genderwhile the constraint in Example 7.8 allows NULL.7.3.17.3.1a)ALTER TABLE Movie ADD CONSTRAINT myKeyPRIMARY KEY (title, year);b)ALTER TABLE Movie ADD CONSTRAINT producerCheckFOREIGN KEY (producerC#) REFERENCES MovieExec (cert#);c)ALTER TABLE Movie ADD CONSTRAI
15、NT lengthCheckCHECK (length = 60 AND length 14);e)ALTER TABLE Ships ADD CONSTRAINT shipDateCheck CHECK (ship IN (SELECT s.name FROM Ships s, Battles b, Outcomes o WHERE s.name = o.ship AND b.name = o.battle AND s.launched = YEAR(b.date)7.4.1a)CREATE ASSERTION CHECK (NOT EXISTS ( (SELECT maker FROM P
16、roduct NATURAL JOIN PC) INTERSECT (SELECT maker FROM Product NATURAL JOIN Laptop) ) );b)CREATE ASSERTION CHECK (NOT EXISTS (SELECT maker FROM Product NATURAL JOIN PC WHERE speed ALL (SELECT L2.speed FROM Product P2, Laptop L2 WHERE P2.maker = maker AND P2.model = L2.model ) ) );c)CREATE ASSERTION CH
17、ECK (NOT EXISTS (SELECT model FROM Laptop WHERE price = ALL(SELECT price FROM PC WHERE PC.ram = ALL(SELECT COUNT(*) FROM Ships GROUP BY class) );b)CREATE ASSERTION CHECK (NOT EXISTS(SELECT country FROM ClassesWHERE type = bb )INTERSECT(SELECT country FROM ClassesWHERE type = bc ) );c)CREATE ASSERTIO
18、N CHECK (NOT EXISTS(SELECT o.battle FROM Outcomes o, Ships s, Classes c WHERE o.ship = s.name AND s.class = c.class AND c.numGuns 9 ) INTERSECT(SELECT o.battle FROM Outcomes o, Ships s, Classes cWHERE o.result = sunk AND o.ship = s.name AND s.class = c.class AND c.numGuns 9 ) );d)CREATE ASSERTION CH
19、ECK (NOT EXISTS(SELECT s1.name FROM Ships s1 WHERE s1.launched = 10000000 )2)presC# INT Check(presC# NOT IN (SELECT cert# FROM MovieExec WHERE netWorth (SELECT AVG(netWorth) FROM MovieExec)DELETE FROM MovieExecWHERE (name, address, cert#, netWorth) IN NewStuff;CREATE TRIGGER AvgNetWorthTriggerAFTER
20、DELETE ON MovieExecREFERENCINGOLD TABLE AS OldStuffFOR EACH STATEMENTWHEN (500000 (SELECT AVG(netWorth) FROM MovieExec)INSERT INTO MovieExec(SELECT * FROM OldStuff);7.5.27.5.2a)CREATE TRIGGER LowPricePCTriggerAFTER UPDATE OF price ON PCREFERENCINGOLD ROW AS OldRow,OLD TABLE AS OldStuff,NEW ROW AS Ne
21、wRow,NEW TABLE AS NewStuffFOR EACH ROWWHEN (NewRow.price (SELECT AVG(price) FROM Laptop)BEGINDELETE FROM LaptopWHERE (model, speed, ram, hd, screen, price) IN NewStuff;INSERT INTO Laptop(SELECT * FROM OldStuff);END;d)CREATE TRIGGER HardDiskTriggerAFTER UPDATE OF hd, ram ON PCREFERENCINGOLD ROW AS Ol
22、dRow,OLD TABLE AS OldStuff,NEW ROW AS NewRow,NEW TABLE AS NewStuffFOR EACH ROWWHEN (NewRow.hd 35000)UPDATE NewStuff SET displacement = 35000;c)CREATE TRIGGER newOutcomesTriggerAFTER INSERT ON OutcomesREFERENCINGNEW ROW AS NewRowFOR EACH ROWWHEN (NewRow.ship NOT EXISTS (SELECT name FROM Ships)INSERT
23、INTO Ships (name, class, lunched)VALUES (NewRow.ship, NULL, NULL);CREATE TRIGGER newOutcomesTrigger2AFTER INSERT ON OutcomesREFERENCINGNEW ROW AS NewRowFOR EACH ROWWHEN (NewRow.battle NOT EXISTS (SELECT name FROM Battles)INSERT INTO Battles (name, date)VALUES (NewRow.battle, NULL);d)CREATE TRIGGER c
24、hangeShipTriggerAFTER INSERT ON ShipsREFERENCINGNEW TABLE AS NewStuffFOR EACH STATEMENTWHEN ( 20 ALL(SELECT COUNT(name) From Ships NATURAL JOIN ClassesGROUP BY country)DELETE FROM ShipsWHERE (name, class, launched) IN NewStuff;CREATE TRIGGER changeShipTrigger2AFTER UPDATE ON ShipsREFERENCINGOLD TABL
25、E AS OldStuff,NEW TABLE AS NewStuffFOR EACH STATEMENTWHEN ( 20 ALL(SELECT COUNT(name) From Ships NATURAL JOIN ClassesGROUP BY country)BEGINDELETE FROM ShipsWHERE (name, class, launched) IN NewStuff;INSERT INTO Ships(SELECT * FROM OldStuff);END;e)CREATE TRIGGER sunkShipTriggerAFTER INSERT ON Outcomes
26、REFERENCINGNEW ROW AS NewRowNEW TABLE AS NewStuffFOR EACH ROWWHEN ( (SELECT date FROM Battles WHERE name = NewRow.battle) ALL(SELECT date FROM Battles WHERE name IN (SELECT battle FROM Outcomes WHERE ship = NewRow.ship ANDresult = “sunk” ) ) )DELETE FROM OutcomesWHERE (ship, battle, result) IN NewSt
27、uff;CREATE TRIGGER sunkShipTrigger2AFTER UPDATE ON OutcomesREFERENCINGNEW ROW AS NewRow,NEW TABLE AS NewStuffFOR EACH ROWFOR EACH ROWWHEN ( (SELECT date FROM Battles WHERE name = NewRow.battle) ALL (SELECT COUNT(*) FROM StarIn s, MovieStar mWHERE s.starName = m.nameGROUP BY s.movieTitle, m.gendar) )
28、INSERT INTO MovieStar(SELECT * FROM OldStuff);* only considering DELETE from MovieStar since the assumption was thedesired condistion was satisfied before any change.* not considering INSERT into StarsIn since no gender info can beextracted from a new row for StarsIn.d)CREATE TRIGGER numMoviesTrigge
29、rAFTER INSERTINSERT ON MoviesREFERENCINGNEW TABLE AS NewStuffFOR EACH STATEMENTWHEN (100 ALL(SELECT COUNT(*) FROM MoviesGROUP BY studioName, year)DELETE FROM MoviesWHERE (title, year, length, genre, StudioName, procedureC#)IN NewStuff;CREATE TRIGGER numMoviesTrigger2AFTER UPDATEUPDATE ON MoviesREFER
30、ENCINGOLD TABLE AS OldStuffNEW TABLE AS NewStuffFOR EACH STATEMENTWHEN (100 ALL(SELECT COUNT(*) FROM MoviesGROUP BY studioName, year)BEGINDELETE FROM MoviesWHERE (title, year, length, genre, StudioName, procedureC#)IN NewStuff;INSERT INTO Movies (SELECT * FROM OldStuff);END;e)CREATE TRIGGER avgMovie
31、LenTriggerAFTER INSERTINSERT ON MoviesREFERENCINGNEW TABLE AS NewStuffFOR EACH STATEMENTWHEN (120 ALL(SELECT AVG(length) FROM MoviesGROUP BY year)DELETE FROM MoviesWHERE (title, year, length, genre, StudioName, procedureC#)IN NewStuff;CREATE TRIGGER avgMovieLenTrigger2AFTER UPDATEUPDATE ON MoviesREFERENCINGOLD TABLE AS OldStuffNEW TABLE AS NewStuffFOR EACH STATEMENTWHEN (120 ALL(SELECT AVG(length) FROM MoviesGROUP BY year)BEGINDELETE FROM MoviesWHERE (title, year, length, genre, StudioName, procedureC#)IN NewStuff;INSERT INTO Movies (SELECT * FROM OldStuff);END;