Assume a schema for the following set of relations :



Shelves(Sid, Size) with primary key Sid

Items(Iid, Man, Name, Space, Cost, Price) with primary key Iid

Stores(Iid, Sid, Remove Date) with primary key Iid

(a) Give a SELECT statement that returns all rows of Shelves.
(b) Give a SELECT statement that returns all rows of Shelves corresponding to shelves that
have a size greater than 100.
(c) Give a SELECT statement that returns the manufacturer and name in all rows of Items
corresponding to items whose retail price is more than twice its cost.
(d) Give a SELECT statement that returns the manufacturer and name in all rows of Items
corresponding to items stored on the shelf whose Sid has value 12345.
(e) We would like the database schema to enforce the restriction that, for any item stored on
a shelf, the space occupied by the item not exceed the size of the shelf. Use SQL to do
this.

(a) Give a SELECT statement that returns all rows of Shelves.
Solution:



SELECT *

FROM Shelves

(b) Give a SELECT statement that returns all rows of Shelves corresponding to shelves that
have a size greater than 100.
Solution:



SELECT *

FROM Shelves

WHERE Size > 100

(c) Give a SELECT statement that returns the manufacturer and name in all rows of Items
corresponding to items whose retail price is more than twice its cost.
Solution:



SELECT Man, Name

FROM Items

WHERE Price > 2 * Cost

(d) Give a SELECT statement that returns the manufacturer and name in all rows of Items
corresponding to items stored on the shelf whose Sid has value 12345.
Solution:



SELECT I.Man, I.Name

FROM Items I, Stores S, Shelves V

WHERE I.Iid = S.Iid AND S.Sid = V.Sid AND S.Sid = 12345

(e) We would like the database schema to enforce the restriction that, for any item stored on
a shelf, the space occupied by the item not exceed the size of the shelf. Use SQL to do
this.
Solution:



CREATE ASSERTION Fits

CHECK ( NOT EXISTS (

SELECT *

FROM Items I, Stores S, Shelves V

WHERE I.Iid = S.Iid AND S.Sid = V.Sid

AND I.Space > V.Size))