Systems Analysis

1
Systems Analysis Design

• Conceptual Data Modeling

2
Conceptual Data Modeling

• Entity Relationship (E-R) Diagrams show how data
  is stored
• E-R Diagrams are the most common method for data
  modeling

3
Components of an E-R Diagram

• Entity

• Mandatory 1

• Mandatory Many (At least 1)

• Optional 1 (0 to 1 records)

• Optional Many (At least 0)

• Many with Maximum (1 to n)

4
Other Possible Symbols

• There are a number of other symbols that you can
  use to lay out specific data concepts without
  using tables and column names

• Identifier (Key)

• Attribute (Column Name)

• Multivalued Attribute

5
Example using Other Components
Student
6
Insert Sample Problem Here
7
Refined Example
Student
Course
8
Insert sample problem here
9
Detailed Diagram Components

• It is often useful to show actual table and
  column names on an E-R Diagram rather than just
  entity names.

Table Name
Primary Key Columns
Column Names
10
Drill Down 1 on ER Sample
11
Drill Down 2 on ER Sample
12
Example converted to Table View
Student
Course

• Student_ID
• Name
• Address
• Phone

• Course_ID
• Course_Name
• Course_Date

13
E-R Diagrams are Useful!

• This is an example of a quick-and-dirty E-R
  diagram that I build in Excel to help explain and
  keep track of changes that I was making to a
  database

14
Insert Sample Problem Here
15
Relationship Degrees - Unary

• Sometimes, a table will need to join back to
  itself.
• This is the case in most tree situations, as in
  any situation where you need to specify
  relationships between like parts
• This is called a Unary relationship

16
Using relationship tables with Unary Relationships

• Using extra tables to handle associations
  sometimes makes it easier

17
Relationship Degrees - Binary

• Most Common relationship relationship between
  instances of two entity types

Student
PC User Account
Campus Building
Classroom
Student
Course
18
Relationship Degrees - Ternary

• Relationship among instances of three entity types

Classroom
Student
Course
19
Associative Entities

• As mentioned above, Associative Entities are
  useful for creating associations between
  entities, and for holding additional information
  about the association.

20
Sample Problem?
21
Cardinality

• The minimum and/or maximum number of instances of
  Entity B that can be related to Entity A.
• Cardinality should be indicated with the line
  endings in the E-R Diagram, as indicated in slide
  3 of this presentation.

22
Business Rules

• Entity Integrity
• Each entity must have a unique identifier that is
  not null. For database tables, this is called a
  Key and can contain more than one column.
• Referential Integrity Constraints
• Rules concerning the relationships between entity
  types. Databases can automatically enforce these
  rules.
• Domains
• Rules about valid values for fields in an Entity

23
Sample Problem Order Form

• Turn the following order form into an E-R Diagram

24
Problem General Solution
Customer
Order Item
5
Stock Item
25
Problem Table Solution
26
Problem Normalized Solution
27
REVIEW E-R Diagram Components

• Must be 1 Record

• At least 1 Record

• Either 0 or 1 Records

• Any number 0 to many

• From 1 t 4 records

• Must be 1 (Poorly drawn)