IST 331/ 504 Database
Design, implementation and
Management
CHAPTER 2: DATA MODELS FALL 2024
Data Modeling and Data Models
Data modeling: creating a specific
data model for a determined problem
domain
Data model: simple representation
of complex real-world data structures
Usefulfor supporting a specific
problem domain
Model: abstraction of a more
complex real-world object or event
The Importance of Data Models
Facilitates communication
Gives various views of the database
Organizes data for various users
applications programmer’s view of the data is different from that of the manager
or the end user
Provides an abstraction for the creation of good a database
Applications are created to manage data and to help transform data into
information
data is viewed in different ways by different people
Unlikely to create a good database without first creating an appropriate
data model
Data Model Basic Building Blocks
Entity:person, place, thing, or event about which
data will be collected and stored
Attribute: characteristic of an entity
Relationship: association among entities
One-to-many (1:M OR 1..*)
Many-to-many (M:N or *..*)
One-to-one (1:1 OR 1..1)
Constraint: restriction placed on data
Ensures data integrity
Checkpoint #1
What is a relationship,
and what three types
of relationships exist?
Business Rules
Business rule: a brief, precise, and unambiguous description of a
policy, procedure, or principle within a specific organization
They apply to any organization that stores and uses data to generate
information
Used to define entities, attributes, relationships, and constraints
They must be easy to understand and widely disseminated
Examples of business rules include the following:
A customer may generate many invoices
An invoice is generated by only one customer
Discovering Business Rules
Sources of business rules
Company managers
Policy makers
Department managers
Written documentation
Company procedures
Direct interviews with end users
Business Rules and DB Design
Standardize company’s view of data
Facilitate communications tool between users and
designers
Assist designers
Understand the nature, role, scope of data
Understand the business processes
Developappropriate relationship participation rules
and constraints
Create an accurate data model
Translating Business Rules into Data Model
Components
Business rules set the stage for the proper identification of
entities, attributes, relationships, and constraints
Nouns translate into entities
Verbs translate into relationships among entities
Relationships are bidirectional
Questions to identify the relationship type
How many instances of B are related to one instance of A?
How many instances of A are related to one instance of B?
Naming Conventions
Entity name requirements
Be descriptive of the objects in the business environment
Use terminology that is familiar to the users
Attribute name
Required to be descriptive of the data represented by the attribute
Proper naming
Facilitates communication between parties
Promotes self-documentation
It is good practice to prefix the name of an attribute with the name or
abbreviation of the entity in which it occurs
CUSTOMER entity, customer’s credit limit may be called CUS_CREDIT_LIMIT
The Evolution of Data Models
Data models represent what a database is, what it should do, the types of
structures that it should employ, and the technology that would be used to
implement these structures
The major data models in chronological order
Hierarchical Model
Network Model
Relational Model
Entity Relationship Model
Object-Oriented Data Model
Extended Relational Data Model
NoSQL Data Model
Hierarchical Model
Hierarchical model: developed in the 1960s to
manage large amounts of data for complex
manufacturing projects
The hierarchical structure contains levels, or
segments
Segment: the equivalent of a file system’s record
type
A higher layer is perceived as the parent of the
segment directly beneath it, which is called the
child
Depicts a set of one-to-many (1:M) relationships
Hierarchical Model Pros and Cons
Advantages Disadvantages
Requires knowledge of physical
Promotes data sharing
data storage characteristics
Parent/child relationship Navigational system requires
promotes conceptual knowledge of hierarchical path
simplicity and data integrity Changes in structure require
Database security is provided changes in all application programs
and enforced by DBMS Implementation limitations
Efficient with 1:M No data definition
relationships Lack of standards
Network Model
Network models: created to represent complex
data relationships effectively
Improved database performance and imposed a
database standard
Allows a record to have more than one parent
Standard database concepts that emerged with the
network model are still used by modern data
models
Schema and subschema
Data manipulation language (DML)
Network Model (cont.)
Schema: the conceptual organization of the entire database as
viewed by the database administrator
Subschema: the portion of the database “seen” by the
application programs that produce the desired information from
the data within the database
Data manipulation language (DML): defines the
environment in which data can be managed and is used to work
with the data in the database
Data definition language (DDL): enables the database
administrator to define the schema components
Network Model Pros and Cons
Advantages Disadvantages
Conceptual simplicity System complexity limits efficiency
Handles more relationship types Navigational system yields
complex implementation,
Data access is flexible application development, and
Data owner/member relationship management
promotes data integrity Structural changes require
Conformance to standards changes in all application
programs
Includes data definition language
(DDL) and data manipulation
language (DML)
The Relational Model
Relational model based on a mathematical concept
known as a relation
Relation: a two-dimensional structure composed of
intersecting rows and columns (table)
Tuple: each row in a relation
Attribute: each column in a relation
Produced an automatic transmission database that replaced
standard transmission databases
Describes a precise set of data manipulation constructs
RDBMS
Relational database management system
(RDBMS) implements the relational data
Performs basic functions provided by the hierarchical
and network DBMS systems
Makes the relational data model easier to understand
and implement
Hides the complexities of the relational model from
the user
The users sees a collection of tables in which the data
is stored
The Relational Model Tables
Figure 2.1
Linking
Relational Tables
The Relational
Model Diagram
Figure 2.2 A Relational
Diagram
The Relational Model – SQL-based
Any SQL-based relational database application
involves the following three parts:
End-user interface
Allows end user to interact with the data
Collection of tables stored in the database
Each table is independent from another
Rows in different tables are related based on
common values in common attributes
SQL engine
Executes all queries
Relational Model Pros and Cons
Advantages Disadvantages
Structural independence is Requires substantial hardware
promoted using independent tables
and system software overhead
Tabular view improves conceptual
Conceptual simplicity gives
simplicity
untrained people the tools to
Ad hoc query capability is based on
SQL
use a good system poorly
May promote information
Isolates the end user from physical-
level details problems
Improves implementation and
management simplicity
The Entity Relationship Model
Complex design activities require conceptual simplicity to yield
successful results
Database designers prefer to use a graphical tool in which
entities and their relationships are pictured
The entity relationship (ER) model (ERM) was developed
to do just that
The relational data model and ERM combined to provide the
foundation for tightly structured database design
Entity Relationship Model (ER)
ER model: Graphical representation of entities Three ER notations:
and their relationships in a database structure Chen notation
Entity relationship diagram (ERD): uses graphic Crow’s Foot notation
representations to model database components
Class diagram notation (part
Entity instance or entity occurrence: rows in the of the Unified Modeling
relational table Language (UML))
Entity box: a rectangle used to represent an entity in
the ERD
Attributes: describe particular characteristics
Relationships: relationships describe associations
among data
ER
Diagra
m
The ER Model Notations
Figure 2.3
The ER
Model
Notations
Entity Relationship Model Pros and
Cons
Advantages Disadvantages
Visual modeling yields conceptual Limited constraint representation
simplicity Limited relationship representation
Visual representation makes it an No data manipulation language
effective communication tool
Loss of information content occurs
Is integrated with the dominant
when attributes are removed from
relational model
entities to avoid crowded displays
The Object- Oriented Data Model
Object-oriented data model The OODM is based on the following
(OODM): Both data and its components:
relationships are contained in a single Object: contains data and their
structure known as an object relationships with operations that are
Object-oriented database performed on it
management system(OODBMS): Basic building block for
based on OODM autonomous structures
Semantic data model because Abstraction of real-world entity
semantic indicates meaning
Attribute: describes the properties of
an object
Object
Oriented
Model
The Object-Oriented Data Model
The OODM is based on the following components:
Class: a collection of similar objects with shared structure and behavior
Method: represents a real-world action such as finding a selected
PERSON’s name, changing a PERSON’s name, or printing a PERSON’s
address
Class hierarchy: resembles an upside-down tree where each class has
only one parent
Inheritance: the ability of an object within the class hierarchy to inherit
the attributes and methods of the classes above it
Unified Modeling Language (UML): describes sets of diagrams and
symbols to graphically model a system
The Object-Oriented Model (OODM)
Object/Relational and XML
Extended relational data model (ERDM): adds many of the OO model’s
features within the simpler relational database structure
Supports OO features, extensible data types based on classes, and inheritance
Object/relational database management system (O/R DBMS):
based on ERDM
Success of the O/R DBMSs due to the model’s conceptual simplicity, data integrity,
easy-to-use query language, high transaction performance, high availability, security,
scalability, and expandability
Extensible Markup Language (XML)
Manages unstructured data for efficient and effective exchange of structured,
semistructured, and unstructured data
Object-Oriented Model Pros and Cons
Advantages
Semantic content is added
Visual representation includes semantic content
Inheritance promotes data integrity
Disadvantages
Slow development of standards caused vendors to supply their own
enhancements
Complex navigational system
Learning curve is steep
High system overhead slows transactions
Emerging Data Models: Big Data and
NoSQL
Internet of Things (IoT): a web of Internet-connected devices exchanging
and collecting data
The IoT has accelerated the rate of data growth so that about 2.5 quintillion bytes of
data are created daily
Big Data
Find new and better ways to manage large amounts of web and sensor-generated
data and derive business insight from it
Provide high performance at a reasonable cost
Characteristics of Big Data – 3Vs
Volume
Velocity
Variety
Big Data Technologies and Challenges
Frequently used Big Data technologies:
Hadoop: a Java-based, open-source, high-speed, fault-tolerant distributed storage
and computational framework
Hadoop Distributed File System (HDFS): a highly distributed, fault-tolerant file
storage system designed to manage large amounts of data at high speeds
MapReduce: an open-source application programming interface (API) that provides
fast data analytics services
NoSQL: a large-scale distributed database system that stores structured and
unstructured data in efficient ways
Challenges of Big Data
Volume doesn’t allow usage of conventional structures
Expensive
OLAP tools proved inconsistent dealing with unstructured data
Big Data and NoSQL Characteristics
NoSQL databases have the following general
characteristics:
They are not based on the relational model and SQL
They support highly distributed database architectures
They provide high scalability, high availability, and fault
tolerance
They support very large amounts of sparse data
They are geared toward performance rather than transaction
consistency
Provides a broad umbrella for data storage and manipulation
NoSQL Pros and Cons
Advantages Disadvantages
High scalability, availability, and fault Complex programming is required
tolerance are provided There is no relationship support
Uses low-cost commodity hardware There is no transaction integrity support
Supports Big Data In terms of data consistency, it provides
Key-value model improves storage an eventually consistent model
efficiency
Data Models: A
Summary
Figure 2.5 The Evolution
of Data Models
Checkpoint #2
What does
the term “3
Vs” refers to?
Degrees of Data Abstraction and
ANSI
American National Standards Institute (ANSI): Standards
Planning and Requirements Committee (SPARC) defined a framework
for data modeling based on degrees of data abstraction
Three levels of data abstraction
External
Conceptual
Internal
In Figure 2.6, on the following slide, the ANSI/SPARC framework has
been expanded with the addition of a physical model to explicitly
address physical-level implementation details of the internal model
Degrees of Data Abstraction
Figure 2.6
Data
Abstraction
Levels
The External Model
External model: the end users’ view of the data environment
End users operate in an environment in which an application
has a specific business unit focus
End users within those business units view their data
subsets as separate from or eternal to other units within the
organization
ER diagrams will be used to represent the external views
External schema: specific representation of an external view
The External Model
Figure 2.7
External
Models for
Tiny College
The External Model Advantages
External view advantages:
It is easy to identify specific data required to
support each business unit
It makes the designer’s job easy by
providing feedback about the model’s
adequacy
It helps to ensure security constraints in the
database design
It makes application program development
much simpler
The Conceptual Model
Conceptual model: represents a global view of the entire
database by the entire organization
Conceptual schema: the basis for the identification and high-level
description of the main data objects
Logical design: task of creating a conceptual data model
The most widely used conceptual model is the ER model
Conceptual model advantages
Macro-level view of data environment
Software and hardware independent
The Conceptual
Model diagram
Figure 2.8
Conceptual
Model for Tiny
College
The Internal Model
Internal model: the representation of the database as “seen” by the DBMS
The internal model requires a designer to match the conceptual model’s
characteristics and constraints to those of the selected implementation
model
The internal model depends on specific database software, it is said to be
software dependent
Internal schema: depicts a specific representation of an internal model,
using the database constructs supported by the chosen database
Logical independence: changing internal model without affecting the
conceptual model
Hardware independent: unaffected by the type of computer on which the
software is installed
The Internal Model
Diagram
Figure 2.9
Internal Model for
Tiny College
The Physical Model
Physical model: operates at the lowest level of abstraction, describing
the way data is saved on storage media such as magnetic, solid state, or
optical media
Requires the definition of both the physical storage devices and the
(physical) access methods required to reach the data within those storage
devices
The physical model is both software and hardware dependent
Relational model aimed at logical level- does not require physical-level
details
Physical independence: changes in physical model do not affect internal
model
Levels of Data Abstraction
Table 2.4
Levels of Data Abstraction
Degree of Abstraction
Model Focus Independent of
External High End-user views Hardware and software
Conceptual Medium-High Global view of data (database Hardware and software
model independent)
Internal Medium-Low Specific database model Hardware
Physical Low Storage and access methods Neither hardware nor software
Checkpoint #3
What is logical
independence?