Normalization of Database Management Systems

Only available on StudyMode
  • Download(s) : 363
  • Published : December 3, 2012
Open Document
Text Preview
Normalization of Database Tables

Database Tables and Normalization

* Normalization is a process for assigning attributes to entities. It reduces data redundancies and helps eliminate the data anomalies. * Normalization works through a series of stages called normal forms: * First normal form (1NF)

* Second normal form (2NF)
* Third normal form (3NF)
* Fourth normal form (4NF)
* The highest level of normalization is not always desirable.

* The Need for Normalization
* Case of a Construction Company
* Building project -- Project number, Name, Employees assigned to the project. * Employee -- Employee number, Name, Job classification * The company charges its clients by billing the hours spent on each project. The hourly billing rate is dependent on the employee’s position. * Periodically, a report is generated.

* The table whose contents correspond to the reporting requirements is shown in Table 5.1.

Scenario

A few employees works for one project.
Employee Num : 101, 102, 103, 105 Project Num : 15
Project Name : Evergreen

Sample Form

Table Structure Matches the Report Format

Database Tables and Normalization
* Problems with the Figure 5.1
* The project number is intended to be a primary key, but it contains nulls. * The table displays data redundancies.
* The table entries invite data inconsistencies.
* The data redundancies yield the following anomalies: * Update anomalies.
* Addition anomalies.
* Deletion anomalies.

* Conversion to First Normal Form
* A relational table must not contain repeating groups. * Repeating groups can be eliminated by adding the appropriate entry in at least the primary key column(s).

Data Organization: First Normal Form

BEFORE

AFTER

First Normal Form ( 1 NF )

* 1NF Definition
* The term first normal form (1NF) describes the tabular format in which: * All the key attributes are defined.
* There are no repeating groups in the table.
* All attributes are dependent on the primary key.

Dependency Diagram

* Dependency Diagram
* The primary key components are bold, underlined, and shaded in a different color. * The arrows above entities indicate all desirable dependencies, i.e., dependencies that are based on PK. * The arrows below the dependency diagram indicate less desirable dependencies -- partial dependencies and transitive dependencies.

Second Normal Form (2 NF)

* Conversion to Second Normal Form
* Starting with the 1NF format, the database can be converted into the 2NF format by * Writing each key component on a separate line, and then writing the original key on the last line and * Writing the dependent attributes after each new key. PROJECT (PROJ_NUM, PROJ_NAME)

EMPLOYEE (EMP_NUM, EMP_NAME, JOB_CLASS, CHG_HOUR)
ASSIGN (PROJ_NUM, EMP_NUM, HOURS)

Dependency Diagram

Second Normal Form

A table is in 2NF if:
* It is in 1NF and
* It includes no partial dependencies; that is, no attribute is dependent on only a portion of the primary key.
(It is still possible for a table in 2NF to exhibit transitive dependency; that is, one or more attributes may be functionally dependent on nonkey attributes.)

Third Normal Form

* Conversion to Third Normal Form
* Create a separate table with attributes in a transitive functional dependence relationship. PROJECT (PROJ_NUM, PROJ_NAME)
ASSIGN (PROJ_NUM, EMP_NUM, HOURS)
EMPLOYEE (EMP_NUM, EMP_NAME, JOB_CLASS)
JOB (JOB_CLASS, CHG_HOUR)

* 3NF Definition
* A table is in 3NF if:
* It is in 2NF and
* It contains no transitive dependencies.

The Completed Database

Boyce-Codd Normal Form (BCNF)

* A table is in Boyce-Codd normal form...
tracking img