Relational databases and structured query language (SQL) - AQA

A database is a collection of data organised in a way that makes it easy for a computer program to search and store information. It can be likened to an electronic filing system that allows for easy storage, access and management of data.

Databases are used in many different settings, for different purposes. For example, libraries use databases to keep track of which books are available and which are out on loan. Schools may use databases to keep student records that hold information, such as names, addresses, parental/carer contact information and medical information.

Data within a database is organised into what is known as a table structure. A table consists of rows and columns. Each row holds data related to an individual record.

A relational database is a way of organising data that can be linked based on related information. The table structures allow for efficient data management and they reduce duplication. A relational database can contain many tables.

Example

For example, a row may hold information of a student named Chloe Clarke and another row will hold information related to another student. Each column has a unique heading that serves as an identifier. The name given to each column heading is a field. Another table might be used to hold student class information.

This database has 7 fields and 6 records. Each record has six attributes. The field Student ID has been assigned a unique ID for each student. In databases this is known as a primary key.

Databases can be linked together to reduce duplication of data. This means that data is stored once and retrieved when needed. To link databases together to create a relational database, a foreign key is used. A foreign key is a field in one table that is linked to the primary key in another table. This forms a relationship between the two tables.

The TutorID table below stores details on students taking Computing at GCSE level. StudentID in the TutorGroupID table is a foreign key that links back to the students' table.

When different versions of the same data are stored in different locations on a system, this can lead to data mismatches and data inconsistency.

Data redundancy is created within a database when the same piece of data is held in several places. This leads to records with duplicated data.

Relational databases help by ensuring that data is not duplicated in many different tables. Instead it is entered and stored once, then referenced as and when needed. This helps to reduce and prevent errors such as spelling mistakes and data mismatches.

Structured Query Language is also known as SQL. It is used to create queries that interrogate the data held in a relational database. Examples are:

• ​​​SELECT queries retrieve data from the database

• INSERT queries insert new values into a database

• UPDATE queries edit values in a database

• DELETE queries delete data from a database.

The select statement is used to select data from the database and return records that meet a specific condition.

To select all the records from a table, the following command is used (where Table_Name would be replaced by the name of the table in the database):

SELECT * FROM Table_Name;

In the example above the table is called Student so the statement would be:

SELECT * FROM Student;

All records in the student table would be returned. The asterisk * is used to return all data found in the table.

The following SQL command could be used to select specific data from the table:

SELECT StudentID, Surname, Medical_Need? FROM Student;

In addition, the WHERE clause can be used to only return records that meet a specific condition:

SELECT StudentID, Surname, Medical_Need? FROM StudentWHERE Gender = ‘F’;

ORDER BY can then be added to allow ordering of the data in ascending (ASC) or descending (DSC) order:

SELECT StudentID, Surname, Medical_Need? FROM Student

WHERE Gender = ‘F’

ORDER BY Surname ASC;

The INSERT INTO statement is used to add a record to a table:

INSERT INTO Student

VALUES (S7, Matilda, Rai, F, 35 The Mall Ashford,N,B1)

The UPDATE statement is used to update the records held in a table and can be used with a WHERE clause.

The WHERE clause identifies the specific records that are to be updated. If the WHERE clause is not used, all values in a column will be updated:

UPDATE Student

SET Medical_Need? = ‘Y’

WHERE StudentID = ‘S6’

DELETE queries delete data from a database. It is important to note that using a DELETE statement without a WHERE clause will delete all records held in a table.

For example, to delete the record for Peter Jabbal, the following SQL would be used:

DELETE FROM Student

WHERE First Name = ‘Peter’ AND Surname = ‘Jabbal’;