FOR FREE MATERIALS

Normalization: GATE and NET questions

Q: GATE-CS-2003 | Question 85

Consider the following functional dependencies in a database:

Date_of_Birth → Age

Age → Eligibility

Name → Roll_number

Roll_number → Name

Course_number → Course_name

Course_number → Instructor

(Roll_number, Course_number) → Grade

The relation (Roll_number, Name, Date_of_birth, Age) is:
(A) In second normal form but not in third normal form
(B) In third normal form but not in BCNF
(C) In BCNF
(D) None of the above

Before seeing the examples please follow previous chapters: 

Find Super and Candidate Key from Attribute Closer,  2NF, 3NF, BCNF, Normalization Examples.

Solution:

Those FD related to attribute which is not in relation just filter or reject those FDs.

Rejected FDs:

Age → Eligibility (Eligibility not present in relation)

Cno → Cname, Cname → instruction (Cno, Cname, instruction not present)

Actual FDs:

But sometime before discarding FDs, we can split it to get valid FD like:

In this case, we split rollno → Name age and rollno → Eligibility then discard it only rollno → Eligibility.

From closer of attribute, we will get candidate key and prime attribute

Candidate key (CK) = {(Roll_no Date_of_Birth), (Name Date_of_Birth)}

Prime Attributes = {Roll no, DOB, Name}

So, Option (d) is the correct answer, relation (table) is in 1NF.

Q: GATE CS 2004 Question-90

The relation scheme Student Performance (name, courseNo, rollNo, grade) has the following functional dependencies:

name, courseNo → grade

rollNo, courseNo → grade

name → rollNo

rollNo → name 

The highest normal form of this relation scheme is
(A) 2 NF
(B) 3 NF
(C) BCNF
(D) 4NF

Before seeing the examples please follow previous chapters: 

Find Super and Candidate Key from Attribute Closer,  2NF, 3NF, BCNF, Normalization Examples.

Solution:

Closer of attribute:

So, Candidate Key = [{rno, Cno}, {name, Cno}]

Prime Attribute = {name, Cno, rno}

So, the correct option is (b) 3NF

Q: UGC NET CS 2017 –NOV-Question 10. 

Consider a relation R (A, B, C, D, E, F, G, H), where each attribute is atomic, and the following functional dependencies exist. 

CH → G, A → BC, B → CFH, E → A, F → EG

The relation R is

(a) In 1NF but not in 2NF

(b) In 2NF but not in 3NF

(c) In 3NF but not in BCNF

(d) In BCNF

Before seeing the examples please follow previous chapters: 

Find Super and Candidate Key from Attribute Closer,  2NF, 3NF, BCNF, Normalization Examples.

Solution:

R (A, B, C, D, E, F, G, H)

F = {CH → G, A → BC, B → CFH, E → A, F → EG}

So, Candidate Key= {AD,

Prime Attributes= {A, D,

Now we will search for Prime Attribute at RHS of Functional Dependency, then we will replace it with its LHS. So, we get E → A

So, Candidate Key = {AD, ED

Prime Attributes = {A, D, E,

Again we get, F → EG (F → E  and F → G) then replace E with F.   

∴ Candidate Key = {AD, ED, FD,

Prime Attributes = {A, D, E, F,

Again we get, B → CFH   (B → C, B → F, and B → H) then replace F by B.   

So,

So,

Candidate Key= {AD, ED, FD, BD}

Prime Attributes = {A, D, E, F, B}

Non-Prime Attributes = {C, G, H}

So, we can say the relation R is in 1NF, as in the question, it is said that each attribute to atomic.

Check for 2NF

(R is in 2NF) ⇔ [(R is in 1NF) + (not contain any partial dependency)]

(X → Y is a PD) ⇔ ((X ⊂ C.K) ∧ (Y : NPA))

(X → Y is FD) ⇔ ((X ⊄ C.K) ∨ (Y : PA))

Here,                      

FD1: CH → G is in 2NF, as CH is not a subset of candidate key (CH ⊄ Candidate Key)

FD2: A → BC is not in 2NF, if we split the FD we will get:

A → B and A → C

A → B (not partial dependency) is in 2NF because A is a subset of candidate key AD but B is a prime attribute. 

A → C (partial dependency) is not in 2NF  because A is a subset of candidate key AD and C is a non-prime attribute.

So, A → BC is not in 2NF

We need not check further, as we already get an FD which is a partial dependency.

So, relation R is in 1NF, not in 2NF

∴ Answer: Option (a) is the correct answer. 

Q: Gate CS 2008 and UGC NET 2014 –July-paper-3 Question 22

Consider the following relational schemas for a library database:

Book (title, Author, Catalog no, publisher, year, price)

Collection (title, Author, Catalog no)

The following functional dependencies: 

title, Author → Catalog no

Catalog no → title Author publisher year

publisher title year → price 

Assume (Author, Title) is the key for both schemas.

Which one of the following is true?

(a) Both Book and Collection are in BCNF.

(b) Both Book and Collection are in 3NF.

(c) Book is in 2NF and Collection in 3NF.

(d) Both Book and Collection are in 2NF.

Before seeing the examples please follow previous chapters: 

Find Super and Candidate Key from Attribute Closer,  2NF, 3NF, BCNF, Normalization Examples.

Solution:

FDs are

i) Title, Author → Catalog no.

ii) Catalog no. → Title, Author, Publisher, Year.

iii) Publisher, Title, Year → Price.

Relation Book:

So, Candidate Key = {catalog no, title Author} and Prime Attribute = {catalog no, title, Author}

title, Author → Catalog no    –  BCNF because title, Author = Super key

Catalog no → title Author publisher year – BCNF because Catalog no = Super key

publisher title year → price  – not BCNF, not 3NF, it is 2NF

publisher title year ≠ Super key and price = Non-prime attribute.

So, publisher title year → price is a transitive dependency and it is not in 3NF.

∴ Book is in 2NF

Relation Collection:

In the case of relation, collection candidate key and prime attributes are the same as Book. 

So, Candidate Key = {catalog no, title Author} and Prime Attribute = {catalog no, title, Author}

We check the normal form in a reverse way:

Collection

title, Author → Catalog no  - BCNF because title, Author = Super key

Catalog no → title Author  - BCNF because Catalog no =super key

∴ Collection is in BCNF

Answer: 

Option (c) 

Book is in 2NF and Collection is in 3NF.