filtertips
filtertips

Reputation: 903

Data normalisation into third normal form

I have done data normalization on dummy data and would like to know if I did it correctly. If it is done correctly, I would also like to ask two things below, because it is about 3NF.

1NF: This table should be 1NF.

userId userName keyNumber keyCode accessGroup doors
1 Alice 1 1 1 1
1 Alice 1 1 1 2
1 Alice 1 1 1 3
2 Bob 2 2 2 1
2 Bob 2 2 2 2
3 Alice 3 3 2 1
3 Alice 3 3 2 2

2NF: I selected composite key (userID and Doors) as they represent minimal candidate key and got three tables applying FD rule.

Primary_key: userID

userId userName keyNumber keyCode accessGroup

Primary_key: doors

doors

Primary_key: (userid, doors)

userId doors

3NF: Applying the rule of transitive dependency on 1st table in 2NF, I got out 4 tables (showing only first two, because the last two remain unchanged)

Primary_key: userID

userId userName

Primary_key: keyNumber

keyNumber keyCode accessGroup
1 1 1
2 2 2
3 3 2

Questions:

  1. Is this database normalisation correct? If not could you point me where I did mistake?
  2. If answer on first question is True: Should the last table in 3NF be transformed into two tables, given it is not in correct Third normal form. Two non-key atributes have FD keycode -> accessGroup

Upvotes: 1

Views: 188

Answers (0)

Related Questions