Exam Mis
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: PC010 Project Manager: M. Philips
Project Title: Database System Project budget: 240.000$
Employee No. Emp. Name Dept ID Dept Name Hourly 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: 4 Average 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
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: PC010 Project Manager: M. Philips
Project Title: Database System Project budget: 240.000$
Employee No. Emp. Name Dept ID Dept Name Hourly 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: 4 Average 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