Relational Databases

Overview

https://www.youtube.com/watch?v=fvkzrlMVt1s&list=PLCiOXwirraUDUYF_qDYcZV8Hce8dsE_Ho&index=163

TRC PowerPoint

https://studentthomrothac-my.sharepoint.com/:p:/g/personal/wayne_jones_thomroth_ac_uk/EXg3YB2Z8xFEvC18Vg99aroBupFTCHNQH3NKQLoXMMw8RA?e=YSWja5

What is a Relational Database

A database made up of several related tables. Instead of saving the data in a single large table the data is broken down using normalisation into several tables with the relationships created by using primary and foreign keys.

Terms

Entity

This is an object or thing of interest to an organisation about which data is to be recorded. One entity will translate to one table within your relational database.

Each row of the table will store information about a single instance of the entity. This is often called a record.

Attribute

An attribute is a property you wish to store about an entity. These will essentially be the fields with a database table, and will be stored for each instance of the entity.

Primary Key

each record or row within a table must be unique, ie each instance of the entity is only stored once within the table. The primary key is therefore the field or fields which make each record unique. If you think about a car, each has a unique registration number and would therefore make a good primary key. College has given each student a unique student number, this will allow college to cope with students who have the same name.

Composite Key

A composite key is a primary key that contains multiple fields. For example if you think about examinations, if you had a field to identify each individual and used this as the primary key then each individual could only ever take a single examination. If you have a field to identify each examination and used this as the primary key then you would only be able to allow a single student to take each examination.

The answer is to use a composite key of Student ID and Exam ID. This would allow any student to take any examination, but they can only take it once.

Foreign Key

When the primary key of one table is used as a normal field in another table.

Referential Integrity

There must be a corresponding value in the primary table when a foreign key is used in a table. This means that you will be prevented from adding customer orders for a customer that doesn't exist. This means you will be prevented from deleting a student record if that student has entries in other tables.