Scribd
Upload
14 views2 pages

Module 3

The document outlines various exercises related to database normalization, including examples of tables in different normal forms (1NF, 2NF, 3NF, BCNF) using a student's database and a refrigerator relation. It also discusses the need for higher normal forms and evaluates functional dependencies to determine candidate keys and the normal form status of given relations. Additionally, it includes tasks for decomposing relations and checking for lossless and dependency-preserving decompositions.

Uploaded by

nithyarevathi
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
14 views2 pages

Module 3

The document outlines various exercises related to database normalization, including examples of tables in different normal forms (1NF, 2NF, 3NF, BCNF) using a student's database and a refrigerator relation. It also discusses the need for higher normal forms and evaluates functional dependencies to determine candidate keys and the normal form status of given relations. Additionally, it includes tasks for decomposing relations and checking for lossless and dependency-preserving decompositions.

Uploaded by

nithyarevathi
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Module 3

1. (i). Give an example of a table which is not in First Normal Form. Your table
must be formed from a student’s database with attributes sid, sname, sex,
major, gpa, activity and fee.

(ii) Give an example of a table which in First Normal Form but not in Second
Normal Form. Use the same student’s database. Provide your own keys and
constraints. Convert your table into Second normal form relations.

(iii) Give an example scenario in which this student database is in Second


Normal form but not in Third normal form. Convert your table into third
normal form relations. Provide the keys and constraints used by you.

(iv) Give an instance of student’s database which is in Third Normal Form


but not in Boyce Codd normal form.

(v) What is the need for higher normal forms namely Fourth normal form,
fifth normal form and Domain/Key normal form. Which is the highest normal
form and why?

2. Consider the relation REFRIG( Model#, Year, Price, Manufacturing-plant,


Colour), which is abbreviated as REFRIG(M, Y, P, Mp, C) , and with the
following set F of functional dependencies:
F = { MMp, MYP, MpC}
(i) Evaluate each of the following as a candidate key for REFRIG, giving
reasons why it can or cannot be a key: M, MY, MC.
(ii) Based on the above key determination, state whether the relation REFRIG
is in 3NF and provide proper reasons.
(iii) Based on the above key determination, state whether the relation REFRIG
is in BC/NF and provide proper reasons.
(iv)Consider the decomposition of REFRIG into D = (R1(M, Y, P) and R2(M,
Mp, C). Is this decomposition loseless? Show why.
(v) Check whether the decomposition is dependency preserving.
3. Consider the relation R= (ABCDEFG) with the functional dependencies
namely F= {AB → CD, AF → D, DE → F, C → G, F → E and G → A}. The values
of R are atomic.

(i) Find the candidate keys of R.

(ii) Apply the closure computation algorithm and find the closure of F.

(iii) Apply attribute closure algorithm and find the minimal cover of F.

(iv) Check whether the given R is in 1NF? If not convert it into 1 NF


relations. Also check whether the given R is in 2NF? If not convert it
into 2 NF relations.

(v) Check whether the given R is in 3 NF? If not convert it into 3 NF


relations. Also check whether R is in BC/NF. If not, convert R into
BC/NF relations.

4. Consider the universal relation R (A, B, C, D, E, F, G, H, I, J).

The set of functional Dependencies are FD = { AB  C, A  DE, BF,


FGH, DIJ }.
(i) What is the key for R?
(ii) Check whether it is in 2NF. If not, convert it into 2NF relations.
(iii) Check whether it is in 3NF. If not, convert it into 3NF relations
(iv) Find the closure of the set FD.
(v) Find the closure of the attribute A.
2.

You might also like