Normalisation in a Video Library

A video library operates in a similar way to any normal library, so the first thing to do is to write down a list of attributes for the whole of the system. It would in this case be easy to put the attributes straight into separate tables but in a more complex system this would not do. It is also important not to list to many attributes, only keeping it to the ones that are definitely needed. MEMBER (Membership number, Surname, Forename, Title, Street, Town, Postcode, Date of birth, Tel. No. , (Video number, Video title, Category, Cost, Price, Rental price, Date borrowed))

At this stage I have placed all the attributes into the entity MEMBER. I must now go through the normalisation process to determine how many tables are needed and which ones they will be. Due to the video details being repeating attributes they need to be removed and put into 1NF by being giving their own entity. MEMBER (Membership number, Surname, Forename, Title, Street, Town, Postcode, Date of birth, Tel. No. ,) Membership Number is underlined because in this case it is a key field, all the other attributes relate to this field.

We Will Write a Custom Essay Specifically
For You For Only $13.90/page!


order now

VIDEO RENTAL (Membership number, Video number, Video title, Category, Cost, Price, Rental price, Date borrowed) The data now needs converted to 2NF. This needs to be done because the attributes do not depend on both keys. Video number, Video title, Category, Cost price and Rental price depend only on the Video number and therefore need placed in their own entity named VIDEO. MEMBER (Membership number, Surname, Forename, Title, Street, Town, Postcode, Date of birth, Tel. No. ,) VIDEO RENTAL (Membership number, Video number, Video title, Category, Cost, Price, Rental price, Date borrowed)

VIDEO (Video number, Video title, Category, Cost price, Rental price) The data is now in 2NF, it needs to be put into 3NF. To do this we have to look and see if any of the attributes are dependent on other non-key attributes. In this case this is none therefore 2NF becomes 3NF and data shown below is fully normalised. MEMBER (Membership number, Surname, Forename, Title, Street, Town, Postcode, Date of birth, Tel. No. ,) VIDEO RENTAL (Membership number, Video number, Video title, Category, Cost, Price, Rental price, Date borrowed) VIDEO (Video number, Video title, Category, Cost price, Rental price)