These homework assignment is largely a repeat of Homework 10 from the Spring 1998 semester which was, in turned, an extension of a problem from the Quiz 2 of that term.
The table
Start with a table used to track purchase orders. Each purchase order is made to a specific company and contains several lines corresponding to the items being ordered. The specific columns of this table are
| attribute | description |
|---|---|
PO-NUM |
A unique id for purchase orders |
LINE-NUM |
The line number within a purchase order |
REQUESTOR |
The name of the person requesting the purchase. This is expressed as initials followed by last name. |
REQ-OFFICE |
The office of the person requesting the purchase. This is where the item should be delivered. |
DEPT |
Department that pays for the order |
ACCT-NUM |
Account number to be charged |
ITEM |
Brief name of the item |
QUANTITY |
Number of the item being order |
COST |
Cost of the item |
Here are some organization rules that should help you in define functional dependencies useful for normalizing the database.
- All items on a single PO must be paid from the same account number.
- Each item of PO is identified by a line number.
- Every requestor is associated with one department.
- Every account number is associated with one department.
- A department may have several account numbers.
- All items of the same type have the same price.
Homework 8
Homework 8 is to write an SQL statement that will create a completely unnormalized table containing the information described above.
Submit this statement as a file to the
Homework 8 Moodle page.
The file that you submit must be a text file that
can be executed with the .read command of
sqlite3.
Homework 9
Homework 9 of the assignment is to create some reasonable functional dependencies and to normalize the table into third normal form. Remember that Boyce-Codd normal form is more restrictive than third normal form.
Submit this homework in class on Wednesday, April 2.Homework 10
Homework 10 is to write SQL statements that create your databases and populate them with some interesting tuples to illustrate the database.
Submit these statements as a file to the
Homework 10 Moodle page.
The file that you submit must be a text file that
can be executed with the .read command of
sqlite3.