Data Management and Database Technologies

Introduction and Context

Modern organisations depend on data for operational control, customer service, compliance, forecasting and strategic decision-making. Data management is the disciplined practice of collecting, organising, storing, protecting, integrating and using data throughout its lifecycle. Database technologies provide the technical foundation for this practice, but effective data management also requires sound modelling, governance, quality controls, security and recovery planning.

An advanced information technology professional must be able to select an appropriate database architecture, translate business requirements into data models, implement relational schemas, write reliable SQL, understand NoSQL alternatives, improve performance through indexing, and protect data against corruption, loss and unauthorised use.

This chapter treats databases as both technical systems and organisational assets. It therefore connects database design with transaction processing, analytics, metadata, governance, privacy and operational resilience.

Learning Objectives

By the end of this chapter, a student should be able to:

1. Explain the role and architecture of modern database management systems.
2. Model business data using entities, attributes, relationships and constraints.
3. Apply normalisation principles and recognise appropriate denormalisation.
4. Create and query relational databases using SQL.
5. Explain transactions, concurrency control and the ACID properties.
6. Compare relational, document, key-value, column-family and graph databases.
7. Use indexes appropriately while recognising their costs.
8. Assess data quality, metadata and governance requirements.
9. Describe data warehouses, data lakes and analytical pipelines.
10. Design suitable backup, recovery and continuity practices.

1. Database Management Foundations

A database is an organised collection of related data managed so that authorised users and applications can store, retrieve and update information efficiently. A database management system (DBMS) is the software that provides this capability. Examples include PostgreSQL, MySQL, Microsoft SQL Server, Oracle Database, MongoDB and Neo4j.

A DBMS generally provides:

• a data definition facility for creating schemas and constraints;
• a data manipulation facility for inserting, updating, deleting and querying data;
• transaction management;
• concurrency control;
• security and access control;
• backup and recovery;
• performance monitoring and optimisation; and
• metadata management.

The separation between applications and stored data is a major advantage. An application can request a logical view of data without knowing its physical storage details. This is called data independence. Logical data independence means that some changes to the logical schema need not affect user views. Physical data independence means that storage structures, indexes or file locations can change without changing application logic.

A typical architecture has three levels:

The schema describes the structure of the database, whereas the instance is the data stored at a particular time. A constraint is a rule that restricts valid states, such as requiring every order to refer to an existing customer.

2. Data Modelling

2.1 From requirements to a conceptual model

Database design begins with requirements analysis rather than table creation. Analysts identify business objects, facts, events, rules and expected queries. A useful sequence is:

1. Identify important nouns, such as Customer, Product and Invoice.
2. Identify relationships, such as Customer places Order.
3. Identify attributes, such as customer email or product price.
4. Identify identifiers and business rules.
5. Validate the model with users and sample transactions.
6. Convert the conceptual model into a logical schema.

An entity is a distinguishable object or concept. An attribute describes an entity. A relationship represents an association between entities. Cardinality describes how many instances may participate in a relationship: one-to-one, one-to-many or many-to-many.

For example, a customer may place many orders, but each order belongs to one customer. This is a one-to-many relationship. A product may appear on many orders, and an order may contain many products. This many-to-many relationship requires an associative entity, such as OrderLine, which also stores quantity and selling price.

2.2 Keys and integrity constraints

A candidate key is a minimal attribute set that uniquely identifies a row. One candidate key is selected as the primary key. A foreign key references a candidate key in another table and represents a relationship.

Important integrity constraints include:

• Entity integrity: a primary-key value is unique and not null.
• Referential integrity: a foreign-key value must match a referenced key, unless null is explicitly permitted.
• Domain integrity: values must have an appropriate type, range or format.
• Business constraints: rules specific to the organisation, such as quantity > 0 or an order date not preceding a customer’s registration date.

A surrogate key is an artificial identifier, such as an automatically generated integer or UUID. A natural key is derived from business data, such as a national registration number. Surrogate keys simplify relationships when natural identifiers are long or changeable, but important natural-key uniqueness constraints should still be enforced where required.

3. Relational Design and Normalisation

The relational model represents data as relations, commonly implemented as tables. A relation consists of attributes (columns) and tuples (rows). Relational algebra provides operations such as selection, projection, union and join; SQL is the practical language used to express these operations.

3.1 Functional dependencies

A functional dependency exists when one attribute or set of attributes determines another. It is written as X → Y, meaning that if two rows have the same value of X, they must have the same value of Y. For example, CustomerID → CustomerName means each CustomerID identifies one customer name.

Functional dependencies help identify redundancy and decomposition opportunities. They must be based on business rules, not merely on the values observed in a small sample.

3.2 Normal forms

Normalisation is a structured method for reducing redundancy and update anomalies.

• First normal form (1NF): each attribute contains atomic values, and repeating groups are removed. A column should not store an unstructured list of product identifiers.
• Second normal form (2NF): the relation is in 1NF and every non-key attribute depends on the whole candidate key, not only part of a composite key.
• Third normal form (3NF): the relation is in 2NF and non-key attributes do not depend transitively on a key through another non-key attribute.
• Boyce–Codd normal form (BCNF): every determinant of a non-trivial functional dependency is a candidate key. BCNF is stricter than 3NF.

Consider the relation:

OrderLine(OrderID, ProductID, ProductName, Quantity, UnitPrice)

Assume the composite key is (OrderID, ProductID), and ProductID → ProductName, UnitPrice. ProductName and UnitPrice depend only on ProductID, not on the whole composite key. This violates 2NF. A decomposition is:

Product(ProductID, ProductName, UnitPrice)

OrderLine(OrderID, ProductID, Quantity)

The decomposition reduces duplication and prevents inconsistent product descriptions. A good decomposition should be lossless, meaning that joining the decomposed tables reconstructs the intended information, and preferably dependency-preserving, meaning important dependencies can still be enforced without reconstructing a complex join.

3.3 Denormalisation

Normalisation is not an absolute rule. Read-intensive systems may deliberately duplicate data to reduce joins, support reporting or improve response time. Denormalisation should be controlled, documented and supported by mechanisms such as derived-value refreshes, triggers, scheduled jobs or application-level consistency checks. It increases storage and update complexity, so it should follow measured performance evidence rather than intuition.

4. SQL and Relational Implementation

SQL is declarative: the developer specifies the desired result, while the DBMS selects an execution plan. SQL is commonly divided into:

• DDL: CREATE, ALTER and DROP;
• DML: SELECT, INSERT, UPDATE and DELETE;
• DCL: GRANT and REVOKE; and
• TCL: COMMIT, ROLLBACK and SAVEPOINT.

A simplified schema might be written as follows:

CREATE TABLE Customer (
    customer_id BIGINT PRIMARY KEY,
    full_name VARCHAR(120) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE SalesOrder (
    order_id BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    order_date DATE NOT NULL,
    status VARCHAR(20) NOT NULL,
    CONSTRAINT fk_order_customer
        FOREIGN KEY (customer_id) REFERENCES Customer(customer_id)
);

A query joining customers and orders is:

SELECT c.full_name, o.order_id, o.order_date
FROM Customer AS c
JOIN SalesOrder AS o
  ON o.customer_id = c.customer_id
WHERE o.status = 'PAID'
ORDER BY o.order_date DESC;

WHERE filters rows before grouping. GROUP BY forms groups, and HAVING filters groups after aggregation.

SELECT customer_id, COUNT(*) AS order_count
FROM SalesOrder
GROUP BY customer_id
HAVING COUNT(*) >= 5;

A join must be based on meaningful relationship conditions. An accidental omission of the join predicate creates a Cartesian product, potentially producing an extremely large and incorrect result.

Parameterised queries should be used instead of string concatenation. For example, an application should pass a customer identifier as a parameter rather than construct SQL from untrusted input. This reduces SQL injection risk and often improves plan reuse.

5. Transactions and Concurrency

A transaction is a logical unit of work that should move the database from one valid state to another. The ACID properties are:

• Atomicity: all operations of the transaction succeed or none take effect.
• Consistency: committed transactions preserve declared constraints and business rules.
• Isolation: concurrent transactions do not produce unacceptable interference.
• Durability: committed changes survive a system failure, subject to the durability guarantees of the DBMS and storage system.

Consider transferring 100 currency units from Account A to Account B. The debit and credit must be treated as one transaction:

BEGIN;

UPDATE Account
SET balance = balance - 100
WHERE account_id = 1 AND balance >= 100;

-- Application verifies that one row was updated.

UPDATE Account
SET balance = balance + 100
WHERE account_id = 2;

COMMIT;

If the balance condition fails or the second update cannot be completed, the transaction should be rolled back. The application must also verify affected-row counts and handle errors.

Concurrency anomalies include:

• Dirty read: reading uncommitted data from another transaction.
• Non-repeatable read: reading a row twice and obtaining different committed values.
• Phantom read: repeating a range query and finding new or removed matching rows.
• Lost update: one write unintentionally overwrites another.

Isolation levels represent trade-offs between consistency and concurrency. Common levels are Read Uncommitted, Read Committed, Repeatable Read and Serializable. Serializable provides the strongest standard isolation but can reduce concurrency. Some DBMSs implement snapshot or multi-version concurrency control, allowing readers to use consistent versions while writers continue working.

Locks may be shared for reading or exclusive for modification. Poorly designed transactions can cause deadlocks, in which two transactions wait indefinitely for each other. DBMSs normally detect deadlocks and abort one transaction; applications should retry safely where appropriate.

6. Indexing and Query Performance

An index is an auxiliary data structure that helps locate rows without scanning every table row. B-tree indexes are general-purpose structures suitable for equality and range searches. Hash indexes can be effective for equality searches but are generally less suitable for ordered ranges. Bitmap indexes may help low-cardinality analytical workloads, depending on the DBMS.

A useful index often supports columns used in selective filters, joins, ordering or grouping. A composite index on (customer_id, order_date) generally supports searches beginning with customer_id because of the leftmost-prefix principle in common B-tree implementations. It may not efficiently support a search only on order_date.

Indexes have costs:

• additional storage;
• slower INSERT, UPDATE and DELETE operations;
• maintenance and fragmentation; and
• possible optimiser confusion when indexes are excessive or statistics are stale.

Query plans should be examined using the DBMS execution-plan facility. A plan can reveal table scans, inefficient joins, poor cardinality estimates or excessive sorting. Performance decisions should be based on representative workloads, realistic data volumes and measured response times.

A simple average response-time measure is:

Here, Tᵢ is the response time of request i, and n is the number of measured requests. The measure is meaningful only when the workload, measurement method and units—normally milliseconds or seconds—are stated. Percentiles such as p95 and p99 are often more informative for user experience because averages can hide occasional severe delays.

7. NoSQL Database Technologies

NoSQL systems are not one single model. They are designed for particular access patterns, scale requirements, data structures or availability objectives.

Document databases allow related data to be embedded, which can make a common read efficient. Referencing separate documents may be preferable when data is shared widely or changes independently. NoSQL designs usually begin with query and access patterns rather than universal normalisation.

A document might contain a customer and embedded contact preferences, while orders remain in a separate collection because they grow continuously. The design must address document size, update consistency, duplication and indexing.

NoSQL systems may provide flexible schemas, horizontal scaling and high availability, but flexibility does not remove the need for data discipline. Applications still require validation, versioning, security, lifecycle rules and documented meaning. The choice between SQL and NoSQL should be based on requirements, not fashion. Polyglot persistence—using different database technologies for different workloads—can be effective but increases operational and integration complexity.

8. Data Quality, Metadata and Governance

Data quality is fitness for intended use. Important dimensions include:

• accuracy;
• completeness;
• consistency;
• timeliness;
• validity;
• uniqueness; and
• accessibility.

Quality rules should be explicit. For example, an email field may require a permitted format, an order quantity must be a positive integer, and a customer identifier must be unique. Profiling tools can identify null rates, duplicates, invalid codes and unusual distributions.

Data quality can be assessed using metrics. If 9,800 of 10,000 required records contain a valid value, completeness for that field is:

Thus, completeness is (9,800 ÷ 10,000) × 100% = 98%. The definition of “valid or present” must be documented; a non-null value is not necessarily accurate.

Metadata is data about data. Technical metadata describes tables, columns, types, constraints and lineage. Business metadata explains meaning, ownership, permissible use and definitions. Operational metadata records refresh time, job status and quality results. A data catalogue makes metadata discoverable and supports stewardship.

Governance assigns accountability for data decisions. A data owner is accountable for a domain, while a data steward manages definitions, quality rules and issue resolution. Governance should address retention, classification, access, auditability, privacy, third-party sharing and lawful disposal.

Personal data should be collected for specified purposes, minimised where possible, protected through appropriate controls and retained only as long as justified by law or business need. Exact obligations depend on jurisdiction, but privacy principles such as purpose limitation, transparency, access rights and breach response are broadly important.

9. Data Warehousing and Analytical Platforms

Operational databases are commonly optimised for frequent, concurrent transactions. Analytical systems are optimised for complex scans, aggregations and historical comparison. A data warehouse integrates curated, structured data for reporting and analysis.

A common dimensional model contains:

• a fact table, containing measurable events such as sales amount and quantity; and
• dimension tables, containing descriptive context such as date, customer, product and location.

A star schema has a central fact table connected directly to denormalised dimensions. A snowflake schema normalises some dimensions and may reduce duplication at the cost of additional joins.

Extract, transform, load (ETL) processes transform data before loading it into the warehouse. Extract, load, transform (ELT) loads raw data first and uses the target platform for transformation. Incremental loading should use reliable change detection, such as timestamps, log-based capture or source-system change records.

A data lake stores large volumes of raw or semi-structured data, while a lakehouse attempts to combine lake flexibility with warehouse management and analytical reliability. Without cataloguing, quality controls and ownership, a data lake can become a “data swamp.”

Historical dimensions require an explicit policy. In a Type 1 approach, an old value is overwritten. In a Type 2 approach, a new dimension row is created with effective dates or a current-row indicator, preserving history. The appropriate method depends on reporting requirements.

10. Backup, Recovery and Resilience

A backup is a recoverable copy of data. Full backups copy the selected dataset. Incremental backups copy changes since a previous backup, while differential backups copy changes since the last full backup. Transaction-log or archive-log backups can support point-in-time recovery.

Two planning measures are particularly important:

• Recovery Point Objective (RPO): the maximum acceptable amount of data loss measured in time.
• Recovery Time Objective (RTO): the maximum acceptable time to restore service.

For example, an RPO of 15 minutes means the organisation may accept losing up to 15 minutes of committed changes. An RTO of two hours means service should be restored within two hours. These are business requirements, not merely technical settings.

A sound strategy follows the 3-2-1 principle: maintain at least three copies, on at least two different media or storage systems, with at least one copy off-site or isolated. Backups should be encrypted, access-controlled, monitored and tested. A backup that has never been restored is an assumption, not evidence of recoverability.

Recovery planning should document roles, dependencies, credentials, communication procedures, prioritised services and validation checks. It should include scenarios such as accidental deletion, hardware failure, ransomware, corruption and regional outage. Replication improves availability but is not automatically a backup: corruption or malicious deletion may replicate to the secondary system.

11. Security and Operational Management

Database security uses layered controls:

• least-privilege roles;
• strong authentication and, where appropriate, multifactor authentication;
• encryption in transit and at rest;
• separation of duties;
• auditing of sensitive actions;
• network segmentation;
• patching and vulnerability management; and
• masking or tokenisation of sensitive data in non-production environments.

Access should be granted to roles rather than individuals wherever practical. Administrative accounts require especially strong controls. Audit logs should be protected from unauthorised alteration and reviewed for meaningful events.

Operational management includes capacity planning, storage forecasting, connection-pool management, statistics maintenance, patch testing, monitoring and incident response. Database administrators and developers should collaborate: a technically valid schema may still be unsuitable if it creates unacceptable operational risk.

12. Applied Case: Illustrative Online Retail Database

Background

An illustrative online retailer sells electronics through a website and mobile application. Its original system stores customers, orders and products in a single spreadsheet-like table. Customer details are repeated on every order, product prices are overwritten when they change, and monthly reports are slow.

Problem

The organisation experiences duplicate customers, inconsistent addresses, accidental deletion of product information and uncertainty about historical prices. The business requires reliable order processing, current inventory views, monthly sales analysis and recovery after service interruption.

Analysis

The main entities are Customer, Order, OrderLine, Product, Payment and Shipment. Customer-to-Order is one-to-many, while Order-to-Product is many-to-many and therefore requires OrderLine. Product description and current price depend on ProductID. The price charged at the time of sale, however, is an attribute of OrderLine and must be preserved independently of later product-price changes.

The operational model is normalised to approximately 3NF. Primary and foreign keys enforce relationships, while checks enforce positive quantities and permitted order statuses. A unique constraint is applied to the appropriate customer contact identifier after the organisation defines its identity policy.

Approach and solution

The retailer uses a relational DBMS for orders and payments because transaction integrity is central. A document store is introduced only for flexible product specifications, where different product categories have different attributes. An analytical warehouse receives validated sales events through an incremental pipeline.

Indexes are created on Order.customer_id, Order.order_date and OrderLine.product_id after reviewing actual query plans. A composite index supports the common customer order-history query. The team avoids indexing every column because write performance and storage matter.

Transactions ensure that order creation, inventory reservation and payment state changes follow a controlled workflow. Idempotency keys prevent a retry from creating duplicate payments. Daily full backups, frequent log backups and an isolated off-site copy support the agreed RPO and RTO. Quarterly restoration tests verify the plan.

Outcome

The design removes update anomalies, preserves historical selling prices, improves order-query performance and makes monthly reporting reproducible. Governance assigns a product-data steward, defines ownership of customer data and documents retention and access rules.

Learning points

1. A database model must reflect business rules, not just current screens.
2. Transaction boundaries should correspond to meaningful business operations.
3. Historical facts should not be overwritten accidentally.
4. Indexes and technology choices should be evidence-based.
5. Recovery capability must be tested, measured and connected to business priorities.

13. Contemporary Developments and Professional Implications

Cloud-managed databases reduce infrastructure administration but do not eliminate design responsibility. They introduce choices involving regions, service tiers, autoscaling, vendor lock-in, data residency and shared-responsibility security. Serverless and distributed SQL platforms can simplify scaling, but distributed transactions and consistency behaviour must be understood.

Artificial intelligence is increasingly used for anomaly detection, natural-language query assistance, schema discovery and data-quality monitoring. AI-generated SQL must be reviewed for correctness, security, performance and unintended data exposure. Training or prompting systems with confidential records can create privacy risks. Human accountability remains necessary for data definitions, access decisions and regulatory compliance.

The professional database practitioner therefore combines technical competence with critical judgement. The best solution is not necessarily the newest or most scalable system; it is the system whose correctness, security, maintainability, performance and cost fit the organisation’s actual requirements.

Challenges and Limitations

Database systems involve unavoidable trade-offs. Strong consistency can increase latency or reduce availability in distributed environments. Flexible schemas can accelerate development but make governance and quality assurance harder. Replication can improve availability but complicate conflict resolution. Normalisation reduces redundancy but may require joins; denormalisation improves selected reads but increases consistency risk. Encryption protects confidentiality but introduces key-management responsibilities. Cloud services improve elasticity but create dependency on provider capabilities and network connectivity.

These trade-offs should be documented through requirements, risk assessment, prototypes, load tests and operational evidence. Design decisions should also be revisited as data volume, access patterns, regulation and organisational objectives change.

Chapter Summary

Data management combines modelling, storage, quality, governance, security and lifecycle control. A DBMS provides schemas, constraints, query processing, transactions, concurrency management, recovery and metadata. Relational design uses entities, keys, dependencies and normalisation to reduce redundancy and anomalies. SQL supports definition, manipulation, security and transaction control.

Transactions use ACID properties to protect business operations, while isolation levels and locking manage concurrent access. Indexes can improve query performance but impose storage and write costs. NoSQL systems provide alternative document, key-value, wide-column and graph models for particular access patterns. Data quality and metadata make information trustworthy and understandable. Warehouses, lakes and lakehouses support analytical uses distinct from operational processing. Backup, recovery and resilience planning connect technical controls to RPO and RTO requirements. Finally, privacy, least privilege, auditing, testing and responsible use of cloud and AI technologies are essential professional practices.

Key Terms

Database: An organised collection of related data.

DBMS: Software that creates, manages and controls access to databases.

Schema: The defined structure and rules of a database.

Entity: A distinguishable object or concept represented in a data model.

Primary key: A selected candidate key that uniquely identifies rows.

Foreign key: An attribute that references a key in another table.

Referential integrity: The requirement that valid foreign-key references be maintained.

Functional dependency: A rule in which one attribute set determines another.

Normalisation: The systematic reduction of redundancy and update anomalies.

Denormalisation: Deliberate duplication or restructuring to support performance or access needs.

Transaction: A logical unit of database work.

ACID: Atomicity, consistency, isolation and durability properties of transactions.

Index: An auxiliary structure that accelerates selected data access operations.

NoSQL: A category of non-relational database technologies.

Metadata: Data describing the structure, meaning, origin or use of data.

Data quality: The fitness of data for its intended purpose.

Data warehouse: A curated analytical repository integrating historical data.

RPO: Maximum acceptable data loss measured in time.

RTO: Maximum acceptable time to restore service.

Review Questions

Short Answer Questions

1. Distinguish between a database schema and a database instance.
2. What is the purpose of a foreign key?
3. State the four ACID properties.
4. Give two costs associated with maintaining indexes.
5. Explain the difference between RPO and RTO.

Long Answer Questions

1. Explain the process of converting business requirements into a relational database design.
2. Describe 1NF, 2NF and 3NF, using an order-processing example.
3. Compare relational and document databases in terms of structure, consistency, scalability and use cases.
4. Explain common transaction anomalies and how isolation or concurrency controls address them.
5. Discuss the role of metadata, data quality and governance in an enterprise data environment.

Analytical / Application Questions

1. Design a normalised schema for a university course-registration system containing students, courses, sections and enrolments. Identify keys, cardinalities and at least four integrity constraints.
2. An order-history query filters by customer identifier and sorts by order date, but performance declines as the table grows. Recommend an indexing and testing strategy, explaining its likely benefits and costs.
3. A hospital requires an RPO of five minutes and an RTO of one hour. Propose a backup, replication, security and restoration-testing plan, and explain how you would verify that it meets those objectives.

Multiple Choice Questions with Answers

Question 1

Which constraint primarily ensures that a foreign-key value refers to an existing row in another table?

A. Entity integrity

B. Referential integrity

C. Domain integrity

D. User-interface validation

Correct Answer: B. Referential integrity

Question 2

A relation has a composite key (OrderID, ProductID), but ProductName depends only on ProductID. Which normal form is violated?

A. First normal form

B. Second normal form

C. Third normal form only

D. Fourth normal form

Correct Answer: B. Second normal form

Question 3

Which ACID property ensures that a committed transaction survives a system failure, subject to system guarantees?

A. Atomicity

B. Consistency

C. Isolation

D. Durability

Correct Answer: D. Durability

Question 4

What is the main purpose of a parameterised SQL query?

A. To remove the need for primary keys

B. To guarantee that every query uses an index

C. To reduce SQL injection risk

D. To convert a relational database into a document database

Correct Answer: C. To reduce SQL injection risk

Question 5

Which database model represents information primarily as nodes and relationships?

A. Key-value

B. Document

C. Graph

D. Wide-column

Correct Answer: C. Graph

Question 6

A field contains valid values for 9,800 of 10,000 required records. What is its completeness percentage?

A. 2%

B. 9.8%

C. 98%

D. 99.8%

Correct Answer: C. 98%

Question 7

Which statement about indexes is correct?

A. Indexes always improve INSERT performance

B. Indexes can improve reads but add storage and write-maintenance costs

C. Indexes eliminate the need for query-plan analysis

D. Every column should have at least one index

Correct Answer: B. Indexes can improve reads but add storage and write-maintenance costs

Question 8

What does an RPO of 15 minutes describe?

A. The maximum acceptable time to restore service

B. The maximum acceptable amount of data loss measured in time

C. The maximum database query duration

D. The minimum backup retention period

Correct Answer: B. The maximum acceptable amount of data loss measured in time

Question 9

Which structure is central in a dimensional data warehouse and stores measurable business events?

A. Fact table

B. Access-control list

C. Transaction log

D. Data dictionary only

Correct Answer: A. Fact table

Question 10

Why is replication alone not necessarily a sufficient backup strategy?

A. Replication cannot copy any committed data

B. Replicated corruption or deletion may affect the secondary system

C. Replication prevents all high-availability designs

D. Replicated systems cannot be encrypted

Correct Answer: B. Replicated corruption or deletion may affect the secondary system