The model of a relational database was introduced in 1970 by E. Codd. The relational model is concerned with looking at data and how that data is represented. A relational database consists of a set (two or more) of tables (or relations) that hold data and can be cross referenced (joined) to produce complex views of that data.

 

Most database designs produced by inexperienced designers consist of one data table (a FLAT FILE database). Users commonly expect this single data table to hold all of the data to be stored in the database, however it should be noted that this approach will lead to problems. It should be remembered that a database can and often does consist of more than one data table.

 

Let us assume that we have the task of designing a database that will list all students who have cars. The idea being that users can type in a car registration number (a good key field?) and find out who the owner is - a sort of mini DVLC. Also, it will be useful to type in a student's name and find out which car(s) they own.

At this stage we're going to change the structure of some of the tables in your "Student Records" database. In order that you can still access your earlier work on queries, reports and forms you need to make a backup copy of the database.

We'll continue this course using the database version called Student Records.

 

In order to record car details we could amend the design of STUDREC so that is as shown in Figure 6.1.

 

Structure of studrec (ver 1)
SREF INIT SNAME DOB GENDER RES KIDS HTOWN DISTANCE REGNO MODEL COLOUR

Figure 6.1: STUDREC with car details (ver. 1)

 

The additional fields are:

REGNO: (The cars registration number)

MODEL: (What type of Car is it?)

COLOUR: (What colour is the car?)

For the purpose of this exercise we shall assume that these three fields will suffice: if we were interested in calculating traveling expenses then it would also be necessary to include engine size and mileage.

 

The design in Figure 6.1 seems at first glance to work. However if we consider the situation where a student has more than one car (I know it's not likely, but it is possible) things become more complicated. Suppose T Osman owns a C registration Cavalier and then buys a Honda Accord (F registration). How will the current design accommodate this?

We could amend the Structure of STUDREC by adding REGNO2, MODEL2 and COLOUR2 as shown in Figure 6.2.

 

Studrec with car details (ver 2)

SREF

INIT SNAME DOB GENDER RES KIDS HTOWN DISTANCE REGNO MODEL COLOUR REGNO2 MODEL2 COLOUR2

Figure 6.2: STUDREC with car details (Ver. 2)

 

It doesn't take much thought to realise that this is a very inflexible solution. Suppose that T. Osman purchases a third car, or even a fourth or fifth, how would we store the data? It is (I hope) evident that we cannot go on adding more fields to the record.

 

A more sophisticated attempt at solving the problem would be to suggest adding a new record for each car, i.e. if T Osman owned one car there would be one record , two cars would produce two records and three cars would produce three records and so on. This is shown Figure 6.3.

 

Studrec (ver 3)
SREF INIT SNAME DOB GENDER RES KIDS HTOWN REGNO MODEL COLOUR
1 T OSMAN 29/09/53 M F 0 MGREEN C579 CUA CAVALIER WHITE
1 T OSMAN 29/09/53 M F 0 MGREEN F28 CWE HONDA WHITE
1 T OSMAN 29/09/53 M F 0 MGREEN UWJ 189 Y MAZDA RED

Figure 6.3: The Studrec Table with car details added

 

Consider the problems that this would cause.

  1. There would be unwanted duplication of data. The owners name and address and other details would be repeated for each car they own.
  2. What would happen if we deleted a record which contained details of a car that a student had sold? If it was the students only car, we would no longer have any reference to that student.
  3. There would be a danger of inconsistency between duplicate records (ROWS), e.g. different details of (say) different dates of birth may be entered by mistake when amending a record.

If T Osman moved from Millhouse Green to Thurlstone we would need to scan the entire data table to ensure that we updated each record (ROW) which applied to T Osman. This generates ample scope for error - we could easily miss a record. Doing so would lead to internal inconsistencies within the database i.e. the integrity of the data would be compromised.

 

If T Osman were to give up driving and walk to work everyday, we would need to delete all records. If we do this we will delete all reference to T Osman.

The problem areas can be summed up in three broad categories, insert, delete, and update.

 

Rule of Thumb: Always ask what happens when you add data to a database, delete data from a database and amend data in a database.

 

How can the design be altered to overcome or sidestep these problems?

The answer to this problem is to split the data table into two or more smaller files (TABLES) this is a process known as normalisation. There are a number of rules which act as guidelines to a successful split of the data (for those of you who wish to know more, refer to CJ Date [An Introduction to Database Systems Vol 1 Chapter 17] on relational databases and make sure you understand the processes of first, second, and third normalisation of data.

 

Rule of Thumb: A good design principle is "one fact in one place"