Chapter overview and learning outcomes
This chapter develops the conceptual and practical foundations of database management systems. It connects theory with real systems, technical decision-making and responsible computing practice.
- Explain the main concepts and use technical terminology accurately.
- Interpret and create relevant models, diagrams or worked examples.
- Apply the concepts to realistic computing problems.
- Compare alternative methods using evidence and stated assumptions.
- Identify limitations, security concerns and operational risks.
1. Database concepts and database management systems
A database is an organised collection of related data maintained to support defined operations and users. A database management system (DBMS) provides facilities to define structures, store and retrieve data, enforce constraints, manage concurrent access, control permissions and recover from failures. A database system includes the database, DBMS software, applications, people, procedures and computing infrastructure. The DBMS separates logical data organisation from many physical storage details, reducing application dependence on file layouts.
Applied example: Consider a realistic university, business or public-service information system. Identify the users, inputs, outputs, constraints and failure conditions. Trace the relevant operation from beginning to end, record what the system should guarantee, and explain how the design can be tested. Compare a reasonable alternative and discuss trade-offs in performance, reliability, security, cost and maintainability.
Learning activity: Create a labelled diagram or small worked example, explain the terminology in your own words, and prepare one test question that checks application rather than simple recall.
2. Database architectures and data independence
A DBMS may use a centralised, client-server, distributed or cloud-hosted architecture. In a three-schema view, the external level represents user-specific views, the conceptual level describes the overall logical structure, and the internal level describes physical storage. Logical data independence means changing the conceptual schema need not require changes to every external view. Physical data independence means storage changes can be made without changing the logical schema. These abstractions support maintainability and controlled evolution.
Applied example: Consider a realistic university, business or public-service information system. Identify the users, inputs, outputs, constraints and failure conditions. Trace the relevant operation from beginning to end, record what the system should guarantee, and explain how the design can be tested. Compare a reasonable alternative and discuss trade-offs in performance, reliability, security, cost and maintainability.
Learning activity: Create a labelled diagram or small worked example, explain the terminology in your own words, and prepare one test question that checks application rather than simple recall.
3. Requirements analysis and entity-relationship modelling
Database design begins by identifying stakeholders, data requirements, business rules, transactions, privacy needs and expected workload. Entity-relationship (ER) models describe entity types, attributes and relationships. An entity represents a distinguishable object or concept; an attribute records a property; a relationship connects entities. Cardinality describes how many instances may participate, such as one-to-one, one-to-many or many-to-many. Optionality indicates whether participation is mandatory. Resolve ambiguous rules with users before implementing tables.
Applied example: Consider a realistic university, business or public-service information system. Identify the users, inputs, outputs, constraints and failure conditions. Trace the relevant operation from beginning to end, record what the system should guarantee, and explain how the design can be tested. Compare a reasonable alternative and discuss trade-offs in performance, reliability, security, cost and maintainability.
Learning activity: Create a labelled diagram or small worked example, explain the terminology in your own words, and prepare one test question that checks application rather than simple recall.
4. Relational model, keys and integrity constraints
The relational model represents data as relations, commonly implemented as tables. Rows represent tuples and columns represent attributes. A candidate key uniquely identifies a row; a primary key is the selected candidate key. A foreign key references a candidate or primary key in another relation. Entity integrity requires primary key values to be unique and non-null. Referential integrity requires foreign-key values to match referenced values or follow an explicitly permitted null rule. Domain constraints restrict valid attribute values.
Applied example: Consider a realistic university, business or public-service information system. Identify the users, inputs, outputs, constraints and failure conditions. Trace the relevant operation from beginning to end, record what the system should guarantee, and explain how the design can be tested. Compare a reasonable alternative and discuss trade-offs in performance, reliability, security, cost and maintainability.
Learning activity: Create a labelled diagram or small worked example, explain the terminology in your own words, and prepare one test question that checks application rather than simple recall.
5. Relational algebra and relational operations
Relational algebra provides formal operations for manipulating relations. Selection filters rows, projection chooses columns, union combines compatible relations, difference finds rows in one relation but not another, and Cartesian product pairs rows. Joins combine related tuples using matching conditions. These operations provide a theoretical basis for SQL query processing. Understanding them helps students reason about query meaning, identify unnecessary intermediate results and recognise how query optimisers may reorder equivalent operations.
Applied example: Consider a realistic university, business or public-service information system. Identify the users, inputs, outputs, constraints and failure conditions. Trace the relevant operation from beginning to end, record what the system should guarantee, and explain how the design can be tested. Compare a reasonable alternative and discuss trade-offs in performance, reliability, security, cost and maintainability.
Learning activity: Create a labelled diagram or small worked example, explain the terminology in your own words, and prepare one test question that checks application rather than simple recall.
6. SQL: data definition and manipulation
Structured Query Language (SQL) is used to define, query and manipulate relational data. DDL statements such as CREATE and ALTER define schema objects. DML statements such as INSERT, UPDATE and DELETE modify rows. SELECT retrieves data using projections, filters, joins, grouping, aggregation and ordering. Constraints should be declared in the database where appropriate, not only in application code. Use parameterised queries for user-supplied values to reduce SQL injection risk.
Applied example: Consider a realistic university, business or public-service information system. Identify the users, inputs, outputs, constraints and failure conditions. Trace the relevant operation from beginning to end, record what the system should guarantee, and explain how the design can be tested. Compare a reasonable alternative and discuss trade-offs in performance, reliability, security, cost and maintainability.
Learning activity: Create a labelled diagram or small worked example, explain the terminology in your own words, and prepare one test question that checks application rather than simple recall.
7. Normalization and relational design
Normalization organises relations to reduce redundancy and undesirable update anomalies. First normal form requires atomic attribute values under the chosen model. Second normal form removes partial dependency of non-key attributes on a proper subset of a candidate key. Third normal form removes relevant transitive dependencies; Boyce-Codd normal form strengthens determinant requirements. Normalization is guided by functional dependencies and candidate keys. Denormalization may sometimes improve read performance, but should be measured and documented.
Applied example: Consider a realistic university, business or public-service information system. Identify the users, inputs, outputs, constraints and failure conditions. Trace the relevant operation from beginning to end, record what the system should guarantee, and explain how the design can be tested. Compare a reasonable alternative and discuss trade-offs in performance, reliability, security, cost and maintainability.
Learning activity: Create a labelled diagram or small worked example, explain the terminology in your own words, and prepare one test question that checks application rather than simple recall.
8. Transactions and ACID properties
A transaction is a logical unit of database work. Atomicity means all operations in the transaction succeed or the transaction is rolled back. Consistency means constraints remain satisfied. Isolation controls the visibility of concurrent work. Durability means committed changes survive failures within the guarantees of the system. Transactions are essential for tasks such as transferring funds, registering students or updating inventory, where partial completion could leave data inconsistent.
Applied example: Consider a realistic university, business or public-service information system. Identify the users, inputs, outputs, constraints and failure conditions. Trace the relevant operation from beginning to end, record what the system should guarantee, and explain how the design can be tested. Compare a reasonable alternative and discuss trade-offs in performance, reliability, security, cost and maintainability.
Learning activity: Create a labelled diagram or small worked example, explain the terminology in your own words, and prepare one test question that checks application rather than simple recall.
9. Concurrency control and recovery
Concurrent transactions can cause lost updates, dirty reads, non-repeatable reads and phantom rows depending on isolation guarantees. Locking, multiversion concurrency control and serialisation techniques help preserve correctness. Deadlocks can occur when transactions wait cyclically for resources; systems detect or prevent them using strategies such as timeout or victim rollback. Recovery uses logs, checkpoints and backups to restore a consistent state after crashes or operational mistakes.
Applied example: Consider a realistic university, business or public-service information system. Identify the users, inputs, outputs, constraints and failure conditions. Trace the relevant operation from beginning to end, record what the system should guarantee, and explain how the design can be tested. Compare a reasonable alternative and discuss trade-offs in performance, reliability, security, cost and maintainability.
Learning activity: Create a labelled diagram or small worked example, explain the terminology in your own words, and prepare one test question that checks application rather than simple recall.
10. Indexes, query processing and optimisation
An index is an auxiliary structure that speeds up retrieval by helping locate rows without scanning an entire table. B-tree indexes support ordered lookup and range queries; hash indexes suit equality lookup in supported systems. Indexes consume storage and add work to inserts, updates and deletes. A query optimiser estimates costs and selects an execution plan using available statistics, indexes and join strategies. Examine query plans and measure realistic workloads before adding indexes.
Applied example: Consider a realistic university, business or public-service information system. Identify the users, inputs, outputs, constraints and failure conditions. Trace the relevant operation from beginning to end, record what the system should guarantee, and explain how the design can be tested. Compare a reasonable alternative and discuss trade-offs in performance, reliability, security, cost and maintainability.
Learning activity: Create a labelled diagram or small worked example, explain the terminology in your own words, and prepare one test question that checks application rather than simple recall.
11. Database security, administration and distributed data
Database security includes authentication, least-privilege authorisation, role-based access, encryption, auditing, secure configuration and patch management. Backups should be protected, tested and retained according to recovery objectives. Replication maintains copies for availability or read scaling; sharding partitions data across nodes. Distributed systems must consider network failure, replication lag, consistency and transaction coordination. Compliance duties vary by jurisdiction and data type, so minimise collection and define retention and access policies.
Applied example: Consider a realistic university, business or public-service information system. Identify the users, inputs, outputs, constraints and failure conditions. Trace the relevant operation from beginning to end, record what the system should guarantee, and explain how the design can be tested. Compare a reasonable alternative and discuss trade-offs in performance, reliability, security, cost and maintainability.
Learning activity: Create a labelled diagram or small worked example, explain the terminology in your own words, and prepare one test question that checks application rather than simple recall.
Conceptual learning diagram. Interpret each stage in the context of the chapter topic.
Conceptual learning diagram. Interpret each stage in the context of the chapter topic.
Conceptual learning diagram. Interpret each stage in the context of the chapter topic.
Practical laboratory activity
- Choose a small scenario related to the chapter and write a clear problem statement.
- Identify assumptions, inputs, outputs, constraints and expected behaviour.
- Draw a model, flow diagram, schema or sequence of operations.
- Use an appropriate programming language, database, simulator or network tool where relevant.
- Test normal, boundary and failure cases; record observations and explain discrepancies.
- Submit a concise report with methods, results, limitations and recommendations.
Use authorised software, sample data and test environments. Do not access systems or information without permission.
Worked reasoning and self-assessment
Begin by defining the problem and its constraints. Work through a small example manually before relying on a tool. State the expected result, show intermediate steps and verify the outcome independently. For system-design questions, justify the selected approach and discuss at least one alternative. For performance questions, identify the workload and metric being measured. For security questions, describe the threat, the control and the residual risk.
Examination and revision questions
Answer each question with precise definitions, diagrams, calculations or technical examples where appropriate.
What is a DBMS?
Show answer guidance
Software that defines, stores, retrieves, updates and controls access to databases while supporting integrity, concurrency and recovery.
Differentiate a database and a DBMS.
Show answer guidance
A database is the organised data; a DBMS is the software that manages it.
What is an ER model?
Show answer guidance
A conceptual model describing entity types, attributes and relationships.
Define a primary key.
Show answer guidance
A selected candidate key that uniquely identifies each row in a relation.
What is a foreign key?
Show answer guidance
An attribute or set of attributes referencing a candidate key in another or the same relation.
What is referential integrity?
Show answer guidance
The rule that foreign-key references must be valid according to the database constraint.
What does SQL SELECT do?
Show answer guidance
Retrieves rows and expressions from one or more data sources.
What is normalization?
Show answer guidance
Organising relations using dependencies to reduce redundancy and update anomalies.
State the ACID properties.
Show answer guidance
Atomicity, consistency, isolation and durability.
What is a dirty read?
Show answer guidance
Reading data written by a transaction that has not yet committed.
Why are indexes used?
Show answer guidance
To accelerate data retrieval by providing an auxiliary access path.
What is a query execution plan?
Show answer guidance
The operations and access strategies chosen to execute a database query.
What is SQL injection?
Show answer guidance
An attack in which untrusted input changes the meaning of a database query.
Why test database backups?
Show answer guidance
To verify that data can actually be restored within required recovery objectives.
What is database replication?
Show answer guidance
Maintaining copies of data on multiple database instances or nodes.
Revision checklist
- Can I define and distinguish the chapter's core concepts?
- Can I explain the diagrams and reproduce them from memory?
- Can I solve a practical example and justify each step?
- Can I identify likely errors, edge cases and failure modes?
- Can I evaluate security, performance and ethical implications?
- Can I support design decisions with clear reasoning?
Further study and references
Use current university textbooks, peer-reviewed research, official standards and vendor or project documentation. Verify software versions, protocol specifications and security guidance against authoritative sources because technical systems change. Cite the edition and publication details of materials used in assignments, and distinguish established facts from assumptions or illustrative examples.