Hi 5: Part 3 - The Third Phase

(Page 2 of 3 )

The web development team (i.e. whether it is the group or an assigned group member), should sit down and make sure that the database is optimized to its full potential. The reason why this is important is so that when the web developer is programming SQL statements, the data is easily and accurately maintained.

The database developer/s must produce a design that works efficiently. So the developer must know what the difference is between good database design and bad database design.

Good and Bad Database Design

Bad Database Design

A database has bad database design if the data is redundant and command abnormalities. Usually, bad database design is a result of ill-prepared design and thought. The team should take their time at this phase, since the web site is built around a database. It would not be good practice to continue the poorly designed database throughout the whole project.

Good Database Design

Developing a database in which may be said to be of good design, fixes the problems that occur from ill-prepared design strategies. Using the Normalisation Process, the database developer/s may follow a series of steps to identify functional dependencies inside your table design which may cause a loss of vital information.

By following the steps defined by the Normalisation Process, you should have a database ready for server side scripting.

The Normalisation Process

In 1972, a paper entitled “Further Normalization of the Database Relational Model" was written by Edgar Codd. This article will discuss how Edgar’s Normalisation Process improves the efficiency of your database design.

To illustrate how the normalisation Process works, an example table will be introduced.

Zero Normal Form (0NF)

This is the name given before the database base has undergone any Normalisation procedures.

The Table

employee	division	contact_1	contact_2	wage
Adam	1	0400-000-325	0400-000-523	$400
Brian	2	0515-123-456	0515-123-654	$500
Colin	3	0358-654-321	0358-654-123	$600
David	4	0222-222-220	0222-222-022	$700
Elvin	2	0010-000-001	0010-000-100	$500

Table Abnormalities

The table in zeroth normal form suffers from the following abnormalities due to data redundancy:

• Insert Abnormalities

It would not be possible to add a division until an employee is set to the division.

• Deletion Abnormalities

If an employee is removed from the division, the information about the division’s existence may be lost.

• Update Abnormalities

If the wage is altered for a division, modifying the rest of the wages associated with that division will be required. This can be a very timely process and the chance of an error occurring due to at least one wage being missed is a likely scenario.

The First 3 Normal Forms

First Normal Form (1NF)

The first step is to make sure that there exist only atomic values (i.e. there are no repeating groups). If the table does contain atomic values then it is not yet reached 1NF and hence is in zeroth normal form. With this step the primary key is introduced if it didn’t exist previously. In this case e_id was introduced to unify the records.

e_id	employee	division	contact_number	wages
1	Adam	1	0400-000-325	$400
1	Adam	1	0400-000-523	$400
2	Brian	2	0515-123-456	$500
2	Brain	2	0515-123-654	$500
3	Colin	3	0358-654-321	$600
3	Colin	3	0358-654-123	$600
4	David	4	0222-222-220	$700
4	David	4	0222-222-022	$700
5	Elvin	2	0010-000-001	$500
5	Elvin	2	0010-000-100	$500

Insert, delete and update abnormalities occur at this stage since there still exists data redundancy. This will be fixed in 2NF.

Second Normal Form (2NF)

At this stage it is assumed that the relations are in 1NF. Now it is time to remove unnecessary functional dependencies (i.e. removes unwanted relationships within the set of attributes).

To complete this task we must do the following:

• Find the functional dependencies using primary keys.
  o {e_id} -> {employee_name, division}
  o {division}->{wage}
  o {contact_id} ->{contact_number}

The idea here is to produce a unique identifier for each primary key (i.e. move attributes with multiple values into a new table)

• Now we may use the foreign key to relate these new tables.
  
• Remove column that are not dependent on the primary key.

Database in 2NF

EMPLOYEE

e_id	employee_name	division	wages
1	Adam	1	$400
2	Brian	2	$500
3	Colin	3	$600
4	David	4	$700
5	Elvin	2	$500

CONTACTS

contact_id	relative_e_id	contact_number
1	1	0400-000-325
2	1	0400-000-523
3	2	0515-123-456
4	2	0515-123-654
5	3	0358-654-321
6	3	0358-654-123
7	4	0222-222-220
8	4	0222-222-022
9	5	0010-000-001
10	5	0010-000-100

Third Normal Form (3NF)

At this stage it is assumed that the relations are in 2NF. Now it is time to remove columns that are not dependent upon the primary key.

Database in 3NF

EMPLOYEES

e_id	employee_name	relative_division_id
1	Adam	1
2	Brian	2
3	Colin	3
4	David	4
5	Elvin	2

CONTACTS

contact_id	relative_e_id	contact_number
1	1	0400-000-325
2	1	0400-000-523
3	2	0515-123-456
4	2	0515-123-654
5	3	0358-654-321
6	3	0358-654-123
7	4	0222-222-220
8	4	0222-222-022
9	5	0010-000-001
10	5	0010-000-100

DIVISIONS

division_id	wage
1	$400
2	$500
3	$600
4	$700

Hoorah, we have created a nice database which is in 3NF. This is a nice database with no data redundacy or command abnormalities.

Testing your database

To test the database create a time variable in your chosen language. Initialise it then run a conditional loop. Once the loop has completed its cycles of SQL statements, output the current time value. Now run a series of tests to see which statements perform better. The purpose of this stage is to become familiar to the new database.

Next: Conclusion >>
 

More Web Services Articles
More By Ben Shepherd

Comments On: Hi 5: Part 3

>>> Be the FIRST to comment on this article!