Normalization

Data and Information

List of employees and their respective departments

Library Book Collection

List of employees and their respective departments

What IF?

• You need to make changes to an employee’s name? • You need to change the name of a department form IT to IT-MIS? • You need to remove a department in the list?

Library Book Collection

What IF? • You need to make changes to an author’s name? • You need to change the Title of a book?

Disadvantages of Redundant Field/Columns •Prone to Errors •Tedious Repetition of the same items •Bulky and Difficult to manage So, Break down Tables with Redundant Fields

Redundant Field/Columns List of employees and their respective departments

Remove the Redundant Fields

Redundant Field/Columns Library Book Collection

Remove the Redundant Fields

Remove the redundant field(s)

DEPARTMENT

Create a separate table for department data

EMPLOYEE NORMALIZED or SIMPLIFIED TABLES

DEPARTMENT

EMP_DEPT

The new tables are now Normalized.

Library Book Collection

Step 1

Remove Redundant Fields and create a separate table for them

Step 2

AUTHORS

Create a new table for the AUTHORS data

Step 3 Create the relationship/link AUTHORS

Add a new field which makes each record unique (ex. IDNo, codeNo, recNo, AuthorID)

NORMALIZED or SIMPLIFIED TABLES

AUTHORS

BOOKS

BOOK_AUTH

The new tables are now Normalized.

Guidelines in developing a Database 1. Determine the purpose of the database 2. Design the tables   

Design tables on paper first Each table should contain data about one subject Ex. Member table contains data about members only

3. Design the records and fields for each table

4. Be sure every record has a primary key A Primary key is a key field, which uniquely identifies each record in a file)  Use separate fields for logically distinct items

Ex. A name could be stored in six fields:      

Title [Mr., Mrs., Dr., etc.] First name Middle Name Last Name Suffix [Jr., Sr., etc.] Nickname