Exam Mis

Topics: Database normalization, Third normal form, Second normal form Pages: 6 (1048 words) Published: July 2, 2013
NORMALIZATION

The process of normalization begins with a user view of some kind (Report, Table, Screen display, Document, etc...). This view should be about some kind of important data (Invoice, Purchase order, Project details, Registration form, etc....).

Example: Consider the following Project Management Report

Project Management Report

Project Code:PC010Project Manager:M. Philips
Project Title:Database SystemProject budget:240.000$

Employee No.Emp. NameDept IDDept NameHourly Rate
S-1001 A. Smith L004 IT 22$
S-2310 L. Ferguson L007 HR 25$
S-5561 F. Macleod L004 IT 21$
S-0024 A. Jones L009 Accounting 18$

Total Staff on Project: 4Average Rate: 21$

The main objective of the normalization is to extract, out of this view, the normalized tables: All the necessary detailed tables along with their attributes (fields) and identifiers (primary keys).

The first step of normalization is to copy all the data fields of the form and put them into a single relation (table) with a specific identifier (main primary key).

Project data
(Proj_Code, Title, Manager, Budget, Emp_Id, Emp_Name, Dept_Id, Dept_Name, Hour_Rate)

PS: We did not include the fields "total staff number" and "Average rate" because they are calculated fields, so no need to put them or save them into the relation.

Then we have to go through three main steps, from one form into another form, to reach the final normalized design of the data. These steps are known as the three normal forms (1st NF, 2nd NF, 3rd NF).

How to move to the first normal form (1NF):

A relation is said to be in its 1NF if the following constraints apply: - No repeating groups exist in the relation.
- Each relation has its own primary key.

So, to reach the 1NF we have to find all the repeating groups in the original relation and take each group out into a new separate relation with its own primary key.

In the previous example, there is only one repeating group about different employees working in the same project. So we have to take out this group and put it in a separate relation like the following:

Project data
(Proj_Code, Title, Manager, Budget)

Project details
(Proj_Code, Emp_Id, Emp_Name, Dept_Id, Dept_Name, Hour_Rate)

How to move to the second normal form (2NF):

A relation is said to be in its 2NF if the following constraints apply: - Must be in the 1NF.
- All attributes depending fully on the primary key (not only on part of the key).

Back to our example, in the first relation (Project_data) we have no problem, because all the attributes are fully dependant on the primary key. But in the second relation (Project_details) we have some partially dependant attributes.

Emp_Name, Dept_Id, and Dept_Name are dependent upon Emp_Id only (a part of the composite key). Therefore, they must be moved to a new table with Emp_Id being the primary key. However, Hourly Rate is dependent upon both Project Code and Employee Id as an employee may have a different hourly rate depending upon which project they are working on. Therefore it should remain in the original table.

Project data
(Proj_Code, Title, Manager, Budget)

Project details
(Proj_Code, Emp_Id, Hour_Rate)

Employees
(Emp_Id, Emp_Name, Dept_Id, Dept_Name)

How to move to the third normal form (3NF):

A relation is said to be in its 3NF if the following constraints apply: - Must be in the 2NF.
- No attribute depending on other than the primary key (all attributes dependent only on the primary key.

Back to our example, we have no such a problem in tables 1 and 2 so they are directly into the 3NF. But the third table (Employees) has 1 attribute not depending on the primary key. It is the Dept_Name attribute which dependant on Dept_Id (which is not a primary key) and not on Emp_Id the primary key. So we have to extract this attribute into a new relation. And...
Continue Reading

Please join StudyMode to read the full document

You May Also Find These Documents Helpful

  • California High School Exit Exams Essay
  • Essay on Mi Ultimo Adios
  • Philippine Revolution and Mi Ultimo Adios Essay
  • Essay on MIS 535 Final Exam
  • MIS 535 Week 4 Midterm Exam 1 Essay
  • Essay about Exam 2
  • Exam Essay
  • exam Essay

Become a StudyMode Member

Sign Up - It's Free