Relational Model Basics

The Relational Model represents data in a table format using rows (tuples) and columns
(attributes).
Each table (relation) has a unique name and contains multiple attributes.

Key Components of a Relational Model

1. Tuple: Each row in a table.

2. Attributes: Column names (also called fields).
3. Domain: The set of atomic values that an attribute can take.
4. Relation Instance: A table without duplicate rows.
5. Relation Schema: Defines the structure of a relation.
6. Keys: Unique identifiers used to distinguish tuples (e.g., Primary Key).

Important Rules in a Relational Model

No Duplicate Tuples: Each row in a relation must be unique.

Atomic Values: Every attribute must hold single, indivisible values.
Sequence of Tuples Doesn't Matter: Order of rows doesn't affect the table.
Distinct Relation Names: Each relation must have a unique name.

Example Table (Student Database)

Roll No Name Branch Address

1 A IT X

2 B CSE Y

3 C ECE Z

Roll No can act as a Primary Key because it uniquely identifies students.

Each column represents an attribute, while each row is a tuple.
The domain of Roll No includes values like {1, 2, 3}.

Relational Schema & Architecture

A relational model operates under Conceptual Schema, External Schema, and Internal
Schema.
The primary key uniquely identifies rows in a table.
Introduction to Relational Algebra
Relational Algebra is a procedural query language that retrieves data through a
sequence of operations.
It operates on relations (tables) and generates a new relation as output.
These operations help in query optimization and efficient database management.

Types of Relational Operations

1. Selection (σ)

Used to filter rows based on conditions.

Example: σ(Branch = "IT")(Student) → Retrieves students from IT branch.

2. Projection (π)

Extracts specific columns from a relation.

Example: π(Name, Roll_No)(Student) → Retrieves only Name and Roll No.

3. Union (∪)
Combines tuples from two relations, removing duplicates.
∪
Example: A B → Returns all unique tuples from both A and B.

4. Set Difference (-)

Finds tuples present in one relation but not in another.

Example: A - B → Returns tuples from A that are not in B.

5. Cartesian Product (×)

Combines tuples from two relations in all possible ways.

Example: A × B → Forms a new table with every possible combination of A and B.

6. Intersection ( ∩)
Retrieves common tuples between two relations.
∩
Example: A B → Returns only tuples found in both A and B.

7. Rename (ρ)

Renames relation or attributes for better readability.

Example: ρ(NewName, OldRelation) → Changes name of relation.
What Are Constraints?
Constraints are restrictions or limitations applied to a relational database.
They help maintain data accuracy and consistency.
Example: In an online form, a phone number field may require exactly 10 digits.

Types of Relational Model Constraints

1. Domain Constraint

Defines the valid set of values for an attribute.

Example: A phone number field should only contain digits, not letters.

2. Tuple Uniqueness Constraint

Ensures that each row (tuple) in a relation is unique.

Example: A student database should not have duplicate entries for the same student.

3. Key Constraint

The Primary Key must be unique and cannot be NULL.

Example: A Student ID must be unique for each student.

4. Entity Integrity Constraint

Ensures that Primary Key values cannot be NULL.

Example: A customer ID in a banking database must always have a value.

5. Referential Integrity Constraint

Ensures that a Foreign Key in one table refers to a valid Primary Key in another table.
Example: A department ID in a student table must exist in the department table.

Example Table (Student Database)

Student ID Name Branch Department ID

101 A IT D1

102 B CSE D2

103 C ECE D3
 Student ID is a Primary Key (must be unique).
Department ID is a Foreign Key (must match an existing department).

Importance of Constraints

Prevents invalid data entry (e.g., incorrect phone numbers).

Maintains consistency across multiple tables.
Ensures data integrity in relational databases.

Introduction to Relational Calculus

Relational Calculus is a non-procedural query language used to specify what data to
retrieve rather than how to retrieve it.
Unlike Relational Algebra, which provides a step-by-step method, Relational Calculus
focuses only on the conditions for data retrieval.

Types of Relational Calculus

1. Tuple Relational Calculus (TRC)

Works with tuples (rows) in a relation.

Uses logical conditions to filter tuples.
Example Query:
∈ ∧
{T | T Student T.Age > 20}
Retrieves all students whose age is greater than 20.

2. Domain Relational Calculus (DRC)

Works with domains (columns) instead of tuples.

Uses logical conditions to filter specific attributes.
Example Query:
∃ ∈ ∧ ∧
{N | S (S Student S.Age > 20 N = S.Name)}
Retrieves names of students whose age is greater than 20.

Key Differences Between TRC & DRC

 Feature Tuple Relational Calculus Domain Relational Calculus
(TRC) (DRC)

Focus Works with tuples (rows) Works with domains

(columns)

Query Structure Uses tuple variables Uses domain variables

Output Returns entire tuples Returns specific attributes

Importance of Relational Calculus

Forms the foundation for SQL queries.

Helps in query optimization.
Useful for data retrieval in complex queries.

Introduction to Aggregate Functions

Aggregate functions perform calculations on a set of values and return a single value.
Common aggregate functions include:
SUM: Adds up all values in a column.
AVG: Calculates the average of values.
COUNT: Counts the number of rows.
MIN: Finds the smallest value.
MAX: Finds the largest value.

Detailed Explanation of Each Function

1. MAX Function

Finds the highest value in a column.

Example Query:
sql
SELECT MAX(salary) FROM employee;
Retrieves the maximum salary from the employee table.

2. MIN Function
 Finds the lowest value in a column.
Example Query:
sql
SELECT MIN(salary) FROM employee;
Retrieves the minimum salary from the employee table.

3. COUNT Function

Counts the total number of rows in a table.

Example Query:
sql
SELECT COUNT(*) FROM employee;
Returns the total number of employees.

4. SUM Function

Adds up all values in a column.

Example Query:
sql
SELECT SUM(salary) FROM employee;
Returns the total salary paid to employees.

5. AVG Function

Calculates the average value of a column.

Example Query:
sql
SELECT AVG(salary) FROM employee;
Returns the average salary of employees.

Handling NULL Values in Aggregate Functions

NULL values are ignored in calculations.

Example:
If a salary column contains {10,000, 20,000, NULL, 30,000}, the SUM function will
only add {10,000 + 20,000 + 30,000}.

Using DISTINCT with Aggregate Functions

DISTINCT ensures only unique values are considered.

Example Query:
sql
SELECT SUM(DISTINCT salary) FROM employee;
Adds up only unique salary values, ignoring duplicates.

Importance of Aggregate Functions

Helps in data analysis and reporting.

Used in complex queries with GROUP BY and HAVING clauses.
 Essential for financial calculations in databases.

What Are Joins?

Joins allow us to retrieve data from two or more tables based on a common attribute.
They are used when a single table does not contain all the required information.
Joins exist in Relational Algebra and SQL, but their syntax differs.

Example Scenario

Suppose we have two tables:

1. Employee Table (Contains employee details)
2. Department Table (Contains department details)
If we need to find the name of an employee working in the HR department, we must
combine both tables using a join.

Types of Joins

1. Cross Join

Also called Cartesian Product.

Combines every row from one table with every row from another.
Example:
sql
SELECT * FROM Employee CROSS JOIN Department;

2. Natural Join

Automatically matches common attributes between tables.

Example:
sql
SELECT * FROM Employee NATURAL JOIN Department;

3. Equi Join

Uses explicit conditions to match rows.

Example:
sql
SELECT * FROM Employee INNER JOIN Department ON Employee.EmpNo =
Department.EmpNo;

4. Outer Join
 Includes non-matching rows from one or both tables.
Types:
Left Outer Join: Includes all rows from the left table.
Right Outer Join: Includes all rows from the right table.
Full Outer Join: Includes all rows from both tables.

Key Points About Joins

Joins require a common attribute between tables.

Used in SQL queries to retrieve meaningful data.
Essential for relational database operations.

Natural Join in DBMS

Definition: A natural join is a type of database join that automatically matches columns
with the same names in both tables.
Importance: It is considered one of the most essential joins in DBMS because many exam
questions are based on it.
Concept: A join in general is a cross product plus a condition.

Example Scenario

Two tables used:

1. Employee Table (Contains Employee No, Name, Address, Dept No)
2. Department Table (Contains Dept No, Dept Name)
Task: Find the employee names who are working in a department.

Process of Natural Join

1. Identify the common attribute in both tables (Employee No).

2. Apply cross product between Employee and Department tables.
3. Apply a condition to match Employee No in both tables.
4. Filter the results to get only those employees who are allocated a department.

SQL Query for Natural Join

sql

SELECT EmployeeName

FROM Employee

NATURAL JOIN Department;

OR (equivalent detailed query)

sql

SELECT EmployeeName

FROM Employee, Department

WHERE Employee.EmployeeNo = Department.EmployeeNo;

Key Takeaways

Common attributes must have the same name in both tables for Natural Join.
If there is no common attribute, Natural Join cannot be applied.
Ravi (Employee without a department) is excluded from the final result.
The Natural Join automates the process of matching common attributes without needing
explicit conditions.

Self Join in DBMS

Definition: A self join is when a table is joined with itself, creating a relationship between
its own rows.
Purpose: Used when working with hierarchical or related data within the same table.
Example Scenario: Identifying students who are enrolled in at least two courses.

Table Structure

Study Table:
Student ID (S_ID)
Course ID (C_ID)
Since (Enrollment Date)
S_ID acts as a foreign key, referencing the student table.
C_ID refers to the course table.
Student ID and Course ID together form a composite primary key.

Query for Self Join

To find students enrolled in at least two courses:

sql

SELECT T1.S_ID

FROM Study AS T1, Study AS T2

WHERE T1.S_ID = T2.S_ID

AND T1.C_ID <> T2.C_ID;

Understanding the Query

1. Self Join Concept: The study table is treated as two different copies (T1 and T2).
2. Cross Product: Since join is a combination of cross product + condition, two copies help
form relationships.
3. Conditions:
Ensures same student (S_ID) is present in both copies.
Different courses (C_ID) are studied by the same student.
4. Result: Filters out students enrolled in at least two courses.

Key Takeaways

Self Join helps compare values within the same table.

Alias (T1 and T2) is used to differentiate the same table instances.
Output gives students who are enrolled in more than one course.

Equi Join in DBMS

Definition: An Equi Join is a type of inner join that returns results by matching rows from
two tables based on a common attribute using the equal (=) operator.
Purpose: Used when we need to retrieve data from multiple tables where a common
attribute exists.

Example Scenario

Tables Used:
1. Employee Table (Contains Employee No, Name, Address, Dept No)
2. Department Table (Contains Dept No, Dept Name, Location)
Task: Find employees working in a department where the location matches their address.

Process of Equi Join

1. Identify the common attribute in both tables (Dept No).

2. Apply cross product between Employee and Department tables.
3. Apply a condition to match Dept No in both tables.
4. Add an additional condition to match Employee Address with Department Location.

SQL Query for Equi Join

sql
SELECT EmployeeName

FROM Employee, Department

WHERE Employee.DeptNo = Department.DeptNo

AND Employee.Address = Department.Location;

Key Takeaways

Equi Join uses the equal (=) operator to match common attributes.
It is different from Natural Join, as Equi Join allows explicit conditions.
Employees whose address matches their department location are included in the result.
Ravi (Employee without a department) is excluded from the final result.

Left Outer Join in DBMS

Definition: A Left Outer Join returns matching rows from both tables and also includes
rows from the left table that do not have a match in the right table.
Concept: It is a Natural Join + extra rows from the left table.
Key Rule: If a row in the left table has no corresponding match in the right table, it
appears in the result with NULL values for columns from the right table.

Example Scenario

Tables Used:
1. Employee Table (Contains Employee No, Name, Dept No)
2. Department Table (Contains Dept No, Dept Name, Location)
Task: Find all employees and their department details, including employees who do not
belong to any department.

SQL Query for Left Outer Join

sql

SELECT Employee.EmployeeNo, Employee.Name, Department.DeptName,

Department.Location

FROM Employee

LEFT OUTER JOIN Department

ON Employee.DeptNo = Department.DeptNo;
Understanding the Query

1. Natural Join Concept: Matching rows based on Dept No appear in the result.
2. Extra Rows: Employees without a department are included, with NULL values for Dept
Name and Location.
3. Example Output:
E1 Varun → IT, Delhi (Matched)
E2 Amrit → HR, Hyderabad (Matched)
E3 Ravi → IT, Delhi (Matched)
E4 Nitin → NULL, NULL (No department)

Key Takeaways

Left Outer Join ensures all rows from the left table appear in the result.
Unmatched rows from the left table are included with NULL values.
Useful for retrieving complete data, even if some relationships are missing.

Views in SQL
Definition: A view is a virtual table based on the result of a SQL query. It does not store
data physically but retrieves it from underlying tables.
Purpose: Used to simplify complex queries, enhance security, and provide a customized
representation of data.

Types of Views

1. Simple View:
Based on a single table.
Allows DML operations (INSERT, UPDATE, DELETE).
Example:
sql
CREATE VIEW EmployeeView AS
SELECT EmployeeID, Name, Salary FROM Employee;
2. Complex View:
Based on multiple tables using joins.
May not allow DML operations.
Example:
sql
CREATE VIEW EmployeeDeptView AS
SELECT Employee.Name, Department.DeptName
FROM Employee
JOIN Department ON Employee.DeptNo = Department.DeptNo;
3. Materialized View:
 Stores query results physically.
Improves performance for frequently accessed data.
Example:
sql
CREATE MATERIALIZED VIEW SalesSummary AS
SELECT ProductID, SUM(SalesAmount) FROM Sales GROUP BY ProductID;

Key Features of Views

Security: Restricts access to specific columns or rows.

Data Abstraction: Hides complexity of underlying tables.
Performance Optimization: Reduces query execution time for complex queries.

Transaction Control Language (TCL) in SQL

Definition: TCL commands are used to manage transactions in a database.
Purpose: Ensures data integrity and consistency by controlling changes made to the
database.

TCL Commands

1. COMMIT:
Saves all changes made to the database permanently.
Example:
sql
COMMIT;
Once committed, changes cannot be undone.
2. ROLLBACK:
Reverts changes made since the last COMMIT.
Example:
sql
ROLLBACK;
Useful for undoing accidental modifications.
3. SAVEPOINT:
Creates checkpoints within a transaction.
Allows partial rollback to a specific savepoint.
Example:
sql
SAVEPOINT sp1;
Rollback to a savepoint:
sql
 ROLLBACK TO sp1;

Key Takeaways

COMMIT makes changes permanent.

ROLLBACK undoes changes before a COMMIT.
SAVEPOINT allows selective rollback.
TCL commands ensure data consistency and integrity.

Understanding the ORDER BY Clause

The ORDER BY clause in SQL is used to sort records in either ascending (ASC) or
descending (DESC) order.
By default, SQL sorts data in ascending order unless explicitly stated otherwise.

Syntax of ORDER BY

sql

SELECT column_name

FROM table_name

ORDER BY column_name [ASC | DESC];

If you do not specify ASC or DESC, the default sorting is ascending order.

Example Demonstration

1. Sorting based on Emp_ID:

2. sql
3. SELECT * FROM Employees ORDER BY Emp_ID;
This arranges employee IDs in ascending order.
4. Sorting Name column in descending order:
5. sql
6. SELECT * FROM Employees ORDER BY Name DESC;
Names will be arranged from Z to A.
7. Sorting multiple columns (Salary in ascending, Name in descending):
8. sql
9. SELECT * FROM Employees ORDER BY Salary ASC, Name DESC;
First, records are sorted based on Salary (low to high).
If salaries are the same, sorting is done by Name (Z to A).

Key Takeaways
ORDER BY helps organize records for better readability.
Sorting can be applied to one or multiple columns.
ASC (Ascending) is default, while DESC (Descending) must be explicitly defined.