Relational Theory, Data Modeling and Advanced SQL

Introduction and Context

Relational database systems remain central to enterprise information management because they provide a formally grounded model for representing data, enforcing integrity, and querying complex relationships. Although modern systems increasingly incorporate document, graph, stream, and machine-learning technologies, relational databases continue to support financial systems, healthcare platforms, logistics, public administration, and analytical applications. Their durability arises from a combination of mathematical foundations, transactional guarantees, mature optimization, and expressive query languages.

This chapter develops the theory and practice required to design robust relational schemas and exploit advanced SQL. It connects conceptual data modeling with functional dependencies, normalization, controlled denormalization, schema evolution, and sophisticated query construction. It also considers SQL/JSON, temporal data, procedural extensions, recursive queries, and governance concerns.

The discussion uses standard relational terminology. SQL syntax differs among PostgreSQL, Oracle, SQL Server, and MySQL; therefore, examples emphasize portable principles and identify dialect-sensitive features where necessary.

Learning Objectives

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

1. Distinguish entities, attributes, relationships, keys, constraints, and relational schemas.

2. Analyze functional dependencies and decompose relations into normalized schemas.

3. Explain lossless join and dependency preservation.

4. Evaluate when denormalization is justified and how its risks can be controlled.

5. Design schemas that support evolution without compromising integrity.

6. Construct advanced SQL using joins, common table expressions, window functions, recursive queries, and procedural features.

7. Query and validate SQL/JSON and temporal data.

8. Assess performance, security, governance, and maintainability implications of database designs.

1. The Relational Model as a Foundation

A relation can be viewed formally as a set of tuples over a set of attributes. In practical database terminology, a relation is represented by a table, an attribute by a column, and a tuple by a row. The formal model is more precise than the visual table analogy: relations are sets, so duplicate tuples are not conceptually meaningful, and row order has no semantic significance.

A relational schema is written as R(A1, A2, ..., An), where R is the relation name and the Ai are attributes. A relation instance is the set of tuples that conforms to this schema at a particular time. A database schema contains multiple related relations together with integrity constraints.

The relational model separates logical structure from physical implementation. An optimizer may use indexes, partitioning, materialized views, compression, or parallel execution without changing the logical meaning of a query. This logical–physical separation is a major reason for the model’s longevity.

1.1 Keys and Identifiers

A superkey is a set of attributes that uniquely identifies each tuple. A candidate key is a minimal superkey: removing any attribute would destroy uniqueness. One candidate key is selected as the primary key. Other candidate keys may be declared UNIQUE and are often called alternate keys.

A foreign key is an attribute or attribute set in one relation that references a candidate or primary key in another relation. It expresses referential integrity. For example:

```sql

CREATE TABLE Department (

department_id INTEGER PRIMARY KEY,

department_name VARCHAR(100) NOT NULL UNIQUE

);

CREATE TABLE Employee (

employee_id INTEGER PRIMARY KEY,

department_id INTEGER NOT NULL,

employee_name VARCHAR(150) NOT NULL,

CONSTRAINT fk_employee_department

FOREIGN KEY (department_id)

REFERENCES Department(department_id)

);

```

The primary key provides entity integrity: it must be unique and non-null. The foreign key prevents references to nonexistent departments unless the constraint is deliberately deferred or the value is null where nullability is allowed. Referential actions such as CASCADE, SET NULL, or RESTRICT must be selected according to business semantics, not convenience.

Surrogate keys, such as generated integers or UUIDs, do not replace the need for business uniqueness constraints. If employee email addresses must be unique, that rule should be declared independently of the surrogate employee identifier.

1.2 Integrity Constraints

Important constraint categories include:

• Domain constraints: values must belong to an appropriate domain, such as date, integer, or a bounded status set.
• NOT NULL constraints: a value is mandatory.
• CHECK constraints: a predicate must evaluate as true or, depending on SQL three-valued logic, not false; careful design is required when nulls are possible.
• UNIQUE constraints: values or value combinations cannot duplicate.
• PRIMARY KEY constraints: establish the chosen tuple identifier.
• FOREIGN KEY constraints: preserve references between relations.

Constraints should be enforced as close to the data as possible. Application validation is useful but insufficient because data may also arrive through imports, integration services, administrative tools, or other applications.

SQL uses three-valued logic: predicates may evaluate to TRUE, FALSE, or UNKNOWN. UNKNOWN commonly results from comparisons involving NULL. Consequently, `salary <> 50000` does not select rows where salary is null. Use `IS NULL` and `IS NOT NULL` explicitly, and define whether missing information is semantically different from an unknown, inapplicable, or not-yet-recorded value.

2. Conceptual and Logical Data Modeling

Data modeling translates organizational requirements into structures that can be implemented and governed. A typical process includes requirements analysis, conceptual modeling, logical relational design, physical design, implementation, and validation.

2.1 Entities, Attributes, and Relationships

An entity is a distinguishable object or event about which data is stored. Examples include Customer, Product, Invoice, and Shipment. Attributes describe entities. Relationships express associations, such as a customer placing an order.

Cardinality specifies how many instances may participate in a relationship. A one-to-many relationship between Department and Employee means one department may have many employees, while each employee belongs to one department. The foreign key normally appears on the many side.

A many-to-many relationship requires an associative relation. For example, students and courses may be modeled as:

```sql

CREATE TABLE Enrollment (

student_id INTEGER NOT NULL REFERENCES Student(student_id),

course_id INTEGER NOT NULL REFERENCES Course(course_id),

enrolled_on DATE NOT NULL,

grade DECIMAL(4,2),

PRIMARY KEY (student_id, course_id)

);

```

The composite primary key prevents duplicate enrollment in the same course. If retakes are allowed, the key may need to include an attempt number or a distinct enrollment identifier, illustrating that key design depends on business rules.

2.2 Modeling Choices and Their Consequences

A model must distinguish facts from derived values. For example, order total can be computed from quantity and unit price, but storing it may be justified for auditability or historical pricing if the calculation inputs can change. Whenever a derived value is stored, its maintenance rule must be explicit.

Optionality also requires precision. “A customer may have an address” is different from “every order must have a shipping address.” Address history may require a separate relation rather than a nullable column if multiple addresses and effective dates must be retained.

A strong model records the grain of each relation: what one row represents. Ambiguous grain is a common source of double counting in analytical queries and update anomalies in transactional systems.

3. Functional Dependencies and Normalization

A functional dependency (FD) X → Y states that, for any two tuples in a relation, if their X values are equal, their Y values must also be equal. X is the determinant and Y is functionally dependent on X. The dependency is a semantic assertion about the domain, not merely an observation in a small sample.

For relation `Employee(employee_id, employee_name, department_id, department_name)`, plausible dependencies are:

• `employee_id → employee_name, department_id`
• `department_id → department_name`

The second dependency indicates that department name is repeated for every employee in the department. This repetition can cause update, insertion, and deletion anomalies.

3.1 Attribute Closure

The closure X+ is the set of attributes functionally determined by X under a set of dependencies F. It is computed by initially placing X in X+ and repeatedly adding attributes Y whenever a dependency Z → Y has Z contained in X+, until no further attributes can be added.

Suppose R(A, B, C, D) has F = {A → B, B → C, AC → D}. The closure of A proceeds as follows:

1. Start with A+ = {A}.

2. From A → B, add B: A+ = {A, B}.

3. From B → C, add C: A+ = {A, B, C}.

4. Now AC is contained in A+, so add D.

5. Therefore A+ = {A, B, C, D}.

Thus A is a superkey for R. Attribute closure is useful for finding candidate keys and testing whether a dependency follows from a dependency set.

3.2 Normal Forms

First Normal Form (1NF) requires atomic attribute values within the relational interpretation. Repeating groups, such as multiple phone numbers stored in columns Phone1, Phone2, and Phone3, indicate a modeling problem. A separate CustomerPhone relation is generally more flexible.

Second Normal Form (2NF) applies primarily where a candidate key is composite. A relation is in 2NF if it is in 1NF and every non-prime attribute is fully functionally dependent on every candidate key; no non-key attribute depends on only part of a composite key.

Third Normal Form (3NF) requires that for every nontrivial FD X → A, either X is a superkey or A is a prime attribute, meaning it belongs to some candidate key. A common practical interpretation is that non-key attributes should not depend transitively on a key through another non-key attribute.

Boyce–Codd Normal Form (BCNF) is stricter: for every nontrivial FD X → Y, X must be a superkey. BCNF can remove anomalies that 3NF permits, although a BCNF decomposition may fail to preserve all dependencies.

Higher normal forms address multivalued and join dependencies. Fourth Normal Form is relevant when independent multivalued facts are stored together, while Fifth Normal Form concerns certain join dependencies. These forms are less frequently required in ordinary transactional designs but remain important in specialized modeling and theoretical analysis.

3.3 Decomposition: Losslessness and Dependency Preservation

Decomposition replaces a relation with smaller relations. A decomposition should ideally be lossless, meaning joining the decomposed relations reconstructs exactly the original legal relation, without spurious tuples. It should also preserve dependencies so that constraints can be enforced without repeatedly joining relations.

For a binary decomposition of R into R1 and R2, a standard lossless-join test under dependency set F is that the common attributes R1 ∩ R2 functionally determine R1 or determine R2 under F+ . This criterion assumes the dependencies and relation schemas are correctly specified.

A practical example is:

`Employee(employee_id, employee_name, department_id, department_name)`

decomposed into:

• `Employee(employee_id, employee_name, department_id)`
• `Department(department_id, department_name)`

The common attribute `department_id` determines all attributes of Department, so the decomposition is lossless under the stated dependency.

Normalization is not an absolute objective. It reduces redundancy and anomalies, but it may increase join complexity. The appropriate design depends on workload, consistency requirements, latency targets, and the cost of recomputation.

4. Denormalization and Schema Evolution

Denormalization deliberately introduces controlled redundancy. Examples include storing a current account balance, copying a product name into an immutable invoice line, maintaining a summary table, or creating a materialized view for frequent reports.

Denormalization is justified when a measured workload demonstrates that joins or repeated aggregation impose unacceptable cost, and when the additional consistency mechanisms are understood. It should not be used merely because a query appears difficult.

Controls include:

• authoritative-source documentation;
• triggers or transactional application logic;
• scheduled reconciliation jobs;
• checksums and audit trails;
• freshness indicators for derived data;
• monitoring of discrepancy rates.

Schema evolution concerns changes after deployment. Expand-and-contract is a robust migration pattern:

1. Expand: add new tables or nullable columns without breaking old clients.

2. Migrate: backfill and validate data, often in batches.

3. Adopt: deploy code that writes and reads the new representation.

4. Contract: remove obsolete structures only after dependency analysis and a safe deprecation period.

Backward-compatible changes usually include adding nullable columns, adding new tables, and adding indexes. Riskier changes include renaming columns, changing data types, tightening nullability, and altering key semantics. Versioned APIs, migration scripts under source control, rollback plans, and production-like testing are essential.

Online migrations must consider locks, transaction duration, replication lag, triggers, and concurrent writes. A logically simple change can become operationally dangerous on a large table.

5. Advanced SQL Querying

5.1 Common Table Expressions

A common table expression (CTE) gives a name to a query expression for the duration of one statement. It improves readability and can structure multi-stage transformations.

```sql

WITH department_totals AS (

SELECT department_id, SUM(salary) AS total_salary

FROM Employee

GROUP BY department_id

), ranked AS (

SELECT department_id, total_salary,

RANK() OVER (ORDER BY total_salary DESC) AS salary_rank

FROM department_totals

)

SELECT *

FROM ranked

WHERE salary_rank <= 5;

```

A CTE is not automatically a materialized temporary table. Whether it is inlined or materialized depends on the database system and query plan. CTEs should therefore be evaluated for readability and optimization behavior rather than assumed performance benefits.

5.2 Window Functions

A window function computes over a set of related rows while retaining row-level detail. Its logical structure commonly includes `PARTITION BY` and `ORDER BY`.

```sql

SELECT employee_id, department_id, salary,

ROW_NUMBER() OVER (

PARTITION BY department_id

ORDER BY salary DESC, employee_id

) AS department_position,

AVG(salary) OVER (

PARTITION BY department_id

) AS department_average

FROM Employee;

```

`ROW_NUMBER` assigns unique sequence numbers. `RANK` gives tied rows the same rank and leaves gaps after ties. `DENSE_RANK` gives tied rows the same rank without gaps. The tie-breaking column in the example makes row numbering deterministic.

Window frames matter for running calculations. For example, a cumulative amount may be expressed as:

```sql

SUM(amount) OVER (

PARTITION BY account_id

ORDER BY transaction_time, transaction_id

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

)

```

An explicit `ROWS` frame is preferable when physical row sequence is intended, especially where timestamps can tie.

5.3 Recursive Queries

Recursive CTEs are suitable for hierarchies, bill of materials, graph traversal, and organizational reporting lines. A recursive query contains an anchor member and a recursive member.

```sql

WITH RECURSIVE org AS (

SELECT employee_id, manager_id, employee_name, 0 AS level

FROM Employee

WHERE manager_id IS NULL

UNION ALL

SELECT e.employee_id, e.manager_id, e.employee_name, o.level + 1

FROM Employee e

JOIN org o ON e.manager_id = o.employee_id

)

SELECT *

FROM org

ORDER BY level, employee_id;

```

Production queries should address cycles, maximum depth, duplicate paths, and disconnected nodes. Some systems support cycle-detection syntax; otherwise, a path column can be maintained and checked. Recursive queries over very large graphs may require specialized graph or iterative processing techniques.

5.4 Procedural Extensions

Procedural SQL extensions support variables, conditionals, loops, exception handling, and stored routines. They are useful when logic must execute close to data, when a transaction must combine several operations, or when reusable database-side APIs are appropriate.

However, procedural code can become difficult to test, version, and deploy across database products. Set-based SQL is generally preferable to row-by-row loops for bulk operations. Stored procedures should have clear contracts, controlled privileges, predictable transaction behavior, and observability.

6. SQL/JSON and Semi-Structured Data

Relational systems increasingly support JSON columns and functions. JSON is useful for genuinely variable or nested attributes, external payloads, and gradual integration. It should not be used to avoid modeling stable, frequently queried attributes.

A typical table might be:

```sql

CREATE TABLE EventLog (

event_id BIGINT PRIMARY KEY,

occurred_at TIMESTAMP NOT NULL,

payload JSON NOT NULL

);

```

Queries can extract fields, test path existence, expand arrays, and construct JSON results. Exact syntax varies significantly. PostgreSQL commonly uses `->`, `->>`, and `jsonb_path_query`; SQL Server uses `JSON_VALUE` and `OPENJSON`; Oracle and MySQL provide their own JSON operators and indexing strategies.

Important design questions include:

• Which fields require relational constraints?
• Are JSON paths stable enough to index?
• How are schema versions represented?
• How are malformed or malicious payloads rejected?
• Is duplicate information stored both in columns and JSON?

JSON validation is not equivalent to business validation. A syntactically valid document may still contain invalid dates, unauthorized values, or inconsistent identifiers.

7. Temporal Data Management

Temporal databases represent change over time. Two important dimensions are:

• Valid time: when a fact is true in the modeled world.
• Transaction time: when the database recorded the fact.

A product price may be valid from 1 June to 30 June, although it is inserted into the database on 3 June. A correction entered on 10 June may change transaction history without changing the original valid period.

A simple valid-time table may include `valid_from` and `valid_to`. Temporal integrity requires preventing overlapping periods where the business rule permits only one active version. Some systems support system-versioned temporal tables, while others require triggers, exclusion constraints, history tables, or carefully designed application logic.

Queries must state whether they need current state, state as of a business date, or state as known at a previous system time. Auditing and legal reporting often require both dimensions. Time zones, daylight-saving transitions, clock precision, and inclusive versus exclusive interval boundaries must be specified. Half-open intervals—such as `[valid_from, valid_to)`—often simplify adjacent-period logic because the end instant is excluded.

8. Worked Integrated Example

Consider a university that records course offerings, instructors, students, and assessments. An initial relation is:

`Registration(student_id, student_name, course_id, course_title, instructor_id, instructor_name, semester, assessment_type, score)`

Assume the following dependencies:

• `student_id → student_name`
• `course_id → course_title`
• `instructor_id → instructor_name`
• `(course_id, semester) → instructor_id`
• `(student_id, course_id, semester, assessment_type) → score`

The relation contains repeated student, course, and instructor facts. A normalized design could include:

• `Student(student_id, student_name)`
• `Course(course_id, course_title)`
• `Instructor(instructor_id, instructor_name)`
• `Offering(course_id, semester, instructor_id)`
• `Registration(student_id, course_id, semester)`
• `Assessment(student_id, course_id, semester, assessment_type, score)`

The keys reflect the grain of each relation. `Offering` has a composite key because a course may be taught in multiple semesters. `Assessment` uses a composite key including assessment type because a student may receive several assessment results in one offering.

A query identifying each student’s highest assessment score per offering can use:

```sql

WITH scored AS (

SELECT student_id, course_id, semester, assessment_type, score,

RANK() OVER (

PARTITION BY student_id, course_id, semester

ORDER BY score DESC

) AS score_rank

FROM Assessment

)

SELECT student_id, course_id, semester, assessment_type, score

FROM scored

WHERE score_rank = 1;

```

Because `RANK` preserves ties, multiple assessment types may be returned. If exactly one row is required, a deterministic `ROW_NUMBER` policy must be chosen, but that policy should be meaningful rather than arbitrary.

9. Applied Case: Illustrative Retail Order Platform

Background

This illustrative case concerns a growing online retailer. Its original database stored customer information, order data, product descriptions, payment status, and shipping details in a single wide order table. The system worked for a small user base but produced inconsistent product names, duplicated customer data, and slow monthly reports.

Problem

The retailer faced four problems: updates to a product name required many row changes; historical invoices changed when current product prices changed; reports double-counted orders when joining payment and shipment records; and a new requirement demanded storage of external marketplace payloads and auditable address history.

Analysis

The design team first documented relation grain and dependencies. Product identity determined the current product description, but an invoice line needed the price agreed at purchase time. A customer could have multiple addresses, each valid during a period. An order could have multiple payment attempts and shipment events, so these were not attributes of a single order row.

Approach and Solution

The team decomposed the schema into Customer, Address, CustomerAddressHistory, Product, OrderHeader, OrderLine, PaymentAttempt, and ShipmentEvent. OrderLine stored quantity and agreed unit price as a historical commercial fact. Current product descriptions remained in Product. JSON was used for marketplace-specific attributes that varied by source, while core identifiers and monetary values remained relational and constrained.

Schema evolution used an expand-and-contract migration. New tables were added first, legacy data was backfilled in batches, dual writes were monitored, and old columns were retired after reconciliation. Window functions produced the latest payment attempt per order. Recursive SQL was used only for the product-category hierarchy, with cycle checks during administration.

Temporal address history used half-open validity intervals. Reports explicitly selected the address valid at shipment time rather than silently using the customer’s current address. A materialized daily sales summary was introduced only after query-plan analysis demonstrated that repeated aggregation was a measurable bottleneck.

Outcome

The redesigned system eliminated product-name update anomalies, preserved historical invoice facts, and made payment and shipment multiplicity explicit. Reconciliation reports identified discrepancies between legacy and new structures during migration. Query performance improved for recurring reports, although the system required additional indexes, monitoring, and operational discipline.

Learning Points

The case illustrates that normalization is primarily an integrity strategy, not an aesthetic preference. Historical facts must be modeled separately from current descriptive facts. JSON is valuable for bounded variability but should not conceal stable business data. Temporal requirements should be identified during modeling, not added after audit problems occur. Finally, denormalized summaries are safest when their source, refresh policy, and correctness checks are explicit.

10. Performance, Security, and Governance

Advanced SQL must be evaluated with execution plans, cardinality estimates, indexes, and realistic data volumes. Indexes accelerate selected access paths but impose storage and write-maintenance costs. Composite index order should reflect common predicates, joins, and ordering requirements. Query tuning must preserve semantics; replacing a correct query with a faster but incomplete query is not optimization.

Security includes least-privilege roles, parameterized queries, row-level security where appropriate, encrypted connections, secrets management, and protection against inference through overly broad access. Dynamic SQL requires careful identifier handling and parameter binding. Stored procedures can reduce direct table access, but they are not automatically secure.

Governance requires data ownership, classification, retention rules, lineage, auditability, and controlled change management. Personal data in relational or JSON columns remains subject to applicable privacy obligations. Temporal history may improve accountability but can also increase privacy risk by retaining information longer. Deletion, correction, legal hold, and subject-access requirements must be reconciled with audit and regulatory obligations.

11. Contemporary Developments and Critical Perspective

Modern relational platforms increasingly combine relational tables with JSON, vectors, full-text search, geospatial types, event processing, and distributed execution. This convergence is useful, but feature availability does not eliminate modeling trade-offs. A single database product may support multiple representations, yet each representation has different integrity, indexing, transaction, and governance characteristics.

AI-assisted SQL generation can accelerate exploration and documentation, but generated queries require human validation. Risks include incorrect joins, unnoticed row multiplication, unsafe dynamic SQL, leakage of sensitive data into external services, and false confidence caused by plausible results. A responsible workflow defines the intended grain, tests against known cases, reviews execution plans, and checks authorization before deployment.

Best-Practice Checklist

1. State the grain of every relation and query result.

2. Declare keys and business uniqueness explicitly.

3. Enforce critical integrity rules in the database.

4. Use functional dependencies to guide normalization.

5. Confirm that decompositions are lossless and assess dependency preservation.

6. Denormalize only for measured, documented reasons.

7. Treat NULL semantics explicitly.

8. Use deterministic ordering in window functions.

9. Protect recursive queries against cycles and uncontrolled depth.

10. Separate stable relational facts from genuinely variable JSON attributes.

11. Define valid-time and transaction-time semantics precisely.

12. Use expand-and-contract for high-risk schema changes.

13. Test migrations, queries, permissions, and recovery procedures with realistic data.

14. Monitor derived data, replication lag, query plans, and reconciliation results.

Chapter Summary

Relational theory provides the foundation for reliable database design. Keys identify tuples, foreign keys express relationships, and integrity constraints protect domain and referential correctness. Conceptual modeling clarifies entities, relationships, optionality, cardinality, and relation grain.

Functional dependencies support systematic analysis of redundancy and anomalies. Normal forms—especially 1NF, 2NF, 3NF, and BCNF—provide increasingly strong controls over dependency structure. Decompositions should be assessed for lossless reconstruction and dependency preservation. Denormalization can improve performance or preserve historical facts, but it introduces synchronization and governance obligations.

Advanced SQL extends ordinary filtering and joining through CTEs, window functions, recursive queries, procedural extensions, SQL/JSON, and temporal techniques. Effective use requires attention to semantics, determinism, null behavior, cycles, time dimensions, and database-specific syntax. Schema evolution should use staged migrations, validation, compatibility planning, and controlled retirement.

Finally, database engineering is both technical and organizational. Performance, privacy, authorization, auditability, lineage, and responsible use of AI-generated SQL must be considered alongside formal correctness. A high-quality design balances integrity, usability, performance, evolvability, and governance.

Key Terms

Functional dependency: A rule stating that one attribute set determines another.

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

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

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

Referential integrity: The requirement that valid foreign-key references point to existing keys.

Superkey: Any attribute set that uniquely identifies tuples, whether minimal or not.

Normalization: The systematic reduction of redundancy and modification anomalies.

Lossless decomposition: A decomposition whose relations can be joined without spurious tuples.

Dependency preservation: The ability to enforce specified dependencies using decomposed relations.

BCNF: A normal form requiring every determinant of a nontrivial dependency to be a superkey.

Denormalization: Deliberate introduction of redundancy for justified performance or historical purposes.

Common table expression: A named query expression available within one SQL statement.

Window function: A function that computes across related rows while retaining row-level output.

Recursive CTE: A CTE containing anchor and recursive components for hierarchical processing.

SQL/JSON: Relational database functionality for storing, validating, querying, or generating JSON.

Valid time: The period during which a fact is true in the modeled world.

Transaction time: The period or timestamp indicating when a fact was recorded by the database.

Schema evolution: The controlled modification of database structures over their operational lifetime.

Relation grain: The precise meaning represented by one row in a relation or query result.

Three-valued logic: SQL predicate evaluation using TRUE, FALSE, and UNKNOWN.

Review Questions

Short Answer Questions

1. Distinguish a candidate key from a superkey.

2. Why should business uniqueness often be declared separately from a surrogate primary key?

3. What is the purpose of a window function?

4. Define valid time and transaction time.

5. State two risks associated with denormalization.

Long Answer Questions

1. Explain functional dependencies, attribute closure, and their use in identifying candidate keys.

2. Compare 3NF and BCNF, including the trade-off between stronger normalization and dependency preservation.

3. Describe the expand-and-contract approach to schema evolution and explain why it reduces operational risk.

4. Explain how recursive CTEs work and discuss cycle, depth, and duplicate-path concerns.

5. Discuss the appropriate and inappropriate uses of JSON in a relational database.

Analytical / Application Questions

1. A relation `SalesLine(order_id, product_id, product_name, customer_id, customer_name, quantity, unit_price)` stores one row per product in an order. Identify plausible functional dependencies, propose a normalized decomposition, and explain which price should be stored for historical invoices.

2. Write a SQL strategy using a window function to select the latest payment attempt for each order. State how ties in timestamps should be handled.

3. A company stores both current customer addresses and address history. Design a temporal approach that returns the address valid at shipment time and explain how overlapping validity intervals should be prevented.

Multiple Choice Questions with Answers

Question 1

Which statement best defines a candidate key?

A. Any attribute that contains a foreign-key reference

B. A minimal set of attributes that uniquely identifies a tuple

C. A non-null attribute selected only for indexing

D. A set of attributes that may contain duplicate values

Correct Answer: B. A minimal set of attributes that uniquely identifies a tuple

Question 2

Given dependencies A → B and B → C, what is contained in A+?

A. Only A

B. A and B

C. A, B, and C

D. B and C but not A

Correct Answer: C. A, B, and C

Question 3

What is the primary purpose of a foreign-key constraint?

A. To enforce referential integrity

B. To sort rows physically

C. To calculate aggregate values

D. To guarantee that every column is non-null

Correct Answer: A. To enforce referential integrity

Question 4

Which window function assigns the same rank to tied rows and leaves gaps after the ties?

A. ROW_NUMBER

B. DENSE_RANK

C. RANK

D. NTILE

Correct Answer: C. RANK

Question 5

What are the two essential parts of a recursive CTE?

A. A trigger and an index

B. An anchor member and a recursive member

C. A primary key and a foreign key

D. A view and a stored procedure

Correct Answer: B. An anchor member and a recursive member

Question 6

Which technique is most appropriate for a high-risk backward-compatible schema migration?

A. Immediately drop the old column

B. Rewrite all clients before adding the new structure

C. Use expand-and-contract with backfill and validation

D. Disable constraints permanently during migration

Correct Answer: C. Use expand-and-contract with backfill and validation

Question 7

What does valid time represent?

A. When the database server was installed

B. When a fact is true in the modeled world

C. How long a query takes to execute

D. When an index was last rebuilt

Correct Answer: B. When a fact is true in the modeled world

Question 8

Why can storing a stable business attribute only inside JSON be problematic?

A. JSON cannot contain text

B. It prevents all forms of querying

C. It may weaken relational constraints and complicate indexing

D. It automatically duplicates every row

Correct Answer: C. It may weaken relational constraints and complicate indexing

Question 9

Which property means that joining decomposed relations reconstructs the original legal relation without spurious tuples?

A. Dependency preservation

B. Lossless join

C. Domain independence

D. Referential optionality

Correct Answer: B. Lossless join

Question 10

What does SQL three-valued logic add to TRUE and FALSE?

A. NULL

B. UNKNOWN

C. DEFAULT

D. EMPTY

Correct Answer: B. UNKNOWN