Introduction to relational databases

In the previous section we have seen how to create a database containing a single table (the product table) that can then have data added to it, amended and deleted. In addition, we were able to search and sort the data. However, most databases consist of more than a single table - we call a collection of tables a relational database.

Normalisation

In order to design a relational database you must normalise your entities so that:

  • all possible relationships between data are allowed
  • the duplication of data is minimised

Minimising duplicated data is important as it can lead to inconsistencies in the database. For example, in an unnormalised database it would be possible to have two records for the same customer. This could lead to a situation where one of the records was updated with the customer's new address and the other with their new telephone number - it could lead to all sorts of difficulties communicating with this customer.

UNF → 3NF

The goal in normalisation is to get from unnormalised form (UNF) to third normal form (3NF). If we were to include all of the data that we might want to store about each order in our coffee shop we might have the following entity description:

CustomerOrder(CustomerID, FirstName, LastName, Street, Town, PostCode, TelephoneNumber,EMailAddress, OrderID, Date, Time, ProductID, ProductName, Price, ProductType, Quantity)

The video below demonstrates the normalisation process:


Task 10

Watch the above video to gain a clearer understanding of normalisation.


Entity descriptions

Once the data has been normalised entity descriptions should be produced to provide a easy to interpret representation of each entity. The following conventions are used:

  • Primary keys are indicated by underlining the attribute name
  • Foreign keys are indicated by italicising the attribute name

For the coffee shop database we would have the following descriptions:

Customer(CustomerID, FirstName, LastName, Street, Town, PostCode, TelephoneNumber, EMailAddress)

CustomerOrder(OrderID,CustomerID, Date, Time)

OrderItem(OrderID,ProductID, Quantity)

Product(ProductID, Name, Price,ProductType)

ProductType(ProductType)

Improving convenience

The normalisation process has generated a database structure which meets the requirements but there are a couple of tweaks that we can make to improve it. Working with composite keys is not very convenient; it makes queries more complex. Therefore, one tweak is to add a surrogate integer primary key - a single column is easier to work with and it can be auto-generated.

The updated entity descriptions would look as follows:

Customer(CustomerID, FirstName, LastName, Street, Town, PostCode, TelephoneNumber, EMailAddress)

CustomerOrder(OrderID, Date, Time,CustomerID)

OrderItem(OrderItemID,OrderID,ProductID, Quantity)

Product(ProductID, Name, Price,ProductTypeID)

ProductType(ProductTypeID, Description)

Notice that the ProductType entity has a new attribute - Description. This is equivalent to ProductType in the previous description. In addition, the components of the composite key are now foreign keys.

Entity relationship diagram

Another way to visualise the design of the database is through an entity-relationship (ER) diagram. An ER diagram shows the relationships between entities and the degree of each relationship.

The possible degrees of relationship are:

Entity Degrees

Representing the coffee shop database as an ER diagram would result in the following:

Coffee Shop ER Diagram

Notice that were you have a foreign key you tend to have a one-to-many relationship. It is possible for this kind of relationship to be one-to-one but that requires additional constraints to be added when implementing the design.

Having generated the entity descriptions and entity-relationship diagram for the coffee shop database we can turn our attention to implementing the design.