****Need some HELP! Normalization*****

I have been trying to decompose the report (I attached it as an doc.file.).

So far what i have for these questions are:

(a) Write the two reports as zero normal form relations that show the
repeating groups.

Customer ( Customer#, Name, Address, Email, Phone, DateJoined
(TransactionID, RentalDate, NoOfVideos (Video#Copy#, Title,
Distributor, Class, Due Date, Shelf, RentalPrice)

PS: NoOfVideos,TransactionTotal, GSTComponent, TVOfTransactions, TNofVideo are all calculated fields, thus not included in the zero form.

(b) Normalise both relations into third normal form according to the
method outlined in class.Clearly specify any appropriate additional business rules.

1.0 First Normal Form (reduction of multivalue attributes from the Zero Norm Form)

1.1 Customer (Customer#, TransactionID, Video#Copy#, Name, Address, Email, Phone, DateJoined)

1.2 Transaction (TransactionID, Video#, RentalDate)

1.3 Video (Video#, Copy#, Distributor,Title, DueDate, ShelfNo, RentalPrice)

1.4 Distributor (Distributor,Video#, Copy#, Class)

2.0 Second Normal Form (reduction of partial functional dependency from the 1st Normal Form)

1.1®2.1 Customer (Customer#, Name, Address, Email, Phone)

2.2 DateJoined (Customer#, DateJoined)

2.3 Cust-Trans (Customer#, TransactionID)

1.2®2.4 Transaction (TransactionID, RentalDate)

1.3®2.5 Video (Video#, Copy#, Title, DueDate, ShelfNo, RentalPrice)

1.4®2.6 Distributor (Distributor)

2.7 Dist-Video (Video#, Class)

3.0 Third Normal Form ( No transitive dependency)

2.1® 3.1 Customer (Customer#, Name, Email)

3.3 CustomerAddress (Customer#, Address, Phone)

3.4 DateJoined (Customer#, DateJoined)

3.5 Cust-Trans (Customer#, TransactionID)

2.4 ®3.6 Transaction (TransactionID, RentalDate)

2.5 ®3.7 Video (Video#, Copy#, TransactionID, Distributor Title, DueDate, ShelfNo, RentalPrice)

3.8 Distributor (Distributor)

3.9 Dist-Video (Distributor,Video#, Class)

I am not sure if they are correct or not. Does anyone know normalization better than me?

Heaps thanx,

hulan

[SECTION="Some Business Rules:"]Some Business Rules:

• Transaction IDs are unique across customers. That is, no two customers will initiate

transactions with the same transaction ID.

• The classification of a video determines its loan period and rental price. For example, all

Weekly videos have a 7 day loan period and cost $3.00 to rent.

• The store can hold several copies of the same video, with different copies being stored on

different shelves within the store.

• Each copy is uniquely identified by a barcode on the spine of the cover, which is a

concatenation of the video code and a copy number. (For example, video number 1 may

have copies numbered 1, 2 and 3. Video number 2 may have copies 1 and 2.) There is no

need to store the barcode in the database as this can be derived using video code and copy

number.

• All copies of the same video have the same classification and are supplied by the same

distributor.

[/SECTION]

   +----------------------------------------------------------------+
   |                  Attachment filename: q2.doc                   |

   +----------------------------------------------------------------+

--
hulan
------------------------------------------------------------------------
Posted via http://www.webservertalk.com
------------------------------------------------------------------------
View this thread: http://www.webservertalk.com/message252137.html