Relational Database Foundations

Introduction

Modern software systems depend on reliable data. A banking application records accounts and transactions; an online store manages products, customers, payments, and deliveries; a university system maintains students, courses, grades, and enrolments. Although these applications differ greatly, they share a fundamental requirement: data must be stored in a way that supports accuracy, efficient access, controlled sharing, and long-term change.

A relational database is one of the most influential solutions to this requirement. It represents data as related tables and uses a formal language—most commonly SQL—to define, manipulate, and query that data. MySQL and PostgreSQL are two widely used relational database management systems (RDBMSs). They are central technologies in web development, enterprise systems, data services, and cloud applications.

This chapter establishes the conceptual foundation required for later study of data modelling, SQL, normalization, transactions, indexing, security, and database-backed application development.

Learning Objectives

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

1. Define database, DBMS, RDBMS, table, row, column, key, schema, instance, and metadata.
2. Explain the principal components and layers of DBMS architecture.
3. Distinguish schema from database instance and explain data independence.
4. Describe the historical development and continuing importance of the relational model.
5. Compare the roles and characteristics of MySQL and PostgreSQL.
6. Identify common database use cases and evaluate basic design and governance concerns.
7. Explain how database concepts influence the work of a full-stack developer.

1. Data, Information, and Databases

Data consists of recorded representations of facts, observations, or events. A value such as 42, a date such as 2025-09-01, and a customer name are data items. Information is data interpreted in a meaningful context. For example, the statement “42 units of product P17 remain in warehouse W2” is information because relationships and context have been applied.

A database is an organized collection of related data managed so that it can be stored, retrieved, updated, and protected. The word “organized” is important. A random collection of files may contain data, but it may not provide consistent rules for relationships, validation, concurrency, or recovery.

A database management system (DBMS) is software that enables users and applications to define, create, manipulate, secure, and administer databases. A DBMS normally provides:

• Data definition: creating tables, constraints, views, and other structures.
• Data manipulation: inserting, updating, deleting, and retrieving records.
• Transaction management: grouping operations into reliable units.
• Concurrency control: coordinating simultaneous users and processes.
• Security: authenticating users and controlling privileges.
• Recovery: restoring consistency after failures.
• Storage management: organizing data on persistent storage.
• Metadata management: maintaining descriptions of database objects.

A relational database management system stores data primarily in relations, which are commonly presented as tables. The terms “relation” and “table” are closely related in practice, although relation has a precise theoretical meaning in the relational model.

Essential Terminology

A table represents a type of entity or an association between entities. A row, also called a tuple or record, represents one occurrence. A column, also called an attribute or field, represents a property. A domain is the set of valid values for an attribute—for example, a positive integer domain for a quantity.

A primary key uniquely identifies each row. A foreign key is an attribute, or group of attributes, whose values refer to a candidate or primary key in another table. A candidate key is any minimal attribute set capable of uniquely identifying a row. A table may have several candidate keys, but one is selected as the primary key.

A constraint is a rule that restricts valid database states. Common constraints include NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, and CHECK. Constraints express business and structural rules close to the data, reducing the risk that invalid information will be stored.

2. The Relational Model

The relational model was proposed by Edgar F. Codd in 1970. It replaced dependence on navigational structures with a model based on logical relationships and operations over sets of rows. Its central idea is that users should describe what data they want rather than specify every physical step required to obtain it.

Consider an online shop. A simplified design might include:

CUSTOMER(CustomerID, Name, Email)
ORDER(OrderID, CustomerID, OrderDate)
ORDER_ITEM(OrderID, ProductID, Quantity, UnitPrice)
PRODUCT(ProductID, ProductName, CurrentPrice)

CustomerID in ORDER refers to CUSTOMER.CustomerID; this is a foreign-key relationship. ORDER_ITEM resolves the many-to-many relationship between orders and products: one order contains many products, and a product can appear in many orders.

The relational model supports operations such as selection, projection, and join. Selection chooses rows satisfying a condition. Projection chooses columns. A join combines related rows using matching values. SQL expresses these operations in a practical language. For example:

SELECT c.Name, o.OrderDate
FROM Customer AS c
JOIN Orders AS o ON o.CustomerID = c.CustomerID
WHERE o.OrderDate >= '2025-01-01';

This query states the desired result without requiring the developer to specify whether the DBMS should scan a table, use an index, or employ another access strategy. The optimizer makes that implementation decision.

Integrity and Normalization

Relational design seeks to reduce redundancy and update anomalies. If a customer’s email address is repeated in every order row, changing the email requires multiple updates and creates opportunities for inconsistency. Separating customers from orders allows the email to be stored once.

Normalization is a family of design principles that organize attributes and relationships to reduce undesirable redundancy. First Normal Form requires atomic, appropriately structured values rather than repeating groups. Second and Third Normal Forms address dependencies among non-key attributes. Normalization is not a mechanical guarantee of good design; practical systems sometimes introduce controlled redundancy, called denormalization, to improve performance or simplify reporting. Such decisions should be deliberate and protected by tests and constraints.

3. DBMS Architecture

A DBMS can be understood at several levels. The ANSI/SPARC three-schema architecture separates user views from the logical database design and the physical storage implementation.

Figure: The three-schema architecture separates user views, logical design, and physical storage.

The external level contains views tailored to users or applications. A payroll view might expose employee identifiers and salaries to authorized personnel, while hiding unrelated information. The conceptual level describes the complete logical structure: entities, attributes, relationships, and constraints. The internal level describes how data is stored, including pages, indexes, files, buffers, and access paths.

A modern DBMS also contains important operational components:

• The query processor parses SQL, checks it against metadata, rewrites it when useful, creates an execution plan, and executes that plan.
• The storage manager handles files, pages, indexes, and buffers.
• The transaction manager coordinates concurrent operations.
• The recovery manager uses logs and other mechanisms to restore a consistent state after failure.
• The authorization subsystem applies identity and privilege rules.
• The catalog or data dictionary stores metadata about tables, columns, constraints, indexes, users, and permissions.

In a client-server deployment, an application connects to a database server through a driver or library. The server authenticates the client, processes requests, accesses stored data, and returns results. In cloud environments, the database may be managed as a service, but the logical responsibilities remain.

4. Schema, Instance, and Metadata

A database schema is the formal description of database structure. It includes tables, columns, data types, keys, constraints, views, routines, and sometimes permissions. A schema is comparable to a blueprint: it specifies what structures exist and how they relate.

A database instance is the actual collection of data stored at a particular moment. If a customer is added or an order is updated, the instance changes, while the schema may remain unchanged. This distinction is fundamental:

Metadata means “data about data.” A column’s name, type, maximum length, default value, and nullability are metadata. So are index definitions, foreign-key relationships, table ownership, and privilege assignments. Developers use metadata for administration, migration tools, documentation, code generation, and validation.

The term schema can also have a product-specific meaning. In PostgreSQL, a database can contain multiple named schemas that act as namespaces, such as sales.orders and support.tickets. In MySQL, the terms database and schema are commonly treated as near-synonyms. The general conceptual distinction between structure and current contents remains valid across systems.

5. Data Independence

Data independence is the ability to change one level of database description without requiring unacceptable changes at the next higher level. It supports maintainability and protects applications from unnecessary implementation details.

Physical data independence means that internal storage changes do not require changes to the conceptual schema or application programs. For example, an administrator may create an index, reorganize storage, or change a file layout while preserving the same tables and SQL interface.

Logical data independence means that changes to the conceptual schema do not require changes to every external view or application. This is harder to achieve. Adding a nullable column may be harmless, but splitting a table or changing the meaning of an attribute can require view, query, and application changes.

Data independence is not absolute. Applications can become coupled to column names, implicit ordering, undocumented behavior, or vendor-specific features. Good practices—stable interfaces, views, migrations, explicit column lists, and automated tests—help preserve independence.

6. Historical Development

Before relational systems became dominant, organizations commonly used file-processing systems and navigational database models. Applications often knew how records were physically organized and navigated pointers or hierarchical paths. These approaches could be efficient for known access patterns but were difficult to adapt and could create strong application-storage dependence.

Codd’s relational model introduced a mathematical foundation based on relations and predicate logic. System R at IBM and the Ingres project demonstrated that relational ideas could be implemented effectively. SQL evolved from these efforts and became the dominant language for relational data management. The SQL standard has developed through successive revisions, although vendors continue to provide extensions and different interpretations.

MySQL originated in the 1990s and became widely associated with web hosting and the LAMP stack—Linux, Apache, MySQL, and PHP. Its ease of deployment, broad hosting support, and familiar tooling contributed to extensive adoption.

PostgreSQL began as the POSTGRES project at the University of California, Berkeley, and developed into a feature-rich open-source object-relational database. PostgreSQL is known for strong standards support, extensibility, sophisticated data types, advanced indexing, and powerful transactional features.

Both systems are mature production platforms. Neither is universally superior. Selection depends on workload, operational expertise, required features, ecosystem, licensing, compatibility, and organizational goals.

7. MySQL and PostgreSQL in Modern Information Systems

MySQL is frequently used for content platforms, transactional web applications, e-commerce systems, and services whose requirements align with conventional relational storage. It is available in community and commercial distributions and is supported by many hosting providers, frameworks, and cloud platforms.

PostgreSQL is widely used for enterprise applications, analytical workloads, geographic information systems, complex transactional services, and systems requiring extensibility or advanced SQL features. Its ecosystem includes extensions such as PostGIS for spatial data.

The following comparison is intentionally high-level; exact behavior depends on version, configuration, storage engine, and workload.

Developers should avoid treating a database as merely a passive data store. Its constraints, transactions, indexes, query planner, permissions, backup strategy, and observability affect application correctness and performance.

8. Common Use Cases

Online Transaction Processing

OLTP systems support frequent, relatively small transactions such as creating an order or transferring funds. Correctness, concurrency, low latency, and transactional integrity are central.

Content and Customer Platforms

Websites and customer relationship systems store users, permissions, content, interactions, and audit events. Relational constraints are valuable when many parts of an application depend on shared entities.

Reporting and Decision Support

Relational databases can support operational reports and analytical queries. At larger scale, organizations may replicate or transform operational data into a data warehouse or lakehouse to separate reporting workloads from transactional workloads.

Geographic and Specialized Applications

PostgreSQL with suitable extensions can support spatial data. Both MySQL and PostgreSQL can store JSON, but a JSON column should not automatically replace a well-designed relational structure. The choice depends on query patterns, validation needs, and relationship complexity.

Microservices and APIs

A service may own a database and expose data through an API. This can reduce coupling between teams, but it also creates challenges involving distributed transactions, data duplication, synchronization, and reporting across services.

9. Transactions, Reliability, and Governance

A transaction is a logical unit of work. The ACID properties provide a useful reliability framework:

• Atomicity: all operations in a transaction succeed or none take effect.
• Consistency: committed transactions preserve declared rules and constraints.
• Isolation: concurrent transactions are controlled so that their interactions follow the system’s isolation semantics.
• Durability: committed results survive failures according to the system’s guarantees.

ACID does not mean that every database behaves identically under every concurrent workload. Isolation levels and locking or multiversion mechanisms affect phenomena such as dirty reads, non-repeatable reads, and phantom reads. Application developers must understand the guarantees provided by their chosen DBMS and configuration.

Database governance includes access control, encryption, backup testing, retention, auditing, data classification, and privacy protection. Personal data should be collected only for legitimate purposes, protected against unauthorized access, and retained according to applicable law and organizational policy. Principle-of-least-privilege permissions reduce the consequences of compromised credentials.

SQL injection is a major application security risk. It occurs when untrusted input is combined with SQL as executable text. Parameterized queries or prepared statements treat input as values rather than SQL syntax. Input validation remains useful, but it does not replace parameterization.

10. Illustrative Case: University Course Registration

Background

A university needs a system through which students can register for courses. The system must record students, courses, sections, instructors, and registrations. It must prevent duplicate registration and maintain accurate capacity information.

Problem

A file-based design stores a student’s name, course title, instructor, and registration details in one large record. When an instructor changes, many records must be updated. The same student may appear repeatedly, and two simultaneous requests may exceed the section capacity.

Analysis

The data contains several entity types and relationships:

• One student can have many registrations.
• One course can have many sections.
• One section can have many students.
• An instructor can teach multiple sections.
• A student should not register for the same section twice.

A relational design separates these concepts:

STUDENT(StudentID, Name, Email)
COURSE(CourseID, Code, Title, Credits)
INSTRUCTOR(InstructorID, Name)
SECTION(SectionID, CourseID, InstructorID, Term, Capacity)
REGISTRATION(StudentID, SectionID, RegisteredAt, Status)

REGISTRATION uses a composite primary key (StudentID, SectionID), preventing duplicate registrations. Foreign keys ensure that a registration refers to an existing student and section. A transaction can check capacity and insert a registration as one controlled unit. The exact concurrency strategy—such as row locking or a carefully designed conditional update—must be selected and tested for the DBMS and application workload.

Outcome

The normalized design reduces duplication, supports consistent updates, and provides a foundation for reporting. Indexes on REGISTRATION(SectionID) and REGISTRATION(StudentID) can support common searches. Access controls can allow students to view their own registrations while restricting administrative operations.

Learning Points

The case demonstrates that relational design is not only about creating tables. It requires identifying entities, expressing relationships, enforcing integrity, considering concurrency, and aligning physical structures with access patterns.

11. Contemporary Developments and Professional Practice

Relational databases remain important even as organizations adopt NoSQL systems, event streaming, distributed databases, and cloud-native architectures. Polyglot persistence means using different storage technologies for different workloads. It should not mean selecting technologies without understanding consistency, operational cost, and data ownership.

Managed database services reduce routine infrastructure work, but they do not eliminate responsibility for schema design, access control, backups, query performance, cost monitoring, and disaster recovery. Infrastructure-as-code and database migration tools help make schema changes repeatable and reviewable.

AI-assisted coding can generate SQL, explain query plans, or propose schemas. However, generated SQL must be reviewed for correctness, security, privacy, performance, and compatibility. AI tools may reproduce insecure patterns, expose sensitive data through prompts, or misunderstand business rules. Human developers remain responsible for database decisions.

Professional best practices include:

1. Model business entities and relationships before writing large amounts of SQL.
2. Use meaningful primary and foreign keys and enforce important rules with constraints.
3. Use parameterized queries and least-privilege database accounts.
4. Apply schema changes through version-controlled migrations.
5. Examine query plans and measure performance with representative data.
6. Test transactions, failure recovery, backups, and restore procedures.
7. Document ownership, sensitive fields, retention requirements, and dependencies.
8. Choose MySQL, PostgreSQL, or another system according to evidence rather than popularity alone.

Challenges and Limitations

Relational systems require careful modelling. Poorly designed schemas can produce excessive joins, redundancy, or difficult migrations. Strong consistency can involve coordination costs, especially across distributed services. Scaling reads through replicas introduces lag, and scaling writes may require partitioning or architectural changes.

SQL dialect differences can reduce portability. Features such as generated columns, JSON operators, procedural languages, and indexing methods may differ between MySQL and PostgreSQL. Portability is therefore a design goal requiring testing, not an automatic property of SQL.

A relational database also cannot solve every organizational data problem. Incorrect source data, unclear ownership, weak access procedures, and inadequate monitoring remain serious risks regardless of the chosen DBMS.

Chapter Summary

A database is an organized collection of related data, while a DBMS provides the software services required to define, manipulate, protect, and recover that data. Relational databases represent information through tables connected by keys and constrained by integrity rules. Their declarative query model allows applications to request results without prescribing physical access steps.

The three-schema architecture distinguishes external views, the conceptual logical schema, and internal storage structures. Schema describes database structure; an instance is the current data; metadata describes the structures and rules themselves. Physical and logical data independence reduce unnecessary coupling between applications and storage implementation.

The relational model emerged from Codd’s work and remains central to information systems because it combines formal foundations, expressive querying, integrity enforcement, and mature operational tooling. MySQL and PostgreSQL are important modern RDBMSs with overlapping capabilities and different strengths. Their selection should reflect workload, features, operations, ecosystem, security, and organizational requirements.

For full-stack developers, database competence includes more than writing SELECT statements. It involves modelling relationships, applying constraints, using transactions safely, preventing injection, managing migrations, evaluating performance, and protecting personal and business data.

Key Terms

Database: An organized collection of related data.

DBMS: Software for defining, storing, manipulating, securing, and administering databases.

RDBMS: A DBMS based primarily on the relational model.

Relation: A relational-model structure corresponding in practice to a table.

Table: A structured collection of rows and columns.

Row: A tuple or record representing one occurrence.

Column: An attribute representing a property of stored entities.

Domain: The set of valid values for an attribute.

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

Foreign key: An attribute set referring to a key in another table.

Constraint: A rule limiting valid database states.

Schema: The structural definition of a database.

Instance: The data contained in a database at a particular time.

Metadata: Data describing database structures, rules, and properties.

Data independence: The ability to change one database level with limited impact on higher levels.

Normalization: Principles for organizing data to reduce redundancy and anomalies.

Transaction: A logical unit of database work.

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

Query optimizer: A DBMS component that chooses an execution strategy for a query.

SQL injection: An attack in which untrusted input alters executable SQL.

Review Questions

Short Answer Questions

1. What is the difference between a database and a DBMS?
2. Distinguish a schema from a database instance.
3. What purpose does a foreign key serve?
4. Define metadata and give two examples.
5. What does physical data independence mean?

Long Answer Questions

1. Explain the three-schema architecture and describe how each level supports data independence.
2. Describe the relational model and explain how keys and constraints support integrity.
3. Compare MySQL and PostgreSQL using workload, extensibility, ecosystem, and operational considerations.
4. Explain the ACID properties and discuss why transaction isolation matters in concurrent applications.
5. Trace the historical development from file-processing systems to relational database systems and modern cloud deployments.

Analytical / Application Questions

1. Design a relational schema for a library containing members, books, authors, physical copies, and loans. Identify primary keys, foreign keys, and at least three integrity constraints.
2. An application stores customer details in every order row. Analyze two update anomalies that may result and propose a normalized redesign.
3. A registration system occasionally enrolls two students in the final available place of a course. Explain how transaction management and concurrency control could be used to address the problem, and identify tests needed to validate the solution.

Multiple Choice Questions with Answers

Question 1

Which component of a DBMS chooses an execution strategy for an SQL query?

A. Query optimizer

B. Data dictionary

C. Authorization subsystem

D. Backup archive

Correct Answer: A. Query optimizer

Question 2

What is the principal distinction between a database schema and a database instance?

A. A schema contains only indexes, while an instance contains only tables

B. A schema describes structure, while an instance is the current stored data

C. A schema is temporary, while an instance is permanent

D. A schema belongs to users, while an instance belongs to administrators

Correct Answer: B. A schema describes structure, while an instance is the current stored data

Question 3

Which key prevents two rows in a table from having the same identifier?

A. Foreign key

B. Composite attribute

C. Primary key

D. Default value

Correct Answer: C. Primary key

Question 4

Which example best illustrates physical data independence?

A. Changing an index without changing application queries

B. Changing a customer’s name without updating the database

C. Removing a required business rule

D. Allowing duplicate primary-key values

Correct Answer: A. Changing an index without changing application queries

Question 5

In the university registration case, why is REGISTRATION a separate table?

A. To eliminate all foreign keys

B. To represent the many-to-many relationship between students and sections

C. To store database passwords

D. To replace the course table

Correct Answer: B. To represent the many-to-many relationship between students and sections

Question 6

Which ACID property means that a transaction’s operations succeed as a unit or are all undone?

A. Consistency

B. Isolation

C. Durability

D. Atomicity

Correct Answer: D. Atomicity

Question 7

Which practice most directly prevents SQL injection?

A. Using longer table names

B. Sorting query results

C. Using parameterized queries

D. Adding more database replicas

Correct Answer: C. Using parameterized queries

Question 8

What is metadata?

A. A duplicate copy of every database row

B. Data describing database structures and properties

C. Only data stored in memory

D. A transaction that has not committed

Correct Answer: B. Data describing database structures and properties

Question 9

Which statement most accurately describes PostgreSQL?

A. It is only a file-based spreadsheet system

B. It is a feature-rich open-source object-relational DBMS

C. It cannot enforce foreign keys

D. It is exclusively a NoSQL document database

Correct Answer: B. It is a feature-rich open-source object-relational DBMS

Question 10

Why is denormalization sometimes introduced?

A. To deliberately add controlled redundancy for performance or reporting needs

B. To remove all relationships from a database

C. To prevent the use of indexes

D. To make every column contain multiple unrelated values

Correct Answer: A. To deliberately add controlled redundancy for performance or reporting needs