top-rated free essay

Normalizaiton in Database

By aliraza6619 Sep 27, 2013 1773 Words
Normalization of Database Tables

Introduction to
Normalization
of
Database
Tables

Normalization of Database Tables

l

Introduction to
Normalization
of
Database
Tables

4
4
4

ISM 602
Dr. Hamid Nemati

4
4
4

Functional Dependency
l
Introduction to
Normalization
of
Database
Tables

l

l

A Functional Dependency Is A Relationship Between
Or Among Attributes Such That The Values Of One
Attribute Depend On, Or Are Determined By, The
Values Of The Other Attribute(s).
Partial Dependency: Is A Relationship Between
Attributes Such That The Values Of One Attribute Is
Dependent On, Or Determined By, The Values Of
Another Attribute Which Is Part Of The Composite Key.
Partial Dependencies Are Not Good Due To duplication
Of Data And Update Anomalies;

Examples of Functional Dependencies:
l
Introduction to
Normalization
of
Database
Tables

l

l

Transitive Dependencies
l
Introduction to
Normalization
of
Database
Tables

l

Is A Relationship Between Attributes Such That The
Values Of One Attribute Is Dependent On, Or
Determined By, The Values Of Another Attribute
Which Is Not A Part Of The Key.
Exist when a nonkey attribute value is functionally
dependent upon another nonkey value in the record. For
example:
l
l

l

If we know an ISBN, then we know the Book Title and
the author(s)
l ISBN è Book Title
l ISBN è Author(s)
If we know the VIN, then we know who is the Auto
owner
l VIN è Auto_Owner
If we know Student-ID (SID), then we can uniquely
determine his/her Name
l SID è S_Name

So Now what is Normalization?
l
Introduction to
Normalization
of
Database
Tables

GOLDEN RULE OF NORMALIZATION: Enter The
Minimum Data Necessary, Avoiding Duplicate Entry
Of Information, With Minimum Risks To Data
Integrity.

l

Goals Of Normalization:
u

Eliminate Redundancies Caused By:
l

EMPLOYEE_ID --> JOB_CATEGORY
JOB_CATEGORY --> HOURLY_RATE

An employee data table that includes the “hourly pay
rate” would require searching every employee record to
properly update an hourly rate for a particular job
category.

Objectives
The idea of Dependencies of Attributes
Normalization and Database Design
Understand concepts of normalization
(Higher-Level Normal Forms)
Learn how to normalize tables
Understand normalization and database
design issues
Denomalization

l
l

u
u
u

Fields Repeated Within A File
Fields Not Directly Describing The Key Entity
Fields Derived From Other Fields

Avoid Anomalies In Updating (Adding, Editing,
Deleting)
Represent Accurately The Items Being Modeled
Simplify Maintenance And Retrieval Of Info

Basic Rule for Normalization

Database Tables and Normalization
l
Introduction to
Normalization
of
Database
Tables

l

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:
u
u
u

l

l
Introduction to
Normalization
of
Database
Tables

First normal form (1NF)
Second normal form (2NF)
Third normal form (3NF)

u

l
l
l

l
l

EM_SS_NUM --> EM_NAME

Corollaries
u

The highest level of normalization is not always
desirable.

Facilitates data integration.
Reduces data redundancy.
Provides a robust architecture for retrieving
and maintaining data.
Compliments data modeling.
Reduces the chances of data anomalies
occurring.

A relationship is functionally dependent when one
attribute value implies or determines the attribute
value for the other attribute.
l

l

u

Normalization Benefits

Introduction to
Normalization
of
Database
Tables

The attribute values in a relational table should
be functionally dependent (FD) on the primary
key value.

Corollary 1: No repeating groups allowed in
relational tables.
Corollary 2: A relational table should not have
attributes involved in a transitive dependency
relationship with the primary key.

Database Tables and Normalization
l

The Need for Normalization
u

Introduction to
Normalization
of
Database
Tables

Case of a Construction Company
l

l
l

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.

Deletion Anomaly

Database Tables and Normalization
l
Introduction to
Normalization
of
Database
Tables

Problems with the Table 5.1
u
u
u
u

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:
l
l
l

Update anomalies.
Addition anomalies.
Deletion anomalies.

l
Introduction to
Normalization
of
Database
Tables

l

Occurs when the removal of a record results in a
loss of important information about an entity.
Example:
l

l

All the information about a customer is contained in an order file, if the order is canceled, all the customer information could be lost when the order record is deleted

Solution:
l

Create two tables--one table contains order information and
the other table contains customer information.

Update Anomaly
l
Introduction to
Normalization
of
Database
Tables

l

Occurs when a change of a single attribute in one
record requires changes in multiple records
Example:
uA

staff person changes their telephone number
and every potential customer that person ever
worked with has to have the corrected number
inserted.

l

Insertion Anomaly
l
Introduction to
Normalization
of
Database
Tables

l

Occurs when there does not appear to be any
reasonable place to assign attribute values to
records in the database. Probably have
overlooked a critical entity.
Example:
u Adding

new attributes or entire records when they
are not needed. Where do you place information
on new Evaluator’s? Do you create a dummy
Lead.

Solution:
u Put

the employees telephone number in one
location--as an attribute in the employee table.

l

Solution:
u Create

a new table with a primary key that
contains the relevant or functional dependent
attributes.

Database Tables and Normalization
l

Conversion to First Normal Form
u

Introduction to
Normalization
of
Database
Tables

Database Tables and Normalization

u

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). (See Database Table 5.3)

l

Dependency Diagram
u

Introduction to
Normalization
of
Database
Tables

u

The arrows above the entity indicate that the entity’s
attributes are dependent on the combination of
PROJ_NUM and EMP_NUM.
The arrows below the dependency diagram indicate
less desirable dependencies based on only a part of
the primary key -- partial dependencies.

Database Table 5.2 The Evergreen Data
Figure 5.1 A Dependency Diagram: First Normal Form

Database Tables and Normalization
l
Introduction to
Normalization
of
Database
Tables

Database Tables and Normalization

1NF Definition
u

The term first normal form (1NF) describes the
tabular format in which:
l
l
l

All the key attributes are defined.
There are no repeating groups in the table.
All attributes are dependent on the primary key.

l
Introduction to
Normalization
of
Database
Tables

Conversion to Second Normal Form
u

Starting with the 1NF format, the database can be
converted into the 2NF format by
l

l

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)

Database Tables and Normalization
l

2NF Definition
u

Introduction to
Normalization
of
Database
Tables

Database Tables and Normalization

A table is in 2NF if:
l
l

u

It is in 1NF and
It includes no partial dependencies; that is, no
attribute is dependent on only a portion of the primary
key.

l

Conversion to Third Normal Form
u

Introduction to
Normalization
of
Database
Tables

PROJECT (PROJ_NUM, PROJ_NAME)
ASSIGN (PROJ_NUM, EMP_NUM, HOURS)
EMPLOYEE (EMP_NUM, EMP_NAME, JOB_CLASS)
JOB (JOB_CLASS, CHG_HOUR)

Note:
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.

u

See figure 5.2 page 290.

Database Tables and Normalization
l

Normalization and Database Design

3NF Definition
u

Introduction to
Normalization
of
Database
Tables

Create a separate table with attributes in a transitive
functional dependence relationship.

A table is in 3NF if:
l
l

It is in 2NF and
It contains no transitive dependencies.

l
Introduction to
Normalization
of
Database
Tables

Database Design and Normalization Example:
(Construction Company)
u

Summary of Operations:
l
l
l
l

l

l

Normalization and Database Design
l
Introduction to
Normalization
of
Database
Tables

Normalization and Database Design

Two Initial Entities:
PROJECT (PROJ_NUM, PROJ_NAME)
EMPLOYEE (EMP_NUM, EMP_LNAME, EMP_FNAME,
EMP_INITIAL, JOB_DESCRIPTION,
JOB_CHG_HOUR)

Figure 5.7 The Initial E-R Diagram for a Contracting Company

The company manages many projects.
Each project requires the services of many employees.
An employee may be assigned to several different projects.
Some employees are not assigned to a project and perform
duties not specifically related to a project. Some employees are part of a labor pool, to be shared by all project teams. Each employee has a (single) primary job classification.
This job classification determines the hourly billing rate.
Many employees can have the same job classification.

l
Introduction to
Normalization
of
Database
Tables

Three Entities After Transitive Dependency
Removed
PROJECT (PROJ_NUM, PROJ_NAME)
EMPLOYEE (EMP_NUM, EMP_LNAME, EMP_FNAME,
EMP_INITIAL, JOB_CODE)
JOB (JOB_CODE, JOB_DESCRIPTION,
JOB_CHG_HOUR)

Normalization and Database Design

Normalization and Database Design
l

Introduction to
Normalization
of
Database
Tables

Creation of the Composite Entity ASSIGN

Introduction to
Normalization
of
Database
Tables

Figure 5.8 The Modified E-R Diagram for a Contacting Company Figure 5.9 The Final (Implementable) E-R Diagram
for the Contracting Company

Normalization and Database Design
l
Introduction to
Normalization
of
Database
Tables

l

Attribute ASSIGN_HOUR is assigned to the
composite entity ASSIGN.
“Manages” relationship is created between
EMPLOYEE and PROJECT.

Normalization and Database Design

Introduction to
Normalization
of
Database
Tables

PROJECT (PROJ_NUM, PROJ_NAME, EMP_NUM)
EMPLOYEE (EMP_NUM, EMP_LNAME, EMP_FNAME,
EMP_INITIAL, EMP_HIREDATE, JOB_CODE)
JOB (JOB_CODE, JOB_DESCRIPTION,
JOB_CHG_HOUR)
ASSIGN (ASSIGN_NUM, ASSIGN_DATE, PROJ_NUM,
EMP_NUM, ASSIGN_HOURS)

Figure 5.10 The Relational Schema for the Contracting Company

Summary
A Journey of Normalization
Introduction to
Normalization
of
Database
Tables

First Normal Form
(1NF)

Remove
“Repeating Groups”

Second Normal Form
(2NF)

Remove
“Partial Functional
Dependency”

Denormalization

Third Normal Form
(3NF)

Remove
“Transitive Functional
Dependency”

Higher order Normal
Forms

Remove
“All Remaining Functional
Dependency”

l
Introduction to
Normalization
of
Database
Tables

l
l

Normalization is only one of many database design
goals.
Normalized (decomposed) tables require additional
processing, reducing system speed.
Normalization purity is often difficult to sustain in
the modern database environment. The conflict
between design efficiency, information
requirements, and processing speed are often
resolved through compromises that include
denormalization.

Cite This Document

Related Documents

  • Database concepts

    ... Assignments Requirements 1. Look up the definition of functional dependency. Can you find a good example? In relational database theory, a functional dependency is a constraint between two sets of attributes in a relation from a database. Cars Suppose one is designing a system to track vehicles and the capacity of their engines. Each vehic...

    Read More
  • Database

    ...DATABASE: A database is an organized collection of data. The data is typically organized to model relevant aspects of reality (for example, the availability of rooms in hotels), in a way that supports processes requiring this information (for example, finding a hotel with vacancies). A general-purpose database management system (DBMS) is a...

    Read More
  • Database

    ...DATABASE A database is a collection of data organized to meet users’ needs. Why This Matters Without data and the ability to process it, an organization would not be able to successfully engage in business activities, nor would it be able to generate reports to support knowledgeable workers and decision makers which in turn help’s to achi...

    Read More
  • database

    ...sharing among developers, stored procedures give database application developers the advantages of less work, standardized processing, and specialization among developers. Selected Answer: True   Question 10 5 of 5 points   The SQL command used to create a virtual table is ________. Selected Answer:    CREATE VIEW...

    Read More
  • Database

    ... Database concepts | Assignment 1 | | Robyn Wright | 3/27/2013 | | Contents Introduction 2 Task 1 3 1.1 Definition of database management system 3 1.2 5 Advantages of database management system 3 1.3 3 Business functions database management system could do 3 1.4 5 database management system p...

    Read More
  • Database

    ...HISTORY OF DATABASE MANAGEMENT SYSTEM With the progress in technology in the areas of processors, computer memory, computer storage and computer networks, the sizes, capabilities, and performance of databases and their respective DBMSs have grown in orders of magnitudes. The development of database technology can be divided into three e...

    Read More
  • Database Normalization

    ...Normalization Normalization is a method for organizing data elements in a database into tables. Normalization Avoids • Duplication of Data – The same data is listed in multiple lines of the database • Insert Anomaly – A record about an entity cannot be inserted into the table without first inserting information about an...

    Read More
  • Database Management

    ...performed on data or data items to supply some information about an entity. What is a Database? A database is an integrated and structured collection of stored operational data used (shared) by application systems of an enterprise Examples of Databases • Medical records • Bank accounts • Stock control ...

    Read More

Discover the Best Free Essays on StudyMode

Conquer writer's block once and for all.

High Quality Essays

Our library contains thousands of carefully selected free research papers and essays.

Popular Topics

No matter the topic you're researching, chances are we have it covered.