Question

Consider a relation Employee which has information about employees in some company. Employee has attributes id, first, last, sin (social insurance number) where id and sin are the only CKs, and the FDs are:
idfirst
idlast
sinfirst
sinlast
idsin
sinid

Employee Table

ID	First	Last	SIN
1	Jim	Jones	111222333
2	Betty	Smith	333333333
3	Jim	Smith	456789012
4	Lee	Mann	123456789
5	Samantha	McDonald	987654321

Consider a relation Employee which has information about employees in some company.

• You need to expand the database fields into tables from 1NF --> 2NF; 2NF --> 3NF.
• What is the name of the tables?
• What are the PK field names?
• What are the field names?
• Solution should be displayed as:  TableName(PKname, fieldname1, fieldname2, etc).
• Additional information is required to expand upon the CKs and FDs.

Answer 1

The table is already in first normal form as all the values in domains are single attirbute values.

coming to 2nd NF. There should not be any partial functional dependencies in table for 2nd NF. But here we have two primary key which is violation of rules. So dividing it into two tables.

EMP1(ID, FIRST,LAST) ID ---> primary key

EMP2(SIN,ID) SIN-->Primary key, ID---> foreign key

Now the tables are in 2nd NF.

Now checking whether the tables are in 3rd NF or not.For 3rd NF , there should not be any transitive dependency like A-->B , B-->C and A-->C(this should not be in tables). which we don't have.

So tables are already in 3NF.

final tables

EMP1(ID, FIRST,LAST) ID ---> primary key

EMP2(SIN,ID) SIN-->Primary key, ID---> foreign key

DON'T FORGET TO HIT LIKE.

THANKS BY HEART.

COMMENT IF ANY CONFUSION.