Chapter 3

Data modelling and

databases

1
Outline
• Data model
• Data modeling
• E/R model
• Ralational data model
• Data modeling process

2
Data model
• A collection of conceptual tools for describing data,
data relationships, data semantics and consistency
constraint.
• A conceptual representation of data structures
• Visually represents the nature of data, business rules
governing the data, and how it will be organized in the
database.

3
Data modeling
• Involving professional data modelers working closely
with business stakeholders, as well as potential users
of the information system.
• Process of creating a data model for an information system by
applying certain formal techniques.
• Defining and analyze data requirements needed to support
the business processes.
• Objectives
• To make sure all data objects required by a database are
completely and accurately represented
• The blueprint for creating a physical implementation of a
database (commonly)

4
Data modeling requirements
• What is the information/data domain that you are
modeling?
• What are the queries that you want to do?
• e.g., “Find out the most wanted products? How many sales
today?”
• What software do you want (have) to use?
• How do you want to share the data?

5
Data Model Terms
• Entity – a class of real-world objects having common
attributes (e.g., sites, variables, methods).
• Attribute – A characteristic or property of an entity (site
name, latitude, longitude)
• Relationship – an association between two or more
entities
• Cardinality – the number of entities on either end of a
relationship (one-to-one, one-to-many, many-to-many,
etc.)

6
 Examples

• Consider:
• What is the “entity”?
• What are the “attributes” of the
entity?

7
Examples
• What is the entity?
• What are the attributes?

8
Examples
• What is the entity?
• What are the attributes?

9
Examples
• What are the relationships?

Grows In

Apple Apple Tree Orchard

Grows On

10
Different levels of data models

11
Different levels of data models
• Conceptual: describes WHAT the system contains
• High-level description of the data domain
• Does not constrain how that description is mapped to an
actual implementation in software
• Logical: describes HOW the system will be
implemented, regardless of the DBMS
• Technology independent
• Contains more detail than the Conceptual Data Model
• Physical: describes HOW the system will be
implemented using a specific DBMS

12
Conceptual data models
• WHAT the system contains.

13
Logical data models
• HOW the system will be implemented, regardless of
the DBMS

14
Physical data models
• HOW the system will be implemented using a specific
DBMS

15
Entity – Relationship model

16
E/R data model
• E/R is a visual syntax for DB design which is precise
enough for technical points, but abstracted enough for
non-technical people

name
name category
price
Product Makes Company

17
Entity and Entity sets
• Entity
• is a thing in the real world with an independent existence.
• An entity may be an object with a physical existence (a
particular person, car, house, or employee) or it may be an
object with a conceptual existence (a company, a job, or a
university course).
• Entity sets
• a collection of similar entities forms an entity set.
• In ERD, rectangular boxes represent for entity sets
Attributes
• Entity sets have associated attributes, which are
properties of the entities in that set.
• For instance, each entity "student" has some properties such
as student_id, first_name, last_name, dob, gender, address,
and so on.
• In ERD, ovals represent for attributes
• Value domain of an attribute
• Each simple attribute of an entity type is associated with a
value set (or domain of values).
• For example: domain(gender) = {male, female}; domain(dob)
= {date}; domain(last_name) = {char(30)}.

student_id student
gender

full_name dob
Example: Entities, Entity sets, and attributes

Entities are not explicitly

Example: represented in E/R diagrams!

Entity
name category

Name: Xbox
price
Category: Total Name: My Little Pony Doll Entity
Product
Multimedia System
Price: $250
Category: Toy
Price: $25
Attribute

Product

Entity Set

20
 Attribute types

• Simple/atomic city
attributes: Attributes
that are not divisible. street state

• Composite attributes: note address

attributes can be
divided into smaller student_id student
subparts, which
represent more basic gender
attributes with full_name dob
independent
meanings. first_name
last_name
 Attribute types

• Single-valued attributes:
have a single value for a
particular entity lecturer

• Multi-valued attributes: can

have different numbers of subject_id subject
values credit

name
 Attribute types

• Stored attributes vs.

Derived attributes: age dob
student_id
attribute is called a derived
attribute and is said to be
derivable from the dob student
attribute, which is called a
stored attribute.
age
full_name
Keys
• A key is a minimal set of attributes that uniquely
identifies an entity.
• One or more attributes whose values are distinct for each
individual entity in the entity set
• Each entity can have some keys (candidate keys). We
choose one of them to be primary key.
• In ER diagrammatic notation, each key attribute has its
name underlined inside the oval.

student_id student
gender

full_name dob
Relationships
• Relationships are connections among two or more
entity sets.
• In ER diagrams, relationship types are displayed as
diamond-shaped boxes
• which are connected by straight lines to the rectangular boxes
representing the participating entity types.
• The relationship name is displayed in the diamond-shaped
box.
student learn subject
Relationship attributes

dob
subject_id
student_id lecturer

student learn subject

credit
full_name
age result name
 Relationship types

• 1–1 shop
1
manage
1
manager
• 1–n
• n–m class
1
join
n
student
• recursive
n m
course enroll student

role1
subject condition

role2
How to create an ERD
• Step 1: Identify all entity sets
• Notice concepts, nouns
• Step 2: Identify all relationships among entity sets
• Notice verbs
• Type and degree of relationships
An example
• Read carefully the following scenario:
• The information about students includes student identification
(uniquely identify each student), name, gender, date of birth
and address.
• During the education time at school, students must study a lot
of subjects. A subject can be learnt by students. A subject
should be contained information such as subject identification,
name and credit.
• A lecturer can teach some subjects, and a subject can be
taught by a group of lecturers. The information about lecturers
should include lecturer identification, name, phone, email.
• Students learn subjects at some semester, and their results
should be stored.
An example
• We can draw this ER diagram

subject_id lecturer_id name

result credit
student_id
dob

n m n m
student learn subject teach lecturer

name

email phone
gender address name
semester
What is a Relationship?

• A mathematical definition:
• Let A, B be sets B=
A= 1 a
• A={1,2,3}, B={a,b,c,d},
2 b
• A x B (the cross-product) is the set of all pairs
(a,b) c
• A ´ B = {(1,a), (1,b), (1,c), (1,d), (2,a), (2,b), 3
(2,c), (2,d), (3,a), (3,b), (3,c), (3,d)} d
• We define a relationship to be a subset of A
xB
• R = {(1,a), (2,c), (2,d), (3,b)}

31
What is a Relationship?

name name
category
price
Product Makes Company

A relationship between entity sets P and C is a

subset of all possible pairs of entities in P and C,
with tuples uniquely identified by P and C’s keys

32
What is a Relationship?

Company Product
name name category price
GizmoWorks Gizmo Electronics $9.99
GadgetCorp GizmoLite Electronics $7.50
Gadget Toys $5.50

name category name

price
Product Makes Company

A relationship between entity sets P and C is a

subset of all possible pairs of entities in P and C,
with tuples uniquely identified by P and C’s keys

33
 What is a Relationship?

Product Company C × Product P

Company
name name category price C.name P.name P.category P.price

GizmoWorks Gizmo Electronics $9.99 GizmoWorks Gizmo Electronics $9.99

GadgetCorp GizmoLite Electronics $7.50 GizmoWorks GizmoLite Electronics $7.50

Gadget Toys $5.50 GizmoWorks Gadget Toys $5.50
GadgetCorp Gizmo Electronics $9.99
GadgetCorp GizmoLite Electronics $7.50
GadgetCorp Gadget Toys $5.50
name category name
price
Product Makes Company

A relationship between entity sets P and C is a

subset of all possible pairs of entities in P and C,
with tuples uniquely identified by P and C’s keys

34
 What is a Relationship?

Product Company C × Product P

Company
name name category price C.name P.name P.category P.price

GizmoWorks Gizmo Electronics $9.99 GizmoWorks Gizmo Electronics $9.99

GadgetCorp GizmoLite Electronics $7.50 GizmoWorks GizmoLite Electronics $7.50

Gadget Toys $5.50 GizmoWorks Gadget Toys $5.50
GadgetCorp Gizmo Electronics $9.99
GadgetCorp GizmoLite Electronics $7.50
GadgetCorp Gadget Toys $5.50
name category name
price
Product Makes Company
Makes
C.name P.name
A relationship between entity sets P and C is a GizmoWorks Gizmo
subset of all possible pairs of entities in P and C, GizmoWorks GizmoLite
with tuples uniquely identified by P and C’s keys GadgetCorp Gadget

35
What is a Relationship?

• There can only be one relationship for This follows from our
every unique combination of entities mathematical
definition of a
relationship- it’s a
SET!
• This also means that the relationship is
uniquely determined by the keys of its
entities KeyMakes = KeyProduct ∪ KeyCompany

since
• Example: the “key” for Makes (to right) is name category
name

{Product.name, Company.name} price Product Makes Company

36
Decision: Relationship vs. Entity?

• Q: What does this say?

date

name category name

price

Product Purchased Person

• A: A person can only buy a specific product once (on one

date)

Modeling something as a relationship makes it unique; what if

not appropriate?

37
Decision: Relationship vs. Entity?

• What about this way?

date PID# quantity

name
name category Purchase

price
ProductOf BuyerOf
Product Person

• Now we can have multiple purchases per product, person pair!

We can always use a new entity instead of a relationship. For example, to

permit multiple instances of each entity combination!

38
Relational data model

39
Relational Data Model
• introduced by C F Codd in 1970
• A logical view of data
• Enables programmer to view data logically rather than
physically
• Simple and uniform data model
• Based on a firm mathematical foundation (set theory)
• Main aspects:
• Data structure: n-ary two dimensional
• Attributes, domain value Student
• Tuples Id Name Suburb
• Tables/relations 1108 Robert Kew
• Integrity constraints 3936 Glen Bundoora
• entity integrity 8507 Norman Bundoora
• referential integrity
8452 Mary Balwyn

40
Example: University Database
Takes
Student SID SNO Enrol
Id Name Suburb 1108 21
SID Course
1108 Robert Kew 1108 23
3936 101
3936 Glen Bundoora 1108 29
1108 113
8507 Norman Bundoora 8507 23
8507 101
8452 Mary Balwyn 8507 29

Subject
Course
No Name Dept
No Name Dept 21 Systems CSCE
113 BCS CSCE 23 Database CSCE
101 MCS CSCE 29 VB CSCE
18 Algebra Maths

41
Attribute
• Definition
• designated by a meaningful name
• also called field or column
• denoted A
ü Example
• Id, name, suburb, dept, …

• Domain
• define the original sets of data values used
• denoted Domain(A)
Ö Example
• text, number, boolean, date/time, memo
• Id: text(10)
• Name: text(30)
• birthday: date
• …

42
Relation
• Definition
• Defined as a set of attribute
• also called table
• Denoted R(A1,A2, … An)
R(A1,A2, … An) Í Dom(A1) x … x Dom(An)
ü Example
• STUDENT(Id , Name, Suburb)
• SUBJECT (No, Name, Dept)
• A relation is composed of tuples

43
Tuple
• Definition
• defined as a set of attribute value
• also called record or row
• Denoted t(a1,a2, … an)
• t(a1,a2, … an) Î Dom(A1) x … x Dom(An)
ü Example
(1108, Robert, Kew)
(3936, Glen, Bundoora)
• Each tuple must have a primary key which can
uniquely identified the tuple.

44
Key
• Definition
• A set of attribute in a relation
• Used to identify each tuple
• Given R(A1,A2, … An), K Í {Ai},
K is key if "t1, t2 ÎR, $Ai ÎK: t1.K¹t2.K
ü Example
• STUDENT(Id , Name, Suburb)
• TAKE(SID, SNO)
• Remark:
• If K Í {Ai} is key and K Í K’ Í {Ai}
à K’ is super key
• candidate key
• a superkey without redundant attributes

45
Primary key
• Definition
• a “smallest” key, i.e. with single attribute or smallest number
of attributes allowing identify a unique tuple
• A candidate key chosen to be the main key for the relation

• Entity constraint:
• No attribute in the primary key can be NULL

ü Example
• STUDENT(Id , Name, Suburb)
• SUBJECT (No, Name, Dept)

46
Foreign key
• Specified between two relations and maintain the
correspondence between tuples in these relations
• also called referential integrity

• A set of attributes FK in a relation R1 is foreign key if

• The attributes in FK correspond to the attributes in the primary
key of another relation R2
• The value for FK in each tuple of R1 either occur as values of
primary key of a tuple in R2 or is entirely NULL

47
Example: University Database
Takes
Student SID SNO Enrol
Id Name Suburb 1108 21
SID Course
1108 Robert Kew 1108 23
3936 101
3936 Glen Bundoora 1108 29
1108 113
8507 Norman Bundoora 8507 23
8507 101
8452 Mary Balwyn 8507 29

Subject
Course
No Name Dept
No Name Dept 21 Systems CSCE
113 BCS CSCE 23 Database CSCE
101 MCS CSCE 29 VB CSCE
18 Algebra Maths

48
 Takes
Student SID SNO
Id Name Suburb 1108 21
1108 Robert Kew 1108 23
3936 Glen Bundoora 1108 29
8507 Norman Bundoora 8507 23
8452 Mary Balwyn 8507 29

Subject Enrol
No Name Dept SID Course
21 Systems CSCE 3936 101
23 Database CSCE 1108 113
29 VB CSCE 8507 101
18 Algebra Maths

49
 A Brief History

Relationa DB2,
l model ORACLE-
Hierarchical Extended Relational 10i, SQL
model Model Server ...

System R(81), DB2, XML

ORACLE, SQL
IMS, Server, Sybase, ...
System
2k,
...
1965 1970 1975 1980 1985 1990 1995 2000 2005 2010

Entity-Relationship O2,
ORION,
Model IRIS, ...
Network IRDS(87)
model ,CDD+,
...
DMS(65), Object-Oriented Semi-structure
Lore
CODASYL model Model (97), ... 50
(71), IDMS,
Building Models Top Down vs. Bottom Up
• Top-Down
• Bottom-Up
• Using a Hybrid Approach – Middle Out

51
Top-down

© CHRISTOPHER BRADLEY (CDMP FELLOW)

52
Conclusion
• In this lesson you have learnt
• Data model
• Data modeling
• E/R model
• Ralational data model
• Data modeling process

54
 Thank you
for your
attention!!!

55
© CHRISTOPHER BRADLEY (CDMP FELLOW)
56