Hi 



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 repeat
ing 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 a
re 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, RentalPric
e)

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, Sh
elfNo, 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 bet
ter than me?  



Heaps thanx, 

hulan


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


• Transaction IDs are unique across customers. That is, no two customers wil
l 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, w
hich is a

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

have copies numbered 1, 2 and 3. Video number 2 may have copies 1 and 2.) Th
ere 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]

[ Post a follow-up to this message ]