1.

The Relational Data Model and Relational Database Constraints

The relational model is the most widely used data model for databases. It organizes data into two-
dimensional tables, known as relations.
1.1 Relational Model Concepts
 Relation (Table): Think of a relation simply as a table. Each table has a unique name (e.g.,
EMPLOYEE, DEPARTMENT).
 Tuple (Row): Each row in the table represents a single record or instance of the entity the
table describes. For example, in an EMPLOYEE table, each row represents one employee.
 Attribute (Column): Each column in the table represents a characteristic or property of the
entity. For example, Employee_ID, Name, Department_ID are attributes in an EMPLOYEE
table.
 Domain: This defines the set of all possible values for an attribute. For example, the domain
for Employee_ID might be a set of 9-digit numbers, or for Gender it might be {'M', 'F'}.
 Relation Schema: This is the structure of the table, defined by its name and the list of its
attributes, along with their domains. For instance, EMPLOYEE (Employee_ID, Name,
Department_ID).
 Relation State: This refers to the actual data (the set of tuples) in a table at a particular
moment in time. As data is added, deleted, or updated, the relation state changes.
1.2 Relational Model Constraints and Relational Database Schemas
Constraints are rules that ensure the data in the database remains consistent and valid.
 Key Constraints:
o Superkey: One or more attributes that uniquely identify a tuple in a relation. Any set
of attributes that includes a superkey is also a superkey.
o Candidate Key: A minimal superkey; if you remove any attribute from a candidate
key, it can no longer uniquely identify tuples. A relation can have multiple candidate
keys.
o Primary Key: One of the candidate keys chosen by the database designer to uniquely
identify each tuple. It's the most important key and cannot have NULL values.
o Foreign Key: An attribute (or set of attributes) in one table (the referencing table)
whose values must match the primary key values in another table (the referenced
table) or be NULL. This establishes a link or relationship between two tables. For
example, Department_ID in the EMPLOYEE table might be a foreign key
referencing the Department_ID (primary key) in the DEPARTMENT table.
 Integrity Constraints:
o Entity Integrity Constraint: Ensures that the primary key of a relation cannot have
NULL values. This guarantees that every tuple in the table can be uniquely identified.
o Referential Integrity Constraint: This rule ensures that if a foreign key value exists
in the referencing table, it must correspond to an existing primary key value in the
referenced table. It prevents "dangling references" where a record refers to something
that doesn't exist.
1.3 Update Operations, Transactions, and Dealing with Constraint Violations
Database operations change the data in the tables. When performing these operations, the database
management system (DBMS) must ensure that all defined constraints are maintained.
 Update Operations:
o Insert: Adding new tuples (rows) to a relation. The DBMS checks if the new tuple
violates any key, entity, or referential integrity constraints. For instance, if you try to
insert an employee with a Department_ID that doesn't exist in the DEPARTMENT
table, it would be rejected.
o Delete: Removing tuples from a relation. If you delete a tuple that is referenced by
foreign keys in other tables, it can violate referential integrity. The DBMS has
strategies to handle this:
 RESTRICT (default): Prevents the deletion if there are referencing tuples.
 CASCADE: Deletes all referencing tuples along with the original tuple.
 SET NULL: Sets the foreign key values in referencing tuples to NULL.
 SET DEFAULT: Sets the foreign key values in referencing tuples to a
predefined default value.
o Update/Modify: Changing attribute values in existing tuples. Similar to insertions
and deletions, changes to primary or foreign keys trigger constraint checks.
 Transactions: A transaction is a sequence of database operations that is treated as a single
logical unit of work. It must either complete entirely (commit) or have no effect at all
(rollback). This ensures database consistency. For example, transferring money from one
account to another involves deducting from one and adding to another; both must succeed for
the transaction to be valid.
 Dealing with Constraint Violations: If an update operation attempts to violate a constraint,
the DBMS will typically:
o Reject the operation: The most common approach, preventing the invalid data from
entering the database.
o Cascade the changes: As seen with delete/update operations, changes in one table
automatically propagate to related tables.
o Set to NULL/Default: Replace the violating value with NULL or a default value.

2. Formal Relational Languages: Relational Algebra

Formal relational languages provide a theoretical foundation for querying and manipulating data in
relational databases.
2.1 Unary Relational Operations: SELECT and PROJECT
These operations work on a single relation (table).
 SELECT (σ - sigma): This operation filters rows (tuples) from a table based on a given
condition. It's like picking out specific rows from a spreadsheet.
o Notation: σ<condition>(Relation)
 o Example: σSalary > 50000(EMPLOYEE) would return all rows (employees) where
the salary is greater than 50,000.
o Analogy to SQL: The WHERE clause in an SQL SELECT statement.

 PROJECT (π - pi): This operation selects specific columns (attributes) from a table and
removes duplicate rows from the result. It's like choosing which columns to display and then
making sure no two resulting rows are identical.
o Notation: π<attribute list>(Relation)

o Example: πName, Department_ID(EMPLOYEE) would return a table with only the

Name and Department_ID columns for all employees. If multiple employees have the
same name and department ID, only one instance would appear.
o Analogy to SQL: The SELECT clause in an SQL SELECT statement (especially
with DISTINCT keyword for duplicate elimination).
2.2 Relational Algebra Operations from Set Theory
These operations are based on mathematical set theory and require the relations to be "union
compatible," meaning they must have the same number of attributes and corresponding attributes
must have the same data types.

 UNION (∪): Combines all tuples from two union-compatible relations, removing duplicates.

(with the same columns), CURRENT_EMPLOYEES ∪ FORMER_EMPLOYEES

o Example: If you have a table of current employees and a table of former employees

would give you a list of all employees, past and present.

 INTERSECTION (∩): Returns only the tuples that are present in both union-compatible
relations.
o Example: SALES_DEPT_EMPLOYEES ∩ MARKETING_DEPT_EMPLOYEES
would show employees who work in both the sales and marketing departments.
 MINUS (–): Returns the tuples that are in the first relation but not in the second (union-
compatible) relation.
o Example: ALL_EMPLOYEES – FORMER_EMPLOYEES would give you only the
current employees.
 CARTESIAN PRODUCT (×): Combines every tuple from the first relation with every tuple
from the second relation. If Relation1 has m tuples and Relation2 has n tuples, their Cartesian
product will have m * n tuples. This operation is often the first step in a join operation.
o Notation: Relation1 × Relation2

o Example: If EMPLOYEE has 100 rows and DEPARTMENT has 10 rows,

EMPLOYEE × DEPARTMENT would result in 1000 rows, pairing every employee
with every department.
2.3 Binary Relational Operations: JOIN and DIVISION
These operations work on two relations.

 JOIN (⋈): This is a very powerful operation that combines tuples from two relations based on
a common attribute or a join condition. It's used to link related data from different tables.
 o Types of JOINs:

 Theta Join: The most general form. It combines tuples from two relations if
they satisfy a specified condition (theta, represented by θ, can be any
comparison operator like =, <, >).

 Notation: Relation1 ⋈<condition> Relation2

 Example: EMPLOYEE ⋈Employee.Department_ID =

DEPARTMENT.Department_ID DEPARTMENT
 Equijoin: A special type of Theta Join where the condition only uses the
equality operator (=). The result will typically have duplicate columns if the
join attributes have the same name.

 Natural Join (⋈): A special type of Equijoin. It automatically joins relations

on all attributes that have the same name and data type in both relations, and
then it removes duplicate columns from the result. This makes the result
cleaner.
 Example: If EMPLOYEE has Dname and DEPARTMENT has
Dname, a natural join would implicitly join on Dname and only show
Dname once in the result.
o Analogy to SQL: The JOIN clause in SQL queries (e.g., INNER JOIN, LEFT JOIN,
RIGHT JOIN, FULL JOIN).
 DIVISION (÷): This is a less common but very useful operation, typically used for queries
that involve "all" or "every" conditions. It finds tuples in one relation that are associated with
all tuples in another specific relation.
o Notation: Relation1(R) ÷ Relation2(S) where S is a subset of the attributes of R.

o Analogy: Imagine a WORKS_ON(Employee_ID, Project_ID) table and a

IMPORTANT_PROJECTS(Project_ID) table. WORKS_ON ÷
IMPORTANT_PROJECTS would give you Employee_IDs of employees who work
on all projects listed in IMPORTANT_PROJECTS.
o Implementation: While not directly available in SQL as a single operator, it can be
simulated using a combination of PROJECT, CARTESIAN PRODUCT, and MINUS
operations.
2.4 Examples of Queries in Relational Algebra
Relational Algebra operations can be combined to form complex queries. The order of operations is
crucial. Queries are expressed as a sequence of operations, where the output of one operation becomes
the input for the next.
For example, to "Find the names of employees who work on Project 'X'":
1. Select tuples from the WORKS_ON relation where Project_Name is 'X'. σProject_Name =
'X'(WORKS_ON)
2. Project the Employee_ID from the result of step 1. πEmployee_ID(σProject_Name =
'X'(WORKS_ON))
 (πEmployee_ID(σProject_Name = 'X'(WORKS_ON))) ⋈EMPLOYEE.Employee_ID =
3. Join the result of step 2 with the EMPLOYEE relation on Employee_ID.

WORKS_ON.Employee_ID EMPLOYEE

'X'(WORKS_ON))) ⋈EMPLOYEE.Employee_ID = WORKS_ON.Employee_ID

4. Project the Name from the result of step 3. πName((πEmployee_ID(σProject_Name =

EMPLOYEE)
1. Unary Relational Operations
These operations work on a single table.
1.1 SELECT (σ - sigma)
The SELECT operation filters rows (tuples) from a table that satisfy a specified condition. It
essentially picks out the relevant rows.
Concept: Imagine a filter in a spreadsheet. You're keeping only the rows that meet your criteria.
Example Table: EMPLOYEE

EmpID Name DeptID Salary

101 Alice D1 60000

102 Bob D2 45000

103 Carol D1 70000

104 David D3 55000

105 Eve D2 48000

Export to Sheets
Operation: Find employees with a Salary greater than 50000. σSalary > 50000 (EMPLOYEE)
Resulting Table:

EmpID Name DeptID Salary

101 Alice D1 60000

103 Carol D1 70000

104 David D3 55000

Export to Sheets
1.2 PROJECT (π - pi)
The PROJECT operation selects specific columns (attributes) from a table. Importantly, it also
eliminates any duplicate rows that might result from selecting only a subset of columns.
Concept: Imagine choosing only certain columns to display in a spreadsheet, then removing any
identical rows that appear after this selection.
Example Table: EMPLOYEE (same as above)
EmpID Name DeptID Salary

101 Alice D1 60000

102 Bob D2 45000

103 Carol D1 70000

104 David D3 55000

105 Eve D2 48000

Export to Sheets
Operation: Get the Name and DeptID of all employees. πName, DeptID (EMPLOYEE)
Resulting Table:

Name DeptID

Alice D1

Bob D2

Carol D1

David D3

Eve D2

Export to Sheets
Note: Even though Alice and Carol are both in DeptID D1, their names are different, so both rows are
kept. If there were two employees with the same Name and DeptID, one would be removed due to the
duplicate elimination feature of PROJECT.
2. Relational Algebra Operations from Set Theory
These operations combine tuples from two relations. For these to work, the relations must be "union
compatible" (i.e., they must have the same number of columns, and corresponding columns must have
compatible data types).

2.1 UNION (∪)

Combines all tuples from two union-compatible relations, removing duplicates.
Concept: Merging two lists of items, keeping only unique entries.
Example Tables:
STAFF_A | ID | Name | City | |----|-------|--------| | 1 | John | New York | | 2 | Mary | London | | 3 | Peter
| Paris |
STAFF_B | ID | Name | City | |----|-------|--------| | 3 | Peter | Paris | | 4 | Alice | Berlin | | 5 | Mark | New
York |

Operation: STAFF_A ∪ STAFF_B

Resulting Table: | ID | Name | City | |----|-------|--------| | 1 | John | New York | | 2 | Mary | London | | 3
| Peter | Paris | | 4 | Alice | Berlin | | 5 | Mark | New York | Note: Peter from Paris (ID 3) appears in
both tables, but only one instance is kept in the result due to set theory principles.
2.2 INTERSECTION (∩)
Returns only the tuples that are present in both union-compatible relations.
Concept: Finding items that are common to two lists.
Operation: STAFF_A ∩ STAFF_B
Resulting Table: | ID | Name | City | |----|-------|--------| | 3 | Peter | Paris |
2.3 MINUS (–) / DIFFERENCE
Returns the tuples that are in the first relation but not in the second union-compatible relation.
Concept: Finding items that are in one list but not in another.
Operation: STAFF_A – STAFF_B
Resulting Table: | ID | Name | City | |----|------|--------| | 1 | John | New York | | 2 | Mary | London |
2.4 CARTESIAN PRODUCT (×)
Combines every tuple from the first relation with every tuple from the second relation. If R1 has m
rows and R2 has n rows, the result will have m * n rows.
Concept: Pairing every item from one list with every item from another list.
Example Tables:
EMPLOYEE_ID | EmpID | Name | |-------|-------| | 101 | Alice | | 102 | Bob |
PROJECT_NAME | ProjName | |----------| | Alpha | | Beta |
Operation: EMPLOYEE_ID × PROJECT_NAME
Resulting Table: | EmpID | Name | ProjName | |-------|-------|----------| | 101 | Alice | Alpha | | 101 |
Alice | Beta | | 102 | Bob | Alpha | | 102 | Bob | Beta |
3. Binary Relational Operations
These operations involve two relations and are fundamental for combining related data.

3.1 JOIN (⋈)

The JOIN operation combines tuples from two relations based on a common attribute or a specified
join condition. It's how you link data across different tables.
Concept: Merging rows from two tables where a specified column (or set of columns) matches.
Example Tables:
EMPLOYEE | EmpID | Name | DeptID | Salary | |-------|---------|--------|--------| | 101 | Alice | D1 |
60000 | | 102 | Bob | D2 | 45000 | | 103 | Carol | D1 | 70000 |
DEPARTMENT | DeptID | DeptName | Location | |--------|------------|----------| | D1 | Sales | New York
| | D2 | Marketing | London | | D4 | Research | Paris |
EMPLOYEE ⋈ DEPARTMENT (This implies a natural join on the common attribute DeptID)
Operation (Natural Join): Find employees and their department details by matching DeptID.

Resulting Table:

EmpID Name DeptID Salary DeptName Location

101 Alice D1 60000 Sales New York

102 Bob D2 45000 Marketing London

103 Carol D1 70000 Sales New York

Export to Sheets
Note: EmpID 104 and 105 (from previous examples) and DeptID D3 are not included here because
they don't have a match in the other table in this specific example, demonstrating how an inner join
(like Natural Join) works.
3.2 DIVISION (÷)
The DIVISION operation is used for queries that ask "find X that are related to all Y." It identifies
tuples in one relation (the dividend) that are associated with every tuple in another relation (the
divisor).
Concept: Finding entities that have a complete set of relationships with another set of entities.
"Which suppliers supply all parts?"
Example Tables:
SUPPLIES (Supplier, Part) | Supplier | Part | |----------|------| | S1 | P1 | | S1 | P2 | | S1 | P3 | | S2 | P1 | |
S2 | P2 | | S3 | P1 |
REQUIRED_PARTS (Part) | Part | |------| | P1 | | P2 |
Operation: Find Suppliers who supply all parts in REQUIRED_PARTS. SUPPLIES ÷
REQUIRED_PARTS
Resulting Table: | Supplier | |----------| | S1 | | S2 |
Explanation:
 S1 supplies P1, P2, and P3. Since S1 supplies both P1 and P2 (which are in
REQUIRED_PARTS), S1 is in the result.
 S2 supplies P1 and P2. Since S2 supplies both P1 and P2, S2 is in the result.
 S3 supplies only P1. Since S3 does not supply P2 (which is required), S3 is not in the result.
While DIVISION is powerful for "all" type queries, it's typically expressed using a combination of
PROJECT, CARTESIAN PRODUCT, and MINUS operations in most SQL-based database systems,
as there isn't a direct DIVIDE BY operator.