Fundamentals of databases : Relational databases
What is a relational database?
editA 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
edit
Tables consist of columns and rows which can be imagined in the same format as we may imagine a spreadsheet.
Exercise: Picking Tables List the tables you might need to store a database on a football league Answer: Team Player Match Stadium Referee List the tables you might need to record details for an online store Answer: Staff Products Receipts Customers |
Attributes
editEach 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:
Attribute | Data type |
---|---|
Name | Text |
Weight | Real |
IsNeutered | Boolean |
Records
editEach 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 | Weight | TypeOfAnimal | ... |
---|---|---|---|
Chewbacca | 8 | Dog | |
Tabitha | 3 | Cat | |
Percy | 0.25 | Fish |
Exercise: Picking attributes For a social networking site, what attributes and data types would you include for the table of people Answer: Name: string Date of Birth: Date Gender: string (character might also work here) Public profile: boolean Personal description: String For a social networking site, what attributes and data types would you include for the table of relationships (the table that stores 'friend' 'sister' 'father' etc) Answer: Person1 ID: integer Person2 ID: integer Type of relationship: string Date created: Date |
Keys
editPrimary Key
editEvery 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.
Animal_ID | Name | Weight | TypeOfAnimal | ... |
---|---|---|---|---|
1 | Chewbacca | 8 | Dog | |
2 | Tabitha | 3 | Cat | |
3 | Percy | 0.25 | Fish |
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
Example: Picking a Primary Key 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
editSometimes 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.
RoomNumber | StartDate | NumberOfGuests |
---|---|---|
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.
Example: Picking a Composite Key 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:
|
Exercise: Keys Spot the primary key in the following table attributes, and write out the table design:
Answer: Car(Registration number, Colour, Number of doors, Convertible) Spot the primary key in the following table attributes:
Answer: Student(Unique Learner Number, Name, Date of Birth, Mobile number NOT mobile number as they might share a phone with someone else. Where applicable rewrite the table description with composite keys, primary keys, or add a primary key for the following tables: Answer: Receipt(CustomerID, DateTime, Total, StaffID) Match(TeamA, TeamB, Date, TeamAScore, TeamBScore, RefID) Answer: Match(TeamA, TeamB, Date, TeamAScore, TeamBScore, RefID) Phone(ModelID, Colour, Weight, Internet) Answer: 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 Cat(Colour, Weight, NumberofLegs, Name) Answer: 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 Key
edit
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:
Animal_ID | Name | Weight | TypeOfAnimal | Owner_ID | ... |
---|---|---|---|---|---|
1 | Chewbacca | 8 | Dog | 2 | |
2 | Tabitha | 3 | Cat | 2 | |
3 | Percy | 0.25 | Fish | 6 |
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.