Planning a Database
We give here a simple example of avoiding redundancy by normalisation. For a more rigorous treatment and circumstances when complete normalisation is undesirable see, for example, this web page. As expected, there is much on the web about the topic.
Our example is a database containing details of the earlier contributions to this website. We are very familiar with these and can spot most errors readily. You too should begin trials with simple sets of data that you know well.
The starting list of our fields (columns) is:
Program name (up to 20 characters, unique)
Program description (up to 50 characters)
Program category (up to 20 characters)
Programmer ID (integer, unique for each programmer)
Programmer forename (up to 15 characters)
Programmer surname (up to 15 characters, not essential)
Programmer year group for first contribution (almost dependent on age)
Programmer approx age for first contribution (Integer, must be more than 10 and less than 20)
We could put all the data into a single flat file containing one row of all of these columns for each program. There would then be a lot of redundancy because programmers who have contributed more than one program will have their details repeated for each of their programs. Redundancy is avoided by splitting the table into two, one for programmers and the other for programs. These tables have a one-to-many relationship because each programmer can contribute several programs and each program is contributed by one programmer. We need to add the Programmer ID to each program so that we can use it to link the data in the two tables. Used in this way it is a foreign key. A foreign key is an attribute (column) in one table that is a primary key (unique identifier) in another.
Table Programs will consist of the fields Name (primary key), Description, Category and ProgrammerID (foreign key) and Table Programmer will comprise ID (primary key), Forename, Surname, YearGroup and Age. These are as intended in Boyce-Codd Normal Form (BCNF) because in each table every property which is not part of the primary key describes the key and nothing but the key. The DBMS ensures that each value of a primary key is unique and also that, for a foreign key, the same value for the key that it references in the other table must exist. You will not be able to delete a programmer without first deleting his/her programs.
The following sections show you how to set up Firebird then create a database containing both of these tables.