Normalization Using the functional dependencies convert each of the following in

Question

Normalization

Using the functional dependencies convert each of the following into 3NF using the steps discussed in class. Make sure you show each normal form (1NF, 2NF and 3NF) and explain why they are in that form. You may add fields if they will clarify or simplify your relations, but you must include notes on what they are and why you chose to add them.

1. horse(horseId, horseName,dob,sire,dam,

(raceId,raceName,postPosition,finalStanding,dollars,(winPurse,placePurse,showPurse))

FDs

horseId horseName,dob,sire,dam   (sire and dam are also horseIds) raceId raceName, winPurse, placePurse,showPurse horseId, raceId postPosition,finalStanding,dollars

1NF=

2NF

c)3NF

Explanation / Answer

There are 3 types of anomalies that occur when the database is not normalized. These are

Normalization is a database design process and technique which creates table and store data in a manner that reduces redundancy and dependency of data. It divides larger tables to smaller tables and links them using primary-foreign key relationship.

Suppose we have a horse database, which contains and stores horses information. In horse table, there are 5 attribute horseid, horseName, dob, sire, dam.

The table looks like.

Update table with new attributes:

First normal form (1NF)

As per the rule of first normal form, an attribute (column) of a table cannot hold multiple values. It should hold only atomic values

In the above table case, a horse may have more than one race id because each race has their own unique id.

for example, the horse has 2 different race id so the company stored them in the same field but as per 1NF, an attribute can have only one value.

Second normal form (2NF)

A table is said to be in 2NF if both the following conditions hold:

Both tables have a relationship between them by using the composite key and primary-foreign key relationship by using both table horse id attribute.

Third Normal form (3NF)

A table design is said to be in 3NF if both the following conditions hold:

example tables:

Here

Super keys: {horseid}, {horseid, horseName,dob,sire,dam}, {horseid, winPurse, placePurse,showPurse}
Candidate Keys: {horseid}

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.