Databases

A database is an organised collection of data stored so that it can be searched, updated and shared. Paper 1 asks you to explain database concepts, draw and normalise table designs, and write SQL scripts. Normalisation and SQL are the two highest-scoring topics here.

8.1 Database concepts

Limitations of a file-based approach

In a file-based system, each application keeps its own data files. Problems:

• Data redundancy: the same data (for example a customer address) is held in several files.
• Data inconsistency: the copies get out of step when only one is updated.
• Data dependency: the program is tied to the file's format, so changing the file structure means rewriting the program.
• Data is hard to search and combine, hard to share between users safely, and has limited security and little control over simultaneous access.

A relational database removes duplication by splitting the data into related tables, so each fact is stored once.

Terms

| Composite key | A primary key made from two or more fields together | | Index | A separate structure that stores sorted values of a field with pointers to the records, so searching is faster; it takes extra space and slows down updates |

Relationships and entity-relationship diagrams

A relationship shows how records in one table relate to records in another.

• One-to-one (1:1): one record in table A is linked to at most one in table B (a person and their passport).
• One-to-many (1:M): one record in A is linked to many in B (one teacher teaches many students; one customer places many orders). It is implemented by putting the primary key of the "one" table in the "many" table as a foreign key.
• Many-to-many (M:N): many records in A relate to many in B (students and subjects). This cannot be implemented directly in a relational database. It is resolved with a link (junction) table whose primary key is made of the two foreign keys, giving two one-to-many relationships.

An entity-relationship (E-R) diagram shows the entities (tables) as boxes and the relationships as lines joining them, with the crow's foot at the "many" end. For example:

CUSTOMER ||------o{ ORDER ||------o{ ORDER_LINE }o------|| PRODUCT

Tap to enlarge

An entity-relationship diagram of four tables, CUSTOMER, ORDER, ORDER_LINE and PRODUCT, joined by one-to-many relationships shown with a single bar at the one end and a crow's foot at the many end; primary and foreign keys are marked

Close

Normalisation

Normalisation is the process of organising data into tables to reduce redundancy and avoid inconsistency. Each step removes a type of problem.

Worked example. A table of orders:

ORDER(OrderID, OrderDate, CustomerID, CustomerName, CustomerCity, ProductID, ProductName, Price, Quantity) with key (OrderID, ProductID).

• 2NF: OrderDate and CustomerID depend only on OrderID; ProductName and Price depend only on ProductID. These partial dependencies are split into new tables: ORDER(OrderID, OrderDate, CustomerID, CustomerName, CustomerCity), PRODUCT(ProductID, ProductName, Price), ORDER_LINE(OrderID, ProductID, Quantity).
• 3NF: in ORDER, CustomerName and CustomerCity depend on CustomerID, not on the key OrderID. They are moved out: ORDER(OrderID, OrderDate, CustomerID), CUSTOMER(CustomerID, CustomerName, CustomerCity).

The result: each fact is stored once, so there are no update anomalies. In an exam, show each table with its primary key underlined and foreign keys marked, and name the dependency that you removed.

Tap to enlarge

A table ORDER with key OrderID and ProductID holding customer and product details is split in second normal form into ORDER, ORDER_LINE and PRODUCT, and in third normal form the customer details move into a CUSTOMER table, so each fact is stored once

Close

8.2 Database management systems (DBMS)

A database management system (DBMS) is the software used to create, manage and use a database. Its features:

• Data management: it carries out all the storing, retrieving, changing and deleting of data. Programs and users do not touch the files directly.
• Data dictionary: a store of data about the data: for each table, the field names, data types, field sizes, validation rules, primary and foreign keys, indexes, and the relationships. The DBMS uses it to check that data is valid.
• Data modelling: tools to design the structure of the database and its relationships (for example E-R diagrams) and the logical schema.
• Logical schema: the overall logical design of the database, the tables, fields and relationships, described independent of how it is physically stored. A user view (external view) shows only the part each user needs.
• Security: controls who can see and change data using user accounts, passwords and access rights (for example, a teacher may read exam marks but a student cannot). Views can hide sensitive fields; data can be encrypted.
• Backup and recovery: tools for regular backups and for restoring data after a failure, and a log of transactions.
• Integrity: enforces referential integrity (a foreign key must match an existing primary key) and validation rules, and controls simultaneous access (locking records) so two users do not make clashing changes.
• Developer interface: lets a developer create tables, forms, queries and reports, usually using a graphical tool or a query language. Typical tools are query processing (running queries), report generators, and data entry forms.

8.3 Data definition language (DDL) and data manipulation language (DML)

SQL (Structured Query Language) is the standard language for relational databases. It has two parts:

• Data Definition Language (DDL) creates and changes the structure: CREATE, ALTER, DROP.
• Data Manipulation Language (DML) works with the data: SELECT, INSERT, UPDATE, DELETE.

The DBMS carries out all the creation, changing and removal of data when the SQL is run.

DDL: defining the structure

CREATE DATABASE School;

CREATE TABLE Student (
    StudentID  INTEGER      PRIMARY KEY,
    FirstName  VARCHAR(30)  NOT NULL,
    Surname    VARCHAR(30)  NOT NULL,
    DateOfBirth DATE,
    Fees       REAL,
    Active     BOOLEAN
);

CREATE TABLE Result (
    StudentID  INTEGER,
    SubjectID  CHAR(4),
    Mark       INTEGER,
    PRIMARY KEY (StudentID, SubjectID),
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID)
);

ALTER TABLE Student ADD Email VARCHAR(50);

Common data types: CHAR(n) (fixed length text), VARCHAR(n) (variable length text), BOOLEAN, INTEGER, REAL, DATE, TIME. A composite primary key is given as PRIMARY KEY (field1, field2), and a foreign key is declared with FOREIGN KEY ... REFERENCES table(field).

DML: querying and changing data

SELECT returns data. Its main clauses, in order:

SELECT   FirstName, Surname            -- the fields wanted (or * for all)
FROM     Student                       -- the table
WHERE    Fees > 100 AND Active = TRUE  -- the condition
ORDER BY Surname ASC;                  -- ASC (default) or DESC

• WHERE uses the comparison operators =, <>, <, >, <=, >=, with AND, OR, NOT, and LIKE with % for any characters (WHERE Surname LIKE 'Mc%') and BETWEEN.
• Aggregate functions work on groups of records: SUM(), COUNT(), AVG(), MAX(), MIN().
• GROUP BY groups records with the same value so the aggregate is worked out for each group.

SELECT   SubjectID, COUNT(*) AS Entries, AVG(Mark) AS MeanMark
FROM     Result
GROUP BY SubjectID
ORDER BY MeanMark DESC;

Joining tables. Use INNER JOIN to combine records from two tables where the keys match:

SELECT   Student.Surname, Result.SubjectID, Result.Mark
FROM     Student
INNER JOIN Result ON Student.StudentID = Result.StudentID
WHERE    Result.Mark >= 70;

INSERT, UPDATE and DELETE change the data:

INSERT INTO Student (StudentID, FirstName, Surname, Fees, Active)
VALUES (1012, 'Amina', 'Khan', 150.00, TRUE);

UPDATE Student
SET    Fees = Fees * 1.05
WHERE  Active = TRUE;

DELETE FROM Student
WHERE  StudentID = 1012;