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
