A database is an organised collection of data that many programs can share. A relational database stores data in linked tables and is queried with SQL (Structured Query Language).

The relational model

  • Table (relation): data about one type of thing, for example Student.
  • Record (row, tuple): one item in a table. Field (column, attribute): one property.
  • Primary key: a field (or fields) that uniquely identifies each record, never empty or repeated.
  • Foreign key: a field in one table that holds the primary key of another table, linking them.
  • Composite key: a primary key made of two or more fields.

Example tables:

Student(StudentID, Name, FormID)
Form(FormID, TutorName)

FormID in Student is a foreign key referencing Form. This one-to-many link (one form, many students) avoids repeating the tutor’s name for every student. Referential integrity means a foreign key value must match an existing primary key. Many-to-many relationships are broken down with a link table. An entity-relationship (E-R) diagram shows tables and their links.

SQL: defining data (DDL)

CREATE TABLE Student (
  StudentID INTEGER PRIMARY KEY,
  Name VARCHAR(50),
  FormID CHAR(3),
  FOREIGN KEY (FormID) REFERENCES Form(FormID)
);

SQL: manipulating and querying data (DML)

INSERT INTO Student VALUES (101, 'Ana Lee', '10A');
UPDATE Student SET FormID = '10B' WHERE StudentID = 101;
DELETE FROM Student WHERE StudentID = 101;

SELECT Name, FormID
FROM Student
WHERE FormID = '10A' AND Name LIKE 'A%'
ORDER BY Name ASC;

Useful clauses: WHERE filters rows, ORDER BY sorts, DISTINCT removes duplicates, and LIKE with % matches patterns. Aggregate functions are COUNT, SUM, AVG, MAX and MIN, combined with GROUP BY:

SELECT FormID, COUNT(*) AS Total
FROM Student
GROUP BY FormID;

Joining tables brings data together:

SELECT Student.Name, Form.TutorName
FROM Student
INNER JOIN Form ON Student.FormID = Form.FormID;

Never build queries by gluing user input into SQL strings; use parameters to prevent SQL injection.

Database management systems

A DBMS manages the database, offering data definition, queries, security (user access rights), backup and recovery, and concurrency control. Advantages over separate files: less duplicated data, consistent data, shared access and better security.

Check your understanding

What is a primary key?

  • A field that uniquely identifies each record
  • A field copied from another table
  • The first field alphabetically
  • A password

Answer: A field that uniquely identifies each record. It cannot be empty or duplicated.

What links two tables in a relational database?

  • A foreign key
  • A comment
  • A query
  • A hash

Answer: A foreign key. It holds the primary key of the other table.

Which SQL clause removes rows that do not meet a condition?

  • ORDER BY
  • WHERE
  • GROUP BY
  • JOIN

Answer: WHERE. It filters rows before results are returned.

Which statement counts the students in each form?

  • SELECT FormID, COUNT(*) FROM Student GROUP BY FormID
  • SELECT COUNT FROM Form
  • DELETE FormID
  • UPDATE Student COUNT

Answer: SELECT FormID, COUNT(*) FROM Student GROUP BY FormID. GROUP BY pairs with aggregate functions.

PreviousNext