Reference no: EM133682456

Database Management Systems

Normalization of Database Tables

Learning Objective 1: Explain normalization and its role in the database design process

Learning Objective 2: Identify and describe each of the normal forms: 1NF, 2NF, 3NF, BCNF, and 4NF

Learning Objective 3: Explain how normal forms can be transformed from lower normal forms to higher normal forms

Learning Objective 4: Apply normalization rules to evaluate and correct table structures

Learning Objective 5: Identify situations that require denormalization to generate information efficiently

Learning Objective 6: Use a data-modeling checklist to check that the ERD meets a set of minimum requirements

Normalization of Database Tables

What is normalization? When is a table in 1NF?
Given the dependency diagram shown in Figure 1, answer the following questions
Identify and discuss each of the indicated dependencies
Create a database whose tables are at least in 2NF, showing the dependency diagrams for each table.
Create a database whose tables are at least in 3NF, showing the dependency diagrams for each table.

The dependency diagram in Fig 2 indicates that a patient can receive many prescriptions for one or more medicines over time. Based on the dependency diagram, create a database whose tables are in at least 2NF, showing the dependency diagram for each table.

 

What is a partial dependency? With what normal form is it associated?
What three data anomalies are likely to be the result of data redundancy? How can such anomalies be eliminated?

Database Tables and Normalization

Normalization: evaluating and correcting table structures to minimize data redundancies
Reduces data anomalies
Assigns attributes to tables based on determination
Normal forms
First normal form (1NF)
Second normal form (2NF)
Third normal form (3NF)

Structural point of view of normal forms
Higher normal forms are better than lower normal forms
Properly designed 3NF structures meet the requirement of fourth normal form (4NF)
Denormalization: produces a lower normal form
Results in increased performance and greater data redundancy

The Need for Normalization
Used while designing a new database structure
Analyzes the relationship among the attributes within each entity
Determines if the structure can be improved through normalization
Improves the existing data structure and creates an appropriate database design

The Normalization Process
Objective is to ensure that each table conforms to the concept of well-formed relations
Each table represents a single subject
Each row/column intersection contains only one value and not a group of values
No data item will be unnecessarily stored in more than one table
All nonprime attributes in a table are dependent on the primary key
Each table has no insertion, update, or deletion anomalies

Ensures that all tables are in at least 3NF
Higher forms are not likely to be encountered in business environment
Works one relation at a time
Identifies the dependencies of a relation (table)
Progressively breaks the relation up into a new set of relations

Partial dependency: functional dependence in which the determinant is only part of the primary key
Assumption: one candidate key
Straight forward
Easy to identify
Transitive dependency: attribute is dependent on another attribute that is not part of the primary key
More difficult to identify among a set of data
Occur only when a functional dependence exists among nonprime attributes

Conversion to First Normal Form

Repeating group: group of multiple entries of same type can exist for any single key attribute occurrence
Reduces data redundancies
Three step procedure
Eliminate the repeating groups
Identify the primary key
Identify all dependencies
Dependency diagram: depicts all dependencies found within given table structure
Helps to get an overview of all relationships among table's attributes
Makes it less likely that an important dependency will be overlooked

1NF describes tabular format in which:
All key attributes are defined
There are no repeating groups in the table
All attributes are dependent on the primary key
All relational tables satisfy 1NF requirements
Some tables contain partial dependencies
Update, insertion, or deletion

Conversion to Second Normal Form
Conversion to 2NF occurs only when the 1NF has a composite primary key
If the 1NF has a single-attribute primary key, then the table is automatically in 2NF
The 1NF-to-2NF conversion is simple
Make new tables to eliminate partial dependencies
Reassign corresponding dependent attributes
Table is in 2NF when it:
Is in 1NF
Includes no partial dependencies

Summary Normalization is a technique used to design tables in which data redundancies are minimized
A table is in 1NF when all key attributes are defined and all remaining attributes are dependent on the primary key
A table is in 2NF when it is in 1NF and contains no partial dependencies
A table is in 3NF when it is in 2NF and contains no transitive dependencies
A table that is not in 3NF may be split into new tables until all of the tables meet the 3NF requirements
Normalization is an important part-but only a part-of the design process
A table in 3NF might contain multivalued dependencies that produce either numerous null values or redundant data