IS 421 Information Systems Analysis

1
IS 421Information Systems Analysis

• James Nowotarski
• 14 October 2002

2
Todays Objectives

• Recap basics of data modeling
• Understand more advanced data modeling concepts
• Special types of entities
• Foreign keys
• Normalization (time permitting, intro only)

3
Course Map
Contents 1. Introduction Planning Phase 2.
Project Initiation 3. Project Management Analysi
s Phase 4. Systems Analysis 5. Gathering
Information 6. Process Modeling 7. Data Modeling
1
2
3
4
6
7
8
9
10
11
5
Week
Core Exam Review
Assignments Quizzes Final
4
Todays agenda

• Topic Duration
• Recap Last Week 20-30 minutes
• Homework 30-40 minutes
• Break 15 minutes
• Foreign keys 30 minutes
• Special entity types 30 minutes
• Data Modeling Group Exercises 45 minutes
• Assignment 3 Intro 10 minutes

5
SDLC

• The systems development life cycle (SDLC) is a
  description of the phases of an information system

Implementation
6
Data Modeling in the Analysis Phase
From Planning Phase
Develop Concept for To-Be System
System request Feasibility analysis Workplan
. . .
To Design Phase
Deliverables
Analysis Plan
Functional Requirements Quality Requirements
Data Model Process Model
System Proposal
System Concept
7
Todays agenda

• Topic Duration
• Recap Last Week 20-30 minutes
• Homework 30-40 minutes
• Break 15 minutes
• Foreign keys 30 minutes
• Special entity types 30 minutes
• Data Modeling Group Exercises 45 minutes
• Assignment 3 Intro 10 minutes

8
Key Definitions

• A data model is a
• Formal representation of the data to be used for
  a business system.
• A data model should illustrate
• The people, places and things about which data is
  collected,
• And how they are related to each other

9
Key Definitions

• A logical data model shows the logical
  organization of data
• Without regard for how data gets stored, created
  or manipulated
• A physical data model shows how the data will
  physically be stored in the database (secondary
  memory).
• E.g. Oracle may store a customer table in several
  files (cust1.dbf, cust2.dbf) that span several
  hard drives.

10
Data Modeling
A logical data model deliverable includes an ERD
and descriptions of entities, attributes, and
relationships
Components of a Logical Data Model

• Entity-relationship diagram (ERD)
• Entity descriptions
• Attribute descriptions
• Relationship descriptions
• Domain descriptions (not covered here)

11
Entities and Instances
Instances are occurrences of an entity
12
Examples of Attributes

• Entity Person
• Attributes
• first_name
• last_name
• eye_color
• date_of_birth
• address
• Entity Classroom
• Attributes
• room_no
• max_capacity

13
Depicting Entities, Attributes and Identifiers
Entity name
Identifier
Or, use cd_id (PK)
Attributes
14
Relationships

• Relationships represent connections, links, or
  associations between entities
• e.g., Patient-Appointment
• Relationships have some important properties
• Names, that should be active verbs
• (A patient schedules appointments)
• Cardinality
• Modality.

15
Cardinality

• Cardinality refers to the number of times
  instances in one entity can be related to
  instances in another entity
• One instance in an entity refers to one and only
  one instance in the related entity (11)
• One instance in an entity refers to one or more
  instances in the related entity (1M)
• One or more instances in an entity refer to one
  or more instances in the related entity (MM)

16
Modality

• Modality refers to the minimum number of times
  that an instance in one entity can be related to
  an instance in another entity
• One means that an instance in the related entity
  must exist for an instance in another entity to
  be valid
• Zero means that no instance in the related entity
  is necessary for an instance in another entity to
  be valid

17
Meanings of the Crows Feet
Cardinality
Min Max
0 1 1 1 0 Many 1 Many
18
The Entity-Relationship Diagram (ERD)
19
An ERD Example
20
What Is an ERD?

• A picture of the logical data model,
• i.e., an ERD shows the information created,
  stored, and used by a business system.

21
An ERD Example
22
Todays agenda

• Topic Duration
• Recap Last Week 20-30 minutes
• Homework 30-40 minutes
• Break 15 minutes
• Foreign keys 30 minutes
• Special entity types 30 minutes
• Data Modeling Group Exercises 45 minutes
• Assignment 3 Intro 10 minutes

23
Homework

• Ch. 7  Exercises A, D-3, D-4, H, Minicase 2

24
Todays agenda

• Topic Duration
• Recap Last Week 20-30 minutes
• Homework 30-40 minutes
• Break 15 minutes
• Foreign keys 30 minutes
• Special entity types 30 minutes
• Data Modeling Group Exercises 45 minutes
• Assignment 3 Intro 10 minutes

25
Todays agenda

• Topic Duration
• Recap Last Week 20-30 minutes
• Homework 30-40 minutes
• Break 15 minutes
• Foreign keys 30 minutes
• Special entity types 30 minutes
• Data Modeling Group Exercises 45 minutes
• Assignment 3 Intro 10 minutes

26
Still More Definitions

• A foreign key is all of these things
• a primary key of one entity (sometimes called
  parent entity),
• duplicated in another entity (child entity)
• provides a common linkage between the two entities

27
Foreign Key Example

• Customer(cust_id (PK), cust_name)
• Order (ord_no (PK), cust_id (FK), ord_date)

Customers (Parent)
Orders (Child)
cust_id cust_name 100 Slick Willy,
Inc. 200 George_W, Co. 300 Gore, Ltd.
ord no cust_id ord_date 2100
200 13-Sep-2000 2101 100 14-Nov-2000 2102
100 23-Dec-2000 2103 100 24-Dec-2000
PK
FK
Cust_id
A foreign key is a primary key of one entity that
is duplicated in another entity to provide a
common linkage between entities. Why use them?
28
Todays agenda

• Topic Duration
• Recap Last Week 20-30 minutes
• Homework 30-40 minutes
• Break 15 minutes
• Foreign keys 30 minutes
• Special entity types 30 minutes
• Data Modeling Group Exercises 45 minutes
• Assignment 3 Intro 10 minutes

29
Entity Types

1. Independent
2. Dependent
3. Intersection

30
Independent Entities

• An independent entity exists without the help of
  another entity
• Common entities such as student, professors,
  customers, products
• The identifier is created by the entitys own
  attributes
• Usually on the 1 side of a relationship
• a.k.a. fundamental entity (in Visual Analyst,
  e.g.) or strong entity

31
Dependent Entities

• Alternatively, a dependent entity cannot exist
  without the help of another entity
• Special entities such as emp_dependent (needs an
  employee to exist)
• The identifier is usually based on another
  entitys attributes (emp_ssn dep_ssn)
• Usually on the M side of a relationship
• a.k.a. attributive entity (in Visual Analyst,
  e.g.) or weak entity

32
Intersection entities

• An intersection entity exists based on the
  relationship between two entities.
• They have attributes that are peculiar to the
  relationship between those entity instances, not
  the individual entities themselves
• They are created to store information about two
  entities sharing an MM relationship
• a.k.a. associative entities, gerunds

33
Intersection Entity Example
A student may take many classes. A class may have
many students. Where are grades stored?
Student
Class
Roster
Student
Class
An instance in the student entity is related to
_____ instances in the class entity. An instance
in the class entity is related to _____ instances
in the student entity.
34
An ERD Example CDs Relationships Expanded
35
An Intersection Entity Example
A CD may be a part of many orders. An order may
contain many CDs. The CD-order relationship is
MM. Where do you store quantity of CDs on an
order?
36
Adding Intersection Entities

1. Create an intersection entity (line item).

• Move the Ms are adjacent to the intersection
  entity.

3. The 1 side goes on the original entities.
37
MN to 1Ms

• Rule The M always go to the intersection
  entity. Why?

38
Creating An Entity-Relationship Diagram
39
Steps in Building Data Models

• Review existing data models
• Define entities
• Independent
• Dependent, including Intersection entities
• Define attributes and keys (primary, foreign)
• Define relationships
• Finalize ERD
• Normalize
• Integrate data models as required
• Verify completeness of the data model
• Validate the data model
• With users
• With the enterprises data administrator

40
Design Guidelines

• Best practices rather than rigid rules
• Entities should have many instances (dont
  include fixed items such as stationery headings)
• Avoid unnecessary attributes (outside the scope
  of your system)
• Apply correct cardinality and modality
• Labels reflect common business terms
• Assumptions should be clearly stated

41
Summary

• The ERD is the most common technique for drawing
  data models. The building blocks of the ERD are
• Entities (nouns), describe people, places, or
  things
• Attributes (nouns), capture information about the
  entity
• Relationships (active verb sentences) associate
  data across entities they have cardinality and
  modality

42
Todays agenda

• Topic Duration
• Recap Last Week 20-30 minutes
• Homework 30-40 minutes
• Break 15 minutes
• Foreign keys 30 minutes
• Special entity types 30 minutes
• Data Modeling Group Exercises 45 minutes
• Assignment 3 Intro 10 minutes

43
Group Exercise

• Textbook, p.207, Your Turn 7-2
• Show
• Entities
• Attributes
• Identifier(s)
• Relationships
• Cardinality
• Modality
• 15 minutes

44
Todays agenda

• Topic Duration
• Recap Last Week 20-30 minutes
• Homework 30-40 minutes
• Break 15 minutes
• Foreign keys 30 minutes
• Special entity types 30 minutes
• Data Modeling Group Exercises 45 minutes
• Assignment 3 Intro 10 minutes

45
Some final points
46
Why Normalize?

• Prevent inconsistencies
• Prevent update anomalies

47
Normalization Process
1NF No repeating groups
2NF 1NF no partial dependencies (non-key
attribute dependent on portion of primary key)
3NF 2NF no transitive dependencies (non-key
attribute dependent on another non-key attribute)
48
Metaphors
ER Model Relational Model Database Spreadsheet
Entity Relation Table, File Table
Instance Tuple Record Row
Attribute Attribute Field Column
Identifier Key Key Key
49
Identifiers

• An identifier should have the following
  characteristics
• Its value should not change over the life of each
  instance
• Its value should never be null or empty
• It should be composed of the minimal number
  (preferably one) of attributes required to ensure
  uniqueness