Databases

🔑 11.02 The relational database

In the relational model each item of data is stored in a relation, a special kind of table (the name comes from mathematical set theory). A relational database is a collection of relations.

A table is created by giving it a name and then naming its attributes. In a design, the attribute names are listed in brackets after the table name (Figure 11.01):

Figure 11.01 Two tables in a database design for the theatrical agency

Each attribute is a column, and the entries beneath it are attribute values. It is only a logical view: the relational model defines no order for the columns (or rows). Some products let you open a table and look at it, but in true relational spirit you inspect data with a query.

A row is formally a tuple, though people often say "record" and call the values "fields". In Table 11.02 each tuple is one band member. A tuple is a set of atomic values: each attribute has one value or no value.

🗝️ Keys

The most important idea in the relational model is the primary key: one attribute, or a combination, whose value in every tuple exists and is unique. Every table must have one.

If several attributes are guaranteed unique, each is a candidate key; one is chosen as the primary key and the others become secondary keys. Often there is no candidate key at all, so one is invented, as MemberID was for Member. The primary key is underlined in a design.

The DBMS refuses to insert a primary-key value that already exists, so every tuple is unique, which helps data integrity. The primary key also gives a unique reference to any value a query selects.

Tables are usually related to each other through a foreign key.

In Figure 11.01 the Band table is created first, with BandName as its primary key (band names are guaranteed unique). Then Member is created with MemberID as primary key and BandName as a foreign key referencing Band. From then on the DBMS rejects any Member tuple whose BandName is not already in Band. That is referential integrity, another way the relational model protects data integrity.

🔗 11.03 Entity–relationship modelling

Database design can use stepwise refinement, a top-down method that breaks the job into simple steps, adding detail at each one (see also Chapter 12, Section 12.08). In database design that means an entity–relationship (E–R) diagram, produced by a database designer or a systems analyst working with one.

An entity (strictly, an entity type) can be a thing, a type of person, an event, a transaction or an organisation. Crucially, it must have a number of instances. An entity is something that will become a table.

Worked example 11.01

Creating an entity–relationship diagram for the theatrical agency

The starting point is a statement of the requirement:

The agency needs a database to handle bookings for bands. Each band has a number of members. Each booking is for a venue. Each booking might be for one or more bands.

Step 1: Choose the entities

Look for the nouns. Ignore "agency" (there is only one). Choose Booking, Band, Member and Venue, each with many instances. Each booking is for a gig, but the Booking entity can hold the gig data.

Step 2: Identify the relationships

This takes experience; aim not to define too many. Choose Booking with Venue, Booking with Band, and Band with Member. A Member–Venue relationship exists too, but it is handled indirectly through the others.

Step 3: Decide the cardinalities

Each line actually defines two relationships, a "forward" one and a "backward" one. Each is described as one of: one-to-one (1:1), one-to-many (1:M), many-to-one (M:1) or many-to-many (M:M).

Member–Band. One member belongs to only one band (the agency must confirm this), and one band has many members, so Member to Band is M:1. The crow's foot sits at the "many" end:

More detail: a member must belong to a band, and a band must have more than one member:

Each end now carries two symbols: one for the minimum cardinality and one for the maximum. Here they happen to match. Documenting that a member must belong to a band matters: it means no Member tuple can be created without a valid BandName in Band.

Booking–Venue. One booking is for exactly one venue; one venue can host many bookings, but a venue might never have been booked (the circle means zero):

Band–Booking. One booking can be for many bands and one band has many bookings (hopefully!), so this is M:M. A new band may have no booking yet, and a booking may be for just one band:

Step 4: Create the full E–R diagram

Name each relationship:

A reading always starts from one entity. Reading Figure 11.08 forwards and then backwards:

One Band is booked for zero or many Bookings.
One Booking is for one or many Bands.

🧩 11.04 A logical entity–relationship model

A fully annotated E–R diagram like Figure 11.07 is a conceptual model: it does not depend on any particular way of building the system. To implement it as a relational database, convert it to a logical model, starting from a simplified diagram showing only cardinalities.

• 1:M needs no further work: the entity at the "many" end gets a foreign key referencing the primary key of the entity at the "one" end.
• 1:1 has several implementation options, but such relationships are extremely rare and not needed here.
• M:M is the problem. A foreign key holds a single value, so it cannot hold the many references needed. Or: each table would need a foreign key to the other, so neither could be created first.

The solution is a link entity. For Band and Booking:

The link entity now holds two foreign keys, one referencing Band and one referencing Booking. Every entity becomes a table, so the keys can be summarised in a key table:

Table 11.04 A key table for the agency booking database

Primary keys follow the uniqueness rule. The link entity cannot use BandName or BookingID alone, but the compound primary key of both works.

🧹 11.05 Normalisation

Normalisation is a design technique for building a set of table designs from a list of data items. It can also improve existing table designs.

Worked example 11.02

Normalising data for the theatrical agency

Start from a booking data sheet the agency might use:

Ignoring headings, the data items are a set of attributes:

(BookingID, VenueName, VenueAddress1, VenueAddress2, Date,
  (BandName, NumberOfMembers, Headlining))

The outer brackets start a table design. The inner brackets mark a repeating group: several bands per booking. A relational table needs single rows and atomic values, so the only way to store this in one table is with tuples like Table 11.05, which duplicates the BookingID, the venue data and the date:

BookingID	VenueName	VenueAddress1	VenueAddress2	Date	BandName	NumberOfMembers	Headlining
2016/023	Cambridge International Theatre	Camside	CA1	23.06.2016	ComputerKidz	5	Y
2016/023	Cambridge International Theatre	Camside	CA1	23.06.2016	ITWizz	3	N
2016/023	Cambridge International Theatre	Camside	CA1	23.06.2016	DeadlyDuo	2	N

Table 11.05 Data stored in an unnormalised table

Step 1: Conversion to first normal form (1NF)

Split the data into two groups, written as table definitions with names and primary keys. The non-repeating attributes form one table:

Booking(BookingID, VenueName, VenueAddress1, VenueAddress2, Date)

The repeating group needs a compound primary key, plus a foreign key back to the first table:

Band-Booking(BandName, BookingID(fk), NumberOfMembers, Headlining)

Neither table now has data redundancy, which is one aspect of 1NF. In each table the attributes depend on the primary key.

Step 2: Conversion to second normal form (2NF)

Look at each non-key attribute: does it depend on both parts of the compound key? Any that depend on only one part move to a new table. NumberOfMembers depends only on BandName:

Booking(BookingID, VenueName, VenueAddress1, VenueAddress2, Date)
Band-Booking(BandName(fk), BookingID(fk), Headlining)
Band(BandName, NumberOfMembers)

Booking is unchanged: a table with a single-attribute key is automatically in 2NF once it is in 1NF. Band-Booking now has two foreign keys. A table in 2NF either has a single primary key, or a compound key with every non-key attribute dependent on both parts.

Step 3: Conversion to third normal form (3NF)

Look for non-key dependencies: a non-key attribute that depends on another non-key attribute. VenueAddress1 and VenueAddress2 depend on VenueName, so they move to a fourth table:

Band(BandName, NumberOfMembers)
Band-Booking(BandName(fk), BookingID(fk), Headlining)
Booking(BookingID, Date, VenueName(fk))
Venue(VenueName, VenueAddress1, VenueAddress2)

Again a new foreign key keeps the reference to the newly created table. These four tables match four of the entities of the logical E–R model, with the keys from Table 11.04. That will not always happen: a logical E–R diagram describes a 2NF set of entities, but not necessarily a 3NF set.

To summarise: in 3NF, every non-key attribute depends on the key, the whole key and nothing but the key.

🗄️ 11.06 The Database Management System (DBMS)

The database approach

A database is not just a collection of data. It is an implementation that follows the rules of a theoretical model. About 40 years ago ANSI (the American National Standards Institute) proposed a three-level model: the external level, the conceptual level and the internal level.

Views are also a security tool for the DBA. Users or groups get access rights for their view: for example, read but not amend, or access to only some of the tables.

🧰 The facilities provided by a DBMS

Databases range from a simple system for one person to a large organisation's central database. Some DBMS facilities only matter in large organisations run by a DBA.

Whatever the size, a database can be created with SQL (next section), though most DBMSs offer alternatives:

• a developer interface: tools to create tables and define attributes and their data types, and to build a user interface;
• a query processor to create and run queries; a query extracts and manipulates data;
• a report generator to present formatted output. Programmers can put queries and reports into the user interface.

DBMS functions likely to be used by a DBA

• Setting up user and programmer views and their access rights.
• The data dictionary: hidden from everyone but the DBA, it holds metadata: definitions of every table and attribute, and how the physical storage is organised.
• Performance features, especially indexes. For a table with many attributes and many tuples, an index is a secondary table holding one attribute's values (unique ones, primary or secondary key) with pointers to the matching tuples. Searching the index is much quicker than searching the full table.
• Integrity: if a transaction starts but a system problem stops it finishing, the database could be left in an undefined state. A built-in feature should prevent this.
• Backup of the stored data, as with any system.

💻 11.07 Structured Query Language (SQL)

SQL is the language a DBMS provides for every operation on a relational database. Even when a package offers high-level tools, they produce SQL behind the scenes.

Data Definition Language (DDL)

DDL creates or alters tables: the structure only, never the data. Some DDL for the database designed in Worked example 11.02:

CREATE DATABASE BandBooking;
CREATE TABLE Band (
    BandName varchar(25),
    NumberOfMembers integer);
ALTER TABLE Band ADD PRIMARY KEY (BandName);
ALTER TABLE Band-Booking ADD FOREIGN KEY (BandName REFERENCES
Band(BandName);

General points about writing SQL:

• SQL is a sequence of commands, each ending with ;, and a command can take more than one line.
• It is not case sensitive. This book uses upper case for commands and lower case for data types; table and attribute names keep their own capitals.
• Items in a list are separated by commas; the list in CREATE TABLE is enclosed in parentheses.
• A primary or foreign key can be defined inside CREATE TABLE, or added afterwards with ALTER TABLE (which can also add extra attributes).
• Every attribute needs a data type. Names vary by DBMS, but you can always set a character length in brackets: BandName varchar(25) allows up to 25 characters.
• Data-type names you may meet: character, varchar, boolean, integer, real, date, time (lower case here; often upper case elsewhere).

CREATE TABLE Band (
    BandName varchar(25),
    NumberOfMembers integer,
    PRIMARY KEY (BandName));

CREATE TABLE Venue (
    VenueName varchar(25),
    VenueAddress1 varchar(25),
    VenueAddress2 varchar(25),
    PRIMARY KEY (VenueName));

CREATE TABLE Booking (
    BookingID varchar(8),
    Date date,
    VenueName varchar(25),
    PRIMARY KEY (BookingID),
    FOREIGN KEY (VenueName) REFERENCES Venue(VenueName));

CREATE TABLE Band_Booking (
    BandName varchar(25),
    BookingID varchar(8),
    Headlining character,
    PRIMARY KEY (BandName, BookingID),
    FOREIGN KEY (BandName) REFERENCES Band(BandName),
    FOREIGN KEY (BookingID) REFERENCES Booking(BookingID));

✍️ Data Manipulation Language (DML)

DML has three uses: inserting data when the database is created, modifying or removing data, and reading data.

Inserting

INSERT INTO Band ('ComputerKidz', 5);
INSERT INTO Band-Booking (BandName, BookingID)
VALUES ('ComputerKidz', '2016/023');

The first, shorter form works if you know the attribute order. The second is safer: it names the attributes, then gives the values. Both use parentheses; each tuple needs its own INSERT; the attributes have a defined order, but the table itself has no concept of the tuples being ordered.

Querying with SELECT

A query always starts with SELECT: attributes after SELECT, table after FROM, with parts separated by spaces.

SELECT BandName FROM Band;

SELECT BandName, NumberOfMembers
FROM Band;

SELECT * FROM Band;

The * means all attributes. Attribute lists are separated by commas without parentheses. ORDER BY sorts the output:

SELECT BandName, NumberOfMembers
FROM Band
ORDER BY BandName;

BandName is unique in Band, but it repeats in Band-Booking. To list each band that has a booking only once, use GROUP BY:

SELECT BandName
FROM Band-Booking
GROUP BY BandName;

A WHERE clause adds a condition. Long queries read best on separate lines:

SELECT BandName
FROM Band-Booking
WHERE Headlining = 'Y'
GROUP BY BandName;

SELECT BandName, NumberOfMembers
FROM Band
WHERE NumberOfMembers > 2
ORDER BY BandName;

The first gives one output line per band that has headlined; the second leaves out the duos.

Aggregate functions

SUM, COUNT and AVG work on one attribute across many tuples and return one value, so they are called aggregate functions.

SELECT Count(*)
FROM Band;

SELECT AVG(NumberOfMembers)
FROM Band;

SELECT SUM(NumberOfMembers)
FROM Band;

Joining two tables

A query can use a join condition. The most common is an inner join. Each attribute is given its full name, table name then attribute name separated by a dot:

SELECT VenueName, Date
FROM Booking
WHERE Band-Booking.BookingID = Booking.BookingID
AND Band-Booking.BandName = 'ComputerKidz';

How it works: Band-Booking is searched for tuples where BandName is ComputerKidz; for each one the BookingID is noted; Booking is searched for tuples with that BookingID; for each one found, VenueName and Date are output. Some versions of SQL require an explicit INNER JOIN:

SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_field = table2.common_field;

Changing and removing data

If ComputerKidz recruits an extra member:

UPDATE Band
SET NumberOfMembers = 6
WHERE BandName = 'ComputerKidz';

Forget the WHERE clause and every band gets 6 members. DELETE needs the same care. If ITWizz disbands:

DELETE FROM Band-Booking
WHERE BandName = 'ITWizz';
DELETE FROM Band
WHERE BandName = 'ITWizz';

Deleting from Band first gives an error, because BandName is a foreign key in Band-Booking and every entry there must have a matching value in Band.

▶️ SQL playground

A real SQL engine (SQLite, compiled to WebAssembly) running in your browser, loaded with the agency's 3NF database: Band, Member, Venue, Booking, Band_Booking. Foreign keys are switched on, so the wrong-order DELETE really fails. Tap an example from the book, edit it, and press Run. Changes stay until you reset.

SELECT VenueName, Date FROM Booking ORDER BY Date DESC;

SELECT BandName, COUNT(*) FROM Band_Booking GROUP BY BandName;

SELECT Band_Booking.BandName
FROM Band_Booking, Booking
WHERE Band_Booking.BookingID = Booking.BookingID
AND Booking.VenueName = 'Cambridge International Theatre';

UPDATE Member SET BandName = 'NoSuchBand' WHERE BandName = 'DeadlyDuo';
-- fails: FOREIGN KEY constraint (referential integrity)

📌 Summary

• A database offers better ways of ensuring data integrity than a file-based approach.
• A relational database is made of tables of a special type; each table has a primary key and may contain foreign keys.
• Entity–relationship modelling is a top-down approach to database design.
• Normalisation starts with a collection of attributes and converts them into first, then second and finally third normal form.
• A database architecture gives the user a conceptual level interface to the stored data.
• A DBMS provides a data dictionary, indexing, control of user access rights and backup procedures.
• SQL includes DDL commands for establishing a database and DML commands for creating queries.

🃏 Key-term flashcards

Tap a card to flip it.

✅ Quick quiz

📝 Exam-style questions

Write your answer first, then open the model answer. These are my own mark-scheme-style points, not official mark schemes.

3 Consider the travel agency booking form (Figure below). (a) Identify an unnormalised list of attributes, distinguishing repeating and non-repeating attributes. (b) Convert to 1NF with two tables and their keys. (c) Identify the table to modify and convert it to two tables in 2NF; explain the keys. (d) Identify which part of your design is not in 3NF.[5] [3] [5] [2]›

(a)

(BookingNumber, HotelName, HotelAddress1, HotelAddress2, Rating,
 (Date, RoomType, NumberOfRooms, RoomRate))

The inner bracket is the repeating group.

(b)

Booking(BookingNumber, HotelName, HotelAddress1, HotelAddress2, Rating)
BookingRoom(BookingNumber(fk), Date, RoomType, NumberOfRooms, RoomRate)

(c) Modify BookingRoom: it has a compound key, and RoomRate depends only on RoomType, not on the whole key.

BookingRoom(BookingNumber(fk), Date, RoomType(fk), NumberOfRooms)
Room(RoomType, RoomRate)

Date and RoomType are needed in the key because the same booking has several dates and several room types on the same date. (Strictly the rate belongs to a room type at a hotel; with more than one hotel, Room's key would be HotelName + RoomType.)

(d) Booking: HotelAddress1, HotelAddress2 and Rating depend on HotelName, a non-key attribute. Fix: Hotel(HotelName, HotelAddress1, HotelAddress2, Rating) with HotelName as a foreign key in Booking.

CREATE TABLE STUDENT (
    StudentID VARCHAR(6),
    StudentName VARCHAR(20),
    StudentOtherName VARCHAR(20),
    DateOfBirth DATE);

CREATE TABLE SUBJECT (
    SubjectName VARCHAR(20),
    SubjectTeacher VARCHAR(30));

CREATE TABLE TUTORIAL (
    StudentID VARCHAR(6),
    SubjectName VARCHAR(20),
    WeekNumber INTEGER,
    Day VARCHAR(9),
    PeriodNumber INTEGER);

ALTER TABLE SUBJECT ADD PRIMARY KEY (SubjectName);
ALTER TABLE TUTORIAL ADD FOREIGN KEY (StudentID) REFERENCES STUDENT(StudentID);
ALTER TABLE TUTORIAL ADD FOREIGN KEY (SubjectName) REFERENCES SUBJECT(SubjectName);

SELECT StudentName, StudentOtherName
FROM STUDENT
ORDER BY DateOfBirth;

SELECT StudentID, FirstName
FROM STUDENT
WHERE TutorGroup = '10B'
ORDER BY LastName;

SELECT STUDENT.LastName
FROM STUDENT, CLASS_GROUP
WHERE STUDENT.StudentID = CLASS_GROUP.StudentID
AND CLASS_GROUP.ClassID = 'CS1';

6 b The programmer uses three tables: MEMBER, BOOKING and CLASS. Complete the E–R diagram.[2]›

MEMBER 1 —< M BOOKING M >— 1 CLASS. One member makes many bookings and one class has many bookings; each booking is for one member and one class. BOOKING is the link entity resolving the M:M between MEMBER and CLASS.

CREATE TABLE CLASS (
    ClassID VARCHAR(5),
    Description VARCHAR(30),
    StartDate DATE,
    ClassTime TIME,
    NoOfSessions INTEGER,
    AdultsOnly BOOLEAN,
    PRIMARY KEY (ClassID));