Subject Name: Database Management

System
Week 1
Module 1 : Introduction to Database
Faculty Name: Mrs. Apurva S. Shinde
Index -

Lecture 1 –Introduction to database systems, File Vs. Database system

Lecture 2 –Data Models

Lecture 3 -Architecture of Database, challenges in building database

2 Introduction to database concepts

Unit No: 1 :Introduction to Database

Lecture No: 1:Introduction to

database systems, File Vs.
Database system
Introduction : Data and Information

❑ What is Data?
e.g. related facts and figures The collection of called is Data.
❑ Is Data == Information ? planning to take voting here

E.g

related facts and figures The collection of related

The collection of called is Processing on data facts and figures is called
Data. Data.

4 Introduction to database concepts

Introduction : Database and DBMS

Collection of
❑ What is Database ? related data

✓ Student info
✓ Subject info
✓ Faculty Info
✓ Marks info

❑ What is Database Management Systems ?

Manager
Student info Subject info Faculty info

• Add • Add • Add

• Modify • Modify • Modify
• Delete • Delete • Delete

5 Introduction to database concepts

Introduction , characteristics of Database

▪ Why Database ?
• Collection of interrelated data

• Set of programs to access the data

• An environment that is both convenient and efficient to use.

6 Introduction to database concepts

What is Data?

17 Lecture 1 – Introduction Database Concepts

Data Processing Example

8 Introduction to database concepts

What is Database?

▪ record keeping system that is used to record , maintain and retrieve data.

▪ Operate and handle large amount of information.

▪ Storing, retrieving and managing data in database.

9 Introduction to database concepts

What is Database Management System ?

▪ Software group to perform operations .

▪ Creating and managing databases
▪ Interface between the application programs and database

10 Introduction to database concepts

Commonly Used Databases

11 Introduction to database concepts

Where can we find Database?

• DBMS contains information about a particular enterprise

– Collection of interrelated data
– Set of programs to access the data
– An environment that is both convenient and efficient to use
• Database Applications:
– Banking: transactions
– Airlines: reservations, schedules
– Universities: registration, grades
– Sales: customers, products, purchases
– Online retailers: order tracking, customized recommendations
– Manufacturing: production, inventory, orders, supply chain
– Human resources: employee records, salaries, tax deductions
• Databases can be very large.
• Databases touch all aspects of our lives

12 Introduction to database concepts

Different Types of Database Systems

• RDBMS- Relational Database Management Systems

• OODBMS- Object Oriented Database Management Systems

• ORDBMS- Object Oriented Database Management Systems

13 Introduction to database concepts

RDBMS- Relational Database Management Systems

❑ What is Relational Database Management Systems?

14 Introduction to database concepts

What is Object Oriented Database Management Systems?

• Can we store photos, signature, audio, video in a database cell?

✓ Account Holder Master

✓ Transaction Table
✓ Loan Table

15 Introduction to database concepts

RDBMS- Relational Database Management Systems

16 Introduction to database concepts

❑ What is Object Relational Database Management Systems?

❑ ORDBMS = Combination of OO Concepts + Relational Database

Employee- Customer-
Account Master
Master Master
XYZ
• CITY • CITY • CITY
• STATE • STATE • STATE Bank
• PIN-CODE • PIN-CODE • PIN-CODE
• COUNTRY • COUNTRY • COUNTRY
Domain

17 Introduction to database concepts

Characteristics of databases

19 Introduction to database concepts

Characteristics of databases

A modern DBMS has the following characteristics −

1. Real-world entity − A modern DBMS is more realistic and uses real-world entities
to design its architecture. It uses the behaviour and attributes too. For example, a
school database may use students as an entity and their age as an attribute.

2. Relation-based tables − DBMS allows entities and relations among them to form
tables. A user can understand the architecture of a database just by looking at the
table names.

3. Isolation of data and application − A database system is entirely different than its
data. A database is an active entity, whereas data is said to be passive, on which
the database works and organizes. DBMS also stores metadata, which is data
about data, to ease its own process.

20 Introduction to database concepts

What is File system?

▪ Stores electronic data in a set of

files.

▪ If a file consists of only one file,

then it is a flat file

▪ To search data, first, it is

necessary to parse each row
and load it to an array at
runtime.

▪ Quite inefficient and time-

consuming.

21 Introduction to database concepts

 Types of Files and File Processing Systems

Master Files Transaction File

Employee-Master Account Holder New Customer Loan Release

• Customer-id • Loan release-

• Employee-id • Account-No • Customer id
• Employee-Name • Person-name name • Type
• Employee - • Person-Address • Customer • amount
Address address • Rate
• Person- DOB
• Person-photo
• Person- Day to Day Activity
Signature

Table File
Updated less frequently
Loan Table Interest rate

Security File • Loan-id • Interest-id

• Loan- Type • Type
Example • Duration • Rate
New File Old File
-Hard Registers
Must be replaced -Folders- bill, tax,
Updated by periodically
periodically loan

2 Introduction to database concepts

File system v/s Database system

• A Database Management System (DMS) is a combination of

computer software, hardware, and information designed to
electronically manipulate data via computer processing.

• File Management System (FMS) is a Database Management

System that allows access to single files or tables at a time. FMS’s
accommodate flat files that have no relation to other files.

23 Introduction to database concepts

File Processing Vs. Database Systems

Data Redundancy

Data Inconsistency

Transaction challenges

Scattered data

Concurrent Access and

Recovery

Security challenges

Manager

24 Introduction to database concepts

Data Redundancy and Data Inconsistency

❑ Data Redundancy

▪ Exists when unnecessarily duplicated data are found in the database.

▪ Data Redundancy may also be the result of poorly designed databases that
allow the same data to be kept in different locations.

▪ E.g - Student contact details maintained in Administrative dept., Account

Section, Exam section and individual department level also.

❑ Data Inconsistency
▪ Due to data redundancy, it is possible that data may not be inconsistent
state.

▪ E.g – any Student contact details changed then should be changed in the
mentioned all department.

25 Introduction to database concepts

Concurrent Access and Recovery

❑ Concurrent Access

▪ Multiple users are allowed to access the data simultaneously(concurrently).

This is for sake of better performance and faster response.

▪ E. g
Consider an example of withdrawal transaction need to maintain old balance
amount, calculate new balance and write the new balance back to the file.

❑ Data Recovery : Need on-disk data structure to:

▪ Represent the tree of named directories and files

▪ Record the identities of the blocks that holds each files content.
▪ keep track of the areas of the disk which are free.

26 Introduction to database concepts

File system v/s Database system

DBMS File Processing System

Minimal data redundancy problem in
Data Redundancy problem exits
DBMS
Data Inconsistency does not exist Data Inconsistency exist here
Accessing database is easier Accessing is comparatively difficult
Data is scattered in various files and files
The problem of data isolation is not found
may be of different format, so data
in database
isolation problem exists
Transactions like insert, delete, view, In file system, transactions are not
updating, etc are possible in database possible
Concurrent access and recovery is Concurrent access and recovery is not
possible in database possible
Security of data Security of data is not good
A database manager (administrator)
A file manager is used to store all
stores the relationship in form of
relationships in directories in file systems.
structural tables

27 Introduction to database concepts

Unit No: 1 Introduction Database Concepts

Lecture No: 2
Data Models
Types of DBMS- Database Models

▪ Design approach based on

database models.

▪ Database Model Provides

the structure in which way
data is organized and
manipulated with database.

29 Introduction to database concepts

Hierarchical Database Model

▪ Based on Parent Child

Relationship.

▪ Oldest form of database.

▪ Organizes the data in the

tree structure
❑ Advantages
Data access is quite predictable in
structure and retrieval and updates can be
highly optimized by a DBMS.

❑ Disadvantages
The link is permanently established and
cannot be modified which makes this
model rigid

30 Introduction to database concepts

Network Database Model

▪ Progression from hierarchical database

model

▪ Ability to handle more complex data by

allowing records to have more than one
parent.

▪ It was developed as an alternative to

the hierarchical database.

▪ It expands on the hierarchical model

by providing multiple paths among
segments.

▪ Hence this model allows one-to-one,

one-to-many and many-to-many
relationships

31 Introduction to database concepts

Network Database Model

❑ Advantages
Supporting multiple paths in the
data structure eliminates some of
the drawbacks of the hierarchical
model, the network model is not
very practical

❑ Disadvantages
It can be quite complicated to
maintain all the links

32 Introduction to database concepts

3. Relational Database Model

❑ It represents all data in the database as simple two-dimensional tables called

relations.
❑ Each row of a relational table, called tuple, represents a data entity with
columns of the table representing attributes(fields).
❑ Advantages
Provides flexibility that allows
changes to the database structure
to be easily accommodated. It
facilitates multiple views of the
same database for different users.

❑ Disadvantages

it does not easily support the

distribution of one database
across a number of servers

33 Introduction to database concepts

Relational Database Model

▪ Most Commonly used model In industry.

▪ Include Oracle, MySql, MSSQL, MSAccess

34 Introduction to database concepts

4. Object-Oriented Database Model

❑ An object-oriented database stores and maintains objects.

❑ Advantages
The users can define own
data access methods, the
representation of data and
the method of manipulating it.

❑ Disadvantages
- Lack of universal data
model
- Lack of experience

35 Introduction to database concepts

Object Oriented Database Model

• All Applications are treated as objects and actions performed by these objects.
• In OODMS- allows the database designers to model complex databases.

36 Introduction to database concepts

Unit No:1 Introduction to Database Concepts

Lecture No: 3 Architecture of

Database, challenges in
building database
DBMS system architecture

▪ helps in design, development, implementation, and maintenance of a database.

▪ stores critical information for a business.
▪ Selecting the correct Database Architecture to quick access and secure access to
this data.

38 Introduction to database concepts

DBMS system architecture- one Tire Architecture

39 Introduction to database concepts

DBMS system architecture- one Tire Client Server Architecture

▪ Client, Server, and

Database all reside on
the same machine.

▪ Eg: MS Office, any game

installation

40 Introduction to database concepts

DBMS system architecture- Two Tire Architecture

41 Introduction to database concepts

DBMS system architecture- Two Tire Client Server Architecture

• Presentation layer runs

on a client (PC, Mobile,
Tablet, etc)

• Data is stored on a
Server

• ODBC (Open Database

Connectivity) an API
which allows the client-
side program to call the
DBMS.

42 Introduction to database concepts

DBMS system architecture- Three Tire Architecture

43 Introduction to database concepts

 DBMS system architecture- Three Tire Architecture

Goals
▪ To separate the user
applications and
physical database.

▪ Support of multiple
views of the data

▪ Example of Three-
tier Architecture- Any
large website on the
internet

44 Introduction to database concepts

 DBMS Schemas: Internal, Conceptual, External

There are mainly three

levels of data abstraction:

▪ Internal Level: Actual

PHYSICAL storage
structure and access
paths.

▪ Conceptual or
Logical Level:
Structure and
constraints for the
entire database

▪ External or View
level: Describes
various user views.

45 Introduction to database concepts

Data Abstraction Levels

❑ Physical Level :
▪ The lowest level of abstraction.
▪ describes how the data are actually stored.

❑ Logical Level :
• describes what data are stored in the database, and what relationships
exist among those data.
• describes the entire database in terms of a small number of relatively
simple structures.

❑ View Level :
• highest level of abstraction
• describes only part of the entire database.
• to simplify their interaction with the system

46 Introduction to database concepts

Data Abstraction

• For the system to be usable, it must

retrieve data efficiently.

• The need for efficiency has led

designers to use complex data
structures to represent data in the
database.

• Developers hide the complexity from

users through several levels of
abstraction to simplify users
interactions with the system.

47 Introduction to database concepts

Database Architecture – Users and Administrator (View Level)

48 Introduction to database concepts

Typical Users of Database Systems

▪ System administrators-
database system’s gener al
operations.

▪ Database administrators -
manage the DBMS and
ensure that the database i s
functioning properly.

▪ Database designers-
design the database
structure

▪ System analysts and

programmers-design and
implement the application
programs

▪ End users- use the

application programs to run
the organization’s daily
54o
9 peLreacttiuoren1s–. Introduction Database Concepts
Database Users and User Interfaces : 4 Types of Users

❑ Naive Users :
▪ who interact with the system by invoking one of the application programs
▪ Eg. 1. A bank teller need to transfer money through money transfer program
▪ Eg. 2, Customer can check the account balance using World Wide Web
application

❑ Application Programmers :
▪ computer professionals who write application programs.
▪ Rapid application development (RAD) tools are tools that enable an
application programmer to construct forms and reports without writing a
program.
▪ Most major commercial database systems include a fourth generation
language.

50 Introduction to database concepts

Database Users and User Interfaces continue..

❑ Sophisticated users :
▪ interact with the system without writing programs
▪ Instead they use database query language
▪ Submit query to query processor
▪ Online analytical processing (OLAP) tools simplify analysts’ tasks by
letting them view summaries of data in different ways.

❑ Specialized users :
▪ users who write specialized database
▪ applications that do not fit into the traditional data-processing framework.
▪ Knowledge base and expert systems
▪ E.g Graphics data and audio data.

51 Introduction to database concepts

Database Architecture – Query Processor (Conceptual Level)

52 Introduction to database concepts

Query Processor

❑ DDL interpreter : which interprets DDL statements and records the

definitions in the data dictionary.

❑ DML compiler : which translates DML statements in a query language into

an evaluation plan consisting of low-level instructions that the query
evaluation engine understands.

❑ Query optimization : it picks the lowest cost evaluation plan from among
the alternatives.

❑ Query evaluation engine : which executes low-level instructions generated

by the DML compiler.

53 Introduction to database concepts

Database Architecture – Storage Manager(Internal Level)

54 Introduction to database concepts

Storage Manager

▪ Provides interface between the low level data stored in the database and
the application programs and queries submitted to the system.

▪ It is responsible for storing, retrieving, and updating data in the database.

❑ Components in storage manager :

▪ Authorization and integrity manager : which tests for the satisfaction of

integrity constraints and checks the authority of users to access data.

▪ Transaction manager : which ensures that the database remains in a

consistent (correct) state despite system failures, and that concurrent
transaction executions proceed without conflicting.

55 Introduction to database concepts

Components in storage manager..

▪ File manager : which manages the allocation of space on disk storage

and the data structures used to represent information stored on disk.

▪ Buffer manager :
• which is responsible for fetching data from disk storage into main memory,
and deciding what data to cache in main memory.
• The buffer manager is a critical part of the database system,
• to handle data sizes that are much larger than the size of main memory.

56 Introduction to database concepts

Disk Storage

❑ Data files: which store the database itself.

❑ Data dictionary: which stores metadata about the structure of the

database, in particular the schema of the database.

❑ Indices: which provide fast access to data items that hold particular
values

57 Introduction to database concepts

 Database Administrator (DBA)

▪ DBA is a resource that supervises both the database and the use of the DBMS.
▪ DBA is usually a group, but sometimes it refers to the database administrator.

Selection of H/W and Improving Query

S/W Performance and Tuning
Performance

Managing Data Security, Data Role of DBA

Integrity, Database Recovery and
Privacy
58 Introduction to database concepts
 Fig: Database system Architecture

59 Introduction to database concepts

Database System Environment

Lecture 3 – DBMS system architecture

Challenges in building Database Management

• In the last few years, data volumes have grown and the way we use data
has changed. Here are five of the top database management challenges.

1. Data security

2. Performance.

3. Data safety.

4. Resource utilization.

5. High availability.

61 Introduction to database concepts

[Link] security

• In the last two years, over 100,000 systems were hacked into because their
database had been left completely exposed on the public internet.

• How many people actually got to reading and implementing proper security
practices!

• The organizations using the database’s built-in security, sticking with what your
organization is already using, or reinforcing your application with both.

62 Introduction to database concepts

2. Performance.

• Databases have always faced harsh performance criteria.

• The amount of data you are taking in may be expanding, but to satisfy
your users you need to process it faster, without making them jump
through hoops to get there.

• A database must be built for high-performance, regardless of the

hardware. If a database performs well on older machines, and smaller
machines then it has good “native” performance.

63 Introduction to database concepts

3. Data safety

• Data needs to be processed in a way that ensures nothing is lost.

• A good database is one that can give you both superior performance and
transactional guarantees.

• The challenge for fully-transactional databases is performance.

64 Introduction to database concepts

4. Resource utilization

• It used to be assumed databases would run on the best hardware

available and they behaved accordingly.

• To keep up performance in the face of limited computing power and

handle larger amounts of more complex data, a database must get 100
percent out of the resources it is working on for every nanosecond it is
running.

65 Introduction to database concepts

5. High availability

• In a single server database, if your server goes down, your organization

goes down.

• A data cluster consisting of several servers working together gives you

multiple levels of backup. For e-commerce, it is similar to installing extra
cash registers to handle a growing line of people.

• A good database takes that into consideration and creates innovative

solutions to maintain your performance along with guaranteeing always on
availability.

66 Introduction to database concepts

 ICT- Video – Activity -fundamentals of a database systems

▪ Understand the Data Independence ,Database Administrator

▪ Type of Content :- Animation Video

▪ Video Embed in next Slide.

Lecture 4 – Data Independence ,Database Administrator

ICT- Quiz – Activity -fundamentals of a database systems

Click below

Lecture 4 – Data Independence ,Database Administrator

Summary

❑ Data, Information, DBMS, RDBMS, OODBMS and ORDBMS

❑ File Types –Master Files, Table Files, Transaction Files and Recovery Files
❑ File System Vs Database Management System
❑ Database Architecture- Users, Query processor, Storage Manager and Data
Storage.
❑ Database Administrator Role- Its like a Manager
❑ Database Models- Hierarchical model, Network Model, Relational Model,
Object-Oriented database model.

69 Introduction to database concepts

Thank You
Database Management System

Unit 1: Introduction to Database

Faculty Name: Mrs. Apurva S. Shinde

Index -

Lecture 4 – Entity Relationship Model (Entity sets, Types of Attributes- Keys, Relationship)

Lecture 5 – E-R diagram Notation (Weak and Strong Entity Set, Cardinality constraints)

Lecture 6 - Extended Entity Relationship Model: Specialization, Generalization and

Aggregation.

2
Unit 1: Introduction to Database

Lecture 4 :
Entity Relationship
Model (Entity sets,
Types of Attributes-
Keys, Relationship)
Design Phases

 database design is to characterize fully

the data needs of the prospective
database users.

 designer chooses a data model, apply data

modelling and translate a conceptual
schema of the database.

1 Lecture 1 – ER-Model
Data Model

 Stepsindevelopingadatabase

1 Lecture 1 – ER-Model
What is the ER Model?

 ENTITY RELATIONAL (ER) MODEL is a high-level conceptual data model

diagram.

 ER modeling helps you to analyze data requirements systematically

to produce a well-designed database.

 The Entity-Relation model represents real-world entities and the

relationship between them.

 ER modeling helps you to analyze data requirements systematically

to produce a well-designed database. So, it is considered a best
practice to complete ER modeling before implementing your database.

1 Lecture 1 – ER-Model
What is ER Diagrams?

 ENTITY-RELATIONSHIP DIAGRAM (ERD) displays the relationships of

entity set stored in a database.

 In other words, we can say that ER diagrams help you to explain the
logical structure of databases.

 At first look, an ER diagram looks very similar to the flowchart. However,

ER Diagram includes many specialized symbols, and its meanings
make this model unique.

 The purpose of ER Diagram is to represent the entity framework

infrastructure.

1 Lecture 1 – ER-Model
What is ER Diagrams?

 Conceptually it is very simple

• Better visual representation

• Effective communication tool

• Highly integrated with relational model

• Easy conversion to any data model

1 Lecture 1 – ER-Model
Entity, Entity sets, Types of Attributes

What is Entity?
 A real-world thing either living
or non-living that is easily
recognizable and
nonrecognizable.

 An entity can be place, person,

object, event or a concept,
which stores data in the
database.

 The entities are must have an

attribute, and a unique key.
Every entity is made up of some
'attributes' which represent
that entity.

1 Lecture 1 – ER-Model
Components of the ER Diagram

This model is based on

three basic concepts: Example
For example, in a University database,
we might have entities for Students,
E ntities Courses, and Lecturers.

Relationships Students entity can have attributes

Attributes like Rollno, Name, and DeptID.

They might have relationships with

Courses and Lecturers.

Lecture 5 – Entity, Entity sets, Types of Attributes

1
Components of the ER Diagram-Example

Lecture 5 – Entity, Entity sets, Types of Attributes

1
Entity

An entity is an object that exists

and is distinguishable from other
objects.

Entity is represented by
Rectangle.
Examples of entities:

Person: Employee, Student,

Patient
Place: Store, Building
Object: Machine, product, and
Car
Event: Sale, Registration,
Renewal

1 Lecture 1 – ER-Model
Entity Sets

An entity set is a set of entities of the same type that share the same
properties.
Examples: set of all persons, companies, holidays, Students.

Entities are represented by their properties, which also called attributes. All
attributes have their separate values. For example, a student entity may
have a name, age, class, as attributes.

1 Lecture 1 – ER-Model
Relationship

Relationship is nothing but an association among two or more entities.

Relationship is represented by the Diamond.

E.g., Shiva works in the Chemistry department.

For example:

 A student attends a
lecture
 A lecturer is giving a
lecture.

1 Lecture 1 – ER-Model
Relationship

Relationship is nothing but an association among two or more entities.

E.g., Shiva works in the Chemistry and physics department.

For example:

 A student attends a
lecture
 A lecturer is giving a
lecture.

1 Lecture 1 – ER-Model
Attributes

An entity is represented by a set of attributes, that is descriptive properties

possessed by all members of an entity set.

 Attributes are represented by means of ellipses.

 Every ellipse represents one attribute and is directly connected to its entity
(rectangle).
 Entities are represented by means of their properties, called attributes.
 All attributes have values. For example, a student entity may have name, class,
and age as attributes.

16 Introduction to DBMS
Types of Attributes

17 Introduction to DBMS
Types of Attributes- Simple Attributes

Simple attribute −Simple attributes are atomic values, which cannot be

divided further

For example, a student's phone number is an atomic value of 10digits.

18 Introduction to DBMS
Types of Attributes- Key Attributes

A key attribute can uniquely identify an entity from an entity set. For example,
student roll number can uniquely identify a student from a set of students. Key
attribute is represented by oval same as other attributes however the text of
key attribute is underlined.

19 Introduction to DBMS
Types of Attributes- Composite Attributes

• Composite attribute − Composite

attributes are made of more than one
simple attribute.

• For example, a student's complete

name may have first name and
lastname.

• If the attributes are composite, they

are further divided in a tree like
structure.

• Every node is then connected to its

attribute. That is, composite
attributes are represented by ellipses
that are connected with an ellipse.

20 Introduction to DBMS
Types of Attributes- Single Value Attributes

• Single valued attributes are those

attributes which can take only one
value for a given entity from an
entity set.

21 Introduction to DBMS
Types of Attributes- Multi Value Attributes

• Multi-value attributes may contain

more than one values.

• For example, a person can have

more than one phone number,
emaild , address, etc.

• Multivalued attributes are shown

by by double ellipse.

22 Introduction to DBMS
Types of Attributes- Derived Attributes

Derived attribute − Derived attributes are the attributes that do not exist in the
physical database, but their values are derived from other attributes present in the
database.

For example, average_salary in a department should not be saved directly in the

database, instead it can be derived.

For another example, age can be

Derived from date_of_birth.

Derived attributes
are depicted by dashed ellipse.

23 Introduction to DBMS
Composite Attributes

24 Introduction to DBMS
Relationship

A relationship is represented by diamond shape in ER diagram, it shows the

relationship among entities. There are four types of relationships:

1. One to One
2. One to Many
3. Many to One
4. Many to Many

25 Introduction to DBMS
Relationship

26 Introduction to DBMS
Example of E-R Diagram With Composite, Multivalued, and Derived
Attributes

27 Introduction to DBMS
 Keys and Relationship

KeysinDatabase

For the clarity in DBMS, keys are

preferred and they are
important part of the
arrangement of a table.

A database is made up of
tables, which (tables) are made
up of records.

28 Introduction to DBMS
Types of Keys in Database Management System

Types of Keys in Database Management System: Each key which has the
parameter of uniqueness is as follows: made up of fields.

1. Primary Key
2. Candidate Key
3. Super Key
4. Foreign Key
5. Unique Key

29 Introduction to DBMS
Primary Key

 Which is Unique & Can’t be have NULL

Value

 Is the column you choose to maintain

uniqueness in a table at row level.

 Here in Employee table we can choose

either EmployeeID .

30 Introduction to DBMS
To define a field as primary key, following conditions had to be met

 No two rows can have the same primary key value.

 Every row must have a primary key value.

 The primary key field cannot be null..

 Value in a primary key column can never be modified or updated, if any

foreign key refers to that primary key.

31 Introduction to DBMS
Super key

 Super Key is defined as a set of attributes within a table that can uniquely
identify each record within a table. Super Key is a superset of Candidate
key.

 This means that a super key may have some extra attribute which isn't
necessary for uniquely identifying the rows in the table.

 Example: Inthe given Student Table we can have the following keys as
the super key.

32 Introduction to DBMS
Super key

{Roll_no}
{Registration_no}
{Roll_no, Registration_no},
{Roll_no, Name}
{Name, Registration_no}
{Roll_no, Name, Registration_no}

 All the above keys are able to uniquely identify each row.
 So, each of these keys is super key.

 Here you can see that by using Roll_no only, we can uniquely identify the
rows but if you are making a super key.
 then you will try to find all the possible cases of keys that can be used to identify
data uniquely.

33 Introduction to DBMS
Super key examples

• EmployeeID +EmployeeName is a Super Key.

• Super key stands for superset of a key.

• A Super Key is a set of one or more attributes that

are taken collectively and can identify all other
attributes uniquely

34 Introduction to DBMS
Secondary or Alternative key

The rejected candidate keys as primary keys are called as secondary or alternative
keys.
Example:

35 Introduction to DBMS
Candidate Key

A candidate key is a minimal super key or a super key with no redundant

attribute.

It is called a minimal superkey because we select a candidate key from a set of super
key such that selected candidate key is the minimum attribute required to uniquely
identify the table.

It is selected from the set of the super key which means that all candidate keys are
super key. Candidate Keys are not allowed to have NULL values.

36 Introduction to DBMS
Candidate key

{Roll_no}
{Registration_no}
{Roll_no, Registration_no},
{Roll_no, Name}
{Name, Registration_no}
{Roll_no, Name, Registration_no}

 Ifthe subset of the candidate key is a super key, then that candidate key is
not a valid candidate key.

 Example: In the above example, we had 6 super keys but all of them cannot
become a candidate key. Only those super keys would become a candidate key
which have no redundant attributes.

 Hence Roll No and Registration no become candidate keys.

37 Introduction to DBMS
Foreign key

• Generally foreign key is a primary key from one table, which has a relationship with
another table.

39 Introduction to DBMS
Unique Key

Unique key is same as primary with the

difference being the existence of null.

Unique key field allows one value as

NULL value.

40 Introduction to DBMS
Lecture 5

Entity types: Weak and strong

entity sets
Entity set types

Weak Entity Set - The entity set which does not have sufficient attributes to form a
primary key is called as Weak entity set.

A weak entity is a type of entity which doesn't have its key attribute.

Double diamond indicates weak relationship.

It can be identified uniquely by considering the primary key of another entity. For
that, weak entity sets need to have participation.

42 Introduction to DBMS
Strong Entity Set

Strong Entity Set - An entity set that has a primary key is called as Strong entity set.

The primary key of a weak entity set is formed by the primary key of the strong
entity set on which the weak entity set is existence dependent plus the weak
entity sets discriminator.

In the example {loan_number, payment_number} acts as primary key for

payment entity set.

43 Introduction to DBMS
Cont.

The relationship between weak entity and strong entity set is called as
Identifying Relationship.

In example, loan-payment is the identifying relationship for payment entity. A

weak entity set is represented by doubly outlined box .and corresponding
identifying relation by a doubly outlined diamond as shown in figure.

44 Introduction to DBMS
Difference between strong and weak entity set

45 Introduction to DBMS
Summary

 The ER model is a high-level data model diagram.

 ER diagrams are a visual tool which is helpful to represent the ER model
 Entity relationship diagram displays the relationships of entity set stored in a
database.
 ER diagrams help you to define terms related to entity relationship modeling.
 ER model is based on three basic concepts: Entities, Attributes & Relationships.
 An entity can be place, person, object, event or a concept, which stores data in
the database
 Relationship is nothing but an association among two or more entities.
 Different types of attributes and Keys in dbms.
 A weak entity is a type of entity which doesn't have its key attribute.
 It is a single-valued property of either an entity-type or a relationship-type.
 It helps you to defines the numerical attributes of the relationship between two
entities or entity sets

46 Introduction to DBMS
Relationship Constraints:
Participation constraints

Constraints

Relationship types usually have certain constraints that limit the

possible combinations.

Whyto use Constraints ?

• To limit the type of data that can go into a table

• To maintain the accuracy and integrity of the data inside table or

relations of entities that may participate in the corresponding
relationship set.

1 Entity–Relationship Data Model

Constraints in ER Model

• Keys are attributes or sets of attributes that uniquely identify an entity

within its entity set.

• Single-value constraintsrequire that a value be unique in certain

contexts.

• Referential integrity constrains require that a value referred to actually

exists in the database.

• Domain constraints specify what set of values an attribute can take.

• General constraints are arbitrary constraints that should hold in the

database.

• Constraints are part of the schema of a database.

1 Entity–Relationship Data Model

Participation constraints

 The participation constraint specifies whether the existence of an entity depends

on its being related to another entity via the relationship type.

 This constraint specifies the minimum number of relationship instances that

each entity can participate in.

 This constraint specifies the number of instances of an entity that are

participating in the relationship type.

There are two types of participation constraints:

1. Total Participation

2. Partial Participation

49 IntrEondtu
ityc–tR
ioen
lattio
onD
shBipM
DSata Model
Total Participation constraints

 It specifies that each entity present in the entity set must mandatorily participate
in at least one relationship instance of that relationship set, for this reason, it is
also called as mandatory participation.

 It is represented using a double line between the entity set and relationship
set .

50 IntrEondtu
ityc–tR
ioen
lattio
onD
shBipM
DSata Model
Total Participation constraints-Example

 It specifies that each student must

be enrolled in at least one course
where the “student” is the entity
set and relationship “enrolled
in” signifies total participation

 It means that every student must

have enrolled at least in one
course

51 IntrEondtu
ityc–tR
ioen
lattio
onD
shBipM
DSata Model
Partial Participation constraints

 It specifies that each entity in the entity set may or may not participate in the
relationship instance of the relationship set, is also called as optional
participation.

 It is represented using a single line between the entity set and relationship set
in the ER diagram

52 IntrEondtu
ityc–tR
ioen
lattio
onD
shBipM
DSata Model
Partial Participation constraints-Example

 A single line between the entities

i.e courses and enrolled in a
relationship signifies the partial
participation.

 which means there might be some

courses where enrollments are not
made i.e enrollments are
optional in that case.

53 IntrEondtu
ityc–tR
ioen
lattio
onD
shBipM
DSata Model
Cardinality constraints

Whatare Relationships in DBMS

 Arelationship in context of DBMSis mutual situation where two
tables in a RDBMSmodel are related to one another.

 The relationship is of the following types –.

 Unary Relationship
 Binary
 n-ary

54 IntrEondtu
ityc–tR
ioen
lattio
onD
shBipM
DSata Model
Unary Relationship

 It is a case when an entity is related

to itself.

 A unary relationship exists when

both the participating entity type
are the same. When such a
relationship is present we say that
the degree of relationship is 1.

55 IntrEondtu
ityc–tR
ioen
lattio
onD
shBipM
DSata Model
Binary Relationship

 It is when there are two entities

which different entities are related
to one another like a student is
related to another entity called
courses, as enroll type of
relationship.

 A binary relationship exists when

exactly two entity type
participates. When such a
relationship is present we say that
the degree is 2

56 IntrEondtu
ityc–tR
ioen
lattio
onD
shBipM
DSata Model
Ternary Relationship

 A ternary relationship exists

when exactly three entity type
participates. When such a
relationship is present we say
that the degree is 3.

 As the number of entity increases

in the relationship, it becomes
complex to convert them into
relational tables.

57 IntrEondtu
ityc–tR
ioen
lattio
onD
shBipM
DSata Model
n-ary Relationship

n-ary relationship is when there is complex

relationship amongst various entities. These are
generally avoided by programmers to keep
database architecture simple.

For example –
Men eating animal, Animals eating animals, Men
eating Plants, Animal eating plants etc etc. All of
them are bound by eating relationship.

58 IntrEondtu
ityc–tR
ioen
lattio
onD
shBipM
DSata Model
Cardinality constraints in DBMS

 Cardinality tells how many times the

entity of an entity set participates in
a relationship.

There are 4 types of

cardinalities

 One to one
 One to many
 Many to one
 Many to many

59 IntrEondtu
ityc–tR
ioen
lattio
onD
shBipM
DSata Model
One to one cardinality constraints in DBMS

 When a single instance of an entity is

associated with a single instance of
another entity, then it is called as
one to one cardinality.

 Here each entity of the entity set

participate only once in the
relationship.

 Example for one to one cardinality

60 IntrEondtu
ityc–tR
ioen
lattio
onD
shBipM
DSata Model
One to Many cardinality constraints in DBMS

 When is a single instance of an entity

is associated with more than one
instance of another entity then this
type of relationship is called one to
many relationships.

 Here entities in one entity set can

take participation in any number of
times in relationships set and entities
in another entity set can take
participation only once in a
relationship set.

 Example for one to many cardinality

61 IntrEondtu
ityc–tR
ioen
lattio
onD
shBipM
DSata Model
Many to one cardinality constraints in DBMS

 When entities in one entity set can

participate only once in a
relationship set and entities in
another entity can participate more
than once in the relationship set,
then such type of cardinality is
called many-to-one.

 In real time a student takes only one

course but a single course can be
taken by any number of students.
so many to one relationship is
observed here.

 Example for many to one cardinality

62 IntrEondtu
ityc–tR
ioen
lattio
onD
shBipM
DSata Model
Many to many cardinality constraints in DBMS

 Here more than one instance of an

entity is associated with more than
one instance of another entity then it
is called many to many relationships.

 In this cardinality, entities in all

entity sets can take participate any
number of times in the relationship
cardinality is many to many.

 In the Real world assume that a

student can take more than one
course and the single course can be
taken by any number of students
this relationship will be many to
many relationship  Example for many to many cardinality

63 IntrEondtu
ityc–tR
ioen
lattio
onD
shBipM
DSata Model
Lecture No: 6

Extended Entity-Relationship
(EER) Model : Generalization :
The Extended features of (Enhanced) ER Model- What is the need?

 As the complexity of data increased in the late 1980s, it became more and
more difficult to use the traditional ER Model for database modelling.

 Hence some improvements or enhancements were made to the existing ER

Model to make it able to handle the complex applications better.

 Hence, as part of the Enhanced ER Model, along with other improvements,

three new concepts were added to the existing ER Model, they were:

• Generalization
• Specialization
• Aggregation

65 IntrEondtu
ityc–tR
ioen
lattio
onD
shBipM
DSata Model
What is Generalization?

Generalization is a bottom-up approach in which two lower level entities

combine to form a higher level entity. In generalization, the higher level
entity can also combine with other lower level entities to make further
higher level entity.

It's more like Superclass and Subclass system, but the only difference is the
approach, which is bottom-up.

Hence, entities are combined to form a more generalised entity, in other

words, sub-classes are combined to form a super-class.

66 IntrEondtu
ityc–tR
ioen
lattio
onD
shBipM
DSata Model
 Example of Generalization

 In a bank there are two different

types of accounts – Current and
Savings, combine to form a super
entity Account.

 It thus follows system like classes,

like super classes and subclasses
right?

 It may also be possible that the

higher level entity may also
combine with further entity to form
a one more higher level entity.

67 IntrEondtu
ityc–tR
ioen
lattio
onD
shBipM
DSata Model
 Example of Generalization

 In generalization, entities are

combined to form a more
generalized entity, i.e., subclasses
are combined to make a superclass.

 Faculty and Student entities can be

generalized and create a higher
level entity Person.

68 IntrEondtu
ityc–tR
ioen
lattio
onD
shBipM
DSata Model
Extended Entity-Relationship
(EER) Model : Specialization
and
Aggregation
Extended Entity-Relationship (EER) Model : Specialization and
Aggregation

• Specialization is opposite to Generalization. It is a top-down approach in which

one higher level entity can be broken down into two lower level entity.

• In specialization, a higher level entity may not have any lower-level entity sets, it's
possible.

70 IntrEondtu
ityc–tR
ioen
lattio
onD
shBipM
DSata Model
Specialization cont.

 Top-down design process; we

designate subgroupings within
an entity set that are distinctive
from other entities in the set.

 While generalization may follow

a bottom up approach.

 Specialization is opposite to
that, it follows a top down
approach rather.
 For example –
 Employee may be decomposed
to further as current employee
entity and ex employee entity.

71 IntrEondtu
ityc–tR
ioen
lattio
onD
shBipM
DSata Model
Specialization Example

72 IntrEondtu
ityc–tR
ioen
lattio
onD
shBipM
DSata Model
Aggregation

Aggregation is a process when relation between two entities is treated as a single

entity.

In the diagram , the relationship

between Center and Course toge
ther, is acting as an Entity, which
is in relationship with another
entity Visitor.

Now in real world, if a Visitor or

a Student visits a Coaching
Centre, he/she will never enquire
about the centre only or just
about the course, rather he/she
will ask enquire about both.

74 IntrEondtu
ityc–tR
ioen
lattio
onD
shBipM
DSata Model
Without Aggregation Example

 Consider the ternary relationship works_on, which we saw earlier

 Suppose we want to record managers for tasks performed by an

employee at a branch

75 IntrEondtu
ityc–tR
ioen
lattio
onD
shBipM
DSata Model
 Aggregation -Examples

• Relationship sets works_on and

manages represent overlapping
information.

 Eliminate this redundancy via

aggregation.

–Treat relationship as an
abstract entity
–Allows relationships between
relationships
–Abstraction of relationship into
new entity

76 IntrEondtu
ityc–tR
ioen
lattio
onD
shBipM
DSata Model
E-R Diagram With Aggregation

Aggregation is simply when we

would consider two different
entities as a single entity
together.

For example –
University offering course can be
considered a same entity, when
viewed from a student entity
perspective.

77 IntrEondtu
ityc–tR
ioen
lattio
onD
shBipM
DSata Model
Summary of Symbols Used in E-R Notation

78 IntrEondtu
ityc–tR
ioen
lattio
onD
shBipM
DSata Model
Summary of Symbols Used in E-R Notation-Attributes

79 IntrEondtu
ityc–tR
ioen
lattio
onD
shBipM
DSata Model
Summary of Symbols Used in E-R Notation-Participation Constraints

80 IntrEondtu
ityc–tR
ioen
lattio
onD
shBipM
DSata Model
Summary of Symbols Used in E-R Specialization and Generalization-

81 IntrEondtu
ityc–tR
ioen
lattio
onD
shBipM
DSata Model
Summary of Symbols Used in Cardinality Constraints / Ratios-

82 IntrEondtu
ityc–tR
ioen
lattio
onD
shBipM
DSata Model
E-R Diagram for a Hospital Management Systems

83 IntrEondtu
ityc–tR
ioen
lattio
onD
shBipM
DSata Model
Summary

 The ER model is a high-level data model diagram.

 ER diagrams are a visual tool which is helpful to represent the ER model
 Entity relationship diagram displays the relationships of entity set stored in a
database.
 ER diagrams help you to define terms related to entity relationship
modeling.
 ER model is based on three basic concepts: Entities, Attributes & Relationships.
 An entity can be place, person, object, event or a concept, which stores data in
the database
 Relationship is nothing but an association among two or more entities.
 Different types of attributes and Keys in dbms.
 A weak entity is a type of entity which doesn't have its key attribute.
 It is a single-valued property of either an entity-type or a relationship-type.
 It helps you to defines the numerical attributes of the relationship between two
entities or entity sets

84 IntrEondtu
ityc–tR
ioen
lattio
onD
shBipM
DSata Model
Thank You
Subject Name: Database Management
System
Unit No.:2
Unit Name: Relational Model

Faculty Name: Mrs. Apurva S. Shinde

Index -

Lecture 7 Introduction to the Relational Model

Lecture 8 Relational schema and concept of keys.

Lecture 9 Mapping the ER Model the Relational Model

Relational Model and relational

2
Algebra
Lecture No: 7

Introduction to the
Relational Model
Relational Model Schemaaaa

• 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.

• Relational Model represents how data is stored in Relational Databases.

Relational Model and relational

4
Algebra
Characteristics of Relational Database

• Each relation in a database must have a distinct or unique name which would
separate it from the other relations in a database.
• A relation must not have two attributes with the same name. Each attribute must
have a distinct name.
• Duplicate tuples must not be present in a relation

Relational Model and relational

5
Algebra
Characteristics of Relational Database

 Each tuple must have exactly one data value for an attribute.

 Tuples in a relation do not have to follow a significant order as the relation is

not order-sensitive.

 The attributes of a relation also do not have to follow certain ordering, it’s
up to the developer to decide the ordering of attributes.

Relational Model and relational

6
Algebra
Example of Relation

attributes
(or columns)

tuples
(or rows)

Relational Model and relational

7
Algebra
Terminology

• Relational Model: Relational model represents data in the form of relations or tables.
• Relational Schema: Schema represents structure of a relation. e.g.; Relational Schema
of STUDENT relation can be represented as:
STUDENT (STUD_NO, STUD_NAME, STUD_PHONE, STUD_STATE,
STUD_COUNTRY, STUD_AGE)
• Relational Instance: The set of values present in a relation at a particular instance of
time is known as relational instance

• Cardinality of a relation: The number of tuples in a relation determines its cardinality

• Degree of a relation: Each column in the tuple is called an attribute. The number of
attributes in a relation determines its degree.
• Domains: A domain definition specifies the kind of data represented by the attribute.

Relational Model and relational

8
Algebra
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
• E.g. multivalued attribute values are not atomic

 The special value null is a member of every domain

Relational Model and relational

9
Algebra
Lecture No:8

Relational schema and

concept of keys.
What is Relation Schema ?

 Relation schema defines the design and structure of the relation.

 It consists of the :
• Relation name
• Set of attributes/field names/column names
• Every attribute would have an associated domain.

Relational Model and relational

11
Algebra
Relation Schema

 Formally ,given domains 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
 Schema of a relation consists of
• attribute definitions
o name
o type/domain
• integrity constraints

Relational Model and relational

12
Algebra
Relation Instance

• An instance of a relation is a set of tuples, also called records.

• 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
• Order of tuples is irrelevant (tuples may be stored in an arbitrary
order)
attributes
(or columns)
customer_name customer_street customer_city

Jones Main Harrison

Smith North Rye tuples
Curry North Rye (or rows)
Lindsay Park Pittsfield

Customer Relation
Relational Model and relational
13
Algebra
Database

• A database consists of multiple relations

• Information about an enterprise is broken up into parts, with each
relation storing one part of the information
• E.g.
account : information about accounts
depositor : which customer owns which account
customer : information about customers

Relational Model and relational

14
Algebra
Relations are Unordered

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

• Example: instructor relation with unordered tuples

Relational Model and relational

15
Algebra
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.
• 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

Relational Model and relational

16
Algebra
Schema Diagram for University Database

Relational Model and relational

17
Algebra
Lecture No: 9

Mapping the ER Model the

Relational Model
Database Design

Main Phases of Database Design

– Conceptual Database Design
– Logical Database Design

EER-Model
• EER is a high-level data model that incorporates the extensions to the
original ER model. Enhanced ERD are high level models that represent the
equirements and complexities of complex database.

Relational Model and relational

19
Algebra
Data Model

Relational Model and relational

20
Algebra
Example of ERD

Relational Model and relational

21
Algebra
 ER-to-Relational Mapping Algorithm

Step 1: Mapping of Regular Entity Types.

• create a relation R that includes all the simple attributes of E

• Choose one of the key attributes of E as the primary key for R

If the chosen key of E is composite, the set of simple

attributes that form it will together form the primary key of R.

22 Lecture 10: Mapping ER to Relational MODEL

Step 1- Result

Relational Model and relational

23
Algebra
ER-to-Relational Mapping Algorithm (cont)

Step 2: Mapping of Weak Entity Types

i. For each weak entity type, create a corresponding relation that includes
all the simple attributes
ii. Add as a foreign key all of the primary key attribute(s) in the entity
corresponding to the owner entity type
iii. The primary key is the combination of all the primary key attributes from
the owner and the partial key of the weak entity, if any

24 Lecture 10: Mapping ER to Relational MODEL

Step 2 -Result

Relational Model and relational

25
Algebra
ER-to-Relational Mapping Algorithm (cont)

Step 3: Mapping of Binary 1:1 Relation Types

For each binary 1:1 relationship type R in the ER schema, identify the relations S
and T that correspond to the entity types participating in R. There are three
possible approaches:
1. Foreign Key approach: Choose one of the relations-S, say-and include a
foreign key in S the primary key of T. It is better to choose an entity type with total
participation in R in the role of S.
Example: relation MANAGES is mapped by choosing the participating entity type
DEPARTMENT to serve in the role of S, because its participation in the MANAGES
relationship type is total.

[Link] relation option: An alternate mapping of a 1:1 relationship type is

possible by merging the two entity types and the relationship into a single relation.
This may be appropriate when both participations are total.
3. Cross-reference or relationship relation option: The third alternative is to set up
a third relation R for the purpose of cross-referencing the primary keys of the two
relations S and T representing the entity types.

26 Lecture 10: Mapping ER to Relational MODEL

Step 3- Result

Relational Model and relational

27
Algebra
ER-to-Relational Mapping Algorithm (cont)

Step 4: Mapping of Binary 1:N Relationship Types.

• For each regular binary 1:N relationship type R, identify the relation S that
represent the participating entity type at the N-side of the relationship type.
• Include as foreign key in S the primary key of the relation T that represents the
other entity type participating in R.

• Include any simple attributes of the 1:N relation type as attributes of S.

Example: 1:N relationship types WORKS_FOR, CONTROLS, and

SUPERVISION in the figure. For WORKS_FOR we include the primary key
DNUMBER of the DEPARTMENT relation as foreign key in the EMPLOYEE
relation and call it DNO.

28 Lecture 10: Mapping ER to Relational MODEL

Step 4 - Result

Relational Model and relational

29
Algebra
 ER-to-Relational Mapping Algorithm (cont)

Step 5: Mapping of Binary M:N Relationship Types.

• For each regular binary M:N relationship type R, create a new relation S to represent
R.

• Include as foreign key attributes in S the primary keys of the relations that represent
the participating entity types; their combination will form the primary key of S.
• Also include any simple attributes of the M:N relationship type (or simple components
of composite attributes) as attributes of S.

Example: The M:N relationship type WORKS_ON from the ER diagram is

mapped by creating a relation WORKS_ON in the relational database schema. The
primary keys of the PROJECT and EMPLOYEE relations are included as foreign keys
in WORKS_ON and renamed PNO and ESSN, respectively.
Attribute HOURS in WORKS_ON represents the HOURS attribute of the relation type.
The primary key of the WORKS_ON relation is the combination of the foreign key
attributes {ESSN, PNO}.

30 Lecture 10: Mapping ER to Relational MODEL

Step 5 - Result

Relational Model and relational

31
Algebra
Differences Between E-R Model and Relational Model

1. The basic difference between E-R Model and Relational Model is that E-R
model specifically deals with entities and their relations. On the other
hand, the Relational Model deals with Tables and relation between the data
of those tables.
2. An E-R Model describes the data with entity set, relationship set and
attributes. However, the Relational model describes the data with the
tuples, attributes and domain of the attribute.
3. One can easily understand the relationship among the data in E-R Model
as compared to Relational Model.
4. E-R Model has Mapping Cardinality as a constraint whereas Relational
Model does not have such constraint.

32 Lecture 10: Mapping ER to Relational MODEL

Thank You
Subject Name: Database Management
System
Unit No.: 2
Unit Name: Relational Model
Faculty Name: Mrs. Apurva S. Shinde
Index

Lecture 10 Relational Algebra Introduction

Lecture 11 Relational Algebra operations

Lecture 12 Set Operations

2 Module 2: Relational Model

Lecture No. :10

Relational Algebra
Introduction
 Relational Query Languages

• Languages for describing queries on a

relational database.
• Structured Query Language (SQL)
– Predominant application-level query
language
– Declarative
• Relational Algebra
– Intermediate language used within
DBMS
– Procedural query language

4 Module 2: Relational Model

Relational Algebra vs Relational Calculus

BASIS FOR
RELATIONAL ALGEBRA RELATIONAL CALCULUS
COMPARISON
Basic Relational Algebra is a Relational Claculus is Declarative
Procedural language. language.

States Relational Algebra states how to Relational Calculus states what

obtain the result. result we have to obtain.

Order Relational Algebra describes the Relational Calculus does not

order in which operations have to specify the order of operations.
be performed.

Domain Relational Algebra is not domain Relational Calculus can be

dependent. domain dependent.

Related It is close to a programming It is close to the natural language.

language.

5 Module 2: Relational Model

Relational algebra

• Relational Algebra is a procedural query language used to query the

database tables to access data in different ways.

6 Module 2: Relational Model

Types of operations in relational algebra

• Two categories:
1. Basic Operations
2. Derived Operations

• Basic/Fundamental Operations:

1. Select (σ)
2. Project (∏)
3. Union (∪)
4. Set Difference (-)
5. Cartesian product (X)
6. Rename (ρ)
7 Module 2: Relational Model
Cont..

• Derived Operations:

1. Natural Join (⋈)

2. Left, Right, Full outer join (𝔴, ⟖, 𝔴 )
3. Intersection (∩)
4. Division (÷)

8 Module 2: Relational Model

Relational Algebra Operators

9 Module 2: Relational Model

Lecture No. -11

Relational Algebra Operators

Relational Algebra Basic Operations

• Basic operations which can be performed over single or multiple relations in

order to generate new relations(single or multiple).

• Relational operators can perform certain operations on single

attribute(called unary operator) or multiple attribute(called binary operator).

11
Module 2: Relational Model
Select Operation

• This operation is used to fetch rows from given table or relation on the basis
of given conditions, it is denoted by “Sigma(σ)”.

• Syntax : σ <Condition> (Relation Name)

• For example: Consider the table of relation R(Roll No, Name, Age, Marks).

• Query Used : σ Name and Age>21 (Student_Details)

12 Module 2: Relational Model

Project Operation

• This operation is also used to fetch all the rows/tuples/data according to

the requested attribute.

• It means, using project operation one can simply fetch all the tuples
corresponding to a single attribute or multiple attributes.

• It does not supports any conditions as select operation and is denoted

using “Pie(π)”.

• Syntax : π<attribute>(Relation Name)

13 Module 2: Relational Model

Project Operation Example

• For example: Consider the table of relation R(Roll No, Name, Age, Marks

• Query Used : πMarks(Student_Details)

14 Module 2: Relational Model

Rename Operation

• When operations like project and select are performed to fetch new
results, these results requires renaming.

• They can be renamed using the rename operation which is denoted using
Greek letter “Rho(ρ)”.

• Syntax : ρ<New Name>(New Relation)

15 Module 2: Relational Model

Lecture No: 12

Set Operation
Conditions For Using Set Theory Operators

• To use set theory operators on two relations,

• The two relations must be union compatible.

• Union compatible property means-

• Both the relations must have same number of attributes.
• The attribute domains (types of values accepted by attributes) of both the
relations must be compatible.

17 Module 2: Relational Model

Union Operation

• The Union Operation : The union operation fetches the data from
both tables and projects it accordingly. It is denoted through “Union
Symbol(U)”.

• Both the relations compulsory to have same number of attributes.

• Both the relations compulsory to have same domain for attributes.

• Syntax : X1 U X2

• where X1 & X2 are two different relations satisfying the above two
conditions.

18 Module 2: Relational Model

Union Operation Example

• For example : Consider the two tables with relations X1(Name, Age)
and X2(Name, Age).

• Result:

19 Module 2: Relational Model

Set Difference Operation

• The Set Difference Operation: To fetch the data which is not present
in any one of the relation, set difference operation is used. The set
difference operation is denoted by “Minus(-)”.

• Syntax : X1 - X2 or X2 - X1 ,
• where X1 & X2 are two different relations having some attributes.

• Note : X1 - X2 ≠ X2 - X1 {Not Commutative}

20 Module 2: Relational Model

Set Difference Operation Example

• For example: Consider the two tables with relations X1(Name, Age) and
X2(Name, Age).

• Result:

21
Module 2: Relational Model
Cartesian Product

• The Cartesian product operation will generate the possible combinations

among the tuples from the relations resulting in table containing all the
data.

• It combines the information of two or more relations in one single relation.

• Cartesian product is different from union operation and is denoted by

“Cross(X)”.

• Syntax : A1 * A2
• Where, A1 & A2 are two different relations having some attributes.

22
Module 2: Relational Model
Cartesian Product Example

• For example : Consider the two tables with relations A1(Name, Roll
No) and A2(Name, Roll No).

23 Module 2: Relational Model

Derived Operations:

1. Natural Join (⋈)

2. Left, Right, Full outer join (𝔴, ⟖, 𝔴 )

3. Intersection (∩)

4. Division (÷)

24 Module 2: Relational Model

Join Operation

• To combine two or more tables based on the related attributes.

• It is basically a cross product followed by some more operations like

select, project etc.

Inner Join
• Natural Join
• Theta Join
• Equi Join

Outer Join
• Left Outer Join
• Right Inner Join
• Full Outer Join

25 Module 2: Relational Model

Inner Join

• Inner join is a type of join in which only those tuples are selected which full
fill the required conditions.

• All those tuples which do not satisfy the required conditions are excluded

• Natural Join(⋈)
• Natural Join is a join which is performed if there is a common attribute
between the relations.

• Notation: R1 ⋈ R2 where R1 and R2 are two relations.

26 Module 2: Relational Model

Natural Join(⋈)

• Example: Two tables of Student(S_id, Name, Class, Age, C_id) and

Courses(C_id, C_name).

• Student ⋈ Course

27 Module 2: Relational Model

Theta Join

• Theta join is a join which combines the tuples from different relations
according to the given theta condition.

• The join condition in theta join is denoted by theta (θ) symbol.

• This join uses all kind of comparison operator.

• Notation: where R1 and R2 are relations such that they don't have
any common attribute.

28 Module 2: Relational Model

Theta Join Example

• Example: Two tables of Student(S_id,Name, Std, Age) and Courses

(Class, C_name).

• The above join operations check, if the 'Std' attribute in Student is equal to the
values of the 'Class' attribute of the Course table. If these values are equal
then it is included in the resulting table.

29 Module 2: Relational Model

Equi Join

• Equi Join is a type of theta join where we use only the equality operator.

• Unlike theta join where we can use any operator.

• The previous example which we gave in the theta join is also an example
of equi-join.

30 Module 2: Relational Model

Outer Join

• Difference : In Inner Join, matched rows are returned and unmatched

rows are not returned. But, in outer join, we include those tuples which
meet the given condition along with that, we also add those tuples which
do not meet the required condition.

• The result also includes the tuples from the left and right tables which do
not satisfy the conditions.

• Based on the tuples that are added from left, right or both the tables, the
outer join is further divided into three types.

31 Module 2: Relational Model

Left Outer Join(𝔴)

• All the tuples from left relation are included and only those tuples from
right relation are included which have a common value in the common
attribute on which the join is being performed.

• Notation: R1𝔴 R2 where R1 and R2 are relations.

• Student 𝔴 Course

32 Module 2: Relational Model

Right Outer Join(⟖)

• All the tuples from right relation are included and only those tuples from
left relation are included which have a common value in the common
attribute on which the right join is being performed.

• Notation: R1 ⟖ R2 where R1 and R2 are relations.

33 Module 2: Relational Model

Right Outer Join(⟖) Example

• Example: Two tables, Student(S_id, Name, Class, Age, C_type) and

Courses (C_type, C_name), Then,

• Student ⟖ Course

34
Module 2: Relational Model
Full Outer Join(𝔴)

• All the tuples from the left and right relation which are having the same
value on the common attribute.

• Also, all the remaining tuples which are not common on in both the
relations.

• Notation: R1 𝔴 R2 where R1 and R2 are relations.

35 Module 2: Relational Model

Full Outer Join(𝔴) Example

• Example: Two tables, Student(S_id, Name, Class, Age, C_type) and

Courses (C_type, C_name).

• Student 𝔴 Course

36
Module 2: Relational Model
Intersect Operation

• The intersect operation performs the same function as the intersect

operation in the set theory.

• It is represented by ∩ symbol.

• This operation selects all those tuples from the relation which are common
in both the relation.

• Notation: R1 ∩ R2 where R1 and R2 are two relations.

37
Module 2: Relational Model
Intersect Operation Example

• Example: Two courses table and we want to perform the intersect operation
on table Course_1(C_id, C_name) and Course_2(C_id, C_name).

• Both the tables have same attributes.

• Course_1 ∩ Course_2

38 Module 2: Relational Model

Intersect Operation

• The intersect operation performs the same function as the intersect

operation in the set theory.

• It is represented by ∩ symbol.

• This operation selects all those tuples from the relation which are common
in both the relation.

• Notation: R1 ∩ R2 where R1 and R2 are two relations.

39 Relational Model and relational Algebra

Intersect Operation Example

• Example: Two courses table and we want to perform the intersect operation
on table Course_1(C_id, C_name) and Course_2(C_id, C_name).

• Both the tables have same attributes.

• Course_1 ∩ Course_2

40 Relational Model and relational Algebra

 Division Operator

• Division Operator (÷):

• Division operator A÷B can be applied if and only if:

• Attributes of B is proper subset of Attributes of A.

• The relation returned by division operator will have attributes = (All

attributes of A – All Attributes of B).

• The relation returned by division operator will return those tuples from
relation A which are associated to every B’s tuple.

41
Division Operator…

Consider the relation STUDENT_SPORTS and ALL_SPORTS

42
Division Operator…

• STUDENT_SPORTS÷ ALL_SPORTS

• The operation is valid as attributes in ALL_SPORTS is a proper subset of

attributes in STUDENT_SPORTS.

• The attributes in resulting relation will have attributes

{ROLL_NO,SPORTS}-{SPORTS}=ROLL_NO

• The tuples in resulting relation will have those ROLL_NO which are
associated with all B’s tuple {Badminton, Cricket}. ROLL_NO 1 and 4 are
associated to Badminton only. ROLL_NO 2 is associated to all tuples of B.
So the resulting relation will be:

43
Thank You
Subject Name: Database Management
System
Unit No.: 2
Unit Name: Relational Model

Faculty Name: Mrs. Apurva S. Shinde

Index

Lecture 13 Relational Algebra Queries

2 Module 2: Relational Model

Lecture No. :13

Relational Algebra Queries

Banking Example

Relations:
• branch(branch_name, branch_city, assets)
• customer(customer_name, customer_street, customer_city)
• account(account_number, branch_name, balance)
• loan(loan_number, branch_name, amount)
• depositor(customer_name, account_number)
• borrower(customer_name, loan_number)

4
Module 2: Relational Model and Relationa Algebra
Cont..

• Find all loans of over $1200.

amount > 1200 (loan)

• Find the loan number for each loan of an amount greater than $1200.

loan_number (amount > 1200 (loan))

• Find the names of all customers who have a loan, an account, or

both, from the bank.

customer_name (borrower)  customer_name (depositor)

5 Module 2: Relational Model and Relationa Algebra

Cont..

• Find the names of all customers who have a loan at the Perryridge

branch.customer_name (branch_name=“Perryridge”
(borrower.loan_number = loan.loan_number(borrower x loan)))

• Find the names of all customers who have a loan at the Perryridge
branch but do not have an account at any branch of the bank.

customer_name (branch_name = “Perryridge”

(borrower.loan_number = loan.loan_number(borrower x loan))) –
customer_name(depositor)

Module 2: Relational Model and Relationa

42
Algebra
Cont..

• Find the names of all customers who have a loan at the Perryridge
branch.

l customer_name (branch_name = “Perryridge” (

borrower.loan_number = loan.loan_number (borrower x loan)))

l customer_name(loan.loan_number = borrower.loan_number (
(branch_name = “Perryridge” (loan)) x borrower))

Module 2: Relational Model and Relationa

10
Algebra
Cont..

• Find the names of all customers who have a loan and an account at
bank.

customer_name (borrower)  customer_name (depositor)

• Find the name of all customers who have a loan at the bank and the
loan amount.

customer_name, loan_number, amount (borrower loan)

Module 2: Relational Model and Relationa

43
Algebra
Cont..

• Find all customers who have an account from at least the “Downtown”
and the Uptown” branches.

l Query 1
customer_name (branch_name = “Downtown” (depositor account )) 
customer_name (branch_name = “Uptown” (depositor account))

l Query 2
customer_name, branch_name (depositor account)
 temp(branch_name) ({(“Downtown” ), (“Uptown” )})

Note that Query 2 uses a constant relation.

Module 2: Relational Model and Relationa

44
Algebra
Cont..

• Find all customers who have an account at all branches located in

Brooklyn city.

customer_name, branch_name (depositor account)

 branch_name (branch_city = “Brooklyn” (branch))

Module 2: Relational Model and Relationa

45
Algebra
Thank You
 Subject Name: Database Management
System
Unit No.: 3 & 4
Unit 3 Name: Relational database Design
Unit 4 Name: Structured Query Language
(SQL)

Faculty Name : Mrs. Apurva S. Shinde

Index

Lecture 19
BCNF

Lecture 20 Overview of SQL 4

Lecture 21 Data Types, Data Definition Commands. 16

2 Module 3 & Module 4

Boyce-Codd Normal Form (BCNF)

• BCNF does not allow dependencies between attributes that belong to candidate keys.
• BCNF is a refinement of the third normal form in which it drops the restriction of a non-
key attribute from the 3rd normal form.
• Third normal form and BCNF are not same if the following conditions are true:
– The table has two or more candidate keys
– At least two of the candidate keys are composed of more than one attribute
– The keys are not disjoint i.e. The composite candidate keys share some
attributes

Example 1 - Address (Not in BCNF)

Scheme  {City, Street, ZipCode }
1. Key1  {City, Street }
2. Key2  {ZipCode, Street}
3. No non-key attribute hence 3NF
4. {City, Street}  {ZipCode}
5. {ZipCode}  {City}
6. Dependency between attributes belonging to a key

3 Module 3 & Module 4

Boyce Codd Normal Form (BCNF)

Example 2 - Movie (Not in BCNF)

Scheme  {MovieTitle, MovieID, PersonName, Role, Payment }
1. Key1  {MovieTitle, PersonName}
2. Key2  {MovieID, PersonName}
3. Both role and payment functionally depend on both candidate keys thus 3NF
4. {MovieID}  {MovieTitle}
5. Dependency between MovieID & MovieTitle Violates BCNF

Example 3 - Consulting (Not in BCNF)

Scheme  {Client, Problem, Consultant}
1. Key1  {Client, Problem}
2. Key2  {Client, Consultant}
3. No non-key attribute hence 3NF
4. {Client, Problem}  {Consultant}
5. {Client, Consultant}  {Problem}
6. Dependency between attributess belonging to keys violates BCNF

4 Module 3 & Module 4

BCNF - Decomposition

1. Place the two candidate primary keys in separate entities

2. Place each of the remaining data items in one of the
resulting entities according to its dependency on the
primary key.
Example 1 (Convert to BCNF)
Old Scheme  {City, Street, ZipCode }
New Scheme1  {ZipCode, Street}
New Scheme2  {City, Street}
• Loss of relation {ZipCode}  {City}
Alternate New Scheme1  {ZipCode, Street }
Alternate New Scheme2  {ZipCode, City}

5 Module 3 & Module 4

Decomposition – Loss of Information

1. If decomposition does not cause any loss of information it is

called a lossless decomposition.
2. If a decomposition does not cause any dependencies to be lost
it is called a dependency-preserving decomposition.
3. Any table scheme can be decomposed in a lossless way into a
collection of smaller schemas that are in BCNF form. However
the dependency preservation is not guaranteed.
4. Any table can be decomposed in a lossless way into 3rd normal
form that also preserves the dependencies.
• 3NF may be better than BCNF in some cases

Use your own judgment when decomposing schemas

6 Module 3 & Module 4

BCNF - Decomposition

Example 2 (Convert to BCNF)

Old Scheme  {MovieTitle, MovieID, PersonName, Role, Payment }
New Scheme  {MovieID, PersonName, Role, Payment}
New Scheme  {MovieTitle, PersonName}
• Loss of relation {MovieID}  {MovieTitle}
New Scheme  {MovieID, PersonName, Role, Payment}
New Scheme  {MovieID, MovieTitle}
• We got the {MovieID}  {MovieTitle} relationship back
Example 3 (Convert to BCNF)
Old Scheme  {Client, Problem, Consultant}
New Scheme  {Client, Consultant}
New Scheme  {Client, Problem}

7 Module 3 & Module 4

Lecture 21

Overview of SQL
Overview of SQL- What is SQL?

 Standard command set used to communicate with relational DBMS.

 All tasks related to Relational Database Management like creating table,

querying the database for information , modifying data in the database.

Advantages

 High level language that provides a greater degree of abstraction than

procedural languages.
 Allow the end-users to deal with number of database management systems
where it is available.
 Simple and easy to learn can handle complex situation.
 All SQL operations are performed at set level.
 For digital systems, the variable takes on discrete values.

9 Module 3 & Module 4

Data Models

Model of
system
in client’s Entity model of
mind client’s model

Table model
of entity model Oracle
server

Tables on disk

10 Module 3 & Module 4

Definition of a Relational Database

A relational database is a collection of relations or two-dimensional tables.

Oracle
server

Table Name: EMPLOYEES Table Name: DEPARTMENTS

… …

11 Module 3 & Module 4

Relational Database Management System

Server

User tables Data

dictionary

12 Module 3 & Module 4

Communicating with a RDBMS Using SQL

SQL statement
is entered. Statement is sent to
SELECT department_name Oracle Server.
FROM departments;

Oracle
server

13 Module 3 & Module 4

Advantages of DBMS

 No redundant data
 Data Consistency
 Secure
 Privacy – Limited access
 Easy access to data
 Easy recovery

14 Module 3 & Module 4

Relational Model

Column/Attribute
Student Relation
Name Address Phone_no DOB Semester
Smith . . . III
Jones . . . III
Curry . . . III
Jack . . . III

Record/tuple
Domain

15 Module 3 & Module 4

Example 2- Employee Relation

ID Name Age Salary

1 Adam 34 13000
2 Alex 28 15000
3 Stuart 20 18000
4 Ross 42 19020

16 Module 3 & Module 4

What is a Record/Tuple, Column/Attribute, Domain?

 A single entry in a table is called a Record or Row.

1 Adam 34 13000

17 Module 3 & Module 4

SQL (Structured Query Language)

 Standard Relational Database Language

 Allows to create DB

 Allows to manipulate DB

 Supported Features:

• Data Definition Language

• Data Manipulation Language
• Data Control Language

18 Module 3 & Module 4

What Can SQL do?

 execute queries against a database

 retrieve data from a database

 insert records in a database

 update records in a database

 delete records from a database

 create new databases

 create new tables in a database

19 Module 3 & Module 4

Lecture 22

Data Types, Data Definition

Commands
Data Types in SQL

 char(n): Fixed length character string, with user-specified length n.

 varchar(n) : Variable length character strings, with user-specified maximum

length n.

 Int : Integer (a finite subset of the integers that is machine-dependent).

 number (p,d): Fixed point number, with user-specified precision of p digits,

with n digits to the right of decimal point. Eg: numeric(3,1) allows 44.5 to be
stored exactly

 float(n): Floating point number, with user-specified precision of at least n

digits.

21 Module 3 & Module 4

Data Types in SQL….Continue…..

 DATE:
• It is used to store date and time information .
• It stores century ,month ,day, hour minute and seconds.
• If time is not specified then it stores the time of [Link] at midnight if
not the system time(default in 24 hour format)
• If date information is not provided by default it takes date of first day of
current month.

 RAW: It stores binary data upto 2000 bytes long(256 bytes in 7.X)

 LONG: It is used to store character data upto 2 G.B in size

 LONG RAW: It is used to store binary data upto 2GB in size.

22 Module 3 & Module 4

Data Types in SQL….Continue…..

 CLOB: (Character Large Object):

• It is used to store character strings upto 4 GB in size .There can be
multiple CLOB columns per table.

 BLOB (Binary Large Object):

• It is used to store binary strings upto 44GB in [Link] can be multiple
BLOB columns per table.

 BFILE: It stores the pointer or locator pointing towards operating system.

 NCLOB: N(Nationa)CLOB:-
• It is used to store multibyte character.
Eg:In English one character uses 1 byte

23 Module 3 & Module 4

 SQL Statements

SELECT Data retrieval

INSERT
UPDATE Data manipulation language (DML)
DELETE
MERGE

CREATE
ALTER Data definition language (DDL)
DROP
RENAME
TRUNCATE
COMMIT
ROLLBACK Transaction control
SAVEPOINT

GRANT
REVOKE Data control language (DCL)

Modu2l4
e 3 & Module 4
DATA DEFINITION STATEMENTS

 CREATE
 ALTER
 DROP
 TRUNCATE
 RENAME

25 Module 3 & Module 4

Naming Rules

 Table names and column names:

• Must begin with a letter

• Must be 1–30 characters long
• Must contain only A–Z, a–z, 0–9, _, $, and #
• Must not duplicate the name of another object owned by the same user
• Must not be an Oracle server reserved word

26 Module 3 & Module 4

CREATE TABLE

 Create Table

CREATE TABLE <tablename>

( columnname1 datatype (size),
columnname2 datatype (size),
columnname3 datatype (size));
Table created.

27 Module 3 & Module 4

Creating Tables

 Create the table.

CREATE TABLE dept

(deptno NUMERIC(2),
dname VARCHAR(14),
loc VARCHAR(13));
Table created.

 Confirm table creation.

DESCRIBE dept

28 Module 3 & Module 4

The ALTER TABLE Statement

Use the ALTER TABLE statement to:

• Add a new column

• Modify an existing column
• Define a default value for the new column
• Drop a column

29 Module 3 & Module 4

The ALTER TABLE Statement

Use the ALTER TABLE statement to add, modify, or drop columns.

ALTER TABLE table

ADD (column datatype [DEFAULT expr]
[, column datatype]...);

ALTER TABLE table

MODIFY (column datatype [DEFAULT expr]
[, column datatype]...);

ALTER TABLE table

DROP (column);

30 Module 3 & Module 4

Adding a Column

DEPT80 New column

“Add a new
column to
the DEPT80
table.”
DEPT80

31 Module 3 & Module 4

Adding a Column

• You use the ADD clause to add columns.

ALTER TABLE dept80

ADD (job_id VARCHAR(9));
Table altered.

• The new column becomes the last column.

32 Module 3 & Module 4

Modifying a Column

• You can change a column’s data type, size, and default value.

ALTER TABLE dept80

MODIFY (last_name VARCHAR(30));
Table altered.

• A change to the default value affects only subsequent insertions to

the table.

33 Module 3 & Module 4

Dropping a Column

Use the DROP COLUMN clause to drop columns you no longer need from
the table.

ALTER TABLE dept80

DROP COLUMN job_id;
Table altered.

34 Module 3 & Module 4

Dropping a Table

• All data and structure in the table is deleted.

DROP TABLE dept80;

Table dropped.

35 Module 3 & Module 4

Thank You
Subject Name: Database Management
System
Unit No.: 4
Unit Name: SQL

Faculty Name: Mrs. Apurva S. Shinde

Index

22. Data Manipulation commands (DML)

23 Data Control commands, Transaction Control Commands (DCL, TCL)

24 Integrity Constrains

2 Module 2: Relational Model

Lecture No. - 22

Data Manipulation commands

(DML)
DATA MANIPULATION STATEMENTS

 INSERT

 UPDATE

 DELETE

Module 4: Structured Query

4
Language
INSERT

• Insert into Table

INSERT INTO <tablename> VALUES

( VALUE1,’VALUE2’,..);
1 ROW AFFECTED.

INSERT INTO <tablename> VALUES

( &COLUMNNAME1,’&COLUMNNAME’,..);
1 ROW AFFECTED.
SQL>/ TO ENTER MORE VALUES

If datatype is varchar & date give single quotation mark while inserting data in
a table

Module 4: Structured Query

5
Language
INSERT

• Insert into Table

INSERT INTO EMPLOYEE VALUES

( 100,’JOHN’,10000);
1 ROW AFFECTED.

INSERT INTO EMPLOYEE VALUES

( &EMP_ID,’&EMP_NAME’,&SALARY);
Enter Emp_id:100
Enter Emp_name:John
Enter Salary:10000
1 ROW AFFECTED.
SQL>/ TO ENTER MORE VALUES

Module 4: Structured Query

6
Language
UPDATE

• Update values of table

UPDATE <tablename> SET COLUMNNAME = NEWVALUE

WHERE <CONDITION>;

UPDATE EMPLOYEE SET EMP_ID = 80

WHERE EMP_NAME=‘JOHN’;

Module 4: Structured Query

7
Language
DELETE

• Delete entire rows from table

DELETE FROM <TABLENAME>;

DELETE FROM EMPLOYEE;

• Delete particular row from table

DELETE
FROM TABLENAME
WHERE <CONDITION>;

DELETE FROM EMPLOYEE

WHERE DEPT_NO=10;

Module 4: Structured Query

8
Language
Lecture No. - 23

Data Control commands,

Transaction Control Commands
(DCL, TCL)
Outline

 Data Control language (DCL)

 Grant

 Revoke

 Data Transaction Language (DTL)

 Commit

 rollback

10 Module 4: Structured Query Language

Data Control Language(DCL)

 Is used to control privilege in Database. To perform any operation in the

database, such as for creating tables, sequences or views we need
privileges.

 Privileges are of two types,

• System : creating session, table etc are all types of system privilege.
• Object : any command or query to work on tables comes under object
privilege.

 DCL defines two commands,

• Grant : Gives user access privileges to database.
• Revoke : Take back permissions from user.

Module 4: Structured Query

11
Language
Data Control Commands

Module 4: Structured Query

12
Language
Data Control Commands

Module 4: Structured Query

13
Language
Transaction Control Language Commands

 Transaction Control Language(TCL) commands are used to manage

transactions in database. These are used to manage the changes made by
DML statements. It also allows statements to be grouped together into logical
transactions.

Commit command

 Commit command is used to permanently save any transaction into

database.
 Following is Commit command's syntax,

Commit;

14 Module 4: Structured Query Language

Savepoint

Savepoint command

 Savepoint command is used to temporarily save a transaction so that you

can rollback to that point whenever necessary.

 Following is savepoint command's syntax,

Module 4: Structured Query

15
Language
TCL Commands (Cont.)

Rollback command

 This command restores the database to last commited state. It is also use
with savepoint command to jump to a savepoint in a transaction.

 Following is Rollback command's syntax,

16 Module 4: Structured Query Language

Example of Savepoint and Rollback

Module 4: Structured Query

17
Language
Example of Savepoint and Rollback

• The resultant table will look like

Module 4: Structured Query

18
Language
Lecture No. - 24

Integrity Constrains
What are Constraints?

 Constraints enforce rules at the table level.

 Constraints ensures that changes made to a database do not result in a

loss of data consistency.

 Types of Constraints :
– NOT NULL
– UNIQUE
– PRIMARY KEY
– FOREIGN KEY (Referential Integrity)
– CHECK

Module 4: Structured Query

20
Language
Constraint Guidelines

 Create a constraint either:

• At the same time as the table is created, or
• After the table has been created

 Define a constraint at the column or table level.

 View a constraint in the data dictionary.

Module 4: Structured Query

21
Language
Defining Constraints

CREATE TABLE <tablename>

(column1 datatype [CONSTRAINT TYPE],
column2 datatype
CONSTRAINT [CONSTRAINT VARIABLE]
[CONSTRAINT TYPE]
...
);

CREATE TABLE employees(

employee_id NUMBER(6),
first_name VARCHAR2(20),
...
job_id VARCHAR2(10) NOT NULL,
CONSTRAINT emp_emp_id_pk
PRIMARY KEY (EMPLOYEE_ID));

Module 4: Structured Query

22
Language
The NOT NULL Constraint

Ensures that null values are not permitted for the column:

…
NOT NULL constraint NOT NULL Absence of NOT NULL
(No row can contain constraint constraint
a null value for (Any row can contain
this column.) null for this column.)

Module 4: Structured Query

23
Language
The NOT NULL Constraint

Is defined at the column level:

CREATE TABLE employees(

employee_id NUMBER(6),
last_name VARCHAR2(25) NOT NULL, System
salary NUMBER(8,2), named
commission_pct NUMBER(2,2),
hire_date DATE
CONSTRAINT emp_hire_date_nn User
NOT NULL, named
...

Module 4: Structured Query

24
Language
The UNIQUE Constraint

UNIQUE constraint
EMPLOYEES

…
INSERT INTO

Allowed
Not allowed:
already exists

Module 4: Structured Query

25
Language
The UNIQUE Constraint

 Defined at either the table level or the column level:

CREATE TABLE employees(

employee_id NUMBER(6),
last_name VARCHAR2(25) NOT NULL,
email VARCHAR2(25),
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
hire_date DATE NOT NULL,
...
CONSTRAINT emp_email_uk UNIQUE(email));

Module 4: Structured Query

26
Language
The PRIMARY KEY Constraint

DEPARTMENTS
PRIMARY KEY

Not allowed INSERT INTO

(Null value)

Not allowed
(50 already exists)
Module 4: Structured Query
27
Language
The PRIMARY KEY Constraint

 Defined at either the table level or the column level:

CREATE TABLE departments(

department_id NUMBER(4),
department_name VARCHAR2(30)
CONSTRAINT dept_name_nn NOT NULL,
manager_id NUMBER(6),
location_id NUMBER(4),
CONSTRAINT dept_id_pk PRIMARY KEY(department_id));

Module 4: Structured Query

28
Language
The FOREIGN KEY Constraint

DEPARTMENTS

PRIMARY
KEY
…
EMPLOYEES
FOREIGN
KEY

Not allowed
INSERT INTO
… (9 does not
exist)
Allowed

Module 4: Structured Query

29
Language
The FOREIGN KEY Constraint

 Defined at either the table level or the column level:

CREATE TABLE employees(

employee_id NUMBER(6),
last_name VARCHAR2(25) NOT NULL,
email VARCHAR2(25),
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
hire_date DATE NOT NULL,
...
department_id NUMBER(4),
CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)
REFERENCES departments(department_id),
CONSTRAINT emp_email_uk UNIQUE(email));

Module 4: Structured Query

30
Language
FOREIGN KEY Constraint Keywords

 FOREIGN KEY: Defines the column in the child table at the table
constraint level

 REFERENCES: Identifies the table and column in the parent table

 ON DELETE CASCADE: Deletes the dependent rows in the child table

when a row in the parent table is deleted.

 ON DELETE SET NULL: Converts dependent foreign key values to null

Module 4: Structured Query

31
Language
Cascading Actions in Referential Integrity

create table course (

course_id char(5) primary key,
title varchar(20),
dept_name varchar(20) references department
)
create table course (
…
dept_name varchar(20),
foreign key (dept_name) references department
on delete cascade
on update cascade,
...
)
alternative actions to cascade: set null, set default

Module 4: Structured Query

32
Language
The CHECK Constraint

 Defines a condition that each row must satisfy

..., salary NUMBER(2)

CONSTRAINT emp_salary_min
CHECK (salary > 0),...

Module 4: Structured Query

33
Language
Adding a Constraint Syntax

 Use the ALTER TABLE statement to:

• Add or drop a constraint, but not modify its structure

• Enable or disable constraints
• Add a NOT NULL constraint by using the MODIFY clause

ALTER TABLE table

ADD [CONSTRAINT constraint] type (column);

Module 4: Structured Query

34
Language
Adding a Constraint

Add a FOREIGN KEY constraint to the EMPLOYEES table indicating that

a manager must already exist as a valid employee in the EMPLOYEES
table.

ALTER TABLE employees

ADD CONSTRAINT emp_manager_fk
FOREIGN KEY(manager_id)
REFERENCES employees(employee_id);
Table altered.

Module 4: Structured Query

35
Language
Dropping a Constraint

 Remove the manager constraint from the EMPLOYEES table.

ALTER TABLE employees

DROP CONSTRAINT emp_manager_fk;
Table altered.

Module 4: Structured Query

36
Language
References

• Dr. P. S. Deshpande, SQL and PL/SQL for Oracle 10g, Black Book, Dreamteach
Press.

• Korth, Silberchatz, Sudarshan, “Database System Concepts”, 6th Edition McGraw –

Hill.

Module 4: Structured Query

37
Language
Thank You
 Subject Name: Database Management
System
Unit No.: 4
Unit Name: Structured Query Language
(SQL)

Faculty Name : Mrs. Apurva S. Shinde

Index

Lecture 25 Set and string operations, aggregate function

Lecture 26 Views, Joins

Lecture 27 Nested and Complex queries.

Unit 4 : Structured Query Language

2
(SQL)
Lecture No. -25

Set and string operations,

aggregate function
The SET Operators

A B

UNION

A B

INTERSECT

A B

MINUS

Unit 4 : Structured Query Language

4
(SQL)
The UNION Operator

A B

The UNION operator returns results from both queries after eliminating
duplications.

Unit 4 : Structured Query Language

5
(SQL)
Using the UNION Operator

SELECT FN, LN
FROM student
UNION
UNION
SELECT fname, lname
FROM staff;

Unit 4 : Structured Query Language

6
(SQL)
The INTERSECT Operator

A B

Unit 4 : Structured Query Language

7
(SQL)
Using the INTERSECT Operator

SELECT FN, LN
FROM student
INTERSECT
INTERSECT
SELECT fname, lname
FROM staff;

Unit 4 : Structured Query Language

8
(SQL)
The MINUS Operator

A B

Unit 4 : Structured Query Language

9
(SQL)
 The MINUS Operator

SELECT fn, ln
FROM student
MINUS
MINUS
SELECT fname, lname
FROM staff;

SELECT fname, lname

FROM staff
MINUS
MINUS
SELECT fn, ln
FROM student;

Unit 4 : Structured Query Language

10
(SQL)
String Functions

▪ LIKE is the ANSI/ISO standard operator for comparing a column value to

another column value, or to a quoted string. Returns either 1 (TRUE) or 0
(FALSE)

▪ The SQL LIKE operator is only applied on a field of types CHAR or

VARCHAR to match a pattern.

▪ To match a pattern from a word, special characters, and wildcards

characters may have used with LIKE operator.

▪ The LIKE operator can be used within any valid SQL statement, such
as SELECT, INSERT INTO, UPDATE or DELETE.

11 Unit 4 : Structured Query Language (SQL)

Like

▪ Syntax:

SELECT column1, column2, ...

FROM table_name
WHERE columnN LIKE pattern;

12 Unit 4 : Structured Query Language (SQL)

Using the LIKE Condition

▪ Use the LIKE condition to perform wildcard searches of valid search string
values.
▪ Search conditions can contain either literal characters or numbers:
▪ % denotes zero or many characters.
▪ _ denotes one character.

SELECT first_name
FROM employees
WHERE first_name LIKE 'S%';

Unit 4 : Structured Query Language

13
(SQL)
Using the LIKE Condition

▪ You can combine pattern-matching characters.

SELECT last_name
FROM employees
WHERE last_name LIKE '_o%';

▪ You can use the ESCAPE identifier to search for the actual % and _
symbols.

Unit 4 : Structured Query Language

14
(SQL)
Different LIKE operators with '%' and '_' wildcards:

LIKE Operator Description

WHERE CustomerName LIKE 'a%' Finds any values that start with "a"
WHERE CustomerName LIKE '%a' Finds any values that end with "a"
WHERE CustomerName LIKE '%or%' Finds any values that have "or" in any
position
WHERE CustomerName LIKE '_r%' Finds any values that have "r" in the
second position
WHERE CustomerName LIKE 'a_%_%' Finds any values that start with "a" and
a_ _% are at least 3 characters in length
WHERE ContactName LIKE 'a%o' Finds any values that start with "a" and
ends with "o"
WHERE Customer Name LIKE 'a%' Finds any values that start with "a"

15 Unit 4 : Structured Query Language (SQL)

Examples

▪ The following SQL statement selects all customers with a CustomerName starting with "a":
SELECT * FROM Customers
WHERE CustomerName LIKE 'a%';

▪ The following SQL statement selects all customers with a CustomerName ending with "a":
SELECT * FROM Customers
WHERE CustomerName LIKE '%a';

▪ The following SQL statement selects all customers with a CustomerName that have "or" in
any position:
SELECT * FROM Customers
WHERE CustomerName LIKE '%or%';

▪ The following SQL statement selects all customers with a CustomerName that have "r" in
the second position:
SELECT * FROM Customers
WHERE CustomerName LIKE '_r%';

16 Unit 4 : Structured Query Language (SQL)

Examples

▪ The following SQL statement selects all customers with a CustomerName

that starts with "a" and are at least 3 characters in length:
SELECT * FROM Customers
WHERE CustomerName LIKE 'a_%_%';

▪ The following SQL statement selects all customers with a ContactName that
starts with "a" and ends with "o":
SELECT * FROM Customers
WHERE ContactName LIKE 'a%o';

▪ The following SQL statement selects all customers with a CustomerName

that does NOT start with "a":
SELECT * FROM Customers
WHERE CustomerName NOT LIKE 'a%';

17 Unit 4 : Structured Query Language (SQL)

What Are Group Functions?

▪ Group functions operate on sets of rows to give one result per

group.
EMPLOYEES

The maximum
salary in
the EMPLOYEES
table.

…
Unit 4 : Structured Query Language
18
(SQL)
Types of Group Functions

• AVG
• COUNT
• MAX
• MIN
• SUM

Unit 4 : Structured Query Language

19
(SQL)
Group Functions Syntax

SELECT [column,] group_function(column), ...

FROM table
[WHERE condition]
[GROUP BY column]
[ORDER BY column];

Unit 4 : Structured Query Language

20
(SQL)
Using the AVG and SUM Functions

▪ You can use AVG and SUM for numeric data.

SELECT AVG(salary), MAX(salary),

MIN(salary), SUM(salary)
FROM employees
WHERE job_id LIKE '%REP%';

Unit 4 : Structured Query Language

21
(SQL)
Using the MIN and MAX Functions

▪ You can use MIN and MAX for any data type.

SELECT MIN(hire_date), MAX(hire_date)

FROM employees;

Unit 4 : Structured Query Language

22
(SQL)
Using the COUNT Function

▪ COUNT(*) returns the number of rows in a table.

SELECT COUNT(*)
FROM employees
WHERE department_id = 50;

Unit 4 : Structured Query Language

23
(SQL)
Using the COUNT Function

▪ COUNT(expr) returns the number of rows with non-null values for the
expr.
▪ Display the number of department values in the EMPLOYEES table,
excluding the null values.

SELECT COUNT(commission_pct)
FROM employees
WHERE department_id = 80;

Unit 4 : Structured Query Language

24
(SQL)
Group Functions and Null Values

▪ Group functions ignore null values in the column.

SELECT AVG(commission_pct)
FROM employees;

Unit 4 : Structured Query Language

25
(SQL)
Using the NVL Function with Group Functions

The NVL function forces group functions to include null values.

SELECT AVG(NVL(commission_pct, 0))

FROM employees;

Unit 4 : Structured Query Language

26
(SQL)
Creating Groups of Data

EMPLOYEES

4400

9500
The
average
3500
salary
in
EMPLOYEES
6400
table
for each
department.
10033

Unit 4 : Structured Query Language

27
(SQL)
Creating Groups of Data: The GROUP BY Clause Syntax

SELECT column, group_function(column)

FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column <DESCending order>];

▪ Divide rows in a table into smaller groups by using the

▪ GROUP BY clause.

Unit 4 : Structured Query Language

28
(SQL)
Using the GROUP BY Clause

▪ All columns in the SELECT list that are not in group functions must be
in the GROUP BY clause.

SELECT department_id, AVG(salary)

FROM employees
GROUP BY department_id ;

Unit 4 : Structured Query Language

29
(SQL)
Using the GROUP BY Clause

▪ The GROUP BY column does not have to be in the SELECT list.

SELECT AVG(salary)
FROM employees
GROUP BY department_id ;

Unit 4 : Structured Query Language

30
(SQL)
Grouping by More Than One Column

EMPLOYEES

“Add up the
salaries in
the EMPLOYEES
table
for each job,
grouped by
department.
…

Unit 4 : Structured Query Language

31
(SQL)
Using the GROUP BY Clause on Multiple Columns

SELECT dept_id, job_id, SUM(salary)

FROM employees
GROUP BY department_id, job_id ;

Unit 4 : Structured Query Language

32
(SQL)
Illegal Queries Using Group Functions

▪ You cannot use the WHERE clause to restrict groups.

▪ You use the HAVING clause to restrict groups.
▪ You cannot use group functions in the WHERE clause.

SELECT department_id, AVG(salary)

FROM employees
WHERE AVG(salary) > 8000
GROUP BY department_id;

WHERE AVG(salary) > 8000

*
ERROR at line 3:
ORA-00934: group function is not allowed here

Unit 4 : Structured Query Language

33
(SQL)
 ORDER BY Clause

▪ Sort rows with the ORDER BY clause

▪ ASC: ascending order, default
▪ DESC: descending order
▪ The ORDER BY clause comes last in the SELECT statement.

SELECT last_name, job_id, department_id, hire_date

FROM employees
ORDER BY hire_date ;

Unit 4 : Structured Query Language

34
(SQL)
Sorting in Descending Order

SELECT last_name, job_id, department_id, hire_date

FROM employees
ORDER BY hire_date DESC ;

Unit 4 : Structured Query Language

35
(SQL)
Excluding Group Results

EMPLOYEES

The maximum
salary
per department
when it is
greater than
$10,000
…

Unit 4 : Structured Query Language

36
(SQL)
Excluding Group Results: The HAVING Clause

Use the HAVING clause to restrict groups:

▪ Rows are grouped.

▪ The group function is applied.
▪ Groups matching the HAVING clause are displayed.

SELECT column, group_function

FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];

Unit 4 : Structured Query Language

37
(SQL)
Using the HAVING Clause

SELECT department_id, MAX(salary)

FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000 ;

Unit 4 : Structured Query Language

38
(SQL)
Using the HAVING Clause

SELECT job_id, SUM(salary) PAYROLL

FROM employees
WHERE job_id NOT LIKE '%REP%'
GROUP BY job_id
HAVING SUM(salary) > 13000
ORDER BY SUM(salary);

Unit 4 : Structured Query Language

39
(SQL)
Nesting Group Functions

▪ Display the maximum average salary.

SELECT MAX(AVG(salary))
FROM employees
GROUP BY department_id;

Unit 4 : Structured Query Language

40
(SQL)
Lecture No: 26

Views and Joins in SQL

What is a View?

EMPLOYEES Table:

Unit 4 : Structured Query Language

42
(SQL)
Why Use Views?

▪ To restrict data access

▪ To make complex queries easy
▪ To provide data independence
▪ To present different views of the same data

Unit 4 : Structured Query Language

43
(SQL)
Creating a View

▪ You embed a subquery within the CREATE VIEW statement.

CREATE VIEW <viewname>

AS (subquery);

▪ The subquery can contain complex SELECT syntax.

Unit 4 : Structured Query Language

44
(SQL)
Creating a View

▪ Create a view, EMPVU80, that contains details of employees in

department 80.

CREATE VIEW empvu80

AS SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 80;
View created.

▪ Describe the structure of the view by using the DESCRIBE command.

DESCRIBE empvu80

Unit 4 : Structured Query Language

45
(SQL)
Creating a View

▪ Create a view by using column aliases in the subquery.

CREATE VIEW salvu50

AS SELECT employee_id ID_NUMBER, last_name NAME,
salary*12 ANN_SALARY
FROM employees
WHERE department_id = 50;
View created.

▪ Select the columns from this view by the given alias names.

Unit 4 : Structured Query Language

46
(SQL)
Retrieving Data from a View

SELECT *
FROM salvu50;

Unit 4 : Structured Query Language

47
(SQL)
Modifying a View

▪ Modify the EMPVU80 view by using CREATE OR REPLACE VIEW

clause. Add an alias for each column name.

CREATE OR REPLACE VIEW empvu80

(id_number, name, sal, department_id)
AS SELECT employee_id, first_name || ' ' || last_name,
salary, department_id
FROM employees
WHERE department_id = 80;
View created.

▪ Column aliases in the CREATE VIEW clause are listed in the same
order as the columns in the subquery.

Unit 4 : Structured Query Language

48
(SQL)
Creating a Complex View

▪ Create a complex view that contains group functions to display values

from two tables.

CREATE VIEW dept_sum_vu

(name, minsal, maxsal, avgsal)
AS SELECT d.department_name, MIN([Link]),
MAX([Link]),AVG([Link])
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name;
View created.

Unit 4 : Structured Query Language

49
(SQL)
Using the WITH CHECK OPTION Clause

▪ You can ensure that DML operations performed on the view stay within
the domain of the view by using the WITH CHECK OPTION clause.

CREATE OR REPLACE VIEW empvu20

AS SELECT *
FROM employees
WHERE department_id = 20
WITH CHECK OPTION CONSTRAINT empvu20_ck ;
View created.
▪

▪ Any attempt to change the department number for any row in the view
fails because it violates the WITH CHECK OPTION constraint.

Unit 4 : Structured Query Language

50
(SQL)
Denying DML Operations

▪ You can ensure that no DML operations occur by adding the WITH READ
ONLY option to your view definition.

▪ Any attempt to perform a DML on any row in the view results in an Oracle
server error.

Unit 4 : Structured Query Language

51
(SQL)
Denying DML Operations

CREATE OR REPLACE VIEW empvu10

(employee_number, employee_name, job_title)
AS SELECT employee_id, last_name, job_id
FROM employees
WHERE department_id = 10
WITH READ ONLY;
View created.

Unit 4 : Structured Query Language

52
(SQL)
Joins:- Obtaining Data from Multiple Tables
EMPLOYEES DEPARTMENTS

Unit 4 : Structured Query Language

53
(SQL)
Cartesian Products

▪ A Cartesian product is formed when:

• A join condition is omitted

• A join condition is invalid
• All rows in the first table are joined to all rows in the second table

▪ To avoid a Cartesian product, always include a valid join condition in a

WHERE clause.

Unit 4 : Structured Query Language

54
(SQL)
Generating a Cartesian Product

EMPLOYEES (20 rows) DEPARTMENTS (8 rows)

Cartesian
product:
20x8=160 rows
…

Unit 4 : Structured Query Language

55
(SQL)
Types of Joins

Oracle Proprietary SQL: 1999

Joins (8i and prior): Compliant Joins:

▪ Equijoin ▪ Cross joins

▪ Outer join ▪ Natural joins
▪ Left Outer Join
▪ Right Outer Join
▪ Full Outer Join

Unit 4 : Structured Query Language

56
(SQL)
Joining Tables Using Oracle Syntax

▪ Use a join to query data from more than one table.

SELECT [Link], [Link]

FROM table1, table2
WHERE table1.column1 = table2.column2;

▪ Write the join condition in the WHERE clause.

▪ Prefix the column name with the table name when the same column name
appears in more than one table.

Unit 4 : Structured Query Language

57
(SQL)
What is an Equijoin?

EMPLOYEES DEPARTMENTS

… …

Foreign key Primary key

Unit 4 : Structured Query Language
58
(SQL)
Retrieving Records with Equijoins

SELECT employees.employee_id, employees.last_name,

employees.department_id, departments.department_id,
departments.location_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;

Unit 4 : Structured Query Language

59
(SQL)
Qualifying Ambiguous Column Names

▪ Use table prefixes to qualify column names that are in multiple tables.

▪ Improve performance by using table prefixes.

▪ Distinguish columns that have identical names but reside in different

tables by using column aliases.

Unit 4 : Structured Query Language

60
(SQL)
Using Table Aliases

▪ Simplify queries by using table aliases.

▪ Improve performance by using table prefixes.

SELECT e.employee_id, e.last_name, e.department_id,

d.department_id, d.location_id
FROM employees e , departments d
WHERE e.department_id = d.department_id;

Unit 4 : Structured Query Language

61
(SQL)
Joining More than Two Tables

EMPLOYEES DEPARTMENTS LOCATIONS

…
▪ To join n tables together, you need a minimum of n-1 join conditions. For
example, to join three tables, a minimum of two joins is required.

Unit 4 : Structured Query Language

62
(SQL)
Outer Joins

DEPARTMENTS EMPLOYEES

…
There are no employees in
department 190.

Unit 4 : Structured Query Language

63
(SQL)
Outer Joins Syntax

▪ You use an outer join to also see rows that do not meet the join condition.

▪ The Outer join operator is the plus sign (+).

SELECT [Link], [Link]

FROM table1, table2
WHERE [Link](+) = [Link];

SELECT [Link], [Link]

FROM table1, table2
WHERE [Link] = [Link](+);

Unit 4 : Structured Query Language

64
(SQL)
Using Outer Joins

SELECT e.last_name, e.department_id, d.department_name

FROM employees e, departments d
WHERE e.department_id(+) = d.department_id ;

Unit 4 : Structured Query Language

65
(SQL)
JOINS

▪ Cross Join

▪ Natural Join

▪ Left Outer Join

▪ Right Outer Join

▪ Full Outer Join

Unit 4 : Structured Query Language

66
(SQL)
Creating Cross Joins

▪ The CROSS JOIN clause produces the cross-product of two tables.

▪ This is the same as a Cartesian product between the two tables.

SELECT last_name, department_name

FROM employees
CROSS JOIN departments ;

Unit 4 : Structured Query Language

67
(SQL)
Creating Natural Joins

▪ The NATURAL JOIN clause is based on all columns in the two tables that
have the same name.

▪ It selects rows from the two tables that have equal values in all matched
columns.

▪ If the columns having the same names have different data types, an error is
returned.

Unit 4 : Structured Query Language

68
(SQL)
Retrieving Records with Natural Joins

SELECT department_id, department_name,

location_id, city
FROM departments
NATURAL JOIN locations ;

Unit 4 : Structured Query Language

69
(SQL)
LEFT OUTER JOIN

SELECT e.last_name, e.department_id, d.department_name

FROM employees e
LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;

Unit 4 : Structured Query Language

70
(SQL)
RIGHT OUTER JOIN

SELECT e.last_name, e.department_id, d.department_name

FROM employees e
RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;

Unit 4 : Structured Query Language

71
(SQL)
FULL OUTER JOIN

SELECT e.last_name, e.department_id, d.department_name

FROM employees e
FULL OUTER JOIN departments d
ON (e.department_id = d.department_id) ;

Unit 4 : Structured Query Language

72
(SQL)
Lecture No: 27

Nested and Complex Queries

Outline- Nested and Complex Queries

▪ Introduction

▪ Subqueries with the SELECT Statement

▪ Subqueries with the INSERT Statement

▪ Subqueries with the Update Statement

▪ Subqueries with the DELETE Statement

74 Unit 4 : Structured Query Language (SQL)

Introduction

▪ A Subquery or Inner query or Nested query is a query within another SQL

query and embedded within the WHERE clause.

▪ A subquery is used to return data that will be used in the main query as a
condition to further restrict the data to be retrieved.

▪ Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE
statements along with the operators like =, <, >, >=, <=, IN, BETWEEN etc.

75 Unit 4 : Structured Query Language (SQL)

There are a few rules that subqueries must follow:

▪ Subqueries must be enclosed within parentheses.

▪ A subquery can have only one column in the SELECT clause, unless multiple
columns are in the main query for the subquery to compare its selected
columns.

▪ An ORDER BY cannot be used in a subquery, although the main query can

use an ORDER BY. The GROUP BY can be used to perform the same
function as the ORDER BY in a subquery.

▪ Subqueries that return more than one row can only be used with multiple
value operators, such as the IN operator.

▪ A subquery cannot be immediately enclosed in a set function.

▪ The BETWEEN operator cannot be used with a subquery; however, the

BETWEEN operator can be used within the subquery.

76 Unit 4 : Structured Query Language (SQL)

Subqueries with the SELECT Statement

▪ Subqueries are most frequently used with the SELECT statement. The
basic syntax is as follows:

77 Unit 4 : Structured Query Language (SQL)

Subqueries with the SELECT Statement (Cont.)

Example:
▪ Consider the CUSTOMERS table having the following records:
▪ Now, let us check following subquery with
SELECT statement:

This would produce the

following result:

78 Unit 4 : Structured Query Language (SQL)

3. Subqueries with the INSERT Statement

▪ Subqueries also can be used with INSERT statements. The INSERT

statement uses the data returned from the subquery to insert into another
table. The selected data in the subquery can be modified with any of the
character, date or number functions.

▪ The basic syntax is as follows:

79 Unit 4 : Structured Query Language (SQL)

Subqueries with the INSERT Statement (Cont.)

Example:

▪ Consider a table CUSTOMERS_BKP with similar structure as CUSTOMERS

table. Now to copy complete CUSTOMERS table into CUSTOMERS_BKP,
following is the syntax:

80 Unit 4 : Structured Query Language (SQL)

Subqueries with the UPDATE Statement

▪ The subquery can be used in conjunction with the UPDATE statement. Either
single or multiple columns in a table can be updated when using a subquery with
the UPDATE statement.

▪ The basic syntax is as follows:

81 Unit 4 : Structured Query Language (SQL)

Subqueries with the UPDATE Statement (Cont.)

Example:
▪ Assuming, we have CUSTOMERS_BKP table available which is backup of
CUSTOMERS table.
▪ Following example updates SALARY by 0.25 times in CUSTOMERS table for
all the customers whose AGE is greater than or equal to 27:

This would impact two rows and

finally CUSTOMERS table
would have the following
records:

Unit 4 : Structured Query Language

82
(SQL)
Subqueries with the DELETE Statement (Cont.)

Example:
▪ Assuming, we have CUSTOMERS_BKP table available which is backup of
CUSTOMERS table.
▪ Following example deletes records from CUSTOMERS table for all the
customers whose AGE is greater than or equal to 27:

This would impact two rows and

finally CUSTOMERS table would
have the following records:

Unit 4 : Structured Query Language

83
(SQL)
Subqueries with the DELETE Statement

▪ The subquery can be used in conjunction with the DELETE statement like
with any other statements mentioned above.

▪ The basic syntax is as follows:

84 Unit 4 : Structured Query Language (SQL)

Introduction

▪ Derived Relations

▪ The with Clause

85 Unit 4 : Structured Query Language (SQL)

Derived Relations

▪ Find the average account balance of those branches where the average
account balance is greater than $1,000.

select bname, avg-balance from (select bname, avg(balance)

from account

group by bname)

as result(bname, avg-balance)

where avg-balance > 1000

86 Unit 4 : Structured Query Language (SQL)

With Clause

▪ Consider the following query, which selects accounts with the maximum
balance ; if there are many accounts with the same maximum balance, all of
them are selected.

with Max_balance (Value) as

select max (Balance)
from Account
select Account_number
from Account, Max_balance
where [Link]= Max_balance.Value;

87 Unit 4 : Structured Query Language (SQL)

Thank You