Database concepts, DBMS and SQL

Why storing data in separate files stops working

A file-based approach means each application keeps its own file or files of records, in whatever format that application was written to expect. The exams office has a pupil file, the library has a borrower file, the catering system has an account file, and each was set up on its own. It works while the systems are small and separate, and then it stops working for reasons you should be able to list and explain.

The first is data redundancy: the same facts are stored many times over. A pupil's name, tutor group and address appear in the exams file, the library file and the catering file. Redundancy on its own wastes space, but the real damage is what follows from it. When the pupil moves house, somebody updates the address in the exams file and forgets the other two, and now the same pupil has two different addresses. That is data inconsistency, and it is the limitation that examiners most often ask you to explain. Redundancy is the cause, inconsistency is the consequence.

The second is program-data dependence. Each program contains code that knows the exact layout of its file: the length of each record, the offset at which each field starts, the order of the fields. Change the file - widen a surname field, add a field, reorder them - and every program that reads that file has to be rewritten and retested. There is also no way to ask a new question of the data without writing another program, so a report that the head of year wants today takes a programming request and a week. There is no ad hoc querying.

The third group covers security, sharing and control. Access is granted to a whole file, so any program that can open the pupil file can read and change every field in it - there is no way to let the catering staff see names but not medical notes. Two users can open and update the same file at the same time, and the last one to save overwrites the other's change, so updates are lost. There is no transaction control: if a program fails halfway through writing, the file is left half-updated with no way to roll back. Validation rules have to be repeated in every program, and nothing stops a new program bypassing them. Finally there is no shared data dictionary, so one file calls the field Surname, another Last_Name and a third FamName, in different formats and lengths.

What a relational database does differently

A relational database stores all of an organisation's data in one place as a set of tables (also called relations), and a database management system (DBMS) controls every read and write. Data is held once, in one table, and everything else refers to it. That single change removes the cause of most file-based problems: if a pupil's address exists in exactly one record, then updating that record cannot leave the address inconsistent anywhere else.

Duplication is designed out using keys instead of copied text. An order does not store the customer's name, address and town; it stores the CustomerID, and the customer's details live once in the Customer table. When the customer moves, one field changes and every order still points at the right customer. Where a copy of a value is unavoidable - a foreign key repeats values by definition - the DBMS enforces referential integrity so the copies can never point at something that does not exist.

Control moves from the program to the DBMS. Integrity rules are declared once, in the database, so every application is subject to them. Access rights are granted per user or group and can be limited to individual tables or even columns, so catering staff can be given read access to names and nothing else. The DBMS handles concurrent access with locking and transactions, so an update either completes fully or is rolled back. It also takes the backups and provides recovery from them.

Two further features matter for the exam. A data dictionary holds the description of the data - field names, types, sizes, validation rules, keys, relationships - so all programs share one definition. And indexing keeps the data quick to search as it grows: an index is a separate structure that lets the DBMS jump to the records it needs instead of reading the table from the start. Finally, users can ask new questions directly in SQL, without any new program being written, which is what makes ad hoc reporting possible.

• Redundancy is removed by storing each fact once and referring to it with a key.
• Inconsistency is prevented because there is only one copy to update.
• Program-data dependence goes because programs address tables and fields by name, not by byte offsets, and the DBMS maps names to storage.
• No ad hoc queries is solved by SQL: a new question is a new query, not a new program.
• Weak security is solved by access rights per user or group, down to column level.
• Concurrent update problems are solved by transaction control and locking, with rollback if an update fails.
• No central integrity is solved because validation and key rules are declared once in the database.
• No data dictionary is solved because the DBMS maintains one, and it is the single definition all programs use.

The terminology you must use precisely

An entity is a thing in the real world that the database needs to hold data about - a student, a book, an order. A table is the implementation of one entity: a set of rows, all with the same structure. The distinction is worth stating clearly, because an E-R diagram is drawn in entities and a physical design is written in tables. The same word 'entity' is also used for the type, while one occurrence of it - one student - is an instance.

Within a table, one row is a record, also called a tuple. One column is a field, also called an attribute. So a record is one complete set of values about one instance, and a field is one property of the entity, with a name and a data type that apply to every record. The words are interchangeable pairs: record and tuple, field and attribute, table and relation. Use whichever the question uses, and do not mix them within one answer.

A primary key is a field, or the smallest set of fields, that uniquely identifies each record in a table. It must be unique and it must never be null, and each table has exactly one. A candidate key is any field or set of fields that could serve as the primary key because it is unique and not null - StudentID, an email address, a national insurance number. One candidate key is chosen as the primary key and the rest remain candidate keys. A composite key is a primary key made of two or more fields together, which is needed in a table that resolves a many-to-many relationship.

A secondary key is a field used to search or sort the table that is not unique - Surname, Town, DateOfBirth. It cannot identify a record on its own, so it is not a candidate key, but it is the field queries will filter on, and the DBMS may build an index on it to make those searches fast. A foreign key is a field in one table that holds the value of the primary key of another table, and it is how a relationship is implemented. Unlike a primary key, a foreign key may repeat and may be null.

Foreign keys, relationships and referential integrity

Every relationship between two entities is one of three types, and the type decides where the foreign key goes. In a one-to-many relationship, one record in the first table is linked to many in the second, but each record in the second is linked to only one in the first: one customer places many orders, one tutor supervises many students, one course contains many enrolments. The foreign key always goes into the table on the many side, so CustomerID sits in Orders and repeats once per order. Putting it the other way round would need a list of orders inside a single customer record, which a relational table cannot hold.

In a one-to-one relationship, one record in each table is linked to exactly one in the other: an employee and the company car allocated to them, a student and their locker. The foreign key can go in either table, but it is usually placed in the table that is optional or less important, and it must be declared unique so that no two records can point at the same record. In an many-to-many relationship, many records in each table link to many in the other: students take many courses, and each course is taken by many students. This cannot be implemented directly, because either table would need to hold a list of values. It is resolved with a third table, sometimes called a junction or linking table, which has a foreign key to each of the two tables and a composite primary key made of both, plus any attributes that belong to the pairing itself, such as the enrolment date or the grade.

Referential integrity is the rule that every foreign key value must match an existing primary key value in the referenced table, or be null. It is enforced by the DBMS, not by the application, and it protects the links between tables from both directions. You cannot insert an order for a customer who does not exist, and you cannot change a foreign key to a value that is not in the parent table. You also cannot delete a customer who still has orders, because those orders would be left pointing at nothing - the DBMS refuses the delete unless the relationship has been set up to cascade the change or to set the foreign keys to null. If a customer's CustomerID changes, every referencing order must change with it.

Documenting a design with an entity-relationship diagram

An entity-relationship (E-R) diagram is the design document you produce before any tables exist. It shows the entities the system must hold, the relationships between them and how many records at each end take part. The conventions are simple: an entity is a rectangle labelled with a singular noun (Customer, not Customers), a relationship is a line joining two entities labelled with a verb or verb phrase (places, is allocated, enrols on), and the cardinality is written at each end of the line - 1 or M - showing how many records at that end may be involved.

Attributes are usually shown as a list inside the entity box, with the primary key underlined. That underline is not decoration: it is how the diagram records which field identifies the entity, and it is what lets the diagram be turned into tables. If you draw attributes as separate ovals, underline the primary key oval and connect the rest to their entity.

The diagram earns its keep because it converts to a design by three rules. Each entity becomes a table, with its attributes as fields and its underlined attribute as the primary key. Each 1:M relationship becomes a foreign key in the table on the many side - the key of the one side. Each M:N relationship becomes a new table containing the primary keys of both entities as foreign keys, and those two fields together become its composite primary key. A 1:1 relationship becomes a foreign key in one of the tables, declared unique.

A library shows all of this at once. The entities are Member, Copy and Loan; the relationships are that a member takes many copies over time and a copy is taken many times, which is many-to-many, so Loan is the junction table holding MemberID, CopyNo, DateTaken and DateReturned. Book is a separate entity from Copy, because one title has many physical copies. Read the diagram one line at a time and say out loud what the cardinality means - 'one member, many loans' - and errors become obvious.

Normalisation: 1NF, 2NF and 3NF

Normalisation is the step-by-step restructuring of tables so that each fact is stored once. Its purpose is to remove the update anomalies that redundancy causes: with a well-normalised design you cannot update one copy and leave another wrong, cannot insert a record without inventing unrelated data, and cannot lose data by deleting a record. There are three stages, and each one assumes the one before it: a table cannot be in 2NF unless it is already in 1NF.

First Normal Form (1NF) forbids repeating groups and multi-valued fields. Every field must hold a single atomic value, and there must be no repeating columns such as Course1, Course2 and Course3, and no field holding a list such as 'Smith, Jones'. The fix is to put one value in each field and add a row for each additional value, so a student taking three courses becomes three rows. A table in 1NF is also expected to have a primary key, which will often be composite.

Second Normal Form (2NF) requires 1NF plus no partial dependency: every non-key field must depend on the whole primary key, not on part of it. This can only go wrong when the key is composite. Take Enrolment(StudentID, CourseCode, StudentName, CourseTitle, Grade) keyed on (StudentID, CourseCode). Grade depends on both fields, which is correct. StudentName depends on StudentID alone, and CourseTitle on CourseCode alone, so both are partial dependencies and the table is only in 1NF. Splitting them out gives Student(StudentID, StudentName), Course(CourseCode, CourseTitle) and Enrolment(StudentID, CourseCode, Grade), which is 2NF.

Third Normal Form (3NF) requires 2NF plus no transitive dependency: no non-key field may depend on another non-key field. Suppose each course has one tutor, so Course becomes Course(CourseCode, CourseTitle, TutorID) and the tutor's name is added as TutorName. TutorName depends on TutorID, and TutorID is not the key, so TutorName is only indirectly dependent on the key - a transitive dependency - and the table is not in 3NF. Removing TutorName into Tutor(TutorID, TutorName) leaves Course(CourseCode, CourseTitle, TutorID), which is 3NF. The test in one sentence: every non-key field depends on the key, the whole key and nothing but the key.

The DBMS: what it does, and the tools inside it

A database management system (DBMS) is the software layer that sits between the users and applications on one side and the stored data on the other. Nothing touches the files directly: every request goes through the DBMS, which is what allows it to enforce rules and control access. The syllabus names four features it provides, and each one answers a file-based problem.

Data management, including maintaining a data dictionary. The data dictionary - sometimes called the system catalogue - is the DBMS's own store of metadata: the description of the data rather than the data. For every table it records the field names, data types, sizes, validation rules, which field is the primary key, which are foreign keys and what they reference, plus who owns the table and who may access it. Programs and users consult it rather than hard-coding a layout, which is what removes program-data dependence.

Data modelling is the DBMS's support for describing the data independently of how it will be stored: entities, attributes, relationships and constraints, expressed in the E-R diagram and then in the tables. The logical schema is the result - the DBMS's view of the structure: which tables exist, their fields and types, their keys, the relationships between them and the integrity constraints. It says nothing about where the records physically sit on disk, which files or cylinders they occupy or how they are indexed; that is the physical schema, and the DBMS is free to change it without altering the logical schema. That separation is what makes it possible to reorganise storage without rewriting programs.

Data integrity is enforced centrally, in three parts. Entity integrity means the primary key is unique and never null. Referential integrity means every foreign key matches an existing primary key or is null. Domain integrity means each value is of the right type, length and range for its field. Because the DBMS applies these rules to every user and every program, no application can bypass them.

The tools you are expected to know about are the practical face of these features. Data security is handled by access rights granted to individuals or groups - typically roles such as read, insert, update, delete and alter - so a clerk can add orders but not drop a table, and by backup procedures: regular full backups plus more frequent incremental ones, copies held off site, the transaction log kept so completed transactions can be replayed, and restores tested rather than assumed. The developer interface is the console or graphical tool in which the developer writes and tests SQL, creates tables, inspects the data dictionary and sets permissions. The query processor is the component that takes a submitted SQL statement, parses it, checks it against the data dictionary for valid table and field names, works out an efficient way to execute it - using an index where one exists - runs it and returns the result set.

DDL: creating the structure, choosing data types and declaring keys

The DBMS keeps two languages apart. Its Data Definition Language (DDL) is used for all creation and modification of the structure: making a database, making and altering tables, declaring fields and data types, and declaring keys and relationships. The DBMS records everything a DDL statement defines in its data dictionary, which is why the structure can be enforced afterwards. Its Data Manipulation Language (DML) is used for all queries and for the maintenance of the data inside those tables. The industry standard for both is SQL, Structured Query Language, which is why a query written for one relational DBMS will usually run, perhaps with small changes, on another.

The DDL statements in the syllabus are a small, fixed set, and you should be able to read any of them and write them from a description. CREATE DATABASE makes an empty database. CREATE TABLE defines a table by listing each field with its data type, and optionally declaring the primary key and any foreign keys. ALTER TABLE changes a definition that already exists: it can add a field, change a field's type, or add a primary key or foreign key to a table that was created without one.

Choosing a data type is a decision about which values are legal, so a mark is usually available for the type and for the reason. A price is REAL because it has a fractional part; a quantity in stock is INTEGER because it is a whole number; whether a book has been returned is BOOLEAN because there are only two possible values; a date of birth is DATE because it will be compared and sorted as a date, not as text. A surname is VARCHAR(30) because names vary in length and 30 characters is enough, whereas a single-character grade is CHARACTER(1) because every grade is exactly one character. Text that will be searched belongs in a text type even when it looks numeric: a phone number stored as INTEGER loses its leading zero and cannot hold spaces.

Keys are part of the definition. A primary key can be declared inline after the field, or on its own line as PRIMARY KEY (CustomerID), which is the only way to declare a composite key such as PRIMARY KEY (StudentID, CourseCode). A foreign key must be declared separately, because it has to name the table and field it references: FOREIGN KEY (CustomerID) REFERENCES Customer (CustomerID). The order matters - the referenced table and its primary key must already exist before another table can point at them - and the data types on both sides of a foreign key must match. Read any CREATE TABLE statement field by field and you can say exactly what it enforces: the data type of each field, which field identifies a record, and which field must match a record in another table.

• CREATE DATABASE School; makes a new, empty database called School.
• CREATE TABLE Customer (CustomerID INTEGER, Surname VARCHAR(30), PRIMARY KEY (CustomerID)); creates a table with two fields and declares the primary key.
• ALTER TABLE Customer ADD COLUMN Email VARCHAR(60); adds a field to a table that already exists.
• ALTER TABLE Orders ADD PRIMARY KEY (OrderID); adds a primary key to a table created without one.
• ALTER TABLE Orders ADD FOREIGN KEY (CustomerID) REFERENCES Customer (CustomerID); adds a foreign key linking Orders to Customer.

DML: querying and maintaining the data

DML covers everything done to the data once the tables exist: retrieving it with SELECT, adding records with INSERT INTO, changing them with UPDATE and removing them with DELETE FROM. SQL is a declarative language: you state which records and fields you want, and the query processor works out how to fetch them. That is why the same statement can run against a table of ten records or ten million without being rewritten.

A SELECT statement is built from clauses, and it helps to know both the order you write them in and the order they take effect. You write SELECT, then FROM, then WHERE, then GROUP BY, then ORDER BY. Logically the DBMS first takes the tables in FROM and joins them, then filters rows with WHERE, then groups the surviving rows with GROUP BY, then works out the fields and aggregates in SELECT, and finally sorts the result with ORDER BY - which is why ORDER BY can sort on a value that WHERE could never filter on.

The three aggregates you need are SUM, COUNT and AVG. Used without GROUP BY they summarise the whole table: SELECT COUNT(*) FROM Orders returns one number. Used with GROUP BY they summarise each group: SELECT CustomerID, COUNT(OrderID) FROM Orders GROUP BY CustomerID returns one row per customer. The rule that catches students out is that every field in the SELECT list that is not inside an aggregate must appear in the GROUP BY clause, because otherwise the DBMS would not know which of the group's values to show.

INNER JOIN combines rows from two tables where a condition matches, and returns only the rows that match in both. The condition is almost always foreign key equals primary key: FROM Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID. Because both tables may share a field name, qualify the field with the table name. A customer with no orders simply does not appear in the result, which is the difference between an inner join and an outer join - and outer joins are outside this syllabus.

The maintenance statements are short and easy to lose marks on. INSERT INTO names the table and the fields, then gives the values in the same order. UPDATE names the table, SET gives the new values, and WHERE chooses the records. DELETE FROM names the table and WHERE chooses the records. In UPDATE and DELETE the WHERE clause is not optional in practice: leave it out and every record in the table is changed or deleted. That single omission is the most expensive mistake in this topic.