Transcript Database Systems: Design, Implementation, and Management

1
Chapter 2
Data Model
Database Systems:
Design, Implementation, and Management, Sixth
Edition, Rob and Coronel
1
In this chapter, you will learn:
• Why data models are important
• About the basic data-modeling building blocks
• What business rules are and how they affect database
design
• How the major data models evolved, and their
advantages and disadvantages
• How data models can be classified by level of
abstraction
2
1
The Importance of Data Models
• Data model
– Relatively simple representation, usually graphical,
of complex real-world data structures
– Communications tool to facilitate interaction among
the designer, the applications programmer, and the
end user
• Good database design uses an appropriate data model
as its foundation
• End-users have different views and needs for data
• Data model organizes data for various users
3
1
Data Model Basic Building Blocks
• Entity –
is anything about which data are to be collected and
stored
• Attribute –
is a characteristic of an entity
• Relationship –
describes an association among (two or more) entities
– One-to-many (1:M) relationship
– Many-to-many (M:N or M:M) relationship
– One-to-one (1:1) relationship
4
1
Business Rules
• How do database designers go about determining
the entities, attributes, and relationships that will
build a data model?
• Business Rules –
Brief, precise, and unambiguous description of a
policy, procedure, or principle within a specific
organization’s environment
5
1
Examples of Business Rules
• A customer may generate many invoices.
• Each invoice is generated by only one customer.
6
1
Sources of Business Rules
• Company managers
• Policy makers
• Department managers
• Written documentation
– Procedures
– Standards
– Operations manuals
• Direct interviews with end users
7
1
Importance of Business Rules
• Standardize company’s view of data
• Constitute a communications tool between users
and designers
• Allow designer to understand the nature, role, and
scope of data
• Allow designer to understand business processes
• Allow designer to develop appropriate
relationship participation rules and constraints
8
1
The Evolution of Data Models
• Hierarchical
• Network
• Relational
• Entity relationship
• Object oriented
9
Hierarchical Database Model
1
• Logically represented by an upside down tree
– Each parent can have many children
– Each child has only one parent
10
Hierarchical Database Model
1
• Advantages
– Conceptual simplicity
– Database security and integrity
– Data independence
– Efficiency
• Disadvantages
– Complex implementation
– Difficult to manage and lack of standards
– Lacks structural independence
– Applications programming and use complexity
– Implementation limitations
11
Network Database Model
1
• Each record can have multiple parents
– Composed of sets
– Each set has owner record and member record
– Member may have several owners
12
Network Database Model
1
• Advantages
– Conceptual simplicity
– Handles more relationship types
– Data access flexibility
– Promotes database integrity
– Data independence
– Conformance to standards
• Disadvantages
– System complexity
– Lack of structural independence
13
1
Relational Database Model
• Developed by Codd (IBM) in 1970
• Perceived by user as a collection of tables for data
storage
• Tables are a series of row/column intersections
• Tables related by sharing common entity characteristic(s)
(Figure 2.4)
• Relational table is purely logical structure
– How data are physically stored in the database is of no
concern to the user or the designer
14
1
Linking Relational Tables
15
Relational Schema
1
• Visual representation of relational database’s entities,
attributes within those entities, and relationships
between those entities.
16
1
Relational Database Model
• Advantages
– Structural independence
– Improved conceptual simplicity
– Easier database design, implementation,
management, and use
– Ad hoc query capability with SQL
– Powerful database management system
17
1
Relational Database Model
• Disadvantages
– Substantial hardware and system software overhead
– Poor design and implementation is made easy
18
1
Entity Relationship Model
• Widely accepted and adapted graphical tool
for data modeling
• Introduced by Chen in 1976
• Graphical representation of entities and their
relationships in a database structure
19
1
The ER Model — Basic Structure
• Entity relationship diagram (ERD)
– Uses graphic representations to model database components
– Entity is mapped to a relational table
• Entity instance (or occurrence)
is row in the relational table
• Connectivity labels types of relationships
– Diamond connected to related entities through a relationship
line
• Entity set is collection of like entities
• Unfortunately, ERD designers use “entity” as a substitute for “entity set”
and we ‘ll conform to that established practice.
20
Relationships: The Basic Chen ERD
1
21
Relationships: The Basic Crow’s Foot ERD
1
22
1
Entity Relationship Database Model
• Complements the relational data model concepts
• Represented in an entity relationship diagram (ERD)
• Based on entities, attributes, and relationships
23
1
Entity Relationship Database Model
• Advantages
–
–
–
–
Exceptional conceptual simplicity
Visual representation
Effective communication tool
Integrated with the relational database model
• Disadvantages
– Limited constraint representation
– Limited relationship representation
(relationships between attributes within entities cannot be
represented)
– No data manipulation language (DML)
– Loss of information content
24
1
The Object Oriented Model
• Semantic data model (SDM) developed by
Hammer and McLeod in 1981
• Modeled both data and their relationships in
a single structure known as an object
• Basis of object oriented data model (OODM)
• OODM becomes the basis for the object
oriented database management system
(OODBMS)
25
1
Object-Oriented Database Model
• An object is an abstraction of a real-world entity
– Attributes describe properties of an object
– Collection of similar objects is a class
• Methods represent real world actions of classes
• Classes are organized in a class hierarchy
– Inheritance is ability of object
to inherit attributes and methods of classes
above it
26
1
OO Data Model
• Advantages
–
–
–
–
Adds semantic content
Visual presentation includes semantic content
Database integrity
Both structural and data independence
• Disadvantages
–
–
–
–
Lack of OODM standards
Complex navigational data access
Steep learning curve
High system overhead slows transactions
27
The Development of Data Models
1
• The evolution has always been driven by the search for
new ways of modeling increasingly complex real-world data.
28
1
Database Models and the Internet
• Characteristics of “Internet age” databases
– Flexible, efficient, and secure Internet access
– Easily used, developed, and supported
– Supports complex data types and relationships
– Seamless interfaces with multiple data sources and
structures
– Relative conceptual simplicity to make database design
and implementation less cumbersome
– Many database design, implementation, and application
development tools
– Powerful DBMS GUI make DBA job easier
29
Degrees of Data Abstraction
1
• Way of classifying data models
• American National Standards Institute/Standards
Planning and Requirements Committee
(ANSI/SPARC)
– Classified data models according to their degree of
abstraction (1970s):
• Conceptual
• External
• Internal
30
Data Abstraction Levels
1
ER
31
1
The Conceptual Model
 The
conceptual model represents a global view of the
data.
 It
is an enterprise-wide representation of data as viewed
by
high-level managers.
 Entity-Relationship
(E-R) model is the most widely used
conceptual model.
 The
conceptual model is_ software independence
hardware independence
32
A Conceptual Model for Tiny College
1
33
1
Advantages of Conceptual Model
• Provides a relatively easily understood macro level view
of data environment
• Independent of both software and hardware
– Does not depend on the DBMS software used to implement
the model
– Does not depend on the hardware used in the
implementation of the model
– Changes in either the hardware or the DBMS software have
no effect on the database design at the conceptual level
34
1
The Internal Model

Representation of the database as “seen” by the
DBMS

Once a specific DBMS has been selected,
the internal model adapts
the conceptual model to the DBMS.

The internal model is software-dependent
hardware-independence.
35
The External Model

End users’ views of data environment

Each external model is then represented by its own
external schema.

Provides subsets of internal view

Makes application program development easier

The external model is DBMS-dependent
hardware-independence.
1
• CREATE VIEW CLASS_VIEW AS
SELECT (CLASS_ID, CLASS_NAME, PROF_NAME, CLASS_TIME, ROOM_ID)
FROM CLASS, PROFESSOR, ROOM
WHERE CLASS.PROF_ID = PROFESSOR.PROF_ID AND
CLASS.ROOM_ID = ROOM.ROOM_ID;
36
A Division of an Internal Model into External Models
1
Internal
External
37
The External Models for Tiny College
1
38
1
The Physical Model

The physical model operates at the lowest level of
abstraction, describing the way data is saved on storage
media such as disks or tapes.

Requires that database designers have a detailed knowledge
of the hardware and software used to implement database
design

The physical model is_ software-dependent
hardware-dependent.
39
1
Levels of Data Abstraction
40
1
Database Models
• Collection of logical constructs used to represent data
structure and data relationships within the database
– Conceptual models: logical nature of data representation
• focus on the logical nature of the data representation. They
are concerned with what is represented
rather than how it is represented.
• E.g. E-R model
– Implementation models: emphasis on how the data are
represented in the database
• place the emphasis on
how the data are represented in the database or on
how the data structures are implemented.
• E.g. relational database model
41
1
Summary
• A good DBMS will perform poorly with a poorly
designed database
• A data model is a (relatively) simple abstraction
of a complex real-world data-gathering
environment
• Basic data modeling components are:
– Entities
– Attributes
– Relationships
42
1
Summary (continued)
• Hierarchical model
– Based on a tree structure composed of a root
segment, parent segments, and child segments
– Depicts a set of one-to-many (l:M) relationships
between a parent and its children
– Does not include ad hoc querying capability
43
1
Summary (continued)
• Network model attempts to deal with many of the
hierarchical model’s limitations
• Relational model:
– Current database implementation standard
– Much simpler than hierarchical or network design
• Object is basic modeling structure of object
oriented model
• Data modeling requirements are a function of
different data views (global vs. local) and level of
data abstraction
44