In Chapter 32 we introduced databases and how data is organised into fields and records and tables. This post will go into the relational aspect of by seeing how we can define relationships between tables in a database.
Glossary
Relationship | A table is said to have a relationship with another table when there is matching data in key fields. |
Foreign Key | A special field in one table that refers to a primary key of another table. |
One-to-One | A record in one table is associated with exactly one record in another table, e.g., |
One-to-Many | A record in one table is associated with multiple records in another table. |
Many-to-Many | Multiple records in one table can be associated with multiple records in another table. |
Cardinality | The maximum number of times an instance in one entity can relate to instances of another entity. |
Optionality | The minimum number of times an instance in one entity can be associated with an instance in the related entity. |
A Game Tournament Scenario
For this topic, it is probably a good idea to go through some example scenarios that you can relate to. First, let us design a database for an online game tournament.
We have already defined a Player table. Each player record has the following fields: Full Name, Username, and Password which will be stored encrypted. We have already set our primary key as an auto-generated number called PlayerID.
We need to display an Avatar for each player and having the image data in separate table will be a good idea to keep the Player table light. How can we create a link between different table records in order to describe that a particular Player has a particular Avatar?
Make Links Using Foreign Keys
We can link one Player with one Avatar by making use of a foreign key. When one record in a table links to just one record in another table, like in our example depicted on the left, then we call that a one-to-one relationship. The Entity Relationship diagram below illustrates this notion.
The Player table has a field called AvatarID. This field is a foreign key that links to the primary key in the Avatar table. It is quite a simple idea if you think about it.
A foreign key in one table is a reference to a primary key of the other table.
Let us see how the data in each table would look like:
Defining relationships like this allows us to fetch data from different tables at the same time through a query. For example, if we wanted to fetch all the player names and avatar images of the top five players, we would be able to do it easily thanks to the foreign key relationship. We will look into building queries like this in detail in Chapter 34.
Relational Table Notation
There are a number of notations out there, but here we will present a standard notation for defining tables in text. Notice how Primary Keys are underlined, and foreign keys are in italics. Take a close look at the example below.
PLAYER ( PlayerID, Full Name, Username, Password, AvatarID ) |
AVATAR ( AvatarID, Image ) |
In order to illustrate the type of relationship between two tables we can draw an Entity Relationship diagram using crow's foot notation. You need to know about two types of relationships:
One-to-One Relationships This means that given two tables A and B, each record in A can be associated with only one record in B and similarly each record in B can be associated with only one record in table A. The notation to show a one-to-one relationship is a line as depicted below. Many real world relationships are of this nature for instance, one Employee can only have one Parking Space or one Parking Space can only be used by one Employee depending on how you look at it. For another example, one Athlete can use one Locker or one Locker can be used by one Athlete.
One-to-many Relationships This means that given two tables A and B each record in A can be associated with any number of records in B while each record in B can be associated with only one record in table A. For example, it is possible for a person to have multiple cars, therefore one Owner can have many Cars. Once again, it is not too difficult to think of real world relationships of this nature. For example one hospital Ward can house many Patients. And one Department at a store can have many Products in it. The notation to show a one-to-many relationship is a line with a crow's foot end as depicted below.
Cardinality and Optionality
Self-Service Munchies Scenario
Let's say we are building an app for customers at a restaurant to submit their own orders. Each Table will have a Tablet provided by the restaurant, with the app installed, through which a customer can submit their Order. This is quite a complicated scenario, but we start by breaking it down into entities.
Table, Zone There are Tables both inside the restaurant and outside on the terrace. Those inside are made of wood, while the ones outside are made of plastic. The entire floor space of the restaurant is divided into a number of Zones, each with a number of tables in them.
Attendant Attendants do not take orders, but they ferry the Orders out to Tables based on the Zone they are assigned. Each Attendant is assigned one Zone to service, but one Zone can have many Attendants.
Order Each Order will track the: time it was created; the total due, and, store the selected items as text for the kitchen to print out. One Table can submit many Orders. When a customer submits their order, the kitchen will prepare it and an Attendant will then bring orders out when they are done, hopefully to the right table.
Here is a database model that consists of the entities we mentioned. Note the relationships between them.
Let us see some sample data.
Well done on getting this far! By now you should be really comfortable with grouping fields into the correct entities and identify appropriate relationships between them. Just as our programs evolve, so do our data models and be prepared to make suggestions for entities and fields in exam questions. Do you have an idea of a field we can add to our current data model? For instance, how about adding a Paid field to the Order table, to be able to track whether an order has been paid or not when customers go to the till?
Referential Integrity
When designing table relationships, databases provide an option called Referential Integrity. If this feature is chosen, then the DBMS would not allow references to foreign key values that do not exist. Using our previous case study as an example, if we tried to set a Zone for Table 23 to D, this would not be allowed, since Zone is a foreign key that refers to Code in the Zone table, and does not have an entry for D.
Comments