第1页 / 共17页
第2页 / 共17页
第3页 / 共17页
第4页 / 共17页
第5页 / 共17页
第6页 / 共17页
第7页 / 共17页
第8页 / 共17页
数据库系统第七章答案.doc
Solutions
Chapter 7
REFERENCES MovieExec(cert#)
REFERENCES MovieExec(cert#),
CHAR(100),
INT,
INT,
CHAR(10),
CHAR(30),
INT,
7.1.1
a)
CREATE TABLE Movies (
title
year
length
genre
studioName
producerC#
PRIMARY KEY (title, year),
FOREIGN KEY (producerC#)
);
or
CREATE TABLE Movies (
CHAR(100),
title
INT,
year
INT,
length
CHAR(10),
genre
CHAR(30),
studioName
producerC#
INT
PRIMARY KEY (title, year)
);
b)
CREATE TABLE Movies (
CHAR(100),
title
INT,
year
INT,
length
CHAR(10),
genre
CHAR(30),
studioName
producerC#
INT
ON DELETE SET NULL
ON UPDATE SET NULL,
PRIMARY KEY (title, year)
);
c)
CREATE TABLE Movies (
title
year
length
CHAR(100),
INT,
INT,
REFERENCES MovieExec(cert#)
REFERENCES MovieExec(cert#)
CHAR(10),
genre
studioName
CHAR(30),
producerC#
INT
ON DELETE CASCADE
ON UPDATE CASCADE,
PRIMARY KEY (title, year)
);
d)
CREATE TABLE StarsIn (
movieTitle
movieYear
starName
PRIMARY KEY (movieTItle, movieYear, starName)
);
e)
CREATE TABLE StarsIn (
movieTitle
movieYear
starName
PRIMARY KEY (movieTItle, movieYear, starName)
);
CHAR(100)
ON DELETE CASCADE,
INT,
CHAR(30),
CHAR(100)
INT,
CHAR(30),
REFERENCES
Movie(title),
REFERENCES
Movie(title)
7.1.2
To 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 title
and 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.3
ALTER TABLE Product
ADD PRIMARY KEY (model);
ALTER TABLE PC
ADD FOREIGN KEY (model)
REFERENCES
Product (model);
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)
ALTER TABLE Battles
ADD PRIMARY KEY (name);
REFERENCES Classes (calss);
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 SET 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 NULL
ON UPDATE SET NULL;
7.2.1
a)
year
b)
length
c)
studioName
INT
INT
CHECK (year >= 1915)
CHECK (length >= 60 AND length <= 250)
CHAR(30)
CHECK (studioName IN (‘Disney’, Fox’, ‘MGM’, ‘Paramount’) )
DECIMAL(4,2)
CHECK (speed >= 2.0)
7.2.2
a)
CREATE TABLE Laptop (
);
b)
CREATE TABLE Printer (
);
c)
CREATE TABLE Product (
);
d)
CREATE TABLE Product (
CHAR(4)
…
model
…
speed
…
…
type
…
…
type
…
…
7.2.3
a)
VARCHAR(10)
CHECK (type IN (‘laser’, ‘ink-jet’, ‘bubble-jet’))
VARCHAR(10)
CHECK (type IN(‘pc’, ‘laptop’, ‘printer’))
CHECK (model IN (SELECT model FROM PC
UNION ALL
SELECT model FROM laptop
UNION ALL
SELECT model FROM printer))
);
* note this doesn’t check the attribute constraint violation caused by
deletions from PC, laptop, or printer
CHECK (starName IN (SELECT name FROM MovieStar
WHERE YEAR(birthdate) > movieYear))
CHAR(255)
CHECK (address IS UNIQUE)
CREATE TABLE StarsIn (
CHAR(30)
…
starName
…
);
b)
CREATE TABLE Studio (
…
address
…
);
c)
CREATE TABLE MovieStar (
…
name
…
…
Name
…
);
d)
CREATE TABLE Studio (
);
e)
CREATE TABLE Movies (
CHAR(30)
CHECK (name NOT IN (SELECT name FROM MovieExec))
CHAR(30)
CHECK (name IN (SELECT studioName FROM Movies))
…
CHECK (producerC# NOT IN (SELECT presC# FROM Studio) OR
WHERE presC# = producerC#))
studioName IN (SELECT name FROM Studio
…
);
7.2.4
a)
b)
7.2.5
a)
b)
c)
CHECK (speed >= 2.0 OR price <= 600)
CHECK (screen >= 15 OR hd >= 40 OR price <= 1000)
CHECK (class NOT IN (SELECT class FROM Classes
WHERE bore > 16))
CHECK (class NOT IN (SELECT class
FROM Classes
WHERE numGuns > 9 AND bore > 14))
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)
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 noDupInExec
CHECK (name NOT IN (SELECT name FROM MovieExec));
ALTER TABLE MovieExec ADD CONSTRAINT noDupInStar
CHECK (name NOT IN (SELECT name FROM MovieStar));
e)
ALTER TABLE Studio ADD CONSTRAINT noDupAddr
CHECK (address is UNIQUE);
7.3.2
a)
ALTER TABLE Classes ADD CONSTRAINT myKey
b)
ALTER TABLE Outcomes ADD CONSTRAINT battleCheck
PRIMARY KEY (class, country);
FOREIGN KEY (battle) REFERENCES Battles (name);
c)
ALTER TABLE Outcomes ADD CONSTRAINT shipCheck
FOREIGN KEY (ship) REFERENCES Ships (name);
d)
ALTER TABLE Ships ADD CONSTRAINT classGunCheck
CHECK (class NOT IN (SELECT class FROM Classes
WHERE numGuns > 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.1
a)
CREATE ASSERTION CHECK
(NOT EXISTS
(
)
);
INTERSECT
(SELECT maker FROM Product NATURAL JOIN PC)
(SELECT maker FROM Product NATURAL JOIN Laptop)
(NOT EXISTS
b)
CREATE ASSERTION CHECK
(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
)
)
);
(NOT EXISTS
c)
CREATE ASSERTION CHECK
(SELECT model
FROM Laptop
WHERE price <= ALL
)
)
);
d)
CREATE ASSERTION CHECK
(EXISTS
(SELECT price FROM PC
WHERE PC.ram < Laptop.ram
)
(SELECT p2.model FROM Product p1, PC p2
WHERE p1.type = ‘pc’ AND
P1.model = p2.model)
UNION ALL
(SELECT l.model
FROM Product p, Laptop l
WHERE p.type = ‘laptop’ AND
p.model = l.model)
UNION ALL
(SELECT p2.model
FROM Product p1, Printer p2
WHERE p1.type = ‘printer’ AND
P1.model = p2.model)
);
相关推荐
2023年江西萍乡中考道德与法治真题及答案.doc
2012年重庆南川中考生物真题及答案.doc
2013年江西师范大学地理学综合及文艺理论基础考研真题.doc
2020年四川甘孜小升初语文真题及答案I卷.doc
2020年注册岩土工程师专业基础考试真题及答案.doc
2023-2024学年福建省厦门市九年级上学期数学月考试题及答案.doc
2021-2022学年辽宁省沈阳市大东区九年级上学期语文期末试题及答案.doc
2022-2023学年北京东城区初三第一学期物理期末试卷及答案.doc
2018上半年江西教师资格初中地理学科知识与教学能力真题及答案.doc
2012年河北国家公务员申论考试真题及答案-省级.doc
2020-2021学年江苏省扬州市江都区邵樊片九年级上学期数学第一次质量检测试题及答案.doc
2022下半年黑龙江教师资格证中学综合素质真题及答案.doc
