Lecture 7 | DB Constraints

Created @May 19, 2025 2:24 PM

Tags

Chapter Outline
1. Relational Model Concepts

2. Relational Model Constraints and Relational Database Schemas

3. Update Operations and Dealing with Constraint Violations

Relational Model Concepts

🔹 Informal Definitions
A relation resembles a table of values.

Each row (tuple) represents facts about a real-world entity or relationship.

Each column (attribute) has a name indicating the type of data it holds.

Each row has a value, or as set of values that are unique for each row → key

Artificial key / Surrogate key: sequential numbers assigned as keys to identify

rows in the relation

🔹 Example
SSN Name Major

123-45-6789 John Doe CS

987-65-4321 Jane Smith EE

SSN is a key that uniquely identifies each row.

Formal Definitions

Lecture 7 | DB Constraints 1
 🔹 Schema
A relation schema is denoted by:
R(A1, A2, ..., An)

where:

R = name of relation

A1...An = attribute names

Example:
CUSTOMER(Cust-id, Cust-name, Address, Phone#)

Cust-id: 6-digit number (Domain = 6-digit integers)

🔹 Tuple
A tuple is an ordered set of values, enclosed in angle brackets:

Example:
<632895, "John Smith", "101 Main St.", "(404) 894-2000">

This is a 4-tuple in the CUSTOMER relation.

Each value is derived from an appropriate domain

A relation is a set of such tuples

🔹 Domain
A domain is the set of valid values for an attribute.

Example:

USA_phone_numbers : All valid 10-digit U.S. numbers

Format: (ddd)ddd-dddd

Same domain can be used differently based on attribute name:

e.g., Date → used for both "Invoice-date" and "Payment-date"

Relational Database Schema

A relational database schema is a set of relation schemas:

Lecture 7 | DB Constraints 2
 Denoted as:
S = {R1, R2, ..., Rn}

Example:

COMPANY database with 6 relations: EMPLOYEE, DEPARTMENT, PROJECT,

etc.

Summary

Characteristics of Relations

Lecture 7 | DB Constraints 3
 Ordering of tuples: Not important in relational theory.

Ordering of attributes: Considered ordered in both schema and tuples.

Relational Integrity Constraints

🔹 Overview
Constraints are rules that must hold for valid relation states:

1. Key Constraints

2. Entity Integrity

3. Referential Integrity

4. Domain Constraints (Implicit)

Key Constraints
A primary key is chosen from candidate keys to identify tuples uniquely.

Underlined in schema notation.

Example:
CAR(State, Reg#, SerialNo, Make, Model, Year)

Candidate Keys: SerialNo, (State, Reg#)

Chosen Primary Key: SerialNo

✅ Tip: Prefer smallest candidate key (if practical).

Entity Integrity
Rule: The primary key of a relation must not be NULL.

If PK = (A, B), neither A nor B can be NULL.

Justification:

PK values are used to uniquely identify tuples.

Lecture 7 | DB Constraints 4
 Referential Integrity
🔹 Concept
A foreign key (FK) in one relation refers to the primary key (PK) in another.

Relations:

Referencing relation: R1

Referenced relation: R2

🔹 Rule
For each tuple in R1, the value of FK must be:

1. A value of PK in some tuple of R2

2. NULL, if allowed and not part of R1's primary key

🔹 Example
Employee Dept_No (FK)

Ahmed 3

Dept_No (PK) Dept_Name

3 Marketing

Displaying Constraints
Primary keys: Underlined in schema

Foreign keys: Shown as arrows pointing to referenced relation

Example

Lecture 7 | DB Constraints 5
 Other Types of Constraints
🔹 Semantic Integrity Constraints
Constraints based on business logic

Example: “Max hours per employee per week = 56 hrs”

May require special constraint specification languages

Update Operations and Constraint Violations

Whenever the database is changed, a new state arises

Basic operation for changing the database:

Update Types
1. INSERT - add a tuple

2. DELETE - remove a tuple

3. MODIFY - change one or more attribute values

❗ Integrity constraints must not be violated during updates.

Updates may propagate to cause other updates
automatically → may maintain integrity constraints.

Lecture 7 | DB Constraints 6
 Handling Constraint Violations
🔹 General Strategies
RESTRICT/REJECT: Cancel the operation

CASCADE: Propagate changes

SET NULL: Set FK fields to NULL

User-defined handler: Execute corrective actions

Violations by Operation
🔹 INSERT Violations
Domain Constraint: Attribute value not in domain

Key Constraint: Duplicate primary key

Entity Integrity: Null primary key

Referential Integrity: FK references a non-existent PK

🔹 DELETE Violations
Only affects referential integrity: if the PK of the deleted tuple is a reference
to other tuples

Solutions:

RESTRICT: Prevent deletion

CASCADE: Delete dependent tuples

SET NULL: Nullify dependent FKs

🧾 Example:
Employee Table:

SSN Name DNO

112233 Ahmed 3

Lecture 7 | DB Constraints 7
 Department Table:

DNO Name

3 Marketing

Deleting Dept 3 would violate FK in Employee unless handled.

🔹 UPDATE Violations
Domain/NOT NULL: Invalid value or null assignment

PK Change: Similar to DELETE + INSERT

FK Change: May break referential integrity

Ordinary Attribute: Only domain constraints apply

Summary
Covered Relational Model Concepts

Tables → Relations

Rows → Tuples

Columns → Attributes

Data types → Domains

Discussed Constraints:

Domain, Key, Entity, Referential, Semantic

Explained Update Operations:

INSERT, DELETE, MODIFY

How to handle constraint violations

Lecture 7 | DB Constraints 8