Part A: Database Design
Basic (15 pts)

Question 1:  Defining Relations (9 pts)

   You are to design a database for the inventory kept in a warehouse 
of a grocery chain.
 All inventory items have as relevant attributes 
1. a uniform product code (UPC)
2. a lengthy description, used in ordering documents
3. a received-on date
4. a sell-by date
5. a cost per item
6. a weight per item
7. a supplier code, 
8. a quality code
  There may up four additional fields for some of the item types.
The quality code is a function of the current-date and the sell by date.
  Furthermore, for each supplier there is
1. the address
2. the expected delay from order-date to shipment arrival in the warehouse
For each grocery store there is also
1. the address
2. the expected delay for delivery from the warehouse 

1.a  Define Boyce-Codd-Normal Form relations that can faithfully 
represent these data.
1.b  Sketch a corresponding Entity-relationship diagram, identifying 
the relative cardinality of all relationships.


Question 2.  Views (2 pts)

For the warehouse data of Question 1 define a view convenient for a grocery
order which includes the quality code.


Question  3.  Graph representation (4 pts)

You have a network as follows
                 
     2      4
     o---E--o
   /B \      \G
1 o     \D    o 6
   \A     \  /F
     o--C---o
     3      5
Present two alternative relational representations
Which one is preferable when the graph has to be extended?
State all your assumptions.


Part A: Database Design
Advanced (30 pts)

Question 1: Object Design (8 pts)
   For a warehouse two distinct object configurations are being
proposed by your programmers.  We use R--name--*S to denote the
relationship in an object hierarchy from a superior concept to its
elements.

P1: Suppliers --supply--* Items --neededby--*Customers.
P2: Customers --order--* Items --providedby--*Suppliers.

1a. Which object configuration is correct? 
1b. Which object configuration is more practical, under what assumptions?
Justify your choice carefully.


Question 2: Date Representation: The year 2000 problem (8pts)
   You have become manager of a company with 4 databases systems.
Database A uses 8 8-bit characters for the date: YYYYMMDD.
Database B uses 6 8-bit characters for the date: MMDDYY; this
database creates regularily accessed legal records, that would take
many weeks to convert.
Database C uses 6 4-bit decimal digits for the date: MMDDYY.
Database D uses a 16-bit binary integer to denote days since 1849.

Propose solutions that are reasonable, provide long-term viability, and
simplify interoperation of these databases. 


Question 3: Access performance (12 pts)
In a university we keep a relation for student's grades. In the last week of
each quarter updates are very frequent, although inquiries are frequent
as well, so that performance matters.

The attributes are:
1. Student id
2. Student name
3. class
4. year
5. quarter
6. grade

For each of these 6 attributes suggest an access process and structure
that gives high performance. Consider the selectivity of the attributes.  
State any assumptions you make to arrive at your choice.

 
Question 4: Statistical queries (2 pts)

We frequently need the average grade in a class for the data described in
Question 3. Suggest a structure to accomodate this requirement. Justify your
choice.