relational-databases

Relational Databases

What is a database?

• A database is an organised collection of data

• It allows easy storage, retrieval, and management of information

• Electronic databases offer a number of key benefits:

  • Easier to add, delete, modify and update data

  • Data can be backed up and copied easier

  • Multiple users, from multiple locations, can access the same database at the same time

Database terminology

Primary, Foreign & Secondary Keys

Visualising a database

• This database has 2 tables : orders and customers

• Fields:

  •  In the orders table are order_id, product, total and customer_id

  • In the customers table are customer_id, first_name, last_name, phone and country

• A record would be 1 complete row in either table

• order_id is the primary key for the orders table
  customer_id is the primary key for the customers table

• In the orders table customer_id is a foreign key – it links an order back to the customer that made the order in the customer table

Indexing (secondary keys)

• Indexing is a technique used to speed up data retrieval in a database

• It works in a similar way to the index in a book 

• If a student had a maths book and wanted to find the section on factorising, they could start at the first page and look at each page in turn until they found the section they wanted 

• But this would be slow, so it is better to look in the index to find where the factorising section is and just go directly to it 

• Likewise in a database, certain columns can be indexed so that the DBMS does not have to look at every single record during a search and can just go to the relevant records directly 

• This can greatly speed up searches 

• Fields that are indexed are known as secondary keys

Flat File vs Relational Database

• A flat file database is one that stores all data in a single table

• It is simple and easy to understand but causes data redundancy, inefficient storage and is harder to maintain

• A relational database is one that organises data into multiple tables 

• It uses keys to connect related data which reduces data redundancy, makes efficient use of storage and is easier to maintain

• Consider this example flat file table of students 

• This table has redundant data – the tutor and form room information repeats 

• This is inefficient

• If a tutor changed their name we would need to find all instances of that name and change them all

• Missing any would mean the table had inconsistent data

• A relational database would solve this issue:

  • A new table could be created to store the tutor information and the tutor information in the student table could be moved to the new table. Then a foreign key in the student table (TutorID) could link a student to their tutor

• Now the name of each tutor and their form room is stored only once

• This means if they change only one piece of data, the data is updated in the entire database and Inconsistency is avoided