Question

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

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.

Homework Answers

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.

Know the answer?
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Not the answer you're looking for?
Ask your own homework help question
Similar Questions
ADVERTISEMENT
Need Online Homework Help?

Get Answers For Free
Most questions answered within 1 hours.

Ask a Question
ADVERTISEMENT