Problem 3 Consider the following schema Sailors ( s-ID , s-name, s-rating, s-age

Question

Problem 3

Consider the following schema

Sailors (s-ID, s-name, s-rating, s-age)

Boats (b-ID, b-name, b-color, b-year)

Reserves (s-ID, b-ID, r-Date)

Assume that the marina has policy that needs to be enforced, specified as follows:

Whenever a new sailor is (attempted to be) entered in the database, if he is younger than 35, give him a “sign-in bonus “reservation of the oldest blue-colored boat (i.e. one with smallest b-year value).

Write the statement for an SQL trigger that will monitor/enforce this policy on behalf of the marina.

Explanation / Answer

To achieve the given policy first we need to query the person with age < 35 and query the oldest blue boat.

SELECT s-ID, b-ID

FROM Sailors S, Boats B

WHERE S.s-ID =

(SELECT S.s-ID

FROM Sailors S

WHERE S.s-age < 35) AND

B. b-ID =

(SELECT B. b-ID

FROM BOATS B

WHERE B. b-color = "blue" AND

B. b-year =

(SELECT MIN (B. b-year)

FROM BOATS B)

Now we got s-ID and b-ID. Update the Reserves table with the r-Date considering s-ID and b-ID.

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

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