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))

Computer Science & Information Technology

You might also like to view...

A _________________________ is a database used to temporarily persist state data for software programs .

Fill in the blank(s) with the appropriate word(s).

Computer Science & Information Technology

The option part in the command line who H am i is

a: who b: am i c: H d: H and am i e: who H

Computer Science & Information Technology

_________________________ operates on VLANs and treats all VLANs connected as separate physical networks.

Fill in the blank(s) with the appropriate word(s).

Computer Science & Information Technology

In the ____ access control, the user can adjust the permissions for other users over network devices.

A. mandatory B. discretionary C. role based D. temporal

Computer Science & Information Technology