RDBMS (Relational Database Management Systems) use key constraints to define rules and restrictions on data stored in database tables. These constraints ensure data integrity and maintain consistency within the database. Here are some key constraints commonly used in RDBMS:
In types of Key constraints we have :
Primary keys play a crucial role in establishing relationships between tables in a relational database. They are used as references by foreign keys in related tables.
Unique keys are useful when you need to ensure the uniqueness of values within a column or set of columns, but you want to allow some flexibility by permitting NULL values in those columns.
Difference between Primary Key and Unique Key
-- mysql format
--Creating a Foreign Key During Table Creation
-- Creating the parent table ST_REG
CREATE TABLE ST_REG (
ST_ID INT PRIMARY KEY,
-- Other columns
);
-- Creating the child table ST_FEE with a foreign key referencing ST_REG(ST_ID)
CREATE TABLE ST_FEE (
FEE_ID INT PRIMARY KEY,
ST_ID INT,
-- Other columns
FOREIGN KEY (ST_ID) REFERENCES ST_REG(ST_ID)
);
--Creating a Foreign Key During Table Creation
-- Creating the parent table ST_REG (if not already created)
CREATE TABLE ST_REG (
ST_ID INT PRIMARY KEY,
-- Other columns
);
-- Creating the child table ST_FEE without a foreign key initially
CREATE TABLE ST_FEE (
FEE_ID INT PRIMARY KEY,
ST_ID INT,
-- Other columns
);
-- Adding a foreign key constraint to the ST_FEE table
ALTER TABLE ST_FEE
ADD FOREIGN KEY (ST_ID) REFERENCES ST_REG(ST_ID);
Both primary keys and foreign keys serve important roles in database design:
Additional notes:
Problem:
Deletion of Record Problem
CREATE TABLE st_fee (
st_id INT(3),
-- Other columns
FOREIGN KEY (st_id) REFERENCES st_reg(st_id) ON DELETE CASCADE ON UPDATE CASCADE
);
Examples:
1. NULL Constraint:
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Birthdate DATE,
Email VARCHAR(100) NULL -- Email can be null
);
2. NOT NULL Constraint:
CREATE TABLE Users (
UserID INT PRIMARY KEY,
Username VARCHAR(50) NOT NULL, -- Username cannot be null
Password VARCHAR(100),
Email VARCHAR(100)
);
3. CHECK Constraint:
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
Price DECIMAL(10, 2) CHECK (Price >= 0), -- Price must be non-negative
StockQuantity INT CHECK (StockQuantity >= 0), -- Stock quantity must be non-negative
);
4. DEFAULT Constraint:
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
OrderDate DATE DEFAULT CURRENT_DATE, -- Default to the current date
CustomerID INT,
Status VARCHAR(20) DEFAULT 'Pending' -- Default to 'Pending' status
);
These key constraints ensure data consistency, reliability, and accuracy within the relational database, making it easier to manage and maintain data integrity.
Both procedural and non-procedural query languages serve as powerful tools for interacting with relational databases, allowing users to retrieve and manipulate data according to their needs.
There are two fundamental "Pure" Query languages or Mathematical Query Languages:
Understanding these languages is crucial for comprehending how database systems manage relational databases, as well as for gaining insights into how SQL functions.
Relational Algebra and Relational Calculus provide the theoretical foundation for query languages. SQL, on the other hand, is a practical implementation of these theories, enabling us to retrieve data from Relational Database Management Systems (RDBMS).
Basic Introduction
What is the Concept of Relational Algebra?
How Does Relational Algebra Work?
Each query in Relational Algebra describes a step-by-step procedure for computing the desired answer.
The Relational Algebraic Operations can be categorized in several ways:
Fundamental Operations
Additional Operations
Set-Oriented Operations
Relation-Oriented Operations
Unary Operations: When we use only a single table and apply operations
Binary Operations: When we use two tables
Select (σ): Selects all tuples that satisfy the given selection condition from relation 'R'.
Project (π): Produces a new relation with a subset of the attributes from 'R'.
Cross Product (X): Generates a relation that combines attributes from both 'R1' and 'R2,' including all possible combinations of tuples from 'R1' and 'R2'.
Union (⋃): Produces a relation that includes all the tuples from either 'R1' or 'R2' (or both).
Set Difference (-): Produces a relation that includes all the tuples in 'R1' that are not in 'R2'.
Set Intersection (⋂): Generates a relation that includes all the tuples found in both 'R1' and 'R2'.
Division (÷;): Used for queries involving concepts like 'For All' or 'Every.'
Rename (ρ): Used to assign a name to a relation obtained after applying any relational algebra operation.
The select operation is used when we want to retrieve or display a particular set of tuples from a 'relation' based on certain conditions.
Syntax: σ <condition> (R)
Example:
STUDENT (R)
+---------+----------+-------+-----------+----------+
| S_ID | S_Name | S_Age | S_State | S_Course |
|---------+----------+-------+-----------+----------+
| 1 | Deepak | 25 | Delhi | B.Com |
| 2 | Rahul | 24 | Pune | B.Sc |
| 3 | Kapil | 30 | Delhi | B.Com |
| 4 | Sudesh | 22 | Bangalore | M.Sc |
| 5 | Varun | 29 | Pune | B.Com |
+---------+----------+-------+-----------+----------+
Query: Select * from student where S_Age > 25
Algebraic Representation: σ < S_Age > 25 > (STUDENT)
Output:
+---------+----------+-------+--------+----------+
| S_ID | S_Name | S_Age |S_State | S_Course |
|---------+----------+-------+--------+----------+
| 3 | Kapil | 30 | Delhi | B.Com |
| 5 | Varun | 29 | Pune | B.Com |
+---------+----------+-------+--------+----------+
Query: Select * from student where S_Age > 25 AND S_Age < 30
Algebraic Representation: σ < S_Age > 25 AND S_Age < 30
>
(STUDENT)
We could also use : σ < S_Age > 25 ∧ S_Age < 30 > (STUDENT)
Output:
+---------+----------+-------+---------+----------+
| S_ID | S_Name | S_Age | S_State | S_Course |
|---------+----------+-------+---------+----------+
| 5 | Varun | 29 | Pune | B.Com |
+---------+----------+-------+---------+----------+
Query: Select * from student where S_Age > 25 AND S_Course = "B.Com"
Algebraic Representation: σ < S_Age > 25 ∧ S_Course = "B.Com" > (STUDENT)
Output:
+---------+----------+-------+---------+----------+
| S_ID | S_Name | S_Age | S_State | S_Course |
|---------+----------+-------+---------+----------+
| 3 | Kapil | 30 | Delhi | B.Com |
| 5 | Varun | 29 | Pune | B.Com |
+---------+----------+-------+---------+----------+
Example:
STUDENT (R)
+---------+----------+-------+-----------+----------+
| S_ID | S_Name | S_Age | S_State | S_Course |
|---------+----------+-------+-----------+----------+
| 1 | Deepak | 25 | Delhi | B.Com |
| 2 | Rahul | 24 | Pune | B.Sc |
| 3 | Kapil | 30 | Delhi | B.Com |
| 4 | Sudesh | 22 | Bangalore | M.Sc |
| 5 | Rahul | 29 | Pune | B.Com |
+---------+----------+-------+-----------+----------+
Query: Retrieve S_Age from STUDENT.
Algebraic Representation: π < S_Age > (STUDENT)
Output:
+-------+
| S_Age |
+-------+
| 25 |
| 24 |
| 30 |
| 22 |
| 29 |
+-------+
Query: Retrieve S_ID & S_Name from STUDENT.
Algebraic Representation: π < S_ID, S_Name > (STUDENT)
Output:
+---------+----------+
| S_ID | S_Name |
|---------+----------+
| 1 | Deepak |
| 2 | Rahul |
| 3 | Kapil |
| 4 | Sudesh |
| 5 | Rahul |
+---------+----------+
Query: Retrieve S_Name from STUDENT.
Algebraic Representation: π < S_ID, S_Name > (STUDENT)
Output:
+----------+
| S_Name |
+----------+
| Deepak |
| Rahul |
| Kapil |
| Sudesh |
+----------+
Query: Retrieve S_Name, S_Id & S_Course from STUDENT Where S_Course = "B.Com".
Algebraic Representation: σ < S_Course = "B.Com" > (π < S_ID, S_Name, S_Course > (STUDENT))
Output:
+---------+----------+----------+
| S_ID | S_Name | S_Course |
|---------+----------+----------+
| 1 | Deepak | B.Com |
| 3 | Kapil | B.Com |
| 5 | Rahul | B.Com |
+---------+----------+----------+
Query: Retrieve S_Name, S_Id & S_Course from STUDENT Where S_Course = "B.Com".
Algebraic Representation: σ < S_Course = "B.Com" > (π < S_ID, S_Name, S_Course > (STUDENT))
Output:
+---------+----------+----------+
| S_ID | S_Name | S_Course |
|---------+----------+----------+
| 1 | Deepak | B.Com |
| 3 | Kapil | B.Com |
| 5 | Rahul | B.Com |
+---------+----------+----------+
We want to retrieve S_Age and S_State column with new name = Student_details
ρ(Student_Details, π < A_Age, S_State > (STUDENT))
Output:
Student_Details
+-------+-----------+
| S_Age | S_State |
+-------+-----------+
| 25 | Delhi |
| 24 | Pune |
| 30 | Delhi |
| 22 | Bangalore |
| 29 | Pune |
+-------+-----------+
Student_Details
+-------+-----------+
| Age | State |
+-------+-----------+
| 25 | Delhi |
| 24 | Pune |
| 30 | Delhi |
| 22 | Bangalore |
| 29 | Pune |
+-------+-----------+
Employee Student
+-------+-----------+ +-------+-----------+
| E_ID | E_Name | | S_ID | S_Name |
+-------+-----------+ +-------+-----------+
| 1 | Deepak | | 6 | Sudesh |
| 2 | Rajesh | | 5 | Rahul |
| 3 | Deepak | | 2 | Deepak |
| 4 | Kapil | | 7 | Sanjay |
+-------+-----------+ | 9 | Deepak |
+-------+-----------+
Employee ⋃ Student
Output:
+-------+-----------+
| E_ID | E_Name |
+-------+-----------+
| 1 | Deepak |
| 2 | Rajesh |
| 3 | Deepak |
| 4 | Kapil |
| 6 | Sudesh |
| 5 | Rahul |
| 7 | Sanjay |
| 9 | Deepak |
+-------+-----------+
π < E_Name > (Employee) ⋃ π < S_Name > (Student)
Output:
+-----------+
| E_Name |
+-----------+
| Deepak |
| Rajesh |
| Kapil |
| Sudesh |
| Rahul |
| Sanjay |
+-----------+
Union Properties
Compatibility Conditions:
Employee Student
+-------+-----------+ +-------+-----------+
| E_ID | E_Name | | S_ID | S_Name |
+-------+-----------+ +-------+-----------+
| 1 | Deepak | | 6 | Sudesh |
| 2 | Rajesh | | 5 | Rahul |
| 3 | Deepak | | 2 | Deepak |
| 4 | Kapil | | 7 | Sanjay |
+-------+-----------+ | 9 | Deepak |
+-------+-----------+
(π < S_ID > (Student)) ∩ (π < E_ID > (Employee))
+-------+
| S_ID |
+-------+
| 2 |
+-------+
Now, if we also want to show the name column:
(π < E_Name > (Employee)) ∩ (π < S_Name > (Student))
+-----------+
| S_Name |
+-----------+
| Deepak |
+-----------+
Intersection Properties
Compatibility Conditions:
Employee Student
+-------+-----------+ +-------+-----------+
| E_ID | E_Name | | S_ID | S_Name |
+-------+-----------+ +-------+-----------+
| 1 | Deepak | | 6 | Sudesh |
| 2 | Rajesh | | 5 | Rahul |
| 3 | Deepak | | 2 | Deepak |
| 4 | Kapil | | 4 | Kapil |
+-------+-----------+ | 9 | Deepak |
+-------+-----------+
Employee - Student
+-------+-----------+
| E_ID | E_Name |
+-------+-----------+
| 1 | Deepak |
| 2 | Rajesh |
| 3 | Deepak |
+-------+-----------+
(π < E_Name > (Employee)) - (π < S_Name > (Student))
+-----------+
| E_Name |
+-----------+
| Rajesh |
+-----------+
Set Difference Properties:
Compatibility Conditions:
Example:
Table A Table B
+-------+-----------+ +-------+-----------+
| ID | Name | | Code | Category |
+-------+-----------+ +-------+-----------+
| 1 | John | | 101 | A |
| 2 | Alice | | 102 | B |
| 3 | Bob | | 103 | A |
+-------+-----------+ | 104 | C |
+-------+-----------+
Cartesian Product (A × B):
+-------+-----------+-------+-----------+
| ID | Name | Code | Category |
+-------+-----------+-------+-----------+
| 1 | John | 101 | A |
| 1 | John | 102 | B |
| 1 | John | 103 | A |
| 1 | John | 104 | C |
| 2 | Alice | 101 | A |
| 2 | Alice | 102 | B |
| 2 | Alice | 103 | A |
| 2 | Alice | 104 | C |
| 3 | Bob | 101 | A |
| 3 | Bob | 102 | B |
| 3 | Bob | 103 | A |
| 3 | Bob | 104 | C |
+-------+-----------+-------+-----------+
Cartesian Product Properties:
Concept:
Imagine you have two tables: "Customers" and "Orders." The "Customers" table contains
information
about different customers, and the "Orders" table keeps track of various orders made by those
customers.
Now, let's say you want to find customers who have ordered all available products. In other words, you want to identify customers who have ordered every product in your store.
This is where the Division operation comes in.
The result of "Customers ÷ Orders" will give you a list of customer IDs (or names) who have ordered every product.
Customers Table:
+-----------+-----+
| CustomerID| Name|
+-----------+-----+
| 101 | John|
| 102 | Alice|
| 103 | Bob |
+-----------+-----+
Orders Table:
+---------+-----------+---------+
| OrderID | CustomerID| Product |
+---------+-----------+---------+
| 1 | 101 | A |
| 2 | 101 | B |
| 3 | 102 | A |
| 4 | 101 | C |
| 5 | 102 | B |
| 6 | 102 | C |
| 7 | 103 | A |
| 8 | 103 | B |
+---------+-----------+---------+
Now, if you perform "Customers ÷ Orders," the result will be a list of customer IDs who have ordered every product. In this case, it will be:
+-----------+
| CustomerID|
+-----------+
| 101 |
| 102 |
+-----------+
This tells you that both John (CustomerID 101) and Alice (CustomerID 102) have ordered every product available in the store.
The Join operation is used when you have two or more tables, and you want to combine them based on a common column or attribute. It helps you retrieve information from multiple tables by linking related data.
The Join operation combines rows from two or more tables based on a specified condition or matching column. This condition is typically defined using the equality operator (=), where the values in the specified columns must match for the rows to be included in the result.
There are several types of Join operations in relational algebra, including:
Let's consider two tables: "Employees" and "Departments." The "Employees" table contains information about employees, including their names and department IDs. The "Departments" table contains department names and corresponding department IDs.
+-------+-----------+-----------+
| EmpID | EmpName | DeptID |
+-------+-----------+-----------+
| 1 | John | 101 |
| 2 | Alice | 102 |
| 3 | Bob | 101 |
| 4 | Carol | 103 |
+-------+-----------+-----------+
+-------+------------+
| DeptID| DeptName |
+-------+------------+
| 101 | HR |
| 102 | Sales |
| 103 | Marketing |
+-------+------------+
An Inner Join between the "Employees" and "Departments" tables on the "DeptID" column would result in:
+-------+-----------+-----------+------------+
| EmpID | EmpName | DeptID | DeptName |
+-------+-----------+-----------+------------+
| 1 | John | 101 | HR |
| 2 | Alice | 102 | Sales |
| 3 | Bob | 101 | HR |
| 4 | Carol | 103 | Marketing |
+-------+-----------+-----------+------------+
This Inner Join combines data from both tables where the "DeptID" values match. You get a result set that includes employee names and their corresponding department names.
+-------+-----------+-----------+
| EmpID | EmpName | DeptID |
+-------+-----------+-----------+
| 1 | John | 101 |
| 2 | Alice | 102 |
| 3 | Bob | 101 |
| 4 | Carol | 103 |
+-------+-----------+-----------+
+-------+------------+
| DeptID| DeptName |
+-------+------------+
| 101 | HR |
| 102 | Sales |
| 103 | Marketing |
| 104 | Finance |
+-------+------------+
+-------+-----------+-----------+------------+
| EmpID | EmpName | DeptID | DeptName |
+-------+-----------+-----------+------------+
| 1 | John | 101 | HR |
| 2 | Alice | 102 | Sales |
| 3 | Bob | 101 | HR |
| 4 | Carol | 103 | Marketing |
| null | null | 104 | Finance |
+-------+-----------+-----------+------------+
In this Left Outer Join example, all rows from the "Employees" table are included in the result, along with matching rows from the "Departments" table. However, there's one additional row with null values in the "Employees" columns because there was no matching employee for the "Finance" department.
+-------+-----------+-----------+
| EmpID | EmpName | DeptID |
+-------+-----------+-----------+
| 1 | John | 101 |
| 2 | Alice | 102 |
| 3 | Bob | 101 |
| 4 | Carol | 103 |
+-------+-----------+-----------+
+-------+------------+
| DeptID| DeptName |
+-------+------------+
| 101 | HR |
| 102 | Sales |
| 103 | Marketing |
| 104 | Finance |
+-------+------------+
+-------+-----------+-----------+------------+
| EmpID | EmpName | DeptID | DeptName |
+-------+-----------+-----------+------------+
| 1 | John | 101 | HR |
| 2 | Alice | 102 | Sales |
| 3 | Bob | 101 | HR |
| null | null | 104 | Finance |
+-------+-----------+-----------+------------+
In this Right Outer Join, all rows from the "Departments" table are included in the result, along with matching rows from the "Employees" table. If there's no match for a particular department in the "Employees" table, the employee-related columns will contain null values.
+-------+-----------+-----------+
| EmpID | EmpName | DeptID |
+-------+-----------+-----------+
| 1 | John | 101 |
| 2 | Alice | 102 |
| 3 | Bob | 101 |
| 4 | Carol | 103 |
+-------+-----------+-----------+
+-------+------------+
| DeptID| DeptName |
+-------+------------+
| 101 | HR |
| 102 | Sales |
| 103 | Marketing |
| 104 | Finance |
+-------+------------+
+-------+-----------+-----------+------------+
| EmpID | EmpName | DeptID | DeptName |
+-------+-----------+-----------+------------+
| 1 | John | 101 | HR |
| 2 | Alice | 102 | Sales |
| 3 | Bob | 101 | HR |
| 4 | Carol | 103 | Marketing |
| null | null | 104 | Finance |
+-------+-----------+-----------+------------+
In this Full Outer Join example, all rows from both the "Employees" and "Departments" tables are included in the result. If there's no match for a particular department in the "Employees" table or if there's no match for a particular employee in the "Departments" table, the corresponding columns will contain null values.
Properties of Join
Key Points:
So, when you use Relational Calculus, you're essentially telling the database what you're looking for, and it handles the behind-the-scenes work to find the information you need.
Relational Calculus Variants
Relational Calculus, as a query language used in database management, comes in two main variants, each with its unique approach to formulating queries:
Both Tuple and Domain Relational Calculus provide a declarative way to query databases, allowing users to state what they want from the database without detailing how to retrieve it. These variants have significantly influenced the development of query languages used in practice.
Influence on Query Languages
Relational Calculus has played a pivotal role in shaping the design of many query languages, including widely used ones like:
These query languages, influenced by Relational Calculus, have made it easier for database users to interact with and retrieve data from relational databases, regardless of their technical expertise.