Database Systems I
Instructor: Elke A. Rundensteiner
What is a Database System?
a large collection of related data
usually too large to fit in computer memory at once
usually many users want to access this data and do so fast
Focus: information and knowledge, rather than computation
Have you ever used a database application?
E-commerce: books etc at Amazon, B&N
Banks -- your valuable $$ and ATM transactions
Airlines – manage flights to get you places
Universities – manage student enrollment
GIS (Maps) – find restaurants closest to WPI
WWW (World Wide Web) – blobs, wikis, etc. ?
Bio-informatics (genome data)
Data sets increasing in diversity
and volume are everywhere !!!
Why use DBMS, and not files?
Data independence (robustness under change)
Efficient access even on huge data sets
Reduced application development time
Data integrity ensures consistency of data even
with multiple users
Recovery from crashes, security, etc.
A collection of “types” used for describing data
Describes structures for a particular application,
using the given model
Collection of actual data that conforms to given
Database Management System :
Software that allows us to create, use and maintain
a database (conforming to given model).
Relational Data Models
The relational model of data
The most widely used model today.
Main concept: relation, basically a table with
rows and columns.
Every relation has a schema, which describes
the columns, or fields.
Example Database : Relational
Tabular View of Data: Airline System
flightNo start destination miles
Flight 101 BOS LAX 3000
102 PVD LAX 2900
pName ffNumber DoB milesEarned
Passenger Joe 1001 1980 12000
Mary 1002 1981 11000
flightNo ffNumber date
FlewIn 101 1001 Jan 4
102 1002 Jan 5
Tabular view of data is called Relational Model
Levels of Abstraction
• External schema (view) -- View1 View2 View3
describes how users see the
data Logical Schema
• Logical schema – Physical Schema
describes the logical
structures used disk
• Physical schema --
describes files and indexes
Levels of Abstraction: Example
Logical (Conceptual) Schema:
Flight, Passenger, FlewIn tables
Flight table stored as a sorted file
Index on flightNo attribute for Flight relation
Views ( External Schema )
NoOfPassengers (flightNo, date, numPassengers)
Applications insulated from how data is structured
Logical data independence:
Logical schema can change, but views need not change
Protection from changes in logical structure of data.
Physical data independence:
Protection from changes in physical structure of data.
Physical schema such as indexes can change, but logical
schema need not change.
Indexes gives direct access to “necessary” portion
of data, as opposed to sequential access in files.
Estimate expected execution times
Query optimization :
Automatically determine and prepare optimal
access plans for getting to the data
Optimizer = “The Bread and Butter of a DBMS !”
Higher level of data abstraction
Queries are written in a high level language
tailored for database applications
WHERE flightNo = 101
DBMS ensures data is consistent under
E.g.: multiple airline staff trying to reserve a seat
for different customers.
Transactions – grouping multiple instructions
(reads/writes) into one atomic unit
Locks – locking of resources (tables)
Recovery from Crashes
If system crashes in middle of transaction,
recovery must be provided :
Cannot afford to loose data
Ideas: logging, commit/rollback of transactions
Who use databases?
DB application programmers
Data availability, crash recovery
Database tuning (for performance)
Summary : Why study DBMS?
Need to process large amounts of data increasing
Video, WWW, computer games, geographic
information systems (GIS), genome data, digital
DB administrators and programmers hold
DBMS research is one of the most exciting areas in
Computer Science !!