Scribd
Upload
184 views3 pages

Database Normalization Steps

The document discusses normalization of relations into higher normal forms by decomposing relations based on their functional dependencies. It provides examples of relations along with their functional dependencies and asks to identify candidate keys, determine the normal form, and normalize the relations into higher normal forms through decomposition. Questions include examples of normalization from 1NF to 2NF and 3NF, identifying anomalies during updates, and determining equivalence of sets of functional dependencies.

Uploaded by

Pragati Shukla
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
184 views3 pages

Database Normalization Steps

The document discusses normalization of relations into higher normal forms by decomposing relations based on their functional dependencies. It provides examples of relations along with their functional dependencies and asks to identify candidate keys, determine the normal form, and normalize the relations into higher normal forms through decomposition. Questions include examples of normalization from 1NF to 2NF and 3NF, identifying anomalies during updates, and determining equivalence of sets of functional dependencies.

Uploaded by

Pragati Shukla
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
You are on page 1/ 3

Normalization Assignment

1.What normal form is the following relation in:

STORE_ITEM (SKU, PromotionID, Vendor, Style, Price)

SKU, PromotionID -> Vendor, Style, Price


SKU -> Vendor, Style
Normalize the above relation into the next higher normal form:

2. What normal form is the following relation in

STUFF (H, I, J, K, L, M, N, O)

H, I -> J, K, L
J -> M
K -> N
L -> O
3. Suppose you are given a relation R = (A,B,C,D,E) with the following functional
dependencies: {CE -> D,D-> B,C -> A}.
a. Find all candidate keys.
b. Identify the best normal form that R satisfies (1NF, 2NF, 3NF).
c. At each step, identify a new relation, decompose and re-compute the keys and the normal forms they
satisfy.

4. Consider the following relation:


Shipping (ShipName, ShipType, VoyageID, Cargo, Port, Date)
Hint: Date is the date the ship arrives in the given Port

With the functional dependencies:


ShipName -> ShipType
VoyageID -> ShipName, Cargo
ShipName, Date -> VoyageID, Port

(a) Identify the candidate keys.


(b) Normalize to 2NF
(c) Normalize to 3NF

5. Given the following relation and example data:

PartNu Description Supplier SupplierAddress Price

10010 20 GB Disk Seagate Cuppertino, CA $100

10010 20 GB Disk IBM Armonk, NY $90

10220 256 MB RAM card Kensington San Mateo, CA $220

10220 256 MB RAM card IBM Armonk, NY $290


10220 256 MB RAM card Sun Microsystems Palo Alto, CA $310

10440 17" LCD Monitor IBM Armonk, NY $2,100

List the functinoal dependencies and Normalize this relation into 3NF.

6. Given the following relation and example data:

(a) The table shown in Figure above is susceptible to update anomalies. Provide examples of
insertion, deletion, and modification anomalies.

(b) Describe and illustrate the process of normalizing the table shown in Figure 1 to 3NF. State
any assumptions you make about the data shown in this table.

(c) Assumptions include that a patient is registered at only one surgery and he/she may have
more than one appointment on a given day. All the schedules have been fixed for the whole
days and week.

7. Q6a)Give a set of FD’s for the relation schema R(A,B,C,D) with primary key as AB under
which R is in 2NF but not in 3NF.

b) Consider the relation schema R(A,B,C) which has the FD B C. If A is the candidate
key for R, is it possible for R to be in BCNF? If so in what condition ,if not, why?

c) Consider the relation schema R(A,B,C,D,E) which has the FD A B, BC E, E


DA. List the key of R, What normal form is R in?

8 Consider the following relation: Car_Sale( Car#, Date_sold, Salesman#, commission%,


dis_amt)

Assume that a car may be sold by multiple salesmen. Additional dependencies are:
Date_sold determines dis_amt, and salsman# determines commission%.

Find the primary key of the above relation and normalize the same till 3NF.

9. Consider the relation for published books:

Book( Book_title,, Authorname, book_type, List_price, Author_affil, Publisher)

Author_affil refers to the affiliation of the author, suppose the following dependencies exist:

Book_title determines publisher, book_type

Book_type determines Listprice

Authorname determines Author_affil

a) What normal form is the relation in? Why

b) Apply normalization upto 3NF.

10. Given R( X, Y, Z ) and a set of FD’s P & Q such that:

P = {X →Y, Y → Z, Z →X } & Q = { X → YZ, Y →X, Z → X }

Are P and Q equivalent?

You might also like