Question

In each of these exercises, consider the relation, CKs, and FDs. Determine if the relation is in BCNF, and if not in BCNF give a non-loss decomposition into BCNF relations. The last 5 questions are abstract and give no context for the relation nor attributes.

1. Consider a relation Player which has information about players for some sports league. Player has attributes id, first, last, gender. id is the only CK and the FDs are:
idfirst
idlast
idgender

Player-sample data

ID	First	Last	gender
1	Jim	Jones	Male
2	Betty	Smith	Female
3	Jim	Smit	Male
4	Lee	Mann	Male
5	Samantha	McDonald	Female

2.Consider a relation Employee which has information about employees in some company. The 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 – sample data

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

• Complete the exercise to achieve 3NF. Describe in your own words these normalization concepts into your saved document file.
  • You may display this information in table format (as posted in the exercise) or use DBDL design format.

Answer 1

Relation A -> B is said to be in BCNF, if and only if A is the primary key/candidate key for every FD(functional dependency) in the given relation (A -> B)

(1)

Here as given id is the only CK(candidate key)
The FDs are:-
-"id -> First" is in BCNF (id is Candidate key)
-"id -> Last" is in BCNF (id is Candidate key)
-"id -> Gender" is in BCNF (id is Candidate key)

Since all the FDs (functional dependency) is in BCNF, the given relation is in BCNF.
   Here id determines the First and First determines gender, so it is not in 3NF because of transitive FD

(2)

The given CKs are id and sin and here each CK have unique values , so any of both can act as primary key
And the FDs are :-
-"id -> first" is in BCNF, (id is a candidate key)
-"id -> last" is in BCNF, (id is a candidate key)
-"sin -> first" is in BCNF, (id is a candidate key)
-"sin -> last" is in BCNF, (id is a candidate key)
-"id -> sin" is in BCNF, (id is a candidate key)
-"sin -> id" is in BCNF, (id is a candidate key)

Since all the FDs (functional dependency) is in BCNF, the given relation is in BCNF.
     Here it can be said that it is in 3NF because all other attributes are dependent on primary key (id or sin)