help with normalisation

[shauna]

hi, i am an As level student studying Applied ICT, im having problems with normalisation.

our problem is to computerise a made up business.mine for example is a beauty salon. below are my attributes and they need to be normalised to 3rd normal form! i have also shown what i have come up with but i am unsure as to whether it is correct!

please help as im finding this very difficult.

Normalisation
The goal of normalisation is to create a set of relational tables that are free of redundant data and that can be consistently and correctly modified. This means that all tables in a relational database should be in the third normal form (3NF).

By normalising my database it will ensure that;
• There is no data redundancy among my tables, i.e. there will be no unnecessary duplication.
• Data is consistent in all my tables. For example if a field is updated in one table it will update in all tables. This will save time.
• Complex queries can be performed from data on different tables.

Below are all the fields that will be included in my database

Customer (ID, title, first name, surname, address, town, postcode, telephone#, mobile#, DOB, email address, medical details, allergies, previous treatments, booking ID, date of booking, appt date, appt time, treatment required, treatment duration, treatment price, stock ID, stock name, amount in stock, reorder level, supplier ID, supplier name, supplier email, supplier telephone #)

First normal form- in this normal form I removed all the repeating groups

Customer file (customer ID, title, first name, surname, address, town, postcode, telephone #, mobile#, DOB, email address, medical details, allergies, previous treatments,)

Booking file (booking ID, customer ID, date of booking, appt date, appt time, treatment required, treatment duration, treatment price, stock ID, customer id, stock name, amount in stock, reorder level supplier ID, supplier name, supplier email, supplier telephone number)




Second normal form- here I removed the partial dependencies (A partial dependency is when an attribute is functionally dependent on only
part of the primary key.)

Customer file (customer ID, title, first name, surname, address, town, postcode, telephone #, mobile#, DOB, email address, medical details, allergies, previous treatments,)

Booking file (booking ID, customer ID)

Booking file (booking ID, date of booking, appt date, appt time, treatment required, treatment duration, treatment price, stock ID, customer id, stock name, amount in stock, reorder level supplier ID, supplier name, supplier email, supplier telephone number)


3rd normal form - here I have removed the transitive dependencies (these are repeating attributes.)


Customer file (customer ID, title, first name, surname, address, town, postcode, telephone #, mobile#, DOB, email address, medical details, allergies, previous treatments,)

Booking file (booking ID, customer ID)


Booking file (booking ID, date of booking, appt date, appt time, treatment required, treatment duration, treatment price, stock ID, customer id, stock name, amount in stock, reorder level)

Supplier (supplier ID, supplier name, supplier email, supplier telephone number)

[MMcCarthy]

Firstly have a look at this tutorial and then come back with any questions you may have.

Normalisation and Table structures

Mary