DATABASE SYSTEMS
DESIGN IMPLEMENTATION AND MANAGEMENT
INTERNATIONAL EDITION
ROB • CORONEL • CROCKETT
CHAPTER 2
DATA MODELS
1
DATABASE SYSTEMS: Design Implementation and Management (Rob, 2
Coronel & Crockett 9781844807321)
In this chapter, you will learn:
A. Data model building blocks
B. Business rules
C. Evolvement of data models
D. Data abstraction
Whenever you see a Einstein picture, the section is
finished
Database Systems: Design, Implementation, & Management, International Edition, Rob, Coronel & Crockett 2
DATABASE SYSTEMS: Design Implementation and Management (Rob, 2
Coronel & Crockett 9781844807321)
A. The importance of data models e.g.
ER diagram
• A data model is a relatively simple representation, usually
graphical, of more complex real-world data structures
• Powerful database design tools are available to make useful
drawings and automate a lot of the design process e.g. Visio
• Saves costs, reduces development time, decreases errors
• Improves understanding of the business and
• Improves communication between role players on the project.
Database Systems: Design, Implementation, & Management, International Edition, Rob, Coronel & Crockett 3
DATABASE SYSTEMS: Design Implementation and Management (Rob, 2
Coronel & Crockett 9781844807321)
B. Data model basic building blocks
• An entity is a real world object distinguishable from
others.
– Example: specific person, place, thing or event
• Entities have attributes (characteristics)
– Example: people
• Relationship describes an association among entities
4
DATABASE SYSTEMS: Design Implementation and Management (Rob, 2
Coronel & Crockett 9781844807321)
Examples of Entities
5
DATABASE SYSTEMS: Design Implementation and Management (Rob, 2
Coronel & Crockett 9781844807321)
Examples of Entities
6
DATABASE SYSTEMS: Design Implementation and Management (Rob, 2
Coronel & Crockett 9781844807321)
Relationships
• A relationship is an association among entities
– One to many (1:*) – One lecturer many students
– Many to many (*: *) – Many students study many courses
– One to One (1:1) – one Department has one HOD
• Constraint is a restriction placed on the data
– Ensures data integrity
– E.g. student’s DP percentage must be between 0 and 100
– Each class must have one and only one teacher per
subject
7
DATABASE SYSTEMS: Design Implementation and Management (Rob, 2
Coronel & Crockett 9781844807321)
A database is just a container
8
DATABASE SYSTEMS: Design Implementation and Management (Rob, 2
Coronel & Crockett 9781844807321)
Business Rules – 1
• Business rules are 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
Database Systems: Design, Implementation, & Management, International Edition, Rob, Coronel & Crockett 9
DATABASE SYSTEMS: Design Implementation and Management (Rob, 2
Coronel & Crockett 9781844807321)
Business Rules – 2
• 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: Design, Implementation, & Management, International Edition, Rob, Coronel & Crockett 10
DATABASE SYSTEMS: Design Implementation and Management (Rob, 2
Coronel & Crockett 9781844807321)
Discovering Business Rules
Sources of Business Rules:
• Company managers
• Policy makers
• Department managers
• Written documentation
– Procedures
– Standards
– Operations manuals
• Direct interviews with end users
Database Systems: Design, Implementation, & Management, International Edition, Rob, Coronel & Crockett 11
DATABASE SYSTEMS: Design Implementation and Management (Rob, 2
Coronel & Crockett 9781844807321)
Discovering Business Rules
• Generally, nouns translate into entities
– Customer, Invoice, Course, Classroom
• Verbs translate into relationships among entities
– Purchase, pay, generate invoice, attend course,
Database Systems: Design, Implementation, & Management, International Edition, Rob, Coronel & Crockett 12
DATABASE SYSTEMS: Design Implementation and Management (Rob, 2
Coronel & Crockett 9781844807321)
Discovering Business Rules
• Relationships are bi-directional
– A customer may generate many invoices
– An invoice is generated by only one customer
• A relationship is an association among entities
– One to many (1:*)
– Many to many (*: *)
– One to One (1:1)
Database Systems: Design, Implementation, & Management, International Edition, Rob, Coronel & Crockett 13
DATABASE SYSTEMS: Design Implementation and Management (Rob, 2
Coronel & Crockett 9781844807321)
In-class exercise
Consider a university course offered to students and involving a lecturer.
Give an example of a business rule showing both directions of the
relationship (remember the 3 types of relationships).
• Fort Hare has many departments. (Hint: University + department)
• Fort Hare has different departments that teaches a variety of specific
courses. (Hint: Department + course)
• The lectures are taught in a classroom allocated specifically to that
course (Hint: Classroom + lecture)
• The students attend classes taught by a lecturer. (Hint: class+
student)
Database Systems: Design, Implementation, & Management, International Edition, Rob, Coronel & Crockett 14
DATABASE SYSTEMS: Design Implementation and Management (Rob, 2
Coronel & Crockett 9781844807321)
In-class exercise
• Consider a university course offered to students
and involving a lecturer. Give an example of a
business rule showing both directions of the
relationship.
– Example 1: One lecturer teaches many students.
Students are taught by one lecturer.
– Example 2: One department offers many courses. A
course is offered by one department.
– Example 3: A classroom is used for many lectures. A
lecture takes place in one classroom.
Database Systems: Design, Implementation, & Management, International Edition, Rob, Coronel & Crockett 15
DATABASE SYSTEMS: Design Implementation and Management (Rob, 2
D. The Evolution of Data Models
Coronel & Crockett 9781844807321)
16
DATABASE SYSTEMS: Design Implementation and Management (Rob, 2
Coronel & Crockett 9781844807321)
D. The Evolution of Data Models
D1. Hierarchical
D2. Network
D3. Relational
D4. Entity relationship
D5. Object oriented (OO)
Database Systems: Design, Implementation, & Management, International Edition, Rob, Coronel & Crockett 17
DATABASE SYSTEMS: Design Implementation and Management (Rob, 2
Coronel & Crockett 9781844807321)
D3. The Relational Model
• Developed by EF Codd in 1970
• Mathematician, computer scientist
• Pilot in Royal Air Force during World
War 2
• Moved to New York in 1948 to work for
IBM as a mathematical programmer
• Worked for IBM in California until 1980s
• Invented the relational model for
database management, the theoretical
basis for relational databases
Source: Wikipedia article on EF Codd, 2010
• Received Turing Award in 1981
Database Systems: Design, Implementation, & Management, International Edition, Rob, Coronel & Crockett 18
DATABASE SYSTEMS: Design Implementation and Management (Rob, 2
Coronel & Crockett 9781844807321)
Codd: Some career highlights – 1
• Published his paper called "A Relational Model of Data for
Large Shared Data Banks" in 1970. This was the start of
relational databases.
• Initially, IBM refused to implement the relational model in
order to preserve revenue from IMS/DB.
• Codd then showed IBM customers the potential of the
implementation of its model, and they in turn pressured IBM.
Then IBM included in its Future Systems project a System R
subproject.
Database Systems: Design, Implementation, & Management, International Edition, Rob, Coronel & Crockett 19
DATABASE SYSTEMS: Design Implementation and Management (Rob, 2
Coronel & Crockett 9781844807321)
Codd: Some career highlights – 2
• A language called SEQUEL was developed, not what Codd
had developed, but still much better than what was available.
When the language was launched it was called SQL
• As the relational model started to become fashionable in the
early 1980s, Codd fought a sometimes bitter campaign to
prevent the term being misused by database vendors who
had merely added a relational veneer to older technology.
• Edgar Codd coined the term OLAP and wrote the twelve laws
of online analytical processing
Database Systems: Design, Implementation, & Management, International Edition, Rob, Coronel & Crockett 20
DATABASE SYSTEMS: Design Implementation and Management (Rob, 2
Coronel & Crockett 9781844807321)
D3. The Relational Model – 1
• “A relational model for Large Shared Databanks”
• 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: Design, Implementation, & Management, International Edition, Rob, Coronel & Crockett 21
DATABASE SYSTEMS: Design Implementation and Management (Rob, 2
Coronel & Crockett 9781844807321)
D3. The Relational Model – 2
• The relational model is implemented through a sophisticated
Relational Database Management System (RDBMS)
• Most important advantage of the RDBMS is its ability to hide
the complexities of the relational model from the user
Database Systems: Design, Implementation, & Management, International Edition, Rob, Coronel & Crockett 22
DATABASE SYSTEMS: Design Implementation and Management (Rob, 2
Coronel & Crockett 9781844807321)
D3. The Relational Model – 3
• Table (relations)
– Matrix consisting of a series of row/column
intersections
– Related to each other through sharing a common
entity characteristic
Database Systems: Design, Implementation, & Management, International Edition, Rob, Coronel & Crockett 23
DATABASE SYSTEMS: Design Implementation and Management (Rob, 2
Coronel & Crockett 9781844807321)
D3. The Relational Model – 4
Database Systems: Design, Implementation, & Management, International Edition, Rob, Coronel & Crockett 24
DATABASE SYSTEMS: Design Implementation and Management (Rob, 2
Coronel & Crockett 9781844807321)
D3. The Relational Model – 5
• A table is purely a 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
– If you're familiar with spreadsheets you're familiar with
tables of rows and columns.
25
DATABASE SYSTEMS: Design Implementation and Management (Rob, 2
Coronel & Crockett 9781844807321)
D3. The Relational Model – 6
• Relational diagram:
– Is a representation of the relational database’s entities,
– attributes within those entities, and
– relationships between those entities
Database Systems: Design, Implementation, & Management, International Edition, Rob, Coronel & Crockett 26
DATABASE SYSTEMS: Design Implementation and Management (Rob, 2
Coronel & Crockett 9781844807321)
D3. The Relational Model – 7
• Rise to dominance of the relational model was 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
• Information in the model is represented using entity-
relationship models
Database Systems: Design, Implementation, & Management, International Edition, Rob, Coronel & Crockett 27
DATABASE SYSTEMS: Design Implementation and Management (Rob, 2
Coronel & Crockett 9781844807321)
E. Degrees of Data Abstraction – 1
• Data abstraction is the reduction of a certain portion of data for
a simple presentation of content. Abstraction, in general, the
process of removing features from something to reduce the set
of necessary features
• 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
Database Systems: Design, Implementation, & Management, International Edition, Rob, Coronel & Crockett 28
DATABASE SYSTEMS: Design Implementation and Management (Rob, 2
Coronel & Crockett 9781844807321)
E. Degrees of Data Abstraction – 2
• American National Standards Institute (ANSI)
Standards Planning and Requirements Committee
(SPARC)
– Defined a framework for data modeling based on
degrees of data abstraction(1970s)
Database Systems: Design, Implementation, & Management, International Edition, Rob, Coronel & Crockett 29
DATABASE SYSTEMS: Design Implementation and Management (Rob, 2
E. Degrees of Data Abstraction – 3
Coronel & Crockett 9781844807321)
E1
E2
E3
E4
Database Systems: Design, Implementation, & Management, International Edition, Rob, Coronel & Crockett 30
DATABASE SYSTEMS: Design Implementation and Management (Rob, 2
Coronel & Crockett 9781844807321)
E1. The External Model – 1
• End users’ view of the data environment
• Use business rules
• Highest level of abstraction
Database Systems: Design, Implementation, & Management, International Edition, Rob, Coronel & Crockett 31
DATABASE SYSTEMS: Design Implementation and Management (Rob, 2
Coronel & Crockett 9781844807321)
End user
Database Systems: Design, Implementation, & Management, International Edition, Rob, Coronel & Crockett 32
DATABASE SYSTEMS: Design Implementation and Management (Rob, 2
Coronel & Crockett 9781844807321)
E1. The External Model – 2
• Advantages:
– Easy to identify specific data required to support each
business unit’s operations
– Facilitates designer’s job by providing feedback about
the model’s adequacy
– Creation of external models helps to ensure security
constraints in the database design
– Simplifies application program development
Database Systems: Design, Implementation, & Management, International Edition, Rob, Coronel & Crockett 33
DATABASE SYSTEMS: Design Implementation and Management (Rob, 2
Coronel & Crockett 9781844807321)
E2. The Conceptual Model – 1
• 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
Database Systems: Design, Implementation, & Management, International Edition, Rob, Coronel & Crockett 34
DATABASE SYSTEMS: Design Implementation and Management (Rob, 2
Coronel & Crockett 9781844807321)
2. The Conceptual Model – 2
• 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
Database Systems: Design, Implementation, & Management, International Edition, Rob, Coronel & Crockett 35
DATABASE SYSTEMS: Design Implementation and Management (Rob, 2
Coronel & Crockett 9781844807321)
Designer
Database Systems: Design, Implementation, & Management, International Edition, Rob, Coronel & Crockett 36
DATABASE SYSTEMS: Design Implementation and Management (Rob, 2
Coronel & Crockett 9781844807321)
E3. 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
Database Systems: Design, Implementation, & Management, International Edition, Rob, Coronel & Crockett 37
DATABASE SYSTEMS: Design Implementation and Management (Rob, 2
Coronel & Crockett 9781844807321)
E4. The Physical Model – 1
• 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: Design, Implementation, & Management, International Edition, Rob, Coronel & Crockett 38
DATABASE SYSTEMS: Design Implementation and Management (Rob, 2
Coronel & Crockett 9781844807321)
Factory floor
Database Systems: Design, Implementation, & Management, International Edition, Rob, Coronel & Crockett 39
DATABASE SYSTEMS: Design Implementation and Management (Rob, 2
Coronel & Crockett 9781844807321)
E4. The Physical Model – 2
Database Systems: Design, Implementation, & Management, International Edition, Rob, Coronel & Crockett 40
DATABASE SYSTEMS: Design Implementation and Management (Rob, 2
Coronel & Crockett 9781844807321)
Database Systems: Design, Implementation, & Management, International Edition, Rob, Coronel & Crockett 41
DATABASE SYSTEMS: Design Implementation and Management (Rob, 2
Coronel & Crockett 9781844807321)
D. Data Models: A Summary – 2
Database Systems: Design, Implementation, & Management, International Edition, Rob, Coronel & Crockett 42
DATABASE SYSTEMS: Design Implementation and Management (Rob, 2
Coronel & Crockett 9781844807321)
• A hospital patient receives medications that have
been ordered by a particular doctor. Because the
patient often receives several medication per day,
there is a 1:* relationship between PATIENT and
ORDER. Similarly, each order can include several
medications, creating a 1:* relationship between
ORDER AND MEDICATION.
– Identify the business rules for PATIENT, ORDER and
MEDICATION
– Create an ERD to capture these business rules
Database Systems: Design, Implementation, & Management, International Edition, Rob, Coronel & Crockett 43