Database and Relational Foundations

Introduction

Modern software systems depend on reliable data. Banking applications record transactions, hospitals maintain patient histories, universities manage enrolment, and online retailers coordinate products, customers, payments, and deliveries. A database is not merely a collection of files; it is an organized, governed, and queryable representation of information. Database technology provides structures and rules that allow data to be stored accurately, retrieved efficiently, shared safely, and changed without unnecessary inconsistency.

This chapter introduces the conceptual foundations required for later study of SQL. It explains database terminology, database-management-system architecture, relational theory, tables, keys, relationships, schemas, relational algebra, SQL standards, and major database platforms. The emphasis is on understanding why relational databases are designed as they are, rather than memorizing isolated commands.

Learning Objectives

After completing this chapter, you should be able to:

• distinguish data, information, a database, and a DBMS;
• explain the principal components and levels of DBMS architecture;
• describe relations, tuples, attributes, domains, and relational schemas;
• identify primary, candidate, alternate, foreign, composite, and surrogate keys;
• analyze one-to-one, one-to-many, and many-to-many relationships;
• explain integrity constraints and normalization as foundations of reliable design;
• relate common relational-algebra operations to SQL queries;
• compare SQL standards with the behavior of major database platforms; and
• evaluate design, security, governance, and operational considerations in a practical scenario.

1. Data, Information, and Databases

Data consists of recorded facts, measurements, symbols, or observations. Examples include a student number, a product price, or a transaction date. Information is data interpreted in a context that makes it useful—for example, “the number of students enrolled in Database Systems increased by 12 percent.”

A database is an organized collection of related data managed according to defined structures and rules. A database may be small enough for a single application or distributed across many servers and regions. A database-management system (DBMS) is software that defines, creates, stores, protects, retrieves, and administers databases. SQL Server, PostgreSQL, Oracle Database, MySQL, and SQLite are examples of relational DBMS products.

A DBMS provides capabilities that ordinary files generally do not provide reliably:

• controlled concurrent access by multiple users;
• constraints that prevent invalid data;
• transaction processing and recovery after failure;
• authorization and auditing;
• indexes and query optimization;
• backup and restoration; and
• logical data independence, meaning that applications need not know every storage detail.

A database system includes the database, DBMS, application programs, users, procedures, and hardware or cloud infrastructure. This wider meaning is important because successful database work involves people, policies, and operations as well as SQL statements.

2. DBMS Architecture

A useful conceptual model separates a database system into three levels, often called the ANSI/SPARC three-schema architecture:

1. External level: user or application views. A payroll clerk may see employee identity and salary, while a manager sees aggregated departmental costs.
2. Conceptual level: the unified logical model of the entire database, including entities, relationships, attributes, and constraints.
3. Internal level: physical storage structures such as pages, indexes, compression, and file organization.

The separation supports two forms of data independence. Logical data independence is the ability to change the conceptual design without requiring changes to every external view. Physical data independence is the ability to change storage structures without changing the conceptual model or application meaning.

A modern DBMS commonly contains the following components:

A client application sends a SQL request to the DBMS. The parser checks syntax and identifies referenced objects. The optimizer considers alternative execution plans, such as whether to scan a table or use an index. The execution engine runs the selected plan, while the storage and transaction subsystems retrieve or modify data safely.

The traditional client-server model places applications on clients and the DBMS on a server. In a three-tier architecture, a presentation layer communicates with an application or service layer, which communicates with the database layer. This arrangement centralizes business rules and reduces direct exposure of the database to end users.

3. The Relational Model

The relational model, introduced by E. F. Codd, represents data using mathematical relations. In practical systems, a relation is usually represented as a table. The terminology is precise:

• A relation is a set of tuples satisfying a defined relational schema.
• A tuple is an ordered collection of attribute values; in a table it resembles a row.
• An attribute is a named property; in a table it resembles a column.
• A domain is the set of permitted values for an attribute, such as valid dates or nonnegative monetary amounts.
• A relational schema describes a relation’s name, attributes, data types, and constraints.
• A relation instance is the set of tuples stored at a particular time.

For example, a schema might be written informally as:

STUDENT(StudentID, GivenName, FamilyName, Email, ProgrammeCode)

The schema describes structure; the current rows form the instance. A table should represent one coherent subject or relationship, not an unrelated mixture of facts.

The pure relational model treats a relation as a set, so duplicate tuples are not meaningful and row order has no semantic significance. SQL tables can differ from this ideal: SQL permits duplicate rows unless a constraint prevents them, and query results have no guaranteed order unless ORDER BY is specified. These distinctions explain why relational theory and practical SQL are related but not identical.

Null Values

SQL uses NULL to represent an unknown, unavailable, or inapplicable value. It is not the same as zero, an empty string, or false. Comparisons involving NULL generally produce UNKNOWN, leading to SQL’s three-valued logic: TRUE, FALSE, and UNKNOWN. Consequently, a condition such as Email = NULL is not the correct way to find missing email values; Email IS NULL is used instead.

Nulls are sometimes necessary, but excessive use can make business rules unclear. Designers should decide whether a value is mandatory, unknown, not applicable, or better represented by a separate relation.

4. Tables, Keys, and Integrity

A key is an attribute or set of attributes used to identify rows or establish references.

• A superkey is any attribute set that uniquely identifies a row.
• A candidate key is a minimal superkey; removing any attribute would destroy uniqueness.
• A primary key is the candidate key selected as the principal identifier.
• An alternate key is a candidate key not selected as the primary key.
• A foreign key is an attribute or attribute set whose values refer to a candidate or primary key in another table.
• A composite key contains more than one attribute.
• A surrogate key is an artificial identifier, such as an integer or universally unique identifier, created for database use rather than derived from business meaning.

Consider:

CUSTOMER(CustomerID, Email, Name)

If both CustomerID and Email are unique, each may be a candidate key. If CustomerID is chosen as the primary key, Email may be enforced as an alternate key with a UNIQUE constraint.

Relational integrity has several dimensions:

• Entity integrity: a primary-key value must be unique and not null.
• Referential integrity: a foreign-key value must match a referenced key or be null when the relationship is optional.
• Domain integrity: values must conform to their data types, ranges, formats, and business rules.
• Business integrity: organization-specific rules must hold, such as “a course section cannot have more than its approved capacity.”

Constraints should be enforced as close to the data as practical. Application validation is useful, but database constraints provide a second line of defense against imports, scripts, concurrent applications, and programming errors.

5. Relationships and Data Modeling

A relationship expresses an association between entities. In conceptual modeling, an entity is a distinguishable object or occurrence, such as Customer, Product, or Order. An entity type becomes a table or set of tables in a relational design.

The principal cardinalities are:

• One-to-one (1:1): each row in one entity relates to at most one row in another. For example, a person and a unique passport record.
• One-to-many (1:M): one parent can relate to many children, while each child relates to one parent. A customer can place many orders.
• Many-to-many (M:N): many rows on each side can be related. Students can take many courses, and courses can contain many students.

A many-to-many relationship is implemented using an associative or junction table. For example:

ENROLLMENT(StudentID, CourseID, EnrolmentDate, Grade)

The pair (StudentID, CourseID) may form the composite primary key, while each component is also a foreign key. If a student may retake a course, the design may require an additional attempt number or enrolment identifier.

Optionality also matters. A customer may have zero orders, whereas every order may be required to belong to exactly one customer. Good design records both cardinality and optionality rather than merely drawing connecting lines.

6. Schemas and Normalization

A database schema is the formal structure of a database: tables, columns, data types, keys, constraints, views, and other objects. The term can also refer to a namespace used to group objects within a DBMS. A schema is distinct from the data currently stored in those objects.

Poorly designed tables often create three anomalies:

• Update anomaly: the same fact must be changed in several rows.
• Insertion anomaly: a fact cannot be recorded without an unrelated fact.
• Deletion anomaly: removing one fact unintentionally removes another.

Normalization is a disciplined approach to organizing relations to reduce redundancy and anomalies while preserving meaning. Important normal forms include:

• First normal form (1NF): each attribute contains atomic values appropriate to the model, rather than repeating groups or lists in one cell.
• Second normal form (2NF): the relation is in 1NF and every non-key attribute depends on the whole candidate key, relevant mainly when a key is composite.
• 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.

Normalization is not a mechanical guarantee of good design. A designer must understand functional dependencies, candidate keys, business rules, and transaction requirements. Controlled denormalization can improve performance in reporting or analytical systems, but it should be deliberate, documented, and supported by mechanisms that preserve correctness.

7. Relational Algebra and SQL

Relational algebra is a formal procedural language for manipulating relations. It provides a theoretical foundation for relational query processing and helps explain what SQL queries mean.

The principal operations are:

• Selection (σ): chooses rows satisfying a predicate.
• Projection (π): chooses specified columns.
• Union (∪): combines compatible relations and removes duplicate tuples in the formal model.
• Difference (−): returns tuples in one compatible relation but not the other.
• Cartesian product (×): pairs every tuple in one relation with every tuple in another.
• Join (⋈): combines related tuples, usually using matching values.
• Rename (ρ): changes relation or attribute names for clarity.

For example, to obtain the names of students in programme CS, the formal expression is:

π GivenName, FamilyName (σ ProgrammeCode = 'CS' (STUDENT))

A corresponding SQL expression is:

SELECT GivenName, FamilyName
FROM Student
WHERE ProgrammeCode = 'CS';

SQL is largely declarative: the programmer states the desired result, while the optimizer chooses an execution strategy. SQL also extends relational algebra with grouping, aggregation, outer joins, null semantics, data-definition commands, transactions, and authorization. A query’s logical processing order is conceptually FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, although the DBMS may physically execute operations in another order.

8. SQL Standards and Database Platforms

SQL is standardized principally by ISO and IEC, but vendors implement different versions and extensions. The standard defines broad language features, while products differ in syntax, data types, procedural languages, indexing, concurrency behavior, security, and administration.

Portability requires attention to reserved words, identity or sequence mechanisms, date and time functions, JSON features, procedural code, transaction isolation, and pagination syntax. Applications should use parameterized queries rather than concatenating user input. Database migration tools, automated tests, and a documented compatibility target reduce vendor lock-in and deployment errors.

9. Transactions, Concurrency, and Reliability

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

• Atomicity: all operations of a transaction succeed or none take effect.
• Consistency: constraints and declared rules remain satisfied after commit.
• Isolation: concurrent transactions do not expose inappropriate intermediate effects.
• Durability: committed changes survive permitted failures.

Isolation is not identical across all platforms or settings. Higher isolation can reduce anomalies but may increase locking, waiting, or resource consumption. Designers must understand the chosen DBMS’s isolation levels and workload rather than assuming that “ACID” gives one universal behavior.

Indexes can accelerate searches, joins, and ordering, but they consume storage and slow some inserts, updates, and deletes. Query plans should be inspected with platform-specific tools, and performance should be measured using realistic data volumes. Premature indexing or denormalization can create maintenance costs without solving the actual bottleneck.

10. Security, Governance, and Ethics

Database security includes authentication, authorization, encryption, auditing, backup protection, and secure disposal. The principle of least privilege gives users and services only the permissions required for their roles. Views can expose only necessary columns or rows, while stored procedures and row-level security may centralize controlled access.

Organizations should classify sensitive data, retain it only as long as justified, and maintain accurate records of access and change. Privacy regulations differ by jurisdiction, but common principles include purpose limitation, data minimization, transparency, accuracy, and protection against unauthorized disclosure. A technically correct schema can still support unethical surveillance or excessive collection. Database professionals therefore share responsibility for lawful, fair, and explainable data practices.

Backups must be tested through restoration exercises. Disaster recovery plans should define acceptable recovery time and recovery point objectives, although these are operational targets rather than universal formulas. Audit logs should be protected from unauthorized alteration, and production data should be masked or minimized in development environments.

11. Applied Case: University Enrolment Database

Background. A university initially stores enrolment information in a spreadsheet containing student details, programme details, course details, lecturer names, and grades. A student taking four courses appears in four rows.

Problem. The spreadsheet contains repeated names and programme descriptions. A change to a lecturer’s name must be repeated across many rows. A course cannot be recorded until a student enrols, and deleting the last enrolment can erase the only record of a course. These are update, insertion, and deletion anomalies.

Analysis. The principal entities are Student, Programme, Course, Lecturer, Section, and Enrolment. A programme has many students. A course may be offered in many sections, and a lecturer may teach many sections. Students and sections have a many-to-many relationship resolved by Enrolment.

Approach. A normalized design might include:

• PROGRAMME(ProgrammeCode, ProgrammeName);
• STUDENT(StudentID, GivenName, FamilyName, ProgrammeCode);
• COURSE(CourseCode, CourseTitle, Credits);
• LECTURER(LecturerID, LecturerName);
• SECTION(SectionID, CourseCode, LecturerID, Semester, Capacity); and
• ENROLMENT(StudentID, SectionID, EnrolmentDate, Grade).

Primary and foreign keys enforce identity and relationships. A unique constraint can prevent duplicate enrolment in the same section. A check constraint can restrict credits or grades to permitted values, while a transaction can enroll a student and update capacity as one logical operation.

Outcome. Course information can exist before enrolment, lecturer changes occur once, and student-course participation is represented without repeating unrelated facts. Reports can be generated through joins and aggregation. The university still needs policies for privacy, retention, access roles, backup, and correction of inaccurate records.

Learning points. Modeling begins with business rules, not with columns copied from an existing spreadsheet. Keys and constraints express those rules. Normalization reduces anomalies, while transaction design and governance make the system dependable in operation.

12. Contemporary Developments

Relational databases increasingly support JSON, geospatial data, full-text search, columnar storage, cloud-managed deployment, replication, and distributed architectures. New capabilities do not eliminate relational foundations; they make data modeling and governance more important. Polyglot architectures may combine relational databases with document, graph, cache, or analytical systems, but each additional technology introduces synchronization and operational complexity.

AI-assisted coding and natural-language query tools can help generate SQL, explain query plans, or identify anomalies. They can also produce insecure queries, misunderstand business definitions, expose confidential data, or invent nonexistent columns. Human review, least-privilege credentials, testing, parameterization, and auditability remain necessary. AI should assist database professionals, not replace responsibility for correctness and privacy.

Best-Practice Checklist

1. Model entities, relationships, keys, and business rules before writing extensive SQL.
2. Give every important table a clear primary key.
3. Enforce foreign keys, domains, uniqueness, and required values in the database.
4. Use junction tables for many-to-many relationships.
5. Normalize to reduce anomalies, then denormalize only for a measured reason.
6. Use explicit column lists and ORDER BY when result order matters.
7. Use parameterized SQL and least-privilege accounts.
8. Test transactions, backups, recovery, and migrations—not only individual queries.
9. Document platform-specific behavior and portability assumptions.
10. Treat data quality, privacy, and stewardship as design requirements.

Chapter Summary

A database system combines organized data, a DBMS, applications, users, procedures, and infrastructure. The DBMS provides storage, querying, transactions, recovery, security, and metadata management. The relational model represents information through relations, tuples, attributes, domains, keys, and constraints. Primary keys identify rows; foreign keys connect tables and support referential integrity. Cardinality and optionality describe relationships, while junction tables implement many-to-many associations.

Schemas describe database structure, and normalization reduces redundancy and insertion, update, and deletion anomalies. Relational algebra supplies a formal foundation for SQL operations such as selection, projection, union, difference, and join. SQL is standardized but implemented with important vendor differences. Reliable professional practice also requires transaction awareness, performance measurement, security, privacy, governance, backup testing, and careful evaluation of emerging tools such as AI-assisted SQL.

Key Terms

Database: An organized collection of related data.

DBMS: Software that defines, stores, queries, protects, and administers databases.

Schema: The formal structure of database objects, attributes, and constraints.

Relation: A set of tuples described by a relational schema.

Tuple: A row-like member of a relation.

Attribute: A named property represented by a column.

Domain: The permitted set of values for an attribute.

Primary key: The selected principal identifier for rows in a relation.

Candidate key: A minimal attribute set that uniquely identifies rows.

Foreign key: An attribute set referencing a key in the same or another relation.

Composite key: A key consisting of more than one attribute.

Surrogate key: An artificial identifier created for database use.

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

Normalization: The structured reduction of redundancy and data anomalies.

Transaction: A logical unit of database work.

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

Relational algebra: A formal system of operations on relations.

Selection: Relational algebra operation that filters tuples.

Projection: Relational algebra operation that chooses attributes.

Junction table: A table that represents a many-to-many relationship.

Review Questions

Short Answer Questions

1. What is the difference between a database and a DBMS?
2. Define a primary key and a foreign key.
3. What is meant by logical data independence?
4. Why are many-to-many relationships implemented using junction tables?
5. What does NULL represent in SQL, and why is it not equivalent to zero?

Long Answer Questions

1. Explain the three-schema architecture and discuss how it supports data independence.
2. Describe the relational model using the concepts of relation, tuple, attribute, domain, and schema.
3. Explain entity, referential, domain, and business integrity, giving an example of each.
4. Compare normalization through 1NF, 2NF, and 3NF, including the anomalies normalization seeks to reduce.
5. Compare SQL standards with vendor-specific features using at least three major relational platforms.

Analytical / Application Questions

1. Design tables and keys for a library in which members borrow many books and each book may be borrowed many times over its lifetime. Explain how your design prevents duplicate or impossible borrowing records.
2. A customer table contains customer details, product details, and order-line details in one wide table. Identify likely insertion, update, and deletion anomalies and propose a normalized design.
3. An application builds SQL by concatenating search text supplied by users. Analyze the security and reliability risks and propose database, application, and governance controls.

Multiple Choice Questions with Answers

Question 1

Which DBMS component typically chooses among alternative execution plans for a SQL statement?

A. Query optimizer

B. Backup archive

C. Authentication directory

D. Presentation layer

Correct Answer: A. Query optimizer

Question 2

Which constraint primarily enforces that a row identifier is unique and not null?

A. Foreign-key constraint

B. Primary-key constraint

C. Check constraint

D. Default constraint

Correct Answer: B. Primary-key constraint

Question 3

A many-to-many relationship between Student and Course is normally implemented with which structure?

A. A single nullable column in Student

B. A duplicate Student table

C. A junction table containing references to both entities

D. A view with no underlying tables

Correct Answer: C. A junction table containing references to both entities

Question 4

Which relational-algebra operation filters tuples according to a predicate?

A. Projection

B. Selection

C. Rename

D. Union

Correct Answer: B. Selection

Question 5

Which statement correctly describes a foreign key?

A. It must always contain an artificial integer

B. It identifies the DBMS storage page

C. It refers to a candidate or primary key in a relation

D. It automatically sorts every table

Correct Answer: C. It refers to a candidate or primary key in a relation

Question 6

Which SQL condition correctly tests whether Email has no value?

A. Email = NULL

B. Email <> NULL

C. Email IS NULL

D. Email == EMPTY

Correct Answer: C. Email IS NULL

Question 7

Which ACID property means that a committed change survives an allowed system failure?

A. Atomicity

B. Consistency

C. Isolation

D. Durability

Correct Answer: D. Durability

Question 8

A table violates which normal-form principle when a non-key attribute depends on only part of a composite key?

A. First normal form

B. Second normal form

C. Third normal form

D. No normal form because composite keys are invalid

Correct Answer: B. Second normal form

Question 9

Which practice most directly reduces SQL injection risk?

A. Concatenating user input into SQL strings

B. Using parameterized queries

C. Removing all foreign keys

D. Granting every user administrator privileges

Correct Answer: B. Using parameterized queries

Question 10

Which statement about SQL result order is correct?

A. Rows are always returned in primary-key order

B. Rows are always returned in insertion order

C. Rows have guaranteed order only when an ORDER BY clause specifies it

D. The DBMS standardizes one order for all platforms

Correct Answer: C. Rows have guaranteed order only when an ORDER BY clause specifies it