数据库系统基础教程第七章答案.docx

SolutionsChapter 77.1.1a)CREATE TABLE Movies (title year length genre studioName producerC#CHAR(100), INT, INT, CHAR(10), CHAR(30), INT,PRIMARY KEY (title, year),FOREIGN KEY (producerC#) REFERENCES MovieExec(cert#) );orCREATE TABLE Movies (title year length genre studioName producerC#CHAR(100),INT,INT,CHAR(10),CHAR(30),INTREFERENCES MovieExec(cert#),PRIMARY KEY (title, year) );b)CREATE TABLE Movies (titleyear length genre studioName producerC#CHAR(100),INT,INT,CHAR(10),CHAR(30), INTREFERENCES MovieExec(cert#)ON DELETE SET NULL ON UPDATE SET NULL, PRIMARY KEY (title, year) );c)CREATE TABLE Movies (title year lengthCHAR(100), INT, INT,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 TABLE StarsIn (movieTitleCHAR(100)REFERENCES Movie(title)ON DELETE CASCADE,movieYearINT,starNameCHAR(30),PRIMARY KEY (movieTItle, movieYear, starName);7.1.2To declare such a foreign-key constraint between the relations Movie and StarsIn, values of the referencing attributes in Movie should appear in MovieStar as unique values. However, based on primary key declaration in relation StarIn, the uniqueness of movies is guaranteed with movieTitle, movieYear, and starName attributes.Even with titleand year as referencing attributes there is no way of referencing unique movie from StarsIn without starName information. Therefore, such a constraint can not be expressed using a foreign-key constraint.7.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 Product (model);ALTER 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 OutcomesADD 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 the primary key for Classes.b)ALTER TABLE OutcomeADD FOREIGN KEY (battle) 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;a)yearINTCHECK (year >= 1915)b)lengthINTCHECK (length>= 60 AND length <= 250)c)studioNameCHAR(30)CHECK (studioName IN (, Disney?, Fox ?, MGM? , ,Paramount7.2.2a)CREATE TABLE Laptop (speedDECIMAL(4,2);CHECK (speed >=2.0)b)CREATE TABLE Printer (typeVARCHAR(10)CHECK (type IN (,laser?, , ink-jet ?, ,bubble-jet?);c)CREATE TABLE Product (typeVARCHAR(10)CHECK (type IN(,pc? , laptop?, , printer?);d)CREATE TABLE Product (7.2.1?) )modelCHAR(4)CHECK (model IN (SELECT model FROM PCUNION ALLSELECT model FROM laptopUNION ALLSELECT model FROM printer);* note this doesn?t check the attribute constraint violation caused bydeletions from PC, laptop, or printer7.2.3a)CREATE TABLE StarsIn (starName CHAR(30)CHECK (starName IN (SELECT name FROM MovieStarWHERE YEAR(birthdate) > movieYear);b)CREATE TABLE Studio (address CHAR(255);CHECK (address IS UNIQUE)c)CREATE TABLE MovieStar (name CHAR(30);d)CREATE TABLE Studio (Name CHAR(30);e)CREATE TABLE Movies (CHECK (name NOT IN (SELECT name FROM MovieExec)CHECK (name IN (SELECT studioName FROM Movies)CHECK (producerC# NOT IN (SELECT presC# FROM Studio) ORstudioName IN (SELECT name FROM StudioWHERE presC# = producerC#);7.2.4a)CHECK (speed >= 2.0 OR price <= 600)b)CHECK (screen >= 15 OR hd >= 40 OR price <= 1000)7.2.5a)CHECK (class NOT IN (SELECT class FROM ClassesWHERE bore > 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 oWHERE s.name = o.ship ANDb.name = o.battle ANDs.launched > YEAR(b.date)7.2.6The constraint in Example 7.6 does not allow NULL value for genderwhile the constraint in Example 7.8 allows NULL.7.3.1a)ALTER TABLE Movie ADD CONSTRAINT myKey PRIMARY KEY (title, year);b)ALTER TABLE Movie ADD CONSTRAINT producerCheck FOREIGN KEY (producerC#) REFERENCES MovieExec (cert#);c)ALTER TABLE Movie ADD CONSTRAINT lengthCheck CHECK (length >= 60 AND length <= 250);d)ALTER TABLE MovieStar ADD CONSTRAINT noDupInExecCHECK (name NOT IN (SELECT name FROM MovieExec);ALTER TABLE MovieExec ADD CONSTRAINT noDupInStarCHECK (name NOT IN (SELECT name FROM MovieStar);e)ALTER TABLE Studio ADD CONSTRAINT noDupAddr CHECK (address is UNIQUE);7.3.2a)ALTER TABLE Classes ADD CONSTRAINTmyKeyPRIMARY KEY (class, country);b)ALTER TABLE Outcomes ADD CONSTRAINTbattleCheckFOREIGN KEY (battle) REFERENCES Battles (name);c)ALTER TABLE Outcomes ADD CONSTRAINTshipCheckFOREIGN KEY (ship) REFERENCES Ships (name);d)ALTER TABLE Ships ADD CONSTRAINT classGunCheckCHECK (class NOT IN (SELECT class FROM ClassesWHERE numGuns > 14);e)ALTER TABLE Ships ADD CONSTRAINT shipDateCheckCHECK (ship IN (SELECT s.name FROM Ships s, Battles b, Outcomes oWHERE s.name = o.ship ANDb.name = o.battle ANDs.launched >= YEAR(b.date)7.4.1a)CREATE ASSERTION CHECK(NOT EXISTS(SELECT maker FROM Product NATURAL JOIN PC)INTERSECT(SELECT maker FROM Product NATURAL JOIN Laptop) );b)CREATE ASSERTION CHECK(NOT EXISTS(SELECT makerFROM Product NATURAL JOIN PCWHERE speed > ALL(SELECT L2.speedFROM Product P2, Laptop L2WHERE P2.maker = maker ANDP2.model = L2.model);c)CREATE ASSERTION CHECK(NOT EXISTS(SELECT modelFROM LaptopWHERE price <= ALL(SELECT price FROM PCWHERE PC.ram < Laptop.ram);d)CREATE ASSERTION CHECK(EXISTS(SELECT p2.model FROM Product p1, PC p2WHERE p1.type = ,pc? ANDP1.model = p2.model)UNION ALL(SELECT l.modelFROM Product p, Laptop lWHERE p.type = ,laptop? ANDp.model = l.model)UNION ALL(SELECT p2.modelFROM Product p1, Printer p2WHERE p1.type = ,printer? ANDP1.model = p2.model);7.4.2a)CREATE ASSERTION CHECK( 2 >= 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 ASSERTION CHECK(NOT EXISTS(SELECT o.battle FROM Outcomes o, Ships s, Classes cWHERE 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 ANDs.class = c.class AND c.numGuns < 9);d)CREATE ASSERTION CHECK(NOT EXISTS(SELECT s1.name FROM Ships s1WHERE s1.launched < (SELECT s2.launched FROM Ships s2WHERE s2.name = s1.class);e)CREATE ASSERTION CHECK(ALL (SELECT class FROM Classes c)IN (SELECT class FROM Ships GROUP BY class) );7.4.31)presC# INT CHECK(presC# IN (SELECT cert# FROM MovieExecWHERE netWorth >= 10000000)2)presC# INT Check(presC# NOT IN (SELECT cert# FROM MovieExecWHERE netWorth < 10000000)7.5.1CREATE TRIGGER AvgNetWorthTriggerAFTER INSERT ON MovieExecREFERENCINGNEW TABLE AS NewStuffFOR EACH STATEMENTWHEN (500000 > (SELECT AVG(netWorth) FROM MovieExec)DELETE FROM MovieExecWHERE (name, address, cert#, netWorth) IN NewStuff;CREATE TRIGGER AvgNetWorthTriggerAFTER DELETE ON MovieExecREFERENCINGOLD TABLE AS OldStuffFOR EACH STATEMENTWHEN (500000 > (SELECT AVG(netWorth) FROM MovieExec)INSERT INTO MovieExec(SELECT * FROM OldStuff);7.5.2a)CREATE TRIGGER LowPricePCTriggerAFTER UPDATE OF price ON PCREFERENCINGOLD ROW AS OldRow,OLD TABLE AS OldStuff,NEW ROW AS NewRow,NEW TABLE AS NewStuffFOR EACH ROWWHEN (NewRow.price < ALL(SELECT PC.price FROM PCWHERE PC.speed = NewRow.speed)BEGINDELETE FROM PCWHERE (model, speed, ram, hd, price) IN NewStuff;INSERT INTO PC(SELECT * FROM OldStuff);END;b)CREATE TRIGGER NewPrinterTriggerAFTER INSERT ON PrinterREFERENCINGNEW ROW AS NewRow,NEW TABLE AS NewStuffFOR EACH ROWWHEN (NOT EXISTS (SELECT * FROM ProductWHERE Product.model = NewRow.model)DELETE FROM PrinterWHERE (model, color, type, price) IN NewStuff;c)CREATE TRIGGER AvgPriceTriggerAFTER UPDATE OF price ON Laptop REFERENCINGOLD TABLE AS OldStuff,NEW TABLE AS NewStuffFOR EACH STATEMENTWHEN (1500 > (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 PC REFERENCINGOLD ROW AS OldRow,OLD TABLE AS OldStuff, NEW ROW AS NewRow, NEW TABLE AS NewStuffFOR EACH ROWWHEN (NewRow.hd < NewRow.ram * 100)BEGINDELETE FROM PCWHERE (model, speed, ram, hd, price) IN NewStuff; INSERT INTO PC(SELECT * FROM OldStuff);END;e)CREATE TRIGGER DupModelTriggerBEFORE INSERT ON PC, Laptop, Printer REFERENCINGNEW ROW AS NewRow,NEW TABLE AS NewStuffFOR EACH ROWWHEN (EXISTS (SELECT * FROM NewStuff NATUAL JOIN PC)UNION ALL(SELECT * FROM NewStuff NATUAL JOIN Laptop) UNION ALL(SELECT * FROM NewStuff NATUAL JOIN Printer) BEGINSIGNAL SQLSTATE ,10001?(,Duplicate Model Insert Failed?);END;7.5.3a)CREATE TRIGGER NewClassTrigger AFTER INSERT ON Classes REFERENCINGNEW ROW AS NewRowFOR EACH ROWBEGININSERT INTO Ships (name, class, lunched)VALUES (NewRow.class, NewRow.class, NULL);END;b)CREATE TRIGGER ClassDisTriggerBEFORE INSERT ON ClassesREFERENCINGNEW ROW AS NewRow,NEW TABLE AS NewStuffFOR EACH ROWWHEN (NewRow.displacement > 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 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 changeShipTriggerAFTER INSERT ON ShipsREFERENCINGNEW TABLE AS NewStuffFOR EACH STATEMENTWHEN ( 20 < ALL(SELECT COUNT(name) From Ships NATURAL JOIN Classes GROUP BY country)DELETE FROM ShipsWHERE (name, class, launched) IN NewStuff;CREATE TRIGGER changeShipTrigger2AFTER UPDATE ON ShipsREFERENCINGOLD TABLE AS OldStuff,NEW TABLE AS NewStuffFOR EACH STATEMENTWHEN ( 20 < ALL(SELECT COUNT(name) From Ships NATURAL JOIN Classes GROUP BY country)BEGINDELETE FROM ShipsWHERE (name, class, launched) IN NewStuff;INSERT INTO Ships(SELECT * FROM OldStuff);END;e)CREATE TRIGGER sunkShipTriggerAFTER INSERT ON OutcomesREFERENCINGNEW ROW AS NewRowNEW TABLE AS NewStuffFOR EACH ROWWHEN ( (SELECT date FROM Battles WHERE name = NewRow.battle)< ALL(SELECT date FROM BattlesWHERE name IN (SELECT battle FROM OutcomesWHERE ship = NewRow.ship ANDresult =“ sunk)DELETE FROM OutcomesWHERE (ship, battle, result) IN NewStuff;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 date FROM BattlesWHERE name IN (SELECT battle FROM OutcomesWHERE ship = NewRow.ship ANDresult =“ sunk)BEGINDELETE FROM OutcomesWHERE (ship, battle, result) IN NewStuff;INSERT INTO Outcomes(SELECT * FROM OldStuff);END;7.5.4CREATE TRIGGER changeStarsInTrigger AFTER INSERT ON StarsInREFERENCINGNEW ROW AS NewRow,FOR EACH ROWWHEN (NewRow.starName NOT EXISTS(SELECT name FROM MovieStar)INSERT INTO MovieStar(name)VALUES(NewRow.starName);CREATE TRIGGER changeStarsInTrigger2AFTER UPDATE ON StarsInREFERENCINGNEW ROW AS NewRow,FOR EACH ROWWHEN (NewRow.starName NOT EXISTS(SELECT name FROM MovieStar)INSERT INTO MovieStar(name)VALUES(NewRow.starName);b)CREATE TRIGGER changeMovieExecTriggerAFTER INSERT ON MovieExecREFERENCINGNEW ROW AS NewRow,FOR EACH ROWWHEN (NewRow.cert# NOT EXISTS(SELECT presC# FROM Studio)UNION ALLSELECT producerC# FROM Movies)INSERT INTO Movies(procucerC#) VALUES(NewRow.cert#);* insert into the relation Movies rather than Studio since there?s no associated info with Studio.CREATE TRIGGER changeMovieExecTrigger2AFTER UPDATE ON MovieExecREFERENCINGNEW ROW AS NewRow,FOR EACH ROWWHEN (NewRow.cert# NOT EXISTS(SELECT presC# FROM Studio)UNION ALLSELECT producerC# FROM Movies)INSERT INTO Movies(procucerC#)VALUES(NewRow.cert#);c)CREATE TRIGGER changeMovieTriggerAFTER DELETE ON MovieStarREFERENCINGOLD TABLE AS OldStuff,FOR EACH STATEMENTWHEN ( 1 > ALL (SELECT COUNT(*) FROM StarIn s, MovieStar mWHERE s.starName = m.nameGROUP BY s.movieTitle, m.gendar)INSERT INTO MovieStar(SELECT * FROM OldStuff);1 only considering DELETE from MovieStar since the assumption was the desired condistion was satisfied before any change.2 * not considering INSERT into StarsIn since no gender info can be extracted from a new row for StarsIn.d)CREATE TRIGGER numMoviesTriggerAFTER INSERT 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 UPDATE ON MoviesREFERENCINGOLD 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 avgMovieLenTriggerAFTER INSERT ON MoviesREFERENCINGNEW TABLE AS NewStuffFOR EACH STATEMENTWHEN (120 < ALL(SELECT AVG(length) FROM MoviesGROUP BY year)D