Data Modeling 1

1
Data Modeling 1

• 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
Data Model and Data Modeling

• Model an abstraction of a real-world object or
  event
• Useful in understanding complexities of the
  real-world environment
• Data model
• Relatively simple representations of complex
  real-world data structures
• Data modeling is iterative and progressive process

4
Data Model by Access
5
Data Model by Peter Chen Notation (first -
original)
6
Data modeling

• The data modeling revolves around discovering and
  analyzing organizational and users data
  requirements based on business rules.
• Identify what data is important
• Identify 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.

7
Data Model Basic Building Blocks

• Entity anything about which data are to be
  collected and stored
• Attribute a characteristic of an entity
• Relationship describes an association among
  entities
• One-to-many (1M) relationship
• Many-to-many (MN or MM) relationship
• One-to-one (11) relationship
• Constraint a restriction placed on the data

8
The Importance of Data Model

• Blue print documentation
• Facilitate interaction among the managers, the
  designer, and the end user
• Effective Communication Tool
• User involvement
• Independence from a particular DBMS

9
Business Rules

• Descriptions of policies, procedures, or
  principles within a specific organization
• Use for discovering and analyzing organizational
  and users data requirements for the data model
• Use for describing characteristics of data
• Allow designer to understand business processes
• Allow designer to develop appropriate
  relationship participation rules and constraints

10
Discovering Business Rules

• Sources of business rules
• Top management (policy makers) and managers
• Written documentation
• Procedures
• Standards
• Operations manuals
• Direct interviews with end users

11
Translating Business Rules into Data Model
Components

• Nouns translate into entities
• Verbs translate into relationships among entities
• Relationships are bidirectional
• Two 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?
• Example relationship between student and class
• In how many classes can one student enroll?
  many classes
• How many students can enroll in one class? many
  students

12
Business Rules Example 1

• A professor can teach many classes and each class
  is taught by one professor.
• A professor can advise many students and each
  student is advised by one professor.

13
Business Rules Example 2

• Each sales representative writes many invoices
  and each invoice is written by one sales
  representative.
• Each sales representative is assigned to many
  department and each department has one at most
  one sales representative.
• Each customer can generate many invoices and each
  invoice is generated by one customer.

14
Entity Relationship diagram (ERD)

• Data modeling methodology
• Developed by Peter Chen (1976).
• Entity anything about which data are to be
  collected and stored
• 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)

15
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

16
Most popular ERD Notation (very minor differences
with our textbook)
17
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
18
Entity Instance (cont)
19
Entity Type and Entity Instances
20
Attributes

• Describe characteristics of an entity
• Entity Employee
• Attributes
• Employee-Name
• Address (composite)
• Phone Extension
• Date-Of-Hire
• Job-Skill-Code
• Salary

21
Classes of attributes

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

22
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

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

24
Single-valued attribute

• can have only a single (atomic) value.
• Examples
• A person can have only one social security
  number.
• A manufactured part can have only one serial
  number.
• A single-valued attribute is not necessarily a
  simple attribute.
• Part No CA-08-02-189935
• Location CA, Factory08, shift 02, part
  189935

25
Multi-valued attributes

• can have many values.
• Examples
• A person may have several college degrees.
• A household may have several phones with
  different numbers
• A car color

26
Example - Movie Database

• Entity
• Movie Star
• Attributes
• SS 123-45-6789 (single-valued)
• Cell Phone [phone]
  (multi-valued)
• Name Harrison Ford (composite)
• Address 123 Main Str., LA, CA (composite)
• Birthdate 1-1-50 (simple)
• Age 50 (derived)

27
How to find entities?

• Entity
• A fundamental thing of relevance to the
  organization about which data may be kept
• people, places, objects, events.
• Tangible customer, product
• Intangible order, invoice
• look for nouns (beginner) BUT a proper noun is
  not a good candidate.

28
How to find attributes?

• Attribute
• property or characteristic of an entity
• A descriptor whose values are associated with
  individual entities of a specific entity type
• look for characteristics of entity

29
(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)

30
Practice

• Try Practice 1 4 from Data Modeling Practice
  slide from week 6 class website.