Normal Form and Key in Database Normalization

A database management system is a collection of computer software that enables users to define, create and maintain a database. Defining a database involves specifying constraints for the data stored in the database, and normalization is a logical design method which minimizes duplicate, or redundant, data and design flaws. Normalization is the process of effectively organizing data in a database. The primary purpose of normalization is to allow update, insert and delete operations to be performed on a single database table and propagated throughout the database by means of the defined relationship.

There are two goals of the normalization process:

1. Eliminating redundant data ( for example storing the same data in more than one table)
2. Ensuring data dependencies make sense (only storing related data in a table)

EVALUATION

1. Define Normalization.
   1. Define Database Management System.

NORMAL FORM

The normal form (NF) of relational database theory provide critical for determining a table’s degree of vulnerability to logical inconsistencies and anomalies. The higher the normal form applicable to a table, the less vulnerable it is. Each table has a “Highest Normal Form”(HNF): by definition, a table always meets the requirements of its HNF and all normal forms lower than its HNF; also by definition, a table fails to meet the requirement of any normal form higher than its HNF.

Normal form theory deal with how to reduce the amount of redundancy of data within a given table. Each normal form represents a level. To satisfy each the requirements for certain level, the requirements for the previous level must be met. To reach the optimal normal form for the tables within a database, the creator starts with a large list of all the data that is to be held in the database, and then works through the normal forms until he can no longer break the data down into smaller table.

GENERAL EVALUATION

1. Explain Normal Form.
   1. State the two goals of normalization process.

READING ASSIGNMENT

Data Processing for Senior Secondary Education by HiiT Plc. Chapter Two, Page 65

WEEKEND ASSIGNMENT

Instruction: Choose the correct option from the ones lettered A to D

1. ____ is a collection of computer software that enables users to define, create and maintain a database. (a) Database Management System (b) Normalization(c) Data Model (d) Normal Form

2.____ is the logical design method which minimizes duplicate or redundant data and design flaws. (a) Database  (b) Normalization   (c) Form   (d) Data Model

3. One of the goals of normalization process is ____ (a)Eliminate redundant data (b) Ensure data independencies (c) Allow Update, insertion and deletion(d) Create tables

4. The primary purpose of normalization is to ____ (a) Eliminate redundant data (b) Ensure data dependencies (c) Allow Update, insertion and deletion (d) Create tables

5. ____ is the process of efficiently organizing data in a database.

(a) Database(b) Normalization   (c) Form   (d) Model

SECTION B

1. What is Normal Form?

2.  Differentiate between a common sense and systematic normalization

Types of Normal Form

First Normal Form (1NF)

First Normal Form (1NF) says that all column values must be atomic. 1NF dictates that, for every row by column position in a given table, there exist only one value, not an array or list of values i.e in 1NF, the following rules are observed;

1. Eliminating repeating information
2. Create separate tables for related data.

Second Normal Form (2NF)

The second normal form (2NF) further addresses the concept of removing duplicate data. The rule for the second normal form is;

1. Remove subsets of data that apply to multiple rows of a table and place them in separate table.
2. Create relationships between these new tables and their predecessors through the use of foreign keys.

EVALUATION

1. State the rules for carrying out first normal form (1NF).
   1. State the rules for carrying out second normal form (2NF).

NOTE: The 2NF attempts to reduce the amount of redundant data in a table by extracting it, placing it in new table(s) and creating relationships between those tables.

Third Normal Form (3NF)

There are two basic requirements for a database to be in Third Normal Form:

1. The requirements of both 1NF and 2NF must have been met.
2. Remove columns that are not fully dependent upon the primary key.

The Fourth Normal Form (4NF) and Fifth Normal Form (5NF)

The fourth and the fifth normal form are beyond the scope of this syllabus.

Note: Both the fourth and the fifth normal form still follows the goal of normalization process, which are;

1. eliminating redundant data.
2. ensuring data dependencies make sense.

GENERAL EVALUATION

1. State the goal of normalization process.
2. State the rules for carrying out the first normal form (1NF)

READING ASSIGNMENT: Data Processing for Senior Secondary Education by HiiT Plc. Chapter Three, pages 65 – 67

WEEKEND ASSIGNMENT

Instruction: Choose the correct option from the ones lettered A to D

1. ____ normal form says that all column values must be atomic.
2. 1NF            (b) 2NF            (c) 3NF (d) 4NF
3. ____ normal form must meet the requirement of first and second.
4. 1NF            (b) 2NF            (c) 3NF (d) 4NF
5. ____ normal form are beyond the scope for the syllabus.
6. 1NF and 2NF          (b) 2NF and 3NF          (c) 3NF and 4NF          (d) 4NF and 5NF
7. One of the following is a basic requirement for database to be in the third normal form.
8. Remove columns that are not fully dependent upon the primary key
9. Create relationship between these new tables
10. Eliminate repeating information
11. Remove subsets of data that apply to multiple rows
12.  How many value(s) exist in 1NF for every row-by-column position in a given table?

(a) one        (b) two (c) three          (d) four                                                                                                                                                                                                                 

SECTION B

1. State the rules for carrying out first normal form.

State the rules for carrying out second normal form.

KEY

A key is an attribute or field that can be used to identify a record in a database table or file.

The primary Key is a unique attribute that can be used to identify a record in a database table. For Example, in the student database table above, the attribute that is unique to identify each record is the Student-ID or RegNo.

CONCEPTS OF FOREIGN KEY

A foreign key is a field in a relational table that matches a candidate key of another table. A foreign key (FK) is a column or combination of columns that is used to establish and enforce a link between the data in two tables. You can create a foreign key by defining a FOREIGN KEY constraint when you create or modify a table. The foreign key can be used to cross-reference table. The foreign key identifies a column or set of columns in one (referencing or child) table that refers to a column or set of column in another (referenced or parent) table. The columns in the child table must reference the columns of the primary key or other super key in the parent table.            

EVALUATION

1. Define Key as regards to database.
   1. Differentiate between primary key and foreign key

EXPLAIN THE DETERMINATION OF NORMAL FORM

Normalization is a technique for producing a set of suitable relations that support the data requirements of an enterprise. Characteristics of a suitable set of relations include:

1. The minimal number of attributes necessary to support the data requirements of the enterprise
   1. Attributes with a close logical relationship are found in the same relation.
      1. Minimal redundancy with each attribute represented only once with the important exception of attributes that form all or part of foreign keys.

READING ASSIGNMENT

Data Processing for Senior Secondary Education Hiit Plc. Chapter Four Page 67.

WEEKEND ASSIGNMENT

Instruction: Choose the correct option from the ones lettered A to D

1. ____ is a unique attribute that can be used to identify a record in a database table.
2. Foreign Key            (b) Primary Key (c) Secondary Key        (d) Elementary Key
   1. ____ is a field in a relational table that matches a candidate key of another table.
3. Foreign Key            (b) Primary Key (c) Secondary Key        (d) Elementary Key
   1. ____ is a technique for producing a set of suitable relations that support the data requirements of an enterprise.
4. Normal Form          (b) Entity         (c) Attribute    (d) Normalization
   1. One of the following is not a part of the characteristics that support the data requirements of an enterprise.
5. The minimal number of attributes necessary to support the data requirements of the enterprise
6. Attributes with a close logical relationship are found in the same relation.
7. Minimal redundancy with each attribute represented only once with the important exception of attributes that form all or part of foreign keys.
8. Attributes with a open logical relationship are found in the same relation.
   1. ____ is an attribute or field that can be used to identify a record in a database table or file. (a) Key     (b) Attribute   (c) Entity         (d) Relationship

SECTION B

1. Using tables, explain the differences between primary key and foreign key.
   1.  State two characteristics of suitable relations that support the data requirements of an enterprise.