1

Module -2

4/3/2025
2

4/3/2025
3

4/3/2025
4

4/3/2025
5

4/3/2025
6

4/3/2025
7

4/3/2025
8

4/3/2025
9

4/3/2025
10

4/3/2025
11

4/3/2025
12

4/3/2025
13

4/3/2025
14

4/3/2025
15

4/3/2025
16

4/3/2025
17

4/3/2025
 18

UPDATE OPERATION
✔ INSERT
✔ DELETE
✔ MODIFY

4/3/2025
19

4/3/2025
20

4/3/2025
21

4/3/2025
22

4/3/2025
23

4/3/2025
24

4/3/2025
25

4/3/2025
 26

DELETE

4/3/2025
27

4/3/2025
28

4/3/2025
29

4/3/2025
30

4/3/2025
 31

MODIFY

4/3/2025
32

4/3/2025
33

4/3/2025
34

4/3/2025
35

4/3/2025
36

4/3/2025
37

4/3/2025
38

4/3/2025
 39

EXAMPLE PROBLEMS

4/3/2025
40

4/3/2025
41

4/3/2025
42

4/3/2025
43

4/3/2025
44

4/3/2025
45

4/3/2025
46

4/3/2025
47

4/3/2025
48

4/3/2025
49

4/3/2025
50

4/3/2025
51

4/3/2025
52

4/3/2025
53

4/3/2025
 54

Relational algebra

4/3/2025
55

4/3/2025
56

4/3/2025
57

4/3/2025
58

4/3/2025
59

4/3/2025
60

4/3/2025
61

4/3/2025
62

4/3/2025
63

4/3/2025
64

4/3/2025
65

4/3/2025
66

4/3/2025
67

4/3/2025
68

4/3/2025
69

4/3/2025
 70

SET THEORY

4/3/2025
71

4/3/2025
72

4/3/2025
73

4/3/2025
74

4/3/2025
75

4/3/2025
76

4/3/2025
77

4/3/2025
BCS403- Database Management System
Subject: Database Management System
Semester : V

Course Instructor: Dr.Ciyamala Kushbu

Department of Information Science and
Engineering, CMRIT – Bangalore.
Email Id: [email protected]
 79

BINARY

4/3/2025
80

4/3/2025
81

4/3/2025
82

4/3/2025
83

4/3/2025
84

4/3/2025
85

4/3/2025
86

4/3/2025
87

4/3/2025
88

4/3/2025
89

4/3/2025
90

4/3/2025
91

4/3/2025
92

4/3/2025
93

4/3/2025
94

4/3/2025
95

4/3/2025
96

4/3/2025
 97

Additional Relational Operations

4/3/2025
98

4/3/2025
99

4/3/2025
100

4/3/2025
101

4/3/2025
102

4/3/2025
103

4/3/2025
104

4/3/2025
105

4/3/2025
106

4/3/2025
107

4/3/2025
108

4/3/2025
109

4/3/2025
110

4/3/2025
111

4/3/2025
112

4/3/2025
113

4/3/2025
114

4/3/2025
115

4/3/2025
116

4/3/2025
117

4/3/2025
118

4/3/2025
 119

Examples

4/3/2025
120

4/3/2025
121

4/3/2025
122

4/3/2025
123

4/3/2025
124

4/3/2025
125

4/3/2025
126

4/3/2025
127

4/3/2025
128

4/3/2025
129

4/3/2025
130

4/3/2025
131

4/3/2025
132

4/3/2025
133

4/3/2025
 134
Homework

4/3/2025
135

4/3/2025
136

4/3/2025
137

4/3/2025
138

4/3/2025
139

4/3/2025
140

4/3/2025
141

4/3/2025
142

4/3/2025
143

4/3/2025
144

4/3/2025
145

4/3/2025
146

4/3/2025
147

4/3/2025
148

4/3/2025
149

4/3/2025
150

4/3/2025
151

4/3/2025
152

4/3/2025
153

4/3/2025
154

4/3/2025
155

4/3/2025
 156
Introduction
• The table is called relation (or) simply, a table is a collections of related attributes
and there is a relation among the attributes.
• The relational model is today the primary data model for commercial data
processing applications.
• A relational database consists of a collection of tables, each of which is assigned a
unique name.
• a row in a table represents a relationship among a set of values. Since a table is a
collection of such relationships, there is a close correspondence between the concept
of table and the mathematical concept of relation, from which the relational data
model takes its name.
• Thus, in the relational model the term relation is used to refer to a table, while the
term tuple is used to refer to a row. Similarly, the term attribute refers to a column of
a table.
 157
Relational Model Concepts

Definition 1:
• 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, dom(Ai)
Definition 2:
• RELATIONAL MODEL (RM) 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.
• In the relational model, data are stored as tables. However, the physical storage of the
data is independent of the way the data are logically organized.
 158

In relational model, the data and relationships are represented by collection

of inter-related tables. Each table is a group of column and rows, where
column represents attribute of an entity and rows represents records
 159
Domain, Attributes, Tuples, and Relations
Domain
► A Domain D is a set of atomic values. Atomic mean each value in the
domain is indivisible.
► Domain can be specified by data type from the which the data values
forming the domain.
► Data type and format also specified.
► Specify the name for the domain – It will be useful.
► E.g, Phone_number;
► Aadhar_number; etc.,
 160

Attribute Types

• Each attribute of a relation has a name

• The set of allowed values for each attribute is called the domain of the attribute
• Attribute values are (normally) required to be atomic; that is, indivisible
• E.g. multivalued attribute values are not atomic
• E.g. composite attribute values are not atomic
• The special value null is a member of every domain. Indicated that the value is
“unknown”
• The null value causes complications in the definition of many operations
• we shall ignore the effect of null values in our main presentation and consider
their effect later
 161

Relation Name
Attributes
► Student
Name SSN Home_Phone Address Office_Phone Age
► Tuples Marx 1001 42345 12 Germany 4857474 65
Lenin 1201 42356 34 Masco 48456594 53
Stalin 2313 34848 23 Russia 475648 64

4/3/2025
 162

Relations are Unordered

• Order of tuples is irrelevant (tuples may be stored in an arbitrary order)

• Example: instructor relation with unordered tuples

4/3/2025
 163
Ordering of Tuples in a Relation

► A relation is defined as a set of tuples. Mathematically, elements of

a set have no order among them, hence, tuples in a relation do not
have any particular order.
► Tuples ordering is not part of a relation definition because a relation
attempts to represent facts at logical or abstract level.

4/3/2025
 164
Ordering of Values within a Tuple and an Alternative definition
of a relation

A Relation Schema R={A1, A2,…….An} is a set of attributes and a relation state r(R)
is a finite set of mappings r={t1,t2,….tm}, where each tuple ti is a mapping from R to
D, and D is the union of attribute domains; that is D= dom(A1) U dom(A2) U
Dom(A3) U….U Dom(An).

Here, a tuple can be considered as a set of (<attribute>, <value>) pair, where each
pair gives the value of the mapping from an Attribute Ai to a value Vi from dom(Ai).
Ordering of attribute is not important, because the attribute name appears with its
value.

4/3/2025
 165

Values and NULLs in the Tuples

► Each value in a tuple is an atomic value. – It is not divisible into component.

► Composite and multivalued attributes are not allowed. This model is
sometime called as flat relation model. (First Normal Form)

► Null Values, used to represent the values of attributes that may be unknown
or may not apply to a tuple. A Special value, called NULL, can be used in this
case.

4/3/2025
 166

Interpretation(Meaning) of a Relation

► A relation schema (table) can be interpreted as a declaration or a

type of assertion. Eg., Student relation, (student table or entity) has
a name, ssn, home_phone, address, office_phone, and age.
► Note: Some relations may represent fact about entities: whereas
other relation may represent fact about relationships.

4/3/2025
 167

Relational Model Constraints and Relational Database Schemas

► In relational database, there will typically be many relations, and the tuples in those
relations are related in many ways.
► The state of the whole database will correspond to the states of all its relations at a
particular point in time.
► There are many restrictions or Constraints on the actual values in a database state.
- Constraints that are inherent in the data model is called inherent model based
constraints or implicit constraints.
- Constraints that can be directly expressed in the schemas of the data model –
specifying them in DDL (while creating table), - Schema based Constraints
or Explicit Constraints. 4/3/2025
 168

► Constraints that cannot be directly expressed in the schemas of the data

model, and hence must be expressed and enforced by the application
programs or in some other way. This is called application-based or
Semantic constraints or Business rules.

4/3/2025
 169

Every relation has some conditions that must hold for it to be a valid relation. These
conditions are called Relational Integrity Constraints. There are three main integrity
constraints −
• Key constraints
• Domain constraints
• Referential integrity constraints

Key Constraints

There must be at least one minimal subset of attributes in the relation, which can identify a
tuple uniquely. This minimal subset of attributes is called key for that relation. If there are
4/3/2025
more than one such minimal subsets, these are called candidate keys.
 170

• In a relation with a key attribute, no two tuples can have identical values for key
attributes.
• A key attribute can not have NULL values. Key constraints are also referred to as
Entity Constraints
The data values stored in the database must satisfy certain consistency constraints
• Eg., Account balance of a department must never be negative.
• DDL provides facilities to specify such constraints. The database system checks
these constraints every time the database is updated
Domain Constraints
Attributes have specific values in real-world scenario. For example, age can only be a
positive integer. The same constraints have been tried to employ on the attributes of a
relation. Every attribute is bound to have a specific range of values. For example, 4/3/2025
age
 171

• Domain Constraints: A domain of possible values must be associated with every

attribute (for example, integer types, character types, date/time types). Declaring an
attribute to be of a particular domain acts as a constraint on the values that it can take.

Referential integrity Constraints

Referential integrity constraints work on the concept of Foreign Keys. A foreign key is a
key attribute of a relation that can be referred in other relation. Referential integrity
constraint states that if a relation refers to a key attribute of a different or same relation,
then that key element must exist.
 172

• Referential Integrity: There are cases where we wish to ensure that a value that
appears in one relation for a given set of attributes also appears in a certain set of
attributes in another relation

• Assertions: An assertion is any condition that the database must always satisfy.
Domain constraints and referential-integrity constraints are special forms of assertions.

4/3/2025
 173

Relational Database Schema

• When we talk about a database, we must differentiate between the database schema,
which is the logical design of the database, and the database instance, which is a
snapshot of the data in the database at a given instant in time.
• A database consists of multiple relations
• Information about an enterprise is broken up into parts, with each relation storing one part
of the information, some examples follows:
• student (ID, name, dept name, tot cred)
• advisor (s id, i id)
• takes (ID, course id, sec id, semester, year, grade)
• classroom (building, room number, capacity)
• time slot (time slot id, day, start time, end time)
 174
Relationship Sets
A relationship is an association among several entities. For example, we can define a
relationship advisor that associates instructor Katz with student Shankar. This relationship
specifies that Katz is an advisor to student Shankar.

relationship set is a set of relationships of the same type. Formally, it is a mathematical

relation on n ≥ 2 (possibly nondistinct) entity sets. If E1, E2, . . . , En. are entity sets, then a
relationship set R is a subset of
{(e1, e2, . . . , en) | e1 ∈ E1, e2 ∈ E2, . . . , en ∈ En}
where (e1, e2, . . . , en) is a relationship.

Attributes: For each attribute, there is a set of permitted values, called the domain, or
value set, of that attribute.
attribute types are:
• Simple and composite attributes.
• Single-valued and multivalued 4/3/2025
 175

Referential Integrity: We wish to ensure that a value that appears in one relation for a given set
of attributes also appears for a certain set of attributes in another relation. This condition is
called referential integrity.

• Foreign keys can be specified as part of the SQL create table statement by using the foreign
key clause.

let r1 and r2 be relations whose set of attributes are R1 and R2, respectively, with primary keys
K1 and K2. We say that a subset of R2 is a foreign key referencing K1 in relation r1 if it is
required that, for every tuple t2 in r2, there must be a tuple t1 in r1 such that t1.K1 = t2.

• Requirements of this form are called referential-integrity constraints, or subset

dependencies.
4/3/2025
 176
Relation Schema and Instance

• A1, A2, …, An are attributes – Eg., USN, Name, Address, Mobile_NO

• R = (A1, A2, …, An ) is a relation schema - Student(USN, Name, Address, Mobile_No)
• Create table student(USN Varchar(10), Name varchar(20), Address varchar (40),
Mobile_No number(10));
Example: instructor = (ID, name, dept_name, salary)
• Formally, given sets D1, D2, …. Dn a relation r is a subset of
D1 x D2 x … x Dn
Thus, a relation is a set of n-tuples (a1, a2, …, an) where each ai ∈ Di
• The current values (relation instance) of a relation are specified by a table
• An element t of r is a tuple, represented by a row in a table 4/3/2025
 177

BASIC STRUCTURE
• Formally, given sets D1, D2, …. Dn a relation r is a subset of D1 x D2 x … x Dn
Thus a relation is a set of n-tuples (a1, a2, …, an) where each ai ∈ Di
Example: if
customer-name = {Jones, Smith, Curry, Lindsay}
customer-street = {Main, North, Park}
customer-city = {Harrison, Rye, Pittsfield}
Then r = { (Jones, Main, Harrison),
(Smith, North, Rye),
(Curry, North, Rye),
(Lindsay, Park, Pittsfield)} is a relation over customer-name x customer-street y
customer-city z 4/3/2025
 178

► A Relational Schema ‘s’ is a set of relational schemas S={R1, R2,….Rn} and set of
integrity constraints IC.
► A Relational database state DB of S is a set of relation states DB={r 1,r2,….rn} such
that each ri is a state of Ri and such that the relation ri relation state specify integrity
constraints specified in IC.
► Relational Database Schema – Company
Company = {EMPLOYEE, DEPARTMENT, DEPT_LOCATIONS, PROJECT,
WORKS_ON, DEPENDENT}
When we refer to a relational database, we implicitly include both its schema and its
current state.
4/3/2025
 179

EMPLOYEE Fname Minit Lname SSN Bdate Address Sex Salaey Super_ssn Dno

DEPARTMENT Dname Dnumber Mgr_ssn Mgr_start_date

DEPT_LOCATIONS Dnumber Dlocation

PROJECT Pname Pnumber Plocation Dnum

WORKS_ON Essn Pno Hours

DEPENDENT Essn Dependent_name Sex Relationship

4/3/2025
 180

Keys
• Let K ⊆ R
• K is a superkey of R if values for K are sufficient to identify a unique tuple of each possible
relation r(R)
• Example: {ID} and {ID,name} are both superkeys of instructor(Relation or Table).

• Superkey K is a candidate key if K is minimal. Example: {ID} is a candidate key for

Instructor
• One of the candidate keys is selected to be the primary key. which one?
• Foreign key constraint: Value in one relation must appear in another
• Referencing relation
• Referenced relation
• Example – dept_name in instructor is a foreign key from instructor referencing
department
 181

Super Key: One or more attributes used to identify the record from record set is called super key.

Candidate Key: Minimal sub-set of super key is called candidate key

Primary Key: The Database Admin can assign any one of the candidate key as ‘Primary key’.

Foreign key: The value in one relation must appear in another relation.

4/3/2025
Schema Diagram for University Database

182

4/3/2025
 183

Entity Integrity, Referential Integrity, and Foreign Keys

► The entity integrity constraint states that no primary key value can be NULL.
► This is because the primary key value is used to identify individual tuples in a
relation.
► Having NULL values for the primary key implies that we cannot identify some
tuples.
► Key constraints and entity integrity constraints are specified on individual
relations.
► The referential integrity constraint is specified between two relations and is
used to maintain the consistency among tuples in the two relations. 4/3/2025
 184

Update Operations, Transactions, and

Dealing with Constraint Violations

The Insert operation provides a list of attribute values for a new tuple t that is to be inserted
into a relation R.

Insert can violate any of the four types of constraints.

• Domain constraints can be violated if an attribute value is given that does not appear in the
corresponding domain or is not of the appropriate data type.
• Key constraints can be violated if a key value in the new tuple t already exists in another
tuple in the relation r(R).
• Entity integrity can be violated if any part of the primary key of the new tuple t is NULL.
• Referential integrity can be violated if the value of any foreign key in t refers to a tuple
that does not exist in the referenced relation.
4/3/2025
 185

The Delete operation can violate only referential integrity. This occurs if the
tuple being deleted is referenced by foreign keys from other tuples in the
database. To specify deletion, a condition on the attributes of the relation
selects the tuple (or tuples) to be deleted.

The Update (or Modify) operation is used to change the values of one or more
attributes in a tuple (or tuples) of some relation R. It is necessary to specify a
condition on the attributes of the relation to select the tuple (or tuples) to be
modified.
4/3/2025
 186

Update Operations

In certain situations, we may wish to change a value in a tuple without changing all values in the
tuple. For this purpose, the update statement can be used. As we could for insert and delete, we
can choose the tuples to be updated by using a query.

Suppose that annual salary increases are being made, and salaries of all instructors are to be
increased by 10 percent. We write:

update instructor set salary= salary * 10.05;

Updating an attribute that is neither part of a primary key nor part of a foreign key usually causes no
problems; the DBMS need only check to confirm that the new value is of the correct data type and domain.
Modifying a primary key value is similar to deleting one tuple and inserting another in its place because we
use the primary key to identify tuples. 4/3/2025
 187
Transactions Concepts

• A database application program running against a relational database typically executes

one or more transactions.
• A transaction is an executing program that includes some database operations, such as
reading from the database, or applying insertions, deletions, or updates to the database.
• At the end of the transaction, it must leave the database in a valid or consistent state that
satisfies all the constraints specified on the database schema.

• A transaction consists of a sequence of query and/or update statements. The SQL

standard specifies that a transaction begins implicitly when an SQL statement is
executed.

4/3/2025
 188

• One of the following SQL statements must end the transaction:

Commit work: commits the current transaction; that is, it makes the updates performed by
the transaction become permanent in the database. After the transaction is committed, a
new transaction is automatically started.

Rollback work: causes the current transaction to be rolled back; that is, it all the updates
performed by the SQL statements in the transaction. Thus, the database state is restored to
what it was before the first statement of the transaction was executed.
• Transaction rollback is useful if some error condition is detected during execution of a
transaction
• Once a transaction has executed commit work, its effects can no longer be undone by
4/3/2025
rollback work
 189

Relation Algebra
The relational algebra defines a set of operations on relations, paralleling the usual algebraic
operations such as addition, subtraction or multiplication, which operate on numbers
Relation Algebra

Unary Binary
- Select - Union
- Project - Intersection
- Rename - Differences
- Cartesian Product
Aggregate Function

• Find all the loans of over 1200

σ amount >1200(loan) 4/3/2025
SQL> select *from loan where amount>1200;
 190

► The basic set of operations for formal relation model is the relational
algebra.
► Those operations enable a user to specify basic retrieval requests knows as
relational algebra expression.
► The result of the retrieval query is a new relation.
► The algebra operations thus produces new relation, which can be further
manipulated using operations of the same algebra.

4/3/2025
 191

► The algebra defines a set of operations for the relational model

► The relational calculus provide a higher level declarative language for
specifying relational query.

4/3/2025
 192

Select Operation
The select operation selects tuples that satisfy a given predicate. The Select operation is used to
select subset of the tuples from relation that satisfies the selection condition.
Notation: σ p(r)
p is called the selection predicate
Defined as:
σ p(r) = {t | t ∈ r and p(t)}
Where p is a formula in propositional calculus consisting of terms connected by : ∧ (and), ∨
(or), ¬ (not)

The Select operating is commutative

σ<cond1>(σ<cond 2>(R))= σ<cond 2>(σ<cond 1>(R))
4/3/2025
 193

σDno=4(EMPLOYEE)
σsalary>30000(EMPLOYEE)
Each term is one of: <attribute> op <attribute> or <constant>
where op is one of: =, ≠, >, ≥. <. ≤
Example of selection:
σ branch-name=“Perryridge”(account)

4/3/2025
 194

Thus, to select those tuples of the loan relation where the branch is “Perryridge,” we write

SQL>σ branch-name = “Perryridge” (loan)

We can find all tuples in which the amount lent is more than $1200 by writing

SQL>σ amount>1200 (loan)

find those tuples pertaining to loans of more than $1200 made by the Perryridge branch, we write
SQL> σ branch-name = “Perryridge” ∧ amount>1200 (loan)

The Project Operation

we write the query to list all loan numbers and the amount of the loan as

SQL>Π loan-number, amount(loan) 4/3/2025

 195

Composition of Relational Operations

The fact that the result of a relational operation is itself a relation is important. Consider the more complicated
query “Find those customers who live in Harrison.” We write:

SQL> Π customer-name (σ customer-city = “Harrison” (customer ))

The Union Operation how to find the names of all customers with a loan in the bank

SQL> Π customer-name (borrower)

and

SQL> Πcustomer-name (depositor)

4/3/2025
SQL> Πcustomer-name (borrower ) ∪ Πcustomer-name (depositor)
 196

Find the loan number for each loan for an amount greater then 1200

π loan_number(σ amount>1200) (loan))

SQL> select loan_number from loan where amount >1200;

Find the names of all customer who have a loan, an account, or both from the bank

π customer_name(borrower) U π customer_name(depositor)

SQL> select customer_name from borrower

union
4/3/2025
select customer_name from depositor
 197

Find the names of all customer who have a loan and an account at bank
π customer_name (borrower) ∩ π customer_name(depositor)
SQL> select customter_name from borrower intersect select customer_name from
depositor;

4/3/2025
 198

The Project Operation

• The project operation is a unary operation that returns its argument relation, with certain attributes
left out.
• Suppose we want to list all instructors’ ID, name, and salary, but do not care about the dept name.
Projection is denoted by the uppercase Greek letter pi (π)
• The result is defined as the relation of k columns obtained by erasing the columns that are not listed
• Duplicate rows removed from result, since relations are sets
Example
π ID, name, salary (instructor )

4/3/2025
 199

Example

To find the set of all courses taught in the Fall 2009 semester, we
write:
π course id (semester =“Fall”∧ year=2009 (section))
To find the set of all courses taught in the Spring 2010 semester, we write:
π course id (semester =“Spring”∧year=2010 (section))
To answer the query, we need the union of these two sets; that is, we need all section IDs that
appear in either or both of the two relations. We find these data binary operation union,
denoted, as in set theory, by ∪. So the expression
needed is:
course id (semester =“Fall”∧ year=2009 (section)) ∪ course id (semester =“Spring” ∧
4/3/2025
year=2010 (section))
 200

Find the names of all customers who have loan at the bank and find the amount of the loan;

Find the names of all branches with customer who have an account in the bank and who lives in “Harrison”.

Division Operation

Find all customer who have an account at all the branches located in ‘Brooklyn’.

Aggregate Function

> Find the average account balance at the ‘Perryridge’ branch.

> Find the average account balance at each branch.
> Find the No. of depositor for each branch.
> Find the branches where the average account balance is more than 1200
> Find the average balance for each customer who lives in “Harrison” and has at least three account
4/3/2025
 201

Nested Queries

Find all customers who have both a loan and an account.

Find all customers who have both an account and a loan at the ‘Perryridge’ branch.

Find all customer who do have a loan at the bank, but do not have an account at the bank.

Select names of customer who have a loan at the bank and where names are neither “Smith” nor
“Jones”.

Find the names of all the branches that have assets greater than those of at least one branch located in
“Brooklyn”

Find the branch that has the highest average balance. 4/3/2025
 202

The Union Operation

• Relations r, s:

• r ∪ s:

4/3/2025
 203

Set difference of two relations

• Relations r, s:

•r–s

4/3/2025
 204

Set intersection of two relations

• Relation r, s:

r∩s

• Note: r ∩ s = r – (r – s)

4/3/2025
 205

The Set Difference Operation

find all customers of the bank who have an account but not a loan

SQL> Πcustomer-name (depositor) − Πcustomer-name (borrower)

4/3/2025
 206

The following command creates a relation department in the database.

create table department (dept name varchar (20), building varchar (15), budget numeric
(12,2), primary key (dept name));

Queries: “Find the names of all instructors.”

The instructor’s name appears in the name attribute, so we put that in the select clause.

select name from instructor;

Example query: “Find the department names of all instructors,”

select dept name from instructor ; 4/3/2025

 207

• The select clause is used to list the attributes desired in the result of a query.
• The from clause is a list of the relations to be accessed in the evaluation of the query.
• The where clause is a predicate involving attributes of the relation in the from clause.

A typical SQL query has the form

select A1, A2, . . . , An
from r1, r2, . . . , rm
where P;

4/3/2025
 208

Aggregate Functions

Aggregate functions are functions that take a collection (a set or multiset) of values as
input and return a single value. SQL offers five built-in aggregate functions:
• Average: avg
• Minimum: min
• Maximum: max
• Total: sum
• Count: count
Basic Aggregation
Consider the query “Find the average salary of instructors in the Computer Science
department.” We write this query as follows:

select avg (salary) from instructor where dept name= ’Comp. Sci.’; 4/3/2025
 209

An example arises in the query “Find the total number of instructors who teach a course in the
Spring 2010 semester.”

select count (distinct ID)

from teaches where semester = ’Spring’ and year = 2010;

consider the query “Find the average salary in each department.” We write this query as follows:
select dept name, avg (salary) as avg salary
from instructor group by dept name;

consider the query “Find the average salary of all instructors.” We write this query as follows:
select avg (salary) from instructor;
4/3/2025
 210
Referential Integrity

USN (PK) Name DOB Address Phone

1mj18is1 Abc 12 Dfkdj 12541
1mj18is2 Def 12 Doierj 13256
1mj18is3 ghi 21 xxcns 65238

Sport No S_name USN Name Date

100 X 1mj18is1 Abc
200 Y 1mj18is2 Def
300 z 1mj18is3 ghi

4/3/2025
 211
Relational database design using ER-to-relational mapping

Entity-Relationship Model. The entity-relationship (E-R) data model uses a collection of basic
objects, called entities, and relationships among these objects. An entity is a “thing” or “object”
in the real world that is distinguishable from other objects. The entity-relationship model is
widely used in database design.

• The entity-relationship (E-R) data model was developed to facilitate database design by
allowing specification of an enterprise schema that represents the overall logical structure of
a database.

• The E-R model is very useful in mapping the meanings and interactions of real-world
enterprises onto a conceptual schema.

• The E-R data model employs three basic concepts: entity sets, relationship sets, and
4/3/2025
attributes.
 212

Basic SQL
► SQL uses the term table, row, and column for the formal relational model
terms relation, tuple, and attribute.
► The Concept of SQL Schema – grouping together tables and other constructs
that belongs to the same database applications. (Schema is called database)
► SQL schema is identified by the schema name and includes an authorization
identification.
► The schema can be create via create schema statement.
► Eg., create schema employee authorization ‘IseDepartment’;
► create user employee identified by ‘IseDepartment’; 4/3/2025
 213

► catalog – Collection of schemas.

SQL
•SQL
•Data Definition Language
•Data Manipulation Language

4/3/2025
 214

SQL - Data Definition and Data Types

CREATE TABLE command
create table company(Fname varchar(15) not null, Minit char, Lname
varchar(15) not null, Ssn char(9) not null, Bdate date, Address varchar(30),
Gender char, Salary decimal(10,2), Super_ssn char(9), Dno int not null,
primary key(Ssn);
SQL> create table student (usn varchar(10), name varchar(15), course
varchar(3), phone number(10));
4/3/2025
 215

SQL: Data definition and data types

Integrity Constraints: Integrity constraints ensure that changes made to the database by authorized
users do not result in a loss of data consistency.
Examples of integrity constraints are:
• An instructor name cannot be null.
• No two instructors can have the same instructor ID.
• Every department name in the course relation must have a matching department name in the
department relation.
• The budget of a department must be greater than $0.00.

Constraints on a Single Relation:

• not null
• unique
• check(<predicate>)
4/3/2025
 216

Attribute Data types and Domains in SQL

Numeric Data Types
* int, smallint, float or real, and double precision.
* Formatted numbers can be declared by decimal( i, j) or dec(i, j) or number(i, j)
Character String
* char(n) or character(n), varchar(n)
Bit String
*
Boolean – has the traditional value of true or false 4/3/2025
 217

► Date – date – YYY-MM-DD(Year, Month, and Day), and the Time data type
HH:MM:SS(Hour, Minutes, and Second)

4/3/2025
 218

Insert, Delete, and update statement in SQL

• To insert data into a relation, we either specify a tuple to be inserted or write a Query whose result is a set of
tuples to be inserted.
insert into course
values (’CS-437’, ’Database Systems’, ’Comp. Sci.’, 4);
(OR)
insert into course (course id, title, dept name, credits)
values (’CS-437’, ’Database Systems’, ’Comp. Sci.’, 4);

In certain situations, we may wish to change a value in a tuple without changing all values in the tuple. For this
purpose, the update statement can be used.

Suppose that annual salary increases are being made, and salaries of all instructors are to be increased by 5
percent. We write:

update instructor set salary= salary * 1.05; 4/3/2025

 219
Text books and Reference books

Text Books
Fundamentals of Database Systems, Ramez ElmasriandShamkantB.Navathe,7thEdition,2017,Pearson. ISBN: 978-0-13-397077-7
1.

Databasemanagementsystems,Ramakrishnan,andGehrke,3rdEdition,2014, McGrawHill ISBN-10: 0072465638

2.

Reference Books
Silberschatz Korth and Sudharshan, Database System Concepts, 6th Edition,Mc-GrawHill,2013. ISBN-10: 9332901384
1.

Coronel, Morris, and Rob, Database Principles Fundamentals of Design, Implementation and Management, Cengage Learning2012.
2. ISBN 9781285196145
 220
Some useful links

https://www.cs.uregina.ca/Links/class-info/215/erd/#:~:text=Data%20modeling%20is%20a%20technique,data%20
requirements%20for%20a%20database
https://www.geeksforgeeks.org/generalization-specialization-and-aggregation-in-er-model/
https://www.guru99.com/relational-data-model-dbms.html
https://www.tutorialspoint.com/dbms/relational_algebra.htm
 221

Thank
You