DATABASE MANAGEMENT
SYSTEM
Dr. Rashmi K. Thakur
Structured Query
Language
Referential Integrity
Dr. Rashmi Thakur - TCET
Foreign Key Concept in Database
Dr. Rashmi Thakur - TCET
How SQL Handles Referential Integrity
• CREATE TABLE Student ( Stud_id INT PRIMARY KEY, name
VARCHAR(255) NOT NULL, branch VARCHAR(255) NOT NULL, city
varchar(255) )
• CREATE TABLE marks ( stud_id INT,subject VARCHAR(255) NOT NULL,
marks int, FOREIGN KEY (Stud_id) REFERENCES Student (Stud_id) )
CREATE TABLE marks ( stud_id INT,subject VARCHAR(255) NOT NULL,
marks int, FOREIGN KEY (Stud_id) REFERENCES Student (Stud_id) ON
DELETE CASCADE On Update Cascade )
Dr. Rashmi Thakur - TCET
Sub Queries
Concept of Sub Query
Dr. Rashmi Thakur - TCET
Set Membership - IN Keyword
Dr. Rashmi Thakur - TCET
Another Example of IN Keyword
Dr. Rashmi Thakur - TCET
Another Example of IN Keyword
Dr. Rashmi Thakur - TCET
Another Example of NOT IN Keyword
Dr. Rashmi Thakur - TCET
Set Comparison in Sub Queries – Some
Keyword
Dr. Rashmi Thakur - TCET
Set Comparison in Sub Queries – All
Keyword
Set Comparison Example
Branch ( branch_name,city,assests)
1) Find the names of all branches that have assets greater than those of
atleast one branch located in Mumbai
2) Find the names of all branches that have assets greater than each branch
located in Mumbai
Dr. Rashmi Thakur - TCET
Exists Keyword in Sub Queries
Dr. Rashmi Thakur - TCET
Not Exists Keyword in Sub Query
Dr. Rashmi Thakur - TCET
Joins in SQL
Inner and Outer Joins(Left and Right)
Dr. Rashmi Thakur - TCET
Natural Join
Dr. Rashmi Thakur - TCET
Cross Join (Cartesian Product)
Dr. Rashmi Thakur - TCET
Assertion
Dr. Rashmi Thakur - TCET
Trigger
Dr. Rashmi Thakur - TCET
Database Security and Authorization
Dr. Rashmi Thakur - TCET
Privileges in SQL
Dr. Rashmi Thakur - TCET
Types of Authorization
• Read authorization – allows reading, but not modification of data.
• Insert authorization – allows insertion of new data, but not
modification of existing data.
• Update authorization – allows modification, but not deletion of data.
• Delete authorization – allows deletion of data.
Dr. Rashmi Thakur - TCET
Granting and Revoking of Authorization in
SQL-Authorization Graph
If DBA revokes grant from U1:
–Grant must be revoked from U4 since U1 no
longer has authorization
– Grant must not be revoked from U5 since U5
has another authorization path from DBA
through U2
E.g:- grant select on branch to U1, U2, U3
grant select on branch to U1 with grant
option
revoke select on branch from U1, U2, U3
cascade
Dr. Rashmi Thakur - TCET
Views
Views in SQL
Foreign Key
Primary Key Marks Table
Student Table
Creating View from Multiple Table
Creating View from Single Table
CREATE VIEW MarksView AS
SELECT StudentDetails.NAME, StudentDetails.ADDRESS,
CREATE VIEW DetailsView AS SELECT NAME,
StudentMarks.MARKS
ADDRESS FROM StudentDetails WHERE S_ID < 5
FROM StudentDetails, StudentMarks
WHERE StudentDetails.NAME = StudentMarks.NAME
Dr. Rashmi Thakur - TCET
Fundamental Relational Algebra Operations
Dr. Rashmi Thakur - TCET
Fundamental Relational Algebra Operations
Dr. Rashmi Thakur - TCET
Fundamental Relational Algebra Operations
Dr. Rashmi Thakur - TCET
Fundamental Relational Algebra Operations
Dr. Rashmi Thakur - TCET
Fundamental Relational Algebra Operations
Dr. Rashmi Thakur - TCET
Fundamental Relational Algebra Operations
Dr. Rashmi Thakur - TCET
Practice Queries
Take Away of Todays Session
Referential Sub Queries Views Practice
Integrity Queries
On Delete Set Views from
Cascade Membership Single Table
On Update Set Views from
Cascade Comparision Multiple
Table
Dr. Rashmi Thakur - TCET
