Information Systems 12 (E238) 2005 DB Normalisation: Worked Example

> Lawley > Information Systems > Year 12 > Databases > Normalisation > A Worked Example Page 1 of 5

Database Normalisation—A Worked Example Page 1
All text copyright by Ian Gilfillan © 2000.

Original Source: www.databasejournal.com/sqletc/article.php/1428511

Minor modifications by LXR to fit Year 12 Information Systems (E238) course work (2005), spelling (Australian English), and punctuation.

Database normalisation
22 March 2000

By now, you are familiar with the basics of using databases. Many of your databases will be small, with one or two tables. As you become braver, tackling bigger projects, you may start finding that the design of your tables is proving problematic and data anomalies start to creep in. It is time to learn about database normalisation, or the optimisation of tables.

Let’s begin by creating a sample set of data. Imagine we are working on a system to keep track of employees working on certain projects.

Table 1:

Project number	Project name	Employee number	Employee name	Rate category	Hourly rate
1023	Madagascar travel site	11	Vincent Radebe	A	$60
		12	Pauline James	B	$50
		16	Charles Ramoraz	C	$40
1056	Online estate agency	11	Vincent Radebe	A	$60
		17	Monique Williams	B	$50

A problem with the above data should immediately be obvious. Tables in relational databases, which would include most databases you’ll work with, are in a simple grid, or table format. Here, each project has a set of employees. So we couldn’t even enter the data into this kind of table. And if we tried to use null attributes to cater for the attributes that have no value, then we cannot use the Project number, or any other attribute, as a primary key. There is not much use in having a table if we can’t uniquely identify each record in it.

So, our solution is to make sure that each attribute has no sets, or repeating groups. Now we can place the data in a table.

Table 2:

employee_project table

Project number	Project name	Employee number	Employee name	Rate category	Hourly rate
1023	Madagascar travel site	11	Vincent Radebe	A	$60
1023	Madagascar travel site	12	Pauline James	B	$50
1023	Madagascar travel site	16	Charles Ramoraz	C	$40
1056	Online estate agency	11	Vincent Radebe	A	$60
1056	Online estate agency	17	Monique Williams	B	$50

Notice that the Project number cannot be a primary key on its own. It does not uniquely identify a tuple. So, our primary key must be a combination of Project number and Employee number. Together these two attributes uniquely identify one tuple. (Think about it. You would never add the same employee more than once to a project. If for some reason this could occur, you’d need to add something else to the key to make it unique.)

Go to page: 1 2 3 4 5 Next