UNIT-2

Relational Model CONCEPTS in DBMS

Relational model can represent as a table with columns and rows.
Each row is known as a tuple. Each table of the column has a name
or attribute.
Domain: It contains a set of atomic values that an attribute can take.
Attribute: It contains the name of a column in a particular table.
Each attribute Ai must have a domain
Relational instance: In the relational database system, the
relational instance is represented by a finite set of tuples. Relation
instances do not have duplicate tuples.
Relational schema: A relational schema contains the name of the
relation and name of all columns or attributes.
Relational key: In the relational key, each row has one or more
attributes. It can identify the row in the relation uniquely.
Example: STUDENT Relation
ADVERTISEMENT

NAME ROLL_NO PHONE_NO ADDRESS AGE

Ram 14795 7305758992 Noida 24
Shyam 12839 902628893 Delhi 35
6
Laxman 33289 8583287182 Gurugram 20
Mahesh 27857 7086819134 Ghaziabad 27
Ganesh 17282 9028 9i3988 Delhi 40

In the given table, NAME, ROLL_NO, PHONE_NO, ADDRESS, and AGE are the
attributes.

The instance of schema STUDENT has 5 tuples.

t3 = <Laxman, 33289, 8583287182, Gurugram, 20>

Relational Model Constraints and Relational Database Schemas

The Relational Model is a foundational concept in relational databases. It

provides a logical view of the data and defines how data is organized and
manipulated using relations (tables).
1. Relational Model Constraints

Relational model constraints are rules that ensure the accuracy and integrity
of data in a relational database. There are three main types of
constraints:

A. Domain Constraints

 Define the permissible values for a given attribute (column).

 Example: A BirthDate attribute must be of the date type.
 Enforced using data types and CHECK constraints.

B. Key Constraints

 Ensure uniqueness of rows in a table.

 Primary Key: Uniquely identifies each tuple (row) in a relation.
 Candidate Key: An attribute or set of attributes that can serve as a
primary key.
 Foreign Key: An attribute in one table that refers to the primary key
in another.

C. Integrity Constraints

 Entity Integrity: No part of a primary key can be NULL.

 Referential Integrity: Foreign key values must match existing
primary key values or be NULL.

2. Relational Database Schemas

A Relational Schema defines the structure of a relation (table), including:

 Relation Name (e.g., Student)

 Attributes (e.g., StudentID, Name, DOB)
 Domains (data types for each attribute)
 Keys (primary and foreign keys)
 Constraints (e.g., NOT NULL, UNIQUE, CHECK)

Schema Notation

A relation schema is typically written as

Student(StudentID: INT, Name: VARCHAR, DOB: DATE)

Where:
  StudentID is usually the primary key.Other constraints can be defined
separately.

Example:Student Relation Schema

Student(StudentID INT PRIMARY KEY,

Name VARCHAR(100) NOT NULL,
DOB DATE,
DeptID INT,
FOREIGN KEY (DeptID) REFERENCES Department(DeptID))

Department Relation Schema

Department(DeptID INT PRIMARY KEY, DeptName VARCHAR(100) NOT NULL)

Update Operations, Tractions, Dealing with Constraint Violations

Update operations modify data in relations. The three basic update

operations are:

1. Insert
Adds a new tuple (row) into a relation.
Example:

INSERT INTO Student (ID, Name, Age) VALUES (101, 'Ravi', 20);

2. Delete
Removes an existing tuple from a relation.
Example:

DELETE FROM Student WHERE ID = 101;

3. Modify (Update)
Changes the value of an existing attribute in a tuple.
Example:

UPDATE Student SET Age = 21 WHERE ID = 101;

Transactions in DBMS

A transaction is a sequence of operations performed as a single logical unit

of work. A transaction must satisfy ACID properties:

 Atomicity: All or nothing.

  Consistency: Preserve the integrity constraints.
 Isolation: No interference from other transactions.
 Durability: Changes persist even after system failure.

BEGIN TRANSACTION;
UPDATE Account SET balance = balance - 500 WHERE account_no = 123;
UPDATE Account SET balance = balance + 500 WHERE account_no = 456;
COMMIT;

If any operation fails, we use:

ROLLBACK;

Dealing with Constraint Violations

When performing update operations, constraints (rules) must not be violated.

There are several types:

1. Key Constraint Violation

 A duplicate primary key is inserted.

 A null is inserted in a primary key column.

Solution:

 Validate keys before insert/update.

 Use UNIQUE and NOT NULL constraints.

2. Entity Integrity Violation

 A NULL primary key is inserted.

Solution:

 Ensure primary key fields are always filled.

3. Referential Integrity Violation

 A foreign key refers to a non-existent record in the parent table.

Solution:

 Use ON DELETE CASCADE or ON UPDATE CASCADE where applicable.

 Insert parent records before child records.
4. Domain Constraint Violation

 Data type, length, or allowed values are incorrect.

 Validate input types and ranges.

 Use CHECK constraints.

Formal Relational Languages:

Unary Relational Operations: SELECT and PROJECT

Unary operations operate on a single relation (table). The two fundamental

unary operations in Relational Algebra are:

1. SELECT (σ) Operation

 Also called restriction.

 Used to filter rows based on a condition.
 It picks horizontal subsets of the table (tuples).

Syntax: σ<condition>(Relation)

Example: σAge > 18(Student) - Returns all students whose age is

greater than 18.

2. PROJECT (π) Operation

 Used to choose specific columns (attributes) from a relation.

 It picks vertical subsets of the table (columns).
 Removes duplicate rows automatically.

Syntax:π<attribute_list>(Relation)

πName, Age(Student)-----Returns only the Name and Age columns from the
Student table.
SELECT Name, Age FROM Student;

Feature SELECT (σ) PROJECT (π)

Works on Rows (tuples) Columns (attributes)
Type of subset Horizontal Vertical
Purpose Filters records Chooses columns
Retains
Duplicates Removes duplicates
duplicates
Relational Algebra Operations from Set Theory

Conditions for Set Operations:

Relations must be union-compatible:

→ Same number of attributes
→ Same data types in corresponding columns

1. UNION (∪)

 Combines tuples from both relations.

 Removes duplicate tuples (as it's based on set theory).

Syntax:R ∪ S
Meaning:All tuples that are in R or S or both.

Example:

If
R = {1, 2, 3}
S = {3, 4, 5}

R ∪ S = {1, 2, 3, 4, 5}
Then

SELECT * FROM R
UNION
SELECT * FROM S;

2. SET DIFFERENCE (−)

Returns tuples that are in R but not in S.

Syntax:R − S

Example:

If
R = {1, 2, 3}
S = {2, 3, 4}
Then
R − S = {1}

SELECT * FROM R
EXCEPT
SELECT * FROM S;

3. INTERSECTION (∩)

 Returns tuples that are common to both R and S.

Syntax:R ∩ S

Example:

If
R = {1, 2, 3}
S = {2, 3, 4}
Then
R ∩ S = {2, 3}

SELECT * FROM R
INTERSECT
SELECT * FROM S;

4. CARTESIAN PRODUCT (×)

 Also called Cross Product.

 Combines every tuple in R with every tuple in S.

Syntax:R × S

If
R has m tuples and
S has n tuples,
then R × S has m × n tuples.

Example:

If
R = {(1), (2)}
S = {(A), (B)}
Then
R × S = {(1,A), (1,B), (2,A), (2,B)}

SELECT * FROM R, S;
 ∪
Operation Symbol Purpose
Union Combines all tuples
Set Difference − Tuples in R but not in S
Intersection ∩ Common tuples in R and S
Cartesian Product × Pairwise combination of tuples

Binary Relational Operations: JOIN and DIVISION

1. JOIN Operation

The JOIN operation combines related tuples from two relations based on a
common attribute or condition. It is the most commonly used binary
operation in relational databases.

Types of JOIN:

A. Theta Join (θ Join)

 Combines tuples using a comparison operator (e.g., =, <, >).

 General form of a join.

Syntax:R ⋈θ S

Employee ⋈ Employee.dept_id = Department.dept_id Department

B. Equi-Join

 A type of theta join where the condition is only equality (=).

 Duplicates common attributes.

Example:Employee ⋈ Employee.dept_id = Department.dept_id Department

C. Natural Join (⋈)

 A special case of equi-join.

 Automatically joins on attributes with the same name and data type.
 Removes duplicate columns.

Syntax:R ⋈ S

If both Employee and Department have dept_id, then:Employee ⋈

Example:

Department
→ Joins automatically on dept_id.

SELECT * FROM Employee

JOIN Department ON Employee.dept_id = Department.dept_id;

2. DIVISION Operation

The DIVISION (÷) operation is used when we want to find tuples in one
relation that are associated with all tuples in another relation.

Purpose: Used for queries like:

"Find students who enrolled in all courses."

Syntax:R ÷ S

 R has attributes (A, B)

 S has attributes (B)
 Result: attributes from R that are related to all values in S

⋈
Operation Symbol Purpose
JOIN Combines related tuples from two tables
DIVISION ÷ Matches tuples with all values in another table