DBS101

Introduction to Database Models

REFERENCE TEXT:
CHA PT ER 0 2 : DATABA SE SYSTEMS: DES I GN, I M P L EMENTATIO N, A N D
M A N AGEMENT

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage. All Rights Reserved. May
not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Learning Objectives

 Will talk about data modeling and why data models are important ??

 Basic data-modeling building blocks.

 What business rules are and how they influence database design??

 What are different types of data models and levels of abstraction ??

 Flashback From last Week

Database is:

 A centralized repository of data.

 Maintained in real-time.

 Allows multiple users to work concurrently.

 Allows ad-hoc queries for data extraction for informed decision making.
 Data Modeling and Data Models
Data modeling: Iterative and progressive process of creating a specific data model for a
determined problem domain.

Data models:
 Simple representations of complex real-world data structures.
 Useful for supporting a specific problem domain.
 Model - Abstraction of a real-world object or event.
 Describes the structure and format of data storage.
 Defines the limitations place on the data stored through a series of defined constraints or
enforced rules.
 Specifies operations performed on data through selection (queries) and modification
(insertion, updating and deleting).
Importance of Data Models

Are a communication tool

Give an overall view of the database

Organize data for various users

Are an abstraction for the creation of good

database
Data Models
Generation Time Data Models

First 1960-1970 File Systems

Second 1970s Hierarchical and network

Third Mid-1970s Relational

Fourth Mid-1980s Object oriented relational

Fifth Mid-1990s XML hybrid DBMS

Emerging Early 2000 to present Key value store column store

technologies
Data Model Basic Building Blocks (1)
An entity is a person, place, thing, concept, or event about which data will be collected and
stored.

An attribute is a characteristic of an entity.

A relationship describes an association among entities.

The following are three different types of relationships:

◦ One-to-many (1:M or 1..*) relationship
◦ Many-to-many (M:N or *..*) relationship
◦ One-to-one (1:1 or 1..1) relationship
Data Model Basic Building Blocks (2)
A constraint is a restriction placed on the data.
◦ Constraints help ensure data integrity.

Constraints are normally expressed in the form of rules:

◦ An employee’s salary must have values that are between 6,000 and 350,000
◦ A student’s GPA must be between 0.00 and 4.00
◦ Each class must have one and only one teacher
Business Rules
A business rule is 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.

Business rules are 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.
◦ A customer may buy some products.
Discovering Business Rules
The main sources of business rules are company managers, policy makers, department
managers, and written documentation such as company procedures.

Business rules are essential to database design due to the following reasons:

◦ It helps to standardize the company’s view of data.

◦ It can be a communication tool between users and designers.
◦ It allows the designer to understand the nature, role, and scope of the data.
◦ It allows the designer to understand business processes.
◦ It allows the designer to develop appropriate relationship participation rules and
constraints and to create an accurate data model.
 Translating Business Rules into Data Model
Components (1)
 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?
Translating Business Rules into Data
Model Components (2)
For example, the business rule “a customer may generate many invoices”
contains two nouns and a verb that associates the nouns.
From this business rule, you could deduce the following:
◦ Customer and invoice are objects of interest for the environment and should
be represented by their respective entities.
◦ There is a generate relationship between customer and invoice.
The rule above is complemented by the business rule “an invoice is generated
by only one customer”.
◦ The relationship is one-to-many (1:M)
Naming Conventions
Entity names should be descriptive of the objects in the business environment and use
technology that is familiar to the users.

An attribute name should also be descriptive of the data represented.

It is good practice to prefix the name of an attribute with the name or abbreviation of
the entity in which it occurs.
For example, in the CUSTOMER entity, customer’s credit limit may be called
CUS_CREDIT_LIMIT

A proper naming convention can help make your model self-documenting.

The Relational Model (1)
The relational model’s foundation is a mathematical concept known as a relation.
◦ A relation is a two-dimensional structure composed of intersecting rows and
columns.
◦ Each row in a relation is called a tuple and each column represents an attribute.
The relational data model is implemented through a very sophisticated relational
database management system (RDBMS).
◦ The RDBMS performs the same basic functions provided by the hierarchical and
network DBMS systems.
The RDBMS manages all of the details, while the users sees a collection of tables in
which the data is stored.
The Relational Model (2)

Figure 2.1 Linking Relational Tables

The Relational Model (3)

Figure 2.2 A Relational Diagram

The Relational Model (4)
Any SQL-based relational database application involves the following three
parts:
◦ The end user interface – the interface allows the end user to interact with the
data.

◦ A collection of tables stored in the database – the tables “present” the data to
the end user in a way that is easy to understand.

◦ SQL engine – the SQL engine executes all queries or data requests.
The Entity Relationship Model (1)
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
An entity relationship diagram (ERD) uses graphical representations to model
database components
The Entity Relationship Model (2 )
The ER model is based on the following components:
◦ Entity – an entity is represented in the ERD by a rectangle (entity box)
◦ Attributes – each entity consists of a set of attributes that describes
characteristics of the entity
◦ Relationships – relationships describe associations among data
The following are three ER notations:
◦ Chen notation
◦ Crow’s Foot notation
◦ Class diagram notation (part of the Unified Modeling Language (UML))
The Entity Relationship Model (3)

Figure 2.3 The ER Model Notations

 Entity Relationship Model (One last time)
 Relational model was introduced in 1970s by E.F.Codd of IBM.

 Relation is a table composed of rows and columns.

 Each row in a relation is called a tuple.

 Each column represents an attribute.

 Relation represents an entity or relationship between entities.

Entity\Table\Relation \File
Row
Record
Instance
Tuple

Column
Property
Attribute
Degrees of Data Abstraction (1)
The American National Standards Institute (ANSI) Standards Planning and
Requirements Committee (SPARC) defined a framework for data modeling based
on degrees of data abstraction.

The three levels of data abstraction are external, conceptual, and 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 (2)

Figure 2.6 Data Abstraction Levels

The External Model (1)
The external model is the end users’ view of the data environment.

End users usually 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.

A specific representation of an external view is known as an external schema.
The External Model (2)

Figure 2.7 External Models for Tiny

College
The External Model (3)
The use of external views that represent subsets of the database has some
important 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 (1)
The conceptual model represents a global view of the entire database by the entire
organization.
Also known as a conceptual schema, it is the basis for the identification and high-level
description of the main data objects.

The most widely used conceptual model is the ER model.

The following are advantages of the conceptual model:
◦ It provides a bird’s-eye view of the data environment that is easy to understand.
◦ The conceptual model is independent of both software and hardware.

The term logical design refers to creating a conceptual data model .

The Conceptual Model (2)

Figure 2.8 Conceptual Model for Tiny

College
The Internal Model (1)
The internal model is 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.
An internal schema depicts a specific representation of an internal model, using the
database constructs supported by the chosen database.
Because the internal model depends on specific database software, it is said to be
software dependent.
When you change the internal model without affecting the conceptual model, you have
logical independence.
However, the internal model is still hardware independent.
The Internal Model (2 )

Figure 2.9 Internal Model for Tiny

College
The Physical Model
The physical model operates at the lowest level of abstraction, describing the
way data is saved on storage media.

The physical model requires the definition of both the physical storage devices
and the (physical) access methods required to reach the data within those
storage devices.
◦ This means the physical model is both software and hardware dependent

When you can change the physical model without affecting the internal model,
you have physical independence .
Key Terms
1. Business Rule 6. Data Model

2. Relational Model 7. Entity

3. ERD 8. Logical Independence

4. Relationship 9. Network Model

5. Software Independence 10. Database Modeling

 Review Questions
 Discuss the importance of data models.

 List different types of data models and compare relational with hierarchical and
network data models.

 How do you translate business rules into data model components?

 What is a relationship, and what three types of relationship exists?

 What is a table, and what roles does it play in the relational model?