Find the closure of set of attributes

Find the closure of set of attributes, Check whether the given attributes forms candidate keys, How to find closure of attributes? Find closure - solved exercise, Check for unique determination of an attribute by the other attribute

Question:

2. Let F = {A → B, A → C, BC → D}. Can A determine D uniquely?

Solution:

To find this, we need to find the closure of A.

Closure: In simple terms, if you know an attribute (or set of attributes) in a relation R, then what other attribute (or set of attributes) you would determine uniquely is called the closure. We normally find the closure of left hand side (LHS) attributes of the functional dependencies of relation R. Closure is used to find the candidate keys of the relation. Refer here to know more about attribute closure.

To find A⁺:

result := A

result := AB from the functional dependency (FD) A → B

result := ABC from the FD A → C

result := ABCD from the FD BC → D

The closure of attribute A is ABCD. And the closure includes the attribute D in it. Hence, values of attribute A can uniquely determine the values of attribute D.

****************

Go back to Question/QUIZ page

Go back to Online Quizzes home page

Normalize the table into 3NF

Normalize the given table into third normal form, 3nf example, 3nf solved exercise, How to normalize a relational schema into 3NF? Third Normal Form Normalization Process, Normalize to 3nf

Normalization Exercises

Consider the relation PLAYER with relational schema PLAYER (Player-no, Player-name, Team, Team-color, Coach-no, Coach-name, Player-position, Team-captain) and set of functional dependencies as follows;

F = {Player-no → Player-name, Player-no → Player-position, Player-no → Team, Coach-no → Coach-name, Team → Team-color, Team → Coach-no, Team → Team-captain}

Answer the questions given below;

a) Is PLAYER in 2NF? If not, convert into 2NF.

b) Is PLAYER in 3NF? If not, convert into 3NF.

a) Is PLAYER in 2NF?

To answer this question, we need to find the key for PLAYER. (Refer here - How to find key?)

Let us find the closure for all the left hand side attributes of all the FDs of F.

(Player-no)⁺ = Player-no, Player-name, Player-position, Team, Team-color, Coach-no, Team-captain, Coach-name.

(Team)⁺ = Team, Team-color, Team-captain, Coach-no, Coach-name

(Coach-no)⁺ = Coach-no, Coach-name

When we find closure, only Player-no can uniquely determine all the attributes of PLAYER. Hence, Player-no is the only candidate key.

Question to think: (Team, Player-no)+ = Player-no, Player-name, Player-position, Team, Team-color, Coach-no, Team-captain, Coach-name. (Team, Player-no) combination, as given above can uniquely determine all the attributes of PLAYER. Hence, (Team, Player-no) can be a candidate-key? NO. Reason - The proper subset of (Team, Player-no) is already a candidate key (Player-no).

As the key (Player-no) is single and simple attribute, there is no possibilities for partial-key dependencies. Hence, PLAYER is in 2NF.

b) Is PLAYER is in 3NF?

To answer this question, we need to check for non-key dependencies or transitive dependencies. That is, we have to look for dependencies like the one follows;

Non-key attribute(s) → Non-key attribute(s)

From the given set of functional dependencies F, we could derive the following non-key dependencies;

Team → Team, Team-color, Team-captain, Coach-no, Coach-name

Coach-no → Coach-no, Coach-name

Hence, PLAYER is not in 3NF.

Solution:

Decompose PLAYER into more tables based on the non-key dependencies. Then we shall get the tables as follows;

PLAYER (Player-no, Player-name, Player-position, Team)

TEAM (Team, Team-color, Team-captain, Coach-no, Coach-name)

The key for PLAYER is Player-no, and all the others are non-key attributes. Hence, PLAYER is in 2NF (no partial dependencies) and 3NF (no transitive dependencies).

The key for TEAM is Team. All the other attributes are non-key attributes and depends on Team-no. Hence, TEAM is in 2NF. TEAM has following transitive dependency;

Team → Coach-no → Coach-name.

Hence, TEAM is not in 3NF. To convert, decompose TEAM as follows;

TEAM (Team, Team-color, Team-captain, Coach-no)

COACH (Coach-no, Coach-name)

Now, TEAM and COACH are both in 2NF and 3NF.

Final set of decomposed tables that are in 3NF are;

PLAYER (Player-no, Player-name, Player-position, Team)

TEAM (Team, Team-color, Team-captain, Coach-no)

COACH (Coach-no, Coach-name)

********************

Go back to Normalization – solved exercises page.

Determination of Normal Forms

Determine whether the given table is in 1NF, 2NF, 3NF etc., how to determine the normal form of a table? Normalization YES/NO questions, Normalization questions answered

Normalization Exercises

Consider a relation Teacher with the attributes Teacher_Name, Gender, DOB, Subject_Handles, School_Name, and School_Location. Assume that Teacher is in 1NF. Answer the following questions;

a. Assume that there are set of functional dependencies in Teacher. If (Teacher_Name, DOB) is the key for Teacher, is Teacher in 2NF?

CANNOT DETERMINE.

A table is in 2NF, if there is no partial key dependency. Here, the key for Teacher is a composite primary key. So, there are possibilities for partial key dependencies.

We cannot determine whether this table is in 2NF or not as the given information is not enough.

b. If Teacher_Name is the key for Teacher, is Teacher in 2NF?

YES.

The key for Teacher is formed by a single column, ie., Teacher_Name. If all the attributes of Teacher can be determined uniquely by Teacher_Name, then only Teacher_Name attribute forms the key. And, there are no possibilities for partial key dependencies. Hence, Teacher is in 2NF.

c. (Teacher_Name, DOB) is the key for Teacher, all the other attributes depend on the whole key, and only other FD is "School_Name determines the School_Location uniquely". Is Teacher in 2NF?

YES.

As per the given information, there is no partial key dependency present. Hence, the table is in 2NF.

d. (Teacher_Name, DOB) combination is the key for Teacher, all the other attributes depend on the whole key, and only other FD is "School_Name determines the School_Location uniquely". Is Teacher in 3NF?

NO.

For a table in 3NF, it should be in 2NF and non-key attribute dependencies (transitive dependencies) must not present.

From the given information, it is clear that the table is in 2NF. Teacher is not in 3NF because of the non-key dependency School_Name → School_Location.

e. Assume a set of FDs in Teacher, and it is found that Teacher_Name is the key. If Teacher_Name is the key for Teacher, is Teacher in 3NF?

CANNOT DETERMINE.

Only information given here is the key for Teacher. If we are given the set of functional dependencies, then only we can decide. In this case we cannot determine.

*********************

Go to Normalization Solved Exercises and Questions page