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

=o.battleAND
unched> 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.
ON DELETE SET NULL
ON UPDATE SET NULL;
c)
ALTER TABLE Outcomes
ADD FOREIGN KEY (ship) REFERENCES Ships (name)
ON DELETE SET NULL
ON UPDATE SET NULL;
7.2.1
a)
yearINTCHECK (year >= 1915)
7.1.1
a)
CREATE TABLE Movies(
titleCHAR(100),
yearINT,
lengthINT,
genreCHAR(10),
studioNameCHAR(30),
producerC#INT,
PRIMARY KEY (title, year),
FOREIGN KEY (producerC#)REFERENCESMovieExec(cert#)
PRIMARY KEY (title, year)
);
c)
CREATE TABLE Movies(
titleCHAR(100),
yearINT,
lengthINT,
genreCHAR(10),
studioNameCHAR(30),
producerC#INTREFERENCESMovieExec(cert#)
…
);
c)
CREATE TABLEMovieStar(
…
name CHAR(30)CHECK (name NOT IN (SELECT name FROMMovieExec))
…
);
d)
CREATE TABLE Studio (
…
Name CHAR(30)CHECK (name IN (SELECTstudioNameFROM Movies))
a)
CHECK (speed >= 2.0 OR price <= 600)
b)
CHECK (screen >= 15 ORhd>= 40 OR price <= 1000)
7.2.5
a)
CHECK (class NOT IN (SELECT class FROM Classes
WHERE bore > 16))
c)
ALTER TABLE Movie ADD CONSTRAINTlengthCheck
CHECK (length >= 60 AND length <= 250);
d)
ALTERTABLEMovieStarADD CONSTRAINTnoDupInExec
CHECK (name NOT IN (SELECT name FROMMovieExec));
…
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 (
7.3.1
a)
ALTER TABLE Movie ADD CONSTRAINTmyKey
PRIMARY KEY (title, year);
b)
ALTER TABLE Movie ADD CONSTRAINTproducerCheck
FOREIGN KEY (producerC#) REFERENCESMovieExec(cert#);
CREATE TABLEStarsIn(
…
starNameCHAR(30)
CHECK (starNameIN (SELECT name FROMMovieStar
WHERE YEAR(birthdate) >movieYear))
…
);
b)
CREATE TABLE Studio (
…
address CHAR(255)CHECK (addressIS UNIQUE)
b)
CHECK (class NOT IN (SELECT class FROM Classes
WHEREnumGuns> 9 AND bore > 14))
c)
CHECK (ship IN (FROM Ships s, Battles b, Outcomes o
=o.shipAND
);
or
CREATE TABLE Movies(
titleCHAR(100),
yearINT,
lengthINT,
genreCHAR(10),
studioNameCHAR(30),
producerC#INTREFERENCESMovieExec(cert#),
PRIMARY KEY (title, year)
ALTERTABLEMovieExecADD CONSTRAINTnoDupInStar
CHECK (name NOT IN (SELECT name FROMMovieStar));
e)
ALTER TABLE Studio ADD CONSTRAINTnoDupAddr
CHECK (address is UNIQUE);
UNION ALL
SELECT model FROM printer))
…
);
* note this doesn’t check the attributeconstraintviolation caused bydeletions from PC, laptop, or printer
7.2.3
a)
ALTER TABLE Printer
ADDFOREIGN KEY (model) REFERENCESProduct (model);
7.1.4
ALTER TABLE Classes
ADDPRIMARY KEY (class);
ALTER TABLE Ships
ADDPRIMARY KEY (name);
PRIMARY KEY (movieTItle,movieYear,starName)
);
7.1.2
Todeclare such aforeign-keyconstraint betweenthe relationsMovie andStarsIn, values ofthereferencingattributesin Movie should appear inMovieStaras unique values. However,based on primary key declaration in relationStarIn,the uniqueness of movies is guaranteed withmovieTitle,movieYear, andstarNameattributes. Even with title and year as referencing attributes there is no way of referencing unique moviefromStarsInwithoutstarNameinformation.Therefore, such a constraint can not be expressed using a foreign-key constraint.
…
typeVARCHAR(10)
CHECK (type IN(‘pc’,‘laptop’,‘printer’))
…
);
d)
CREATE TABLE Product (
…
modelCHAR(4)
CHECK (model IN (SELECT model FROM PC
UNION ALL
SELECT model FROM laptop
ON DELETE CASCADE
ON UPDATE CASCADE,
PRIMARY KEY (title, year)
);
d)
CREATE TABLEStarsIn(
movieTitleCHAR(100)REFERENCESMovie(title),
movieYearINT,
starNameCHAR(30),
b)
lengthINTCHECK (length >= 60 AND length <= 250)
c)
studioNameCHAR(30)
CHECK (studioNameIN (‘Disney’, Fox’,‘MGM’,‘Paramount’) )
7.2.2
a)
CREATE TABLE Laptop (
ALTER TABLE Outcomes
ADDFOREIGN KEY (battle) REFERENCES Battles (name);
7.1.5
a)
ALTER TABLE Ships
ADD FOREIGN KEY (class) REFERENCES Classes (class)
ON DELETE SET NULL
);