“battleships” database schema: Classes(class, type, country, numGuns, bore, disp

Question

“battleships” database schema:

Classes(class, type, country, numGuns, bore, displacement)

Ships(name, class, launched) Battles(name, date)

Outcomes(ship, battle, result)

Write each of the following SQL Constraints:

a) Write the following as tuple-based CHECK SQL constraint: If a class of ships has more than 18 guns, then their bore must be no larger than 28 inches.

? b) Write the following as tuple-based CHECK SQL constraint: No ship can be in battle before it is launched.

c) Write the following as SQL Assertions: No country may have both battle-ships and battle-cruisers.

d) Write the following as SQL Assertions: For every class, there is a ship with the name of that class. ?

Write the following as SQL Triggers. In each case, disallow or undo the modification if it does not satisfy the stated constraint.

e) When a new class is inserted into Classes, also insert a ship with the name of that class and a NULL launch date.

f) When a new class is inserted with a displacement greater than 44,000 tons, allow the insertion, but change the displacement to 44,000. ?

Explanation / Answer

This question has multiple sub parts. I have answered the first three:

(a)

Alter table Classes ADD CONSTRAINT borecheck CHECK ( numGUNS > 18 AND bore < 28)

you can also write it like this while creating table

(b)

Alter table Battles ADD CONSTRAINT battlecheck CHECK ((select date from battles) > (select launched from ships))

(c)

create assertion checkname CHECK ( SELECT C1.country
FROM    Classes C1,
Classes C2
WHERE   C1.country = C2.country
AND C1.type    = 'bb'
AND C2.type    = 'bc' );

create assertion chk1 CHECK(not exists( Select class from classes where ( class = select class from battles))

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

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