Data base management system WEEK 1 Merged PDF watermark PDF

Preview

Summary

Radar is an electromagnetic system for the detection and location of objects. It operates by
transmitting a particular type of waveform, a pulse-modulated sine wave for example, and
detects the nature of the echo signal. Radar is used to extend the capability of one's senses for
...

Description

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

Database Management Systems Module 01: Cour

verview

Par Department o Indian

s nd Engineering y, Kharagpur

gp.ernet.in oni Majumdar adri 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

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

Module Objectives

Database System Concepts - 6th Edition



To understand the importance of database management systems in modern day applications



To Know Your Course

01.2

©Silberschatz, Korth and Sudarshan

PPD

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

Module Outline

Database System Concepts - 6th Edition



Why Databases?



KYC: Know Your Course 

Course Prerequisite



Course Outline



Course Text Book



Course TAs

01.3

©Silberschatz, Korth and Sudarshan

WHY DATABAS

Database System Concepts - 6th Edition

SWAYAM: SWAYAM:NPTEL-NOC NPTEL-NOCMOOCs MOOCsInstructor: Instructor:Prof. Prof.PPPPDas, Das,IIT IITKharagpur. Kharagpur.Jan-Apr, Jan-Apr,2018 2018

?

01.4

Why Databases? KYC: Know Your Course PPD

©Silberschatz, Korth and Sudarshan

SWAYAM: SWAYAM:NPTEL-NOC NPTEL-NOCMOOCs MOOCsInstructor: Instructor:Prof. Prof.PPPPDas, Das,IIT IITKharagpur. Kharagpur.Jan-Apr, Jan-Apr,2018 2018

Database Management System (DBMS)

Database System Concepts - 6th Edition





DBMS contains information about a particular enterprise 

Collection of interrelated data



Set of programs to access the data



An environment that is both con

nd efficient to use

Database Applications: 

Banking: transactions



Airlines: reservations,



Universities: regis



Sales: custome



Online retail



Manufacturing:



Human resources:



…

chases ng, customized recommendations on, inventory, orders, supply chain mployee records, salaries, tax deductions



Databases can be very large



Databases touch all aspects of our lives 01.5

©Silberschatz, Korth and Sudarshan

SWAYAM: SWAYAM:NPTEL-NOC NPTEL-NOCMOOCs MOOCsInstructor: Instructor:Prof. Prof.PPPPDas, Das,IIT IITKharagpur. Kharagpur.Jan-Apr, Jan-Apr,2018 2018

University Database Example

Database System Concepts - 6th Edition





Application program examples 

Add new students, instructors, and courses



Register students for courses, and



Assign grades to students, com (GPA) and generate transcr

In the early days, database a top of file systems

ate class rosters oint averages built directly on

01.6

©Silberschatz, Korth and Sudarshan

SWAYAM: SWAYAM:NPTEL-NOC NPTEL-NOCMOOCs MOOCsInstructor: Instructor:Prof. Prof.PPPPDas, Das,IIT IITKharagpur. Kharagpur.Jan-Apr, Jan-Apr,2018 2018

Drawbacks of using file systems to store data 

Data redundancy and inconsistency 



Difficulty in accessing data 



Database System Concepts - 6th Edition

Need to write a new program t

h new task

Data isolation 



Multiple file formats, duplication of information in different files

Multiple files and forma

Integrity problems 

Integrity constra in program co



Hard to add ne

nt balance > 0) become “buried” eing stated explicitly ts or change existing ones

01.7

©Silberschatz, Korth and Sudarshan

SWAYAM: SWAYAM:NPTEL-NOC NPTEL-NOCMOOCs MOOCsInstructor: Instructor:Prof. Prof.PPPPDas, Das,IIT IITKharagpur. Kharagpur.Jan-Apr, Jan-Apr,2018 2018

Drawbacks of using file systems to store data (Cont.) 



Atomicity of updates 

Failures may leave database in an inc nsistent state with partial updates carried out



Example: Transfer of funds from either complete or not happe

Concurrent access by multip 

Concurrent access ne



Uncontrolled conc 



to another should

Example: T updating time

ance can lead to inconsistencies ng a balance (say 100) and g money (say 50 each) at the same

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

01.9

KNOW YOUR CO RSE

Database System Concepts - 6th Edition

SWAYAM: SWAYAM:NPTEL-NOC NPTEL-NOCMOOCs MOOCsInstructor: Instructor:Prof. Prof.PPPPDas, Das,IIT IITKharagpur. Kharagpur.Jan-Apr, Jan-Apr,2018 2018

Why Databases? KYC: Know Your Course PPD

©Silberschatz, Korth and Sudarshan

PPD

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

Course Prerequisites 

Essential 

Set Theory 

Definition of a Set – Intentional Definition – Extensional Definition – Set-builder Notation



Membership, Subset, Supe



Operations on sets: – Union, Intersection

Universal Set , 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

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

Course Prerequisites 

Database System Concepts - 6th Edition

Essential 

Relations and Functions 

Definition of Relations



Ordered Pairs and Binary Relatio – Domain and Range – Image, Preimage, Inv – Properties – Refl Total



Definition of F



Properties of



Composition of Fu



Inverse of a Function

, Antisymmetric, Transitive,

njective, Surjective, Bijective ons

 MOOCs:

Discrete Mathematics: https://nptel.ac.in/noc/individual_course.php?id=noc16-ma01 01.11

©Silberschatz, Korth and Sudarshan

PPD

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

Course Prerequisites 

Essential 



Propositional Logic 

Truth Values & Truth Tables



Operators: conjunction (and), disjunctio



Closure under Operations



MOOCs: Discrete Mathematics https://nptel.ac.in/noc/individu

n (not), implication, equivalence

=noc16-ma01

Predicate Logic 

Predicates



Quantification – Existe



MOOCs: Discrete Mathe s: https://nptel.ac.in/noc/individ al_course.php?id=noc16-ma01

Database System Concepts - 6th Edition

al

01.12

©Silberschatz, Korth and Sudarshan

PPD

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

Course Prerequisites 

Essential 

Data Structures 

Array



List



Binary Search Tree – Balanced Tree



B-Tree



Hash Table / Map



MOOCs: Design and https://nptel.ac.in/noc/ind



MOOCs: Fundamental Alg ithms – Design and Analysis: https://nptel.ac.in/noc/individual_course.php?id=noc16-cs24

Database System Concepts - 6th Edition

lgorithms: ourse.php?id=noc17-cs27

01.13

©Silberschatz, Korth and Sudarshan

PPD

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

Course Prerequisites 

Essential 

Algorithms and Programming in C 

Sorting – Merge Sort – Quick Sort



Search – Linear Search – Binary Search – Interpolation Sear



MOOCs: Design and A https://nptel.ac.in/noc/indiv



MOOCs: Introduction to Programming in C: https://nptel.ac.in/noc/individual_course.php?id=noc17-cs43

Database System Concepts - 6th Edition

Algorithms: _course.php?id=noc17-cs27

01.14

©Silberschatz, Korth and Sudarshan

PPD

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

Course Prerequisites 

Desirable 

Object-Oriented Analysis and Design 



MOOCs: Object-Oriented Analysis and D https://nptel.ac.in/noc/individual_course.p

7-cs25

Programming in C++ / Java 

MOOCs: Programming in C++: cs24

Database System Concepts - 6th Edition

/noc/individual_course.php?id=noc17-

01.15

©Silberschatz, Korth and Sudarshan

PPD

Database System Concepts - 6th Edition

Topics

Week 1

Course Overview Introduction to RDBMS

Week 2

Structured Query Langu

Week 3

Relational Algebra Entity-Relationsh

Week 4

Relational Da

Week 5

Applicatio Case S Stor

QL)

cture

Week 6

In Que

Week 7

Query O mization Transactions (Serializability and Recoverability)

Week 8

Concurrency Control Recovery Systems Course Summarization

hing ing

01.16

Application Programmer

Week :

DBA / DBMS Developer

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

Course Outline

©Silberschatz, Korth and Sudarshan

PPD

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

Course Textbook

Database System Concepts - 6th Edition

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

7th Edition will also do

01.17

©Silberschatz, Korth and Sudarshan

PPD

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

Course TAs 

Srijoni Majumdar, [email protected], 9674474267



Himadri B G S Bhuyan, [email protected], 9438911655



Gurunath Reddy M, [email protected],

Database System Concepts - 6th Edition

01.18

37638

©Silberschatz, Korth and Sudarshan

PPD

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

Module Summary

Database System Concepts - 6th Edition



Elucidates the importance of database management systems in modern day applications



Introduced various aspects of the C

01.19

©Silberschatz, Korth and Sudarshan

PPD

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

Instructor and TAs Name

Mail

Mobile

Partha Pratim Das, Instructor

[email protected]

9830030880

Srijoni Majumdar, TA

majumdarsri

9674474267

Himadri B G S Bhuyan, TA

himadrib

Gurunath Reddy M

mgu

Slides used in this pre book.com/ with kind per

mail.com om

9438911655

l.com

9434137638

on are borrowed from http://dbssion 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: Introduc

to DBMS/1

Par Department o Indian

s nd Engineering y, Kharagpur

gp.ernet.in oni Majumdar adri 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

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

Module Recap

Database System Concepts - 6th Edition



Why Databases?



KYC: Know Your Course 

Course Prerequisite



Course Outline



Course Text Book



Course TAs

02.2

©Silberschatz, Korth and Sudarshan

PPD

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

Module Objectives 

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

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

Module Outline

Database System Concepts - 6th Edition



Levels of Abstraction



Schema & Instance



Data Models 

Relational Databases



DDL & DML



SQL



Database Design

02.4

©Silberschatz, Korth and Sudarshan

SWAYAM: SWAYAM:NPTEL-NOC NPTEL-NOCMOOCs MOOCsInstructor: Instructor:Prof. Prof.PPPPDas, Das,IIT IITKharagpur. Kharagpur.Jan-Apr, Jan-Apr,2018 2018

PPD

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

LEVELS OF ABS RACTION

Database System Concepts - 6th Edition

02.5

©Silberschatz, Korth and Sudarshan

SWAYAM: SWAYAM:NPTEL-NOC NPTEL-NOCMOOCs MOOCsInstructor: Instructor:Prof. Prof.PPPPDas, Das,IIT IITKharagpur. Kharagpur.Jan-Apr, Jan-Apr,2018 2018

Levels of Abstraction

Database System Concepts - 6th Edition



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 salary : in end;



View level: appl 

Views can als security purpose

hide details of data types mation (such as an employee’s salary) for

02.6

©Silberschatz, Korth and Sudarshan

View of Data

02.7

An architecture for a database system

Database System Concepts - 6th Edition

SWAYAM: SWAYAM:NPTEL-NOC NPTEL-NOCMOOCs MOOCsInstructor: Instructor:Prof. Prof.PPPPDas, Das,IIT IITKharagpur. Kharagpur.Jan-Apr, Jan-Apr,2018 2018

©Silberschatz, Korth and Sudarshan

SWAYAM: SWAYAM:NPTEL-NOC NPTEL-NOCMOOCs MOOCsInstructor: Instructor:Prof. Prof.PPPPDas, Das,IIT IITKharagpur. Kharagpur.Jan-Apr, Jan-Apr,2018 2018

PPD

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

SCHEMA AND IN TANCE

Database System Concepts - 6th Edition

02.8

©Silberschatz, Korth and Sudarshan

PPD

SWAYAM: SWAYAM:NPTEL-NOC NPTEL-NOCMOOCs MOOCsInstructor: Instructor:Prof. Prof.PPPPDas, Das,IIT IITKharagpur. Kharagpur.Jan-Apr, Jan-Apr,2018 2018

Schemas and Instances

Database System Concepts - 6th Edition



Similar to types and variables in programming languages



Schema 

Logical Schema – the overall logi

ucture of the database



Analogous to type informati

le in a program



Example: The database customers and accou them



Customer Sch

Name 

Custom

t#

Aadhaar ID

Mobile #

Account Sch

Account # 

mation about a set of d the relationship between

Account Type

Interest Rate

Min. Bal.

Balance

Physical Schema– the overall physical structure of the database

02.9

©Silberschatz, Korth and Sudarshan

PPD

SWAYAM: SWAYAM:NPTEL-NOC NPTEL-NOCMOOCs MOOCsInstructor: Instructor:Prof. Prof.PPPPDas, Das,IIT IITKharagpur. Kharagpur.Jan-Apr, Jan-Apr,2018 2018

Schemas and Instances 

Instance 

The actual content of the database at a particular point in time



Analogous to the value of a variab



Customer Instance

Name

Customer ID

Acc

aar ID

Mobile #

Pavan Laha

6728

719289372

9830100291

Lata Kala

8912

918291204829

7189203928

Nand Prabhu

6617

127837291021

8892021892

Database System Concepts - 6th Edition



Account Insta

Account #

Account T

917322

e

Interest Rate

Min. Bal.

Savings

4.0%

5000

7812

372912

Current

0.0%

0

291820

827183

Term Deposit

6.75%

10000

100000

02.10

Balance

©Silberschatz, Korth and Sudarshan

SWAYAM: SWAYAM:NPTEL-NOC NPTEL-NOCMOOCs MOOCsInstructor: Instructor:...