Data Modeling

1
Data Modeling

• Yong Choi
• School of Business
• CSUB

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

3
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

4
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.

5
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.

6
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)

7
ERD using Chen Notation (first - original)
8
Chens Notation

• Entities
• rectangle containing the entitys name.
• Attributes
• oval containing the attributes name.
• Relationships
• diamond containing the relationships name.

9
Steps for creating an ERD

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

10
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)

11
ERD using IE Notation (most popular)
12
Entity Instance

• Entity instance a single occurrence of an
  entity.
• 6 instances

Student ID Last Name First Name
2144 Arnold Betty
3122 Taylor John
3843 Simmons Lisa
9844 Macy Bill
2837 Leath Heather
2293 Wrench Tim
Entity student
instance
13
Attributes

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

14
Classes of attributes

• Simple attribute
• Composite attribute
• Derived attributes
• Single-valued attribute
• Multi-valued attribute

15
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

16
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)

17
(unique) Identifier

• attributes that uniquely identify entity
  instances
• Uniquely identify every instance of the entity
• One or more of the entitys 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)

18
Type of Relationships

• One to One (11)
• Each instance in the relationship will have
  exactly one related member on the other side
• One to Many (1M)
• 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 (MN)
• Instances on both sides of the relationship can
  have many related instances on the other side

19
11 relationship in Set notation
20
1M relationship in Set notation
21
MN relationship in Set notation
22
MN relationship
Each student takes many classes, and a class must
be taken by many students.
STUDENT
CLASS
IS_TAKEN_BY
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)
23
Example of MN 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. 
24
Example MN Relationship
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
25
Converting MN Relationship to Two 1M
Relationships
Bridge Entity
26
Bridge Entity

• MUST have a composite (unique) identifier
• STU_NUM (from STUDENT entity) and CLASS_CODE
  (from CLASS entity)