UNIT 2: Relational Data Model

1. The Relational Data Model: Concepts and Formal

Definitions
The relational model represents a database as a collection of relations. To understand this
model with the rigor required for database design, we must move beyond the "table" analogy
and define it through its mathematical foundations.

1.1 Formal Definition of a Relation

A Relation Schema, denoted by R(A_1, A_2, \dots, A_n), is made up of a relation name R
and a list of attributes A_1, A_2, \dots, A_n. Each attribute A_i is the name of a role played
by some domain D in the relation schema R.

A Relation State (or instance), denoted by r(R), is a mathematical set of n-tuples r = \{t_1,
t_2, \dots, t_m\}. Formally, a relation r(R) is a subset of the Cartesian product of the
domains that define its attributes: r(R) \subseteq (dom(A_1) \times dom(A_2) \times \dots \
times dom(A_n)) This signifies that each tuple is an ordered list of n values, where each
value v_i is an element of dom(A_i) [i, Section 5.1].

1.2 Key Terminology

Term Formal Definition

A named column in a relation representing a specific role of a domain (e.g.,
Attribute
Name, Major).
A set of atomic values (indivisible in the model). The physical realization of
Domain
a domain is a Data Type (e.g., INTEGER, VARCHAR).
Tuple An ordered set of values representing a single record or row in the relation.
Relation The logical description of the relation, including its name and attributes,
Schema denoted as R(A_1, \dots, A_n).
Degree The number of attributes n in a relation schema [i].

1.3 Illustration: The UNIVERSITY Database

Using the STUDENT relation from Figure 1.2, we observe the following:

 Relation Schema: STUDENT(Name, Student_number, Class, Major)

 Attributes: A_1:Name, A_2:Student\_number, A_3:Class, A_4:Major.
 Tuple Example: t = \langle \text{'Smith', 17, 1, 'CS'} \rangle.
 Domain Constraints: The domain for Class is logically defined as \{1, 2, 3, 4, 5\},
where each integer maps to a specific level (Freshman, Sophomore, etc.) [i, Section
1.2].

--------------------------------------------------------------------------------
2. Schema-Instance Distinction and Database States
A fundamental principle of the database approach is the separation between the structural
definition and the data contents.

2.1 Intension vs. Extension

 Intension (Database Schema): The description of the database, specified during

design. It is relatively static and represents the "meta-data" stored in the system
catalog [i, Section 2.1].
 Extension (Database State): The actual data stored at a specific moment t, also
called a snapshot. Unlike the intension, the extension is highly volatile, changing
with every INSERT, DELETE, or UPDATE operation.

2.2 The Lifecycle of a Database State

1. Empty State: The database structure is defined (Intension), but no data (Extension)
exists.
2. Initial State: The state when the database is first populated with data.
3. Valid State: A state that satisfies all structural and integrity constraints. The DBMS
must ensure that the transition from state S_1 to S_2 always results in a valid state.

2.3 Schema Evolution

While the schema is intended to be stable, Schema Evolution occurs when application
requirements change, requiring structural modifications (e.g., adding a Birth_date attribute
to STUDENT). This is distinct from state changes, as it alters the Intension itself [i, Section
2.1.2].

--------------------------------------------------------------------------------

3. Relational Integrity Constraints

Integrity constraints are rules derived from the "miniworld" that the DBMS must enforce to
ensure data consistency.

3.1 Key Constraints: Super, Candidate, and Primary Keys

 Super Key (SK): A set of attributes S such that no two distinct tuples in any valid
state r(R) will have t_1[S] = t_2[S].
 Candidate Key (CK): A minimal Super Key. A super key is a candidate key if
removing any attribute from it destroys the uniqueness property.
o Example: In STUDENT, the set {Student_number, Name} is a Super Key, but
it is not a Candidate Key because the subset {Student_number} is sufficient
to identify tuples.
 Primary Key (PK): The specific candidate key designated by the designer to
uniquely identify tuples and serve as the primary access path [i, Section 5.2.2].
3.2 Entity Integrity Constraint

The formal rule states: No primary key value can be null. Since the PK is used to identify
individual tuples, a null value would render the tuple unidentifiable and the relation logically
inconsistent.

3.3 Referential Integrity and Foreign Keys

This constraint maintains consistency between relations by ensuring that a value in a Foreign
Key (FK) attribute in relation R_1 matches a Primary Key value in a referenced relation
R_2.

 Nullability: An FK can be null unless it is part of its own relation's primary key.
 Update Behaviors: When a referenced PK is updated or deleted, the DBMS can:
o RESTRICT: Reject the operation.
o CASCADE: Propagate the change (e.g., deleting a student deletes all their
grade reports).
o SET NULL / SET DEFAULT: Modify the FK to null or a default value [i,
Section 5.3].

--------------------------------------------------------------------------------

4. Relational Algebra Part I: Unary Operations and Set

Theory
Relational Algebra is a procedural formal language used to specify retrievals by applying
operators to relations.

4.1 Unary Operations

 SELECT (\sigma): Acts as a horizontal filter, retrieving tuples that satisfy a

selection condition.
o Syntax: \sigma_{\langle condition \rangle}(R)
 PROJECT (\pi): Acts as a vertical partition, selecting specific attributes and
automatically eliminating duplicates to maintain set properties [i, Section 8.1].
o Syntax: \pi_{\langle attribute\_list \rangle}(R)

4.2 Set Theory Operations and Type Compatibility

To apply UNION (\cup), INTERSECTION (\cap), or SET DIFFERENCE (-), the

relations must be Type Compatible (Union Compatible). This requires:

1. Both relations have the same number of attributes (Degree).

2. The domains of corresponding attributes are identical (dom(A_i) = dom(B_i)).
3. The attributes are in the same order [i, Section 8.2].

Example Query: Unary Operations Retrieve the names of all 'CS' majors: \pi_{Name}(\
sigma_{Major='CS'}(STUDENT))
--------------------------------------------------------------------------------

5. Relational Algebra Part II: Binary Operations and

Division
5.1 JOIN Operations

Join operations combine related tuples from two relations.

 Theta Join (\bowtie_{\theta}): The most general join, where \theta is any
comparison operator (=, <, >, \dots).
 Equijoin: A subset of Theta Join where the condition \theta is strictly the equality
operator (=).
 Natural Join (*): An equijoin on all attributes with common names, which then
removes the redundant duplicate columns [i, Section 8.3].

5.2 Inner vs. Outer Joins

Outer joins are used when one wishes to preserve tuples that do not have a match in the join
condition.

SQL
Join Type Formal Purpose
Equivalent
Inner Join Includes only tuples that meet the join condition. INNER JOIN
Left Outer Keeps all tuples from the left relation, padding right attributes LEFT JOIN
Join with NULL if no match exists.
Right Outer Keeps all tuples from the right relation, padding left attributes RIGHT JOIN
Join with NULL.
Full Outer FULL OUTER
Keeps all tuples from both relations. JOIN
Join

5.3 DIVISION Operation

The DIVISION operator (R \div S) is used for "all" or "every" queries. For relations R(Z)
and S(X), where X \subset Z, let Y = Z - X. The result T(Y) includes a tuple t if and only if
for every tuple s in S, the concatenated tuple ts is in R [i, Section 8.3].

--------------------------------------------------------------------------------

6. Introduction to Relational Calculus

Unlike Algebra, Relational Calculus is non-procedural (declarative); it describes what
result is desired without specifying the operations to retrieve it.

6.1 Tuple Relational Calculus (TRC)

TRC variables range over tuples. An expression is formatted as \{t \mid P(t)\}, where t is a
tuple variable and P(t) is a formula.

 Example: Find the names of students in the 'CS' major: \{[Link] \mid STUDENT(t) \
wedge [Link] = 'CS'\}
 Variables: Variables are Bound if they are within the Scope of a quantifier (\exists or
\forall); otherwise, they are Free Variables [i, Section 8.6].

6.2 Domain Relational Calculus (DRC)

DRC variables range over individual domains (attributes) rather than entire tuples. For
example, instead of tuple t, we use variables x_1, x_2, \dots, x_n for each attribute in the
schema [i, Section 8.7].
Unit 2: Data Modeling and Advanced SQL
Mastery
1. Foundations and History of SQL

Structured Query Language (SQL) is the comprehensive standard language utilized for the
definition and manipulation of relational databases [39]. It is pedagogically important to
recognize that SQL’s data manipulation capabilities are grounded in the formal mathematical
foundations of Relational Algebra, as specified in the curriculum syllabus [Syllabus].
Database languages are technically categorized into three primary functional domains: Data
Definition Language (DDL), Data Manipulation Language (DML), and View Definition
Language (VDL) [39]. DDL is employed by designers to define the conceptual and internal
schemas of the database system [39]. VDL is used to specify user-oriented views and their
relative mappings to the conceptual schema [39]. DML provides the operations for the
retrieval, insertion, deletion, and modification of data records [39]. SQL represents a robust
integration of these distinct language types into a single cohesive framework [39].

SQL Command Examples

Language Category Primary Function
(Ref. Section 6)
Data Definition Defining and modifying database CREATE TABLE, ALTER
Language (DDL) schemas and structures [39]. TABLE, DROP TABLE [232].
View Definition Specifying user-specific views and
CREATE VIEW [228].
Language (VDL) conceptual mappings [39].
Data Manipulation Handling data retrieval, insertion, SELECT, INSERT, UPDATE,
Language (DML) and state updates [39]. DELETE [198].

2. SQL Data Definition and Schema Concepts

The "defining" process of a database involves the precise specification of data types,
structures, and constraints for the data to be stored [6]. This descriptive information is
maintained by the DBMS in a centralized repository known as the system catalog or
dictionary [6, 10]. The information stored within this catalog is referred to as meta-data,
which allows the DBMS to understand the structure of the primary database, including file
formats and data item types [10, 42]. To define records accurately, the SQL standard requires
specific base data types for attributes as listed in the technical documentation [179]. These
include Numeric types such as INT, FLOAT, and DECIMAL, String types including CHAR(n) and
VARCHAR(n), and temporal types such as DATE [179].

 Schema: The stable, overall description of the database structure that is specified during the
initial design phase [34].
 Catalog: A centralized system repository where the DBMS stores the comprehensive
description of the database structure and constraints [10].
 Meta-data: The specific data stored in the catalog that describes the structure, type, and
storage format of the primary database records [10, 42].

Generalized CREATE TABLE Syntax Template

CREATE TABLE[Table_Name] ( [Column_Name] [Data_Type] [Optional_Constraint],
[Column_Name] [Data_Type] [Optional_Constraint], PRIMARY KEY ([Column_Name]),
FOREIGN KEY ([Column_Name]) REFERENCES Other_Table [ON DELETE Action] [ON
UPDATE Action] ); [179, 184]

3. Integrity Constraints and Update Behaviors

Integrity constraints are essential "business rules" derived from the semantics of the
miniworld that the DBMS must automatically enforce to ensure data accuracy [21]. Primary
Keys provide a unique identifier for each record and cannot contain null values, while the
UNIQUE constraint prevents duplicate entries in specified non-primary columns [184]. NOT
NULL constraints are applied to ensure that critical data fields are never left empty during data
entry [184]. Referential integrity constraints require that a record in a referencing table must
relate to an existing record in a referenced table, typically enforced via Foreign Keys [21].
These referential triggered actions are explicitly defined within the FOREIGN KEY clause of
the CREATE TABLE or ALTER TABLE statements to maintain consistency when a referenced
primary key is modified or deleted [183].

Constraint Reference Guide

 Primary Key: A unique identifier for each record in a table that maintains entity integrity by
prohibiting null values [184].
 Foreign Key: A column or set of columns that establishes a link between data in two tables
by referencing the primary key of the parent table [184].
 Referential Triggered Actions:
o CASCADE: Automatically propagates the deletion or update of a referenced primary
key to all matching foreign key records in referencing tables [183, 195].
o SET NULL: Resets the foreign key values in the referencing records to NULL when the
associated primary key record is deleted or modified [183, 195].
o SET DEFAULT: Resets the foreign key values in referencing records to a predefined
default value if the referenced primary key is changed [183, 195].

4. Data Manipulation Operations (DML)

DML operations are the primary functions used to change the state of the database by adding,
removing, or modifying stored tuples [6]. These operations may handle single or multiple
tuples depending on the selection criteria provided in the statement [198].

INSERT [198]

 Required Parameters: The target table name and the specific values to be associated with
each column in the new tuple [198].
 Potential Constraint Violations: Primary key violations occur if the ID already exists, and a
violation occurs if the number of values supplied does not match the number of columns
defined for the table [165, 198].

DELETE [198]

 Required Parameters: The target table name and a selection condition to identify the
specific tuple or tuples to be removed from the database [198].
  Potential Constraint Violations: Referential integrity violations occur if the tuple being
deleted is currently referenced by a foreign key in another table and no triggered action is
defined [165].

UPDATE [198]

 Required Parameters: The table name, the columns to be modified, the new values, and a
condition to select the target tuples for modification [198].
 Potential Constraint Violations: Referential integrity violations can be triggered if an update
to a Primary Key is attempted while that key is being referenced by other tables [165].

5. Advanced Retrieval: Nested Queries and Subqueries

Nested queries allow for complex data retrieval by nesting one query within the WHERE or
HAVING clause of another [207]. Comparison operators such as IN, ANY, ALL, and EXISTS are
utilized to evaluate the results of the inner subquery against the outer query [207].

Concept Comparison: Subquery Execution Logic [268, 283]

 Uncorrelated Subqueries: These function independently of the outer query; the inner query
is executed exactly once, and its result is then used by the outer query for its own evaluation
[268].
 Correlated Subqueries: These are functionally dependent on the outer query because they
refer to specific attributes of the outer tuple currently being processed [268]. Consequently,
the inner query must be re-evaluated for every individual tuple processed by the outer
query loop [283].

6. Aggregation, Grouping, and Summarization

SQL provides five standard aggregate functions to summarize data: COUNT, SUM, MAX, MIN,
and AVG [293]. To perform these calculations on specific subsets of data, the GROUP BY clause
is used to partition the result set into sets based on attribute values [308]. The HAVING clause
is subsequently employed to filter these partitions by applying conditions to the aggregate
results [308]. It is a critical distinction that the WHERE clause filters individual tuples, whereas
the HAVING clause filters entire groups [308].

Syntax Flow: Logical Execution Order [187, 308]

1. SELECT: Specifies the attributes or aggregate results to be projected in the final result set
[187].
2. FROM: Identifies the source tables involved in the query [187].
3. WHERE: Filters individual tuples based on specific selection criteria before any grouping
operations occur [187, 308].
4. GROUP BY: Partitions the remaining tuples into sets or groups based on shared attribute
values [308].
5. HAVING: Filters the resulting groups by applying conditions to the aggregate function
outcomes of each group [308].