2.
Ensuring
data dependencies make sense (only storing related data in a table).
Normal
form
The normal forms (NF) of relational
database theory provide criteria for determining a table's degree of vulnerability
to logical Inconsistencies and anomalies.
The higher the normal form applicable to 1 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 of all normal forms lower than its HNF; also by definition, a table fails to meet the requirements of any normal form higher than Its HNF.
Normal forms theory deals with
how to reduce the amount of redundancy of data within a given table. Each
normal form represents a level. To satisfy each the requirement for a certain
level, the requirements for the previous levels must be mat. 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 tables.
The normal forms are applicable to individual tables; to say that an entire database is in normal form is to say that all of its tables are in normal form.
First Normal Form (INF)
First Normal Form (INF) says that
all column values must be atomic. INF dictates that, for every row-by- column
position in a given table, there exists only one value, not an array or list of
values i.e. In INF, the following rules are observe:
1. Eliminate repeating
information.
2. Create separate tables for
related data.
If you think about the flat table design with many repeated sets of fields for the students and courses database, you can identify two distinct topics which are students and courses.
Taking your students and courses database to the first normal form would mean that you create two tables: one for students and one for courses.
The rule for the second normal
form is Second Normal Form (2NF) further addresses the concept of removing
duplicate data as follows:
1. Remove subsets of data that
apply to multiple rows of a table and place them in separate tables.
2. Create relationships between
these new tables and their predecessors through the use of foreign keys.
These rules can be summarized in a simple statement as follows:
2MF 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.
For example imagine a company that maintains customer Information in a database. They might have a single table called Customers with the following elements:
In 2NF compliant database structure redundant information like ZIP, City State extracted end stored in a separate table. Our new table (let's call it ZIPs)
Now that we've removed the duplicate data from the customers table, we’ve satisfied the first rule of second normal form. We still need to use a foreign key to tie the two tables together. We'll use the code (the primary key from the ZIPs table) to create that relationship.
Third Normal Form
There are two basic requirements
for a database to be in Third Normal Form:
The requirements of both 1NF and
2NF have been met.
Remove columns that are not fully
dependent upon the primary key.
Let assume that we have a table
of orders that contain the following attributes:
The Fourth Normal Form and Fifth Normal Form is pond the scope of this syllabus.
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
2. Attributes with a close
logical relationship are found in the same relation:
3. Minimal redundancy with each
attribute represented only once with the important exception of attributes that
form all or part of foreign keys.
Concepts of foreign keys: A foreign key us 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 tables. The foreign key identifies a column or set of columns in one (referencing or child) table that refers to a column or set of columns 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.
For example, in the Student table in a database has a link to the Studentcourse table because there is a logical relationship between sales orders and salespeople. The StudentID column in the Studentcourse table matches the primary key column of the Studenttable. The StudentID column is the primary key in Student table and is the foreign key to the Studentcourse table.
EXERCISE
1.
Explain
the term normal form
2.
Give
an example of forms of normal forms
3.
Give
an example of tables in the normal forms
4.
What
is foreign key in a database
5.