Transcript Chapter 1

Data Modeling and Database
Design
Chapter 1:
Database Systems: Architecture
and Components
Terminology
• Data
• Information
• Metadata
Chapter 1 – Database Systems: Architecture and Components
2
Data Management
1.
2.
3.
4.
Creation of data
Retrieval of data
Update or modification of data
Deletion of data
For that, data must be accessed and, for the ease of
access, data must be organized.
Chapter 1 – Database Systems: Architecture and Components
3
Exercise
Assume you want to organize your DVD collection.
The only tool available is an Excel sheet. What would
your columns and rows in Excel look like?
Chapter 1 – Database Systems: Architecture and Components
4
Exercise (continued)
Maybe like this?
Chapter 1 – Database Systems: Architecture and Components
5
Data Management
Only two approaches for accessing data exist:
• Sequential access
• Direct access
Important:
A DBMS facilitates access of data without burdening
the user with details of how the data is physically
organized.
Chapter 1 – Database Systems: Architecture and Components
6
History of Data Management
Object-oriented DBMS
Relational DBMS
Network DBMS
Hierarchical DBMS
File systems
1950
1960
1970
1980
Chapter 1 – Database Systems: Architecture and Components
1990
2000
7
Limitations of File-Processing Systems
•
Lack of Data Integrity
Data integrity (data values are correct, consistent,
complete, and current) is often violated in isolated
environments.
•
Lack of Standards
Organizations find it hard to enforce standards for
naming data items as well as for accessing, updating,
and protecting data.
•
Lack of Flexibility/Maintainability
File-processing systems are not amenable to structural
changes in data and are therefore dependent upon a
programmer who can either write or modify program
code.
Chapter 1 – Database Systems: Architecture and Components
8
Limitations of File-Processing Systems
(continued)
The limitations to file-processing systems are due to:
• Lack of Data Integration
Data are separated and isolated in a file-processing
environment.
•
Lack of Program-Data Independence
The structure of each file is embedded in the application
programs.
Chapter 1 – Database Systems: Architecture and Components
9
Limitations of File-Processing Systems
(continued)
STUDENT
Files
Student
Processing
Applications
Users
FACULTY and
STAFF Files
Faculty and Staff
Processing
Applications
ALUMNI
Files
Alumni
Processing
Applications
Users
Users
Figure 1.1 An example of a file processing environment
Chapter 1 – Database Systems: Architecture and Components
10
So, What Is Desirable?
• Integrated data
– Not data in isolation to be integrated by the application
program/programmer
• Data Independence
– Application program(s) immune to changes in storage
structure and access strategy
– Independent user views of data
Chapter 1 – Database Systems: Architecture and Components
11
History of Data Management
In the 1970s, the Standards Planning and
Requirements Committee (SPARC) of the American
National Standards Institute (ANSI) proposed what
came to be known as the ANSI/SPARC threeschema architecture: conceptual, internal and
external schema.
Chapter 1 – Database Systems: Architecture and Components
12
Three Perspectives of Metadata in a Database
Individual User Views
EXTERNAL
SCHEMA
. . . . . . . .
Global View
CONCEPTUAL SCHEMA
Storage View
EXTERNAL
SCHEMA
INTERNAL SCHEMA
STORED DATABASE
Figure 1.2 The ANSI/SPARC three-schema Architecture
Chapter 1 – Database Systems: Architecture and Components
13
Conceptual Schema
• Core of the architecture
• Represents the global view of the structure of the
entire database for a community of users
• Captures data specification (metadata)
• Describes all data items and relationships between
data together with integrity constraints
• Separates data from the program (or views from the
physical storage structure)
• Technology independent
Chapter 1 – Database Systems: Architecture and Components
14
Internal Schema
• Describes the physical structure of the stored data
(e.g., how the data is actually laid out on storage
devices)
• Describes the mechanism used to implement access
strategies (e.g., indexes, hashed addresses, etc.)
• Technology dependent
• Concerned with the efficiency of data storage and
access mechanisms
Chapter 1 – Database Systems: Architecture and Components
15
External Schema
• Represents different user views, each describing
portions of the database
• Technology independent
• Views are generated exclusively by logical references
Chapter 1 – Database Systems: Architecture and Components
16
Physical and Logical Data Independence
• Physical Data Independence
Definition: External views unaffected by changes to
the internal structure
How?: Introduction of conceptual schema between
the external views and the internal (physical) schema
Chapter 1 – Database Systems: Architecture and Components
17
Physical and Logical Data Independence
(continued)
• Logical Data Independence
Definition: External views unaffected by design
changes (growth or restructuring) in conceptual
schema
How?: External views generated exclusively
through logical reference to elements in the
conceptual schema
Consequence: External views unaffected by
changes to other external views
Chapter 1 – Database Systems: Architecture and Components
18
What is a Database System?
• A self-describing collection of integrated records
Self-describing
The structure of the database (metadata) is recorded
within the database system – not in the application
programs.
Integrated
The responsibility for 'integrating' data items as
needed is assumed by the DBMS instead of the
programmer.
Chapter 1 – Database Systems: Architecture and Components
19
Characteristics of a Database System
Database
A single, integrated set of files
Database Management System (DBMS)
A collection of general-purpose software that facilitates
the process of defining, constructing, and manipulating
a database for various applications
Chapter 1 – Database Systems: Architecture and Components
20
An Early View of a Database System
Users
Application
Program 3
Application
Program 2
Application
Program 4
Application
Program 1
Management
D
as
ab
at
e
A
B
D
C
F
E
Application
Program 5
H
G
Sy
I
st
em
Data Items
(Minimal/Controlled Redundancy)
ar
ftw
e
rS
fo
im
e
pl
ad
c
ho
r
ue
Q
s
ie
o
User-friendly Database Interrogation S
Users
Figure 1.4 An early view of a database system*
*Adapted From Richard L. Nolan, "Computer Data Bases: The Future is Now," Harvard Business Review, (September-October, 1973)
Chapter 1 – Database Systems: Architecture and Components
21
What is a Database?
A database is a self-describing collection of interrelated files.
• Data consists of recorded facts that have implicit meaning.
• Viewed through the lens of metadata, the meaning of
recorded data becomes explicit.
• A database is self-describing in that the metadata is
recorded within the database – not in applications
programs.
Chapter 1 – Database Systems: Architecture and Components
22
What is a Database Management System
(DBMS)?
A DBMS is a collection of general-purpose software that
facilitates the processes of defining, constructing, and
manipulating a database.
Chapter 1 – Database Systems: Architecture and Components
23
Components of a DBMS
The major components of a DBMS include one or more
query languages; tools for generating reports; facilities
for providing security, integrity, backup and recovery; a
data manipulation language for accessing the database;
and a data definition language used to define the
structure of data.
Chapter 1 – Database Systems: Architecture and Components
24
Components of a Database System
Database Management System [DBMS]
Software component
Query Language
[SQL]
Report Generator
Security & Recovery
[DCL/SQL]
Data Definition Language
[DDL/SQL]
Data Repository
{Data Models
Metadata}
Data Manipulation
Language
[DML/SQL]
Access Routines
Data Dictionary
{DBMS Metadata}
Database
{Contains Data}
Computer-aided
Software Engineering
Tools
[CASE Tools]
Figure 1.5 Components of a database system
Chapter 1 – Database Systems: Architecture and Components
25
An Example of a Database System
Student
Processing
Applications
Users
Database
Management
System
Faculty and Staff
Processing
Applications
Users
Database
Alumni
Processing
Applications
Users
Figure 1.6 An example of a database system
Chapter 1 – Database Systems: Architecture and Components
26
Types of Database Systems
• Number of users
 Single-user
Desktop database system
 Multi-user
Workgroup database system
Enterprise database system
• Scope
 Desktop database system
 Workgroup database system
 Enterprise database system
Chapter 1 – Database Systems: Architecture and Components
27
Some Commercial DBMS
• IBM & DB2:
www-306.ibm.com/software/data/db2/
• Oracle:
www.oracle.com/database/index.html
• Microsoft & SQL Server:
www.microsoft.com/sql/default.mspx
Chapter 1 – Database Systems: Architecture and Components
28
Important Terms
• Data Integrity
(correct, consistent, complete and current)
• Data Redundancy
Chapter 1 – Database Systems: Architecture and Components
29
Data Models
• A model is an expression of “observed or
unobservable” reality.
• Example: chair versus department
• A database represents some aspect of the real world
that is called the Universe of Interest.
• The initial step in the design process is the
requirements specification activity (i.e., business
rules).
Chapter 1 – Database Systems: Architecture and Components
30
Steps in Database Design
• Conceptual design ---> Conceptual schema capturing
user-specified business rules
– Tool: e.g., ER modeling, NIAM modeling
– Presentation Layer ER Model
ER diagram and semantic integrity constraints
– Design-Specific ER Model
Coarse and fine level of granularity
Chapter 1 – Database Systems: Architecture and Components
31
Steps in Database Design (continued)
• Logical Design ---> Logical schema
– Tool: normalization
– Architecture: hierarchical, network, or relational
• Physical Design
– Specifying internal storage structure and access
strategies
Chapter 1 – Database Systems: Architecture and Components
32
Steps in Database Design (continued)
Chapter 1 – Database Systems: Architecture and Components
33