Unit -2: Relational Model in DBMS

Introduction:
The Relational Model organizes data using tables (relations) consisting of rows and
columns.
• The relational model represents how data is stored and managed in Relational
Databases where data is organized into tables, each known as a relation.
• Each row of a table represents an entity or record and each column represents
a particular attribute of that entity.
• The relational model transforms conceptual designs from ER diagrams into
implementable structures. These structures are used in relational database
systems like Oracle SQL and MySQL.
Example: Consider a relation STUDENT with attributes ROLL_NO, NAME, ADDRESS,
PHONE and AGE shown in the table

concepts of domain, attribute, tuple, relation:

Domain:
A domain D is a set of atomic values. By atomic we mean that each value in the domain is
invisible as far as the formal relational model is concerned. A common method of specifying
a domain is to specify a data type from which the data values forming the domain are
drawn. It is also useful to specify the name for the domain, to help in interpreting its values.
Some examples of domains follow:
● Usa_phone_numbers: The set of ten-difgit phone numbers valid in United States.
● Social_security_numbers: The set of valid nine-digit social security numbers.

1
● Names: The set of character strings that represents the names of persons.
● Employee_ages: Possible ages of employees in a company; each must be an integer value
between 15 and 80.

Attribute:
An attribute Ai is the name of a role played by some domain D in the relation schema R. D is
called the domain of Ai and is denoted by dom(Ai).

Tuple:
Mapping from attributes to values drawn from the respective domains of those attributes.
Tuples are intended to describe some entity (or relationship between entities) in the
miniworld Example: a tuple for a PERSON entity might be { Name ”smith”, Gender Male,
Age 25 }

Relation:
A named set of tuples all of the same form i.e., having the same set of attributes.

Importance of null values:

In a Database Management System (DBMS), a null value represents missing, unknown, or
inapplicable data. Unlike zero or an empty string, a null value explicitly indicates that no
value is assigned to a particular field.

constraints (Domain, Key constraints, integrity constraints) and their

importance:
Domain Constraints
Definition: Domain constraints define the permissible values for a column (attribute) in a
table. Each column has a data type that restricts the values it can store. Example: CREATE
TABLE Employees ( emp_id INT PRIMARY KEY,

2
name VARCHAR(50), age INT CHECK
(age > 18 AND age < 65), salary
DECIMAL(10,2)
);
Here, the age column must be greater than 18 and less than 65, ensuring that only valid
employee ages are stored.

Importance of Domain Constraints:

• Prevents invalid data entry (e.g., entering text in a numeric field).
• Ensures data accuracy by restricting values to a valid range.
• Reduces data inconsistencies and errors.

Key Constraints Definition:

Key constraints ensure uniqueness and uniquely identify records in a table. The main
types of key constraints are:

a) Primary Key (PK)

• Ensures uniqueness and non-null values in a column.

• A table can have only one primary key.

Example: CREATE TABLE Students (student_id INT PRIMARY KEY,name

VARCHAR(50),email VARCHAR(100));
• Each student_id must be unique and cannot be null

b) Unique Key
• Ensures column values are unique but allows nulls.
• Example: CREATE TABLE Users ( user_id INT PRIMARY KEY, username VARCHAR(50)
UNIQUE);
• No two users can have the same username.
c) Foreign Key (FK)
• Establishes a relationship between tables by enforcing referential integrity.
• Example:

3
CREATE TABLE Orders ( order_id
INT PRIMARY KEY, customer_id
INT,
FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);
• Prevents deleting a customer_id in the Customers table if it exists in Orders.
Importance of Key Constraints:
• Prevents duplicate records.
• Ensures data relationships remain valid.
• Supports data consistency across tables.

3. Integrity Constraints Definition:

Integrity constraints ensure that data is accurate and consistent throughout the
database.

Types of Integrity Constraints:

a) Entity Integrity
• Ensures that every table has a unique identifier (Primary Key) and that it cannot
be NULL.
• Example: CREATE TABLE Employees ( emp_id INT PRIMARY KEY, emp_name
VARCHAR(50));

• Every employee must have a unique emp_id.

b) Referential Integrity

• Ensures that foreign keys reference valid primary keys in another table.
• Prevents orphan records (records without valid references).
• Example:
CREATE TABLE Orders ( order_id
INT PRIMARY KEY, customer_id
INT, FOREIGN KEY (customer_id)
REFERENCES

4
Customers(customer_id) ON
DELETE CASCADE);

• If a customer is deleted, all their orders are also deleted.

c) Check Constraint
• Enforces a condition on a column.
• Example:
CREATE TABLE Products ( product_id INT PRIMARY KEY, price DECIMAL(10,2)
CHECK (price > 0));

• Ensures that the price is always greater than zero.

d) Not Null Constraint
• Ensures that a column cannot contain NULL values.

• Example:

CREATE TABLE Employees ( emp_id INT PRIMARY KEY, emp_name VARCHAR(50)

NOT NULL); • Every employee must have a name.

Importance of Integrity Constraints:

Prevents orphan records and maintains valid relationships.
Ensures data accuracy and prevents invalid entries.

Maintains data reliability and consistency across tables.

Relational Algebra :
Relational Algebra is a formal language used to query and manipulate relational
databases, consisting of a set of operations like selection, projection, union,
and join. It provides a mathematical framework for querying databases, ensuring
efficient data retrieval and manipulation. Relational algebra serves as the
mathematical foundation for query SQL.
Relational algebra simplifies the process of querying databases and makes it
easier to understand and optimize query execution for better performance. It is
essential for learning SQL because SQL queries are based on relational algebra
operations, enabling users to retrieve data effectively.

5
Basic Operators in Relational Algebra
Relational algebra consists of various basic operators that help us to fetch and
manipulate data from relational tables in the database to perform certain
operations on relational data. Basic operators are fundamental operations that
include selection (σ), projection (π), union (U), set difference (−), Cartesian
product (×), and rename (ρ).

1. Selection(σ)
The Selection Operation is basically used to filter out rows from a given table
based on certain given condition. It basically allows us to retrieve only those rows
that match the condition as per condition passed during SQL Query.
Example: If we have a relation R with attributes A, B, and C, and we want to select
tuples where C > 3, we write:

σ(c>3)(R) will select the tuples which have c more than 3.

Output:

6
Explanation: The selection operation only filters rows but does not display or
change their order. The projection operator is used for displaying specific
columns.
2. Projection(π)
While Selection operation works on rows, similarly projection operation of
relational algebra works on columns. It basically allows us to pick specific columns
from a given relational table based on the given condition and ignoring all the
other remaining columns.
Example: Suppose we want columns B and C from Relation R.
π(B,C)(R) will show following columns.
Output:

Explanation: By Default, projection operation removes duplicate values.

3. Union(U)
The Union Operator is basically used to combine the results of two queries into a
single result. The only condition is that both queries must return same number of
columns with same data types. Union operation in relational algebra is the same
as union operation in set theory.
Example: Consider the following table of Students having different optional
subjects in their course.
FRENCH

7
GERMAN

If FRENCH and GERMAN relations represent student names in two subjects, we

can combine their student names as follows:
π(Student_Name)(FRENCH) U π(Student_Name)(GERMAN)
Output:

Explanation: The only constraint in the union of two relations is that both
relations must have the same set of Attributes.
4. Set Difference(-)
Set difference basically provides the rows that are present in one table, but not in
another tables. Set Difference in relational algebra is the same set difference
operation as in set theory.
Example: To find students enrolled only in FRENCH but not in GERMAN, we write:
π(Student_Name)(FRENCH) - π(Student_Name)(GERMAN)

8
Explanation: The only constraint in the Set Difference between two relations is
that both relations must have the same set of Attributes.
5. Rename(ρ)
Rename operator basically allows you to give a temporary name to a specific
relational table or to its columns. It is very useful when we want to avoid
ambiguity, especially in complex Queries. Rename is a unary operation used for
renaming attributes of a relation.
Example: We can rename an attribute B in relation R to D

ρ(D/B)R will rename the attribute 'B' of the relation by 'D".

Output Table:

9
6. Cartesian Product(X)
The Cartesian product combines every row of one table with every row of another
table, producing all the possible combination. It's mostly used as a precursor to more
complex operation like joins. Let’s say A and B, so the cross product between A X B
will result in all the attributes of A followed by each attribute of B. Each record of A
will pair with every record of B.
Relation A:

Relation B:

Output: If relation A has 3 rows and relation B has 2 rows, the Cartesian product A ×
B will result in 6 rows.

Explanation: If A has 'n' tuples and B has 'm' tuples then A X B will have 'n*m' tuples.

10
Relational Calculus
Relational calculus is a non-procedural query language used in the context of
relational algebra. It focuses on what data to retrieve, rather than how to retrieve
it, making it different from relational algebra, which is procedural. In relational
calculus, queries are expressed using logical formulas that describe the desired
result, without specifying the exact steps to get there.
Relational Calculus has two variations:
a. Tuple Relational Calculus (TRC)
b. Domain Relational Calculus (DRC)
Tuple Relational Calculus (TRC) in DBMS
Tuple Relational Calculus (TRC) is a non-procedural query language used to
retrieve data from relational databases by describing the properties of the
required data (not how to fetch it).
It is based on first-order predicate logic and uses tuple variables to represent
rows of tables.
Syntax: The basic syntax of TRC is as follows:
{ t | P(t) }
• t: Tuple variable (row placeholder)
• P(t): Predicate condition to satisfy
• {}: Denotes a set of result tuples
Logical Operators in TRC:
• ∧: AND
• ∨: OR
• ¬: NOT
Quantifiers:
• ∃ t ∈ r (Q(t)) → There exists a tuple t in relation r satisfying predicate Q(t)
• ∀ t ∈ r (Q(t)) → For all tuples t in relation r, predicate Q(t) holds
For example, let's say we have a table called "Employees" with the
following attributes:

11
 To retrieve the names of all employees who earn more than $50,000 per year, we
can use the following TRC query:
{ t | Employees(t) ∧ t.Salary > 50000 }
Explanation:
• Employees(t) means t is a tuple from the Employees table.
• ∧ (AND) is used to add a condition on salary.
• The result is a set of tuples where each employee earns more than $50,000

• TRC is non-procedural - it specifies what data to retrieve, not how

to retrieve it.
• While expressive, TRC is more abstract and mainly used in
academic or theoretical contexts, not practical database systems.

Domain Relational Calculus (DRC)

Domain Relational Calculus (DRC) is a formal query language for

relational databases. It describes queries by specifying a set of conditions
or formulas that the data must satisfy. These conditions are written using
domain variables and predicates, and it returns a relation that satisfies
the specified conditions.
A general form of a DRC query is written 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.
The query returns a relation where each tuple consists of values for the
domain variables x1,x2,…,xn such that the predicate PPP is true.

12
Basic SQL:
Simple Database schema:
A database schema defines the structure of a database, including tables, columns,
data types, and relationships. Below is a basic schema for an Employee
Management System using SQL.
Creating a Simple Database Schema
Tables in the Schema
1. Employee – Stores employee details.
2. Department – Stores department details.
3. Project – Stores project details.
4. Works_On – Tracks which employees work on which projects.
SQL Schema Definition
Employee Table
CREATE TABLE Employee ( Emp_ID INT PRIMARY KEY, Name VARCHAR(50) NOT
NULL, Age INT, Salary DECIMAL(10,2), Dept_ID INT,FOREIGN KEY (Dept_ID)
REFERENCES Department(Dept_ID));
Department Table
CREATE TABLE Department (Dept_ID INT PRIMARY KEY, Dept_Name VARCHAR(50)
UNIQUE NOT NULL);
Project Table
CREATE TABLE Project (Proj_ID INT PRIMARY KEY, Proj_Name VARCHAR(100) NOT
NULL, Budget DECIMAL(12,2));
Works_On Table (Many-to-Many Relationship)
CREATE TABLE Works_On (Emp_ID INT, Proj_ID INT, Hours_Worked INT, PRIMARY
KEY (Emp_ID, Proj_ID),
FOREIGN KEY (Emp_ID) REFERENCES Employee(Emp_ID),

FOREIGN KEY (Proj_ID) REFERENCES Project(Proj_ID)); Data

types:

13
Data types are used to represent the nature of the data that can be stored in the
database table. Data types mainly classified into three categories for every
database.

1. String Data types

2. Numeric Data types
3. 3. Date and time Data types
Data Types in MySQL:
1. Numeric Types:
• INT (Integer): Used for storing whole numbers.
• DECIMAL or NUMERIC: Used for storing fixed-point numbers.
• FLOAT and DOUBLE: Used for storing floating-point numbers.
2. String Types:
• CHAR and VARCHAR: Used for storing character strings. CHAR has a fixed length,
while VARCHAR has a variable length.
• TEXT: Used for large text data
3. Date and Time Types:
• DATE: Used for storing dates in the format 'YYYY-MM-DD'.
• TIME: Used for storing times in the format 'HH:MM:SS'.
• DATETIME: Used for storing both date and time in the format 'YYYY-MM DD
HH:MM:SS'.

14
• TIMESTAMP: Similar to DATETIME, but often used to store the current
timestamp.

Table definitions (create, alter):

Data Definition Language (DDL)
DDL changes the structure of the table like creating a table, deleting a table,
altering a table, etc.
All the command of DDL are auto-committed that means it permanently save all
the changes in the database.
Here are some commands that come under DDL:
CREATE
ALTER
DROP
TRUNCATE
a. CREATE It is used to create a new table in the database.
Syntax: CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,....]);
Example: CREATE TABLE EMPLOYEE(Name VARCHAR(20), Email VARCHAR (100),
DOB DATE);
b. DROP: It is used to delete both the structure and record stored in the table.
Syntax: DROP TABLE table_name;
Example
DROP TABLE EMPLOYEE;
c. ALTER: It is used to alter the structure of the database. This change could be
either to modify the characteristics of an existing attribute or probably to add
a new attribute.
Syntax:
Syntax: To add a new column in the table
ALTER TABLE table_name ADD column_name COLUMN-definition; To
modify existing column in the table:

15
ALTER TABLE table_name RENAME (column_definitions....); To
rename existing column name in the table:
ALTER TABLE table_name RENAME old_column_name to new-column_name;
EXAMPLE
ALTER TABLE STU_DETAILS ADD(ADDRESS VARCHAR(20)); ALTER
TABLE STU_DETAILS MODIFY (NAME VARCHAR(20));
d. TRUNCATE: It is used to delete all the rows from the table and free the space
containing the table.
Syntax: TRUNCATE TABLE table_name;
Example: TRUNCATE TABLE EMPLOYEE;

Data Manipulation Language (DML) :

It’s a subset of SQL (Structured Query Language) used to manipulate
or work with the data inside database tables.
Different DML operations (insert, delete, update):
DML commands are used to modify the database. It is responsible for all form of
changes in the database.
The command of DML is not auto-committed that means it can't permanently
save all the changes in the database. They can be rollback.
Here are some commands that come under DML:
INSERT
UPDATE
DELETE
a. INSERT: The INSERT statement is a SQL query. It is used to insert data into the
row of a table.
Syntax:
INSERT INTO TABLE_NAME
(col1, col2, col3,.... col N)
VALUES (value1, value2, value3, .... valueN);

16
Or
INSERT INTO TABLE_NAME
VALUES (value1, value2, value3, .... valueN); For
example:
INSERT INTO students (Name, Subject) VALUES ("Sonoo", "DBMS");

b. UPDATE: This command is used to update or modify the value of a column in

the table.
Syntax:
UPDATE table_name SET [column_name1= value1,...column_nameN = valueN]
[WHERE CONDITION] For example:
UPDATE students
SET User_Name = 'Sonoo'
WHERE Student_Id = '3'
c. DELETE: It is used to remove one or more rows from a table.
Syntax: DELETE FROM table_name [WHERE condition]; For
example:
DELETE FROM EMPLOYEE WHERE Name="Sonoo";

17