Normalisation

[hiyamwah]

Hi i am a little unsure of how you do this problem:

A company wishes to allocate staff to a new project that as been proposed by the company. As a result, a new relation was created for this propose as follows:

Staff_Project (Staff_No, Proj_Id, Proj_Name Proj_Location, Staff_Hours, Staff_Name, Staff_Address)
Assuming that,

Staff_No stands for staff number of the employee;

Proj_Id is identifies of one of the projects that the staff works on;

Staff_Hours is the number of hours per week that the staff works on the project;

Proj_Name and Project_location are the name and location of the project;

Staff_Name is the staff surname;

Staff_Address is postcode of the address of the staff.
__________________________________________________ __

This is what is needed:

(a)Using appropriate diagrams to aid in your answer, briefly describe the concepts of “Normalisation” and “Functional Dependencies”. You should then describe the relationship between them with examples.

(b)Based on the given primary key (Staff_No, Proj_Id). Show, comprehensively, how you would normalise the relation to third normal form, indicating the functional dependencies you use in each stage of normalisation with justifications. State any assumptions you make about the data, if any.

(c)Identify the primary and foreign keys in your 3NF relations.
__________________________________________________ __

This is what ive done so far:

(a)Concepts of Normalisation and Functional Dependencies with the use of diagrams.

Staff _No ------------ Staff_Name
Project_Id -------- Proj_Name and Project_location
Staff_Name ------ Staff_Address and Staff_Hours

dotted lines are functional dependant

Your help and advise is much appreciated

Thanks

[darylbnz]

I would have thought that this would result in 3 tables;

Table1: Staff (Staff_No [PrimaryKey], Staff_Name, Staff_Address) - The Staff_No is the only unique field.

Table2: Projects (Proj_ID [PrimaryKey], Proj_Name, Proj_Location) - The Proj_ID is the only unique field.

Table3: ProjectTime (Proj_ID [ForeignKey], Staff_No [ForeignKey], Staff_Hours) - This table does not need a primary key but you could assign a new unique index if you wish. It would probably be useful in a real situation. The Foreign keys are the primary keys in their own tables.

hope this helps.

[sharifa]

hiya i have exactly the same scenario of ur courswrok n am really stuck. can ya giv us any way of starting it. thanks