You should understand all basic concepts, not just the ones listed below. You should also understand how to model data and constraints using ER diagrams and furthermore using SQL DDL.

Problem 1. Basic concepts.

a. What are the two properties of a candidate key? Why are these two properties important?

b. What are the two properties of a foreign key constraint?

c. Is a foreign key constraint a syntactic or semantic constraint? Whichever one it is, give an example of the other kind.

d. Is a foreign key constraint a static or dynamic constraint? Whichever one it is, give an example of the other kind.

Problem 2. Modeling for relational databases. (adapted from one last semester)

A store displays items on shelves and would like to keep track of which items are on which shelves. An item is described by a unique item Id, iId, its manufacturer, manuf, 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. When an item is placed on a shelf, a date, removeDate, is specified for when the item will be removed from the shelf. You can assume any reasonable domains for these attributes.

a. Give an E-R diagram that completely describes the entities and relationships of this plan.

b. Translate the E-R diagram into SQL tables using SQL DDL, by specifying a table for items, a table for shelves, and a table for which items are displayed on which shelves.

c. How to require that each manufacturer assign a unique name to each item it produces, in SQL?

d. How to specify that the retail price of an item must always be greater than its costs, in SQL?

e. How to enforce the restriction that the only valid shelf sizes are 100, 150, 200, and 300, in SQL?

f. How to specify that one can not remove a shelf if there is any item on it, in SQL?

g. How the E-R diagram changes if we add the restriction that no shelf is empty?

h. How to enforce the restriction that no shelf is empty in SQL?

i. Sales people need not be concerned with the cost and item Ids of items. Create an element of an external schema which is a view of items that does not contain these attributes.

answer, assuming that Items is the name of the table for items:
  CREATE VIEW Sales (manuf,name,space,price) AS
  SELECT man,name,space,price
  FROM Items

Problem 3. Simple database queries.

Use the table specified in part (b) of Problem 2.

a. Write a SELECT statement that returns all shelves that have a size greater than 100.

b. Write a SELECT statement that counts the total number shelves.

c. Write a SELECT statement that returns the manufacturer and name of all items whose retail price is more than twice its cost.

d. Write a SELECT statement that returns the manufacturer and name of all items displayed on the shelf whose shelf Id is 12345.