Skip to main content

CBE NEXUS · UNDERGRADUATE TEXTBOOK SERIES

Chapter 5: Database Design, SQL and Data Management

Information Systems

Comprehensive undergraduate notes, worked database examples, applied design studios and university assessment.

Undergraduate level · Chapter 5 of 16 · Year 2 core discipline knowledge

Learning outcomes: Explain database architecture and models; create ER and relational models; apply normalisation; write SQL queries and updates; manage transactions and concurrency; optimise queries; secure and recover databases; and evaluate data quality, warehouses and governance.

Database systems and data management

Data management is the disciplined collection, validation, organisation, storage, protection, integration, use, retention and disposal of organisational data. Information systems depend on trustworthy records: a payroll service cannot calculate correct salaries from inconsistent employee records, and an admissions platform cannot make fair decisions from incomplete qualifications. Database design therefore begins with the meaning and use of information, not with a choice of software product.

A database is an organised collection of related data managed under defined rules. A database management system (DBMS) defines structures, queries and updates records, controls concurrent access, enforces integrity, secures information, recovers from failure and supports administration. The DBMS is distinct from the database itself and from the application that uses it. A complete database solution includes people, policies, processes, interfaces, infrastructure and governance.

Data governance assigns decision rights and accountability for data definitions, quality, access and lifecycle. Metadata describes data: meaning, type, source, owner, permitted values, sensitivity, update frequency and lineage. A data dictionary prevents departments from using the same term differently. For example, 'active student' might mean registered, financially cleared, attending or not withdrawn. Reports become misleading when definitions are inconsistent.

In practical database work, state the business rule being supported, identify the responsible data owner and test both valid and invalid cases. Document assumptions about scale, access, retention and recovery. A technically valid schema or query is not sufficient if it gives the wrong interpretation of the organisation's information. Review sample outputs with domain users and keep definitions consistent across operational screens, exports and management reports.

Database architecture and the three-schema model

A database system normally includes a database engine, storage files, memory structures, transaction and concurrency services, query processor, security controls, backup mechanisms and administration tools. Applications connect through drivers, APIs or service layers. The DBMS parses a query, checks permissions, creates an execution plan, accesses data and returns results while enforcing constraints.

The three-schema architecture distinguishes external views, the conceptual logical structure and the internal physical representation. External views show the subset relevant to a user or application. The conceptual schema describes entities and relationships across the organisation. The internal schema describes files, indexes and storage. Data independence means changes at one level can be made with limited impact on other levels, although tightly coupled applications can undermine this benefit.

A logical design should describe business meaning independently of a particular vendor. Physical design then considers the selected DBMS, data types, indexes, partitioning, storage and operational configuration. Architecture decisions should record assumptions about transaction volume, concurrency, availability, recovery, privacy and integration. A diagram should identify scope and relationships; it should not be treated as a substitute for a data dictionary or operational plan.

In practical database work, state the business rule being supported, identify the responsible data owner and test both valid and invalid cases. Document assumptions about scale, access, retention and recovery. A technically valid schema or query is not sufficient if it gives the wrong interpretation of the organisation's information. Review sample outputs with domain users and keep definitions consistent across operational screens, exports and management reports.

Database models and selecting a DBMS

The relational model represents data as relations (tables) with rows and columns. Document databases store flexible document structures; key-value stores associate keys with values; graph databases model nodes and relationships; and column-family stores support particular large-scale access patterns. Object-relational systems extend relational capabilities, while time-series databases are designed around timestamped measurements.

Selection depends on transaction requirements, consistency, query patterns, data relationships, scale, availability, skills, operational maturity, cost and governance. Relational systems are often appropriate for structured business transactions requiring integrity and joins. A document store may fit variable product descriptions. A graph database may help analyse complex connections. Avoid choosing a model solely because a vendor markets it as modern.

A selection exercise should use representative queries and data, not slogans. Compare operational complexity, backup and recovery, monitoring, security, portability, licensing and staff capability. A technically powerful platform can be a poor organisational choice if it cannot be administered safely. Prototype high-risk workloads and document why the selected model fits the actual problem.

In practical database work, state the business rule being supported, identify the responsible data owner and test both valid and invalid cases. Document assumptions about scale, access, retention and recovery. A technically valid schema or query is not sufficient if it gives the wrong interpretation of the organisation's information. Review sample outputs with domain users and keep definitions consistent across operational screens, exports and management reports.

Database development lifecycle and requirements

Database development proceeds through requirements discovery, conceptual modelling, logical modelling, physical design, implementation, migration, testing, deployment, monitoring and change management. Stakeholders explain what information is needed, who creates it, who uses it, how long it is retained and what decisions depend on it. Existing spreadsheets and applications should be inspected, but should not be copied blindly.

Data requirements identify entities, attributes, relationships, volumes, sources, users, transactions, reports, security classifications and retention periods. Interviews reveal user needs; workshops reconcile conflicting definitions; observation shows how work actually happens; document analysis reveals forms and reports; and data profiling exposes quality issues in existing sources.

Requirements distinguish functional needs, such as recording enrolment, from quality constraints, such as ensuring that a registration transaction is atomic. Capture transaction rates, peak loads, query response targets, availability, recovery objectives, audit requirements and access restrictions. Record assumptions and unresolved questions. 'Store accurate student data' is too vague without definitions, ownership, validation rules and acceptance tests.

In practical database work, state the business rule being supported, identify the responsible data owner and test both valid and invalid cases. Document assumptions about scale, access, retention and recovery. A technically valid schema or query is not sufficient if it gives the wrong interpretation of the organisation's information. Review sample outputs with domain users and keep definitions consistent across operational screens, exports and management reports.

Entity-relationship modelling

An entity represents a distinguishable type of thing about which the organisation stores information, such as Student, Course, Lecturer, Invoice or Supplier. An attribute describes an entity, such as StudentNumber, DateOfBirth or CourseTitle. A relationship expresses an association between entity types. Entity-relationship diagrams (ERDs) communicate these concepts before physical implementation.

Cardinality describes how many instances may participate: one-to-one, one-to-many or many-to-many. Optionality indicates whether participation is mandatory. A student may have many enrolments, while each enrolment belongs to one student. A course may have many students and each student may take many courses, so the many-to-many association is resolved through an associative entity such as Enrolment.

Business rules must be explicit. Can an applicant submit several applications in one admission cycle? Can a course offering exist before a lecturer is assigned? Can an invoice have multiple payments? These questions affect optionality, cardinality and constraints. Validate the ERD with users and test it against realistic scenarios, including exceptions and historical records.

In practical database work, state the business rule being supported, identify the responsible data owner and test both valid and invalid cases. Document assumptions about scale, access, retention and recovery. A technically valid schema or query is not sufficient if it gives the wrong interpretation of the organisation's information. Review sample outputs with domain users and keep definitions consistent across operational screens, exports and management reports.

Keys, identifiers and integrity constraints

A superkey is any attribute set that uniquely identifies a row. A candidate key is a minimal superkey. One candidate key is selected as the primary key; other candidate keys may be enforced with unique constraints. A foreign key references a candidate or primary key in another relation. A composite key contains more than one attribute. Keys should be stable, unique and appropriate for their purpose.

Entity integrity requires a primary key to be unique and non-null. Referential integrity ensures foreign-key values refer to valid parent rows or are null when permitted. Domain integrity constrains values to appropriate types and ranges. Business constraints express organisational rules, such as an end date not preceding a start date. Constraints should be enforced as close to the data as practical, usually in the DBMS as well as application logic.

Natural keys use meaningful existing values, such as a national identifier, but may change or have privacy implications. Surrogate keys are generated identifiers and can remain stable when business attributes change. A surrogate key does not remove the need to enforce real-world uniqueness where required. Decide how missing, duplicate, merged and retired records will be handled.

In practical database work, state the business rule being supported, identify the responsible data owner and test both valid and invalid cases. Document assumptions about scale, access, retention and recovery. A technically valid schema or query is not sufficient if it gives the wrong interpretation of the organisation's information. Review sample outputs with domain users and keep definitions consistent across operational screens, exports and management reports.

Relational algebra and relational operations

Relational algebra provides a formal foundation for relational querying. Selection filters rows by a predicate; projection chooses columns; union combines compatible relations; difference identifies rows present in one relation but not another; Cartesian product pairs rows; and join combines related tuples. These operations help explain how queries produce results and why query structure matters.

An inner join returns matching rows; a left outer join preserves all rows from the left relation and supplies nulls where no right-side match exists. Incorrect join conditions can multiply rows and inflate totals. Analysts should understand the grain of each table and verify that aggregation occurs at the intended level. A join is not merely a visual combination; it uses a predicate to match related values.

Set operations require compatible structures and data types. UNION removes duplicate result rows by default, while UNION ALL retains them. Difference and intersection operations are useful for identifying missing or shared records. Before combining datasets, verify that the columns represent the same concepts and use compatible units, periods and inclusion rules.

In practical database work, state the business rule being supported, identify the responsible data owner and test both valid and invalid cases. Document assumptions about scale, access, retention and recovery. A technically valid schema or query is not sufficient if it gives the wrong interpretation of the organisation's information. Review sample outputs with domain users and keep definitions consistent across operational screens, exports and management reports.

Functional dependencies and normalisation

A functional dependency X → Y means that the value of attribute set X determines the value of attribute set Y within the relation's business rules. Candidate keys and dependencies help identify redundancy. First normal form requires appropriate atomic values and no repeating groups; second normal form removes partial dependencies on part of a composite candidate key; third normal form removes transitive dependencies of non-key attributes.

Consider Enrolment(StudentID, StudentName, CourseID, CourseTitle, LecturerID, LecturerName, Grade), with composite key (StudentID, CourseID). StudentName depends on StudentID; CourseTitle and LecturerID depend on CourseID; Grade depends on both key attributes. Splitting Student, Course and Enrolment relations reduces update anomalies. The design must preserve dependencies and allow correct joins.

Normalisation reduces insertion, update and deletion anomalies. Without it, changing a lecturer's name may require editing hundreds of rows; a new course may be impossible to record until a student enrols; and deleting the last enrolment may accidentally delete course information. Normal forms are tools for analysing dependencies, not a mechanical instruction to create as many tables as possible.

In practical database work, state the business rule being supported, identify the responsible data owner and test both valid and invalid cases. Document assumptions about scale, access, retention and recovery. A technically valid schema or query is not sufficient if it gives the wrong interpretation of the organisation's information. Review sample outputs with domain users and keep definitions consistent across operational screens, exports and management reports.

Higher normal forms and denormalisation

Boyce-Codd normal form requires every determinant of a non-trivial functional dependency to be a superkey. Fourth normal form addresses independent multivalued dependencies; fifth normal form addresses certain join dependencies. These forms matter when complex relationships create redundancy that ordinary third normal form may not eliminate.

Denormalisation deliberately duplicates or precomputes data to improve particular read workloads. Examples include summary tables, materialised views and cached aggregates. It can improve performance but creates consistency and refresh challenges. Document the source of truth, update mechanism, acceptable staleness and reconciliation process.

Start with a sound logical model, measure real workload performance and only then consider denormalisation. Record why the optimisation exists and how it is maintained. If a summary table is refreshed nightly, users must know that today's report is not real-time. If duplicated values are updated asynchronously, monitoring must detect failed refreshes and divergence.

In practical database work, state the business rule being supported, identify the responsible data owner and test both valid and invalid cases. Document assumptions about scale, access, retention and recovery. A technically valid schema or query is not sufficient if it gives the wrong interpretation of the organisation's information. Review sample outputs with domain users and keep definitions consistent across operational screens, exports and management reports.

SQL language families and data definition

SQL is commonly grouped into data definition language (DDL), data manipulation language (DML), data query language (DQL), data control language (DCL) and transaction control language (TCL). CREATE, ALTER and DROP define structures; INSERT, UPDATE and DELETE change rows; SELECT retrieves data; GRANT and REVOKE manage privileges; and COMMIT or ROLLBACK control transactions.

SQL is declarative: the user states what data is required, and the DBMS chooses how to retrieve it. Syntax and features vary across implementations. Students should understand standard concepts and consult documentation for their DBMS. Scripts used in production should be reviewed, versioned and tested, particularly where destructive commands or schema changes are involved.

CREATE TABLE defines a relation, its columns, data types and constraints. Data types should reflect meaning and expected range. Use appropriate integer, decimal, character, date, timestamp and boolean types. Choose lengths carefully and avoid storing several unrelated facts in one field. Names should be consistent, descriptive and compatible with DBMS conventions.

In practical database work, state the business rule being supported, identify the responsible data owner and test both valid and invalid cases. Document assumptions about scale, access, retention and recovery. A technically valid schema or query is not sufficient if it gives the wrong interpretation of the organisation's information. Review sample outputs with domain users and keep definitions consistent across operational screens, exports and management reports.

Constraints, defaults and schema evolution

Constraints include PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL and CHECK. Defaults can provide values where policy permits. A CHECK constraint may require a quantity to be positive or a status to belong to an approved set. Constraints are executable documentation of data rules. Test them with valid and invalid inserts and updates.

Schema evolution changes tables, columns, constraints and indexes as requirements develop. A safe migration should consider existing application versions, data transformation, locking, downtime, rollback and compatibility. Expand-and-contract techniques add a new structure, allow old and new code to coexist, migrate data and later remove obsolete structures.

Do not make destructive schema changes without an approved plan and verified recovery path. Test migrations against a representative copy and measure execution time. Preserve data meaning, not merely row counts. Record migration version, author, purpose and result so operators can identify which schema is deployed.

In practical database work, state the business rule being supported, identify the responsible data owner and test both valid and invalid cases. Document assumptions about scale, access, retention and recovery. A technically valid schema or query is not sufficient if it gives the wrong interpretation of the organisation's information. Review sample outputs with domain users and keep definitions consistent across operational screens, exports and management reports.

SELECT, filtering, sorting and expressions

SELECT retrieves columns and expressions from one or more relations. WHERE filters rows before grouping; ORDER BY sorts the final result; DISTINCT removes duplicate result rows; aliases provide readable names; and expressions calculate derived values. Use explicit column lists instead of SELECT * in stable application code because table changes can unexpectedly alter returned data.

NULL represents missing or unknown information and requires special logic. Comparisons with NULL do not evaluate as ordinary true or false; use IS NULL or IS NOT NULL. Aggregate functions often ignore null values, which can affect interpretation. A filter on a nullable column may exclude records unintentionally. Query designers must decide how unknown values should be handled.

A query should return the intended population and grain. If a report counts students, decide whether it counts people, applications, enrolments or course registrations. Add a deterministic ORDER BY when result order matters. Limit results for interactive search and paginate large result sets. Explain units, date boundaries and treatment of missing values.

In practical database work, state the business rule being supported, identify the responsible data owner and test both valid and invalid cases. Document assumptions about scale, access, retention and recovery. A technically valid schema or query is not sufficient if it gives the wrong interpretation of the organisation's information. Review sample outputs with domain users and keep definitions consistent across operational screens, exports and management reports.

Joins, subqueries and set operations in SQL

INNER JOIN returns matching pairs; LEFT JOIN preserves all left-side rows; RIGHT JOIN preserves right-side rows; and FULL OUTER JOIN preserves unmatched rows from both sides where supported. Self-joins compare rows within one relation. Cross joins form combinations and can create very large results if used unintentionally.

Subqueries provide a query inside another query. EXISTS tests whether related rows exist; IN compares values with a result set; and correlated subqueries refer to the outer query. UNION combines compatible results and removes duplicates by default; UNION ALL retains duplicates. Use readable aliases and qualify ambiguous column names.

Verify row counts and totals after joins, especially where relationships are one-to-many. If one invoice has several payment allocations, joining invoice totals to allocations can repeat the invoice amount. Aggregate at the correct level or use a subquery to avoid double counting. Explain the join path and expected cardinality.

In practical database work, state the business rule being supported, identify the responsible data owner and test both valid and invalid cases. Document assumptions about scale, access, retention and recovery. A technically valid schema or query is not sufficient if it gives the wrong interpretation of the organisation's information. Review sample outputs with domain users and keep definitions consistent across operational screens, exports and management reports.

Aggregation, grouping and window functions

COUNT, SUM, AVG, MIN and MAX summarise groups of rows. GROUP BY defines grouping columns; HAVING filters groups after aggregation. A report of enrolments per programme groups by programme, while a fee summary may group by month and payment status. Aggregation must match the grain of the underlying facts to avoid double counting.

Window functions calculate across related rows without collapsing them into one row per group. ROW_NUMBER, RANK, LAG, LEAD and running SUM support ranking, period comparisons and cumulative totals. PARTITION BY defines independent groups and ORDER BY defines sequence. Window frames matter: a running total differs from a full-partition total.

Before publishing an aggregate, define how null values, duplicates, withdrawn records, refunds and late-arriving transactions are treated. Compare totals with a trusted source and test boundary periods. A syntactically correct query can still answer the wrong question if the population or measure is not clearly defined.

In practical database work, state the business rule being supported, identify the responsible data owner and test both valid and invalid cases. Document assumptions about scale, access, retention and recovery. A technically valid schema or query is not sufficient if it gives the wrong interpretation of the organisation's information. Review sample outputs with domain users and keep definitions consistent across operational screens, exports and management reports.

INSERT, UPDATE, DELETE and safe data changes

INSERT adds rows, UPDATE modifies selected rows and DELETE removes rows. Always inspect the intended target and predicate before executing a destructive statement. An UPDATE without a suitable WHERE clause can change every row. A DELETE may violate foreign-key constraints or trigger cascading effects. Use transactions and backups for important changes.

Applications should validate input and use parameterised statements. A business operation may need multiple changes to succeed together. Database constraints provide a final integrity barrier. Soft deletion, status changes or archival may be more appropriate than permanent deletion where records must be retained for audit or legal purposes.

For a production change, identify the authorised requester, scope, expected row count, approval and recovery plan. Run a SELECT using the same predicate before an UPDATE or DELETE. Use a transaction where appropriate, inspect affected rows and commit only after validation. Test scripts against non-production data first.

In practical database work, state the business rule being supported, identify the responsible data owner and test both valid and invalid cases. Document assumptions about scale, access, retention and recovery. A technically valid schema or query is not sufficient if it gives the wrong interpretation of the organisation's information. Review sample outputs with domain users and keep definitions consistent across operational screens, exports and management reports.

Transactions and ACID properties

A transaction is a logical unit of work. Atomicity means all its operations succeed or none are committed; consistency means defined integrity rules remain satisfied; isolation controls interference among concurrent transactions; and durability means committed changes survive relevant failures. Transaction boundaries should match business operations rather than arbitrary code blocks.

Consider a fee payment: the system records the payment, allocates it to an invoice and writes an audit event. If only one part succeeds, the account becomes inconsistent. A transaction can ensure the set commits as one unit. External payment gateways cannot usually participate in the same local database transaction, so systems need idempotency, reconciliation and compensating actions for distributed workflows.

A transaction should be short enough to avoid holding locks unnecessarily, but long enough to preserve business consistency. Handle exceptions and roll back failed operations. Do not tell a user a payment succeeded merely because a request was sent; confirm the authoritative provider result and reconcile uncertain outcomes.

In practical database work, state the business rule being supported, identify the responsible data owner and test both valid and invalid cases. Document assumptions about scale, access, retention and recovery. A technically valid schema or query is not sufficient if it gives the wrong interpretation of the organisation's information. Review sample outputs with domain users and keep definitions consistent across operational screens, exports and management reports.

Concurrency control and isolation

Multiple users and services may read and modify the same records at the same time. Concurrency control protects correctness while allowing useful parallel work. Locks can prevent conflicting operations; optimistic control checks whether data changed before committing; and multiversion concurrency control (MVCC) allows readers to access consistent snapshots in many systems.

Isolation levels trade consistency guarantees against concurrency and performance. Common anomalies include dirty reads, non-repeatable reads and phantom rows. A lost update occurs when one change overwrites another. Applications can use version columns, explicit locks or serializable transactions for critical operations.

Select the weakest isolation level that safely meets business requirements, then test realistic concurrent workloads. Seat allocation, stock reservation and fee balance updates require particular care. Retry logic must recognise deadlocks and transient failures without duplicating non-idempotent effects.

In practical database work, state the business rule being supported, identify the responsible data owner and test both valid and invalid cases. Document assumptions about scale, access, retention and recovery. A technically valid schema or query is not sufficient if it gives the wrong interpretation of the organisation's information. Review sample outputs with domain users and keep definitions consistent across operational screens, exports and management reports.

Indexes and query optimisation

An index is an auxiliary structure that helps the DBMS locate rows without scanning an entire table. B-tree indexes support many equality and range queries; hash indexes support particular equality lookups; and specialised full-text, spatial or columnar structures serve other workloads. Indexes can speed reads but consume storage and add overhead to inserts, updates and deletes.

Index columns used frequently in selective filters, joins and ordering, but measure the result. Composite index column order matters. A query that wraps an indexed column in a function may prevent efficient use of a normal index unless an expression index or equivalent is available. Query plans reveal scans, joins, estimates and index choices.

Optimisation begins with evidence, representative data and measured latency. Avoid adding an index for every column. Monitor query performance before and after changes, consider write overhead and remove unused or redundant indexes only after confirming they are not required for constraints or critical workloads.

In practical database work, state the business rule being supported, identify the responsible data owner and test both valid and invalid cases. Document assumptions about scale, access, retention and recovery. A technically valid schema or query is not sufficient if it gives the wrong interpretation of the organisation's information. Review sample outputs with domain users and keep definitions consistent across operational screens, exports and management reports.

Views, stored procedures and triggers

A view is a saved query presented as a virtual relation. Views can simplify complex queries and restrict which columns or rows users see, but their security properties depend on DBMS configuration and ownership rules. Materialised views store results and require refresh. Stored procedures package database operations, while triggers execute automatically in response to specified data events.

Procedures can centralise logic and reduce network round trips, but excessive business logic hidden in the database may complicate testing and portability. Triggers can enforce auditing or derived updates, but hidden side effects make behaviour harder to trace. Document execution order, permissions and failure behaviour.

Choose these mechanisms deliberately and keep responsibilities clear between application and database layers. A view is not automatically a security boundary. Test permissions under the actual user identity. Monitor materialised view freshness and document trigger side effects for developers and administrators.

In practical database work, state the business rule being supported, identify the responsible data owner and test both valid and invalid cases. Document assumptions about scale, access, retention and recovery. A technically valid schema or query is not sufficient if it gives the wrong interpretation of the organisation's information. Review sample outputs with domain users and keep definitions consistent across operational screens, exports and management reports.

Database security and access control

Database security protects confidentiality, integrity and availability. Authentication verifies the connecting identity; authorisation grants permitted actions. Least privilege means each account receives only the access necessary for its role. Separate application, reporting, administrator and migration identities. Avoid shared superuser credentials and review access periodically.

Role-based access control groups permissions into roles; row-level security restricts access to particular records; column-level controls protect sensitive fields where supported. Encrypt network connections and sensitive stored data, manage keys securely and rotate credentials. Audit privileged activity and monitor unusual queries.

Backups and replicas also contain sensitive information and must receive equivalent protection. Use secure secret storage, network restrictions, patching and tested incident procedures. A database exposed to the public internet should have a documented reason and layered controls; usually applications should access it through a protected service boundary.

In practical database work, state the business rule being supported, identify the responsible data owner and test both valid and invalid cases. Document assumptions about scale, access, retention and recovery. A technically valid schema or query is not sufficient if it gives the wrong interpretation of the organisation's information. Review sample outputs with domain users and keep definitions consistent across operational screens, exports and management reports.

Privacy, retention and the data lifecycle

Personal data should be collected for specified legitimate purposes, limited to what is necessary, kept accurate and protected with appropriate safeguards. Data lifecycle management covers creation, use, sharing, retention, archival, anonymisation and secure disposal. The correct retention period depends on legal, contractual, academic and operational obligations; it should not be guessed.

Data classification identifies sensitivity and handling requirements. Pseudonymisation replaces direct identifiers with codes but does not necessarily make data anonymous. Anonymisation aims to prevent identification, yet re-identification risks can remain when datasets are linked. Privacy assessments should examine collection, access, sharing, international transfers, backups, analytics and deletion.

Consult applicable laws and institutional data-protection officers. Retention rules should cover production data, exports, logs, test copies and backups. A deletion request may require coordinated handling across replicas and downstream systems, subject to legitimate retention obligations. Record the decision and evidence of completion.

In practical database work, state the business rule being supported, identify the responsible data owner and test both valid and invalid cases. Document assumptions about scale, access, retention and recovery. A technically valid schema or query is not sufficient if it gives the wrong interpretation of the organisation's information. Review sample outputs with domain users and keep definitions consistent across operational screens, exports and management reports.

Backup, recovery and replication

Backups create recoverable copies of data. Full backups copy the selected dataset; incremental backups capture changes since a previous backup; differential backups capture changes since the last full backup. Point-in-time recovery combines backups with transaction logs where supported. A backup strategy defines frequency, retention, encryption, off-site protection and restore testing.

Replication maintains copies of data on other nodes for availability, read scaling or geographic resilience. Synchronous replication can provide stronger consistency but may increase latency and depend on network availability; asynchronous replication reduces some latency but may lose recent changes during failure. Replication is not a substitute for backup because accidental deletion or corruption can be copied to replicas.

Recovery Point Objective (RPO) is the target maximum data loss measured in time. Recovery Time Objective (RTO) is the target maximum time to restore service. A restore exercise should validate both elapsed time and data integrity. An untested backup is an assumption, not evidence of recoverability.

In practical database work, state the business rule being supported, identify the responsible data owner and test both valid and invalid cases. Document assumptions about scale, access, retention and recovery. A technically valid schema or query is not sufficient if it gives the wrong interpretation of the organisation's information. Review sample outputs with domain users and keep definitions consistent across operational screens, exports and management reports.

Data warehouses and dimensional modelling

A data warehouse integrates historical information from multiple operational sources for analysis and reporting. ETL extracts, transforms and loads data; ELT loads data before transformation in the target platform. Staging areas support validation and reconciliation. Warehouses often use dimensional models with fact tables for measurable events and dimensions for descriptive context.

A star schema places a central fact table around dimension tables. A fact table has a defined grain, such as one row per payment transaction or one row per student-course-semester enrolment. Dimensions may include time, programme, campus and student category. Slowly changing dimensions preserve historical context when attributes change.

Warehouse measures need agreed definitions and lineage back to source systems. Operational systems are optimised for current transactions; analytical systems are optimised for historical questions. Do not confuse an operational dashboard with a governed warehouse. Reconcile source and warehouse totals and identify the time at which data was refreshed.

In practical database work, state the business rule being supported, identify the responsible data owner and test both valid and invalid cases. Document assumptions about scale, access, retention and recovery. A technically valid schema or query is not sufficient if it gives the wrong interpretation of the organisation's information. Review sample outputs with domain users and keep definitions consistent across operational screens, exports and management reports.

Data lakes, lakehouses and big data

A data lake stores large volumes of raw or lightly processed data in varied formats. A lakehouse combines lake-style storage with management features associated with analytical databases. Big data discussions often refer to volume, velocity, variety, veracity and value. These labels are useful only when connected to actual storage, processing, governance and analytical needs.

Unmanaged lakes can become data swamps with unclear ownership, poor quality and unknown sensitivity. Catalogues, schemas, access controls, lineage, retention and quality checks are essential. Distributed processing frameworks divide work across nodes, but data skew, network transfer and small-file problems can reduce efficiency.

Start with a clear analytical purpose and data stewardship model. Identify who may discover, access and export datasets. Tag sensitive data at ingestion and preserve source metadata. Monitor pipeline costs and quality. A large platform is not valuable if users cannot establish whether its data is accurate, current or permitted for their purpose.

In practical database work, state the business rule being supported, identify the responsible data owner and test both valid and invalid cases. Document assumptions about scale, access, retention and recovery. A technically valid schema or query is not sufficient if it gives the wrong interpretation of the organisation's information. Review sample outputs with domain users and keep definitions consistent across operational screens, exports and management reports.

Data quality, master data and metadata

Data quality is multidimensional: accuracy, completeness, consistency, validity, uniqueness and timeliness. Profiling measures missing values, duplicates, ranges and distribution anomalies. Cleansing may standardise formats, resolve duplicates and correct known errors, but automated changes must preserve meaning and be traceable. A data-quality rule needs an owner and a response when it fails.

Master data describes core shared entities such as customers, students, suppliers, products and locations. Master data management seeks a trusted, governed version across systems. Reference data includes controlled codes such as country, currency or status lists. Metadata catalogues definitions, ownership, lineage, sensitivity and usage.

A quality dashboard should report rule, population, failure count, severity, trend, owner and remediation status. Duplicate resolution requires identity rules and human review for ambiguous matches. Do not silently merge people because their names are similar. Preserve provenance and allow correction through an accountable process.

In practical database work, state the business rule being supported, identify the responsible data owner and test both valid and invalid cases. Document assumptions about scale, access, retention and recovery. A technically valid schema or query is not sufficient if it gives the wrong interpretation of the organisation's information. Review sample outputs with domain users and keep definitions consistent across operational screens, exports and management reports.

NoSQL and distributed data systems

NoSQL is an umbrella term for non-relational data systems, not one uniform model. Document, key-value, wide-column and graph databases have different query, consistency and scaling characteristics. Some systems prioritise availability and partition tolerance with eventual consistency in certain operations; others provide stronger transactional guarantees.

Distributed systems face network partitions, latency, node failures and replication conflicts. Eventual consistency means replicas may temporarily differ but converge if updates stop and propagation succeeds. It can suit shopping-cart views or social feeds, but may be inappropriate for balances or unique seat allocation without additional safeguards.

Understand the system's guarantees and failure modes before selecting it for a critical transaction. Define which reads may be stale, how conflicts are resolved and what happens when a region is unavailable. A design that relies on a single-node development environment must still be tested under distributed failure conditions.

In practical database work, state the business rule being supported, identify the responsible data owner and test both valid and invalid cases. Document assumptions about scale, access, retention and recovery. A technically valid schema or query is not sufficient if it gives the wrong interpretation of the organisation's information. Review sample outputs with domain users and keep definitions consistent across operational screens, exports and management reports.

Cloud databases and managed services

Managed database services may provide provisioning, patching, monitoring, backups, scaling and high-availability options. They reduce some operational burden but do not remove the customer's responsibility for data classification, access configuration, application security, retention, cost monitoring and recovery verification. Shared-responsibility boundaries vary by service model and provider.

Evaluate service location, data residency, encryption, network access, identity integration, backup export, portability, service-level commitments, support, pricing and exit strategy. Autoscaling can protect performance but may create unexpected costs. Serverless databases can scale with demand but have limits and pricing behaviour that must be understood.

Test failover and restore procedures using the selected service configuration. Ensure that access policies, private networking and audit logs are enabled as intended. Plan for provider outages, quota limits, credential rotation and export of data in a usable format. Cloud convenience does not eliminate architecture or governance responsibilities.

In practical database work, state the business rule being supported, identify the responsible data owner and test both valid and invalid cases. Document assumptions about scale, access, retention and recovery. A technically valid schema or query is not sufficient if it gives the wrong interpretation of the organisation's information. Review sample outputs with domain users and keep definitions consistent across operational screens, exports and management reports.

Data integration and ETL pipelines

Integration combines data from operational systems, external partners, files, APIs and devices. ETL extracts source records, transforms them into a consistent model and loads the destination. Data pipelines should handle schema changes, late-arriving records, duplicate events, failed batches and partial completion. Data lineage records where information came from and how it was transformed.

Batch processing handles data in scheduled groups; streaming processes events continuously or near-continuously. Change data capture detects source changes for downstream systems. Pipelines need monitoring, retries, dead-letter handling, reconciliation and access controls. Idempotent processing helps avoid duplicate effects when a job reruns.

Validate counts, totals and key relationships at each stage. Record pipeline version, run time, source watermark and rejected records. A retry should not create duplicate financial transactions. Where an external source changes its schema, fail safely and alert the data owner instead of silently dropping new fields.

In practical database work, state the business rule being supported, identify the responsible data owner and test both valid and invalid cases. Document assumptions about scale, access, retention and recovery. A technically valid schema or query is not sufficient if it gives the wrong interpretation of the organisation's information. Review sample outputs with domain users and keep definitions consistent across operational screens, exports and management reports.

Database administration and operations

Database administration includes installation, configuration, user and role management, patching, backup, recovery, capacity planning, performance tuning, monitoring, replication and incident response. Operational procedures should be documented and tested. Administrative access is highly privileged and should be controlled, logged and reviewed.

Monitor connection pools, storage growth, CPU, memory, cache efficiency, query latency, lock waits, replication lag and backup status. Alert thresholds should reflect service objectives and avoid excessive noise. Capacity forecasts should consider seasonal peaks and data retention. Maintenance windows and schema changes require communication and compatibility planning.

A database runbook should identify service owner, escalation contacts, diagnostic steps, backup status, failover authority and validation checks. After incidents, record timeline, impact, root causes, recovery actions and preventive work. Avoid blaming individuals; improve controls and system design.

In practical database work, state the business rule being supported, identify the responsible data owner and test both valid and invalid cases. Document assumptions about scale, access, retention and recovery. A technically valid schema or query is not sufficient if it gives the wrong interpretation of the organisation's information. Review sample outputs with domain users and keep definitions consistent across operational screens, exports and management reports.

Database testing and migration

Database testing covers schema correctness, constraints, query results, transactions, concurrency, security, performance, backup and recovery. Use representative data volumes and distributions. Test both valid and invalid operations. Migration testing compares source and target counts, key relationships, totals, samples and business reports. A migration is not complete merely because a script exits without an error.

A safe migration plan includes source profiling, mapping, cleansing, transformation, rehearsal, freeze or change capture, cutover, reconciliation, user acceptance and rollback. For large systems, migrate in phases and maintain a clear source of truth during coexistence. Preserve audit history and protect personal data in test environments.

Mask or synthesise sensitive data when production records are not required. Keep migration logs and rejected-record reports. Reconcile financial and academic totals independently. Define rollback criteria before cutover, and do not delete the source until owners accept the migrated system and the retention policy permits disposal.

In practical database work, state the business rule being supported, identify the responsible data owner and test both valid and invalid cases. Document assumptions about scale, access, retention and recovery. A technically valid schema or query is not sufficient if it gives the wrong interpretation of the organisation's information. Review sample outputs with domain users and keep definitions consistent across operational screens, exports and management reports.

Ethics and professional responsibility in data management

Database professionals influence what an organisation remembers, measures, shares and forgets. Poor design can expose private information, reinforce biased classifications or make people unable to correct inaccurate records. Professional practice requires data minimisation, transparency, access controls, accuracy, retention discipline and a clear route for correcting or challenging consequential records.

Automated analytics should be evaluated for representativeness, bias, explainability and inappropriate secondary use. A dataset's existence does not mean every proposed use is justified. Respect intellectual property, contractual restrictions and institutional policies. Escalate unsafe requests and report breaches through approved channels.

The technical ability to query data does not establish ethical or legal authority to do so. Design decisions should consider affected people, foreseeable misuse and unequal impacts. Document limitations and uncertainty. Where an automated decision has significant consequences, provide appropriate human oversight, explanation and a means to challenge errors.

In practical database work, state the business rule being supported, identify the responsible data owner and test both valid and invalid cases. Document assumptions about scale, access, retention and recovery. A technically valid schema or query is not sufficient if it gives the wrong interpretation of the organisation's information. Review sample outputs with domain users and keep definitions consistent across operational screens, exports and management reports.

Applied case study: university student information database

A university needs a database for applications, admissions, student registration, programmes, course offerings, enrolments, fees and results. The initial spreadsheet repeats programme and student details across many rows. Staff disagree about the meaning of 'registered', and reports count course registrations inconsistently. The project must establish definitions, a reliable relational model and controlled access for departments.

A conceptual model identifies Applicant, Application, Student, Programme, Course, Offering, Enrolment, Invoice, Payment and Result. Application relates to Applicant and Programme; an admitted applicant may become a Student; an Offering represents a Course in a particular semester; Enrolment connects Student and Offering. Payment allocation should be represented explicitly rather than inferred from a text note. The model separates current status from historical events so audit and reporting remain meaningful.

Design a data dictionary and role matrix. Write queries for pending applications by programme, accepted offers by cycle, enrolments by offering and outstanding balances. Test duplicate applications, missing documents, withdrawn students and repeated payment notifications. Explain how audit history, privacy, retention and backup recovery are handled. Confirm business rules with the registrar and finance office rather than assuming them.

For assessment, submit the model or query, explain the decisions, identify risks and provide test evidence. Include realistic sample data and show how the design responds to duplicates, missing values, concurrent changes and permission failures. Where the solution depends on institutional policy or jurisdictional law, record the assumption and seek confirmation from the appropriate owner rather than inventing a rule.

Worked example: normalising a registration relation

Assume Registration(StudentID, StudentName, ProgrammeID, ProgrammeName, CourseID, CourseTitle, Semester, Grade) with candidate key (StudentID, CourseID, Semester). StudentName and ProgrammeID depend on StudentID; ProgrammeName depends on ProgrammeID; CourseTitle depends on CourseID; Grade depends on the complete registration key. These dependencies reveal partial and transitive dependencies.

Decompose into Student(StudentID, StudentName, ProgrammeID), Programme(ProgrammeID, ProgrammeName), Course(CourseID, CourseTitle) and Registration(StudentID, CourseID, Semester, Grade). Foreign keys preserve relationships. This reduces repeated names and update anomalies. If a student changes programme, the programme identifier is updated once in Student, while historical programme-at-registration may require an additional snapshot or history relation depending on reporting requirements.

The decomposition must preserve the ability to reconstruct the original information through joins. Test a student with multiple courses, a course with many students and a semester with no registrations. Identify candidate keys, nullability and uniqueness rules. Explain which facts describe the current student and which facts describe the historical registration event.

For assessment, submit the model or query, explain the decisions, identify risks and provide test evidence. Include realistic sample data and show how the design responds to duplicates, missing values, concurrent changes and permission failures. Where the solution depends on institutional policy or jurisdictional law, record the assumption and seek confirmation from the appropriate owner rather than inventing a rule.

Worked example: SQL aggregation and interpretation

Suppose Enrolment contains one row per student-course offering and Offering identifies the semester. To count enrolments per offering, group by OfferingID and count EnrolmentID. To calculate average marks, use AVG(Mark) but define whether missing marks are excluded and whether withdrawn students are included. A query can be syntactically correct and still answer the wrong question if the population or grain is undefined.

Before publishing a report, compare the total number of rows with an independent count, check duplicate keys, inspect null values and verify selected examples with registry staff. A join to a table with multiple matching rows can multiply results. Document the metric definition, time period, inclusion rules, source and refresh date so readers can interpret it responsibly.

A query might be: SELECT OfferingID, COUNT(*) AS enrolment_count FROM Enrolment GROUP BY OfferingID. For a report of offerings with more than 100 enrolments, add HAVING COUNT(*) > 100. Explain whether the count includes withdrawn, cancelled or audit registrations. The SQL alone cannot decide institutional policy.

For assessment, submit the model or query, explain the decisions, identify risks and provide test evidence. Include realistic sample data and show how the design responds to duplicates, missing values, concurrent changes and permission failures. Where the solution depends on institutional policy or jurisdictional law, record the assumption and seek confirmation from the appropriate owner rather than inventing a rule.

Worked example: indexing and query cost

A student table contains 500,000 rows and users frequently search by StudentNumber. Without a suitable index, a query may scan many or all rows. A B-tree index on StudentNumber can make selective lookups faster, subject to the DBMS plan and data distribution. If the field is unique, a unique constraint and index can also enforce identifier uniqueness.

An index is not free. Each insert, update or delete may require index maintenance, and the structure consumes disk and memory. If a table has many indexes, write performance and maintenance can suffer. Use query plans and workload measurements to decide which indexes are justified.

Benchmark with representative records and concurrent users. Compare execution plans and latency distributions before and after indexing. Consider whether the query filters on StudentNumber alone or also sorts by another field. State the expected benefit, write overhead and monitoring plan. Never claim an index is useful solely because the column appears in a query.

For assessment, submit the model or query, explain the decisions, identify risks and provide test evidence. Include realistic sample data and show how the design responds to duplicates, missing values, concurrent changes and permission failures. Where the solution depends on institutional policy or jurisdictional law, record the assumption and seek confirmation from the appropriate owner rather than inventing a rule.

Database design studio: retail inventory

Model products, categories, suppliers, purchase orders, goods receipts, warehouses, stock movements and sales orders. Decide whether stock quantity is stored as a current balance, derived from movements or maintained as a controlled summary. Define the transaction that receives goods and the transaction that reserves stock for an order. Explain how the system prevents two concurrent users from allocating the same final unit.

Create a data dictionary for product code, unit of measure, reorder level, warehouse, movement type and transaction timestamp. Write a report showing low-stock products by warehouse and supplier. Include constraints for positive quantities and valid movement types. Discuss the audit trail required to investigate discrepancies and how returns, damaged goods and stock counts are represented.

A movement ledger provides traceability but may require aggregation for current balances. A summary balance can improve performance but must be reconciled against the ledger. Explain how the design handles cancellation, partial delivery, back orders and stock transfers. Test simultaneous reservations and duplicate supplier delivery messages.

For assessment, submit the model or query, explain the decisions, identify risks and provide test evidence. Include realistic sample data and show how the design responds to duplicates, missing values, concurrent changes and permission failures. Where the solution depends on institutional policy or jurisdictional law, record the assumption and seek confirmation from the appropriate owner rather than inventing a rule.

Database design studio: privacy-sensitive health records

Model patient identity, encounters, practitioners, diagnoses, prescriptions, laboratory orders and results. Treat health information as highly sensitive and define strict access rules. A practitioner should access information needed for care, while administrative staff may need scheduling details but not all clinical notes. Consider emergency access, audit review and break-glass procedures where applicable.

Specify how corrections are recorded without silently overwriting clinical history. Define retention and disclosure controls according to applicable law and institutional policy. Test whether a user can access a record outside their authorised relationship. Consider encryption, secure backups, availability during clinical emergencies and the consequences of incorrect patient matching.

The model should distinguish a patient identity from a clinical encounter and distinguish an order from its result. Use audit records to preserve who viewed or changed sensitive information. Design carefully for duplicate identities, name changes and uncertain matches. The case illustrates why integrity, privacy and availability are inseparable in critical information systems.

For assessment, submit the model or query, explain the decisions, identify risks and provide test evidence. Include realistic sample data and show how the design responds to duplicates, missing values, concurrent changes and permission failures. Where the solution depends on institutional policy or jurisdictional law, record the assumption and seek confirmation from the appropriate owner rather than inventing a rule.

SQL practice and query reasoning

For a sample university schema, write a SELECT query that joins Student to Programme, a LEFT JOIN that lists all programmes including those with no students, a GROUP BY query that counts students per programme and a HAVING clause that returns programmes above a chosen count. Explain the difference between filtering rows in WHERE and filtering groups in HAVING.

Create an UPDATE statement that changes a student's contact email using a primary key and a DELETE statement for a disposable test record. Before executing either, run a SELECT with the same predicate and confirm the intended row count. Wrap related updates in a transaction and demonstrate ROLLBACK in a test environment.

Explain why production data changes require authorisation and a recovery plan. Include a test for a missing identifier and a test for a duplicate email where uniqueness is required. Show the number of affected rows and verify the final state. Do not execute destructive examples against a live database.

For assessment, submit the model or query, explain the decisions, identify risks and provide test evidence. Include realistic sample data and show how the design responds to duplicates, missing values, concurrent changes and permission failures. Where the solution depends on institutional policy or jurisdictional law, record the assumption and seek confirmation from the appropriate owner rather than inventing a rule.

Performance and operational review

Select a slow query from a hypothetical student portal and inspect its execution plan. Identify full scans, expensive joins, sorts, inaccurate estimates and missing or unused indexes. Form a hypothesis and change one factor at a time. Compare response-time distributions and resource consumption using the same dataset and concurrency profile.

Propose monitoring for storage growth, failed backups, replication delay, connection exhaustion, lock waits and error rates. Set thresholds based on service objectives. Write an incident runbook for an unavailable database, including communication, triage, failover decision, recovery validation and post-incident review.

Report p50 and p95 latency, throughput, error rate and resource use rather than a single best-case timing. Explain whether the bottleneck is CPU, I/O, locking, network or application behaviour. Recommend a change only when measurements support it and include a rollback strategy.

For assessment, submit the model or query, explain the decisions, identify risks and provide test evidence. Include realistic sample data and show how the design responds to duplicates, missing values, concurrent changes and permission failures. Where the solution depends on institutional policy or jurisdictional law, record the assumption and seek confirmation from the appropriate owner rather than inventing a rule.

Database design and operations diagrams

Database development lifecycle
Requirements and data discovery
↓
Conceptual ER model
↓
Logical relational model and normalisation
↓
Physical schema, indexes and security
↓
Implementation, migration and testing
↓
Monitoring, backup and improvement
University enrolment ER relationship
STUDENT (StudentID PK)
↓
1 ─── 0..*
↓
ENROLMENT (StudentID FK, OfferingID FK, Grade)
↓
* ─── 1
↓
OFFERING (OfferingID PK, CourseID FK, Semester)
Three-schema architecture
External views — user and application perspectives
↓
Conceptual schema — organisation-wide logical model
↓
Internal schema — files, indexes and physical storage
↓
DBMS maps requests between levels
SQL query processing
SQL statement submitted
↓
Parse, validate and check permissions
↓
Optimiser evaluates access paths and join order
↓
Execution engine reads indexes and data
↓
Rows returned to application
Backup and recovery strategy
Production database and transaction logs
↓
Scheduled full / incremental backups
↓
Encrypted off-site or isolated copy
↓
Restore rehearsal and integrity checks
↓
Service recovery against RTO and RPO

University examination and revision questions

Show clear reasoning, define assumptions and use SQL or ER diagrams where appropriate. Model answers identify essential marking points.

Question 1. Define a database and a DBMS.

Model answer: A database is an organised collection of related data; a DBMS is software that defines, queries, updates, secures, controls and recovers that data.

Question 2. Distinguish data, information and knowledge.

Model answer: Data are recorded facts; information is data interpreted in context; knowledge is understanding used to explain or guide action.

Question 3. Explain the three-schema architecture.

Model answer: External views serve users and applications; the conceptual schema represents the logical organisation-wide model; the internal schema describes physical storage.

Question 4. Compare relational and document databases.

Model answer: Relational systems use structured relations and constraints; document systems store document-shaped records with flexible structures. Choose according to relationships, queries, consistency and operations.

Question 5. What is an entity in ER modelling?

Model answer: A distinguishable type of thing about which the organisation stores data, such as Student, Course or Invoice.

Question 6. Explain one-to-many and many-to-many relationships.

Model answer: One-to-many means one parent can relate to several child records; many-to-many means multiple instances on both sides and is normally resolved with an associative relation.

Question 7. Differentiate primary and foreign keys.

Model answer: A primary key uniquely identifies a row; a foreign key references a key in another relation and supports referential integrity.

Question 8. What is a functional dependency?

Model answer: X → Y means values of attribute set X determine values of attribute set Y under the relation's business rules.

Question 9. Explain first, second and third normal forms.

Model answer: 1NF removes repeating groups; 2NF removes partial dependencies on a composite key; 3NF removes transitive dependencies of non-key attributes.

Question 10. What is denormalisation and why use it?

Model answer: Controlled duplication or precomputation to improve measured read performance, at the cost of extra consistency and refresh responsibilities.

Question 11. Differentiate DDL, DML, DQL and DCL.

Model answer: DDL defines structures; DML changes rows; DQL retrieves data; DCL manages permissions. TCL controls transactions.

Question 12. Write a query to retrieve students in programme 10.

Model answer: SELECT StudentID, StudentName FROM Student WHERE ProgrammeID = 10;

Question 13. Explain NULL in SQL.

Model answer: NULL represents missing or unknown information and must be tested with IS NULL or IS NOT NULL rather than ordinary equality.

Question 14. Compare INNER JOIN and LEFT JOIN.

Model answer: INNER JOIN returns matching rows only; LEFT JOIN preserves all left-side rows and fills unmatched right-side columns with NULL.

Question 15. What is GROUP BY used for?

Model answer: It partitions rows into groups so aggregate functions can calculate counts, sums, averages or other summaries for each group.

Question 16. Differentiate WHERE and HAVING.

Model answer: WHERE filters individual rows before grouping; HAVING filters groups after aggregate calculations.

Question 17. State the ACID properties.

Model answer: Atomicity, consistency, isolation and durability.

Question 18. Explain a lost update anomaly.

Model answer: Two concurrent transactions read the same value and later write changes, causing one update to overwrite the other.

Question 19. Why are indexes useful and costly?

Model answer: They speed selected lookups and ordering but consume storage and add work to inserts, updates and deletes.

Question 20. What does an execution plan show?

Model answer: The DBMS's chosen operations for a query, such as scans, index access, joins, sorts and estimated costs.

Question 21. Differentiate a view and a materialised view.

Model answer: A normal view presents a stored query; a materialised view stores query results and needs refresh to remain current.

Question 22. Why use least privilege for database accounts?

Model answer: It limits the damage from compromised credentials or mistakes by granting only permissions necessary for each role.

Question 23. What is the difference between backup and replication?

Model answer: Backups preserve recoverable historical copies; replication maintains copies on other nodes for availability or read scaling and may replicate corruption or deletion.

Question 24. Compare full, incremental and differential backups.

Model answer: Full copies all selected data; incremental copies changes since the prior backup; differential copies changes since the last full backup.

Question 25. What is a data warehouse?

Model answer: An integrated store of historical data designed for analysis and reporting rather than primarily for operational transactions.

Question 26. Explain a star schema.

Model answer: A dimensional model with a central fact table at a defined grain linked to descriptive dimension tables.

Question 27. What is data lineage?

Model answer: A record of data origin, movement and transformations that supports traceability, quality analysis and trust.

Question 28. Explain eventual consistency.

Model answer: Replicas may temporarily differ but are expected to converge after updates stop and propagation completes.

Question 29. Why is a database migration rehearsal important?

Model answer: It reveals timing, transformation, integrity, reconciliation and rollback problems before production cutover.

Question 30. Identify four dimensions of data quality.

Model answer: Accuracy, completeness, consistency, validity, uniqueness and timeliness.

Question 31. What is master data management?

Model answer: Governance and processes that maintain trusted shared records for core entities such as students, customers, products or suppliers.

Question 32. Why is a database backup restore test essential?

Model answer: It verifies that backup media and procedures actually recover usable, consistent data within required recovery objectives.

Official Education References

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