Relationships – Relationships in a relational database are used to prevent data that is not needed and also prevents you from putting the same data in twice. Relationships work by matching data in columns that normally have the same name in multiple tables.
For example a database that is used to keep information about books, there may be a table called Titles that has the information about each book such as the title, publisher and when the book was published then there is another table that has information about publishers which could have the publishers phone number and address.
So the relationship for this database is when a book has been selected the publishers details are shown and not duplicated on more than one table. If the publisher’s details were put in the first table then for each title the publisher prints the phone number and address would be duplicated.
This image shows a relational database with customers, orders and order details.
This shows not the actual tables but the relationships between the tables.
You can see that the customer table has all the details but no order details. The database sees the ‘customerID’ (primary key) in the orders table and uses that to find the customer details from the customers table.
Entities – An entity is anything represented in the database that is a real-world objects such as a person for example a member or customer, or it could be a transaction for example a sale or delivery. Anything
Attributes – An attribute is anything or the title of a column on a table. For example, if the table is used for customer details then it would have attributes such as – customer’s name, address, telephone number ect.
Key Benefits – There are many benefits of a relational database. The first key benefit is that it keeps errors in the database to a minimum as there’s only a single storage location for a piece of information which means info is always up-to-date and there no chance of old data remaining giving wrong information.
Another key benefit is that data will never be duplicated when looking for a particular piece of information as the address or number for example only needs to be entered once. It is also user friendly for people who don’t or find it difficult to use a computer. Navigating around a database is very simple when shown how.
Tables - The tables in a relational database is set out in rows and columns. For an example of a table, the image above shows how the table is set out with the fields, rows and colons. A table is also just another term for relations.
Primary Key and Foreign Key
The primary key is what sets up the relationships within the database. The primary key must be unique so the attribute must not repeat which means it will normally be a number of some kind for example if the Table was to keep a record for students the student ID would be used as no student will have the same ID as another student.
The primary key defines the relationship with multiple tables by becoming a foreign key in the next table so they link, the foreign key will remain the primary key in the first table. I will use the same example as above to show primary and foreign keys.
This is a one-to-many relationship
Here you can see the Primary keys in bold are ‘Customer ID’ for the customers table, ‘OrderID’ for the orders table and for the order details table the key is ‘ProductID’
For the relationship to work the primary key must be in the next table and be a foreign key, this is what gives the database its referential integrity.
Referential Integrity
Referential integrity is a concept where the relationships must always be consistent. The foreign keys in a relational database must match the primary key in the first table which means any primary key changes must be applied to the foreign keys. This is also true of the foreign keys in that any updates must be also made to the primary key.
As an example a database with two tables, one with customers which holds details like name, DOB, address and national insurance number and a table for accounts that has details like the account type, account creation date and account holder.
The Primary key for the customers table will be called Customer_ID, to identify a customer and their own personal bank account details in the account table then an existing customer in the customers table must be referenced. That means the Customer_ID (The primary key) column is created in the accounts table which is the foreign key. The foreign key column references existing and identical values in the primary key column in the customers table.
The referential integrity here means that any Customer_ID value in the customers table may not be edited without the same field being edited as well in the accounts table. For example if a customer called John Smiths ID is changed in the customers table then the change must also be applied in the accounts table in the foreign key column so that the data links.
The account table doesn’t hold information about customers apart from the Customer_ID as a foreign key. This is the same for customer ID in the accounts table for example if a customer ID is without a Customer_ID in the customer table then the bank account wouldn’t exist.
If a Customer_ID is deleted in the customer table then all of the entries to do with that unique number will be deleted from the accounts table. This is the same if the customer is deleted from the database, each bank account linked to that customer must be deleted.
Another benefit of referential integrity is that some database systems don’t allow the foreign and primary keys to be edited at the same time, instead the primary key must be deleted before the foreign key can be edited.
To avoid this a database should be design with a fixed primary key with a unique value to each record, normally a number.
Types of relationships
Relationships work by matching data in columns between tables; they usually have the same name in both tables, usually the relationship matches the primary key from one table which provides an identifier (usually a number)
There are three types of relationships in a relational database –
One-To-Many Relationships – A one-to-many relationship is the relationship that is most often used in databases. This is where one row in ‘Table A’ has more than one matching row in ‘Table B’, but a row in ‘Table B’ will only one matching row in ‘Table A’.
Using the example from earlier, the Publishers and Titles tables have a one-to-many relationship: each publisher produces many titles, but each title comes from only one publisher.
A one-to-many relationship is created if only one of the columns is a primary key.
Many-To-Many Relationships – In a many-to-many relationship a row in ‘Table A’ will have more than one matching row in ‘Table B’ and the same will be said the one other way round as in ‘Table B’ will have more than one matching row in ‘Table A’.
This is possible by creating a third table called a junction table where the primary key consists of the foreign keys from the first two tables.
For example, the Authors table and the Titles table have a many-to-many relationship that is defined by a one-to-many relationship from each of these tables to the TitleAuthors table. The primary key of the TitleAuthors table is the combination of the author’s table’s primary key column and the Titles table’s primary key
One-To-One Relationships – In a one-to-one relationship a row in ‘Table A’ will only have one matching row in ‘Table B’. A one-to-one relationship happens is both of the related columns are primary keys.
This is the relationship that gets used the least because most information that is related with the same primary key would most commonly be in the same table. Some of the reasons a one-to-one relationship is to be able to divide a table with many columns or store data that is not going to be used for long and can deleted easily.
