7.1.1
a)
CREATE TABLE Movies (
title CHAR(100),
year INT,
length INT,
b)
CREATE TABLE Movies (
title CHAR(100),
year INT,
c)
CREATE TABLE Movies (
title CHAR(100),
year INT,
);
d)
e)
CREATE TABLE StarsIn (
movieTitle CHAR(100) REFERENCES Movie(title)
ON DELETE CASCADE,
7.1.2
To declare such a foreign-key constraint between the relations Movie
and StarsIn, values of the referencing attributes in Movie should
7.1.3
ALTER TABLE Product
ADD PRIMARY KEY (model);
7.1.4
ALTER TABLE Classes
ADD PRIMARY KEY (class);
ALTER TABLE Ships
7.1.5
a)
ALTER TABLE Ships
ADD FOREIGN KEY (class) REFERENCES Classes (class)
7.2.1
a)
year INT CHECK (year >= 1915)
7.2.2
a)
CREATE TABLE Laptop (
…
speed DECIMAL(4,2) CHECK (speed >= 2.0)
…
);
b)
7.2.3
a)
CREATE TABLE StarsIn (
…
starName CHAR(30)
CHECK (starName IN (SELECT name FROM MovieStar
WHERE YEAR(birthdate) > movieYear))
…
);
d)
CREATE TABLE Studio (
…
Name CHAR(30) CHECK (name IN (SELECT studioName FROM Movies))
…
);
7.2.4
a)
CHECK (speed >= 2.0 OR price <= 600)
7.2.5
a)
CHECK (class NOT IN (SELECT class FROM Classes
7.2.6
The constraint in Example 7.6 does not allow NULL value for gender
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#);
7.3.2
a)
ALTER TABLE Classes ADD CONSTRAINT myKey
PRIMARY KEY (class, country);
b)
ALTER TABLE Outcomes ADD CONSTRAINT battleCheck
FOREIGN KEY (battle) REFERENCES Battles (name);
7.4.1
a)
CREATE ASSERTION CHECK
(NOT EXISTS
(
(SELECT maker FROM Product NATURAL JOIN PC)
INTERSECT
(SELECT maker FROM Product NATURAL JOIN Laptop)
)
);
c)
CREATE ASSERTION CHECK
(NOT EXISTS
(SELECT model
FROM Laptop
WHERE price <= ALL
(SELECT price FROM PC
WHERE PC.ram < Laptop.ram
)
)
)
);
7.4.2
a)
CREATE ASSERTION CHECK
( 2 >= ALL
(SELECT COUNT(*) FROM Ships GROUP BY class)
);
b)
CREATE ASSERTION CHECK
c)
CREATE ASSERTION 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
e)
CREATE ASSERTION CHECK
(ALL (SELECT class FROM Classes c)
IN (SELECT class FROM Ships GROUP BY class)
);
7.4.3
1)
)
2)
presC# INT Check
(presC# NOT IN (SELECT cert# FROM MovieExec
WHERE netWorth < 10000000
)
)
7.5.1
CREATE TRIGGER AvgNetWorthTrigger
AFTER INSERT ON MovieExec
REFERENCING
7.5.2
a)
CREATE TRIGGER LowPricePCTrigger
AFTER UPDATE OF price ON PC
REFERENCING
WHERE (model, speed, ram, hd, price) IN NewStuff;
INSERT INTO PC
(SELECT * FROM OldStuff);
END;
b)
c)
CREATE TRIGGER AvgPriceTrigger
AFTER UPDATE OF price ON Laptop
REFERENCING
OLD TABLE AS OldStuff,
d)
CREATE TRIGGER HardDiskTrigger
AFTER UPDATE OF hd, ram ON PC
REFERENCING
OLD ROW AS OldRow,
OLD TABLE AS OldStuff,
e)
CREATE TRIGGER DupModelTrigger
BEFORE INSERT ON PC, Laptop, Printer
REFERENCING
NEW ROW AS NewRow,
7.5.3
a)
CREATE TRIGGER NewClassTrigger
AFTER INSERT ON Classes
REFERENCING
b)
CREATE TRIGGER ClassDisTrigger
BEFORE INSERT ON Classes
c)
CREATE TRIGGER newOutcomesTrigger
AFTER INSERT ON Outcomes
REFERENCING
d)
CREATE TRIGGER changeShipTrigger
AFTER INSERT ON Ships
REFERENCING
NEW TABLE AS NewStuff
FOR EACH STATEMENT
WHEN ( 20 < ALL
(SELECT COUNT(name) From Ships NATURAL JOIN Classes
GROUP BY country))
(SELECT COUNT(name) From Ships NATURAL JOIN Classes
GROUP BY country))
BEGIN
e)
CREATE TRIGGER sunkShipTrigger
AFTER INSERT ON Outcomes
DELETE FROM Outcomes
WHERE (ship, battle, result) IN NewStuff;
CREATE TRIGGER sunkShipTrigger2
AFTER UPDATE ON Outcomes
REFERENCING
NEW ROW AS NewRow,
NEW TABLE AS NewStuff
FOR EACH ROW
FOR EACH ROW
WHEN ( (SELECT date FROM Battles WHERE name = NewRow.battle)
7.5.4
CREATE TRIGGER changeStarsInTrigger
AFTER INSERT ON StarsIn
REFERENCING
NEW ROW AS NewRow,
FOR EACH ROW
WHEN (NewRow.starName NOT EXISTS
(SELECT name FROM MovieStar))
b)
CREATE TRIGGER changeMovieExecTrigger
AFTER INSERT ON MovieExec
REFERENCING
NEW ROW AS NewRow,
FOR EACH ROW
WHEN (NewRow.cert# NOT EXISTS
CREATE TRIGGER changeMovieExecTrigger2
AFTER UPDATE ON MovieExec
REFERENCING
NEW ROW AS NewRow,
FOR EACH ROW
WHEN (NewRow.cert# NOT EXISTS
c)
CREATE TRIGGER changeMovieTrigger
AFTER DELETE ON MovieStar
REFERENCING
OLD TABLE AS OldStuff,
d)
CREATE TRIGGER numMoviesTrigger
AFTER INSERT ON Movies
REFERENCING
NEW TABLE AS NewStuff
FOR EACH STATEMENT
WHEN (100 < ALL
(SELECT COUNT(*) FROM Movies
GROUP BY studioName, year))
GROUP BY studioName, year))
BEGIN
DELETE FROM Movies
WHERE (title, year, length, genre, StudioName, procedureC#)
IN NewStuff;
INSERT INTO Movies
(SELECT * FROM OldStuff);
END;
e)
CREATE TRIGGER avgMovieLenTrigger
AFTER INSERT ON Movies
REFERENCING
NEW TABLE AS NewStuff
REFERENCING
OLD TABLE AS OldStuff
NEW TABLE AS NewStuff