Data Redundancy - Database Design - Lecture Slides, Slides of Database Management Systems (DBMS)

Download Data Redundancy - Database Design - Lecture Slides and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Chapter 8

Normal Forms Based on Functional

Dependencies

8.1 Normalization

Data redundancy and the consequent

modification (insertion, deletion, and update)

anomalies can be traced to “undesirable”

functional dependencies in a relation schema

Desirable FD: is any FD in a relation schema, R

where the determinant is a candidate key of R;

this will not cause data redundancy.

Undesirable FD: is where the determinant of an

FD in R is not a candidate key of R and this will

cause data redundancy.

Normalization: Update Anomaly

The same information can be expressed on multiple records; therefore updates to the table may result in logical inconsistencies. Example: each record in an "Employees' Skills" table might contain an Employee ID, Employee Address, and Skill; thus a change of address for a particular employee will potentially need to be applied to multiple records (one for each of his skills). If the update is not carried through successfully—if, that is, the employee's address is updated on some records but not others—then the table is left in an inconsistent state. Specifically, the table provides conflicting answers to the question of what this particular employee's address is. This phenomenon is known as an update anomaly.

An update anomaly. Employee 519 is shown as having different addresses on different records.

Normalization: Insertion Anomaly

There are circumstances in which certain facts cannot be recorded at all. For example, each record in a "Faculty and Their Courses" table might contain a Faculty ID, Faculty Name, Faculty Hire Date, and Course Code—thus we can record the details of any faculty member who teaches at least one course, but we cannot record the details of a newly-hired faculty member who has not yet been assigned to teach any courses. This phenomenon is known as an insertion anomaly.

An insertion anomaly. Until the new faculty member is assigned to teach at least one course, his details cannot be recorded.

Normalization (cont)

In order to eliminate this problem with undesirable FD

is to somehow render the undesirable FDs desirable

and the process of doing this is called normalization.

Normal Forms (NFs) provides a stepwise progression

towards the goal of a fully normalized relation

schema that is guaranteed to be free of data

redundancies that cause modification anomalies

from a functional dependency perspective.

Normalization (cont)

 A relation schema is said to be in a particular normal

form if it satisfies certain prescribed criteria; otherwise the relation is said to violate the normal form. The violation of each of these normal forms signals the presence of a specific type of “undesirable” FD.

 It is important to note that the normalization process is

anchored to the candidate key of a relation schema, R.

 We will use the primary key as the basis for evaluating

and normalizing a relation schema.

1NF Violation and Resolution Figure 8.1 pg

348

As you can see this is schema violates the 1NF because there are multiple Artirst_nm associated with an Album_no or the domain of Artist_nm does not have atomic values. In fact by definition, ALBUM is not even a relation.

1NF Violation and Resolution Figure 8.1 pg

348

In order to fix ALBUM we must expand the relation so that there is a tuple for each (atomic) Artist_nm for a given Album_no. The primary key for this is { Album_no, Artist_nm } as we all should hopefully know by now.

2 nd^ NF

Example

• Only Candidate key is (Employee, Skill)
• Not in 2NF
• Current Work Location is dependent on Employee
• Can Cause an Anomaly

Updating Jones Work location for Typing and Shorthand but not Whittling. Then asking “What is Jones current work location”, can cause a contradictory answer, because there are 2 different locations.

2 nd^ NF

Example

• Both tables are in 2NF
• Meets 1NF requirements
• No non-primary key

attribute is dependent on part of a key

Third Normal Form

Example

Eliminate Columns Not Dependent On Key

i.e. if a column is in a relation, then it must be

dependent on the key.

Third Normal Form

Example

Move non-key-dependent attributes to a

new table.

8.2 The Motivating Exemplar Revisited Normalization concepts have been presented by analyzing 1NF, 2NF 3NF and BCNF in isolation.

However in practice normal form violations rarely occur in isolation.

We can see from figure 8.8a that STOCK follows 1NF because there are not composite or multi-valued attributes in it.

Motivating Exemplar Revisited

Using Armstrong’s axioms we get {Store, Product} and {Manager, Location,(cont)

Product} for candidate keys, however we choose {Store, Product} as a primary key.

Now that we have the primary key for STOCK we can see that:

fd1, fd2, fd3 and fd4 violates 2NF in STOCK fd6 violates 3NF in STOCK. fd7 violates BCNF in STOCK

To fix all of the violations above we must decompose the relational schema D:{R1 R2 R3 R4 R5}