Fundamentals of databases : Relational databases
What is a relational database?
A relational database is a method of storing data, where we divide the data to fit into specific structures which have relationships between them.
Imagine a relational database is being used by a vet. The data would be divided up into tables, each representing an entity or 'thing':
All details about animals the vet cares for would be stored inside the animal table, details about the different medicines the vet uses would be stored in the medicine table and so on.
The database also models the relationships between the entities. In the vet example, we have tables for Customer and Animal. Each customer may own one (or more) animals, and each animal belongs to one customer, so there is a relationship between the records in these two tables.
Tables consist of columns and rows which can be imagined in the same format as we may imagine a spreadsheet.
Team Player Match Stadium Referee
Staff Products Receipts Customers
Each column represents an attribute which is a labelled element of data we want to store about the entity represented by this particular table. Here are some examples of entities and possible attributes those entities might have:
Animal: Name, Weight, IsNeutered Customer: First name, Last name, Address, Post code
When the table is created, the data type for each attribute is specified. This allows the database software to allocate sufficient memory to be able to store any potential data that is input, as well as making it able to enforce a type check on the data. For example in our table for Animal we may have the following attributes:
Each row in a table represents a single instance of an entity and is called a record. For example within the table Animal we may have rows for Chewbacca the dog, Tabitha the cat and Percy the fish, each being an instance of an Animal.
Name: string Date of Birth: Date Gender: string (character might also work here) Public profile: boolean Personal description: String
Person1 ID: integer Person2 ID: integer Type of relationship: string Date created: Date
Every record in a relational database must have its own unique Primary Key (PK) attribute, which provides a way of identifying that specific record out of all of the records in the entire table.
The key attribute must be chosen carefully so that it is truly unique. For example we could not choose the attribute "Name" as the primary key for our Animal table because it is extremely likely that two animals brought to the vet will have the same name.
A common method of ensuring a unique Primary Key field is to allow the database software to automatically generate the next integer in sequence when a new record is added. The Primary Key attribute is often named <TableName>ID (in this example Animal_ID), although this is not compulsory.
When writing table designs in shorthand, the Primary Key field is usually written first and underlined:
TableName(PrimaryKey, Attribute, Attribute, Attribute, Attribute) Animal(Animal_ID, Name, Weight, TypeOfAnimal)
Alternatively, in an Entity Relationship Diagram, the Primary Key may be denoted by a *
Animal_ID* Name Weight TypeOfAnimal
Let's look at an example in the Criminal Table:
Which of these data items are unique?
We can then write the tables as:
Criminal(NI Number, Name, Date of Birth, Number of scars, Home town)
Composite Primary Key
Sometimes a combination of attributes are used together as a key instead of one single attribute. The uniqueness is only guaranteed when the attributes are combined together. For example, in a table concerning hotel bookings, a composite key could be created by combining the RoomNumber attribute and the attribute for the StartDate of the stay. It is not possible to have two different bookings for the same room starting on the same date, so this composite key uniquely identifies each record.
|8||24 April 2016||2|
|2||12 May 2016||1|
|8||6 July 2016||2|
RoomNumber on its own is insufficient as a unique identifier as lots of bookings are made for the same room. StartDate on its own is insufficient as a unique identifier as lots of bookings are made which start on the same date.
Take a look at this example for houses:
We could make a primary key up, but if you look carefully, we can use a combination of attributes. The house number and the road name combined seem to be unique. You can't have two 45 Belmont Close can you?.
Does that sound ok? What about if we were storing data on all the towns in the country and there was a 5 London Road in Manchester and a 5 London Road in Winchester. This would mean that the combination was not unique. We might try using the house number and post code instead, and this combination is always unqiue, this is our composite key:
Car(Registration number, Colour, Number of doors, Convertible)
Student(Unique Learner Number, Name, Date of Birth, Mobile number
NOT mobile number as they might share a phone with someone else.
Receipt(CustomerID, DateTime, Total, StaffID)
Match(TeamA, TeamB, Date, TeamAScore, TeamBScore, RefID)
Phone(ModelID, Colour, Weight, Internet)
You might argue that if this was for a particular instance of a phone instead of for a model of phone you'd need to introduce a unique value through serial number or the like
There aren't enough fields here for us to find a unique one or combination. We could have two ginger cats weighing 1 kg with three legs called Phil. We therefore need to introduce a new unique value, CatID Cat(CatID, Colour, Weight, NumberofLegs, Name)
Foreign keys are used to link tables together and cross reference between tables. For example here is the Animal table from our vet example with an added foreign key of Owner_ID:
Owner_ID is a foreign key in the table Animal because it is the primary key of the Owner table.
The foreign key can be used to look up any of the other attributes of the owner, because it corresponds to an Owner_ID in the Owner table, which is the primary key and thus is guaranteed to refer to exactly one of the records.