A store displays items on shelves. An item is described by a unique item Id, Iid, its manufacturer, Man, its name, Name, the space it occupies on a shelf, Space, its cost, Cost, and its retail price, Price. A shelf is described by a unique shelf Id, Sid, and its size, Size. You can assume any reasonable domains for these attributes.

(a) Assume some items are placed on shelves. When an item is placed on a shelf a date,
Remove Date, is specied at which time the item will be removed from the shelf. Give
an E-R diagram that completely describes the entities and relationships of this plan.
(b) In this part you are to translate the entity and relationship sets of (8a) to tables using
SQL. Show all keys (foreign and otherwise).
(c) We would like the database schema to enforce the restriction that the only valid shelf
sizes are 100, 150, 200, and 300. Use SQL to do this.
d) Show how the E-R diagram of (8a) changes if we add the restriction that no shelf is
empty.
(e) Sales people need not be concerned with the cost or item Ids of items. Create a view of
Items that does not contain these attributes.

SOLUTION:



(a)

(b) i. Specify a table, Shelves, for storing information about shelf entities.

Solution:

CREATE TABLE Shelves (


Sid INTEGER,


Size INTEGER,


PRIMARY KEY Sid)

ii. It is assumed that each manufacturer assigns a unique name to each item it produces

and that the retail price of an item is always greater than its cost. Specify a table,

Items, for storing information about item entities that enforces these constraints.

Solution:

CREATE TABLE Items (


Iid INTEGER,


Man CHAR [20],


Name CHAR [20],


Space INTEGER,


Cost INTEGER,


Price INTEGER,


PRIMARY KEY Iid,


CHECK Price > Cost,


UNIQUE (Man, Name))

iii. Specify a table, Stores, for completely describing the relationship type.

Solution:

CREATE TABLE Stores (


Iid INTEGER,


Sid INTEGER,


Remove Date DATE,


FOREIGN KEY Sid REFERENCES Shelves,


FOREIGN KEY Iid REFERENCES Items,


PRIMARY KEY Iid )

(c) Create a new schema element:

CREATE DOMAIN Shelf Sizes INTEGER


CHECK (VALUE IN (100. 150. 200, 300))

and change the size attribute in Shelves to: Size Shelf Sizes. Or add the following

constraint to Shelves

CHECK Size IN (100. 150. 200, 300)

(d) The edge connecting Shelves to the relationship becomes a heavy line.

(e)

CREATE VIEW Sales (Man, Name, Space, Price) AS


SELECT Man, Name, Space, Price


FROM Items