Normalization - Solved exercises Home

Set of solved exercises in Normalization / Normalization Solved Examples / How to find candidate keys, and primary keys in database? / Sets of examples to find the keys of a tables / Process of finding Key in a database - Examples

INSERT, DELETE, MODIFY Anomalies Identification

• Identify the anomalies present in the given relational table

Primary key / Candidate key identification

• SET 1 - What functional dependencies are hold?

• SET 2 - What functional dependencies are hold?

• Find all the candidate keys 

• Find all the candidate keys - One more method - Solved exercise 

• Find all the candidate keys - Exercise

   

 Primary key / Candidate key identification

• SET 1 - Finding key of a given relation

• SET 2 - Finding key of a given relation

• SET 3 - Finding key of a given relation

• SET 4 - Finding key of a given relation

• SET 5 - Finding key of a given relation

• SET 6 - Finding key of a given relation

• SET 7 - Finding key of a given relation

• SET 8 - Finding key of a given relation

  ads.click()
  Normalization Solved Exercises

• SET 9 - Finding key of a given relation

• SET 10 - Finding key of a given relation

• SET 11 - Finding all candidate keys of a table

  Normalization

• SET 1 - Normalizing upto 3NF - User table

• SET 2 -Normalizing upto BCNF

• SET 3 -Normalizing upto BCNF 2

• SET 4 -Normalizing upto BCNF 3

• SET 5 -Normalizing upto BCNF 4

• SET 6 -Normalizing upto BCNF 5 

• SET 7 - Is this relation in 2NF?

• Exercise 8 - Normalize the table to 3NF

• Exercise 9 - Normalize the table to 3NF

• Exercise 10 - Normalize the table to BCNF

• Exercise 11 - Normalize the table to BCNF 

• Exercise 12 - Normalize the table to 3NF 

Find the functional dependencies that violate a normal form

• Exercise 1 - FDs that violate BCNF 

• Exercise 2 - Find FDs, Keys, and normalize to 3NF

Normalization Solved Questions

• Solved questions - Simple

Decomposition - Lossy or lossless

• Decomposition exercise 1 
• Decomposition exercise 2
• Decomposition exercise 3
•  

Equivalent Functional Dependencies

• Find whether sets of FDs are equivalent or not 01

• Find whether sets of FDs are equivalent or not 02

• Find whether sets of FDs are equivalent or not 03

normalization solved exercises

how to normalize a relation to second normal form (2nf)
how to normalize a relation to third normal form (3nf)
how to normalize a relation to Boyce-code normal form (BCNF)
find minimal cover
find canonical cover
lossless and lossy join decomposition
find candidate keys and super keys
what is candidate key
find functional dependencies
transitive functional dependency
armstrong's axioms

Canonical Cover in Database with Simple Examples

Canonical Cover Explained / Canonical Cover in Database with Simple Examples / Minimal Cover with Examples / What is Canonical Cover? / What is Minimal Cover?

Canonical Cover

Definition:

A Canonical Cover for a set of functional dependencies F is another set of functional dependencies Fc such that all the functional dependencies in F logically imply all the functional dependencies in Fc and vice versa. We insist the Fc to meet the following requirements;

1. F should logically imply all FDs in Fc, [F = Fc]

2. Fc should logically imply all FDs in F,

3. Functional dependencies of Fc should not contain any Extraneous attribute. (Refer for Extraneous attributes)

4. The left side of all the functional dependencies in Fc should be unique.

Actually, a Canonical cover Fc is a minimal set of functional dependencies that is equivalent to F, and have no redundant functional dependencies or redundant attributes as part of functional dependencies.

In other words, every functional dependency of Fc is very much needed and it is as small as possible when compared to the size of F.

Canonical Cover Algorithm:

ALGORITHM CanonicalCover(FD set F)

BEGIN

          REPEAT UNTIL STABLE

                   1. Wherever possible, apply UNION rule from Armstrong’s Axioms

                             (e.g., A → BC, A → CD becomes A → BCD)

                   2. Remove “extraneous attributes”, if any, from every FD

(e.g., AB→C, A→B becomes A→B, B→C i.e., A is extraneous in AB→C)

END

Example:

Redundant Functional Dependency:

A → C is redundant in {A → B, B → C, A → C}

Observe from the given set of functional dependencies, A → B and B → C will automatically include A → C as a result of Transitivity. Hence, we do not need to check whether C is uniquely determined by A or not [in other words, A uniquely determines C or not]. Hence, A → C is redundant.

And, the set of functional dependencies {A → B, B → C} is semantically equivalent to given set of functional dependencies {A → B, B → C, A → C}.

Redundant Attributes or Redundant Part of Set of Attributes:

Attribute C is redundant on the Right Hand Side (RHS) of FD A → CD in {A → B, B → C, A → CD}.

Here, C is already determined by B. Hence, we do not need to include in another FD to check the dependency. So, the given set of functional dependencies can be simplified as {A → B, B → C, A → D}. And, this is equivalent.

Attribute C is redundant on the Left Hand Side (LHS) of FD AC → D in {A → B, B → C, AC → D}.

Here, if we know A, intuitively we know C as well through Transitivity rule. Hence, A → D is suffice to represent. So, the given set of functional dependencies can be simplified as {A → B, B → C, A → D}. And, this is equivalent to the given set of FDs.

Finding Extraneous Attribute in DBMS

Finding Extraneous Attributes in DBMS / How to find extraneous attributes in the process of Normalization? / Finding Extraneous Attributes Examples / Extraneous attribute finding as part of finding Canonical cover

Extraneous Attribute

If we are able to remove an attribute from a functional dependency without changing the closure of the set of functional dependencies, that attribute is called as Extraneous Attribute.

[Dictionary meaning of ‘Extraneous’ is ‘irrelevant’, ‘inappropriate’, or ‘unconnected’]

Assume a set of functional dependencies F, and the closure of set of functional dependencies F⁺. Also, assume that we remove an attribute from any of the FDs under F and find the closure of new set of functional dependencies. Let us mention the new closure of set of functional dependencies as F1⁺.  If F⁺ equals the newly constituted closure F1⁺, then the attribute which has been removed is called as Extraneous Attribute. In other words, that attribute does not violate any of the functional dependencies.

Example 1:

Let us consider a relation R with schema R(A, B, C) and set of functional dependencies F = { AB → C, A → C }. The closure for F is F⁺ = { AB → C, A → C }.

In AB → C, B is extraneous attribute. The reason is, there is another FD A → C, which means when A alone can determine C, the use of B is unnecessary (redundant).

Now, we can find the closure for the new set of functional dependencies, which is same as F⁺. Hence, we can declare that B is extraneous.

Example 2:

Let us consider a relation R with schema R(A, B, C, D) and a set of functional dependencies F = { A → BC, B → C, AB → D }. What extraneous attributes are present in FDs of F?

C is extraneous in the RHS (Right Hand Side) of A → BC. Because, A can determine B (A → BC), B can determine C (B → C). Hence, A can determine C also (Transitivity rule). Hence, it is inappropriate to repeat or check an attribute many times.

B is extraneous in the LHS of AB → D. The reason is, from A → BC, it is clear that A determines B. it would indirectly mean that if you know A and B then you know D also.

Formal definition of Extraneous Attribute

In a set of functional dependencies F, consider a functional dependency α → β.

Attribute A is extraneous in α, if A ∈ α, and F logically implies (F − {α → β}) ∪ {(α − A) → β}.

Attribute A is extraneous in β, if A ∈ β, and the set of functional dependencies

(F − {α →β}) ∪ {α → (β − A)} logically implies F.

For example, suppose F contains AB → CD, A → E, and E → C. To check if C is extraneous in AB → CD, we compute the attribute closure of AB under F’ = {AB → D, A → E, and E → C}. The closure is ABCDE, which includes CD, so we infer that C is extraneous.

[Source: Database System Concepts – Korth]

Go to How to find closure of set of functional dependencies? page

Go to Normalization - Solved Exercises page

External source to find Extraneous attributes/ Canonical Cover

Go to Index Page

What is extraneous attributes?

Role of extraneous attributes in normalization process

How to find extraneous attributes in database normalization

Why do we need to find extraneous attributes in finding closures?

Closure of functional dependencies and extraneous attributes

Relationship between extraneous attributes and canonical cover