Relational Model
Introduction
• A data model comprises of three components:
• A Structural part consists of a set of rules that define how the
database is to be constructed.
• A Manipulative part defines a type of operations such as
transaction that are data allow on the data. This include the
operations that are used for updating or retrieving data and for
changing the structure of the database.
• A set of Integrity rule which ensures that the data is accurate.
• Relational Model is based on mathematical concepts of a
relation which is physically represented as a table.
• Terminology for Relational Model:
• Relation: A table with column and rows.
• Attribute: It is a named column of a relation. We represented a
relation as a table in which the rows of a table correspond to
individual records and the table column correspond to attribute.
• Domain: It is a set of allowable values for one or more attributes.
Each attribute in a relational model is associated with the domain.
A domain may be distinct for each attribute or two or more
attributes may be associated with the same domain. Domain defines
the meaning and source of values that attribute can hold.
• Tuple: It is a record of a relation.
• Relational Database: It is a collection of tables that are
appropriately structured.
Employee
Employee_ID Employee_Name Employee_Salary Employee_City
A1335 Ram 10000 Jaipur
A1336 Sita 20000 Jaipur
A1337 Madhu 15000 Udaipur
A1338 Mahesh 25000 Udaipur
Attribute Meaning Domain
Employee_ID Set of all possible ID Alpha Numeric, Size:5
numbers
Employee_Name Set of all possible of Alphabetic, Size: 30
names
Employee_Salary Set of all possible of Numeric, 8 Digits,
employee salary Range: 10000 to
10000000
Employee_City Set of all possible city Alphabetic, Size: 50
names
Properties
• Table has a name that is distinct form of other database.
• Each cell of table contains exactly one value.
• Each column has a distinct name.
• Values of a column are all from the same domain.
• The order of column has no significance. In other words,
provided a column name is move along with the column
values. We can interchange a column.
• Each record is distinct. There is no duplicate records.
• The order of record has no significance.
Database Schema
• It is a logical design of Database.
• It includes attributes of a relation.
• A primary key or foreign key constrains.
• An instances of relation refers to its contents at a point of a time.
Schema of Employee
Employee(Employee_ID, Employee_Name, Employee_Salary, Employee_City)
Instance 1
Employee_ID Employee_Name Employee_Salary Employee_City
A1335 Ram 10000 Jaipur
A1336 Sita 20000 Jaipur
Instance 2
Employee_ID Employee_Name Employee_Salary Employee_City
A1335 Ram 10000 Jaipur
A1336 Sita 20000 Jaipur
A1337 Madhu 15000 Udaipur
Integrity Rule
• Integrity constraints are rules that help to maintain the accuracy and consistency of data in a database.
They can be used to ensure that data is entered correctly. For Eg, a simple integrity constraint in DBMS
might state that all customers must have a valid email address.
• This would prevent someone from accidentally entering an invalid email address into the database.
Integrity constraints can also be used to enforce relationships between tables.
Integrity Constraints
Domain Entity Referential Key
Constraints Constraints Constraints Constraints
• Domain Constraint: A domain constraint is a restriction on the values that can be stored in a column. For
example, if you have a column for "age" domain integrity constraints in DBMS would ensure that only
values between 1 and 120 can be entered into that column. This ensures that only valid data is entered
into the database.
• Entity Integrity: An entity integrity constraint is a restriction on null values. Null values are values that
are unknown or not applicable, and they can be problematic because they can lead to inaccurate results.
Entity integrity constraints would ensure that null values are not entered into any required columns. For
example, if you have a column for "first name" an entity integrity constraint in DBMS would ensure that
this column cannot contain any null values. Another Eg. is Student Roll No.
• Referential Integrity: A referential integrity constraint is a restriction on how foreign keys can be used. A
foreign key is a column in one table that references a primary key in another table. For example, you
have a table of employees and a table of department managers. The "employee ID" column in the
employee's table would be a foreign key that references the "manager ID" column in the manager's
table. Referential integrity constraints in DBMS would ensure that every manager ID in the manager's
table has at least one corresponding employee ID in the employee's table. In other words, it would
prevent you from assigning an employee to a manager who doesn't exist.
• Key Constraints: Key constraints in DBMS are a restriction on duplicate values. A key is composed of
one or more columns whose values uniquely identify each row in the table. For Eg, you have a table of
products with columns for "product ID" and "product name" The combination of these two values would
be the key for each product, and a key constraint would ensure that no two products have the same
combination of product ID and product name.
Keys Constraints
• Super Key
• Candidate Key
• Primary Key
• Foreign Key
• Alternate Key
• Simple Key
• Composite Key
• Unique Key
Null Values: Null represent a value for a column that is currently unknown or is not applicable for
that record. Null are way to deal with incomplete or exceptional data. A null is not the same as
Zero(0) numeric value or a text string with space because 0 and spaces are value but null represents
the absence of a value.
A key constraint is a rule that defines how data in a column(s) can be stored in a table. There are several different
types of key constraints in DBMS, each with its own specific purpose.
1. Primary Key Constraints: A primary key constraint (also known as a "primary key") is a type of key
constraint that requires every value in a given column to be unique. In other words, no two rows in a table can
have the same value for their primary key column(s). A primary key can either be a single column or multiple
columns (known as a "composite" primary key). The null value is not allowed in the primary key column(s).
2. Unique Key Constraints: A unique key constraint is a column or set of columns that ensures that the values
stored in the column are unique. A table can have more than one unique key constraint, unlike the primary key.
A unique key column can contain NULL values. Like primary keys, unique keys can be made up of a single
column or multiple columns.
3. Foreign Key Constraints: A foreign key constraint defines a relationship between two tables. A foreign key
in one table references a primary key in another table. Foreign keys prevent invalid data from being inserted
into the foreign key column. Foreign keys can reference a single column or multiple columns.
4. NOT NULL Constraints: A NOT NULL constraint is used to ensure that no row can be inserted into the
table without a value being specified for the column(s) with this type of constraint. Thus, every row must have
a non-NULL value for these columns.
5. Check Constraints: A check constraint enforces data integrity by allowing you to specify conditions that
must be met for data to be inserted into a column. For example, you could use a check constraint to ensure that
only positive integer values are inserted into a particular column. Check constraints are usually used in
combination with other constraints (such as NOT NULL constraints) to enforce more complex rules.
Relational Languages
• Manipulative part of a data model defines the types of operation that are allow on the data.
• This includes the operations that are used for updating or retrieving data from the database and for
changing the structure of database.
• There are 2 main languages:
• Procedural Query Language: In Procedural Language, the user instructs the system to perform a
series of operations on the database to produce the desired results. Users tell what data to be
retrieved from the database and how to retrieve it.
• Procedural Query Language performs a set of queries instructing the DBMS to perform various
transactions in sequence to meet user requests.
Eg. Relational Algebra
• Non- Procedural Query Language: In Non Procedural Language user outlines the desired
information without giving a specific procedure or without telling the steps by step process for
attaining the information.
• It only gives a single Query on one or more tables to get .
• The user tells what is to be retrieved from the database but does not tell how to accomplish it.
Eg. Relational Calculus
Relational Algebra
• Relational algebra is a procedural query language.
• It gives a step by step process to obtain the result of the query.
• It uses operators to perform queries.
• It provides a set of operations that takes one or more relation as input and return a relation as output.
Relational Algebra
Select Project Union Set Cartesian Rename Set
Operation Operation Operation Difference Product Operation Intersection
• Select Operation: The select operation selects tuples that satisfy a given predicate. It is denoted by sigma (σ).
Notation: σ p(r)
Where: σ is used for selection prediction
r is used for relation
p is used as a propositional logic formula which may use connectors like: AND OR and NOT.
These relational can use as relational operators like =, ≠, ≥, <, >, ≤.
Employee
Emp_ID Emp_Name Emp_Salary Emp_City Dept_Id
A1335 Ram 10000 Jaipur 1001
A1336 Sita 20000 Jaipur 1002
A1337 Madhu 15000 Udaipur 1003
A1338 Mahesh 25000 Udaipur 1004
A1339 Renu 10000 Jodhpur 1005
Output
Emp_ID Emp_Name Emp_Salary Emp_City Dept_Id
σ Emp_City=“Udaipur” (Employee) A1337 Madhu 15000 Udaipur 1003
A1338 Mahesh 25000 Udaipur 1004
• Project Operation: This operation shows the list of those attributes that we wish to appear in the result. Rest
of the attributes are eliminated from the table. It is denoted by ( ∏ )
Notation: ∏ A1, A2,…,An (r)
Where: A1, A2, A3 is used as an attribute name of relation r.
Employee
Emp_ID Emp_Name Emp_Salary Emp_City Dept_Id
A1335 Ram 10000 Jaipur 1001
A1336 Sita 20000 Jaipur 1002
A1337 Madhu 15000 Udaipur 1003
A1338 Mahesh 25000 Udaipur 1004
A1339 Renu 10000 Jodhpur 1005
Output
∏ Emp_ID, Emp_Name, Emp_Salary(Employee)
Emp_ID Emp_Name Emp_Salary
A1337 Madhu 15000
A1338 Mahesh 25000
• Union Operation: The union operation contains all the tuples that are either in R or S or both in R & S. It
eliminates the duplicate tuples. It is denoted by ( ∪ ).
Notation: R ∪ S
A union operation must hold the following condition:
• R and S must have the attribute of the same number.
• Duplicate tuples are eliminated automatically.
Output
Depositor Borrow
Customer
Customer Account_ Customer Loan_No
_name
_name No _name
Ram
Ram A-1335 Ram L-101
Mohit
Sita A-1336 Mohit L-102
Trishan
Madhu A-1337 Trishan L-103
Mahesh
Mahesh A-1338 Mahesh L-104
Amit
Renu A-1339 Amit L-105
Sita
∏ Customer_name (Borrow) ∪ ∏ Customer_name (Depositor) Madhu
Renu
• Set Intersection Operation: The set intersection operation contains all tuples that are in both R & S. It is
denoted by intersection ∩.
Notation: R ∩ S
Depositor Borrow Output
Customer Account_ Customer Loan_No Customer
_name No _name _name
Ram A-1335 Ram L-101 Ram
Sita A-1336 Mohit L-102 Mahesh
Madhu A-1337 Trishan L-103
Mahesh A-1338 Mahesh L-104
Renu A-1339 Amit L-105
∏ Customer_name (Borrow) ∩ ∏ Customer_name (Depositor)
• Set Difference Operation: The set intersection operation contains all tuples that are in R but not in S. It is
denoted by intersection minus (-).
Notation: R - S
Depositor Borrow Output
Customer Account_ Customer Loan_No Customer
_name No _name _name
Ram A-1335 Ram L-101 Mohit
Sita A-1336 Mohit L-102 Trishan
Madhu A-1337 Trishan L-103 Amit
Mahesh A-1338 Mahesh L-104
Renu A-1339 Amit L-105
∏ Customer_name (Borrow) - ∏ Customer_name (Depositor)
• Cartesian Product Operation: The Cartesian product is used to combine each row in one table with each row
in the other table. It is also known as a cross product. It is denoted by (X).
Notation: R X S
Employee Department
Emp_ID Emp_Name Dept_No Dept_Name
Employee X Department
A1335 Ram 101 A
A1336 Sita Output 102 B
Emp_ID Emp_Name Dept_No Dept_Name
A1335 Ram 101 A
A1335 Ram 102 B
A1336 Sita 101 A
A1336 Sita 102 B
• Rename Operation: The rename operation is used to rename the output relation. It is denoted by rho (ρ).
Eg. ρ(STUDENT1, STUDENT)
Relational Calculus
• Relational Calculus is Non-Procedural Query Language used in DBMS which specifies what data we want to
retrieve from the database. It will not describe how the data will fetched from the database.
• Relational Calculus exists in two forms:
• Tuple Relational Calculus (TRC): Tuple Relational Calculus (TRC) is a non-procedural query language used
in relational database management systems (RDBMS) to retrieve data from tables. TRC is based on the
concept of tuples, which are ordered sets of attribute values that represent a single row or record in a
database table.
TRC is a declarative language, meaning that it specifies what data is required from
the database, rather than how to retrieve it. TRC queries are expressed as logical formulas that
describe the desired tuples.
Notation: { t | p ( t ) }
where t is a tuple variable,
P(t) is a logical formula that describes the conditions that the tuples in the result must
satisfy.
The curly braces {} are used to indicate that the expression is a set of tuples.
• P(t) may have various conditions logically combined with OR (∨), AND (∧), NOT(¬).
It also uses quantifiers:
∃ t ∈ r (Q(t)) = ”there exists” a tuple in t in relation r such that predicate Q(t) is true.
∀ t ∈ r (Q(t)) = Q(t) is true “for all” tuples in relation r.
Employee
Emp_ID Emp_Name Emp_Salary Emp_City Dept_Id
A1335 Ram 10000 Jaipur 1001
A1336 Sita 20000 Jaipur 1002
A1337 Madhu 15000 Udaipur 1003
A1338 Mahesh 25000 Udaipur 1004
A1339 Renu 10000 Jodhpur 1005
• To retrieve the names of all employees who earn more than 20,000/- per year, we can use
the following TRC query:
{ t | Employee(t) ∧ t.Emp_Salary >=20000 }
• In this query, the “Employee(t)” expression specifies that the tuple variable t represents a
row in the “Employee” table.
• The “∧” symbol is the logical AND operator, which is used to combine the condition
“t.Emp_salary >=20000” with the table selection.
• The result of this query will be a set of tuples, where each tuple contains the Name
attribute of an employee who earns more than 20,000/- per year.
Eg:
Instructor Department
Inst_id Inst_name Dept_name Salary Dept_id Dept_name Inst_name
101 Ram HR 20000 1001 HR Ram
102 Mohit Manager 10000 1002 HR Sita
103 Trishan HR 15000 1003 HR Trishan
104 Mahesh HR 20000 1004 HR Mahesh
{t | t ∈ Instructor ^ ∃ s ∈ Department (t.dept_name=s.dept_name)}
Inst_id Inst_name Dept_name Salary
101 Ram HR 20000
103 Trishan HR 15000
104 Mahesh HR 20000
• Domain Relational Calculus (DRC): Domain Relational Calculus is a non-procedural query language
equivalent in power to Tuple Relational Calculus. Domain Relational Calculus provides only the
description of the query but it does not provide the methods to solve it. In the Domain Relational
Calculus, a query is expressed as,
{ ( x1, x2, x3, ..., xn ) | P (x1, x2, x3, ..., xn ) }
where, < x1, x2, x3, …, xn > represents resulting domains variables and P (x1, x2, x3, …, xn ) represents
the condition or formula equivalent to the Predicate calculus.
Predicate Calculus Formula: Employee
[Link] of all comparison operators Emp_ID Emp_Name Emp_Salary Emp_City Dept_Id
[Link] of connectives like and, or, not A1335 Ram 10000 Jaipur 1001
[Link] of quantifiers A1336 Sita 20000 Jaipur 1002
A1337 Madhu 15000 Udaipur 1003
A1338 Mahesh 25000 Udaipur 1004
A1339 Renu 10000 Jodhpur 1005
{ (t.Emp_Name, t.Emp_City) | Employee(t) ^ t.Emp_Salary>=20000}
Emp_Name Emp_City
Sita Jaipur
Mahesh Udaipur