Suggest suitable keys for the relations of the battleships database Classes(clas

Question

Suggest suitable keys for the relations of the battleships database

Classes(class, type, country, numGuns, bore, displacement)
Ships(name, class , launched)
Battles(name, date)
Outcomes(ship, battle , result)

================

Write the following referential integrity constraints for the
battleships database as in Exercise 7.1.4.( see above) Use your assumptions about keys
from that exercise, and handle all violations by setting the referencing attribute
value to NULL.

a) Every class mentioned in Ships must be mentioned in Classes.
b) Every battle mentioned in Outcomes must be mentioned in Battles.
c) Every ship mentioned in Outcomes must be mentioned in Ships.

Textbook: Database Systems: The Complete Book 2nd edition, by Hector Garcia-Molina, Jeff Ullman and Jennifer Widom, Prentice Hall, 2008. ISBN: 978-0131873254

http://people.inf.elte.hu/sila/DB1Lect/Ullman_The_Complete_Book.pdf

Page 319

Explanation / Answer

Solution:

a)

ALTER TABLE Classes
ADD FOREIGN KEY (class) REFERENCES Battles(class);

ON DELETE SET NULL;

b)

ALTER TABLE Battles
ADD FOREIGN KEY (battle) REFERENCES Outcomes(battle);

ON DELETE SET CASCADE;

c)

ALTER TABLE Ships
ADD FOREIGN KEY (ship) REFERENCES Outcomes(ship);

ON DELETE SET CASCADE;

I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.