How do i turn the table below into third normal form, the text book im reading i
ID: 3873263 • Letter: H
Question
How do i turn the table below into third normal form, the text book im reading is somewhat helpful but im still confused on how to divide the table up and what to use as a secondary primary key.
I would like to see what the table looks like in second normal form as well, that way i can get a better idea of how it breaks down.
Fname
Lname
SSN
Bdate
Address
Sex
Salary
Pname
Pnumber
Hours
Jason
Smith
123-45-6789
02/12/1978
569 Main st.
M
60,000
Project1
P0001
45
Jason
Smith
123-45-6789
02/12/1978
569 Main st.
M
60,000
Project3
P0003
67
Mark
Johnson
341-04-8745
06/30/1956
876 Oak Ave.
F
89,000
Project7
P0007
12
Lee
Navathe
672-23-9375
12/22/1980
1234 Moon blv.
M
46,000
Project1
P0001
50
Lee
Navathe
672-23-9375
12/22/1980
1234 Moon blv.
M
46,000
Project15
P00015
139
Lee
Navathe
672-23-9375
12/22/1980
1234 Moon blv.
M
46,000
Project7
P0007
21
Fname
Lname
SSN
Bdate
Address
Sex
Salary
Pname
Pnumber
Hours
Jason
Smith
123-45-6789
02/12/1978
569 Main st.
M
60,000
Project1
P0001
45
Jason
Smith
123-45-6789
02/12/1978
569 Main st.
M
60,000
Project3
P0003
67
Mark
Johnson
341-04-8745
06/30/1956
876 Oak Ave.
F
89,000
Project7
P0007
12
Lee
Navathe
672-23-9375
12/22/1980
1234 Moon blv.
M
46,000
Project1
P0001
50
Lee
Navathe
672-23-9375
12/22/1980
1234 Moon blv.
M
46,000
Project15
P00015
139
Lee
Navathe
672-23-9375
12/22/1980
1234 Moon blv.
M
46,000
Project7
P0007
21
Explanation / Answer
In database Normalization is nothing but a database schema design process, by modifiying existing schema to minimize anomolies and dependency of data.Generally Normalization devides large tables into smaller schemas and establishes relationships among them to increases effectiveness.
First Normal Form (1NF):
As per first normal form,each and every attribute in a table must be unique. That means there should no duplication.
So that as per given information the schema is already in 1NF.
Fname
Lname
SSN
Bdate
Address
Sex
Salary
Pname
Pnumber
Hours
Jason
Smith
123-45-6789
02/12/1978
569 Main st.
M
60,000
Project1
P0001
45
Jason
Smith
123-45-6789
02/12/1978
569 Main st.
M
60,000
Project3
P0003
67
Mark
Johnson
341-04-8745
06/30/1956
876 Oak Ave.
F
89,000
Project7
P0007
12
Lee
Navathe
672-23-9375
12/22/1980
1234 Moon blv.
M
46,000
Project1
P0001
50
Lee
Navathe
672-23-9375
12/22/1980
1234 Moon blv.
M
46,000
Project15
P00015
139
Lee
Navathe
672-23-9375
12/22/1980
1234 Moon blv.
M
46,000
Project7
P0007
21
Second Normal Form (2NF):
As per second normal form,schema should be in 1NF and attributes within that table should depend individually on primary key.
As per given data {Pname} is not solely dependent on primary key{SSN}, Here i'm considering SSN as primary key, because two persons may contain same name but they are definitely unique by SSN. By applying 2NF the tables sould be as follows:
Here I have normalized the given table by avoiding partial dependencies. Pname is dependent on Pnumber. So that I have devided those attributes as separate tables and to establish relationship between two tables, using pnumber as foreign key in 1st table.
Third Normal Form (3NF):
As per 3NF, schema should be in 2NF and there should no trivial dependencies.
After normalizing given table to 2NF there are no trivial dependencies.
The above scema is in 3NF.
Fname
Lname
SSN
Bdate
Address
Sex
Salary
Pname
Pnumber
Hours
Jason
Smith
123-45-6789
02/12/1978
569 Main st.
M
60,000
Project1
P0001
45
Jason
Smith
123-45-6789
02/12/1978
569 Main st.
M
60,000
Project3
P0003
67
Mark
Johnson
341-04-8745
06/30/1956
876 Oak Ave.
F
89,000
Project7
P0007
12
Lee
Navathe
672-23-9375
12/22/1980
1234 Moon blv.
M
46,000
Project1
P0001
50
Lee
Navathe
672-23-9375
12/22/1980
1234 Moon blv.
M
46,000
Project15
P00015
139
Lee
Navathe
672-23-9375
12/22/1980
1234 Moon blv.
M
46,000
Project7
P0007
21
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.