Database Management Systems

Chapter 3
Relational Model and Formal Query Foundations

Bachelors Program in AI & ML
Database Management Systems • Bachelor

Relational Model and Formal Query Foundations

Introduction

The relational model represents data through relations governed by precisely defined schemas and integrity constraints. Its mathematical foundations allow database designers to reason about data independently of physical storage, indexes, or programming language. This separation is especially valuable in AI and machine learning systems, where incorrect joins, missing-value assumptions, or poorly enforced keys can silently corrupt training data and model evaluations.

Relational algebra provides an operational language: a query is expressed as a sequence of operations. Relational calculus provides a declarative language: a query specifies the properties that its result must satisfy. SQL draws from both traditions, although SQL also introduces duplicates, nulls, ordering, and other features absent from the classical model.

Learning Objectives

After completing this chapter, students should be able to:

represent data using relations, attributes, domains, and tuples;
distinguish superkeys, candidate keys, primary keys, and foreign keys;
apply domain, key, entity-integrity, and referential-integrity constraints;
reason correctly about null values and SQL’s three-valued logic;
formulate queries using relational algebra;
express queries in tuple and domain relational calculus;
explain the equivalence of relational algebra and safe relational calculus;
derive and interpret relational division, including the empty-divisor case;
translate formal queries into SQL while recognizing semantic differences;
apply formal reasoning to data-quality and AI data-pipeline problems.

1. Foundations of the Relational Model

1.1 Domains, attributes, and relation schemas

A domain is a set of permitted atomic values. Examples include integers from 0 to 100, valid course identifiers, or ISO-formatted dates. A domain may also impose a semantic interpretation: two attributes can both contain integers while representing incompatible concepts, such as age and course credits.

A relation schema has the form

R(A_1:D_1,A_2:D_2,\ldots,A_n:D_n),

where \(R\) is the relation name, each \(A_i\) is an attribute, and \(D_i\) is the attribute’s domain. The number of attributes is the relation’s degree or arity.

Consider:

`STUDENT(SID, Name, Major)`
`COURSE(CID, Title)`
`TAKES(SID, CID, Grade)`

A relation instance is the current finite set of tuples conforming to its schema. Its number of tuples is its cardinality.

1.2 Tuples as mappings

A tuple should be understood as a mapping from attribute names to values, not merely as an ordered list. For example,

t=\{SID\mapsto S1,\ Name\mapsto Ada,\ Major\mapsto CS\}.

The expression \(t[SID]\) denotes the value associated with `SID`. Because attributes are identified by name, their displayed order has no logical significance. Implementations may impose an order for storage or presentation, but that order is not part of classical relational meaning.

1.3 Properties of a classical relation

A classical relation has the following properties:

1. It is a set, so duplicate tuples do not occur.

2. Tuple order has no logical significance.

3. Attribute order has no logical significance.

4. Each tuple has one value for every attribute.

5. Values are atomic with respect to the model.

6. Every value belongs to the declared domain of its attribute.

SQL tables approximate relations but are not identical to them. SQL commonly permits duplicate rows, null markers, ordered output, and complex data types.

2. Keys and Integrity Constraints

2.1 Superkeys and candidate keys

Let \(R\) be a relation schema and \(K\) a subset of its attributes. \(K\) is a superkey if no two distinct tuples in any legal instance of \(R\) can agree on every attribute in \(K\):

\forall t_1,t_2\in R,\quad t_1[K]=t_2[K]\Rightarrow t_1=t_2.

A candidate key is a minimal superkey: removing any attribute from it destroys the uniqueness guarantee. Minimality concerns attributes, not the number of characters or storage bytes.

One candidate key may be designated as the primary key. Remaining candidate keys are called alternate keys. Any superset of a candidate key is a superkey, but it is not itself a candidate key unless it remains minimal.

For `STUDENT`, `SID` might be the primary key, while an institutionally guaranteed unique email address could be an alternate key. In `TAKES`, the pair `(SID, CID)` may be a composite candidate key if a student can take each course only once.

Keys are schema-level constraints over all legal database states. A column that happens to be unique in today’s data is not necessarily a candidate key.

2.2 Foreign keys and referential integrity

A foreign key \(F\) in a referencing relation \(R\) refers to a candidate key \(K\) of a referenced relation \(S\). When all components of \(F\) are present, their combined value must equal one existing value of \(K\).

For example:

`TAKES.SID` references `STUDENT.SID`;
`TAKES.CID` references `COURSE.CID`.

Composite foreign keys must be interpreted as whole values. If `SECTION(DeptCode, CourseNo, SectionNo)` has key `(DeptCode, CourseNo, SectionNo)`, a referencing tuple must match one row on all three components—not obtain each component from a different row.

Null handling for composite foreign keys must be explicitly specified. Common SQL matching rules include:

MATCH SIMPLE: if any foreign-key component is null, the referential check is not required; otherwise all components must match one referenced key.
MATCH FULL: either every component is null, or every component is non-null and the complete value matches; a partially null composite value is invalid.

Unless stated otherwise, this chapter assumes `MATCH SIMPLE`, reflecting the common SQL default. For identifiers whose components are meaningful only together, `MATCH FULL` may better preserve integrity.

2.3 Major integrity constraints

Domain integrity requires every stored value to belong to the attribute’s permitted domain. SQL mechanisms include data types, `CHECK` constraints, and reference tables.

Key constraints require candidate-key values to be unique.

Entity integrity requires every primary-key component to be non-null. Otherwise the database cannot unambiguously identify the entity represented by a row.

Referential integrity requires each applicable non-null foreign-key value to match an existing referenced key.

Semantic constraints express application-specific rules, such as `Grade BETWEEN 0 AND 100` or “a completion date cannot precede an enrolment date.” Some cross-row or cross-table constraints require assertions, triggers, controlled transactions, or application services because SQL implementations provide limited support for general assertions.

2.4 Update actions

When a referenced row is updated or deleted, a database may reject the operation or apply an action such as:

`RESTRICT` or `NO ACTION`;
`CASCADE`;
`SET NULL`;
`SET DEFAULT`.

Cascades should be used only when the dependent rows share the referenced entity’s lifecycle. An inappropriate cascade can transform a small administrative error into large-scale data loss.

3. Null Values and Three-Valued Logic

3.1 Meaning of null

SQL `NULL` is a marker indicating that a value is absent. It may mean unknown, unavailable, withheld, or not applicable. These meanings are conceptually different, so a schema should not use one nullable attribute when the distinctions matter operationally or ethically.

Null is not zero, an empty string, or a conventional value. Consequently, comparisons such as `Grade = NULL` do not test for null. SQL uses `Grade IS NULL` and `Grade IS NOT NULL`.

3.2 Truth values

SQL predicates may evaluate to `TRUE`, `FALSE`, or `UNKNOWN`. For an ordinary comparison involving null, such as `NULL = 5`, the result is `UNKNOWN`.

Important rules include:

| Expression | Result |

|---|---|

| `TRUE AND UNKNOWN` | `UNKNOWN` |

| `FALSE AND UNKNOWN` | `FALSE` |

| `TRUE OR UNKNOWN` | `TRUE` |

| `FALSE OR UNKNOWN` | `UNKNOWN` |

| `NOT UNKNOWN` | `UNKNOWN` |

A `WHERE` clause retains only rows for which its condition is `TRUE`; both `FALSE` and `UNKNOWN` are rejected. Thus `WHERE Grade >= 50` excludes rows whose grade is null, but this does not prove that those students failed.

3.3 Practical null hazards

The predicate `x NOT IN (subquery)` can become `UNKNOWN` if the subquery returns a null. `NOT EXISTS` is usually safer when expressing absence.

Aggregate functions such as `AVG`, `SUM`, and `COUNT(column)` generally ignore nulls, whereas `COUNT(*)` counts rows. Analysts must therefore report the denominator and missingness policy. A model trained only on non-null observations may acquire selection bias if missingness correlates with protected or socioeconomic characteristics.

Null behavior in `UNIQUE` constraints varies across products. Portable designs should not assume that all database systems treat multiple nulls identically. Where supported, `IS DISTINCT FROM` provides a two-valued, null-aware comparison.

The classical relational model does not require nulls. Formal algebraic reasoning is normally performed over ordinary values unless a particular null-extended algebra is explicitly defined.

4. Relational Algebra

Relational algebra is a closed procedural query language: each operation consumes one or more relations and returns a relation. Closure allows complex expressions to be assembled from simpler ones.

Assume the following illustrative data:

`STUDENT`

| SID | Name | Major |

|---|---|---|

| S1 | Ada | CS |

| S2 | Ben | EE |

| S3 | Chen | CS |

`TAKES`

| SID | CID | Grade |

|---|---|---:|

| S1 | C1 | 82 |

| S1 | C2 | 91 |

| S2 | C1 | 74 |

| S3 | C1 | 88 |

| S3 | C2 | 84 |

4.1 Selection

Selection filters tuples:

\sigma_{condition}(R).

For example,

\sigma_{Grade\geq 85}(TAKES)

returns `(S1,C2,91)` and `(S3,C1,88)`. Selection changes cardinality but not degree.

4.2 Projection

Projection retains specified attributes:

\pi_{A_1,\ldots,A_k}(R).

The expression

\pi_{SID}(TAKES)

returns `{S1, S2, S3}`. Classical projection removes duplicates because its result is a set. SQL requires `SELECT DISTINCT SID` to obtain the same behavior.

4.3 Renaming

Renaming changes a relation or attribute name:

\rho_{S(NewA,NewB)}(R).

It is essential for self-joins and for making schemas union-compatible.

4.4 Set operations

For union, intersection, and difference, operands must be union-compatible: corresponding attributes must have compatible domains and represent corresponding meanings.

R\cup S,\qquad R\cap S,\qquad R-S.

Difference is directional. `R − S` contains tuples in `R` but not in `S`.

4.5 Cartesian product

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

R\times S.

If \(|R|=m\) and \(|S|=n\), and there are no complications from an empty input, then \(|R\times S|=mn\). Attribute names must be disambiguated when schemas overlap. Products are often intermediate steps rather than intended final results.

4.6 Joins

A theta join combines tuples satisfying a condition:

R\bowtie_{\theta}S=\sigma_{\theta}(R\times S).

An equijoin uses equality conditions. A natural join automatically equates all same-named attributes and retains one copy of each matched attribute.

To list student names and grades:

\pi_{Name,CID,Grade}(STUDENT\bowtie_{STUDENT.SID=TAKES.SID}TAKES).

Natural joins are concise but risky when unrelated attributes happen to share a name. Production queries should make important join conditions explicit.

Outer joins preserve selected nonmatching tuples and introduce nulls. They are useful in SQL but lie outside the minimal classical algebra unless the algebra is extended with null semantics.

5. Relational Division and Universal Conditions

5.1 Definition

Division answers “for all” questions. Let \(R(X,Y)\) have attribute groups \(X\) and \(Y\), and let \(S(Y)\) have the divisor attributes. Then

R\div S=\{x\in\pi_X(R)\mid \forall y\in S,\ (x,y)\in R\}.

Suppose `REQ(CID)` contains `{C1,C2}`. Then

\pi_{SID,CID}(TAKES)\div REQ

returns `{S1,S3}`, because each has taken both required courses.

5.2 Deriving division from primitive operations

Division can be expressed as

R\div S=\pi_X(R)-\pi_X\big((\pi_X(R)\times S)-R\big).

The derivation is interpreted as follows:

1. \(\pi_X(R)\) identifies candidate \(X\)-values already appearing in \(R\).

2. \(\pi_X(R)\times S\) generates every required candidate–divisor combination.

3. Subtracting \(R\) identifies missing combinations.

4. Projecting \(X\) identifies candidates missing at least one requirement.

5. Subtracting them leaves candidates satisfying every requirement.

5.3 Empty-divisor behavior

If \(S=\varnothing\), the universal condition is vacuously true for every candidate in \(\pi_X(R)\). Therefore,

R\div\varnothing=\pi_X(R).

It does not return every possible value from an external entity relation. The candidate universe in classical division is specifically \(\pi_X(R)\).

5.4 Exact SQL translation

Assume `TAKES.SID` and `TAKES.CID` are non-null and `REQ.CID` is non-null. A set-equivalent SQL formulation is:

```sql

SELECT DISTINCT t.sid

FROM Takes AS t

WHERE NOT EXISTS (

SELECT 1

FROM Req AS r

WHERE NOT EXISTS (

SELECT 1

FROM Takes AS t2

WHERE t2.sid = t.sid

AND t2.cid = r.cid

)

);

```

The outer query ranges over `Takes`, so its candidate universe is exactly \(\pi_{SID}(TAKES)\). If `REQ` is empty, every distinct SID occurring in `Takes` is returned, matching classical division.

If the outer query instead ranges over `STUDENT`, an empty `REQ` returns every student, including students absent from `TAKES`. That query is valid for a different requirement—universal testing over the declared student population—but it is not exactly `TAKES ÷ REQ` unless the candidate universe is explicitly defined as `STUDENT`.

6. Relational Calculus

Relational calculus is declarative. It describes which tuples qualify rather than prescribing an execution sequence.

6.1 Tuple relational calculus

Tuple relational calculus (TRC) uses tuple variables. Its general form is

\{t\mid P(t)\},

where \(P(t)\) is a logical formula.

Students majoring in computer science can be written as

\{s\mid STUDENT(s)\land s.Major='CS'\}.

Student identifiers having at least one grade of 85 or above are:

\{\langle s.SID\rangle\mid STUDENT(s)\land

\exists t(TAKES(t)\land t.SID=s.SID\land t.Grade\geq85)\}.

A universal query can be represented using negated existence. Students in `TAKES` who have taken every required course are:

\{\langle t.SID\rangle\mid TAKES(t)\land

\neg\exists r(REQ(r)\land

\neg\exists u(TAKES(u)\land u.SID=t.SID\land u.CID=r.CID))\}.

The initial `TAKES(t)` deliberately restricts the candidate universe to SIDs appearing in `TAKES`.

6.2 Domain relational calculus

Domain relational calculus (DRC) uses variables ranging over individual attribute values. Students with a grade of at least 85 can be expressed as

\{\langle sid,name\rangle\mid

\exists major\exists cid\exists grade(

STUDENT(sid,name,major)\land

TAKES(sid,cid,grade)\land grade\geq85)\}.

SQL resembles domain calculus because users state desired columns and predicates, although SQL includes many additional operational and language features.

6.3 Safety and domain independence

Unrestricted calculus expressions can describe infinite results. For example, \(\{x\mid \neg STUDENT(x)\}\) may include every value outside the finite database.

A query is domain independent when its result depends only on values in the database’s active domain, not on the choice of a larger external domain. Practical formulations use safe expressions whose output values are appropriately range-restricted by relations or constants.

Codd’s relational completeness result establishes that relational algebra and safe, domain-independent relational calculus have equivalent expressive power for standard relational queries. This does not mean that every unsafe calculus formula has an algebraic equivalent.

7. Query Equivalence and Formal Reasoning

Equivalent algebraic expressions can have different execution costs. Valid transformations include:

\sigma_{p\land q}(R)=\sigma_p(\sigma_q(R))

and, when predicate \(p\) refers only to attributes of \(R\),

\sigma_p(R\bowtie S)=\sigma_p(R)\bowtie S.

The second rule supports selection pushdown, reducing tuples before a join. Projection pushdown can similarly reduce tuple width, provided attributes needed later for joins or output are retained.

Set difference, outer joins, aggregation, duplicate-sensitive SQL, and nulls require additional care. For example, classical equality and SQL equality are not interchangeable when nulls are present. Query optimizers apply only transformations that preserve the database system’s actual semantics.

8. Illustrative Case: AI Programme Completion Audit

Background

A fictional university uses an AI system to recommend internships. Only students who have completed every compulsory module for their programme may be labelled “programme-ready.” An earlier pipeline used row counts and occasionally marked students eligible after duplicate completion records inflated the count.

Problem

The database contains:

`CANDIDATE(SID, Program)`;
`REQUIRED(Program, Module)`;
`COMPLETED(SID, Module)`.

The system must identify each candidate who has completed every module required by that candidate’s programme. Duplicate rows must not affect eligibility, and programmes with no recorded requirements require an explicit governance policy.

Analysis

Counting records is unreliable unless duplicates are removed and the count is compared with the correct programme-specific set. The logical condition is universal:

> There does not exist a required module for the candidate’s programme that the candidate has not completed.

Define:

Expected=CANDIDATE\bowtie REQUIRED,

where the join is on `Program`. This relation contains `(SID, Program, Module)` combinations that should exist.

Define actual completed combinations in the candidate context:

Actual=\pi_{SID,Program,Module}(CANDIDATE\bowtie COMPLETED),

where the join is on `SID`.

Then:

Missing=Expected-Actual,

and

Eligible=CANDIDATE-\pi_{SID,Program}(Missing).

Approach and solution

A corresponding SQL query is:

```sql

SELECT c.sid, c.program

FROM Candidate AS c

WHERE NOT EXISTS (

SELECT 1

FROM Required AS r

WHERE r.program = c.program

AND NOT EXISTS (

SELECT 1

FROM Completed AS d

WHERE d.sid = c.sid

AND d.module = r.module

)

);

```

The outer range is `CANDIDATE`, so a candidate in a programme with no recorded requirements satisfies the condition vacuously. Whether that result is acceptable is a governance question. The university may instead impose a constraint that every active programme must have at least one approved requirement or block the audit when requirement data is incomplete.

Keys and constraints should include:

primary key `(SID, Program)` on `CANDIDATE` if simultaneous programme registrations are represented separately;
primary key `(Program, Module)` on `REQUIRED`;
primary key `(SID, Module)` on `COMPLETED`, or an extended key if repeat attempts must be retained;
foreign keys to authoritative student, programme, and module relations.

Outcome

The formal query eliminates duplicate-count errors and exposes the empty-requirement policy instead of hiding it in implementation behavior. Eligibility can be traced to missing or completed module tuples, improving auditability.

Learning points

Universal requirements are best represented with division or double `NOT EXISTS`.
Candidate universes must be stated explicitly.
Keys prevent duplicates from altering analytical conclusions.
Vacuous truth is mathematically correct but may still require a business-policy safeguard.
AI labels derived from relational data should be reproducible and auditable.

9. Limitations, Governance, and Contemporary Practice

The relational model is powerful but cannot by itself guarantee that stored data is truthful, timely, unbiased, or ethically collected. Domain constraints may validate a grade’s range without proving that the assessment was fair or correctly entered.

For AI and analytics, database governance should include:

documented data lineage from source relations to model features;
least-privilege access and protection of sensitive attributes;
explicit missing-data semantics;
temporal validity where facts change over time;
retention and deletion controls required by applicable regulation;
tests for key, foreign-key, range, and population assumptions;
monitoring for join multiplication and unintended sample exclusion.

Modern systems extend relational technology with JSON values, arrays, graph features, distributed SQL, vector data types, and vector similarity search. These developments do not remove the need for relational discipline. Embedding identifiers, model versions, consent records, and source-document references still require dependable keys and integrity constraints.

Data provenance research further studies why a tuple appears in a result and which source tuples contributed to it. Provenance is particularly relevant to explainable AI, regulatory audits, and debugging feature pipelines.

10. Best Practices

1. Define keys from stable business rules, not accidental uniqueness.

2. Treat composite foreign keys as complete values and choose a null-matching rule deliberately.

3. Prefer non-null identifiers and normalize distinct meanings of missingness.

4. State the candidate universe for every universal query.

5. Use `DISTINCT` when SQL must reproduce set projection.

6. Prefer `NOT EXISTS` to null-sensitive `NOT IN` formulations.

7. Test empty relations, duplicate source rows, nulls, and unmatched foreign keys.

8. Use explicit join predicates when natural joins could be misinterpreted.

9. enforce critical integrity rules in the database where possible.

10. Preserve query definitions, model-feature lineage, and constraint decisions for auditability.

Chapter Summary

The relational model represents a relation as a set of named tuples, where each tuple maps attributes to domain values. Candidate keys minimally identify tuples, primary keys enforce entity identity, and foreign keys connect complete values across relations. Composite foreign-key null behavior must be governed by an explicit matching rule such as `MATCH SIMPLE` or `MATCH FULL`.

Relational algebra expresses queries through operations including selection, projection, set operations, products, joins, and division. Division formalizes universal conditions and returns candidates drawn from the dividend. Consequently, division by an empty relation returns the projection of the dividend on its candidate attributes, not every value from an unrelated entity table.

Tuple and domain relational calculus express desired results through logical predicates. Safe, domain-independent calculus has the same expressive power as relational algebra. SQL is influenced by these foundations but differs through duplicate rows, nulls, three-valued logic, aggregation, and other extensions. Formal reasoning, careful constraints, and explicit candidate populations are essential for trustworthy operational and AI data systems.

Key Terms

Active domain: The set of values appearing in a database instance or explicitly in a query.

Candidate key: A minimal set of attributes that uniquely identifies every tuple in a relation.

Cardinality: The number of tuples in a relation instance.

Composite key: A key containing more than one attribute.

Domain: The permitted set and interpretation of values for an attribute.

Domain independence: The property that a query result does not depend on values outside the active database domain.

Domain relational calculus: A declarative formal language whose variables range over attribute values.

Entity integrity: The requirement that primary-key components are unique and non-null.

Foreign key: Attributes in one relation that refer to a candidate key in another or the same relation.

Natural join: A join that equates all same-named attributes automatically.

Null: An SQL marker for an absent value rather than an ordinary domain value.

Projection: A relational operation that retains selected attributes and removes duplicate tuples.

Referential integrity: The requirement that each applicable foreign-key value match a referenced key.

Relation: A set of tuples conforming to a relation schema.

Relational algebra: An operational formal query language based on relation-producing operators.

Relational division: An operation used to identify values related to every tuple in another relation.

Selection: A relational operation that retains tuples satisfying a predicate.

Superkey: Any attribute set whose values uniquely identify tuples.

Tuple: A mapping from the attributes of a relation schema to corresponding domain values.

Tuple relational calculus: A declarative formal language whose variables range over tuples.

Review Questions

Short Answer Questions

1. Why is a tuple better described as an attribute-to-value mapping than as an ordered list?

2. Distinguish a superkey from a candidate key.

3. What does `MATCH FULL` require for a nullable composite foreign key?

4. Why does `WHERE Grade <> 50` not return rows whose grade is null?

5. What is meant by closure in relational algebra?

Long Answer Questions

1. Explain domain, key, entity-integrity, referential-integrity, and semantic constraints with examples.

2. Compare classical relations with SQL tables, focusing on duplicates, nulls, ordering, and projection.

3. Explain the principal relational algebra operations and the conditions under which each is valid.

4. Compare tuple relational calculus and domain relational calculus, including safety and domain independence.

5. Derive relational division from projection, Cartesian product, and difference, and explain its empty-divisor behavior.

Analytical / Application Questions

1. Given `SUPPLIES(Supplier, Part)` and `REQUIRED(Part)`, formulate relational algebra and SQL queries that return suppliers supplying every required part. Ensure that an empty `REQUIRED` relation returns only suppliers appearing in `SUPPLIES`.

2. Design keys and foreign keys for a university schema containing departments, composite course codes, sections, and registrations. Justify whether `MATCH SIMPLE` or `MATCH FULL` should govern each composite foreign key.

3. An AI pipeline joins student, assessment, and attendance tables but produces more rows than expected. Use relational cardinality, key, and join reasoning to diagnose likely causes and propose integrity tests.

Multiple Choice Questions with Answers

Question 1

In the formal relational model, which description of a tuple is most accurate?

A. An ordered list whose position determines attribute meaning

B. A mapping from attribute names to domain values

C. A multiset of values without attribute names

D. A physical record stored in index order

Correct Answer: B. A mapping from attribute names to domain values

Question 2

Which property distinguishes a candidate key from a general superkey?

A. It may contain null values

B. It must be selected as the primary key

C. It is minimal with respect to its attributes

D. It must contain exactly one attribute

Correct Answer: C. It is minimal with respect to its attributes

Question 3

Under MATCH FULL, what is permitted for a nullable composite foreign key?

A. Any combination of null and non-null components

B. Only the first component may be null

C. Each component may independently match a different row

D. All components are null, or all are non-null and match one referenced key

Correct Answer: D. All components are null, or all are non-null and match one referenced key

Question 4

What is the result of TRUE AND UNKNOWN in SQL three-valued logic?

A. UNKNOWN

B. TRUE

C. FALSE

D. NULL as a stored value

Correct Answer: A. UNKNOWN

Question 5

Given the five TAKES tuples in the chapter, how many tuples satisfy Grade >= 85?

A. 1

B. 2

C. 3

D. 4

Correct Answer: B. 2

Question 6

Which relational algebra operation requires union-compatible operands?

A. Selection

B. Cartesian product

C. Set difference

D. Theta join

Correct Answer: C. Set difference

Question 7

If R(X,Y) contains X-values {a,b} and S(Y) is empty, what does classical R ÷ S return?

A. The empty relation

B. Every value in the universal domain

C. Only X-values referenced by another entity relation

D. The set {a,b}

Correct Answer: D. The set {a,b}

Question 8

Why does the chapter's exact SQL translation of TAKES ÷ REQ range its outer query over TAKES?

A. To make the candidate universe equal to the SIDs occurring in TAKES

B. To include every registered student

C. To prevent REQ from being empty

D. To preserve duplicate SIDs in the output

Correct Answer: A. To make the candidate universe equal to the SIDs occurring in TAKES

Question 9

What restriction is central to the practical use of relational calculus?

A. Every query must contain aggregation

B. Expressions should be safe and domain independent

C. Only universal quantifiers may be used

D. Tuple variables must range over infinite domains

Correct Answer: B. Expressions should be safe and domain independent

Question 10

Which SQL pattern most directly expresses that no required item is missing?

A. A single CROSS JOIN without a predicate

B. NOT IN with an unrestricted nullable subquery

C. Nested NOT EXISTS predicates

D. ORDER BY followed by DISTINCT

Correct Answer: C. Nested NOT EXISTS predicates

Database Management Systems
Chapter 3 of 15

Chapter 3 Relational Model and Formal Query Foundations

Relational Model and Formal Query Foundations

Introduction

Learning Objectives

1. Foundations of the Relational Model

1.1 Domains, attributes, and relation schemas

1.2 Tuples as mappings

1.3 Properties of a classical relation

2. Keys and Integrity Constraints

2.1 Superkeys and candidate keys

2.2 Foreign keys and referential integrity

2.3 Major integrity constraints

2.4 Update actions

3. Null Values and Three-Valued Logic

3.1 Meaning of null

3.2 Truth values

3.3 Practical null hazards

4. Relational Algebra

4.1 Selection

4.2 Projection

4.3 Renaming

4.4 Set operations

4.5 Cartesian product

4.6 Joins

5. Relational Division and Universal Conditions

5.1 Definition

5.2 Deriving division from primitive operations

5.3 Empty-divisor behavior

5.4 Exact SQL translation

6. Relational Calculus

6.1 Tuple relational calculus

6.2 Domain relational calculus

6.3 Safety and domain independence

7. Query Equivalence and Formal Reasoning

8. Illustrative Case: AI Programme Completion Audit

Background

Problem

Analysis

Approach and solution

Outcome

Learning points

9. Limitations, Governance, and Contemporary Practice

10. Best Practices

Chapter Summary

Key Terms

Review Questions

Short Answer Questions

Long Answer Questions

Analytical / Application Questions

Multiple Choice Questions with Answers

Question 1

Question 2

Question 3

Question 4

Question 5

Question 6

Question 7

Question 8

Question 9

Question 10

Chapter 3
Relational Model and Formal Query Foundations