DBMS Module-2

Theoretical Foundations: Relational Algebra & Calculus

These are the theoretical languages used to formally define the operations on a relational database. They are the mathematical basis for languages like SQL.

Relational Algebra

Relational Algebra is a procedural query language. This means it tells the system how to get the result by specifying a sequence of operations. It consists of a set of fundamental operations that take one or two relations (tables) as input and produce a new relation as output.

Operation	Symbol	Description	Analogy/Example
Selection	$\sigma$ (sigma)	Picks out a subset of rows (tuples) from a table that satisfy a given condition.	Filtering a list of students to find only those with a GPA > 3.5.
Projection	$\pi$ (pi)	Picks out a subset of columns (attributes) from a table. Eliminates duplicate rows in the result.	Selecting only the 'Name' and 'Email' columns from the 'Students' table.
Union	$\cup$	Combines the rows from two tables, provided they have the same number of columns and compatible data types (union-compatible). Duplicates are eliminated.	Merging a list of 'Current Students' and 'Graduated Students' into one master list.
Set Difference	$-$	Returns the rows present in the first table but not in the second (must be union-compatible).	Finding students who are enrolled in the 'Math' course but not the 'Physics' course.
Cartesian Product	$\times$	Combines every row from the first table with every row from the second table. The result is a table with $R_1$.rows $\times$ $R_2$.rows and $R_1$.cols + $R_2$.cols.	Combining a list of students with a list of all available courses to see all possible student-course pairings (often an intermediate step).
Rename	$\rho$ (rho)	Gives a new name to a relation or an attribute (column).	Changing the temporary name of a query result.
Join	$\bowtie$	A derived operation. It's essentially a Cartesian Product followed by a Selection condition. The Natural Join is the most common, joining tables on all common attributes.	Linking a 'Students' table and an 'Enrollment' table using the common 'StudentID' column to see which courses each student is taking.

Tuple and Domain Relational Calculus

Unlike Relational Algebra, Relational Calculus is a non-procedural query language. This means it describes what to retrieve without specifying how to retrieve it. It's based on mathematical logic.

Tuple Relational Calculus (TRC)

• Focuses on tuples (rows).

• A query defines a set of tuples for which a specific condition is true.

• Format: $\{t \mid P(t)\}$ - "The set of all tuples t such that the predicate (condition) $P(t)$ is true."

• Example: $\{t \mid t \in \text{Student} \wedge t.\text{GPA} > 3.5\}$

  • Meaning: Find the set of all tuples (t) such that t is in the 'Student' relation AND the GPA attribute of t is greater than 3.5.

Domain Relational Calculus (DRC)

• Focuses on domain variables (column values).

• A query defines a set of values from the domain of attributes for which a condition holds.

• Format: $\{x_1, x_2, \ldots, x_n \mid P(x_1, x_2, \ldots, x_n)\}$ - "The set of $x_1$ through $x_n$ such that $P$ is true."

• Example: $\{N, I \mid \exists G (\text{Student}(N, I, G) \wedge G > 3.5)\}$

  • Meaning: Find the Name (N) and ID (I) such that THERE EXISTS a GPA (G) where the tuple (N, I, G) is in the 'Student' relation AND $G > 3.5$.

Theoretical Foundations: Relational Algebra & Calculus

These are the theoretical languages used to formally define the operations on a relational database. They are the mathematical basis for languages like SQL.

Relational Algebra

Relational Algebra is a procedural query language. This means it tells the system how to get the result by specifying a sequence of operations. It consists of a set of fundamental operations that take one or two relations (tables) as input and produce a new relation as output.

Operation	Symbol	Description	Analogy/Example
Selection	$\sigma$ (sigma)	Picks out a subset of rows (tuples) from a table that satisfy a given condition.	Filtering a list of students to find only those with a GPA > 3.5.
Projection	$\pi$ (pi)	Picks out a subset of columns (attributes) from a table. Eliminates duplicate rows in the result.	Selecting only the 'Name' and 'Email' columns from the 'Students' table.
Union	$\cup$	Combines the rows from two tables, provided they have the same number of columns and compatible data types (union-compatible). Duplicates are eliminated.	Merging a list of 'Current Students' and 'Graduated Students' into one master list.
Set Difference	$-$	Returns the rows present in the first table but not in the second (must be union-compatible).	Finding students who are enrolled in the 'Math' course but not the 'Physics' course.
Cartesian Product	$\times$	Combines every row from the first table with every row from the second table. The result is a table with $R_1$.rows $\times$ $R_2$.rows and $R_1$.cols + $R_2$.cols.	Combining a list of students with a list of all available courses to see all possible student-course pairings (often an intermediate step).
Rename	$\rho$ (rho)	Gives a new name to a relation or an attribute (column).	Changing the temporary name of a query result.
Join	$\bowtie$	A derived operation. It's essentially a Cartesian Product followed by a Selection condition. The Natural Join is the most common, joining tables on all common attributes.	Linking a 'Students' table and an 'Enrollment' table using the common 'StudentID' column to see which courses each student is taking.

Tuple and Domain Relational Calculus

Unlike Relational Algebra, Relational Calculus is a non-procedural query language. This means it describes what to retrieve without specifying how to retrieve it. It's based on mathematical logic.

Tuple Relational Calculus (TRC)

• Focuses on tuples (rows).

• A query defines a set of tuples for which a specific condition is true.

• Format: $\{t \mid P(t)\}$ - "The set of all tuples t such that the predicate (condition) $P(t)$ is true."

• Example: $\{t \mid t \in \text{Student} \wedge t.\text{GPA} > 3.5\}$

  • Meaning: Find the set of all tuples (t) such that t is in the 'Student' relation AND the GPA attribute of t is greater than 3.5.

Domain Relational Calculus (DRC)

• Focuses on domain variables (column values).

• A query defines a set of values from the domain of attributes for which a condition holds.

• Format: $\{x_1, x_2, \ldots, x_n \mid P(x_1, x_2, \ldots, x_n)\}$ - "The set of $x_1$ through $x_n$ such that $P$ is true."

• Example: $\{N, I \mid \exists G (\text{Student}(N, I, G) \wedge G > 3.5)\}$

  • Meaning: Find the Name (N) and ID (I) such that THERE EXISTS a GPA (G) where the tuple (N, I, G) is in the 'Student' relation AND $G > 3.5$.

2. SQL: The Practical Language

SQL (Structured Query Language) is the most widely used language for managing and querying relational databases.

SQL3 / SQL:1999

SQL3, officially known as SQL:1999 (or simply the SQL-99 standard), was a major revision of SQL. Its importance lies in bridging the gap between theoretical relational databases and the practical needs of complex applications.

Key additions in SQL3 include:

1. Object-Relational Features: It introduced concepts from Object-Oriented Programming (OOP) into SQL, allowing for complex data types and structures.

   • User-Defined Types (UDTs): You can define your own data types, e.g., a Location type with latitude and longitude.

   • Row Types and References: Allows a column to contain a structured "row" of values and pointers/references to other objects/rows.

2. Triggers and Assertions: Enhanced control over database integrity and behavior.

3. Recursion (using WITH RECURSIVE): Essential for querying hierarchical data (e.g., organizational charts or Bill-of-Materials).

DDL and DML Constructs

SQL commands are typically categorized into these major groups:

Data Definition Language (DDL)

DDL commands are used to define, modify, and drop the structure (schema) of the database objects. These commands affect the metadata (data about the data).

Command	Purpose	Example
CREATE	Used to create database objects (tables, indexes, views, etc.).	CREATE TABLE Employees (EmpID INT PRIMARY KEY, Name VARCHAR(100));
ALTER	Used to modify the structure of an existing object.	ALTER TABLE Employees ADD Salary DECIMAL(10, 2);
DROP	Used to delete an entire object (and all its data).	DROP TABLE Employees;
TRUNCATE	Used to remove all rows from a table, but keeps the table structure. It's faster than DELETE.	TRUNCATE TABLE Employees;

Data Manipulation Language (DML)

DML commands are used to access and manipulate the data within the database objects.

Command	Purpose	Example
SELECT	Used to retrieve data from the database. (The most common DML command)	SELECT Name, Salary FROM Employees WHERE Salary > 50000;
INSERT	Used to add new rows of data into a table.	INSERT INTO Employees (EmpID, Name, Salary) VALUES (1, 'Alice', 60000.00);
UPDATE	Used to modify existing data within a table.	UPDATE Employees SET Salary = 65000.00 WHERE Name = 'Alice';
DELETE	Used to remove rows of data from a table.	DELETE FROM Employees WHERE Name = 'Alice';

3. Database Management Systems (DBMS)

A DBMS is the software that allows users to define, create, maintain, and control access to the database. They fall into two main categories:

Open Source and Commercial DBMS

Open Source DBMS

These systems have their source code freely available. They are often cost-effective and highly community-supported.

DBMS	Key Characteristics	Common Use Case
MySQL	The world's most popular open-source RDBMS. Known for speed, reliability, and ease of use. It's now owned by Oracle.	Web applications (often part of the LAMP stack: Linux, Apache, MySQL, PHP/Python/Perl).
PostgreSQL	Often referred to as the most advanced open-source RDBMS. Highly standard-compliant and supports complex SQL features (Object-Relational features).	Enterprise-level applications requiring data integrity and complex queries.

Commercial DBMS

These systems are developed and sold by vendors, requiring a license fee. They are known for robustness, advanced features, dedicated support, and high scalability.

DBMS	Key Characteristics	Common Use Case
Oracle Database	The leading commercial RDBMS. Known for extreme scalability, high availability, and advanced security. It is the gold standard for large-scale enterprise systems.	Global banking, massive e-commerce, and mission-critical systems.
Microsoft SQL Server (SQL Server)	A powerful, feature-rich RDBMS primarily used in Microsoft Windows environments, tightly integrated with other Microsoft products. Known for its ease of use and GUI tools.	Mid-to-large size companies running on a Windows server stack.
IBM Db2 (DB2)	Developed by IBM, this RDBMS is highly optimized for transaction processing and large data warehouses. It's common in mainframe and Unix environments.	Financial services, mainframes, and massive data analytics platforms.