Transcript Chapter 2 Data Model 2 Database Systems:

2
Chapter 2
Data Model
Database Systems:
Design, Implementation, and Management, Sixth
Edition, Rob and Coronel
2
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
2
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
2
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
• Constraint - a restriction placed on the data
4
2
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
2
Business Rules (continued)
• Must be rendered in writing
• Must be kept up to date
• Sometimes are external to the organization
• Must be easy to understand and widely disseminated
• Describe characteristics of the data as viewed by the
company
6
2
Examples of Business Rules
• A customer may generate many invoices.
• Each invoice is generated by only one customer.
7
2
Sources of Business Rules
• Company managers
• Policy makers
• Department managers
• Written documentation
– Procedures
– Standards
– Operations manuals
• Direct interviews with end users
8
2
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
9
2
Translating Business Rules into Data
Model Components
• Generally, nouns translate into entities
• Verbs translate into relationships among entities
• Relationships are bi-directional
• A customer may generate many invoices.
• Each invoice is generated by only one customer.
10
The Evolution of Data Models
2
11
2
The Evolution of Data Models
• Hierarchical
• Network
• Relational
• Entity relationship
• Object oriented
12
2
The Hierarchical Model
• Developed in the 1960s
to manage large amounts of data for
complex manufacturing projects
• Basic logical structure is represented by
an upside-down “tree”
13
Hierarchical Database Model
2
• Logically represented by an upside down tree
– Each parent can have many children
– Each child has only one parent
14
Hierarchical Database Model
2
• Advantages
– Conceptual simplicity (The relationship between the
various layers of the model is logically simple)
– 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
15
2
The Network Model
• Created to
– Represent complex data relationships
more effectively
– Improve database performance
– Impose a database standard
16
2
The Network Model (continued)
• Schema
– Conceptual organization of entire
database as viewed by the database
administrator
• Subschema
– Defines database portion “seen” by the
application programs that actually
produce the desired information from
data contained within the database
17
2
The Network Model (continued)
• Schema Data Definition Language (DDL)
– Enables database administrator to define schema
components
• Subschema DDL
– Allows application programs to define database
components that will be used
• Data Management Language (DML)
– Defines the environment in which data can be
managed
– Works with the data in the database
18
2
The Network Model (continued)
• Resembles hierarchical model
• Collection of records in 1:M relationships
• Set
– Relationship
– Composed of at least two record types
• Owner
– Equivalent to the hierarchical model’s parent
• Member
– Equivalent to the hierarchical model’s child
19
Network Database Model
•
2
Each record can have multiple parents
– Composed of sets
– Each set has owner record and member record
– Member may have several owners
 Collection of records in 1:M relationships
20
Network Database Model
2
• 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
21
2
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
22
Linking Relational Tables
2
AGENT_CODE
23
The Relational Model (continued)
2
• Relational diagram
Representation of relational database’s entities,
attributes within those entities, and relationships
between those entities
AGENT_CODE
24
2
The Relational Model (continued)
• Rise to dominance due in part to its powerful
and flexible query language
• Structured Query Language (SQL)
allows the user to specify what must be done
without specifying how it must be done
• SQL-based relational database application
involves:
– User interface
– A set of tables stored in the database
– SQL engine
25
2
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
26
2
Relational Database Model
• Disadvantages
– Substantial hardware and system software
overhead
– Poor design and implementation is made easy
27
2
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
28
2
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.
29
Relationships: The Basic Chen2ERD
30
The Entity Relationship Model
2
Relationships: The Basic Crow’s Foot ERD
31
Relationships: The Basic Chen ERD
2
32
Relationships: The Basic Crow’s Foot ERD
2
33
Entity Relationship Database Model
2
• Complements the relational data model concepts
• Represented in an entity relationship diagram
(ERD)
• Based on entities, attributes, and relationships
34
2
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
• Object-oriented data model OODM is the
basis for the object oriented database
management system (OODBMS)
• OODM is said to be a semantic data model
35
2
Object-Oriented Database Model
• An object is an abstraction of a real-world
entity
– Attributes describe properties of an object
– Objects that share similar characteristics are
grouped in class
– Classes are organized in a class hierarchy
– Inheritance is ability of object
to inherit attributes and methods of classes
above it
36
2
The Object Oriented Model (continued)
37
2
Other Models
• Extended Relational Data Model (ERDM)
– Semantic data model developed in response to
increasing complexity of applications
– DBMS based on the ERDM often described as an
object/relational database management system
(O/RDBMS)
– Primarily geared to business applications
38
Database Models and the Internet
2
• Internet drastically changed role and scope of
database market
• OODM and ERDM-O/RDM
have taken a backseat to development of
databases that interface with Internet
• Dominance of Web has resulted in growing
need to manage unstructured information
39
Data Models: A Summary
2
• Each new data model capitalized on the
shortcomings of previous models
• Common characteristics:
– Conceptual simplicity
without compromising the semantic
completeness of the database
– Represent the real world as closely as possible
40
Data Models: A Summary
2
41
Degrees of Data Abstraction
2
• 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
42
Degrees of Data Abstraction
2
43
2
The External Model
• End users’ view of the data environment
• Requires that the modeler subdivide set of
requirements and constraints into
functional modules that can be examined
within the framework of their external
models
44
2
The External Model (continued)
45
2
The Conceptual Model
• Represents global view of the entire
database
• Representation of data as viewed by the
entire organization
• Basis for identification and high-level
description of main data objects, avoiding
details
• Most widely used conceptual model is the
entity relationship (ER) model
46
The Conceptual Model
2
47
2
The Conceptual Model (continued)
• 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 hardware or DBMS software
have no effect on the database design at the
conceptual level
48
2
The Internal Model
• Representation of the database as
“seen” by the DBMS
• Maps the conceptual model to the DBMS
• Internal schema depicts a specific representation
of an internal model
• Hardware Independent
• Software Dependent (DBMS Dependent)
49
The Internal Model
2
50
2
The Physical Model
• Operates at lowest level of abstraction,
describing the way data are saved on storage
media such as disks or tapes
• Software and hardware dependent
• Requires that database designers have a detailed
knowledge of the hardware and software used to
implement database design
51
2
Levels of Data Abstraction
52
2
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
53
2
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
54
2
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
55
2
Entity Relationship Database Model
• Complements the relational data model concepts
• Represented in an entity relationship diagram (ERD)
• Based on entities, attributes, and relationships
56
2
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
57
2
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
58
2
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
59
2
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
60
A Conceptual Model for Tiny College
2
61
2
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
62
2
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.
63
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.
2
• 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;
64
A Division of an Internal Model into External Models
2
Internal
External
65
The External Models for Tiny College
2
66
2
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.
67
2
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
68