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
Database Systems 6e / Rob & Coronel 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
Database Systems 6e / Rob & Coronel 2- 3
Importance of Data Modeling
End-users have different views and needs for
data
Data model organizes data for various users
Database Systems 6e / Rob & Coronel
2- 4
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
2- 5
Database Systems 6e / Rob & Coronel
Business Rules
Brief, precise, and unambiguous description of a policy,
procedure, or principle within a specific organizations environment
A company airplane must be inspect after 100 hours of flight time
A customer may generate many invoices
Each invoice is generated by only one customer
Apply to any organization that stores and uses data to
generate information
Description of operations that help to create and enforce
actions within that organizations environment
Database Systems 6e / Rob & Coronel
2- 6
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
Database Systems 6e / Rob & Coronel 2- 7
Sources of Business Rules
Company managers
Policy makers
Department managers Written documentation
Procedures Standards
Operations manuals
Direct interviews with end users
Database Systems 6e / Rob & Coronel 2- 8
Importance of Business Rules
Promote creation of an accurate data model Standardize companys 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
Database Systems 6e / Rob & Coronel 2- 9
The Evolution of Data Models
Hierarchical
Network
Relational Entity relationship Object oriented
Database Systems 6e / Rob & Coronel
2- 10
Evolution of Major Data Models
Database Systems 6e / Rob & Coronel
2- 11
The Hierarchical ModelEvolution
North American Rockwell was the prime
contractor for the Apollo project which culminated in the moon landing.
This project required the management of
millions of parts a complex computer file system was used.
An audit of the tapes revealed that 60% of the data was redundant This led Rockwell to develop an alternate strategy to manage their data
2- 12
Database Systems 6e / Rob & Coronel
The Hierarchical ModelEvolution
GUAM (Generalized Update Access Method)
Based on the recognition that the many smaller parts would come together as components of still larger components
In the mid 1960s. IBM joined Rockwell to expand the capabilities of GUAM, replacing computer tapes with disk storage
Information Management System (IMS)
The result of the joint effort between Rockwell and IBM
Worlds leading mainframe hierarchical database system in the 1970s and early 1980s
2- 13
Database Systems 6e / Rob & Coronel
The Hierarchical Model Characteristics
Basic concepts form the basis for subsequent
database development
Limitations lead to a different way of looking
at database design
Basic concepts show up in current data
models
Best understood by examining manufacturing
process
Database Systems 6e / Rob & Coronel 2- 14
A Hierarchical Structure
Database Systems 6e / Rob & Coronel
2- 15
Hierarchical StructureCharacteristics
Each parent can have many children
Each child has only one parent
Tree is defined by path that traces parent segments to child
segments, beginning from the left Hierarchical path
Ordered sequencing of segments tracing hierarchical structure
Preorder traversal or hierarchic sequence Left-list path If Part D is most frequently accessed and updated, change the database structure to place Part D closer to the left side of the tree This will give a shorter traversal
Database Systems 6e / Rob & Coronel
2- 16
The Hierarchical Model
Advantages over a file system
Conceptual simplicity easy to understand the model layout
Database security
Data independence (a change in a data type will be automatically cascaded throughout the database by the DBMS, thereby eliminating the need to make changes in the program segments that reference the changes data type) Database integrity always a link between parent and child
Efficiency very efficient when it contains a large volume of data in 1:M relationships and whose relationships are fixed over time
2- 17
Database Systems 6e / Rob & Coronel
The Hierarchical Model (continued)
Disadvantages over a file system
Complex implementation detailed knowledge of the physical data storage characteristics is required by the designers and programmers
Difficult to manage relocation of segments requires application changes Lacks structural independence Complex applications programming and use programmers and end users must know precisely how the data are physically distributed within the database Implementation limitations difficult to support M:N relationships Lack of standards no standard DDL and no DML
2- 18
Database Systems 6e / Rob & Coronel
Child with Multiple Parents
Database Systems 6e / Rob & Coronel
2- 19
The Network Model
Created to
Represent complex data relationships more effectively
Improve database performance
Impose a database standard
Conference on Data Systems Languages
(CODASYL)
American National Standards Institute (ANSI)
Database Task Group (DBTG)
Database Systems 6e / Rob & Coronel 2- 20
Crucial Database Components
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 Data Management Language (DML) Define data characteristics and data structure in order to manipulate the data
Database Systems 6e / Rob & Coronel 2- 21
Data Management Language Components
Schema Data Definition Language (DDL)
Enables database administrator to define schema components Allows application programs to define database components that will be used Manipulates database contents
Subschema DDL
DML
Database Systems 6e / Rob & Coronel
2- 22
Network ModelBasic Structure
Resembles hierarchical model Collection of records in 1:M relationships
A relationship is called a Set Composed of at least two record types
Owner
Equivalent to the hierarchical models parent
Member
Equivalent to the hierarchical models child A record can appear as a member in more than one set i.e., a member may have multiple owners
Database Systems 6e / Rob & Coronel
2- 23
A Network Data Model
Database Systems 6e / Rob & Coronel
2- 24
The Network Data Model
Advantages
Conceptual simplicity
Handles more relationship types Data access flexibility no need for a preorder traversal Promotes database integrity must first define the owner and then the member record
Data independence
Conformance to standards
2- 25
Database Systems 6e / Rob & Coronel
The Network Data Model (continued)
Disadvantages
System complexity
Lack of structural independence
Database Systems 6e / Rob & Coronel
2- 26
The Relational Model
Developed by Codd (IBM) in 1970 Considered ingenious but impractical in 1970 Conceptually simple Computers lacked power to implement the
relational model
Today, microcomputers can run sophisticated
relational database software
Database Systems 6e / Rob & Coronel 2- 27
The Relational Model: Basic Structure
Relational Database Management System
(RDBMS)
Performs same basic functions provided by
hierarchical and network DBMS systems, plus other functions
RDBMS handles all the complex physical detais
Most important advantage of the RDBMS is its
ability to let the user/designer operate in a human logical environment
Database Systems 6e / Rob & Coronel 2- 28
The Relational Model:Basic Structure
Table (relations)
Matrix consisting of a series of row/column intersections Related to each other by sharing a common entity characteristic
Visual representation of relational databases entities, attributes within those entities, and relationships between those entities
Relational schema
Database Systems 6e / Rob & Coronel
2- 29
Linking Relational Tables
Database Systems 6e / Rob & Coronel
2- 30
Relational Table
Stores a collection of related entities
Resembles a file
Relational table is purely logical structure
How data are physically stored in the database is of no concern to the user or the designer This property became the source of a real database revolution
2- 31
Database Systems 6e / Rob & Coronel
A Relational Schema
Database Systems 6e / Rob & Coronel
2- 32
The Relational Model
Advantages
Structural independence changes in the relational data structure do not affect the DBMSs data access in any way Improved conceptual simplicity by concentrating on the logical view
Easier database design, implementation, management, and use Ad hoc query capability - SQL Powerful database management system
2- 33
Database Systems 6e / Rob & Coronel
The Relational Model (continued)
Disadvantages
Substantial hardware and system software overhead
Can facilitate poor design and implementation
May promote islands of information problems
Database Systems 6e / Rob & Coronel
2- 34
The Entity Relationship Model
Widely accepted and adapted graphical tool
for data modeling
Introduced by Peter Chen in 1976 Graphical representation of entities and their
relationships in a database structure
Database Systems 6e / Rob & Coronel
2- 35
The Entity Relationship 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 table Entity set is collection of like entities Connectivity labels types of relationships
Diamond connected to related entities through a relationship line
2- 36
Database Systems 6e / Rob & Coronel
The Entity Relationship Model CrowFoot Components
Database Systems 6e / Rob & Coronel
2- 37
Relationships: The Basic Chen ERD
Database Systems 6e / Rob & Coronel
2- 38
Relationships: The Basic Crows Foot ERD
Database Systems 6e / Rob & Coronel
2- 39
The Entity Relationship Model
Advantages
Exceptional conceptual simplicity Visual representation Effective communication tool Integrated with the relational data model
Database Systems 6e / Rob & Coronel
2- 40
The Entity Relationship Model
Disadvantages
Limited constraint representation
Limited relationship representation
No data manipulation language
Loss of information content
Database Systems 6e / Rob & Coronel
2- 41
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)
Database Systems 6e / Rob & Coronel 2- 42
The Object Oriented Model
Object is described by its factual content
Like relational models entity
Includes information about relationships
between facts within object and relationships with other objects
Unlike relational models entity
Subsequent OODM development allowed an
object to also contain operations Object becomes basic building block for autonomous structures
Database Systems 6e / Rob & Coronel 2- 43
Developments that Boosted OODMs Popularity
Growing costs put a premium on code
reusability Complex data types and system requirements became difficult to manage with a traditional RDBMS Became possible to support increasingly sophisticated transaction & information requirements Ever-increasing computing power made it possible to support the large computing overhead required
Database Systems 6e / Rob & Coronel 2- 44
Object Oriented Data Model Basic Structure
Object: abstraction of a real-world entity
Attributes describe the properties of an object Objects that share similar characteristics are
grouped in classes
Classes are organized in a class hierarchy Inheritance is the ability of an object within
the class hierarchy to inherit the attributes and methods of classes above it
Database Systems 6e / Rob & Coronel 2- 45
A Comparison of the OO Model and the ER Model
Database Systems 6e / Rob & Coronel
2- 46
The Object Oriented Model
Advantages
Adds semantic content Visual presentation includes semantic content Database integrity Both structural and data independence
Database Systems 6e / Rob & Coronel
2- 47
The Object Oriented Model (continued)
Disadvantages
Slow pace of OODM standards development Complex navigational data access Steep learning curve High system overhead slows transactions Lack of market penetration
Database Systems 6e / Rob & Coronel
2- 48
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
Database Systems 6e / Rob & Coronel
2- 49
Other Models (continued)
Dates objections to ERDM label
Given proper support for domains, relational data models are quite capable of handling complex data
Therefore, capability that is supposedly being extended is already there
O/RDM label is not accurate because the relational data models domain is not an object model structure
Database Systems 6e / Rob & Coronel
2- 50
Data Models: A Summary
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 Representation of real-world transformations (behavior) must be in compliance with consistency and integrity characteristics of any data model
2- 51
Database Systems 6e / Rob & Coronel
The Development of Data Models
Database Systems 6e / Rob & Coronel
2- 52
Database Models and the Internet
Characteristics of successful Internet age
databases
Flexible, efficient, and secure Internet access that is easily used, developed, and supported Support for complex data types and relationships
Seamless interfacing with multiple data sources and structures
2- 53
Database Systems 6e / Rob & Coronel
Database Models and the Internet
Relative conceptual simplicity to make database design and implementation less cumbersome An abundance of available database design, implementation, and application development tools A powerful DBMS graphical user interface (GUI) to help make the DBAs job easier
Database Systems 6e / Rob & Coronel
2- 54
Degrees of Data Abstraction
Way of classifying data models
Many processes begin at high level of
abstraction and proceed to an everincreasing level of detail
Designing a usable database follows the
same basic process
Database Systems 6e / Rob & Coronel
2- 55
Degrees of Data Abstraction (continued)
American National Standards
Institute/Standards Planning and Requirements Committee (ANSI/SPARC)
Classified data models according to their degree of abstraction (1970s):
Conceptual External Internal
Database Systems 6e / Rob & Coronel
2- 56
Data Abstraction Levels
Database Systems 6e / Rob & Coronel
2- 57
The Conceptual Model
Represents global view of the database Enterprise-wide representation of data as
viewed by high-level managers
Basis for identification and description of
main data objects, avoiding details
Most widely used conceptual model is the
entity relationship (ER) model
Database Systems 6e / Rob & Coronel 2- 58
A Conceptual Model for Tiny College
Database Systems 6e / Rob & Coronel
2- 59
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
2- 60
Database Systems 6e / Rob & Coronel
The Internal Model
Representation of the database as seen by
the DBMS
Adapts the conceptual model to the DBMS
Software dependent
Hardware independent
Database Systems 6e / Rob & Coronel
2- 61
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
Good design should:
Consider such relationships between views
Provide programmers with a set of restrictions that govern common entities
2- 62
Database Systems 6e / Rob & Coronel
A Division of an Internal Model into External Models
Database Systems 6e / Rob & Coronel
2- 63
Advantages of External Models
Use of database subsets makes application
program development much simpler
Facilitates designers task by making it easier to identify specific data required to support each business units operations Provides feedback about the conceptual models adequacy
Creation of external models helps to ensure
security constraints in the database design
Database Systems 6e / Rob & Coronel 2- 64
The External Model
DBMS dependent
Hardware independent
Database Systems 6e / Rob & Coronel
2- 65
The External Models for Tiny College
Database Systems 6e / Rob & Coronel
2- 66
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
Database Systems 6e / Rob & Coronel
2- 67
Levels of Data Abstraction
Database Systems 6e / Rob & Coronel
2- 68
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 datagathering environment Basic data modeling components are:
Entities Attributes Relationships
2- 69
Database Systems 6e / Rob & Coronel
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
Database Systems 6e / Rob & Coronel
2- 70
Summary (continued)
Network model attempts to deal with many of the
hierarchical models 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
Database Systems 6e / Rob & Coronel 2- 71
