Relational Model and Integrity

Introduction

The relational model is the dominant conceptual foundation of modern database management. It represents data as relations, commonly implemented as tables, and provides precise rules for describing data, connecting related facts, and protecting correctness. For full-stack developers, understanding the relational model is essential because application code, SQL queries, APIs, reports, and transaction processing all depend on the quality of the underlying database design.

A database should not merely store values. It should ensure that values are meaningful, that each stored record can be identified, and that relationships between records remain valid. These responsibilities are addressed by relational concepts and integrity constraints.

This chapter develops the relational model from its basic vocabulary to practical SQL implementation. It explains relations, tuples, attributes, domains, keys, relational operations, entity integrity, referential integrity, and domain constraints.

Learning Objectives

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

• describe a relation using tuples, attributes, and domains;
• distinguish schemas from relation instances;
• identify superkeys, candidate keys, primary keys, and foreign keys;
• apply selection, projection, union, difference, Cartesian product, and join operations;
• explain entity integrity, referential integrity, and domain constraints;
• design basic relational tables with suitable constraints;
• analyse common integrity failures and propose corrective measures.

1. The Relational Model

The relational model was proposed by E. F. Codd and is based on mathematical relations. In database practice, a relation is usually displayed as a table. A table contains rows and columns, but the relational model gives these familiar structures precise meanings.

A relation schema describes the structure of a relation. For example:

STUDENT(StudentID, StudentName, Programme, Email)

The relation schema specifies the relation name and its attributes. A relation instance is the set of rows stored at a particular time. The schema normally remains stable while the instance changes as records are inserted, updated, or deleted.

A relation is formally a set of tuples. Because a mathematical set has no duplicate members, duplicate tuples are not part of the pure relational model, although some SQL query results may contain duplicates unless DISTINCT is used.

1.1 Tuples and Attributes

A tuple is a single record in a relation. In the STUDENT relation, one tuple might be:

An attribute is a named property or column of a relation. StudentID and Email are attributes. The order of attributes is significant in a written schema, but the conceptual meaning comes from their names and domains.

A relation of degree n contains n attributes. Thus, STUDENT has degree 4. Its cardinality is the number of tuples currently stored. If it contains 120 students, its cardinality is 120. Degree describes structure; cardinality describes population.

1.2 Domains

A domain is the set of valid values that an attribute may contain. Domains provide semantic meaning as well as technical data types. For example:

A database type such as INTEGER or VARCHAR is not always enough. An integer column may technically accept negative values even when the business meaning requires a positive quantity. A domain constraint expresses the additional rule.

1.3 Relation Schema and Instance

A relation schema is the definition of a relation. A relation instance is the current content of that relation. For example:

COURSE(CourseCode, CourseTitle, Credits)

is a schema, whereas the rows CS101, Database Systems, 15 and WEB110, Web Development, 15 form part of an instance.

The database schema is the collection of relation schemas, constraints, views, indexes, and other definitions. Database designers should distinguish structural rules from temporary data. A rule such as “CourseCode is unique” belongs to the schema; the current list of course codes belongs to the instance.

2. Keys and Identification

Keys allow records to be identified and relationships to be established.

2.1 Superkeys

A superkey is one or more attributes whose values uniquely identify each tuple in a relation. In STUDENT, StudentID may be a superkey. The combination of StudentID and StudentName is also a superkey, because it includes an attribute that already ensures uniqueness. However, it contains unnecessary information.

2.2 Candidate Keys

A candidate key is a minimal superkey. “Minimal” means that no attribute can be removed while preserving uniqueness. A relation may have several candidate keys. For example, StudentID and, if guaranteed unique, institutional Email may both be candidate keys.

Candidate keys should be:

• unique for every tuple;
• minimal;
• stable enough for identification;
• composed of values that are available when a record is created.

2.3 Primary Keys

The primary key is the candidate key selected as the principal identifier. It should be unique and NOT NULL. A primary key may be simple, such as StudentID, or composite, such as the pair (StudentID, CourseCode) in an ENROLMENT relation.

A surrogate key is an artificial identifier, such as an auto-generated integer. A natural key is derived from real-world data, such as a national registration number. Surrogate keys simplify relationships and avoid changes when business values change, but they do not remove the need to enforce uniqueness on important natural identifiers.

2.4 Foreign Keys

A foreign key is an attribute or group of attributes in one relation that refers to a candidate key, normally the primary key, in another relation. It represents a relationship between records.

Consider:

STUDENT(StudentID, StudentName)

COURSE(CourseCode, CourseTitle)

ENROLMENT(StudentID, CourseCode, EnrolmentDate, Grade)

ENROLMENT.StudentID refers to STUDENT.StudentID, and ENROLMENT.CourseCode refers to COURSE.CourseCode. The pair (StudentID, CourseCode) can be the primary key of ENROLMENT, preventing a student from being enrolled in the same course more than once.

A foreign key need not have the same name as the referenced key, but compatible data type and meaning are essential. A foreign key may be composite, in which case the number, order, and compatible domains of its attributes must correspond to the referenced key.

3. Relational Operations

Relational algebra provides operations for retrieving and combining data. SQL is a practical language influenced by relational algebra, although SQL also includes features such as duplicate-preserving behaviour and NULL.

3.1 Selection

Selection chooses rows satisfying a condition. It is written conceptually as:

For example, selecting students in the Full Stack Programming programme returns only tuples whose Programme equals that value. In SQL:

SELECT *
FROM Student
WHERE Programme = 'Full Stack Programming';

Selection changes the number of tuples but normally preserves the attributes.

3.2 Projection

Projection chooses specified attributes:

For example:

SELECT StudentID, StudentName
FROM Student;

Projection reduces the columns. Under the pure relational model, duplicate resulting tuples are removed. SQL retains duplicates unless DISTINCT is specified.

3.3 Union

Union combines tuples from two union-compatible relations. The relations must have the same number of attributes, and corresponding attributes must have compatible domains.

SELECT StudentID FROM CurrentStudent
UNION
SELECT StudentID FROM GraduatedStudent;

UNION removes duplicates. UNION ALL preserves them and therefore does not correspond exactly to set-based union.

3.4 Difference

Difference returns tuples in one relation that do not occur in another. The relations must be union-compatible.

SELECT StudentID FROM Student
EXCEPT
SELECT StudentID FROM Enrolment;

This may identify students with no enrolment, although the exact SQL operator varies across database systems; some use MINUS instead of EXCEPT.

3.5 Cartesian Product

The Cartesian product pairs every tuple of one relation with every tuple of another:

If R has m tuples and S has n tuples, the product has m × n tuple combinations before filtering, assuming set semantics. Cartesian products can become very large and are usually useful as an intermediate step for a join rather than as a final result.

3.6 Join

A join combines related tuples according to a condition. An equijoin commonly matches equal key values:

SELECT s.StudentName, c.CourseTitle
FROM Student AS s
JOIN Enrolment AS e ON e.StudentID = s.StudentID
JOIN Course AS c ON c.CourseCode = e.CourseCode;

An inner join returns only matching records. A left outer join returns every tuple from the left relation and matching tuples from the right; unmatched right-side attributes become NULL. Outer joins are useful when the requirement is to include entities with no related records.

3.7 Renaming and Division

Renaming gives a relation or attribute a temporary name, which is important when a relation participates in an operation more than once. SQL table aliases perform this role.

Division expresses queries involving “for every,” such as finding students who completed every required course. Such queries are often written in SQL using grouping, NOT EXISTS, or nested queries. Division is conceptually important even though it is less commonly written as a direct SQL operator.

4. Integrity Constraints

Integrity means that stored data remains accurate, valid, and logically consistent. Constraints are rules enforced by the database system rather than left entirely to application code. Application validation remains useful, but database enforcement protects data from all clients, including scripts, administrative tools, and competing applications.

4.1 Domain Integrity

Domain integrity requires each attribute value to belong to its permitted domain. Typical controls include:

• data types, such as INTEGER, DATE, and VARCHAR;
• NOT NULL, when a value is required;
• CHECK constraints for permitted ranges or patterns;
• DEFAULT values where an appropriate default exists;
• reference tables for controlled vocabularies.

For example:

CREATE TABLE Course (
    CourseCode  VARCHAR(10) PRIMARY KEY,
    CourseTitle VARCHAR(100) NOT NULL,
    Credits     INTEGER NOT NULL CHECK (Credits > 0)
);

The CHECK condition prevents a non-positive credit value. It does not, by itself, prove that every course has a valid institutional credit value; a more restrictive condition or reference table may be required.

NULL requires special care. NULL generally represents missing, unknown, or inapplicable information; it is not the same as zero, an empty string, or false. Comparisons involving NULL use three-valued logic: TRUE, FALSE, or UNKNOWN. For example, Grade = 'A' is not TRUE when Grade is NULL. Use IS NULL or IS NOT NULL to test for NULL.

4.2 Entity Integrity

Entity integrity states that a primary-key value must be unique and must not be NULL. Without this rule, a row could not be reliably identified. In SQL, PRIMARY KEY normally enforces both uniqueness and non-nullability.

A composite primary key requires every component to participate in identification. For ENROLMENT, the pair (StudentID, CourseCode) must be unique, and neither component may be NULL.

4.3 Referential Integrity

Referential integrity requires every non-NULL foreign-key value to match an existing referenced key, unless the database design explicitly permits another mechanism. Thus, an ENROLMENT row cannot refer to a StudentID that does not exist in STUDENT.

Example:

CREATE TABLE Enrolment (
    StudentID     VARCHAR(10) NOT NULL,
    CourseCode    VARCHAR(10) NOT NULL,
    EnrolmentDate DATE NOT NULL,
    Grade         CHAR(2),
    PRIMARY KEY (StudentID, CourseCode),
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
    FOREIGN KEY (CourseCode) REFERENCES Course(CourseCode),
    CHECK (Grade IS NULL OR Grade IN ('A', 'B', 'C', 'D', 'F'))
);

The actions taken when a referenced row is updated or deleted must be chosen deliberately:

CASCADE is not automatically safer. Deleting a student and all enrolments may be correct in a temporary training system but unacceptable where academic history must be retained.

5. Worked Design Example

Suppose a training centre must record learners, modules, and registrations. A learner may register for many modules, and a module may have many learners. This is a many-to-many relationship, so it requires an associative relation.

The design is:

• LEARNER(LearnerID, Name, Email)
• MODULE(ModuleCode, Title, Hours)
• REGISTRATION(LearnerID, ModuleCode, RegistrationDate, Result)

Candidate keys are LearnerID and possibly Email for LEARNER, and ModuleCode for MODULE. Choose LearnerID and ModuleCode as primary keys. REGISTRATION uses the composite primary key (LearnerID, ModuleCode), while both components are foreign keys.

A possible implementation is:

CREATE TABLE Learner (
    LearnerID INTEGER PRIMARY KEY,
    Name      VARCHAR(80) NOT NULL,
    Email     VARCHAR(120) NOT NULL UNIQUE
);

CREATE TABLE Module (
    ModuleCode VARCHAR(12) PRIMARY KEY,
    Title      VARCHAR(100) NOT NULL,
    Hours      INTEGER NOT NULL CHECK (Hours BETWEEN 1 AND 500)
);

CREATE TABLE Registration (
    LearnerID        INTEGER NOT NULL,
    ModuleCode       VARCHAR(12) NOT NULL,
    RegistrationDate DATE NOT NULL,
    Result           VARCHAR(10),
    PRIMARY KEY (LearnerID, ModuleCode),
    FOREIGN KEY (LearnerID) REFERENCES Learner(LearnerID),
    FOREIGN KEY (ModuleCode) REFERENCES Module(ModuleCode),
    CHECK (Result IS NULL OR Result IN ('Pass', 'Fail', 'Withdrawn'))
);

The design prevents duplicate registration, rejects unknown learners and modules, requires valid hours, and restricts Result to approved values. It also allows Result to remain NULL while assessment is incomplete. If the organisation requires a result only after completion, that rule may need an additional status attribute, a trigger, or controlled application workflow.

6. Applied Case: Course Registration System

Background

This illustrative case concerns a small college replacing spreadsheets with a relational database for course registration. The spreadsheet contains student details, course details, and registration rows in one wide file.

Problem

The same student name and email appear repeatedly. A course title is sometimes spelled differently, and registrations occasionally refer to course codes that no longer exist. Deleting the last registration for a course may also remove the only stored copy of course information.

Analysis

The problems arise from duplicated facts and absent integrity constraints. Student, course, and registration facts have different lifecycles and should be represented by separate relations. The many-to-many relationship between students and courses requires an associative table.

Approach and Solution

The college creates STUDENT, COURSE, and ENROLMENT relations. StudentID and CourseCode are primary keys. ENROLMENT has a composite primary key and two foreign keys. NOT NULL and CHECK constraints enforce required values and valid grades. A UNIQUE constraint protects institutional email addresses. Existing spreadsheet data is loaded only after duplicate detection, key validation, and correction of invalid references.

Registration deletion is restricted because academic history is part of the institutional record. Instead of deleting a student who has historical enrolments, the system records an active status or retention flag.

Outcome

New registrations cannot refer to nonexistent students or courses. A student’s contact information is maintained in one location, and reports can join relations consistently. Data-entry errors are detected at insertion rather than discovered later in reports.

Learning Points

The case demonstrates that integrity is a design responsibility. Splitting relations alone is insufficient; keys, foreign keys, domains, and operational policies must work together. Constraints should reflect organisational rules, especially rules concerning retention, deletion, and historical records.

7. Design and Operational Best Practices

1. Identify entities, attributes, and relationships before writing SQL.
2. Select primary keys that are unique, stable, and available.
3. Define domains using both database types and business constraints.
4. Enforce important rules in the database, not only in user-interface code.
5. Index foreign keys where workload and database design make this beneficial; indexing improves lookup performance but does not itself enforce referential integrity.
6. Use transactions when several related changes must succeed or fail together.
7. Test insert, update, and delete operations, including invalid cases.
8. Document the meaning of NULL and permitted values.
9. Treat deletion actions as policy decisions, not merely technical settings.
10. Protect sensitive personal data through access control, encryption where appropriate, auditing, and data-minimisation practices.

8. Challenges and Contemporary Considerations

Relational constraints provide strong guarantees, but they cannot resolve ambiguous business rules. For example, the database may enforce that a date is valid, but a business rule may require that it falls within an academic term. Such rules may require additional tables, triggers, procedures, or application workflows.

Distributed systems introduce further challenges. Multiple services may own different data, and immediate foreign-key enforcement across independent databases may not be practical. In such systems, identifiers, APIs, events, reconciliation jobs, and monitoring help maintain consistency, but they do not provide exactly the same guarantee as a local foreign key.

AI-assisted development can generate SQL and schema suggestions, but generated code must be reviewed. Developers should verify key choices, NULL behaviour, cascade actions, privacy implications, and database-specific syntax. AI tools should not be trusted to infer organisational policies from incomplete requirements.

Integrity also has a governance dimension. Student, health, financial, and identity data require lawful collection, restricted access, retention controls, accurate correction procedures, and auditability. A technically valid relation may still be inappropriate if it collects unnecessary personal data or exposes it to unauthorised users.

Chapter Summary

The relational model represents data as relations consisting of tuples and attributes drawn from defined domains. A relation schema describes structure, while a relation instance contains current tuples. Degree counts attributes and cardinality counts tuples.

Superkeys identify tuples, candidate keys are minimal superkeys, and a selected candidate key becomes the primary key. Foreign keys represent relationships and support referential integrity. Relational operations include selection, projection, union, difference, Cartesian product, joins, renaming, and division-related queries.

Domain constraints restrict values to meaningful types and permitted conditions. Entity integrity requires primary keys to be unique and non-NULL. Referential integrity requires foreign-key values to correspond to existing referenced keys, subject to deliberate update and deletion policies. Good relational design combines appropriate decomposition, keys, constraints, transactions, security, and documented business rules.

Key Terms

Relation: A set of tuples represented in practice as a table.

Relation schema: The name and attribute structure of a relation.

Relation instance: The tuples stored in a relation at a particular time.

Tuple: A single record in a relation.

Attribute: A named property or column of a relation.

Domain: The permitted set of values for an attribute.

Degree: The number of attributes in a relation.

Cardinality: The number of tuples in a relation.

Superkey: An attribute set that uniquely identifies tuples.

Candidate key: A minimal superkey.

Primary key: The candidate key selected as the principal identifier.

Surrogate key: An artificial identifier generated for database use.

Natural key: An identifier with meaning in the real-world domain.

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

Selection: An operation that filters tuples by a condition.

Projection: An operation that selects attributes.

Join: An operation that combines related tuples.

Entity integrity: The rule that primary keys are unique and non-NULL.

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

Domain constraint: A rule restricting permitted attribute values.

Review Questions

Short Answer Questions

1. What is the difference between a relation schema and a relation instance?
2. Distinguish degree from cardinality.
3. Why is a candidate key required to be minimal?
4. What does a foreign key represent?
5. Why is NULL different from zero and an empty string?

Long Answer Questions

1. Explain the relational model using the terms relation, tuple, attribute, domain, schema, and instance.
2. Compare superkeys, candidate keys, primary keys, natural keys, and surrogate keys.
3. Explain selection, projection, union, difference, Cartesian product, and joins, including one practical use of each.
4. Discuss entity integrity, referential integrity, and domain integrity, using SQL examples.
5. Explain how cascade, restrict, set-null, and set-default actions affect database design decisions.

Analytical / Application Questions

1. Design relations for a library in which members borrow many books and each book may be borrowed many times over its lifetime. Identify primary and foreign keys and justify your choices.
2. A registration table contains duplicate registrations, unknown course codes, and negative credit values. Diagnose which integrity rules are absent and propose SQL constraints to correct the design.
3. A college wants to delete student records when students leave. Analyse whether CASCADE is appropriate if the college must retain academic history, and recommend an alternative policy.

Multiple Choice Questions with Answers

Question 1

Which term describes the number of attributes in a relation?

A. Cardinality

B. Degree

C. Domain

D. Selectivity

Correct Answer: B. Degree

Question 2

What is a candidate key?

A. Any attribute that contains text

B. A foreign key with duplicate values

C. A minimal superkey

D. A key that may contain NULL values

Correct Answer: C. A minimal superkey

Question 3

Which constraint ensures that a primary-key value cannot be NULL?

A. Entity integrity

B. Referential integrity

C. Domain projection

D. Cartesian integrity

Correct Answer: A. Entity integrity

Question 4

Which relational operation filters tuples according to a condition?

A. Projection

B. Selection

C. Union

D. Renaming

Correct Answer: B. Selection

Question 5

What is the main purpose of a foreign key?

A. To sort a table alphabetically

B. To store encrypted values

C. To link a tuple to a key in another relation

D. To permit duplicate primary keys

Correct Answer: C. To link a tuple to a key in another relation

Question 6

Which SQL constraint would prevent a course from having zero credits?

A. CHECK (Credits > 0)

B. DEFAULT 0

C. FOREIGN KEY (Credits)

D. UNION (Credits)

Correct Answer: A. CHECK (Credits > 0)

Question 7

A relation has 8 tuples and another has 5 tuples. How many tuple combinations can their Cartesian product contain before filtering?

A. 13

B. 40

C. 64

D. 3

Correct Answer: B. 40

Question 8

Which operation returns attributes StudentID and StudentName but omits other columns?

A. Selection

B. Projection

C. Difference

D. Cartesian product

Correct Answer: B. Projection

Question 9

What does referential integrity normally prevent?

A. A foreign key from matching an existing parent key

B. A row from having any attributes

C. A foreign key from referring to a nonexistent referenced key

D. A table from having a primary key

Correct Answer: C. A foreign key from referring to a nonexistent referenced key

Question 10

Why is (StudentID, CourseCode) a suitable primary key for an enrolment relation?

A. It allows the same student-course registration repeatedly

B. It identifies each student-course registration and prevents its duplication

C. It removes the need for a Student relation

D. It permits unknown students and courses

Correct Answer: B. It identifies each student-course registration and prevents its duplication