Skip to main content
CBE Nexus · Undergraduate Computer Science

Chapter 6: Database Management Systems

Undergraduate database systems notes covering data modelling, ER diagrams, relational design, SQL, normalization, transactions, concurrency, indexing, query optimisation, security, backup and recovery.

Back to Computer Science course guide

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.

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.

Figure 1. Database design workflow
Requirements and business rules→ER conceptual model→Relational schema and constraints→SQL implementation and validation

Conceptual learning diagram. Interpret each stage in the context of the chapter topic.

Figure 2. Relational integrity
Primary key identifies each row→Foreign key references a candidate key→Constraints validate permitted values→Transactions preserve consistent changes

Conceptual learning diagram. Interpret each stage in the context of the chapter topic.

Figure 3. Transaction lifecycle
Begin transaction→Read and modify records→Validate and commit→Rollback if operation fails

Conceptual learning diagram. Interpret each stage in the context of the chapter topic.

Practical laboratory activity

  1. Choose a small scenario related to the chapter and write a clear problem statement.
  2. Identify assumptions, inputs, outputs, constraints and expected behaviour.
  3. Draw a model, flow diagram, schema or sequence of operations.
  4. Use an appropriate programming language, database, simulator or network tool where relevant.
  5. Test normal, boundary and failure cases; record observations and explain discrepancies.
  6. 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.

  1. What is a DBMS?

    Show answer guidance

    Software that defines, stores, retrieves, updates and controls access to databases while supporting integrity, concurrency and recovery.

  2. Differentiate a database and a DBMS.

    Show answer guidance

    A database is the organised data; a DBMS is the software that manages it.

  3. What is an ER model?

    Show answer guidance

    A conceptual model describing entity types, attributes and relationships.

  4. Define a primary key.

    Show answer guidance

    A selected candidate key that uniquely identifies each row in a relation.

  5. What is a foreign key?

    Show answer guidance

    An attribute or set of attributes referencing a candidate key in another or the same relation.

  6. What is referential integrity?

    Show answer guidance

    The rule that foreign-key references must be valid according to the database constraint.

  7. What does SQL SELECT do?

    Show answer guidance

    Retrieves rows and expressions from one or more data sources.

  8. What is normalization?

    Show answer guidance

    Organising relations using dependencies to reduce redundancy and update anomalies.

  9. State the ACID properties.

    Show answer guidance

    Atomicity, consistency, isolation and durability.

  10. What is a dirty read?

    Show answer guidance

    Reading data written by a transaction that has not yet committed.

  11. Why are indexes used?

    Show answer guidance

    To accelerate data retrieval by providing an auxiliary access path.

  12. What is a query execution plan?

    Show answer guidance

    The operations and access strategies chosen to execute a database query.

  13. What is SQL injection?

    Show answer guidance

    An attack in which untrusted input changes the meaning of a database query.

  14. Why test database backups?

    Show answer guidance

    To verify that data can actually be restored within required recovery objectives.

  15. What is database replication?

    Show answer guidance

    Maintaining copies of data on multiple database instances or nodes.

Revision checklist

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.

Academic and responsible-use note: These notes support undergraduate learning and do not replace your university's course outline or laboratory rules. Respect privacy, intellectual property, software licences and institutional policies. Conduct experiments only on systems and datasets for which you have permission.

Return to the full Computer Science course guide

Official Education References

Use these official organisations for curriculum guidance, examinations and further learning information.