Scribd
Upload
5 views9 pages

Dbms Normalization

The document discusses normalization in database management systems, focusing on 2NF, 3NF, and BCNF with solved examples and practice questions. It provides detailed solutions for various relations and functional dependencies to illustrate the normalization process. Additionally, it touches on conflict serializability and the use of precedence graphs for testing concurrent schedules.

Uploaded by

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

Dbms Normalization

The document discusses normalization in database management systems, focusing on 2NF, 3NF, and BCNF with solved examples and practice questions. It provides detailed solutions for various relations and functional dependencies to illustrate the normalization process. Additionally, it touches on conflict serializability and the use of precedence graphs for testing concurrent schedules.

Uploaded by

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

DBMS – Normalization & Conflict Serializability 1 of 9

Dr. Samaresh Mishra

Solved Examples of Normalization


(Additional Materials, as discussed in the Class)

2NF (2nd Normal Form)

Example-1
Given R (A, B, C, D) and F = {AB  CD, B  D}

 Here, AB is the key as ABCD.

 The FD {BD} is a pfd (partial functional dependency) as a key


attribute (i.e. prime attribute, ‘B’ determines a non-key attribute (non-prime
attribute, ‘D’)

Solution
DBMS – Normalization & Conflict Serializability 2 of 9
Dr. Samaresh Mishra

3NF (3rd Normal Form)


DBMS – Normalization & Conflict Serializability 3 of 9
Dr. Samaresh Mishra

Solution as per General Definition of 3NF


DBMS – Normalization & Conflict Serializability 4 of 9
Dr. Samaresh Mishra

BCNF
DBMS – Normalization & Conflict Serializability 5 of 9
Dr. Samaresh Mishra

Additional Practice Questions

1. Given a relation R( X, Y, Z) and Functional Dependency set FD = {X→Y, Y→Z},


determine whether the given R is in 3NF? If not convert it into 3 NF.

Solution

i. X is a candidate key.
ii. As YZ is a t.f.d. (transitive functional dependency), let us decompose R into
R1 & R2 as follow:
a. R1(Y, Z) with {YZ}. [Note: Remove Z from R]
b. R2(X, Y) with XY.

2. Given a Relation schema, (P, Q, R, S, T, U, V, W, X, Y) and a set of Functional


Dependencies {PQ → R, P → ST, Q → U, U → VW, and S → XY}, determine
whether the given R is in 3NF? If not convert it into 3 NF.

Solution

i. Key is {PQ}.
ii. Decompose R into as follow:
DBMS – Normalization & Conflict Serializability 6 of 9
Dr. Samaresh Mishra

3. Normalize the following relation LOTS into 2NF and 3NF normal forms.
LOTS (property-id, country-name, lot-number, area, price, tax-rate), with a set of
functional dependencies,
F={property-id→country-name, lot-number, area, price, tax-rate;
country-name, lot-number → property-id, area, price, tax-rate;
country-name → tax-rate;
area → price;}

We can rewrite the above as follow (NOTE: it is a suggestion, not mandatorily required)

Normalize the following relation LOTS into 2NF and 3NF normal forms.
LOTS (PID, CN, LN, AR, PR, TR),
F = {PID → CN, LN, AR, PR, TR;
CN, LN → PID, AR, PR, TR;
CN → TR;
AR → PR;}
Solution:
Step-1:

Step-2:
DBMS – Normalization & Conflict Serializability 7 of 9
Dr. Samaresh Mishra

Practice Question:

Is the relation schema R (A,B,C) with F={AB → C, C → B} in 3NF?

Testing Conflict Serializability of a


concurrent schedule, using precedence
graph (Solved Examples)
DBMS – Normalization & Conflict Serializability 8 of 9
Dr. Samaresh Mishra
DBMS – Normalization & Conflict Serializability 9 of 9
Dr. Samaresh Mishra

You might also like