database concepts

Using a database: What is a db?

The INVOICE Database

As illustrated in the diagram below, the INVOICE database consists of four linked tables.

linked tables Sample dataSample data Sample dataSample data Sample data

The table CUSTOMER is linked to table INVOICE by the use of a primary key and foreign key.

A primary key is a field that uniquely identifies a record in a table. Some examples are:

In the above tables the primary keys are in bold.

A foreign key is the primary key from one table that is placed into another table, in order to create a relationship between the two tables.

Each of the relationships in the INVOICE database is a one-to-many (1-∞). That is, a record in one table can have one or more related records in another table. For example, one customer can have many invoices, but, an invoice only ever belongs to one customer. There are three types of relationships:

  1. one-to-one. A record in one table is related to one and only one record in another table.
  2. one-to-many. A record in one table can be related to one or more records in another table.
  3. many-to-many. One or more records in a table can be related to one or more records in another table.

Relationships will be covered in depth in the second module, so do not be overly concerned about them at the moment.

The field CustABN is the primary key in CUSTOMER and also exists in INVOICE as a foreign key, where it connects each invoice to its customer.

Nearly all of the practical work in this unit is based on this database and by developing the INVOICE database, you will gain an understanding of how: