Data Management and Database Systems

Introduction

Information technology depends on the ability to collect, organize, protect, retrieve, and interpret data. A university, hospital, bank, retailer, or government agency may generate millions of records, but raw records have limited value unless they are accurate, meaningful, accessible, and used appropriately. Data management provides the policies, processes, technologies, and responsibilities required to manage data throughout its life cycle.

A database is an organized collection of related data managed so that users and applications can store, retrieve, update, and analyze information. Database systems combine databases with software, procedures, security controls, and administrative practices. The database management system (DBMS) is the software layer that manages access to data and provides services such as transaction processing, concurrency control, backup, recovery, and authorization.

This chapter develops the foundations of data modeling, relational database design, SQL, NoSQL systems, data quality, integration, warehousing, privacy, backup, and data governance.

Learning Objectives

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

1. Distinguish data, information, metadata, databases, and DBMSs.
2. Model entities, attributes, relationships, and business rules.
3. Explain primary keys, foreign keys, candidate keys, and referential integrity.
4. Apply normalization principles through Third Normal Form.
5. Write basic SQL queries for creating, retrieving, inserting, updating, and deleting data.
6. Compare relational and NoSQL database systems.
7. Evaluate data quality, integration, warehousing, privacy, backup, and governance practices.
8. Select appropriate database practices for organizational and professional situations.

1. Data, Information, and Database Systems

Data consists of recorded facts, observations, measurements, symbols, or events. A student number, course code, examination mark, and enrollment date are examples of data. Information is data interpreted in a context so that it supports understanding or decision-making. A report showing that a student’s average mark has increased across three semesters is information derived from several data items.

Metadata is data about data. It may describe the meaning, structure, source, format, owner, sensitivity, or permitted use of a data item. For example, metadata for a DateOfBirth field may specify that it is stored as a date, cannot be null, is classified as personal information, and is maintained by the student records office.

A DBMS normally provides:

• data definition and schema management;
• query processing;
• transaction management;
• concurrency control;
• authorization and access control;
• integrity constraint enforcement;
• backup and recovery;
• performance monitoring; and
• interfaces for applications and administrators.

A database schema describes the logical structure of a database: tables, columns, relationships, constraints, views, and other objects. The database instance is the actual data stored at a particular time. The schema changes relatively infrequently, whereas the instance changes as transactions occur.

The data life cycle

Data commonly passes through collection, validation, storage, use, sharing, archival, and disposal. Controls are needed at every stage. Collecting excessive data increases privacy and security risks; retaining data indefinitely increases cost and exposure; disposing of data without authorization may violate legal or operational requirements.

2. Data Modeling

Data modeling is the process of representing organizational data requirements in a structured form. A model helps analysts, developers, and users agree on what data exists, how it is related, and what rules apply.

Entities and attributes

An entity is a distinguishable object or concept about which data is stored. In a university system, examples include Student, Course, Lecturer, and Enrollment. An entity type describes a class of similar entities; an individual student is an entity instance.

An attribute is a property of an entity. Student may have StudentID, Name, Email, and DateOfBirth. Attributes should have clear meanings and appropriate data types. A value such as 2025-09-01 should be stored as a date rather than as unstructured text when date operations are required.

Attributes can be:

• simple, such as GenderCode or Salary;
• composite, such as an address divided into street, city, and postal code;
• single-valued, such as one date of birth; or
• multivalued, such as several telephone numbers.

Multivalued attributes are usually represented in a separate table in a relational design.

Relationships and cardinality

A relationship represents an association between entities. A Student enrolls in a Course; a Lecturer teaches a Course. Cardinality states how many instances may participate in a relationship:

• one-to-one (1:1): each instance of A relates to at most one instance of B;
• one-to-many (1:M): one A may relate to many B instances;
• many-to-many (M:N): many A instances may relate to many B instances.

A many-to-many relationship is implemented relationally through an associative entity. For example, Student and Course are linked by Enrollment. Enrollment may contain StudentID, CourseID, EnrollmentDate, and Grade.

Participation describes whether involvement is mandatory or optional. A course may require one lecturer, while a lecturer may teach zero, one, or many courses. These rules are business rules and must be confirmed with domain experts rather than guessed by a developer.

Conceptual, logical, and physical models

A conceptual model presents major entities and relationships without implementation details. A logical model defines attributes, keys, and constraints while remaining relatively independent of a particular DBMS. A physical model specifies tables, indexes, storage formats, partitions, and DBMS-specific details.

Separating these levels improves communication and allows a logical design to be implemented on different technologies. However, physical decisions can affect the logical design when requirements include very high transaction rates, geographic distribution, or specialized search.

3. Keys and Integrity

A key is an attribute or combination of attributes used to identify or relate records.

• A superkey uniquely identifies a row, possibly with unnecessary attributes.
• A candidate key is a minimal superkey.
• A primary key is the candidate key selected as the main identifier.
• An alternate key is a candidate key not selected as primary.
• A foreign key is an attribute or set of attributes referencing a key in another table.
• A composite key contains more than one attribute.

For Enrollment, the combination (StudentID, CourseID, Semester) may be a composite primary key if a student can take the same course in different semesters. A foreign key from Enrollment.StudentID to Student.StudentID prevents an enrollment from referring to a nonexistent student.

Important integrity rules include:

1. Entity integrity: a primary key must be unique and must not be null.
2. Referential integrity: a foreign-key value must match a referenced key, unless null is explicitly permitted.
3. Domain integrity: values must conform to type, range, format, and business rules.
4. Business integrity: organization-specific rules must be enforced, such as “a final mark must be between 0 and 100.”

Good key design favors stability, uniqueness, minimality, and limited exposure of sensitive information. A generated numeric identifier can be useful, but it does not remove the need for unique constraints on meaningful values such as an institutional email address.

4. Relational Design and Normalization

The relational model represents data as relations, commonly implemented as tables. A table contains rows, called tuples, and columns, called attributes. Each column should represent one well-defined domain, and each row should represent one occurrence of the table’s subject.

Poorly designed tables produce anomalies:

• insertion anomaly: a fact cannot be recorded without an unrelated fact;
• update anomaly: the same fact must be changed in multiple rows; and
• deletion anomaly: deleting one fact unintentionally removes another.

Normalization is a systematic method for reducing these problems by organizing attributes and dependencies.

First Normal Form

A relation is in First Normal Form (1NF) when each field contains an atomic value from a suitable domain and repeating groups are removed. A column called PhoneNumbers containing “555-0101, 555-0102” violates a strict atomic interpretation. A separate StudentPhone table is preferable.

Second Normal Form

A relation is in Second Normal Form (2NF) when it is in 1NF and every non-key attribute is fully functionally dependent on the whole primary key. This matters mainly for composite keys. In a table with key (StudentID, CourseID), StudentName depends only on StudentID, not on the whole key; it belongs in Student.

Third Normal Form

A relation is in Third Normal Form (3NF) when it is in 2NF and has no transitive dependency of a non-key attribute on the key through another non-key attribute. If Course contains (CourseID, DepartmentID, DepartmentName), DepartmentName depends on DepartmentID rather than directly on CourseID. DepartmentName should be moved to Department.

Normalization is not an absolute command to split every possible attribute. Highly normalized designs improve consistency but may require more joins. Controlled denormalization may improve read performance in reporting systems, provided that duplication is documented and maintained reliably.

Worked design example

Suppose an initial table is:

EnrollmentRecord(StudentID, StudentName, CourseID, CourseTitle, LecturerID, LecturerName, Semester, Grade)

Assume the key is (StudentID, CourseID, Semester). StudentName depends only on StudentID; CourseTitle depends only on CourseID; and LecturerName depends only on LecturerID. A normalized design is:

• Student(StudentID, StudentName)
• Course(CourseID, CourseTitle, LecturerID)
• Lecturer(LecturerID, LecturerName)
• Enrollment(StudentID, CourseID, Semester, Grade)

The foreign keys connect the tables. Updating a lecturer’s name now requires one change rather than changes in many enrollment records.

5. SQL Fundamentals

Structured Query Language (SQL) is widely used with relational DBMSs. SQL contains several categories:

• DDL: Data Definition Language, such as CREATE TABLE and ALTER TABLE;
• DML: Data Manipulation Language, such as INSERT, UPDATE, and DELETE;
• DQL: commonly used to describe retrieval with SELECT; and
• DCL/TCL: access control and transaction commands, such as GRANT, COMMIT, and ROLLBACK.

An illustrative schema is:

CREATE TABLE Student (
    StudentID INTEGER PRIMARY KEY,
    StudentName VARCHAR(100) NOT NULL,
    Email VARCHAR(150) UNIQUE NOT NULL
);

CREATE TABLE Enrollment (
    StudentID INTEGER NOT NULL,
    CourseID VARCHAR(20) NOT NULL,
    Semester VARCHAR(20) NOT NULL,
    Grade DECIMAL(5,2),
    PRIMARY KEY (StudentID, CourseID, Semester),
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID)
);

A query retrieving students with grades of at least 70 is:

SELECT StudentID, CourseID, Grade
FROM Enrollment
WHERE Grade >= 70;

A join combines related rows:

SELECT s.StudentName, e.CourseID, e.Grade
FROM Student AS s
JOIN Enrollment AS e
  ON s.StudentID = e.StudentID
WHERE e.Semester = '2025-S1';

Grouping supports summary analysis:

SELECT CourseID, COUNT(*) AS EnrolmentCount,
       AVG(Grade) AS AverageGrade
FROM Enrollment
GROUP BY CourseID;

WHERE filters rows before grouping; HAVING filters groups after aggregation. Null represents missing, unknown, or inapplicable information. It is not the same as zero or an empty string. Comparisons with null use IS NULL or IS NOT NULL, not = NULL.

Transactions and ACID

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

• Atomicity: all operations occur or none occur;
• Consistency: integrity rules remain satisfied;
• Isolation: concurrent transactions do not produce unacceptable interference; and
• Durability: committed changes survive an appropriate system failure.

For example, transferring money requires debiting one account and crediting another within one transaction. If the credit fails, the debit must be rolled back. Isolation levels balance consistency against concurrency and performance; the appropriate level depends on business requirements.

6. Relational and NoSQL Systems

Relational systems are appropriate when data is structured, relationships are important, and transactions require strong consistency. They provide schemas, constraints, joins, mature SQL support, and well-established administration practices.

NoSQL is a broad category rather than one single model. Common types include:

NoSQL systems may offer flexible schemas, horizontal scaling, and high availability for particular workloads. They can also make joins, cross-record transactions, ad hoc querying, and governance more difficult. “Schema-less” does not mean “without structure”; structure may simply be enforced by application code rather than centrally by the DBMS.

The choice should follow workload requirements rather than fashion. Questions include: Are transactions complex? Are relationships central? Is the data structure stable? What consistency is required? What scale, latency, availability, and geographic distribution are expected? Hybrid architectures frequently use relational systems for core transactions and NoSQL or analytical platforms for specialized workloads.

7. Data Quality and Integration

Data quality is multidimensional. Important dimensions include:

• accuracy: values correctly represent reality;
• completeness: required values are present;
• consistency: related records do not conflict;
• timeliness: data is available and current when needed;
• validity: values conform to rules and formats; and
• uniqueness: unwanted duplicates are absent.

A quality program begins with profiling: examining null rates, duplicates, distributions, invalid codes, and unusual values. Remediation may involve validation at entry, standardization, deduplication, reference data, stewardship, and monitoring. Quality should be measured against business use; a minor formatting difference may be harmless in one context but serious in another.

Data integration combines data from multiple systems. Extract-transform-load (ETL) extracts data, transforms it, and loads it into a target. Extract-load-transform (ELT) loads raw data first and transforms it within a scalable target platform. Integration challenges include different identifiers, formats, definitions, update frequencies, and levels of detail.

Master data management establishes trusted versions of shared entities such as Customer, Product, or Supplier. Data lineage records where data originated, how it changed, and where it is used. Both are essential for diagnosing errors and supporting audits.

8. Data Warehousing and Analytics

An operational database supports frequent transactions such as enrollment or payment. A data warehouse supports analysis across time and organizational areas. Warehouses often use a star schema consisting of a central fact table and surrounding dimension tables.

A sales fact table may contain numeric measures such as quantity and revenue, with dimensions for Date, Product, Store, and Customer. The grain of a fact table states what one row represents, for example “one product line on one sales invoice.” Defining grain before adding measures prevents double counting.

A data lake stores large volumes of raw or lightly processed data in diverse formats. A lakehouse attempts to combine flexible storage with warehouse-style management, governance, and analytical performance. These architectures can support machine learning, but they do not eliminate the need for definitions, quality controls, access management, and lifecycle policies.

9. Privacy, Security, Backup, and Recovery

Personal data should be collected for specified purposes, limited to what is necessary, kept accurate, retained only as long as justified, and protected against unauthorized access. Organizations should identify lawful purposes, provide transparency, manage consent where required, respect subject rights, and document processing activities according to applicable law and institutional policy. Exact legal duties vary by jurisdiction.

Security controls include:

• least-privilege access;
• role-based permissions;
• strong authentication and, where appropriate, multifactor authentication;
• encryption in transit and at rest;
• audit logging;
• secure development and parameterized SQL queries;
• vulnerability management; and
• separation of duties.

Parameterized queries are important because concatenating untrusted input into SQL can enable injection attacks.

A full backup copies the selected data. An incremental backup copies changes since the last backup of any type. A differential backup copies changes since the last full backup. Recovery planning should define:

• 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 requires a strategy capable of limiting potential loss to approximately 15 minutes of changes; it does not necessarily mean a backup runs exactly every 15 minutes. Backups should be encrypted, access-controlled, monitored, tested through restoration, and protected from ransomware. The 3-2-1 practice—three copies, on two types of media, with one copy off-site—is a useful baseline, not a substitute for risk analysis.

10. Data Governance

Data governance is the framework through which an organization directs and controls data. It assigns decision rights, responsibilities, standards, and escalation mechanisms. Typical roles include:

• data owner: accountable for a domain and its appropriate use;
• data steward: manages definitions, quality, and operational rules;
• custodian: operates technical storage and controls; and
• data user: accesses data for an authorized purpose.

A governance program may include a data catalog, classification scheme, retention schedule, quality rules, access reviews, lineage documentation, incident procedures, and standards for interoperability. Governance is not merely a compliance activity. Clear ownership improves decision-making, reduces duplication, and increases trust in reports.

Ethical issues include surveillance, discriminatory data, opaque automated decisions, re-identification, secondary use, and unequal access. Data may be legally collected yet ethically inappropriate to use. When AI systems use organizational data, teams should assess provenance, consent, bias, explainability, security, and whether sensitive attributes or proxies create unfair outcomes.

11. Illustrative Case: University Enrollment Data

Background

A fictional university operates separate systems for admissions, enrollment, learning management, finance, and library services. Each system stores student identifiers differently. Senior management receives conflicting counts of active students.

Problem

Students who change names or programs appear as duplicates. Some courses have different codes across systems. Reports are delayed because staff manually reconcile spreadsheets. Access to detailed records is broader than necessary, and backups have not been tested recently.

Analysis

The university lacks a shared master StudentID, common reference data, documented definitions, and clear ownership. It also has data-quality problems involving duplicates, inconsistent codes, incomplete status values, and unclear reporting grain. A report counting enrollments cannot be compared with a report counting distinct students unless the metric definitions are explicit.

Approach and solution

The university establishes a governance committee and assigns owners for Student, Course, and Enrollment data. It introduces a canonical StudentID, a course reference table, validation rules, and a data catalog. Operational systems remain specialized, but an integration layer standardizes identifiers and records lineage. A warehouse is designed with a fact table whose grain is one student-course-semester enrollment. Role-based access limits personally identifiable information, while analysts receive approved, minimized views.

The university also adopts parameterized queries, encryption, access reviews, retention rules, and a tested backup schedule. Data-quality dashboards monitor duplicate rates, missing identifiers, invalid course codes, and reconciliation differences.

Outcome

Reports become comparable because definitions and grain are documented. Duplicate records decline, reconciliation work is reduced, and access to sensitive data is narrowed. The university can trace a reported enrollment figure back to source systems and transformation rules.

Learning points

Technology alone does not solve data problems. Shared definitions, identifiers, stewardship, quality controls, security, and tested recovery processes are equally important. A warehouse is trustworthy only when its source data and transformation logic are governed.

12. Best Practices and Limitations

Effective database practice begins with requirements and business rules, not with selecting a fashionable product. Designers should model important concepts, choose stable keys, normalize transactional data, define constraints, index measured workloads, and test realistic queries. They should document assumptions, classify sensitive data, separate development and production environments, and monitor performance and quality.

Every design has trade-offs. Normalization can increase joins; denormalization can create inconsistency. Strong consistency can reduce availability or increase latency in distributed environments. Flexible NoSQL schemas can accelerate change but weaken centralized validation. Encryption protects stored information but does not correct excessive collection or poor authorization. Automated quality rules can reject legitimate exceptions if domain context is ignored.

Contemporary systems increasingly combine cloud databases, streaming pipelines, APIs, warehouses, lakehouses, and AI services. These technologies expand capability but also increase dependency, cost, attack surface, and governance complexity. Professionals must evaluate portability, service-level commitments, data location, vendor access, observability, and exit strategies.

Chapter Summary

Data management covers the full life cycle of data from collection to disposal. Database systems provide structured storage, query processing, integrity, transaction management, security, and recovery. Data modeling identifies entities, attributes, relationships, cardinalities, and business rules. Keys identify records and support referential integrity. Normalization reduces insertion, update, and deletion anomalies, while carefully controlled denormalization may improve performance.

Relational systems are strong for structured data, joins, constraints, and ACID transactions. NoSQL systems provide several models suited to flexible or distributed workloads, but their benefits and limitations depend on the application. SQL supports schema definition, data manipulation, joins, aggregation, and transactions. Data quality includes accuracy, completeness, consistency, timeliness, validity, and uniqueness. Integration, master data, lineage, warehouses, and lakehouses support organizational analysis.

Privacy and security require purpose limitation, minimization, access control, encryption, logging, and responsible use. Backup and recovery planning must address RPO, RTO, restoration testing, and resilience. Data governance assigns ownership and establishes definitions, standards, controls, and accountability. Reliable data is therefore both a technical and organizational achievement.

Key Terms

Data: Recorded facts, observations, or events.

Information: Data interpreted in a meaningful context.

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

Database: An organized collection of related data.

DBMS: Software that manages databases and provides storage, query, security, and recovery services.

Entity: A distinguishable object or concept about which data is stored.

Attribute: A property of an entity.

Cardinality: The number of entity instances that may participate in a relationship.

Primary key: The selected key that uniquely identifies rows in a table.

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

Referential integrity: The rule that foreign-key references must be valid.

Normalization: Organizing relations to reduce redundancy and anomalies.

SQL: A language used to define, manipulate, and query relational data.

Transaction: A logical unit of database work.

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

NoSQL: A family of non-relational database models.

Data warehouse: An analytical data store integrating data for reporting and analysis.

Data quality: The degree to which data is accurate, complete, consistent, timely, valid, and unique.

Data governance: The framework of authority, accountability, policies, and standards for managing data.

RPO/RTO: Recovery Point Objective and Recovery Time Objective for continuity planning.

Review Questions

Short Answer Questions

1. Distinguish data, information, and metadata.
2. What is the difference between a primary key and a foreign key?
3. Explain the purpose of normalization.
4. State the four ACID properties.
5. What do RPO and RTO measure?

Long Answer Questions

1. Explain how entities, attributes, relationships, and cardinality are used to construct a logical data model.
2. Describe 1NF, 2NF, and 3NF, using examples of dependencies and anomalies.
3. Compare relational, document, key-value, wide-column, and graph database systems.
4. Explain how data quality, metadata, lineage, and master data management support trustworthy analytics.
5. Discuss the technical, ethical, and governance controls required to protect personal data.

Analytical / Application Questions

1. Design a normalized relational schema for a clinic in which patients book appointments with doctors and receive prescriptions. Identify primary and foreign keys.
2. A retailer has slow reports because transactional tables are heavily queried during business hours. Recommend an integration and warehousing approach, including fact-table grain and security controls.
3. An organization wants to replace a relational enrollment system with a document database. Analyze the decision using transaction requirements, relationships, consistency, scalability, data quality, and governance.

Multiple Choice Questions with Answers

Question 1

Which feature ensures that a foreign-key value refers to an existing row in the referenced table?

A. Entity integrity

B. Referential integrity

C. Atomicity

D. Normalization

Correct Answer: B. Referential integrity

Question 2

In a normalized enrollment design, which table should normally store a student's name?

A. Enrollment

B. Course

C. Student

D. Semester

Correct Answer: C. Student

Question 3

Which SQL clause filters groups after an aggregate operation?

A. WHERE

B. ORDER BY

C. FROM

D. HAVING

Correct Answer: D. HAVING

Question 4

A relation with a composite key contains StudentID and CourseID, but StudentName depends only on StudentID. Which normalization issue is present?

A. A violation of 1NF

B. A partial dependency relevant to 2NF

C. A transitive dependency relevant only to 3NF

D. A referential-integrity violation

Correct Answer: B. A partial dependency relevant to 2NF

Question 5

Which database type represents data primarily as nodes and edges?

A. Graph database

B. Key-value database

C. Document database

D. Relational database

Correct Answer: A. Graph database

Question 6

Which ACID property means that a transaction’s committed changes survive an appropriate system failure?

A. Atomicity

B. Consistency

C. Isolation

D. Durability

Correct Answer: D. Durability

Question 7

Which data-quality dimension asks whether a value conforms to an allowed format or rule?

A. Validity

B. Timeliness

C. Uniqueness

D. Completeness

Correct Answer: A. Validity

Question 8

A recovery plan permits at most 30 minutes of lost updates. Which objective is being specified?

A. RTO of 30 minutes

B. RPO of 30 minutes

C. MTBF of 30 minutes

D. Availability of 30 minutes

Correct Answer: B. RPO of 30 minutes

Question 9

What is the main purpose of defining the grain of a warehouse fact table?

A. To determine the database password policy

B. To specify what one fact row represents and prevent incorrect aggregation

C. To select the programming language for ETL

D. To eliminate all dimension tables

Correct Answer: B. To specify what one fact row represents and prevent incorrect aggregation

Question 10

Which practice most directly reduces SQL injection risk?

A. Using larger tables

B. Removing all foreign keys

C. Using parameterized queries

D. Storing passwords in plain text

Correct Answer: C. Using parameterized queries