Transcript db2

INTRO TO DATABASES
PART II
IS 340
BY
CHANDRA S. AMARAVADI
1
IN THIS PRESENTATION..
DBMS evolution
file processing, hierarchical, relational
ER Models
Distributed databases
Multi-media databases
Data warehouses
2
DBMS EVOLUTION
3
EVOLUTION OF DBMS
File processing
systems
Hierarchical
systems
Person
1 March 2002 Greece
2 July 2003 Italy
3 ……………..
Photo1
Greece
Photo2
Italy
Relational
systems
PhotoId
4
EVOLUTION OF DBMS..
FILE PROCESSING SYSTEMS:
A system where data was managed by the program
in the form of flat files.



Data in the form of “flat files” (ascii files)
Each program had its own specific files
Data was duplicated across files.
5
EVOLUTION OF DBMS..
PROBLEMS CAUSED BY FILE PROCESSING



Uncontrolled redundancy
Poor data quality (a.k.a.?)
Lack of data sharing
6
HIERARCHICAL MODEL
A model for storing data:

structure is hierarchical (not flat)
one or more root segments
child segments are linked hierarchically

difficult to write queries but very efficient (fast)!


7
HIERARCHICAL MODEL
(D) DEPARTMENT
NAME
(A) ADMIN-STAFF
ASNAME
JOB_TITLE
TOTAL_ENROLL
NO_OF_PHD
(F) FACULTY
FNAME
RANK
DEGREE
What do you call this?
8
RELATIONAL MODEL
Data organized into tables
Data retrieved by using SQL, 4th GL
 joins tables by equating values of cross reference keys.
ACCT
ACCT#
NAME
DT. OPENED
8895
8896
8897
Smith
Farley
Gomez
4/16/00
4/22/01
1/10/00
BALANCE
$35,000
$300
$2,000
9
ER MODELS
(Data Models)
10
DEFINITIONS
Entity
– Individual example of person, place or thing.
Entity Class – Collection of related entities.
Attributes – Properties of entity classes about which we
would like to collect information.
11
AN EXAMPLE ER CHART
Cust#
Name
ENTITY CLASS
CUSTOMER
ATTRIBUTE
RELATIONSHIP
places
ORDERS
Ord#
are for
PRODUCTS
Ord dt
12
DISCUSSION
Draw Data Models (ER models) corresponding to the following
Situations:
Athletes participate in events (qualifier, semi-finals, finals) in various
sports (swimming, boxing etc.).
Clients can book one or more properties with a rental agency. Each
property can be booked by more than one client (for different dates).
Trains arrive and depart from platforms in train stations,
corresponding to the cities that they stop in. A city can have many
Stations.
Airlines operate a number of flights which arrive and depart from
Gates. There could be several flights from a gate, but each flight
is assigned only one gate. Each airline operates a gate or a set of
Gates which are leased to them by the airport.
13
DISCUSSION
Design a database for the following situation: Bellsouth, a
telephone company needs a database to maintain records of
its automobiles, repairs, mechanics and special equipment needed
to repair cars. Mechanics are assigned skill codes based on the
type of repairs they can carry out. For e.g. Class III mechanics
can perform reborings, engine overhauls and transmission repairs.
The repair shops are located in 300 cities and towns throughout
the state. Mechanics can be assigned to only one repair shop at a
time. The application requires knowing what equipment is (for
e.g. diagnostic equipment, hydraulic machinery) is at what location
and also the repairs carried out on each vehicle. For each vehicle
the application requires knowing its identification number,
mileage and repair history.
14
DISTRIBUTED AND
MULTI-MEDIA DATABASES,
DATA WAREHOUSES
15
CENTRALIZED DATABASES
Centralized databases
 The database is in one physical location.
 All applications regardless of whether the clients
are located in Seoul or Detroit need to access data
from that physical location.
 What are the limitations?
16
DISTRIBUTED DATABASES
The data is physically stored in multiple geographical locations
 Replicated -- copies of the database maintained in
multiple sites
 Partitioned -- database is physically divided into
chunks
17
REPLICATED DATABASES..
18
PARTITIONED DATABASES..
Emp ID
Emp Name
Emp Address
11049
Richard
200 Meadow brook
11051
Cathy
13 E. Willow
11054
Hugh
1400 E. Washington
19
MULTI-MEDIA DATABASES
A database that is able to handle multiple data types:
 Conventional information
 Images
 Audio & video
Capable of storing, retrieving and updating this type of
information
Emp ID
Emp Name Emp. Picture Emp Address
Emp.jpg
20
MULTI-MEDIA DATABASES..
APPLICATIONS
Travel bureaus
Hospitals
Manufacturing
Training
21
MULTI-MEDIA DATABASES..
ISSUES
Storage space
Retrieval
Modification
22
DATA WAREHOUSES
DATA WAREHOUSE:
Historical data organized for analysis and decision
making.
DATA MART:
smaller version of data warehouse, specialized by
functional area e.g. marketing
ON-LINE ANALYTICAL PROCESSING (OLAP):
organizes data into a “cube” for decision analysis.
23
DATA WAREHOUSING & OLAP
OPERATIONAL,
HISTORICAL DATA
INTERNAL
DATA
SOURCES
DATA WAREHOUSE
EXTRACT,
TRANSFORM
HISTORICAL
INFORMATION
OLAP
DATA MINING
EXTERNAL
DATA
SOURCES
24
DISCUSSION QUESTIONS











Are Databases and DBMSs one and the same?
Does a record consist of files?
What is the smallest unit of data in the database?
Does data integrity refer to data quality?
What does the detail line of a report consist of?
Is an “employee” an entity class or an attribute?
Is the data dictionary useful while creating the database?
Why do we use SQL?
Is it necessary to use SQL to produce database reports?
Why do we use data models?
Would multi-national organizations prefer centralized
databases?
25
THAT’S ALL
FOLKS!
26