UNIT2: Introduction
● Introduction to the Relational Model: Structure of RDBMS
● Integrity Constraints over Relations
● Querying Relational Data
● Relational Algebra
● Relational Calculus
● Introduction to SQL: Data Definition commands, Data Manipulation Commands
● Aggregate Operations
● Set operations, Join Operations
● Sub queries and correlated queries,Views
● Triggers
● Cursors, Embedded SQL, Overview of NoSQL database.
Difference between DBMS and RDBMS
• DBMS (Database Management System) is a broad category of
software for managing databases, while RDBMS (Relational Database
Management System) is a specific type of DBMS that uses tables and
relationships to organize data.
• Essentially, RDBMS is a subset of DBMS, with the key difference
being that RDBMS relies on a relational model (tables, rows, and
columns) for data storage and management.
Difference between DBMS and RDBMS
• DBMS: Can store data in different formats, including files, hierarchical
structures, or using a relational model.
• Examples:
XML databases, hierarchical databases, and some older file-based
systems.
• RDBMS: Uses tables (relations) with rows (records) and columns
(fields) to organize data.
• Examples:
MySQL, PostgreSQL, Oracle, SQL Server, and Microsoft Access.
Introduction to the Relational Model: Structure of RDBMS
The relational model represents the database as a collection of
relations. A relation is nothing but a table of values.
Every row in the table represents a collection of related data
values. These rows in the table denote a real-world entity or
relationship.
Some popular Relational Database management systems are:
DB2 and Informix Dynamic Server - IBM
Oracle and RDB – Oracle
SQL Server and Access - Microsoft
Relational Model Concepts
Attribute: Each column in a Table. Attributes are the properties which define a
relation. e.g., Student_Rollno, NAME,etc.
Tables – In the Relational model the, relations are saved in the table format. It is stored
along with its entities. A table has two properties rows and columns. Rows represent
records and columns represent attributes.
Tuple – It is nothing but a single row of a table, which contains a single record.
Relation Schema: A relation schema represents the name of the relation with its
attributes.
Degree: The total number of attributes which in the relation is called the degree of the
relation.
Cardinality: Total number of rows present in the Table.
Relational Model Concepts
Relation instance – Relation instance is a finite set of tuples in the RDBMS
system. Relation instances never have duplicate tuples.
Relation key - unique identification.
domain – In a table what are the values are present on each row and coloum that
values are called domain.
Codd’s rules
Dr Edgar F. Codd, after his extensive research on the Relational Model of
database systems, came up with twelve rules of his own.
A database must obey in order to be regarded as a true relational database.
• Rules:
• Rule 1: Information Rule
• Rule 2: Guaranteed Access Rule
• Rule 3: Systematic Treatment of NULL Values
• Rule 4: Active Online Catalog
• Rule 5: Comprehensive Data Sub-Language Rule
• Rule 6: View Updating Rule
• Rule 7: High-Level Insert, Update, and Delete Rule
• Rule 8: Physical Data Independence
• Rule 9: Logical Data Independence
• Rule 10: Integrity Independence
• Rule 11: Distribution Independence
• Rule 12: Non-Subversion Rule
Conversion of ER model to Relation model
• ER diagrams can be mapped to relational schema, that is, it is possible to create
relational schema using ER diagram.
• Mainly used Mapping diagrams to convert it into relational model
1) Mapping Entity:
Ex:
Mapping Process (Algorithm):
• Create table for each entity.
• Entity's attributes should become fields of tables with their respective data types.
• Declare primary key.
Conversion of ER model to Relation model
2) Mapping Relationship:
Ex:
Mapping Process:
• Create table for a relationship.
• Add the primary keys of all participating Entities as fields of table with their
respective data types.
• If relationship has any attribute, add each attribute as field of table.
• Declare a primary key composing all the primary keys of participating entities.
• Declare all foreign key constraints.
Conversion of ER model to Relation model
• Mapping Weak Entity Sets:
• Ex:
Mapping Process:
• Create table for weak entity set.
• Add all its attributes to table as field.
• Add the primary key of identifying entity set.
• Declare all foreign key constraints.
Conversion of ER model to Relation model
• Mapping Hierarchical Entities:
• Ex:
• Mapping Process:
• Create tables for all higher-level entities.
• Create tables for lower-level entities.
• Add primary keys of higher-level entities in the table of lower-level entities.
• In lower-level tables, add all other attributes of lower-level entities.
• Declare primary key of higher-level table and the primary key for lower-level table.
• Declare foreign key constraints.
Example
Table Structure
Relational Algebra
-Relational database systems are equipped with a query language that can assist its users to
query the database instances.
-There are two kinds of query languages − relational algebra and relational calculus.
-The relational algebra is a procedural query language.
-It consists of a set of operations that take one or two relations as input and produce a new
relation as their result.
Basic Relational Algebra Operations
• Relational Algebra divided in various groups
Unary Relational Operations
• SELECT (symbol: σ)
• PROJECT (symbol: π)
• RENAME (symbol:ρ )
Relational Algebra Operations From Set Theory
• UNION (υ)
• INTERSECTION (∩)
• DIFFERENCE (-)
• CARTESIAN PRODUCT ( x )
Binary Relational Operations
• JOIN(⋈)
• DIVISION(÷)
Relational Algebra
• Select Operator (σ):
• Select Operator is denoted by sigma (σ) and it is used to find the tuples (or rows) in a
relation (or table) which satisfy the given condition.
• Syntax:
• σ Condition/Predicate(Relation/Table name)
• Example:
Name rollno age
σ rollno= 21 (student) Rani 21 18
Rama 22 19
Radha 23 17
output:
Rani 21 18
Name rollno age
Rani 21 18
Rani 21 18
Relational Algebra
• Project Operator (∏):
• Project operator is denoted by ∏ symbol and it is used to select desired columns
(or attributes) from a table (or relation).
• Syntax:
• ∏ column_name1, column_name2, ...., column_nameN(table_name)
• Example:
• Π name,age (student)
• Π name (σ rollno= 21 (student) )
Name
Rani
Relational Algebra
• Rename (ρ):
• Rename (ρ) operation can be used to rename a relation or an attribute of a
relation.
• Rename (ρ) Syntax:
ρ(new_relation_name, old_relation_name)
• Example:
• ρ(student_IIB, student)
Relational Algebra
• Cartesian product (X)
• Cartesian Product is denoted by X symbol.
• Lets say we have two relations R1 and R2 then the cartesian product of these
two relations (R1 X R2) would combine each tuple of first relation R1 with the
each tuple of second relation R2.
• Syntax of Cartesian product (X)
• R1 X R2
• Ex:
• σauthor = ‘Korth'(Books Χ Articles)
Set Operations
• CREATE TABLE students_cse (name VARCHAR(30),dept VARCHAR(10));
• CREATE TABLE students_mech (name VARCHAR(30), dept VARCHAR(10));
• INSERT INTO students_cse VALUES ('Ravi', 'CSE'),('Priya', 'CSE'),('Anil', 'CSE'),('Sita',
'CSE');
• INSERT INTO students_mech VALUES ('Ravi', 'MECH'), ('Kiran', 'MECH'),('Sita', 'MECH'),
• ('Rohit', 'MECH’);
UNION
SELECT name, dept FROM students_cse UNION SELECT name, dept FROM
students_mech;
INTERSECT
(A)SELECT name, dept FROM students_cse INTERSECT SELECT name, dept FROM
students_mech;
(B)SELECT name FROM students_cse INTERSECT SELECT name FROM
students_mech;
-- Oracle Syntax
SELECT name, dept FROM students_cse
MINUS
SELECT name, dept FROM students_cse MINUS SELECT name, dept FROM
students_mech;
Out put UNION
name dept
Ravi CSE
Priya CSE
Anil CSE
Sita CSE
Ravi MECH
Kiran MECH
Sita MECH
Rohit MECH
INTERSECT(A)
name dept
(No rows)
INTERSECT(B)
name
Ravi
Sita
MINUS
name dept
Ravi CSE
Priya CSE
Anil CSE
Sita CSE
Relational Algebra
Set Operations:
In relational algebra set operations are used to manipulate and combine set of rows
from tables.
• Union Operator (∪)
• Union operator is denoted by ∪ symbol and it is used to select all the rows
(tuples) from two tables (relations).
• Syntax of Union Operator (∪)
• table_name1 ∪ table_name2
• Ex:
• ∏ Student_Name (COURSE) ∪ ∏ Student_Name (STUDENT)
Relational Algebra
• Intersection Operator (∩)
• Intersection operator is denoted by ∩ symbol and it is used to select common
rows (tuples) from two tables (relations).
• Syntax of Intersection Operator (∩)
• table_name1 ∩ table_name2
• Ex:
• ∏ Student_Name (COURSE) ∩ ∏ Student_Name (STUDENT)
Relational Algebra
• Set Difference (-)
• Lets say we have two relations R1 and R2 and we want to select all those tuples (rows)
that are present in Relation R1 but not present in Relation R2, this can be done using Set
difference R1 – R2.
• Syntax of Set Difference (-)
• table_name1 - table_name2
• Ex:
• ∏ Student_Name (STUDENT) - ∏ Student_Name (COURSE)
Relational Algebra
• Join Operations:
• Join operation is essentially a cartesian product followed by a
selection criterion.
• Join operation denoted by ⋈.
• JOIN operation also allows joining variously related tuples from
different relations.
Types of JOIN:
• Inner Joins: Outer join:
• Theta join Left Outer Join
• EQUI join Right Outer Join
• Natural join Full Outer Join
Relational Algebra
• Inner Join:
• In an inner join, only those tuples that satisfy the matching criteria are included, while
the rest are excluded.
1) Theta Join: (conditional join)
• The general case of JOIN operation is called a Theta join. It is denoted by symbol θ
• Syntax:
• A ⋈θ B
• Theta join can use any conditions in the selection criteria.
• Example
• student ⋈ deptno = did (department)
Relational Algebra
2) EQUI join:
• When a theta join uses only equivalence condition, it becomes a equi join.
• The equi join uses the comparison operator(=).
• Ex:
• A ⋈ A.column 2 = B.column 2 (B)
Relational Algebra
3)Natural Join:
• A natural join is the set of tuples of all combinations in R and S that are equal on their
common attribute names.
• It is denoted by ⋈.
• Ex: Employee Salary
EMP_CODE EMP_NAME EMP_CODE SALARY
101 50000
101 Stephan
102 30000
102 Jack
103 25000
103 Harry
• Result is: ∏EMP_NAME, SALARY (EMPLOYEE ⋈ SALARY)
EMP_NAME SALARY
Stephan 50000
Jack 30000
Harry 25000
Relational Algebra
• Outer Join:
• The outer join operation is an extension of the join operation. It is
used to deal with missing information.
1) Left outer join:
• Left outer join contains the set of tuples of all combinations in R
and S that are equal on their common attribute names.
• In the left outer join, tuples in R have no matching tuples in S.
• It is denoted by ⟕.
Example
• EMPLOYEE FACT_WORKERS
EMP_NAME STREET CITY EMP_NAME BRANCH SALARY
Ram Civil line Mumbai
Shyam Park street Kolkata
Ram Infosys 10000
Ravi M.G. Street Delhi
Shyam Wipro 20000
Hari Nehru nagar Hyderabad
Kuber HCL 30000
Hari TCS 50000
• Left outer join:
• EMPLOYEE ⟕ FACT_WORKERS
EMP_NAME STREET CITY BRANCH SALARY
Ram Civil line Mumbai Infosys 10000
Shyam Park street Kolkata Wipro 20000
Hari Nehru Hyderabad TCS 50000
street
Ravi M.G. Street Delhi NULL NULL
Relational Algebra
2) Right outer join:
• Right outer join contains the set of tuples of all combinations in R and S that are equal on
their common attribute names.
• In right outer join, tuples in S have no matching tuples in R.
• It is denoted by ⟖.
• Ex:
• EMPLOYEE ⟖ FACT_WORKERS
EMP_NAME BRANCH SALARY STREET CITY
Ram Infosys 10000 Civil line Mumbai
Shyam Wipro 20000 Park street Kolkata
Hari TCS 50000 Nehru Hyderabad
street
Kuber HCL 30000 NULL NULL
Relational Algebra
3)Full outer join:
• Full outer join is like a left or right join except that it contains all rows from
both tables.
• In full outer join, tuples in R that have no matching tuples in S and tuples in S
that have no matching tuples in R in their common attribute name.
• It is denoted by ⟗.
• Ex: EMPLOYEE ⟗ FACT_WORKERS
EMP_NAME STREET CITY BRANCH SALARY
Ram Civil line Mumbai Infosys 10000
Shyam Park street Kolkata Wipro 20000
Hari Nehru Hyderabad TCS 50000
street
Ravi M.G. Street Delhi NULL NULL
Kuber NULL NULL HCL 30000
Views
• Views in SQL are considered as a virtual
table. A view also contains rows and
columns.
• To create the view, we can select the
fields from one or more tables present in
the database.
• A view can either have specific rows
based on certain condition or all the rows
of a table.
• Operations on View:
• 1.Create View
• 2.Update View
• 3.Delete View
Sample table:
StudentMarks
• Student_Detail
• -- Create StudentDetails table
• CREATE TABLE StudentDetails ( S_ID INT PRIMARY KEY, NAME VARCHAR(255), ADDRESS
VARCHAR(255));
• INSERT INTO StudentDetails (S_ID, NAME, ADDRESS)
• VALUES
• (1, 'Harsh', 'Kolkata'),
• (2, 'Ashish', 'Durgapur'),
• (3, 'Pratik', 'Delhi'),
• (4, 'Dhanraj', 'Bihar'),
• (5, 'Ram', 'Rajasthan');
• -- Create StudentMarks tableCREATE TABLE StudentMarks ( ID INT PRIMARY KEY, NAME
VARCHAR(255), Marks INT, Age INT);
• INSERT INTO StudentMarks (ID, NAME, Marks, Age)
• VALUES (1, 'Harsh', 90, 19), (2, 'Suresh', 50, 20), (3, 'Pratik', 80, 19), (4, 'Dhanraj', 95, 21), (5, 'Ram', 85,
18);
• CREATE VIEWS in SQL
• We can create a view using CREATE VIEW statement. A View can be created
from a single table or multiple tables.
• Syntax
• CREATE VIEW view_name AS SELECT column1, column2.....FROM
table_name WHERE condition;
• Parameters:
• view_name: Name for the View
• table_name: Name of the table
• condition: Condition to select rows
• Example 1: Creating View from a single table
• In this example, we will create a View named DetailsView from the table
StudentDetails. Query:
• CREATE VIEW DetailsView AS SELECT NAME, ADDRESS
FROM StudentDetails WHERE S_ID < 5;
• To see the data in the View, we can query the view in the same manner as we
query a table.
• SELECT * FROM DetailsView; Student_Detail
Creating View from multiple tables
• CREATE VIEW MarksView AS SELECT StudentDetails.NAME,
StudentDetails.ADDRESS, StudentMarks.MARKS FROM StudentDetails,
StudentMarks WHERE StudentDetails.NAME = StudentMarks.NAME;
To display data of View MarksView:
SELECT * FROM MarksView;
StudentMarks StudentDetails
UPDATE VIEW in SQL
CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
CREATE OR REPLACE VIEW MarksView AS
SELECT StudentDetails.NAME, StudentDetails.ADDRESS, StudentMarks.MARKS,
StudentMarks.AGE
FROM StudentDetails, StudentMarks
WHERE StudentDetails.NAME = StudentMarks.NAME;
SELECT * FROM MarksView;
• DELETE VIEWS in SQL
• SQL allows us to delete an existing View. We can delete or drop View using the DROP
statement.
• Syntax
• DROP VIEW view_name;
• Example
• In this example, we are deleting the View MarksView.
• DROP VIEW MarksView;
• Advantages of View:
• Complexity: Views help to reduce the complexity. Different views can be created on the
same base table for different users.
• Security: It increases the security by excluding the sensitive information from the view.
• Storage Capacity: Views take very little space to store the data.
SQL Sub Query
• Subquery or Inner query or Nested query is a query in a query.
• SQL subquery is usually added in the WHERE Clause of the SQL statement.
• Subqueries are an alternate way of returning data from multiple tables.
Subqueries can be used with the following SQL statements along with the comparision
operators like =, <, >, >=, <= and IN , NOT IN ,ANY, ALL, EXISTS and NOT
EXISTS etc.
Ex:
Select ename from employee where salary=(select max(salary)
from employee);
SQL Sub Query
• Syntax:
• SELECT column_name , column_name
FROM table1,table2
WHERE column_name OPERATOR
(SELECT column_name , column_name
FROM table1 , table2 );
• Example:
• Find the details of all employee who works in any department
• Select * from emp where eid IN(select eid from dept);
SQL Correlated Sub Query
• A query is called correlated sub query when both the inner query and the outer
query are interdependent.
• For every row processed by the inner query, the outer query is processed as
well.
• Example:
• Find the details of all employee who works in any department
• Select * from emp where exists(select eid from dept where emp.eid = dept.eid);
Triggers
• Triggers are stored programs, which are automatically executed or fired when
some events occur.
• Triggers are, in fact, written to be executed in response to any of the following events
−
• A database manipulation (DML) statement (DELETE, INSERT, or UPDATE)
• A database definition (DDL) statement (CREATE, ALTER, or DROP).
• A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or
SHUTDOWN).
A trigger has three basic parts:
• A triggering event or statement
• A trigger restriction or condition
• A trigger action
Triggers
• The syntax for creating a trigger is −
CREATE [OR REPLACE ] TRIGGER trigger_name
{BEFORE | AFTER }trigger event ON table name
[FOR EACH ROW]
WHEN (condition)
DECLARE
Declaration-statements
BEGIN
Executable-statements
EXCEPTION
Exception-handling-statements
END;
Cursors
• Oracle creates a memory area, known as the context area, for processing an SQL
statement.
• A cursor is a pointer to the context area.
• PL/SQL controls the context area through a cursor.
• A cursor holds the rows (one or more) returned by a SQL statement. The set of rows the
cursor holds is referred to as the active set.
• There are two types of cursors −
• Implicit cursors
• Explicit cursors
Cursors
1) Implicit Cursors:
• Implicit cursors are automatically created by Oracle whenever an SQL statement is
executed.
• implicit cursor is associated with attributes such as %FOUND, %ISOPEN,
%NOTFOUND, and %ROWCOUNT.
2) Explicit cursors:
• Explicit cursors are programmer-defined cursors for gaining more control over
the context area.
• An explicit cursor should be defined in the declaration section of the PL/SQL Block.
Cursors
Syntax:
• Working with an explicit cursor includes the following steps −
• Declaring the Cursor
• CURSOR cursor_name IS select_statement;
• Opening the Cursor
• OPEN cursor_name ;
• Fetching the Cursor
• FETCH cursor_name INTO col1, col2, col3;
• Closing the Cursor
• CLOSE cursor_name;
