Member Avatar for ranasingh1984

Hi guys I am currently revising but I am having difficulty normalizing the project table below. I can achieve first normal form. but when I try to normalize to work out 2nf I get confused. The table does not come out clearly below so I have attaced a word document showing it and the question i am trying to revise.

PROJECT

projNum ProjName EmpNum Emp Name JobClass Chg Hour Hours

15 Ever Green 103 M. Patel Systems Analyst £90 23
101 B. Smith Database Designer £105 19
105 C. Wang Applications designer £48 35
18 Amber Wave 114 M. Zidane Project Manager £150 40
118 L. Kanoute Programmer £55 23
22 Rolling Tide 105 B. Smith Database Designer £105 19
113 R. Gomez General Support £18 30
106 S. Fellini Database Designer £105 19
25 Starflight 107 O. Kuruza Applications Designer £48 14
115 J. Tarski Systems Analyst £90 48
118 L. Kanoute Programmer £55 45


on attempting to achieve 2NF i get the relations project and employee.

The project table contains
proj_Num (PK)
Proj_Name

The Employee Table contains
Emp_Num(PK), Proj_Num(PK), Emp_Name, job_class, chg_hour and Hours.

I believe that the project table has been normalised to 2NF however I cannot say the same about Employee as it still contains a concatenated Primary Key. I do not know if I am right I need help to answer this question as something similar may come up in the exam. Please can some help me get this to 3NF and help explain how they got it to 3NF. I really want to know how to normalize but th I cannot find a simple guide.

I will be grateful for any help
Thanks

Normalization_question.doc (49.5 KB)
  • 2 Contributors
  • 1 Reply
  • 152 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by tesuji
Member Avatar for tesuji

The Employee Table contains
Emp_Num(PK), Proj_Num(PK), Emp_Name, job_class, chg_hour and Hours.

is still 1NF because Emp_Name and job_class only depend on Emp_Num, that is they depend on a part of PK only. Partial dependency must be removed by moving the regarding attributes into an extra table. Also job_class and chg_hour (I assume this is hourly rate) do not depend on Proj_Num(pk). We get then:
1. Employees: Emp_Num(PK), Emp_Name, job_class, chg_hour
2. ProEmp: Emp_Num(PFK), Proj_Num(PFK), Hours
Both tables now fulfil 2NF. (Hint: If a table fulfils 1NF and its PK consists of one attribute only, such table also fulfils 2NF)

3NF?
ProEmp also fulfils 3NF (there cannot be a transitive dependency if a table has only one non-prime attribute), but Employees does not. There exists transitive dependency Emp_Num(PK) --> job_class --> chg_hour. Removing this leads to 3NF:

3. Employees2: Emp_Num(PK), Emp_Name, job_class(FK)
4. Jobs: job_class(PK), chg_hour

Employees2 and Jobs now fulfil 3NF requirements.

(PFK denotes primary and foreign key, PK primary key, FK foreign key)

I hope this will help you.

krs,
tesu

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.