《数据库系统基础教程课后答案第七章》由会员分享，可在线阅读，更多相关《数据库系统基础教程课后答案第七章（17页珍藏版）》请在金锄头文库上搜索。
1、7.1.1 a) CREATE TABLE Movies ( title CHAR(100), year INT, length INT, genre CHAR(10), studioName CHAR(30), producerC# INT, PRIMARY KEY (title, year), FOREIGN KEY (producerC#) REFERENCES MovieExec(cert#) ); or CREATE TABLE Movies ( title CHAR(100), year INT, length INT, genre CHAR(10), studioName CHA
2、R(30), producerC# INT REFERENCES MovieExec(cert#), PRIMARY KEY (title, year) ); b) CREATE TABLE Movies ( title CHAR(100), year INT, length INT, genre CHAR(10), studioName CHAR(30), producerC# INT REFERENCES MovieExec(cert#) ON DELETE SET NULL ON UPDATE SET NULL, PRIMARY KEY (title, year) ); c) CREAT
3、E TABLE Movies ( title CHAR(100), year INT, length INT, genre CHAR(10), studioName CHAR(30), producerC# INT REFERENCES MovieExec(cert#) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY (title, year) ); d) CREATE TABLE StarsIn ( movieTitle CHAR(100) REFERENCES Movie(title), movieYear INT, starName CH
4、AR(30), PRIMARY KEY (movieTItle, movieYear, starName) ); e) CREATE TABLE StarsIn ( movieTitle CHAR(100) REFERENCES Movie(title) ON DELETE CASCADE, movieYear INT, starName CHAR(30), PRIMARY KEY (movieTItle, movieYear, starName) ); 7.1.2 To declare such a foreign-key constraint between the relations M
5、ovie 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 title and year as referenci
6、ng 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.3 ALTER TABLE Product ADD PRIMARY KEY (model); ALTER TABLE PC ADD FOREIGN KEY (model) REFERENCES Product (model);
7、 ALTER TABLE Laptop ADD FOREIGN KEY (model) REFERENCES Product(model); ALTER TABLE Printer ADD FOREIGN KEY (model) REFERENCES Product (model); 7.1.4 ALTER TABLE Classes ADD PRIMARY KEY (class); ALTER TABLE Ships ADD PRIMARY KEY (name); ALTER TABLE Ships ADD FOREIGN KEY (class) REFERENCES Classes (ca
8、lss); ALTER TABLE Battles ADD PRIMARY KEY (name); ALTER TABLE Outcomes ADD FOREIGN KEY (ship) REFERENCES Ships (name); ALTER TABLE Outcomes ADD FOREIGN KEY (battle) REFERENCES Battles (name); 7.1.5 a) ALTER TABLE Ships ADD FOREIGN KEY (class) REFERENCES Classes (class) ON DELETE SET NULL ON UPDATE S
9、ET NULL; In addition to the above declaration, class must be declared the primary key for Classes. b) ALTER TABLE Outcome ADD FOREIGN KEY (battle) REFERENCES Battles (name) ON DELETE SET NULL ON UPDATE SET NULL; c) ALTER TABLE Outcomes ADD FOREIGN KEY (ship) REFERENCES Ships (name) ON DELETE SET NUL
10、L ON UPDATE SET NULL; 7.2.1 a) year INT CHECK (year = 1915) b) length INT CHECK (length = 60 AND length = 2.0) ); b) CREATE TABLE Printer ( type VARCHAR(10) CHECK (type IN (laser, ink-jet, bubble-jet) ); c) CREATE TABLE Product ( type VARCHAR(10) CHECK (type IN(pc, laptop, printer) ); d) CREATE TABL
11、E Product ( model CHAR(4) CHECK (model IN (SELECT model FROM PC UNION ALL SELECT model FROM laptop UNION ALL SELECT model FROM printer) ); * note this doesnt check the attribute constraint violation caused by deletions from PC, laptop, or printer 7.2.3 a) CREATE TABLE StarsIn ( starName CHAR(30) CHE
12、CK (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 ( name CHAR(30) CHECK (name NOT IN (SELECT name FROM MovieExec) ); d) CREATE TABLE Studio ( Name CHAR(30) CHECK (name IN (
13、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.4 a) CHECK (speed = 2.0 OR price = 15 OR hd = 40 OR price 16) b) CHECK (class NOT IN (SELECT class FROM Classes
14、WHERE 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.6 The constraint in Example 7.6 does not allow NULL value for gender while the constraint in Example 7.8 allows NULL. 7.3.1 a)
15、 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 14); e) ALTER TABLE Ships ADD CONSTRAINT shipDateC
16、heck 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.1 a) 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 maker FROM Product NATURAL JOIN PC WHERE speed ALL (SELECT L2.speed F
