Lesson Notes By Weeks and Term - Senior Secondary 2

Term: 1^st Term

Week: 7

Class: Senior Secondary School 2

Age: 16 years

Duration: 40 minutes of 2 periods each

Date:       

Subject:      Data Processing

Topic:-       Normal Form II

SPECIFIC OBJECTIVES: At the end of the lesson, pupils should be able to

1. Define normal forms
2. State the goals of normalization

INSTRUCTIONAL TECHNIQUES: Identification, explanation, questions and answers, demonstration, videos from source

INSTRUCTIONAL MATERIALS: Videos, loud speaker, pictures, Data Processing for senior Secondary Education by Hiit Plc, WAPB Computer Studies for Senior Secondary I by Adekunle et al, On-line Materials.

INSTRUCTIONAL PROCEDURES

PERIOD 1-2

NOTE

Normal form

Normal form is the way of measuring the level, or depth, to which a

database has been normalized. The most common normal forms are:

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

Other normal forms are Boyce /Codd normal form (BCNF), 4^th normal form

and 5^th normal form.

Note: Each subsequent normal form depends on the normalization steps

taken in the previous normal form. The 3NF is sufficient for most typical

database applications.

First normal form (1NF)

A database table is said to be in 1NF if:

• It contains no repeating or duplicate fields/columns (i.e. each column name should be unique)
• No data in a columns is multi-valued  (i.e. each data field is single value)
• Each row of data has a unique identifier (or Primary Key)
• The attribute domain remains the same (i.e. age value cannot be in the name column)

For example, consider the un-normalized table below:

         Table1: Un-normalized table

The table above is not in a first normal form because:

• Multiple items in color field  (i.e. red and blue , red and yellow etc)
• Duplicate records/no particular primary key (i.e. row1 and row3)

So, how do you convert the table above into 1NF?

• Delete one of the duplicate records (i.e. row1 or row3)
• Expand the remaining rows such that each column has a single value 

The resulting table now in 1NF is shown below.

      Table 2: 1NF table

Problems with tables in first normal form (1NF)

Insert anomalies: An Insert Anomaly occurs when certain attributes cannot be inserted into the database without the presence of other attributes. Suppose a new item has just been bought and is to be added into the table and you do not know the tax applicable, it will be difficult to enter a few item of information and not all, thereby leading to Insertion Anomaly.

Delete anomalies: A Delete Anomaly exists when certain attributes are lost because of the deletion of other attributes. If the White colour of the item Pant is no more in stock, and we try to delete ‘white’ from the colour column, then we will be forced to remove the item Pant, the price and the tax as well since the entire row will be deleted

Update anomalies: An Update Anomaly exists when one or more instances of duplicated data are updated, but not all. For example, if the tax applicable to the price 12.0 changed, then we will have to update all the rows where there is 0.60, else data will become inconsistent i.e. there will be different tax value for the price 12.0 in different rows.

EVALUATION:    1. Define normal form

2. How do you create the first normal form a convert a table into it?
3. Discuss the problems with tables in first normal forms

CLASSWORK: As in evaluation

CONCLUSION: The teacher commends the students positively