Note that your final mark will not be saved in the system.
Relational Databases & SQL GapFill
You must fill all the gaps before clicking ‘Check Answers!’
A database is defined as a “structured set of data held in a computer”, the structure being the records, and tables that are used to store and organise that data. Databases are part of our everyday lives, from organising patient records in a hospital to managing the items of stock, their prices, descriptions and availability in an online store. In both examples it is important that the data held is correct and has no data such as a surname which is incorrectly spelled in several different tables or data such as an address stored in several different tables. This is achieved through the use of relational databases which link data in tables together using keys and foreign keys.
Example: This simple database contains three tables linked together by the primary key in the Tutor Table which becomes a key in the Student Table; data redundancy is further improved by using the Year Table's key to link it to the Tutor table.
The whole purpose of storing data in a structured way is to make it easier to search the data and create reports. The language used to search or ‘query’ the database is (SQL).
The main keywords used to create most searches are:
- – the fields that you want to return
- FROM – the tables that contain those fields
- – the criteria for selecting the records
- – how the data is to be organised, e.g. ASC or DESC
In addition special symbols called ‘wildcards’ can also be used, for example the SQL command:
SELECT * FROM Student WHERE TutorGroup = RK... would return all the data records
SQL commands can also be used to:
- INSERT INTO new data into a table, for example:
INSERT INTO Tutor (TutorGroup, TutorName, YearGroup, Room) (LV, Mr Vincent,Yr10,GT02) - DELETE FROM to delete data from the table, for example:
DELETE FROM Student WHERE StudentID = 573
- UPDATE data in a table, for example:
UPDATE Tutor Room = GT05 WHERE TutorGroup = CC
Data can also be found from more than one table by specifying the link between them, in order to find both the student record and the relevant tutor name and registration room the SQL search would be extended:
SELECT FirstName, Surname, TutorName, Room FROM Student, Tutor WHERE Student.TutorGroup = Tutor. TutorGroup AND TutorGroup = RK