Contents

 Introduction and Database

Modeling using ER Model
 Data Modeling and SQL
 Database Design
 Data Storage and Indexes
 Transaction Processing and
Concurrency Control
 Recovery System
1
Text Book
• “Database System Concepts”,
Abraham Silberschatz, Henry F.
Korth and S. Sudarshan, Mc Graw
Hill
Reference Books
• “Database Management Systems”,
Raghu Ramakrishnan and Johannes
Gehrke, 2002, 3rd Edition.

• “Fundamentals of Database
Systems”, Ramez Elmasri and
Shamkant Navathe, Benjamin
Cummings, 1999, 3rd Edition.
Introduction and
Database Modeling using
ER Model

[Module – 1]
Introduction

 Database Systems
 Types of Database Systems
 Data abstraction
 Data Models
 Architecture of Database
Systems

5
Database Management
System (DBMS)

 Collection of interrelated data

 Set of programs to access the data
 DBMS contains information about a
particular enterprise
 DBMS provides an environment
that is both convenient and
efficient to use.
6
Database Applications

 Banking: all transactions

 Airlines: reservations, schedules
 Universities: registration, grades
 Sales: customers, products,
purchases
 Manufacturing: production,
inventory, orders, supply chain
 Human resources: employee
records, salaries, tax deductions
7
Database Systems Vs
File Systems
 In the early days, database
applications were built on top of
file systems

 System stores permanent records

in various files, and needs
different application programs to
extract records from and add
records to, the appropriate files

8
Drawbacks of File-
processing system
 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 part of program code

Hard to add new constraints or change existing ones
9
Drawbacks of File-
processing system (Cont.)
 Atomicity of updates

Failures may leave database in an inconsistent state with
partial updates carried out

E.g. 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

e.g. two people reading a balance and updating it at the same
time

 Security problems

Database systems offer solutions

to all the above problems 10
View of Data

 A major purpose of a database

system is to provide users with an
abstract view of data.

11
Data Abstraction

 Physical Level

 Logical Level

 View Level

12
Levels of Abstraction
 Physical Level

 Physical level describes how the data

are actually stored.

 It describes complex low-level data

structures in detail.

13
Levels of Abstraction
 Logical Level

 It describes what data are stored in

the database, and what relationships
exists among the data.

type customer = record

id : integer;
name : string;
street : string;
city : string;
end; 14
Levels of Abstraction
 View Level

 Highest level of abstraction describes only

part of the entire database

 Simplifies user interaction with the system

 Users see a set of application programs that

hide details of data types. Views can also
hide information (e.g., salary) for security
purposes.

15
Relationship between
three levels of abstraction

16
Instances and Schemas
 Similar to types, variables and values in
programming languages

 Schema – the logical structure of the

database

 e.g., the database consists of information about a

set of customers and accounts and the relationship
between them
 Analogous to type information of a variable in a
program
 Physical schema: database design at the physical
level
 Logical schema: database design at the logical 17
Instances and Schemas
 Instance – the actual content of the database
at a particular point in time
 Analogous to the value of a variable

 Physical Data Independence – the ability to

modify the physical schema without changing
the logical schema
 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.

18
Data Models
 A collection of conceptual tools
for describing

 data
 data relationships
 data semantics
 data constraints

19
Data Models
 Entity-Relationship model (mainly for
database design)
 Relational model
 Other models
 Object-oriented data model
 Object-relational data model
 Semi-structured data models (XML)
 Older models

network data model

hierarchical data model
20
Entity Relationship Model
 E-R model of real world
 Entities (objects)

e.g. customers, accounts, bank branch

Described by set of attributes
 E.g. account-number and balance may
describe an account
 Relationships between entities

e.g. Account A-101 is held by customer
Johnson

Relationship set depositor associates
customer with his/her accounts
21
Entity Relationship Model
 Entity set
 Set of all entities of the same type

 Relationship set
 Set of all relationships of same type

22
Entity Relationship Model
 E-R diagram
 Graphical representation of overall
structure (schema) of a database
 Components

Rectangles – entity sets

Ellipses - attributes

Diamonds – relationships among entity
sets

Lines – link attributes to entity sets and
entity sets to relationships

23
Entity-Relationship Model
(Cont.)
Example of schema in the
entity-relationship model
Attributes

Relationship Entity sets

24
Entity-Relationship Model
(Cont.)

 E-R model also represents certain

constraints to which the contents
of database must conform
 E.g. mapping cardinalities

25
Entity-Relationship Model
(Cont.)

 Widely used for database design

 Database design in E-R model
usually converted to design in the
relational model which is used for
storage and processing

26
Relational Model

 Collection of tables to represent

both data and the relationships
among those data

27
Relational Model Attributes

28
Relational Model

 Example of a record-based
model
 Most widely used data model

29
Database Languages

 Data Definition Language (DDL)

 To specify the database schema

 Data Manipulation Language (DML)

 To express database queries and
updates

DDL and DML form parts of

single database language e.g.
SQL
30
Data Definition Language
(DDL)
 Specification notation for defining
the database schema
 E.g.
create table account
(account-number
char(10),
balance integer)
 DDL compiler generates a set of
tables stored in a data dictionary
31
Data Definition Language
(DDL) (Cont.)
 Data dictionary contains metadata
(i.e., data about data)
 Database schema
 Data storage and definition
language

Language in which the storage
structure and access methods used
by the database system are specified

Usually an extension of the data
definition language
32
Data Manipulation
Language (DML)
 Language for accessing and
manipulating the data organized by the
appropriate data model
 DML also known as query language
 Two classes of languages
 Procedural – user specifies what data is
required and how to get those data
 Nonprocedural (Declarative) – user
specifies what data is required without
specifying how to get those data
 SQL is the most widely used query
language 33
 SQL (Structured Query
Language)

 SQL: Widely used query language

DML component of SQL is non-procedural

 E.g. find the name of the customer with customer-id 192-83-

7465
select customer.customer-name
from customer
where customer.customer-id = ‘192-83-7465’

 E.g. find the balances of all accounts held by the customer with
customer-id 192-83-7465
select account.balance
from depositor, account
where depositor.customer-id = ‘192-83-7465’ and
34
SQL (Cont.)
 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

35
Database Users
 Users are differentiated by the way they
expect to interact with the system
 Application programmers – interact with
system through DML calls
 Sophisticated users – form requests in a
database query language
 Specialized users – write specialized
database applications that do not fit into the
traditional data processing framework
 Naive users – invoke one of the permanent
application programs that have been written
previously
 E.g. people accessing database over the web, bank tellers, clerical
36
staff
Database Administrator
 Coordinates all the activities of
the database system; the
database administrator has a
good understanding of the
enterprise’s information
resources and needs.

37
Database Administrator
(Cont.)
 Database administrator's duties include:
 Schema definition
 Storage structure and access method
definition
 Schema and physical organization
modification
 Granting user authority to access the
database
 Routine maintenance

Periodically backing up the database

Monitoring performance and responding to
changes in requirements
38
Transaction Management
 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.
39
Database System
Structure

 Functional components of a DBMS

 Storage Manager
 Query Processor

40
Storage Manager
 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 file manager
 efficient storing, retrieving and 41
Storage Manager
 Components

 Authorization and integrity manager

 Transaction Manager

 File Manager

 Buffer Manager

42
Query Processor
 Components

 DDL interpreter
 DML compiler
 Query evaluation engine

43
Overall System Structure

Query processor

Storage manager

44
 Application Architectures

 Two-tier architecture: E.g. client programs using ODBC/JDBC to

communicate with a database
 Three-tier architecture: E.g. web-based applications, and
applications built using “middleware”
45
Test Your Knowledge
Q. 1.
Which type of user would usually
perform the following functions for an
inventory system in a large company?
a) Create a monthly report of current
inventory value.
b) Update the number in stock for specific
items received in shipment.
c) Cancel the user account for an employee
who just retired.
d) Change the structure of the inventory
database to include more information on
each item.
e) Reply to a phone request regarding the
number of a particular item that are
currently in stock. 47
Q. 2.
Which type of user would perform
the following functions for a
billing system in a large
company?

a) Respond to call from customer

regarding current balance due on
their account
b) Write a program to generate
monthly bills
c) Develop schema for new kind of
billing system 48
Q. 3.
Would the user use the DML or
the DDL to do each task?

a) Update a student’s grade point

average
b) Define a new course table
c) Add a column to the student table

49
End of Part I of Module - 1