1. a. It uniquely identifies an entity, and it is minimal. The first is so that one can talk about an entity and others know which one exactly he is talking about. The second is for convenience and efficiency of identifying an entity. b. The entity being referenced exists and is unique. c. Syntactic. For semantic: A time attribute is between 8:00-12:00AM d. Static. For dynamic: The age attribute increases by 1 each year 2. a. the diagram looks something like below, except that you should put the English below into the diagram as well: put attribute names in ovals connected with the corresponding entity types or relationship type, and underline primary keys. --------- ----------- | Items | -------> <> --------- | Shelves | --------- ----------- Items has attr: iId, manuf, name, space, cost, price; iId is primary key Shelves has attr: sId, size; sId is primary key the relationship/diamond has attr: removeDate b. CREATE TABLE Items ( CREATE TABLE Shelves ( iId: INTEGER, sId: INTEGER, manuf: CHAR[20], size: INTEGER, name: CHAR[20], PRIMARY KEY: sId ) space: INTEGER, cost: INTEGER, price: INTEGER, PRIMARY KEY: iId ) CREATE TABLE Display ( iId: INTEGER, sId: INTEGER, removeDate: DATE, FOREIGN KEY iId REFERENCE Items, FOREIGN KEY sId REFERENCE Shelves, PRIMARY KEY iId ) c. add in table for Items: UNIQUE (manuf, name) d. add in table for Items: CHECK price > cost e. add in table for Shelves: CHECK (size IN (100, 150, 200, 300)) or add CREATE DOMAIN Shelf_Size INTEGER CHECK (VALUE IN (100, 150, 200, 300)) and change domain of size to Shelf_Size f. add in table for Display, under foreign key sId: ON DELETE NO ACTION g. change the line connection diamond and Shelves to think line h. CREATE ASSERTION NoEmptyShelves CHECK (NOT EXISTS ( SELECT * FROM Shelves S WHERE NOT EXISTS ( SELECT * FROM Display D WHERE S.sId = D.sId ))) 3. a. SELECT * FROM Shelves WHERE size > 100 b. SELECT COUNT(*) FROM Shelves c. SELECT manuf, name FROM Items WHERE price > 2*cost d. SELECT I.manuf, I.name FROM Items I, Display D, Shelves S WHERE I.iId = D.iId AND D.sId = S.sId and S.sId = 12345