DB2
Oracle
MS Access
Chapter
MySQL
ITS232
Introduction To
Database
FILE SYSTEMS & DATABASE
Data Models
The Importance of Data Models
2
Data models
Relatively simple representations, usually
graphical, of complex real-world data structures
Facilitate interaction among the designer, the
applications programmer, and the end user
End-users have different views and needs for
data
Data model organizes data for various users
Data Models
Data Model Basic Building Blocks
3
Entity
anything about which data are to be collected and stored
Attribute
a characteristic of an entity
Relationship
describes an association among entities
Constraint
a restriction placed on the data/user-defined structures
that let you restrict the behaviors of
columns/attributes
Data Models
Data Model Basic Building Blocks
4
Based on previous IBM DB2 lab, determine:
Entity
PERSON, VEHICLE, BUILDING, PLANT, ANIMAL, etc
Attribute
person_name, animal_family, scientific_name, etc
Relationship
1:1, 1:M, M:N
Constraint
NOT NULL, CHECK, PRIMARY KEY, FOREIGN KEY, TRIGGER
Data Models
Business Rules
5
Brief, precise, and unambiguous descriptions of
policies, procedures, or principles within a
specific organization
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
Data Models
Business Rules
6
Must be rendered in writing/available in written form
Must be kept up to date
Sometimes are external to the organization
Must be easy to understand and widely distributed
Describe characteristics of the data as viewed by the
company:
corresponds to a table (ERD)
Entities
Relationship
s
associations between entities
Attributes
characteristics of entities
Connectivity
describe the relationship classification (min
& max)
Constraints
limitations on the type of data accepted
Data Models
Discovering Business Rules
7
Sources of Business Rules:
Company managers
Policy makers
Department managers
Written documentation
Procedures
Standards
Operations manuals
Direct interviews with end users
Data Models
Discovering Business Rules
8
Business rules example:
Data Models
Translating Business Rules into Data Model Components
9
Standardize companys view of data
Act as a communications tool between users
and designers
Allow designer:
to understand the nature, role, and scope of data
to understand business processes
to develop appropriate relationship participation rules
and constraints
Promote creation of an accurate data model
Data Models
Discovering Business Rules
10
Generally
Nouns translate into entities
Verbs translate into relationships among entities
Relationships are bi-directional
Fact finding techniques:
The formal process of using techniques such as
interview and questionnaire to collect facts about
system, requirements and preferences.
To captures the essential facts necessary to build the
required database
What facts are collected?
Captured facts about the current and/or future system.
Data Models
Fact Finding Techniques
11
Examining
documents
(document
review)
Questionnaire
5 commonly
used fact
finding
techniques
Research
Interviewing
Observation the
organization in
operations
Data Models
The Evolution of Data Models
12
Hierarchical Database Model
Represented by a group of records that relates to each
others by a pointer
Network Database Model
Based on set theory, a set consists a collection of records
Relational Database Model
Based on the mathematical concept of relational
Object-Oriented Model
Based on object oriented concepts
Data Models
The Evolution of Data Models
13
Hierachical Database 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 or by a group of records that relates to each others by
a pointer
The uppermost record is a Root
The lower record in a hierarchy is a Child
Depicts a set of one-to-many (1:M) relationships between
a parent and its children segments
Each parent can have many children
each child has only one parent
Data Models
The Evolution of Data Models
14
Hierachical Database Model
Data Models
The Evolution of Data Models
15
Hierachical Database Model
Root
Abu
Johor
3000
A001
Nut
Washer
Samad Kedah
A002
Washer
2500
Zaitun Melaka 4500
A003
Hammer Nut
A004
Bolt Nut
Data Models
The Evolution of Data Models
16
Hierachical Database Model
Root
Segme
nt
Source:
http://worldacademyonline.com/article/25/359/data_models__relational__hierarchical_and_n
etwork_.html
Data Models
The Evolution of Data Models
17
Hierachical Database Model
Advantages
Many of the hierarchical data models features
formed the foundation for current data models
Its database application advantages are replicated,
albeit in a different form, in current database
environments
Generated a large installed (mainframe) base,
created a pool of programmers who developed
numerous tried-and-true business applications
Data Models
The Evolution of Data Models
18
Network Database Model
Develop in 1970 in Conference on Data Systems
Languages (CODASYL), by Database Task Group
(DBTG)
Created to
Represent complex data relationships more
effectively
Improve database performance
Impose a database standard
Resembles hierarchical model
Collection of records in 1:M relationships
Data Models
The Evolution of Data Models
19
Network Database Model
Set
Relationship
Composed of at least two record types
Owner
Equivalent to the hierarchical models parent
Member
Equivalent to the hierarchical models child
A parent can have many child records
A child can have more than one parent record
Data Models
The Evolution of Data Models
20
Network Database Model
Data Models
The Evolution of Data Models
21
Network Database Model
CUSTOMER
Abu
Johor
3000
Samad
Kedah
2500
Zaitun
Melaka
INVOICE
PRODUCT
A001
Nut
A002
Washer
A003
Hammer
A004
Bolt
4500
Data Models
The Evolution of Data Models
22
Network Database Model
Source:
http://worldacademyonline.com/article/25/359/data_models__relational__hierarchical_and_n
etwork_.html
Data Models
The Evolution of Data Models
23
Network Database Model
Disadvantages
Too cumbersome/difficult to handle
The lack of ad hoc query capability put heavy
pressure on programmers
Any structural change in the database could
produce havoc in all application programs that drew
data from the database
Many database old-timers can recall the
interminable information delays
Data Models
The Evolution of Data Models
24
Relational Model
Developed by Codd (IBM) in 1970
considered ingenious but impractical in 1970
Conceptually simple, based on mathematical concept of
relational
Computers lacked power to implement the relational model
Today, microcomputers can run sophisticated relational
database software
Relational Database Management System (RDBMS)
Performs same basic functions provided by hierarchical and
network DBMS systems, in addition to a host of other functions
Most important advantage of the RDBMS is its ability to hide the
complexities of the relational model from the user
Data Models
The Evolution of Data Models
25
Relational Model
Table
(relations)
Relational
diagram
Matrix consisting of a series of row/column
intersections
Related to each other through sharing a common
entity characteristic
Representation of relational databases entities,
attributes within those entities, and relationships
between those entities
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
Data Models
The Evolution of Data Models
26
Relational Model
Example of table structure/relational table
Data Models
The Evolution of Data Models
27
Relational Model
Example of table with data/relational table
Data Models
The Evolution of Data Models
28
Relational Model
Example of table relationship/relational diagram
Data Models
The Evolution of Data Models
29
Relational Model
Example of form
Data Models
The Evolution of Data Models
30
Relational Model
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
Data Models
The Evolution of Data Models
31
Relational Model
Entity Relationship (E-R) Model
Introduced by Chen in 1976
Widely accepted and adapted graphical tool for data
modeling
Graphical representation of entities and their
relationships in dB structure
Entity Relationship Diagram (ERD)
Uses graphic representations to model
database components
Entity is mapped to a relational table
Data Models
The Evolution of Data Models
32
Relational Model
Example of ERD
Chen
Crow
s
Foot
Data Models
The Evolution of Data Models
33
Object Oriented Model
Modeled both data and their relationships in a
single structure known as an object
OO data model (OODM) is the basis for the OO
database management system (OODBMS)
Data Models
The Evolution of Data Models
34
Object Oriented Model
Object described by its factual content
equivalent to entity in Relational Model
Includes information about relationships
between facts within object, and
relationships with other objects but still
unlike relational models entity
Subsequent OODM development allowed an
object to also contain all operations: changing its
data values, finding specific data values, printing
data values
Object becomes basic building block for
autonomous structures
Data Models
The Evolution of Data Models
35
Object Oriented Model
Object is an abstraction of a real-world entity
Attributes describe the properties of an object
E.g. Name, IC Number, Address
Objects that share similar characteristics are grouped
in classes
E.g. PERSON, VEHICLE
Shared structured (attributes) and behavior (methods)
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
Data Models
The Evolution of Data Models
36
Object Oriented Model
A comparison of the OO model and the ER model
Data Models
A Summary
37
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 comply with consistency and
integrity characteristics of any data model
Data Models
A Summary: The development of data model
38
Semantic
data - data
is organized
in such a way
that it can be
interpreted
meaningfully
without
human
intervention
Data Models
Degrees of Data Abstraction
39
Way of classifying data models
Many processes begin at high level of abstraction
and proceed to an ever-increasing level of detail
Designing a usable database follows the same
basic process
The major purpose of a database system is to
provide users with an abstract view of the
system.
The system hides certain details of how data is
stored and created and maintained
Complexity should be hidden from database
users.
Data Models
Degrees of Data Abstraction
40
American National Standards Institute (ANSI) Standards
Planning and Requirements Committee (SPARC)
Defined a framework for data modeling based on
degrees of data abstraction (1970s):
External
Concept
ual
Internal
Data Models
Degrees of Data Abstraction
41
Data abstraction levels
Data Models
Three Level ANSI-SPARC Architecture
42
-users view
External
Model
1.
External
level
User 1
User 2
View 1
View 2
User n
Model
2.Conceptual
Conceptual
level
-designers view
-h/w independent
-s/w independent
Conceptual Schema
3.Internal
Internal
level
Model
-DBMSs view
-h/w independent
-s/w dependent
Internal Schema
Internal Model
Physical
data
Physical
Model
organization
-h/w dependent
-s/w dependent
Database
View n
ERD
Three Level ANSI-SPARC Architecture
External Model
43
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
Advantages:
Easy to identify specific data required to support each
business units operations
Facilitates designers job by providing feedback about the
models adequacy
Creation of external models helps to ensure security
constraints in the database design
Simplifies application program development
Three Level ANSI-SPARC Architecture
External Model
44
Example of External Model for Tiny College
Three Level ANSI-SPARC Architecture
Conceptual Model
45
Global view of the entire database concept of the dB
Describe what data is stored in the dB and relations among
the data
Data as viewed by the entire organization logical
structure
Basis for identification and high-level description of main data
objects, avoiding details
Most widely used conceptual model is the entity relationship (ER)
model
Provides a relatively easily understood macro level view of data
environment
Software and Hardware Independent
Does not depend on the DBMS software used to implement the
model
Does not depend on the hardware used in the implementation
Three Level ANSI-SPARC Architecture
Conceptual Model
46
Example of Conceptual Model for Tiny college
Three Level ANSI-SPARC Architecture
Internal Model
47
Representation of the database as seen by the DBMS
Describes how the data is stored in the dB
Maps the conceptual model to the DBMS
Internal schema depicts a specific representation of an internal
model
Physical representation of the dB on the computer
Software Dependent and Hardware Independent
Depend on the DBMS software used to implement the model
Does not depend on the hardware used in the implementation
of the model
Three Level ANSI-SPARC Architecture
Internal Model
48
An Internal Model for Tiny College
Three Level ANSI-SPARC Architecture
Physical Model
49
The Physical Model
Operates at lowest level of abstraction, describing the way
data are saved on storage media such as disks or tapes
how the data is stored in the database
Software and Hardware Dependent
Requires that database designers have a detailed knowledge of
the hardware and software used to implement database design
Three Level ANSI-SPARC Architecture
Physical Model
50
The Physical Model
Summary of Data Models
The Evolution of Data Models
51
A data model is a (relatively) simple abstraction of a
complex real-world data environment
Basic data modeling components are:
i.
_____________________
ii.
_____________________
iii.
_____________________
iv.
_____________________
Data modeling requirements are a function of different data
views (global vs. local) and level of data abstraction
Summary of Data Models
The Evolution of Data Models
52
Hierarchical Database Model
_________________________________________________
Network Database Model
________________________________________________
Relational Database Model
_____________________________________________
Object-Oriented Model
_____________________________________________
Summary of Data Models
Three Level ANSI-SPARC Architecture
53
-users view
1. External level
User 1
User 2
View 1
View 2
User n
2. Conceptual level
-designers view
-h/w independent
-s/w independent
Conceptual Schema
3.Internal
Internal
level
Model
-DBMSs view
-h/w independent
-s/w dependent
Internal Schema
Physical
data
Physical
Model
organization
-h/w dependent
-s/w dependent
Database
View n
ERD
