Databases, Data Management, and Analytics

Introduction and Context

Modern computing systems are fundamentally data-processing systems. Applications collect, store, transform, exchange, and interpret data in order to support transactions, scientific discovery, public administration, and organizational decision-making. A database is not merely a large collection of files; it is an organized, managed, and governed representation of facts that can be queried and updated reliably. Data management extends this concern to the complete lifecycle of data: creation, acquisition, storage, integration, quality assurance, security, use, archival, and disposal. Analytics uses data, statistical reasoning, computational methods, and domain knowledge to produce descriptive, diagnostic, predictive, or prescriptive insights.

At postgraduate level, database study requires more than learning SQL syntax. It involves understanding data models, abstraction, integrity, transaction processing, distributed systems, data governance, information architecture, and the limits of analytical inference. A technically sophisticated database can still produce poor outcomes if its data is biased, incomplete, insecure, or interpreted without attention to context.

Learning Objectives

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

1. Explain the purpose, architecture, and evolution of database systems.
2. Model data using entities, relationships, relations, keys, and constraints.
3. Apply normalization principles and identify common design anomalies.
4. Formulate SQL queries for definition, manipulation, aggregation, and analysis.
5. Explain transactions, ACID properties, concurrency control, and recovery.
6. Compare relational, NoSQL, distributed, and analytical data architectures.
7. Assess data quality, governance, security, privacy, and ethical risk.
8. Distinguish descriptive, diagnostic, predictive, and prescriptive analytics.
9. Evaluate analytical models using appropriate metrics and validation practices.
10. Design a responsible data solution for a realistic organizational problem.

1. Data, Information, and Database Systems

Data consists of recorded observations, symbols, measurements, or representations. Information is data interpreted in a context that gives it meaning. A database is a persistent, shared, logically structured collection of data managed by a database management system (DBMS). The DBMS provides mechanisms for storage, querying, concurrency, integrity, security, backup, and recovery.

A DBMS separates applications from physical storage. This separation is called data independence. Physical data independence means that storage structures, indexes, or file organization can change without changing the logical schema. Logical data independence means that changes to the logical schema can, within limits, be insulated from application views.

A useful three-level architecture is:

• External level: user or application views.
• Conceptual level: the organization-wide logical structure of data.
• Internal level: files, pages, indexes, compression, and storage devices.

This architecture supports abstraction. Users need not know how records are placed on disk, while database administrators can optimize storage without rewriting every application.

Database terminology

• Schema: the formal description of data structures, constraints, and relationships.
• Instance: the actual contents of a database at a particular time.
• Relation: a table in the relational model.
• Tuple: a row in a relation.
• Attribute: a named property or column.
• Domain: the permitted set of values for an attribute.
• Metadata: data describing data, such as definitions, lineage, ownership, and formats.
• Query: a request to retrieve or manipulate data.
• Index: an auxiliary structure that accelerates retrieval, usually at additional storage and update cost.

2. Data Models and Conceptual Design

A data model specifies how data objects, relationships, constraints, and operations are represented. The relational model represents data as relations and uses keys and constraints to connect tables. The entity–relationship (ER) model is commonly used before relational implementation because it expresses concepts in a form accessible to both technical and non-technical stakeholders.

In an ER model, an entity type represents a class of objects, such as Student or Course. An attribute describes an entity, such as StudentID or CourseTitle. A relationship describes an association, such as Student enrolls in Course. Cardinality specifies how many instances may participate: one-to-one, one-to-many, or many-to-many. Participation may be mandatory or optional.

For example, a university system may contain:

• STUDENT(StudentID, Name, Programme)
• COURSE(CourseID, Title, Credits)
• ENROLMENT(StudentID, CourseID, Semester, Grade)

The ENROLMENT relation resolves the many-to-many relationship between students and courses. StudentID and CourseID together may form a composite primary key if a student can enroll in a course only once per semester; otherwise Semester must also be included in the key.

Keys and integrity constraints

A candidate key is a minimal attribute set that uniquely identifies a tuple. One candidate key is selected as the primary key. A foreign key references a candidate or primary key in another relation.

Important constraints include:

• Domain constraint: values must belong to an appropriate domain.
• Entity integrity: a primary-key value cannot be null.
• Referential integrity: a foreign-key value must match an existing referenced key or be null when permitted.
• Semantic constraints: rules arising from the application domain, such as credits being positive or a course grade belonging to an approved set.

Constraints should be enforced as close to the data as practical. Application-only validation is dangerous because multiple applications may write to the same database and may implement rules inconsistently.

3. Relational Algebra, SQL, and Query Processing

Relational algebra provides a formal foundation for relational queries. Its basic operators include selection, projection, union, set difference, Cartesian product, and join. Selection chooses rows satisfying a predicate; projection chooses columns; a join combines related tuples according to a condition.

SQL is declarative: the user specifies what result is required rather than the exact access path. The DBMS parses SQL, checks names and types, transforms the query into an internal representation, and selects an execution plan using statistics and cost estimates.

A conceptual query for students enrolled in a postgraduate database course is:

SELECT s.StudentID, s.Name
FROM Student AS s
JOIN Enrolment AS e ON e.StudentID = s.StudentID
JOIN Course AS c ON c.CourseID = e.CourseID
WHERE c.Title = 'Advanced Databases';

Aggregation supports analysis:

SELECT CourseID, COUNT(*) AS EnrolmentCount,
       AVG(Marks) AS MeanMarks
FROM Enrolment
GROUP BY CourseID
HAVING COUNT(*) >= 10;

WHERE filters rows before grouping, whereas HAVING filters groups after aggregation. Null values require special care. SQL uses three-valued logic—true, false, and unknown—so a comparison with NULL is not true or false. IS NULL and IS NOT NULL should be used for explicit null tests.

Query performance

An index may reduce the number of pages examined for selective searches, but it consumes storage and increases insert, update, and delete costs. Index design should be guided by workload, selectivity, update frequency, and execution plans rather than by indiscriminate indexing. A query optimizer may choose a sequential scan, index scan, hash join, nested-loop join, or sort-merge join depending on estimated costs.

Performance analysis should consider latency, throughput, concurrency, data volume, and tail behavior. Mean response time alone may conceal unacceptable 95th- or 99th-percentile latency.

4. Relational Design and Normalization

Poor schema design causes update anomalies. An insertion anomaly occurs when a fact cannot be recorded without an unrelated fact. A deletion anomaly occurs when deleting one fact unintentionally removes another. An update anomaly occurs when the same fact is stored in several places and only some copies are changed.

Normalization uses functional dependencies to reduce unnecessary redundancy. A functional dependency X → Y means that values of X determine values of Y. A relation is in First Normal Form (1NF) when attributes contain atomic values rather than repeating groups. Second Normal Form (2NF) requires 1NF and that every non-key attribute be fully functionally dependent on the whole candidate key. Third Normal Form (3NF) requires 2NF and no transitive dependency of a non-key attribute on a key through another non-key attribute. Boyce–Codd Normal Form (BCNF) requires every determinant of a nontrivial functional dependency to be a candidate key.

Consider:

ENROLMENT(StudentID, StudentName, CourseID, CourseTitle, InstructorID, InstructorName, Grade)

Assume:

• StudentID → StudentName
• CourseID → CourseTitle, InstructorID
• InstructorID → InstructorName
• (StudentID, CourseID) → Grade

The composite key is (StudentID, CourseID). StudentName depends only on StudentID, and CourseTitle depends only on CourseID, violating 2NF. InstructorName is transitively dependent through InstructorID. Decomposition into STUDENT, COURSE, INSTRUCTOR, and ENROLMENT removes these redundancies.

Normalization is not an absolute command to split every table. Read-heavy analytical systems may deliberately denormalize to reduce joins. Such denormalization should be documented, tested, and supported by mechanisms that preserve consistency.

5. Transactions, Concurrency, and Recovery

A transaction is a logical unit of work. The classical ACID properties are:

• Atomicity: all operations occur, or none do.
• Consistency: integrity constraints are preserved from one valid state to another.
• Isolation: concurrent transactions behave according to the selected isolation guarantees.
• Durability: committed effects survive failures.

ACID does not mean that every system has identical isolation behavior. Common anomalies include dirty reads, non-repeatable reads, and phantom reads. Isolation levels trade concurrency against consistency. Locking, multiversion concurrency control (MVCC), timestamp ordering, and optimistic validation are widely used approaches.

A serial schedule executes transactions one after another. A concurrent schedule is conflict-serializable if its precedence graph is acyclic; each node represents a transaction, and an edge indicates a conflicting operation order. An acyclic graph implies that the schedule is equivalent to some serial execution.

Recovery commonly uses write-ahead logging (WAL): a log record describing a change is written to stable storage before the changed data page is written. After a crash, recovery can redo committed operations and undo incomplete operations, depending on the logging protocol. Checkpoints reduce the amount of log that must be examined.

6. Distributed and Non-Relational Data Management

Distributed databases place data across multiple nodes. Distribution can improve availability, locality, and scalability, but introduces network delay, partial failure, replication complexity, and consistency trade-offs. Replication may be synchronous or asynchronous. Partitioning may be horizontal, vertical, or functional.

The CAP perspective states that, during a network partition, a distributed system cannot simultaneously guarantee both availability and strong consistency for all operations. CAP is specifically about partition tolerance and the choice between consistency and availability during a partition; it does not claim that systems permanently choose only two of the three properties in ordinary operation.

NoSQL systems are not a single model. Major categories include:

The choice between relational and non-relational systems should follow workload, consistency, relationship complexity, governance, operational capability, and lifecycle requirements. Polyglot persistence may use different technologies for different bounded contexts, but it increases operational and integration burden.

7. Data Warehousing, Lakehouses, and Data Pipelines

Operational databases are optimized for current transactions (OLTP), while analytical systems (OLAP) support complex queries over historical and integrated data. A data warehouse commonly uses dimensional modeling. A fact table stores measurable events, while dimension tables describe entities such as customer, product, and time. A star schema has a central fact table connected to denormalized dimensions.

A data pipeline typically includes extraction, validation, transformation, loading, orchestration, monitoring, and lineage capture. ETL transforms data before loading; ELT loads relatively raw data and transforms it within the analytical platform. A data lake stores diverse raw or semi-structured data, whereas a lakehouse seeks to combine lake flexibility with warehouse management and transactional features.

A mature architecture distinguishes:

• System of record: authoritative operational source.
• System of reference: integrated source used across processes.
• Semantic layer: governed definitions of measures and dimensions.
• Data catalog: searchable metadata, ownership, lineage, and quality information.

A metric such as “active customer” must have a precise definition, time window, exclusion rule, and owner. Otherwise, different departments may report incompatible numbers while each query appears technically correct.

8. Data Quality, Governance, Security, and Privacy

Data quality is multidimensional. Common dimensions include accuracy, completeness, consistency, timeliness, validity, uniqueness, and fitness for purpose. Quality should be measured against explicit business or scientific requirements, not an abstract ideal.

Governance assigns decision rights and accountability. It includes policies for ownership, stewardship, classification, retention, access, lineage, quality, and acceptable use. Data governance is effective when embedded in workflows rather than treated solely as a committee activity.

Security controls include authentication, authorization, encryption in transit and at rest, key management, auditing, backup protection, vulnerability management, and incident response. Least privilege grants only the access necessary for a role. Separation of duties reduces the risk that one person can create, approve, and conceal a sensitive operation.

Privacy requires attention to purpose limitation, data minimization, transparency, lawful processing, retention, and individual rights where applicable under relevant law. Pseudonymization reduces direct identifiers but does not guarantee anonymity; linkage with auxiliary datasets may re-identify individuals. Differential privacy provides a formal approach to limiting the effect of one person's data on released statistics. A common definition uses a privacy parameter ε, where smaller ε generally gives stronger privacy but may reduce utility. Its guarantees depend on the mechanism, neighboring-dataset definition, composition, and implementation assumptions.

Ethical review should address consent, secondary use, surveillance, group harms, discrimination, representativeness, and the consequences of incorrect decisions. Compliance is necessary but not sufficient for responsible data practice.

9. Analytics: From Description to Decision

Analytics is often classified as:

1. Descriptive: What happened?
2. Diagnostic: Why did it happen?
3. Predictive: What is likely to happen?
4. Prescriptive: What action is recommended under stated objectives and constraints?

Descriptive analysis may use counts, rates, distributions, cross-tabulations, and dashboards. For observations x₁, x₂, …, xₙ, the arithmetic mean is:

Here n is the number of observations and x̄ is measured in the same units as xᵢ. The mean is sensitive to extreme values. The sample variance is:

The denominator n − 1 is used when estimating population variance from a sample under the usual independent-sampling framework. The sample standard deviation is s = √s².

Predictive modeling estimates an outcome from input variables. A simple linear model is:

where y is the response, x is a predictor, β₀ is the intercept, β₁ is the expected change in y per unit change in x under the model, and ε is an error term. The relationship is not automatically causal. Causal interpretation requires stronger assumptions, such as appropriate design or identification conditions.

For a binary classifier, a confusion matrix contains true positives (TP), true negatives (TN), false positives (FP), and false negatives (FN). Important metrics include:

Accuracy can be misleading with imbalanced classes. The choice of metric must reflect the costs of errors. Model evaluation should use a holdout test set or suitable cross-validation, avoid leakage, and compare with a meaningful baseline.

Worked analytical example

Suppose a screening model produces TP = 80, TN = 900, FP = 20, and FN = 100. Then the total is 1,100 cases. Accuracy is (80 + 900)/1,100 = 980/1,100 ≈ 0.891, or 89.1%. Precision is 80/(80 + 20) = 0.80, or 80%. Recall is 80/(80 + 100) ≈ 0.444, or 44.4%. The model appears accurate largely because negative cases dominate, but it misses many positive cases. If missing a positive case is costly, recall or a cost-sensitive measure may be more appropriate than accuracy.

10. Contemporary Data and AI Practices

Modern data platforms increasingly support streaming, real-time feature computation, vector search, and machine-learning operations. Streaming systems process events continuously, whereas batch systems process bounded collections. Event-time processing must account for late and out-of-order events; watermarks and windowing policies make the treatment of incomplete time intervals explicit.

AI systems depend on data provenance, labeling quality, feature definitions, monitoring, and feedback loops. A model can drift because the input distribution changes, the relationship between inputs and outcomes changes, or the data-generation process changes. Monitoring should therefore examine both technical performance and social outcomes, including subgroup error rates where appropriate.

Generative AI introduces additional database concerns: retrieval quality, access control, prompt and document leakage, citation provenance, hallucinated outputs, and evaluation of generated content. Retrieval-augmented systems should enforce document-level authorization before retrieval, not merely after generation. AI should augment accountable decision processes rather than obscure responsibility.

11. Illustrative Case: University Student-Success Analytics

Background

This fictional case concerns a university seeking to identify students who may need academic support while protecting privacy and avoiding punitive use of analytics.

Problem

Data is distributed across admissions, learning management, attendance, finance, and advising systems. Definitions differ: “withdrawn,” “active,” and “at risk” are interpreted inconsistently. Some data is missing systematically, and a previous dashboard displayed rankings without explaining uncertainty or limitations.

Analysis

The university first creates a conceptual model linking Student, Course, Enrolment, Assessment, AdvisingInteraction, and SupportReferral. A governed student identifier is used instead of exposing unnecessary personal identifiers. Data profiling measures completeness, duplicate rates, timeliness, and consistency. Historical labels are reviewed because past support decisions may reflect institutional bias rather than student need.

The analytical objective is changed from predicting failure for disciplinary action to identifying students who may benefit from voluntary support. The model is evaluated using time-based validation so that future cases are not used to predict the past. Recall, precision, calibration, and subgroup performance are reported. Advisors receive explanations, confidence ranges, and links to relevant records rather than an opaque score alone.

Approach and solution

A warehouse stores validated analytical data, while operational systems remain systems of record. A data catalog documents definitions and lineage. Role-based access restricts sensitive information. Retention periods are specified, and students are informed about the purpose and limits of the system. Human review is mandatory before outreach, and students can correct inaccurate records.

Outcome

The university reports improved consistency of definitions and earlier voluntary engagement with support services. It does not claim that the model caused improved grades without a suitable evaluation design. Model performance and unintended effects are reviewed each semester.

Learning points

The case demonstrates that analytics is a socio-technical system. Schema design, data quality, governance, validation, privacy, user experience, and institutional incentives are as important as the algorithm. Prediction should not be confused with explanation or causation, and a high-performing model can still be inappropriate if its use violates purpose, fairness, or autonomy.

12. Best Practices and Common Failure Modes

Effective database and analytics practice includes:

• Begin with domain concepts, decision requirements, and data ownership.
• Define keys, constraints, null semantics, units, and valid values explicitly.
• Normalize transactional schemas unless a documented performance reason supports denormalization.
• Measure query performance using realistic workloads and execution plans.
• Design for backup, restoration, failure, and observability—not only normal operation.
• Treat lineage, metadata, and quality rules as production assets.
• Separate development, testing, and production data and access privileges.
• Validate analytical models against baselines and operational costs.
• Check leakage, sampling bias, drift, subgroup performance, and calibration.
• Document assumptions, limitations, versioning, and intended use.
• Involve affected stakeholders in governance and impact assessment.

Common failures include collecting data without a purpose, treating a data lake as a governance strategy, confusing correlation with causation, optimizing only for average latency, assuming encryption solves all security problems, and deploying models without monitoring or an appeal mechanism.

Chapter Summary

Databases provide structured, reliable, and controlled management of persistent data. Data modeling begins with entities, attributes, relationships, keys, and constraints, and the relational model provides a powerful foundation for integrity and declarative querying. Normalization reduces redundancy and anomalies, while carefully justified denormalization may improve analytical performance. Transactions use ACID properties, concurrency control, logging, and recovery to preserve dependable operation.

Distributed and non-relational systems address scale, flexibility, and availability but introduce consistency and operational trade-offs. Warehouses, lakes, lakehouses, pipelines, catalogs, and semantic layers support integrated analytics. Data quality, governance, privacy, security, and ethics determine whether technical systems create trustworthy value. Analytics progresses from description to diagnosis, prediction, and prescription; valid interpretation requires appropriate metrics, validation, causal caution, and awareness of bias. Contemporary AI and streaming systems increase both capability and responsibility.

Key Terms

Database: Organized persistent collection of data managed for reliable access.

DBMS: Software that defines, stores, queries, secures, and recovers databases.

Schema: Formal structure and constraints of a database.

Metadata: Data describing structure, meaning, ownership, or lineage.

Relation: A table in the relational model.

Primary key: Selected candidate key that uniquely identifies tuples.

Foreign key: Attribute set referencing a key in another relation.

Referential integrity: Rule requiring valid references between related tables.

Normalization: Structured decomposition used to reduce redundancy and anomalies.

Functional dependency: Constraint in which one attribute set determines another.

Transaction: Logical unit of database work.

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

MVCC: Concurrency approach using multiple record versions.

OLTP: Online transaction processing for operational workloads.

OLAP: Online analytical processing for complex historical analysis.

Data warehouse: Governed analytical repository integrating historical data.

Data lineage: Record of data origins, transformations, and destinations.

Data governance: Policies, roles, standards, and controls for responsible data use.

Precision: Fraction of predicted positives that are actually positive.

Recall: Fraction of actual positives that are correctly identified.

Review Questions

Short Answer Questions

1. Distinguish a database schema from a database instance.
2. What is the purpose of a foreign key?
3. Explain the difference between normalization and denormalization.
4. State the four ACID properties.
5. Why can accuracy be misleading for an imbalanced classification problem?

Long Answer Questions

1. Explain the three-level database architecture and its relationship to data independence.
2. Describe functional dependencies and show how normalization can eliminate update anomalies.
3. Compare OLTP and OLAP systems in terms of workload, schema, and performance goals.
4. Explain concurrency anomalies and discuss two approaches to concurrency control.
5. Discuss how data governance, privacy, security, and ethics interact in an analytical system.

Analytical / Application Questions

1. Design a relational schema for a hospital appointment system. Identify keys, foreign keys, cardinalities, and at least five integrity constraints.
2. A fraud classifier has TP = 45, TN = 930, FP = 70, and FN = 55. Calculate accuracy, precision, and recall, and recommend a metric based on the assumption that missed fraud is more costly than investigating a legitimate transaction.
3. Propose an architecture for integrating streaming sensor data, an operational database, and a historical analytical warehouse. Justify choices concerning latency, consistency, storage, governance, and model monitoring.

Multiple Choice Questions with Answers

Question 1

Which property ensures that a committed transaction survives a system failure?

A. Atomicity

B. Consistency

C. Isolation

D. Durability

Correct Answer: D. Durability

Question 2

Which normal form removes partial dependency on part of a composite candidate key?

A. First Normal Form

B. Second Normal Form

C. Third Normal Form

D. Fourth Normal Form

Correct Answer: B. Second Normal Form

Question 3

In SQL, which clause filters groups after aggregation?

A. WHERE

B. FROM

C. HAVING

D. ORDER BY

Correct Answer: C. HAVING

Question 4

If TP = 80 and FP = 20, what is precision?

A. 44.4%

B. 80%

C. 89.1%

D. 100%

Correct Answer: B. 80%

Question 5

What does a foreign key primarily enforce?

A. Faster sorting

B. Referential integrity

C. Data compression

D. Transaction logging

Correct Answer: B. Referential integrity

Question 6

Which system is generally optimized for complex queries over historical data?

A. OLTP system

B. OLAP system

C. Cache-only system

D. Device driver

Correct Answer: B. OLAP system

Question 7

Which statement about pseudonymization is correct?

A. It guarantees complete anonymity

B. It removes the need for access control

C. It replaces direct identifiers but may still permit re-identification

D. It prevents all linkage attacks

Correct Answer: C. It replaces direct identifiers but may still permit re-identification

Question 8

What is a key advantage of a graph database?

A. Relationship traversal

B. Mandatory tabular normalization

C. Elimination of all replication costs

D. Guaranteed strong consistency in every deployment

Correct Answer: A. Relationship traversal

Question 9

A model has TP = 80 and FN = 100. What is its recall?

A. 44.4%

B. 55.6%

C. 80%

D. 89.1%

Correct Answer: A. 44.4%

Question 10

Which practice helps prevent data leakage during predictive-model evaluation?

A. Using future information in training

B. Evaluating only on training data

C. Separating validation or test data according to the prediction timeline

D. Removing all missing values after testing

Correct Answer: C. Separating validation or test data according to the prediction timeline