Data Modeling

Yong Choi
School of Business
CSUB
 Part # 2

Study Objectives
 Understand concepts of data modeling and its
purpose
 Learn how relationships between entities are
defined and refined, and how such relationships
are incorporated into the database design process
 Learn how ERD components affect database design
and implementation
 Learn how to interpret the modeling symbols

2
 Part # 2
Why Data Modeling?
Data Model by CASE tool = Actual Database
 Represent “reality” of the actual database
 Blue print: documentation
 Effective Communication Tool
 User involvement
 Identify the business rules to be stored in the
database
 Independence from a particular DBMS
 Example of data model by CASE tool on the
website

3
 Part # 2

Conceptual data modeling

 The conceptual data modeling revolves around
discovering and analyzing organizational and users
data requirements.
 What data is important
 What data should be maintained
 The major activity of this phase is identifying
entities, attributes, and their relationships to
construct model using the Entity Relationship
Diagram methodology.

4
 Part # 2

Entity Relationship diagram (ERD)

 Data modeling methodology
 Developed by Peter Chen (1976).
 See his original ERD article on the class website
 ERD is commonly used to:
 Translate different views of data among managers, users,
and programmers to fit into a common framework.
 Define data processing and constraint requirements to help
us meet the different views.
 Help implement the database.

5
 Part # 2

Basic ERD Elements

 Entity : a collection of people, places, objects, events,
concepts of interest (a table)
 Entity instance – a member of the Entity : a person, a place, an
object … (a row in a table)
 Attribute - property or characteristic of interest of an
entity (a field in a table)
 Relationship – association between entities (corresponds
to primary key-foreign key equivalencies in related tables)

6
ERD using Chen’ Notation (first - original)
Part # 2
 Part # 2

Chen’s Notation
 Entities
 rectangle containing the entity’s name.
 Attributes
 oval containing the attribute’s name.
 Relationships
 diamond containing the relationship’s name.

8
 Part # 2

Steps for creating an ERD

1. Identify entities
2. Identify attributes
3. Identify relationships

9
 Part # 2

Entity
“A fundamental THING of relevance to the enterprise about
which data may be kept”
 What should be an Entity: both tangible & intangible
 An object that will have many instances in the database
 An object that will be composed of multiple attributes
 An object that we are trying to model
 What should NOT be an Entity:
 A user of the database system
 An output of the database system (e.g. a report)

10
 Part # 2

ERD using IE Notation (most popular)

11
 Part # 2

Entity Instance
Entity instance: a single occurrence of an entity.
 6 instances

Entity: student Student Last First

ID Name Name
2144 Arnold Betty
3122 Taylor John
3843 Simmons Lisa
instance
9844 Macy Bill
2837 Leath Heather
2293 Wrench Tim
12
 Part # 2

Attributes
“describe property or characteristic of an entity ”
 Entity: Employee
 Attributes:
 Employee-Name
 Address (composite)
 Phone Extension
 Date-Of-Hire
 Job-Skill-Code
 Salary

13
 Part # 2

Classes of attributes
 Simple attribute
 Composite attribute
 Derived attributes
 Single-valued attribute
 Multi-valued attribute

14
 Part # 2

Simple/Composite attribute

 A simple attribute cannot be subdivided.

 Examples: Age, Gender, and Marital status
 A composite attribute can be further
subdivided to yield additional attributes.
 Examples:
 ADDRESS -- Street, City, State, Zip
 PHONE NUMBER -- Area code, Exchange number

15
 Part # 2

Derived attribute

 is not physically stored within the database

 instead, it is derived by using an algorithm.
 Example: AGE can be derived from the date of birth
and the current date.
 MS Access: int(Date() – Emp_Dob)/365)

16
 Part # 2

(unique) Identifier
“attributes that uniquely identify entity instances”
 Uniquely identify every instance of the entity
 One or more of the entity’s attributes
 Composite identifiers are identifiers that consist of two or more
attributes
 Identifiers are represented by underlying the name of
the attribute(s)
Employee (employee_ID), student (student_ID)

17
 Part # 2

Type of Relationships
 One – to – One (1:1)
 Each instance in the relationship will have exactly one
related member on the other side
 One – to – Many (1:M)
 A instance on one side of the relationship can have many
related members on the other side, but a member on the
other side will have a maximum of one related instance
 Many – to – Many (M:N)
 Instances on both sides of the relationship can have many
related instances on the other side
 Part # 2

1:1 relationship in Set notation

DEPARTMT EMPLOYEE
 Part # 2

1:M relationship in Set notation

DEPARTMT EMPLOYEE
 Part # 2

M:N relationship in Set notation

PRODUCT
WAREHOUSE
 Part # 2

M:N relationship
Each student takes many classes, and a class must be
taken by many students.

IS_TAKEN_BY
CLASS STUDENT
TAKE

** Many-to-many relationships cannot be used in the data

model because they cannot be represented by the
relational model (see the next slide for the reason) **
 Part # 2

Example of M:N
Many-to-many
relationships is a second
sign of complex data.
When x relates to many y's
and y relates to many x's, it
is a many-to-many
relationship.
In our example schema, a
color swatch can relate to
many types of sweaters
and a type of sweater can
have many color
swatches.
Example M:N Relationship
Part # 2
Table to represent Entity

3 to 3
30 to 30
300 to 300
3000 to 3000
30,000 to 30,000
300, 000 to 300, 000
Converting M:N Relationship to Two 1:M Relationships
Part # 2

Bridge Entity
 Part # 2

Bridge Entity
 MUST have a composite (unique) identifier
 STU_NUM (from STUDENT entity) and CLASS_CODE
(from CLASS entity)