1. NORMALIZATION OF DATA
A table of data for a library is shown in the table for Problem 9. Normalize these data into the third normal form, preparing them for use in a relational database environment. The library's computer is programmed to compute the due date to be 14 days after the checkout date. Document the steps necessary to normalize the data similar to the procedures found in the chapter. Index any fields necessary, and show how the databases are related.
|
STUDENT ID NUMBER
|
STUDENT FIRST NAME
|
STUDENT LAST NAME
|
NUMBER OF BOOKS OUT
|
BOOK CALL NO
|
BOOK TITLE
|
DATE OUT
|
DUE DATE
|
|
678-98-4567
|
Amy
|
Baker
|
4
|
hf351.j6
|
Avalanches
|
09-02-09
|
09-16-09
|
|
678-98-4567
|
Amy
|
Baker
|
4
|
hf878.k3
|
Tornadoes
|
09-02-09
|
09-16-09
|
|
244-23-2348
|
Ramesh
|
Sunder
|
1
|
i835.123
|
Politics
|
09-02-09
|
09-16-09
|
|
398-34-8793
|
James
|
Talley
|
3
|
k987.d98
|
Sports
|
09-02-09
|
09-16-09
|
|
398-34-8793
|
James
|
Talley
|
3
|
d879.39
|
Legal Rights
|
09-02-09
|
09-16-09
|
|
678-98-4567
|
Amy
|
Baker
|
4
|
p987.t87
|
Earthquakes
|
09-03-09
|
09-17-09
|
|
244-23-2348
|
Ramesh
|
Sunder
|
1
|
q875.i76
|
Past Heroes
|
09-03-09
|
09-17-09
|
2. DEFINING ENTITIES AND DATE MODELING-FIXED ASSET PROCEDURES
The business rules that constitute the fixed asset procedures for the Safe Buy Grocery Stores chain are similar at all the store locations. The store manager at each location is responsible for identifying needed fixed assets and for selecting the vendor. Freezers, refrigerators, delivery vans, and store shelving are examples of fixed asset purchases. Once the need has been identified, each store follows the following procedures.
The manager creates a purchase order, which is sent to the supplier. The supplier delivers the asset to the receiving clerk, who prepares a receiving report. Each week, the fixed asset department clerk reviews the fixed asset receiving report summary and creates a fixed asset inventory record for each receipt. The fixed asset clerk maintains the inventory records and depreciation schedules. The vendor subsequently submits an invoice to the AP department clerk, who creates an invoice record. The clerk reconciles the invoice against the receiving report and purchase order and then creates a payment obligation to be paid at a future date, depending on the terms of trade. On the due date, a check is automatically prepared and sent to the vendor, and the payment is recorded in the check register. At the end of each day, a payment summary is sent to the AP manager for review.
Required
Assume that the manual system described is to be automated using a relational database system. Perform the following tasks. You may need to make assumptions about how certain automated activities will be performed.
a. List all candidate entities in the procedures described.
b. Identify the valid entities, and explain why the rejected entities should no be modeled.
c. Create a data model of the process showing entity associations.
d. Create a fully attributed model by adding primary keys, foreign keys, and data attributes. Normalize the model.