Amity School of Engineering and Technology

Course Title: Course Title: Database Management System

Course Code: : CSE201

Course Objectives:

The objective of this course is to get students familiar with Databases and
their
use. They can identify different types of available database model,
concurrency
techniques and new applications of the DBMS.
Pre-requisites: Knowledge about the raw data, knowledge about the
operating system and organization
Relational Data Model

2
Relational Data Model

3
Relational Data Model

4
Relational Data Model

5
Relational Data Model

For example at time t1 we

have three tuple in the
relation student
6
DBMS keys

7
Types of keys

8
If I will ask from this table what is the email id is Adam, you will not
able to give the answer, because we have two Adam here.
9
10
 Types of keys in DBMS
• Primary key
• A primary key is the column or columns that
contain values that uniquely identify each
row in a table

11
Primary key

12
Primary key

13
Unique key

14
Unique key

15
Unique key

16
Primary key and Unique key

17
Super key

18
Super key

19
Super key??

20
Answer

21
Super key???

22
Candidate key

23
Candidate key

24
Candidate key

25
Candidate key

26
Primary Key

27
Primary Key

28
DBMS keys

29
Alternate key

30
Foreign key

31
Foreign key

Department table has

three values 1,2,3 ,
we can have same
value in the table
employee, no other
value like 4,5,6 can b
there in employee 32
Foreign key

33
Composite key

34
Composite key

35
 The catalog
• Metadata is simply defined as data about data. It
means it is a description and context of the data. It
helps to organize, find and understand data. Let me
explain to you by giving a real-world example of
metadata:
• Every time you take a photo with today’s cameras a
bunch of metadata is gathered and saved with it. Such
as
• File name,
• Size of the file,
• Date and time,
• Camera settings etc. 36
 The catalog
• Meta data in Relational database:
Relational databases store and provide access not
only data but also metadata in a structure called data
dictionary or system catalog. It holds information
about:
• tables,
• columns,
• data types,
• table relationship,
• constraints etc.
37
The catalog

38
System Catalog

39
Relational algebra

40
Relational algebra

41
Relational algebra

42
Relational Algebra

43
Relational Algebra

44
Relational Algebra

45
Relational algebra

46
 Relational algebra
• Examples on unary operator

47
Relational Algebra

And operator

48
 Try it yourself
•Question:
•Consider the following relational database schema consisting of the
four relation schemas:
• passenger ( pid, pname, pgender, pcity)
• agency ( aid, aname, acity)
• flight (fid, fdate, time, src, dest)
• booking (pid, aid, fid, fdate)
•Answer the following questions using relational algebra queries;
Q1Get the complete details of all flights to New Delhi.
Q2Get the details about all flights from Chennai to New Delhi.

49
Relational Algebra
It is used to select column from the
table

50
Relational Algebra
It is used to select column from the
table

51
Relational Algebra

52
Relational Algebra

53
Relational Algebra

54
Relational Algebra

55
Relational Alebra

Combined query of
selection and
projection

56
 Relational Algebra
Step1: we will apply selection operation

57
 Relational Algebra
Step2: we will apply projection operation

58
 Relational Algebra

Used to change
the name of the
relation

Use to change the name

of the attributes 59
Relational Algebra

60
Relational Algebra
If I want to change the
name of the specific
columns, first we
need to project them
then we can rename
it.

61
Relational Algebra

62
 Binary operator
• Union (U)
Union operation in relational algebra is
same as union operation in set theory,
only constraint is for union of two relation
both relation must have same set of
Attributes.
• For a union operation to be valid, the following
conditions must hold –
• R and S must be the same number of attributes.
• Attribute domains need to be compatible.
• Duplicate tuples should be automatically removed. 63
Relational algebra

64
Relational algebra(try it yourself)

65
solution

66
 Relational Algebra
• An intersection is defined by the symbol ∩
• A ∩ B Defines a relation consisting of a set of
all tuple that are in both A and B. However, A
and B must be union-compatible.

67
Relational Algebra

68
 Relational Algebra
• 3. Difference Operator (-)-
• Let R and S be two relations.
• Then-
• R – S is the set of all tuples belonging to R
and not to S.
• In R – S, duplicates are automatically
removed.
• Difference operation is associative but not
commutative.
69
Relational Algebra

70
Try it yourself

71
Relational Algebra

72
Relational Algebra

73
Relational Algebra

74
Relational Algebra

75
Relational Algebra

76
 Relational Algebra

Degree for the relation c is

2+3=5

Degree is 3
Degree is 2

77
 Relational Algebra

Cardinality for the relation c is

3*2=6

Cardinality
Cardinality that is
that is number of
number of rows are 2
rows are 3 78
 Relational Algebra
• Where to apply cartesian product

• When we need to join two tables and they

are not union compatible.

union operation can be applied on two

tables when they have same degree

79
 Practice questions

• Select all customers from country U.K or Germany

• Fetch all customers till customers id 19
• Fetch all customers except from country Germay, U.S and U.K

Write a query to find all such car models which have driven by all
drivers?

80
 Practice Questions

Find those account numbers which are in delhi?

81
Solution

82
 Practice questions
Que Find the customers names who have a loan from branch
having assets more than 10,000

83
Soultion

84
 Practice question

Perform the cartesian product of the two relations and find out the
instructor with only the ids matches

85
Practice question

86
 Relational Algebra
• A Join operation combines related
tuples from different relations, if and
only if a given join condition is
satisfied. It is denoted by ⋈

87
Relational Algebra

88
Relational algebra

89
Relational Algebra

90
 Relational Algebra
• Result

91
Relational Algebra

92
Relational Algebra

93
Relational algebra

94
Relational algebra

95
 Relational Algebra

Features of Natural Join :

Here, we will discuss the features of
natural join.
[Link] will perform the Cartesian product.
[Link] finds consistent tuples and deletes
96
inconsistent tuples.
What is the difference between cartesian product and join

97
Relational algebra

98
Relational Algebra

99
Relational Algebra

100
Relational Algebra

101
 Relational Algebra
• Theta (θ) Join
• Theta join combines tuples from
different relations provided they
satisfy the theta condition. The join
condition is denoted by the symbol θ.

102
Relational Algebra

103
Relational Algebra

104
Relational Algebra

105
Relational Algebra

106
 Relational Algebra
• EQUI JOIN :
• EQUI JOIN creates a JOIN for equality
or matching column(s) values of the
relative tables. EQUI JOIN also create
JOIN by using JOIN with ON and then
providing the names of the columns
with their relative tables to check
equality using equal sign (=).

107
Relational Algebra

Special case of theta join

108
Relational Algebra

109
Relational Algebra

110
Relational Algebra

111
Relational Algebra

112
Relational Algebra

113
 Relational Algebra

Here disqualified values are b 2 and d 4

114
Relational Algebra

115
Relational Algebra
Try it yourself

116
Relational Algebra

117
Relational Algebra

118
Relation Algebra

119
Relational Algebra

120
Relational Algebra

121
Relational Algebra

122
Relational Algebra

123
Relational Algebra

124
Relational Algebra

125
Relational Algebra

126
Relational Algebra

127
Try it yourself??

128
Relational Algebra

129
Try it yourself

130
Try it yourself

131
Relational Algebra

132
Relational Algebra

133
Relational Algebra

134
Relational Algebra

135
Relational Algebra

136
Example on Relational Algebra

137
Relational Algebra

138
Relational Algebra

139
Relational Algebra

140
Relational Algebra

141
Relational Algebra

142
Relational Algebra

143
Relational Algebra

144
Relational Algebra

145
Relational Algebra

146
Relational Algebra

147
Relational Algebra

148
Relational Algebra

149
Relational Algebra

150
Relational Calculus

151
Relational Calculus

152
Relational Calculus

153
Relational Calculus

154
 Relational Calculus

Here we have written not suppliers

and we have written [Link] it
means we need to find out names of
those suppliers that does not belong
to supplier table.

155
Relational Calculus

156
Relational Calculus

157
Relational Calculus

158
Relational Calculus

159
Relational Calculus

160
Relational Calculus

161
Relational Calculus

162
Relational Calculus

163
Relational Calculus

164
Relational Calculus

165
SQL fundamentals

166
SQL fundamentals

167
SQL fundamentals

168
SQL fundamentals

169
SQL fundamentals

170
SQL fundamentals

171
SQL fundamentals

172
Views In relational algebra and SQL

173
Views In relational algebra and SQL

174
Views In relational algebra and SQL

175
Views In relational algebra and SQL

176
Views In relational algebra and SQL

177
Views In relational algebra and SQL

178
Views In relational algebra and SQL

179
Views In relational algebra and SQL

180
Views In relational algebra and SQL

181
Integrity Constraints

182
Integrity Constraints

183
Integrity Constraints

184
Integrity Constraints

185
Integrity Constraints

186
Integrity Constraints

187
Integrity Constraints

188
 Integrity Constraints
Insert, Update & Delete from Foreign Key
table

If we will insert any value in the referenced table, it

will not affect the referencing table

189
 Integrity Constraints
Insert, Update & Delete from Foreign
If we delete let us say row one
Key table
from base table , it means we
are deleting record of student
A whose roll number is 1 but
if reference table record
would be there, which says
that roll number 1 is still
reading the subject DMMS ,
which is violating the integrity

190
 Integrity Constraints
Insert, Update & Delete from Foreign
Key table
But if we delete 4th row there
would be no affect on the
referencing table

191
 Integrity Constraints
Insert, Update & Delete from Foreign
Key table
If data integrity is not
maintaining on deletion from
base table than what is the
solution ??

Solution1: delete data

manually from both of the
table.

Solution 2: Delete Cascade:

With delete cascade operation

data will automatically deleted
from base table and
referencing table.

192
 Integrity Constraints
Insert, Update & Delete from Foreign
Key table
Solution III: On delete set null

It means when you delete record

from base table it will set null to
the corresponding values in the
referencing table 193
 Integrity Constraints
Insert, Update & Delete from Foreign
Key table
Solution III: On delete set null
But there may be a case that
foreign key in one table could be
primary key of that table, as in this
example roll number is foreign key
for base table but it could be
primary key for referencing table,
in that case delete set null
command will not work.

Because primary key can not be

null

194
 Integrity Constraints
Insert, Update & Delete from Foreign
Key table
Solution IV: On delete No action
with On delete no action there
would be no action on deletion.

Means no record will be deleted

from base table as well as from
referencing table

195
 Integrity Constraints
Insert, Update & Delete from Foreign
Key table
Updating records
Let us say we are updating the
record in base table , in this
example we want to update id 2 to
20, can we do this???

updating the record in base table

would not reflect in refencing table.

Solution is
We can apply

On update cascade
On update set null
On update no action
196
 Integrity Constraints
Insert, Update & Delete from Foreign
Key table

Can we insert values in

refencing table

Answer is no: Integrity

will loss

197
 Integrity Constraints
Insert, Update & Delete from Foreign
Key table

Can we delete value from

referencing table

Answer is yes

Can we update value in

referencing table

Answer is no

198
Integrity constraint

199
Thankyou

200