SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur.

Jan-Apr, 2018

Database Management Systems

Module 01: Course Overview

Partha Pratim Das

Department of Computer Science and Engineering
Indian Institute of Technology, Kharagpur

ppd@cse.iitkgp.ernet.in

Srijoni Majumdar
Himadri B G S Bhuyan
Gurunath Reddy M

Database System Concepts, 6th Ed.

©Silberschatz, Korth and Sudarshan
www.db-book.com
Slides marked with ‘PPD’ are new or edited
 PPD

Module Objectives
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 To understand the importance of database management

systems in modern day applications
 To Know Your Course

Database System Concepts - 6th Edition 01.2 ©Silberschatz, Korth and Sudarshan
 PPD

Module Outline
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Why Databases?
 KYC: Know Your Course
 Course Prerequisite
 Course Outline
 Course Text Book
 Course TAs

Database System Concepts - 6th Edition 01.3 ©Silberschatz, Korth and Sudarshan
 PPD

Why Databases?
KYC: Know Your Course
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

WHY DATABASES?

Database System Concepts - 6th Edition 01.4 ©Silberschatz, Korth and Sudarshan
 Database Management System (DBMS)
 DBMS contains information about a particular enterprise
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 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

Database System Concepts - 6th Edition 01.5 ©Silberschatz, Korth and Sudarshan
 University Database Example
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Application program examples

 Add new students, instructors, and courses
 Register students for courses, and generate class rosters
 Assign grades to students, compute grade point averages
(GPA) and generate transcripts
 In the early days, database applications were built directly on
top of file systems

Database System Concepts - 6th Edition 01.6 ©Silberschatz, Korth and Sudarshan
 Drawbacks of using file systems to store data
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Data redundancy and inconsistency

 Multiple file formats, duplication of information in different files
 Difficulty in accessing data
 Need to write a new program to carry out each new task
 Data isolation
 Multiple files and formats
 Integrity problems
 Integrity constraints (e.g., account balance > 0) become “buried”
in program code rather than being stated explicitly
 Hard to add new constraints or change existing ones

Database System Concepts - 6th Edition 01.7 ©Silberschatz, Korth and Sudarshan
 Drawbacks of using file systems to store data (Cont.)
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Atomicity of updates
 Failures may leave database in an inconsistent state with partial
updates carried out
 Example: Transfer of funds from one account to another should
either complete or not happen at all
 Concurrent access by multiple users
 Concurrent access needed for performance
 Uncontrolled concurrent accesses can lead to inconsistencies
 Example: Two people reading a balance (say 100) and
updating it by withdrawing money (say 50 each) at the same
time
 Security problems
 Hard to provide user access to some, but not all, data

Database systems offer solutions to all the above problems

Database System Concepts - 6th Edition 01.8 ©Silberschatz, Korth and Sudarshan
 PPD

Why Databases?
KYC: Know Your Course
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

KNOW YOUR COURSE

Database System Concepts - 6th Edition 01.9 ©Silberschatz, Korth and Sudarshan
 PPD

Course Prerequisites
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Essential
 Set Theory
 Definition of a Set
– Intentional Definition
– Extensional Definition
– Set-builder Notation
 Membership, Subset, Superset, Power Set, Universal Set
 Operations on sets:
– Union, Intersection, Complement, Difference, Cartesian Product
 De Morgan’s Law
 MOOCs: Discrete Mathematics:
https://nptel.ac.in/noc/individual_course.php?id=noc16-ma01

Database System Concepts - 6th Edition 01.10 ©Silberschatz, Korth and Sudarshan
 PPD

Course Prerequisites
 Essential
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Relations and Functions

 Definition of Relations
 Ordered Pairs and Binary Relations
– Domain and Range
– Image, Preimage, Inverse
– Properties – Reflexive, Symmetric, Antisymmetric, Transitive,
Total
 Definition of Functions
 Properties of Functions – Injective, Surjective, Bijective
 Composition of Functions
 Inverse of a Function
 MOOCs: Discrete Mathematics:
https://nptel.ac.in/noc/individual_course.php?id=noc16-ma01

Database System Concepts - 6th Edition 01.11 ©Silberschatz, Korth and Sudarshan
 PPD

Course Prerequisites
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Essential
 Propositional Logic
 Truth Values & Truth Tables
 Operators: conjunction (and), disjunction (or), negation (not), implication, equivalence
 Closure under Operations
 MOOCs: Discrete Mathematics:
https://nptel.ac.in/noc/individual_course.php?id=noc16-ma01
 Predicate Logic
 Predicates
 Quantification – Existential, Universal
 MOOCs: Discrete Mathematics:
https://nptel.ac.in/noc/individual_course.php?id=noc16-ma01

Database System Concepts - 6th Edition 01.12 ©Silberschatz, Korth and Sudarshan
 PPD

Course Prerequisites
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Essential
 Data Structures
 Array
 List
 Binary Search Tree
– Balanced Tree
 B-Tree
 Hash Table / Map
 MOOCs: Design and Analysis of Algorithms:
https://nptel.ac.in/noc/individual_course.php?id=noc17-cs27
 MOOCs: Fundamental Algorithms – Design and Analysis:
https://nptel.ac.in/noc/individual_course.php?id=noc16-cs24

Database System Concepts - 6th Edition 01.13 ©Silberschatz, Korth and Sudarshan
 PPD

Course Prerequisites
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Essential
 Algorithms and Programming in C
 Sorting
– Merge Sort
– Quick Sort
 Search
– Linear Search
– Binary Search
– Interpolation Search
 MOOCs: Design and Analysis of Algorithms:
https://nptel.ac.in/noc/individual_course.php?id=noc17-cs27
 MOOCs: Introduction to Programming in C:
https://nptel.ac.in/noc/individual_course.php?id=noc17-cs43

Database System Concepts - 6th Edition 01.14 ©Silberschatz, Korth and Sudarshan
 PPD

Course Prerequisites
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Desirable
 Object-Oriented Analysis and Design
 MOOCs: Object-Oriented Analysis and Design:
https://nptel.ac.in/noc/individual_course.php?id=noc17-cs25
 Programming in C++ / Java
 MOOCs: Programming in C++: https://nptel.ac.in/noc/individual_course.php?id=noc17-
cs24

Database System Concepts - 6th Edition 01.15 ©Silberschatz, Korth and Sudarshan
 PPD

Course Outline
Week Topics
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

Application Programmer
Week 1 Course Overview
Introduction to RDBMS
Week 2 Structured Query Language (SQL)
Week 3 Relational Algebra
Entity-Relationship Model
Week 4 Relational Database Design
Week 5 Application Development
Case Studies
Storage and File Structure

DBA / DBMS Developer

Week 6 Indexing and Hashing
Query Processing
Week 7 Query Optimization
Transactions (Serializability and Recoverability)
Week 8 Concurrency Control
Recovery Systems
Course Summarization

Database System Concepts - 6th Edition 01.16 ©Silberschatz, Korth and Sudarshan
 PPD

Course Textbook
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

Website: http://db-book.com/

7th Edition will also do

Database System Concepts - 6th Edition 01.17 ©Silberschatz, Korth and Sudarshan
 PPD

Course TAs
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Srijoni Majumdar, majumdarsrijoni@gmail.com, 9674474267

 Himadri B G S Bhuyan, himadribhuyan@gmail.com, 9438911655
 Gurunath Reddy M, mgurunathreddy@gmail.com, 9434137638

Database System Concepts - 6th Edition 01.18 ©Silberschatz, Korth and Sudarshan
 PPD

Module Summary
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Elucidates the importance of database management systems in

modern day applications
 Introduced various aspects of the Course

Database System Concepts - 6th Edition 01.19 ©Silberschatz, Korth and Sudarshan
 PPD

Instructor and TAs

SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

Name Mail Mobile

Partha Pratim Das, Instructor ppd@cse.iitkgp.ernet.in 9830030880
Srijoni Majumdar, TA majumdarsrijoni@gmail.com 9674474267
Himadri B G S Bhuyan, TA himadribhuyan@gmail.com 9438911655
Gurunath Reddy M mgurunathreddy@gmail.com 9434137638

Slides used in this presentation are borrowed from http://db-

book.com/ with kind permission of the authors.

Edited and new slides are marked with “PPD”.

Database System Concepts - 6th Edition 01.20 ©Silberschatz, Korth and Sudarshan
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

Database Management Systems

Module 02: Introduction to DBMS/1

Partha Pratim Das

Department of Computer Science and Engineering
Indian Institute of Technology, Kharagpur

ppd@cse.iitkgp.ernet.in

Srijoni Majumdar
Himadri B G S Bhuyan
Gurunath Reddy M

Database System Concepts, 6th Ed.

©Silberschatz, Korth and Sudarshan
www.db-book.com
Slides marked with ‘PPD’ are new or edited
 PPD

Module Recap
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Why Databases?
 KYC: Know Your Course
 Course Prerequisite
 Course Outline
 Course Text Book
 Course TAs

Database System Concepts - 6th Edition 02.2 ©Silberschatz, Korth and Sudarshan
 PPD

Module Objectives
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 To familiarize with the basic notions and terminology of database management systems
 To understand the role of data models and languages
 To understand the approaches to database design

Database System Concepts - 6th Edition 02.3 ©Silberschatz, Korth and Sudarshan
 PPD

Module Outline
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Levels of Abstraction
 Schema & Instance
 Data Models
 Relational Databases
 DDL & DML
 SQL
 Database Design

Database System Concepts - 6th Edition 02.4 ©Silberschatz, Korth and Sudarshan
 PPD

Levels of Abstraction
Schema & Instance
Data Models
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

DDL & DML

SQL
Database Design

LEVELS OF ABSTRACTION

Database System Concepts - 6th Edition 02.5 ©Silberschatz, Korth and Sudarshan
 Levels of Abstraction
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Physical level: describes how a record (e.g., instructor) is stored

 Logical level: describes data stored in database, and the relationships
among the data
type instructor = record
ID : string;
name : string;
dept_name : string;
salary : integer;
end;
 View level: application programs hide details of data types
 Views can also hide information (such as an employee’s salary) for
security purposes

Database System Concepts - 6th Edition 02.6 ©Silberschatz, Korth and Sudarshan
 View of Data
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

An architecture for a database system

Database System Concepts - 6th Edition 02.7 ©Silberschatz, Korth and Sudarshan
 PPD

Levels of Abstraction
Schema & Instance
Data Models
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

DDL & DML

SQL
Database Design

SCHEMA AND INSTANCE

Database System Concepts - 6th Edition 02.8 ©Silberschatz, Korth and Sudarshan
 PPD

Schemas and Instances

SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Similar to types and variables in programming languages

 Schema
 Logical Schema – the overall logical structure of the database
 Analogous to type information of a variable in a program
 Example: The database consists of information about a set of
customers and accounts in a bank and the relationship between
them
 Customer Schema

Name Customer ID Account # Aadhaar ID Mobile #

 Account Schema

Account # Account Type Interest Rate Min. Bal. Balance

 Physical Schema– the overall physical structure of the database

Database System Concepts - 6th Edition 02.9 ©Silberschatz, Korth and Sudarshan
 PPD

Schemas and Instances

SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Instance
 The actual content of the database at a particular point in time
 Analogous to the value of a variable
 Customer Instance
Name Customer ID Account # Aadhaar ID Mobile #
Pavan Laha 6728 917322 182719289372 9830100291
Lata Kala 8912 827183 918291204829 7189203928
Nand Prabhu 6617 372912 127837291021 8892021892

 Account Instance
Account # Account Type Interest Rate Min. Bal. Balance
917322 Savings 4.0% 5000 7812
372912 Current 0.0% 0 291820
827183 Term Deposit 6.75% 10000 100000

Database System Concepts - 6th Edition 02.10 ©Silberschatz, Korth and Sudarshan
 Schemas and Instances
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Physical Data Independence – the ability to modify the physical schema

without changing the logical schema
 Analogous to independence of ‘Interface’ and ‘Implementation’ in
Object-Oriented Systems
 Applications depend on the logical schema
 In general, the interfaces between the various levels and components
should be well defined so that changes in some parts do not seriously
influence others.

Database System Concepts - 6th Edition 02.11 ©Silberschatz, Korth and Sudarshan
 PPD

Levels of Abstraction
Schema & Instance
Data Models
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

DDL & DML

SQL
Database Design

DATA MODELS

Database System Concepts - 6th Edition 02.12 ©Silberschatz, Korth and Sudarshan
 Data Models
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 A collection of tools for describing

 Data
 Data relationships
 Data semantics
 Data constraints

 Relational model (we focus in this course)

 Entity-Relationship data model (mainly for database design)
 Object-based data models (Object-oriented and Object-relational)
 Semi-structured data model (XML)
 Other older models:
 Network model
 Hierarchical model

Database System Concepts - 6th Edition 02.13 ©Silberschatz, Korth and Sudarshan
 Relational Model
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 All the data is stored in various tables

 Example of tabular data in the relational model Columns

Rows

Database System Concepts - 6th Edition 02.14 ©Silberschatz, Korth and Sudarshan
 A Sample Relational Database
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

Database System Concepts - 6th Edition 02.15 ©Silberschatz, Korth and Sudarshan
 PPD

Levels of Abstraction
Schema & Instance
Data Models
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

DDL & DML

SQL
Database Design

DDL AND DML

Database System Concepts - 6th Edition 02.16 ©Silberschatz, Korth and Sudarshan
 Data Definition Language (DDL)
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Specification notation for defining the database schema

 Example: create table instructor (
ID char(5),
name varchar(20),
dept_name varchar(20),
salary numeric(8,2))
 DDL compiler generates a set of table templates stored in a data dictionary
 Data dictionary contains metadata (i.e., data about data)
 Database schema
 Integrity constraints
 Primary key (ID uniquely identifies instructors)
 Authorization
 Who can access what

Database System Concepts - 6th Edition 02.17 ©Silberschatz, Korth and Sudarshan
 Data Manipulation Language (DML)
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Language for accessing and manipulating the data organized

by the appropriate data model
 DML also known as query language
 Two classes of languages
 Pure – used for proving properties about computational
power and for optimization
 Relational Algebra (we focus in this course)
 Tuple relational calculus
 Domain relational calculus
 Commercial – used in commercial systems
 SQL is the most widely used commercial language

Database System Concepts - 6th Edition 02.18 ©Silberschatz, Korth and Sudarshan
 PPD

Levels of Abstraction
Schema & Instance
Data Models
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

DDL & DML

SQL
Database Design

SQL

Database System Concepts - 6th Edition 02.19 ©Silberschatz, Korth and Sudarshan
 SQL
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 The most widely used commercial language

 SQL is NOT a Turing machine equivalent language
 Cannot be used to solve all problems that a C program, for
example, can solve
 To be able to compute complex functions SQL is usually
embedded in some higher-level language
 Application programs generally access databases through one of
 Language extensions to allow embedded SQL
 Application program interface (e.g., ODBC/JDBC) which allow
SQL queries to be sent to a database

Database System Concepts - 6th Edition 02.20 ©Silberschatz, Korth and Sudarshan
 PPD

Levels of Abstraction
Schema & Instance
Data Models
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

DDL & DML

SQL
Database Design

DATABASE DESIGN

Database System Concepts - 6th Edition 02.21 ©Silberschatz, Korth and Sudarshan
 Database Design
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

The process of designing the general structure of the database:

 Logical Design – Deciding on the database schema.

Database design requires that we find a “good” collection of
relation schemas.
 Business decision
 What attributes should we record in the database?
 Computer Science decision
 What relation schemas should we have and how
should the attributes be distributed among the
various relation schemas?
 Physical Design – Deciding on the physical layout of the
database

Database System Concepts - 6th Edition 02.22 ©Silberschatz, Korth and Sudarshan
 Database Design (Cont.)
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Is there any problem with this relation?

Database System Concepts - 6th Edition 02.23 ©Silberschatz, Korth and Sudarshan
 PPD

Module Summary
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Familiarized with the basic notions and terminology of database

management systems
 Introduced the role of data models and languages
 Introduced the approaches to database design

Database System Concepts - 6th Edition 02.24 ©Silberschatz, Korth and Sudarshan
 PPD

Instructor and TAs

SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

Name Mail Mobile

Partha Pratim Das, Instructor ppd@cse.iitkgp.ernet.in 9830030880
Srijoni Majumdar, TA majumdarsrijoni@gmail.com 9674474267
Himadri B G S Bhuyan, TA himadribhuyan@gmail.com 9438911655
Gurunath Reddy M mgurunathreddy@gmail.com 9434137638

Slides used in this presentation are borrowed from http://db-

book.com/ with kind permission of the authors.

Edited and new slides are marked with “PPD”.

Database System Concepts - 6th Edition 02.25 ©Silberschatz, Korth and Sudarshan
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

Database Management Systems

Module 03: Introduction to DBMS/2

Partha Pratim Das

Department of Computer Science and Engineering
Indian Institute of Technology, Kharagpur

ppd@cse.iitkgp.ernet.in

Srijoni Majumdar
Himadri B G S Bhuyan
Gurunath Reddy M

Database System Concepts, 6th Ed.

©Silberschatz, Korth and Sudarshan
www.db-book.com
Slides marked with ‘PPD’ are new or edited
 PPD

Module Recap
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Levels of Abstraction
 Schema & Instance
 Data Models
 Relational Databases
 DDL & DML
 SQL
 Database Design

Database System Concepts - 6th Edition 03.2 ©Silberschatz, Korth and Sudarshan
 PPD

Module Objectives
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 To understand models of database management systems

 To familiarize with major components of a database engine
 To familiarize with database internals and architecture
 To understand the historical perspective

Database System Concepts - 6th Edition 03.3 ©Silberschatz, Korth and Sudarshan
 PPD

Module Outline
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Database Design
 OO Relational Model
 XML
 Database Engine
 Storage Management
 Query Processing
 Transaction Management
 Database Users and Administrators
 Database Internals & Architecture
 History of DBMS

Database System Concepts - 6th Edition 03.4 ©Silberschatz, Korth and Sudarshan
 PPD

Database Design
OO Relational Model
XML
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

Database Engine
Database Users and
Administrators
Database Internals &
Architecture
History of DBMS

DATABASE DESIGN

Database System Concepts - 6th Edition 03.5 ©Silberschatz, Korth and Sudarshan
 Database Design
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

The process of designing the general structure of the database:

 Logical Design
 Deciding on the database schema. Database design
requires that we find a “good” collection of relation
schemas.
 Business decision
 What attributes should we record in the database?
 Computer Science decision
 What relation schemas should we have and how
should the attributes be distributed among the
various relation schemas?
 Physical Design
 Deciding on the physical layout of the database

Database System Concepts - 6th Edition 03.6 ©Silberschatz, Korth and Sudarshan
 PPD

Database Design (Cont.)

SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Is there any problem with this relation?

Database System Concepts - 6th Edition 03.7 ©Silberschatz, Korth and Sudarshan
 Design Approaches
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Need to come up with a methodology to ensure that each of the

relations in the database is “good”
 Two ways of doing so:
 Entity Relationship Model (Chapter 7)
 Models an enterprise as a collection of entities and
relationships
 Represented diagrammatically by an entity-relationship
diagram:
 Normalization Theory (Chapter 8)
 Formalize what designs are bad, and test for them

Database System Concepts - 6th Edition 03.8 ©Silberschatz, Korth and Sudarshan
 PPD

Database Design
OO Relational Model
XML
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

Database Engine
Database Users and
Administrators
Database Internals &
Architecture
History of DBMS

OBJECT-RELATIONAL DATA MODELS

Database System Concepts - 6th Edition 03.9 ©Silberschatz, Korth and Sudarshan
 Object-Relational Data Models
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Relational model: flat, “atomic” values

 Object Relational Data Models
 Extend the relational data model by including object orientation
and constructs to deal with added data types
 Allow attributes of tuples to have complex types, including non-
atomic values such as nested relations
 Preserve relational foundations, in particular the declarative
access to data, while extending modeling power
 Provide upward compatibility with existing relational languages

Database System Concepts - 6th Edition 03.10 ©Silberschatz, Korth and Sudarshan
 PPD

Database Design
OO Relational Model
XML
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

Database Engine
Database Users and
Administrators
Database Internals &
Architecture
History of DBMS

XML: EXTENSIBLE MARKUP LANGUAGE

Database System Concepts - 6th Edition 03.11 ©Silberschatz, Korth and Sudarshan
 XML: Extensible Markup Language
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Defined by the WWW Consortium (W3C)

 Originally intended as a document markup language not a
database language
 The ability to specify new tags, and to create nested tag structures
made XML a great way to exchange data, not just documents
 XML has become the basis for all new generation data interchange
formats
 A wide variety of tools is available for parsing, browsing and
querying XML documents/data

Database System Concepts - 6th Edition 03.12 ©Silberschatz, Korth and Sudarshan
 PPD

Database Design
OO Relational Model
XML
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

Database Engine
Database Users and
Administrators
Database Internals &
Architecture
History of DBMS

DATABASE ENGINE

Database System Concepts - 6th Edition 03.13 ©Silberschatz, Korth and Sudarshan
 PPD

Database Engine
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Storage manager
 Query processing
 Transaction manager

Database System Concepts - 6th Edition 03.14 ©Silberschatz, Korth and Sudarshan
 Storage Management
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Storage manager is a program module that provides the interface between the low-level data
stored in the database and the application programs and queries submitted to the system.
 The storage manager is responsible to the following tasks:
 Interaction with the OS file manager
 Efficient storing, retrieving and updating of data
 Issues:
 Storage access
 File organization
 Indexing and hashing

Database System Concepts - 6th Edition 03.15 ©Silberschatz, Korth and Sudarshan
 Query Processing
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

1. Parsing and translation

2. Optimization
3. Evaluation

Database System Concepts - 6th Edition 03.16 ©Silberschatz, Korth and Sudarshan
 Query Processing (Cont.)
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Alternative ways of evaluating a given query

 Equivalent expressions
 Different algorithms for each operation
 Cost difference between a good and a bad way of evaluating a
query can be enormous
 Need to estimate the cost of operations
 Depends critically on statistical information about relations
which the database must maintain
 Need to estimate statistics for intermediate results to compute
cost of complex expressions

Database System Concepts - 6th Edition 03.17 ©Silberschatz, Korth and Sudarshan
 Transaction Management
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 What if the system fails?

 What if more than one user is concurrently updating the same
data?
 A transaction is a collection of operations that performs a single
logical function in a database application
 Transaction-management component ensures that the
database remains in a consistent (correct) state despite system
failures (e.g., power failures and operating system crashes) and
transaction failures.
 Concurrency-control manager controls the interaction among
the concurrent transactions, to ensure the consistency of the
database.

Database System Concepts - 6th Edition 03.18 ©Silberschatz, Korth and Sudarshan
 PPD

Database Design
OO Relational Model
XML
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

Database Engine
Database Users and
Administrators
Database Internals &
Architecture
History of DBMS

DATABASE USERS AND

ADMINISTRATOR

Database System Concepts - 6th Edition 03.19 ©Silberschatz, Korth and Sudarshan
 Database Users and Administrators
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

Database

Database System Concepts - 6th Edition 03.20 ©Silberschatz, Korth and Sudarshan
 PPD

Database Design
OO Relational Model
XML
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

Database Engine
Database Users and
Administrators
Database Internals &
Architecture
History of DBMS

DATABASE INTERNALS AND

ARCHITECTURE

Database System Concepts - 6th Edition 03.21 ©Silberschatz, Korth and Sudarshan
 Database System Internals
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

Database System Concepts - 6th Edition 03.22 ©Silberschatz, Korth and Sudarshan
 Database Architecture
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

The architecture of a database systems is greatly influenced by

the underlying computer system on which the database is running:
 Centralized
 Client-server
 Parallel (multi-processor)
 Distributed

Database System Concepts - 6th Edition 03.23 ©Silberschatz, Korth and Sudarshan
 PPD

Database Design
OO Relational Model
XML
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

Database Engine
Database Users and
Administrators
Database Internals &
Architecture
History of DBMS

HISTORY OF DBMS

Database System Concepts - 6th Edition 03.24 ©Silberschatz, Korth and Sudarshan
 History of Database Systems
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 1950s and early 1960s:

 Data processing using magnetic tapes for storage
 Tapes provided only sequential access
 Punched cards for input
 Late 1960s and 1970s:
 Hard disks allowed direct access to data
 Network and hierarchical data models in widespread use
 Ted Codd defines the relational data model
 Would win the ACM Turing Award for this work
 IBM Research begins System R prototype
 UC Berkeley begins Ingres prototype
 High-performance (for the era) transaction processing

Database System Concepts - 6th Edition 03.25 ©Silberschatz, Korth and Sudarshan
 History (cont.)
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 1980s:
 Research relational prototypes evolve into commercial systems
 SQL becomes industrial standard

 Parallel and distributed database systems

 Object-oriented database systems
 1990s:
 Large decision support and data-mining applications
 Large multi-terabyte data warehouses
 Emergence of Web commerce
 Early 2000s:
 XML and XQuery standards
 Automated database administration
 Later 2000s:
 Giant data storage systems
 Google BigTable, Yahoo PNuts, Amazon, ..

Database System Concepts - 6th Edition 03.26 ©Silberschatz, Korth and Sudarshan
 PPD

Module Summary
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Introduced models of database management systems

 Familiarized with major components of a database engine
 Familiarized with database internals and architecture

Database System Concepts - 6th Edition 03.27 ©Silberschatz, Korth and Sudarshan
 PPD

Instructor and TAs

SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

Name Mail Mobile

Partha Pratim Das, Instructor ppd@cse.iitkgp.ernet.in 9830030880
Srijoni Majumdar, TA majumdarsrijoni@gmail.com 9674474267
Himadri B G S Bhuyan, TA himadribhuyan@gmail.com 9438911655
Gurunath Reddy M mgurunathreddy@gmail.com 9434137638

Slides used in this presentation are borrowed from http://db-book.com/

with kind permission of the authors.

Edited and new slides are marked with “PPD”.

Database System Concepts - 6th Edition 03.28 ©Silberschatz, Korth and Sudarshan
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

Database Management Systems

Module 04: Introduction to Relational Model/1

Partha Pratim Das

Department of Computer Science and Engineering
Indian Institute of Technology, Kharagpur

ppd@cse.iitkgp.ernet.in

Srijoni Majumdar
Himadri B G S Bhuyan
Gurunath Reddy M

Database System Concepts, 6th Ed.

©Silberschatz, Korth and Sudarshan
www.db-book.com
Slides marked with ‘PPD’ are new or edited
 PPD

Module Recap
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Database Design
 OO Relational Model
 XML
 Database Engine
 Storage Management
 Query Processing
 Transaction Management
 Database Users and Administrators
 Database Internals & Architecture
 History of DBMS

Database System Concepts - 6th Edition 04.2 ©Silberschatz, Korth and Sudarshan
 PPD

Module Objectives
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 To understand attributes and their types

 To understand the mathematical structure of relational model – schema, instance and keys
 To familiarize with different types of relational query languages

Database System Concepts - 6th Edition 04.3 ©Silberschatz, Korth and Sudarshan
 PPD

Module Outline
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Attribute Types
 Relation Schema and Instance
 Keys
 Relational Query Languages

Database System Concepts - 6th Edition 04.4 ©Silberschatz, Korth and Sudarshan
 Example of a Relation
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

attributes
(or columns)

tuples
(or rows)

Database System Concepts - 6th Edition 04.5 ©Silberschatz, Korth and Sudarshan
 PPD

Attribute Types
Relation Schema and
Instance
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

Keys
Relational Query
Languages

ATTRIBUTES

Database System Concepts - 6th Edition 04.6 ©Silberschatz, Korth and Sudarshan
 PPD

Attribute Types
 The set of allowed values for each attribute is called the domain of the
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

attribute
 Roll #: Alphanumeric string
 First Name, Last Name: Alpha String
 DoB: Date
 Passport #: String (Letter followed by 7 digits) – nullable
 Aadhaar #: 12-digit number
 Department: Alpha String
 Attribute values are (normally) required to be atomic; that is, indivisible
 The special value null is a member of every domain. Indicated that the
value is “unknown”
 The null value causes complications in the definition of many operations

Roll # First Last DoB Passport # Aadhaar # Department

Name Name
15CS10026 Lalit Dubey 27-Mar-1997 L4032464 1728-6174-9239 Computer

16EE30029 Jatin Chopra 17-Nov-1996 null 3917-1836-3816 Electrical

Database System Concepts - 6th Edition 04.7 ©Silberschatz, Korth and Sudarshan
 PPD

Attribute Types
Relation Schema and
Instance
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

Keys
Relational Query
Languages

SCHEMA AND INSTANCE

Database System Concepts - 6th Edition 04.8 ©Silberschatz, Korth and Sudarshan
 Relation Schema and Instance
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 A1, A2, …, An are attributes

 R = (A1, A2, …, An ) is a relation schema
Example:
instructor = (ID, name, dept_name, salary)
 Formally, given sets D1, D2, …. Dn a relation r is a subset of
D1 x D2 x … x Dn
Thus, a relation is a set of n-tuples (a1, a2, …, an) where each ai  Di
 The current values (relation instance) of a relation are specified by a
table
 An element t of r is a tuple, represented by a row in a table

Database System Concepts - 6th Edition 04.9 ©Silberschatz, Korth and Sudarshan
 Relations are Unordered
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

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

 Example: instructor relation with unordered tuples

Database System Concepts - 6th Edition 04.10 ©Silberschatz, Korth and Sudarshan
 PPD

Attribute Types
Relation Schema and
Instance
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

Keys
Relational Query
Languages

KEYS

Database System Concepts - 6th Edition 04.11 ©Silberschatz, Korth and Sudarshan
 PPD

Keys
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 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?
 A surrogate key (or synthetic key) in a database is a unique identifier for
either an entity in the modeled world or an object in the database
 The surrogate key is not derived from application data, unlike
a natural (or business) key which is derived from application data

Database System Concepts - 6th Edition 04.12 ©Silberschatz, Korth and Sudarshan
 PPD

Keys
 Super Key: Roll #, {Roll #, DoB}
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Candidate Keys: Roll #, {First Name, Last Name}, Passport #, Aadhaar #

 Passport # cannot be a key. Why?
 Null values are allowed for Passport # (a student may not have a passport)
 Primary Key: Roll #
 Secondary / Alternate Key: {First Name, Last Name}, Aadhaar #
 Simple key: Consists of a single attribute
 Composite Key: {First Name, Last Name}
 Consists of more than one attribute to uniquely identify an entity occurrence
 One or more of the attributes, which make up the key, are not simple keys in their own right
Roll # First Last DoB Passport # Aadhaar # Department
Name Name
15CS10026 Lalit Dubey 27-Mar-1997 L4032464 1728-6174-9239 Computer

16EE30029 Jatin Chopra 17-Nov-1996 null 3917-1836-3816 Electrical

15EC10016 Smriti Mongra 23-Dec-1996 G5432849 2045-9271-0914 Electronics

16CE10038 Dipti Dutta 02-Feb-1997 null 5719-1948-2918 Civil

15CS30021 Ramdin Minz 10-Jan-1997 X8811623 4928-4927-5924 Computer

Database System Concepts - 6th Edition 04.13 ©Silberschatz, Korth and Sudarshan
 PPD

Keys
 Foreign key constraint: Value in one relation must appear in another
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Referencing relation
 Enrolment: Foreign Keys – Roll #, Course #
 Referenced relation
 Students, Courses
 A compound key consists of more than one attribute to uniquely identify an
entity occurrence
 Each attribute, which makes up the key, is a simple key in its own right
 {Roll #, Course #} Students
Roll # First Last DoB Passport # Aadhaar # Department
Name Name

Courses
Course # Course Name Credits L-T-P Department

Enrolment
Roll # Course # Instructor ID
Database System Concepts - 6th Edition 04.14 ©Silberschatz, Korth and Sudarshan
 Schema Diagram for University Database
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

Database System Concepts - 6th Edition 04.15 ©Silberschatz, Korth and Sudarshan
 PPD

Attribute Types
Relation Schema and
Instance
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

Keys
Relational Query
Languages

RELATIONAL
QUERY LANGUAGES

Database System Concepts - 6th Edition 04.16 ©Silberschatz, Korth and Sudarshan
 PPD

Relational Query Languages

SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

Procedural vs. Non-procedural or Declarative Paradigms

 Procedural programming requires that the programmer tell the computer what to do
 That is, how to get the output for the range of required inputs
 The programmer must know an appropriate algorithm
 Declarative programming requires a more descriptive style
 The programmer must know what relationships hold between various entities
 Example: Square root of n
 Procedural
1. Guess x0 (close to root of n)
2. i0
3. xi+1  (xi + n / xi) / 2
4. Repeat Step 2 if | xi+1 – xi | > delta
 Declarative
 Root of n is m such that m2 = n

Database System Concepts - 6th Edition 04.17 ©Silberschatz, Korth and Sudarshan
 PPD

Relational Query Languages

SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 “Pure” languages:
 Relational algebra
 Tuple relational calculus
 Domain relational calculus
 The above 3 pure languages are equivalent in computing power
 We will concentrate on relational algebra
 Not Turing-machine equivalent
 Not all algorithms can be expressed in RA
 Consists of 6 basic operations

Database System Concepts - 6th Edition 04.18 ©Silberschatz, Korth and Sudarshan
 PPD

Module Summary
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Introduced the notion of attributes and their types

 Taken an overview of the mathematical structure of relational
model – schema and instance
 Introduced the notion of keys – primary as well as foreign

Database System Concepts - 6th Edition 04.19 ©Silberschatz, Korth and Sudarshan
 PPD

Instructor and TAs

SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

Name Mail Mobile

Partha Pratim Das, Instructor ppd@cse.iitkgp.ernet.in 9830030880
Srijoni Majumdar, TA majumdarsrijoni@gmail.com 9674474267
Himadri B G S Bhuyan, TA himadribhuyan@gmail.com 9438911655
Gurunath Reddy M mgurunathreddy@gmail.com 9434137638

Slides used in this presentation are borrowed from http://db-

book.com/ with kind permission of the authors.

Edited and new slides are marked with “PPD”.

Database System Concepts - 6th Edition 04.20 ©Silberschatz, Korth and Sudarshan
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

Database Management Systems

Module 05: Introduction to Relational Model/2

Partha Pratim Das

Department of Computer Science and Engineering
Indian Institute of Technology, Kharagpur

ppd@cse.iitkgp.ernet.in

Srijoni Majumdar
Himadri B G S Bhuyan
Gurunath Reddy M

Database System Concepts, 6th Ed.

©Silberschatz, Korth and Sudarshan
www.db-book.com
Slides marked with ‘PPD’ are new or edited
 PPD

Module Recap
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Attribute Types
 Relation Schema and Instance
 Keys
 Relational Query Languages

Database System Concepts - 6th Edition 04.2 ©Silberschatz, Korth and Sudarshan
 PPD

Module Objectives
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 To understand relational algebra

 To familiarize with the operators of relational algebra

Database System Concepts - 6th Edition 04.3 ©Silberschatz, Korth and Sudarshan
 PPD

Module Outline
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Operations
 Select
 Project
 Union
 Difference
 Intersection
 Cartesian Product
 Natural Join
 Aggregate Operations

Database System Concepts - 6th Edition 04.4 ©Silberschatz, Korth and Sudarshan
 PPD

Operations
Aggregate Operations
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

RELATIONAL OPERATORS

Database System Concepts - 6th Edition 04.5 ©Silberschatz, Korth and Sudarshan
 Select Operation – selection of rows (tuples)
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Relation r

 A=B ^ D > 5 (r)

Database System Concepts - 6th Edition 04.6 ©Silberschatz, Korth and Sudarshan
 Project Operation – selection of columns (Attributes)
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Relation r:

 A,C (r)

Database System Concepts - 6th Edition 04.7 ©Silberschatz, Korth and Sudarshan
 Union of two relations
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Relations r, s:

 r  s:

Database System Concepts - 6th Edition 04.8 ©Silberschatz, Korth and Sudarshan
 Set difference of two relations
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Relations r, s:

 r – s:

Database System Concepts - 6th Edition 04.9 ©Silberschatz, Korth and Sudarshan
 Set intersection of two relations
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Relation r, s:

 rs

Note: r  s = r – (r – s)

Database System Concepts - 6th Edition 04.10 ©Silberschatz, Korth and Sudarshan
 Joining two relations -- Cartesian-product
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Relations r, s:

 r x s:

Database System Concepts - 6th Edition 04.11 ©Silberschatz, Korth and Sudarshan
 Cartesian-product – naming issue
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Relations r, s: B

 r x s: r.B s.B

Database System Concepts - 6th Edition 04.12 ©Silberschatz, Korth and Sudarshan
 Renaming a Table
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Allows us to refer to a relation, (say E) by more than one name.

 x (E)

returns the expression E under the name X

 Relations r

 r x  s (r) r.A r.B s.A s.B

α 1 α 1
α 1 β 2
β 2 α 1
β 2 β 2

Database System Concepts - 6th Edition 04.13 ©Silberschatz, Korth and Sudarshan
 Composition of Operations
 Can build expressions using multiple operations
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Example: A=C (r x s)

 rxs

 A=C (r x s)

Database System Concepts - 6th Edition 04.14 ©Silberschatz, Korth and Sudarshan
 Joining two relations – Natural Join
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Let r and s be relations on schemas R and S respectively.

Then, the “natural join” of relations R and S is a relation on
schema R  S obtained as follows:
 Consider each pair of tuples tr from r and ts from s.
 If tr and ts have the same value on each of the attributes
in R  S, add a tuple t to the result, where
 t has the same value as tr on r
 t has the same value as ts on s

Database System Concepts - 6th Edition 04.15 ©Silberschatz, Korth and Sudarshan
 Natural Join Example
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Relations r, s:

 Natural Join
n r s

 A, r.B, C, r.D, E ( r.B = s.B ˄ r.D = s.D (r x s)))

Database System Concepts - 6th Edition 04.16 ©Silberschatz, Korth and Sudarshan
 PPD

Operations
Aggregate Operations
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

AGGREGATION OPERATORS

Database System Concepts - 6th Edition 04.17 ©Silberschatz, Korth and Sudarshan
 PPD

Aggregate Operators
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Can we compute:
 SUM
 AVG
 MAX
 MIN

Database System Concepts - 6th Edition 04.18 ©Silberschatz, Korth and Sudarshan
 Notes about Relational Languages
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Each Query input is a table (or set of tables)

 Each query output is a table
 All data in the output table appears in one of the input tables
 Relational Algebra is not Turning complete

Database System Concepts - 6th Edition 04.19 ©Silberschatz, Korth and Sudarshan
 Summary of Relational Algebra Operators
Symbol (Name)                  Example of Use
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

σ
(Selection) σ salary > = 85000 (instructor)
Return rows of the input relation that satisfy the predicate.
Π
(Projection) Π ID, salary (instructor)
Output specified attributes from all rows of the input relation.  Remove 
duplicate tuples from the output.
x
(Cartesian Product) instructor x department

Output pairs of rows from the two input relations that have the same value on 
all attributes that have the same name.
∪
(Union) Π name (instructor)  ∪  Π name (student)
Output the union of tuples from the two input relations.
-
(Set Difference) Π name (instructor)  ‐‐ Π name (student)

Output the set difference of tuples from the two input relations. 
⋈
(Natural Join) instructor ⋈ department

Output pairs of rows from the two input relations that have the same value on 
all attributes that have the same name.

Database System Concepts - 6th Edition 04.20 ©Silberschatz, Korth and Sudarshan
 PPD

Module Summary
SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

 Introduced relational algebra

 Familiarized with the operators of relational algebra

Database System Concepts - 6th Edition 04.21 ©Silberschatz, Korth and Sudarshan
 PPD

Instructor and TAs

SWAYAM: NPTEL-NOC MOOCs Instructor: Prof. P P Das, IIT Kharagpur. Jan-Apr, 2018

Name Mail Mobile

Partha Pratim Das, Instructor ppd@cse.iitkgp.ernet.in 9830030880
Srijoni Majumdar, TA majumdarsrijoni@gmail.com 9674474267
Himadri B G S Bhuyan, TA himadribhuyan@gmail.com 9438911655
Gurunath Reddy M mgurunathreddy@gmail.com 9434137638

Slides used in this presentation are borrowed from http://db-

book.com/ with kind permission of the authors.

Edited and new slides are marked with “PPD”.

Database System Concepts - 6th Edition 04.22 ©Silberschatz, Korth and Sudarshan