Unit 2: Relational data Model and Language

Relational Data Model and Relational Language

Relational Data Model Concepts

The Relational Data Model represents data in the form of tables (relations). It was proposed by E. F. Codd.

Basic Terminology

Term	Meaning
Relation	Table
Tuple	Row
Attribute	Column
Degree	Number of attributes
Cardinality	Number of tuples
Domain	Set of valid values for an attribute

Structure of a Relation

A relation consists of:

• Relation name
• Attributes with domains
• Tuples (records)

Example: STUDENT (RollNo, Name, Course, Marks)

Integrity Constraints

Integrity constraints are rules that ensure correctness and consistency of data in a database.

Types of Integrity Constraints

1. Domain Constraints
2. Key Constraints
3. Entity Integrity
4. Referential Integrity

Domain Constraints

• Ensure that attribute values come from a valid domain
• Prevents invalid data entry

Example: Marks must be between 0 and 100

Marks INT CHECK (Marks BETWEEN 0 AND 100)

Key Constraints

Key constraints ensure uniqueness of data.

Types of Keys

Key Type	Description
Super Key	Uniquely identifies tuples
Candidate Key	Minimal super key
Primary Key	Selected candidate key
Alternate Key	Candidate key not selected
Foreign Key	References primary key of another relation

Entity Integrity Constraint

• Ensures that primary key cannot be NULL
• Every relation must have a primary key

Example: RollNo in STUDENT table cannot be NULL.

RollNo INT NOT NULL PRIMARY KEY

Referential Integrity Constraint

• Ensures consistency between related tables

Foreign key must:

• Match primary key in referenced table, or
• Be NULL

Example

STUDENT (RollNo, Name, DeptID)
DEPARTMENT (DeptID, DeptName)

FOREIGN KEY (DeptID) REFERENCES DEPARTMENT(DeptID)

Actions on Referential Integrity

Action	Meaning
CASCADE	Changes propagate
SET NULL	Foreign key set to NULL
RESTRICT	Prevents change
NO ACTION	Similar to RESTRICT

Relational Algebra

Relational Algebra is a procedural query language.

Characteristics

• Uses operators
• Specifies how to retrieve data
• Foundation of SQL

Basic Relational Algebra Operators

Operator	Symbol	Description
Selection	σ	Select rows
Projection	π	Select columns
Union	∪	Combine relations
Set Difference	−	Subtract relations
Cartesian Product	×	Combine tuples

Additional Operators

Operator	Symbol
Join	⋈
Intersection	∩
Division	÷
Rename	ρ

Example

Find students from MCA course:

σ Course = 'MCA' (STUDENT)

Relational Calculus

Relational Calculus is a non-procedural query language.

Characteristics

• Specifies what data is required
• Does not specify retrieval method
• Based on predicate logic

Types of Relational Calculus

1. Tuple Relational Calculus (TRC)
2. Domain Relational Calculus (DRC)

Tuple Relational Calculus (TRC)

• Variables represent tuples
• Queries written using tuple variables

General Form

{ t | P(t) }

Where:

• t = tuple variable
• P(t) = predicate condition

Example

Find names of students in MCA:

{ t.Name | STUDENT(t) ∧ t.Course = 'MCA' }

Domain Relational Calculus (DRC)

• Variables represent domains (attribute values)
• Closer to SQL

General Form

{ <x1, x2, ..., xn> | P(x1, x2, ..., xn) }

Example

Find RollNo and Name of MCA students:

{ <r, n> | ∃c, m (STUDENT(r, n, c, m) ∧ c = 'MCA') }

Difference Between Relational Algebra and Relational Calculus

Basis	Relational Algebra	Relational Calculus
Nature	Procedural	Non-procedural
Focus	How to get data	What data to get
Foundation	SQL execution	SQL logic
Complexity	Easier to understand	More theoretical

Exam-Focused Summary (MCA)

• Relational model stores data in tables
• Integrity constraints maintain data correctness
• Entity integrity ensures primary key not NULL
• Referential integrity maintains table relationships
• Relational algebra is procedural
• Relational calculus is non-procedural
• TRC uses tuples; DRC uses domains

Introduction to SQL (Structured Query Language)

SQL (Structured Query Language) is a standard database language used to create, access, manipulate, and control data in a relational database.

• Developed by IBM
• Standardized by ANSI and ISO
• Used in DBMS like MySQL, Oracle, SQL Server, PostgreSQL

Characteristics of SQL

Characteristic	Explanation
Declarative Language	Specifies what data is needed, not how
Non-Procedural	No need to define step-by-step process
User Friendly	Easy to learn and use
Standard Language	Same syntax across most DBMS
Supports Multiple Users	Allows concurrent access
Integrated with Programming Languages	Works with Java, Python, C, etc.
Secure	Provides access control
Portable	Works on different platforms

Advantages of SQL

Advantage	Description
Easy Data Access	Retrieve data using simple commands
Reduced Coding	Fewer lines compared to traditional programming
High Performance	Efficient query processing
Data Security	Grant and revoke privileges
Data Integrity	Enforces constraints
Scalability	Handles large databases
Transaction Control	Supports commit and rollback

SQL Data Types and Literals

SQL Data Types

SQL data types define type of data that can be stored in a column.

Numeric Data Types

Data Type	Description
INT / INTEGER	Whole numbers
SMALLINT	Small range integers
BIGINT	Large range integers
DECIMAL(p,s)	Fixed-point numbers
FLOAT / REAL	Floating-point numbers

Character / String Data Types

Data Type	Description
CHAR(n)	Fixed-length string
VARCHAR(n)	Variable-length string
TEXT	Large text data

Date & Time Data Types

Data Type	Description
DATE	Stores date
TIME	Stores time
DATETIME	Date and time
TIMESTAMP	Date and time with timezone

Boolean Data Type

Data Type	Description
BOOLEAN	TRUE or FALSE

---

SQL Literals

A literal is a fixed value used in SQL statements.

Types of Literals

Literal Type	Example
Numeric Literal	100, 45.5
String Literal	'MCA', 'Jay'
Date Literal	'2025-01-01'
Boolean Literal	TRUE, FALSE
NULL Literal	NULL

Types of SQL Commands

SQL commands are classified based on their functionality.

DDL – Data Definition Language

Used to define and modify database structure.

Command	Use
CREATE	Create tables/databases
ALTER	Modify structure
DROP	Delete structure
TRUNCATE	Remove all records
RENAME	Rename object

DML – Data Manipulation Language

Used to insert, update, and delete data.

Command	Use
INSERT	Add new records
UPDATE	Modify existing data
DELETE	Remove records
SELECT	Retrieve data

DCL – Data Control Language

Used to control user access.

Command	Use
GRANT	Provide permissions
REVOKE	Remove permissions

TCL – Transaction Control Language

Used to manage transactions.

Command	Use
COMMIT	Save changes
ROLLBACK	Undo changes
SAVEPOINT	Set rollback point

SQL Operators and Their Procedure

SQL operators are used to perform operations on data.

Arithmetic Operators

Operator	Meaning
+	Addition
-	Subtraction
*	Multiplication
/	Division
%	Modulus

Example: SELECT Marks + 5 FROM Student;

Comparison Operators

Operator	Meaning
=	Equal to
!= or <>	Not equal
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal

Logical Operators

Operator	Meaning
AND	All conditions true
OR	Any condition true
NOT	Negates condition

Set Operators

Operator	Description
UNION	Combines result sets
UNION ALL	Includes duplicates
INTERSECT	Common records
EXCEPT / MINUS	Difference

Special Operators

Operator	Use
BETWEEN	Range check
IN	Match list
LIKE	Pattern matching
IS NULL	Null check
EXISTS	Subquery result

Example: SELECT * FROM Student WHERE Name LIKE 'A%';

Operator Precedence (Procedure)

SQL evaluates operators in the following order:

1. Arithmetic
2. Comparison
3. Logical (NOT → AND → OR)

Exam-Focused Summary (MCA)

• SQL is a standard relational database language
• SQL is declarative and non-procedural
• Data types define storage format
• SQL commands are categorized into DDL, DML, DCL, TCL
• Operators perform arithmetic, logical, and comparison operations

Introduction to SQL (Structured Query Language)

SQL (Structured Query Language) is a standard database language used to create, access, manipulate, and control data in a relational database.

• Developed by IBM
• Standardized by ANSI and ISO
• Used in DBMS like MySQL, Oracle, SQL Server, PostgreSQL

Characteristics of SQL

Characteristic	Explanation
Declarative Language	Specifies what data is needed, not how
Non-Procedural	No need to define step-by-step process
User Friendly	Easy to learn and use
Standard Language	Same syntax across most DBMS
Supports Multiple Users	Allows concurrent access
Integrated with Programming Languages	Works with Java, Python, C, etc.
Secure	Provides access control
Portable	Works on different platforms

Advantages of SQL

Advantage	Description
Easy Data Access	Retrieve data using simple commands
Reduced Coding	Fewer lines compared to traditional programming
High Performance	Efficient query processing
Data Security	Grant and revoke privileges
Data Integrity	Enforces constraints
Scalability	Handles large databases
Transaction Control	Supports commit and rollback

SQL Data Types and Literals

SQL Data Types

SQL data types define type of data that can be stored in a column.

Numeric Data Types

Data Type	Description
INT / INTEGER	Whole numbers
SMALLINT	Small range integers
BIGINT	Large range integers
DECIMAL(p,s)	Fixed-point numbers
FLOAT / REAL	Floating-point numbers

Character / String Data Types

Data Type	Description
CHAR(n)	Fixed-length string
VARCHAR(n)	Variable-length string
TEXT	Large text data

Date & Time Data Types

Data Type	Description
DATE	Stores date
TIME	Stores time
DATETIME	Date and time
TIMESTAMP	Date and time with timezone

Boolean Data Type

Data Type	Description
BOOLEAN	TRUE or FALSE

---

SQL Literals

A literal is a fixed value used in SQL statements.

Types of Literals

Literal Type	Example
Numeric Literal	100, 45.5
String Literal	'MCA', 'Jay'
Date Literal	'2025-01-01'
Boolean Literal	TRUE, FALSE
NULL Literal	NULL

Types of SQL Commands

SQL commands are classified based on their functionality.

DDL – Data Definition Language

Used to define and modify database structure.

Command	Use
CREATE	Create tables/databases
ALTER	Modify structure
DROP	Delete structure
TRUNCATE	Remove all records
RENAME	Rename object

DML – Data Manipulation Language

Used to insert, update, and delete data.

Command	Use
INSERT	Add new records
UPDATE	Modify existing data
DELETE	Remove records
SELECT	Retrieve data

DCL – Data Control Language

Used to control user access.

Command	Use
GRANT	Provide permissions
REVOKE	Remove permissions

TCL – Transaction Control Language

Used to manage transactions.

Command	Use
COMMIT	Save changes
ROLLBACK	Undo changes
SAVEPOINT	Set rollback point

SQL Operators and Their Procedure

SQL operators are used to perform operations on data.

Arithmetic Operators

Operator	Meaning
+	Addition
-	Subtraction
*	Multiplication
/	Division
%	Modulus

Example: SELECT Marks + 5 FROM Student;

Comparison Operators

Operator	Meaning
=	Equal to
!= or <>	Not equal
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal

Logical Operators

Operator	Meaning
AND	All conditions true
OR	Any condition true
NOT	Negates condition

Set Operators

Operator	Description
UNION	Combines result sets
UNION ALL	Includes duplicates
INTERSECT	Common records
EXCEPT / MINUS	Difference

Special Operators

Operator	Use
BETWEEN	Range check
IN	Match list
LIKE	Pattern matching
IS NULL	Null check
EXISTS	Subquery result

Example: SELECT * FROM Student WHERE Name LIKE 'A%';

Operator Precedence (Procedure)

SQL evaluates operators in the following order:

1. Arithmetic
2. Comparison
3. Logical (NOT → AND → OR)

Exam-Focused Summary (MCA)

• SQL is a standard relational database language
• SQL is declarative and non-procedural
• Data types define storage format
• SQL commands are categorized into DDL, DML, DCL, TCL
• Operators perform arithmetic, logical, and comparison operations

Tables

A table is the basic storage structure in a relational database.

Characteristics

• Data stored in rows (tuples) and columns (attributes)
• Each table has a primary key
• Data type defined for each column

Create Table Example

CREATE TABLE Student (
    RollNo INT PRIMARY KEY,
    Name VARCHAR(50),
    Course VARCHAR(20),
    Marks INT
);

Views

A view is a virtual table based on the result of a SQL query.

Characteristics

• Does not store data physically
• Improves security
• Simplifies complex queries

Create View Example

CREATE VIEW MCA_Students AS
SELECT RollNo, Name
FROM Student
WHERE Course = 'MCA';

Advantages of Views

• Data abstraction
• Restricted data access
• Query reusability

Indexes

An index improves the speed of data retrieval.

Characteristics

• Created on columns
• Uses additional storage
• Slows INSERT/UPDATE slightly

Create Index Example

CREATE INDEX idx_rollno
ON Student(RollNo);

Types of Indexes

Type	Description
Simple Index	Single column
Composite Index	Multiple columns
Unique Index	No duplicate values

Queries and Subqueries

Queries

A query retrieves data from database.

SELECT * FROM Student WHERE Marks > 70;

Subqueries

A subquery is a query inside another query.

Types

1. Single-row subquery
2. Multiple-row subquery
3. Correlated subquery

Example

SELECT Name
FROM Student
WHERE Marks > (SELECT AVG(Marks) FROM Student);

Aggregate Functions

Aggregate functions perform calculations on a set of values.

Common Aggregate Functions

Function	Use
COUNT()	Number of rows
SUM()	Total
AVG()	Average
MAX()	Maximum
MIN()	Minimum

Example SELECT AVG(Marks) FROM Student;

GROUP BY Clause

Used with aggregate functions.

SELECT Course, AVG(Marks)
FROM Student
GROUP BY Course;

HAVING Clause

Used to apply condition on groups.

SELECT Course, AVG(Marks)
FROM Student
GROUP BY Course
HAVING AVG(Marks) > 70;

Insert, Update and Delete Operations (DML)

INSERT

INSERT INTO Student VALUES (101, 'Ravi', 'MCA', 80);

UPDATE

UPDATE Student
SET Marks = 85
WHERE RollNo = 101;

DELETE

DELETE FROM Student
WHERE RollNo = 101;

Joins

Joins are used to combine data from multiple tables.

Types of Joins

Join Type	Description
INNER JOIN	Matching records
LEFT JOIN	All from left table
RIGHT JOIN	All from right table
FULL JOIN	All records
SELF JOIN	Table joins with itself

Example (INNER JOIN)

SELECT Student.Name, Department.DeptName
FROM Student
INNER JOIN Department
ON Student.DeptID = Department.DeptID;

Set Operations

Set operations combine results of two queries.

Conditions

• Same number of columns
• Same data type
• Same order

Types of Set Operations

Operation	Meaning
UNION	Combines rows (removes duplicates)
UNION ALL	Keeps duplicates
INTERSECT	Common rows
MINUS	Rows in first query only

Example

SELECT RollNo FROM Student_2024
UNION
SELECT RollNo FROM Student_2025;

Cursors (PL-SQL)

A cursor is a pointer used to process query result row-by-row.

Types of Cursors

1. Implicit Cursor
2. Explicit Cursor

Cursor Steps

1. Declare
2. Open
3. Fetch
4. Close

Example (Explicit Cursor)

DECLARE
  CURSOR c1 IS SELECT Name FROM Student;
  v_name Student.Name%TYPE;
BEGIN
  OPEN c1;
  FETCH c1 INTO v_name;
  CLOSE c1;
END;

Triggers

A trigger is a PL-SQL block that executes automatically when an event occurs.

Trigger Events

• INSERT
• UPDATE
• DELETE

Example

CREATE TRIGGER trg_before_insert
BEFORE INSERT ON Student
FOR EACH ROW
BEGIN
  DBMS_OUTPUT.PUT_LINE('Record Inserted');
END;

Uses of Triggers

• Enforce business rules
• Audit data changes
• Automatic logging

Procedures (PL-SQL)

A procedure is a stored PL-SQL block that performs a specific task.

Characteristics

• Stored in database
• Improves performance
• Can accept parameters

Procedure Example

CREATE PROCEDURE Add_Student
AS
BEGIN
  INSERT INTO Student VALUES (102, 'Amit', 'MCA', 78);
END;

Execute Procedure

EXEC Add_Student;

Difference: Procedure vs Trigger

Basis	Procedure	Trigger
Execution	Manually called	Automatically executed
Parameters	Allowed	Not allowed
Event Based	No	Yes

Exam-Focused Summary (MCA)

• Tables store data physically
• Views are virtual tables
• Indexes improve performance
• Subqueries are nested queries
• Aggregate functions summarize data
• Joins combine tables
• Set operations combine result sets
• Cursors handle row-by-row processing
• Triggers execute automatically
• Procedures store reusable logic