systems analysis design l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Systems Analysis & Design PowerPoint Presentation
Download Presentation
Systems Analysis & Design

Loading in 2 Seconds...

play fullscreen
1 / 62

Systems Analysis & Design - PowerPoint PPT Presentation


  • 302 Views
  • Uploaded on

CSUN Information Systems. Systems Analysis & Design. http://www.csun.edu/~dn58412/IS431/IS431_SP14.htm. Data Modeling. IS 431: Lecture 5. Data Modeling. Elements of Entity Relationship Diagram (ERD) Relational Data Model. Data Modeling.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Systems Analysis & Design' - oshin


Download Now An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
systems analysis design

CSUN Information Systems

Systems Analysis & Design

http://www.csun.edu/~dn58412/IS431/IS431_SP14.htm

Data

Modeling

IS 431: Lecture 5

data modeling
Data Modeling
  • Elements of Entity Relationship Diagram (ERD)
  • Relational Data Model

IS 431 : Lecture 5

data modeling3
Data Modeling

Data Modeling (database modeling, information modeling) is a technique for organizing and documenting a system’s data in a model.

Entity Relationship Diagram (ERD) depicts data in terms of the entities and relationships described by the data.

IS 431 : Lecture 5

from data model to db implementation
From Data Model to DB Implementation
  • ERD: a conceptual model of data entities (things of interest) , their attributes (characteristics of interest), and their relationships (with other things) in an information system (technical independent).(Analysis)
  • Relational Data Model: a blueprint for implementation of a conceptual data model (ERD) in relational database environment (software independent)(Design)
  • MS Access Relationship Window: a graph showing how a data model is implemented with Microsoft Access (a specific DBMS software)(Implementation)

IS 431 : Lecture 5

entity relationship diagrams
Entity-Relationship Diagrams
  • Database = data + relationship
  • ERD is used to model data and their relationship
  • ERD is a graphical representation of a conceptual data model
  • ERD is resource independent: it does not commit to any particular database environment.

IS 431 : Lecture 5

entities
Entities
  • Entityis a group of attributes corresponding to the same conceptual object about which we need to capture and store data
    • objects, persons, places, events concepts whose existence is not dependent on many other entities
  • Entity is a set of occurrences (instances) of the object that it represents
  • Entity must have a unique name (a singular noun), unique identifier, and at least one attribute (the identifier itself)

IS 431 : Lecture 5

entities examples
Entities: Examples
  • Persons: agency, contractor, customer, department, division, employee, instructor, student, supplier.
  • Places: sales region, building, room, branch office, campus.
  • Objects: book, machine, part, product, raw material, software license, software package, tool, vehicle model, vehicle.
  • Events: application, award, cancellation, class, flight, invoice, order, registration, renewal, requisition, reservation, sale, trip.
  • Concepts: account, block of time, bond, course, fund, qualification, stock.

IS 431 : Lecture 5

entity instance example
Entity Instance: Example

Entity instance – a single occurrence of an entity.

Attributes

Entity

Instances

IS 431 : Lecture 5

entities attributes
Entities: Attributes

An attribute is a descriptive property or characteristic of interest of an entity. Also called element, property, and field.

The data type for an attribute defines what type of data can be stored in that attribute.

The domain of an attribute defines what values an attribute can legitimately take on.

The default value for an attribute is the value that will be recorded if not specified by the user.

IS 431 : Lecture 5

data type
Data Type

IS 431 : Lecture 5

data domains
Data Domains

IS 431 : Lecture 5

entities identification
Entities: Identification
  • A key is an attribute, or a group of attributes, that assumes a unique value for each entity instance.
  • A group of attributes that uniquely identifies an instance of an entity is called a composite(or concatenated) key.
  • A candidate key is a “candidate to become the primary key” of instances of an entity. (StudentID, SSN, DriverLicenseNo)
  • A primary key(identifier) is that candidate key that will most commonly be used to uniquely identify a single entity instance. (StudentID)
  • Any candidate key that is not selected to become the primary key is called an alternate key. (SSN, DriverLicenseNo)
  • A secondary key is an attribute whose values divide all entity instances into useful subgroups/sub-criteria. (Major, Gender, etc)

IS 431 : Lecture 5

entities13
Entities ...

ENTITY NAME

- entity id

- attribute 1

- attribute 2

- …………..

- attribute n

CUSTOMER

- Customer_ID

- Cust_Name

- Cust_Address

- Cust_Phone

IS 431 : Lecture 5

relationships
Relationships
  • ARelationshipdocuments an association between one, two, or more entities
  • It must have a name (and may carry data)
  • Degree of Relationship (number of entities)
  • Cardinalities of Relationship (number of instances/members)

IS 431 : Lecture 5

relationships degree
Relationships: Degree
  • Degree of Relationship defines how many entities are involved in a relationship:
    • Recursive (Unary)
    • Binary
    • Ternary
    • ….

IS 431 : Lecture 5

relationships cardinalities
Relationships: Cardinalities
  • Cardinalitiesdocument how many occurrences/members of one entity can relate to a single occurrence/member of another entity in a relationship.
  • Max / Min number of occurrences
  • Reflect business policies or general business practices (e.g., how many classes a student can take, how many students a class can hold).

(15, 35)

(1, 5)

Student

Take

Class

IS 431 : Lecture 5

cardinalities
Cardinalities ...

One-to-One (1:1) Relationship

1

1

Sales

Pay

Cash

Collections

Ex: Cash Sales

One-to-Many (1:M) Relationship

1

M

Sales

Pay

Cash

Collections

Ex: Installment Payments

IS 431 : Lecture 5

cardinalities18
Cardinalities ...

Many-to-One (M:1) Relationship

M

1

Sales

Pay

Cash

Collections

Ex: Pay many credit purchases in full

Many-to-Many (M:N) Relationship

M

N

Sales

Pay

Cash

Collections

Ex:Pay credit purchases with partial payments over some months

IS 431 : Lecture 5

cardinalities19
Cardinalities ...
  • Maximum / Minimum Cardinality

(0,E)

(1,S)

Employee

Possess

Skill

An employee must have minimum one skill A particular skill may not be possessed by any employee

(1,E)

(0,P)

Employee

Belong

Project

A project must have minimum one employee A particular employee may not belong to any project

IS 431 : Lecture 5

relationships20
Relationships ...
  • Recursive relationships involve only one entity (occurrences in the same entity)

N

N

ENTITY

- entityID

- attribute 1

- attribute 2

TOWN

-TownID

-Town_Name

Relationship Name

Attrib 1, Attrib 2

TRAVEL

Distance

M

M

IS 431 : Lecture 5

relationships21
Relationships ...
  • Binary relationship

ENTITY 1

-entity1_ID

-attribute 11

-attribute 12

ENTITY 2

-entity2_ID

-attribute 21

-attribute 22

Relationship Name

Attrib 1, Attrib 2

M

N

EMPLOYEE

- Emp_ID

- Emp_Name

- Emp_Title

PROJECT

- Project_ID

- Proj_Name

- Proj_Due

Manage

Date

M

N

IS 431 : Lecture 5

relationships22
Relationships ...
  • Ternary relationship

EMPLOYEE

- EmpID

- Emp_Name

- Emp_Title

PROJECT

- ProjectID

- Proj_Name

- Proj_Due

Assign

Date

N

M

P

TASK

- TaskID

- TaskName

IS 431 : Lecture 5

logical data modeling stages
Logical Data Modeling Stages
  • Context Data model
    • To establish project scope
  • Key-base data model
    • Eliminate nonspecific relationships
    • Add associative entities
    • Include primary and alternate keys
    • Precise cardinalities
  • Fully attributed data model
    • All remaining attributes
    • Subsetting criteria
  • Normalized data model

IS 431 : Lecture 5

what is a good data model
What is a Good Data Model?
  • A good data model is simple.
    • Data attributes that describe any given entity should describe only that entity.
    • Each attribute of an entity instance can have only one value.
  • A good data model is essentially non redundant.
    • Each data attribute, other than foreign keys, describes at most one entity.
    • Look for the same attribute recorded more than once under different names.
  • A good data model should be flexible and adaptable to future needs.

IS 431 : Lecture 5

building erd
Building ERD
  • Identify entities of interest (Use REAL framework – Resources/Event/Agents/Locations – more in Lecture 5B) – (Top Down)
  • Identify all the attributes with sufficient details (context specific)
  • Assign attributes to entities – (Bottom Up)
  • Identify degrees of relationships between entities (context specific)
  • Complete the relationships with cardinalities (context specific)
  • Build the model

IS 431 : Lecture 5

rules in erd building
Rules in ERD Building
  • Each entity must have a name
  • Each entity must have an identifier
  • An occurrence itselfcannotbe an entity (a constant or a table with one unique record?)
  • Each relationship must have a name (may or may not carry data)
  • Reasonable cardinalities (context specific)

IS 431 : Lecture 5

slide27

Alternative ERD Notations

Attribute 1

Attribute 2

Attribute 1

Attribute 2

relates to

Entity 2

Attribute 3

Entity 1

is related to

Attribute 3

Attribute 4

Attribute 4

Attribute 5

IS 431 : Lecture 5

slide28

Alternative ERD Notations

1

(0, 1)

Entity

Entity

Entity

0

(1, 1)

1

Entity

Entity

Entity

1

(0 , *)

M

Entity

Entity

Entity

0

(1 , *)

M

Entity

Entity

Entity

1

IS 431 : Lecture 5

associative entity
Associative Entity
  • An associative entity is an entity that inherits its primary key from more than one other entity (called parents).
  • Each part of that composite (concatenated) key points to one and only one instance of each of the connecting entities.
  • Represent an M:N relationship carrying data

EMPLOYEE

- Emp_ID

- Emp_Name

- Emp_Title

PROJECT

- Project_ID

- Proj_Name

- Proj_Due

ASSIGN

Date

M

N

1

1

IS 431 : Lecture 5

m n relationship vs associative entity
M:N Relationship vs. Associative Entity

EMPLOYEE

- Emp_ID

- Emp_Name

- Emp_Title

PROJECT

- Project_ID

- Proj_Name

- Proj_Due

ASSIGN

Date

M

N

EMPLOYEE

- Emp_ID

- Emp_Name

- Emp_Title

PROJECT

- Project_ID

- Proj_Name

- Proj_Due

ASSIGNMENT

- Emp_ID

- Proj_ID

- Date

1

M

N

1

IS 431 : Lecture 5

foreign keys in relational database
Foreign Keys in Relational Database
  • A foreign key (FK) in Entity E1(CustID in ORDER) is a primary key of another Entity E2(CustID in CUSTOMER), which is used to identify (link) a 1:M relationship between E1 and E2 (CUSTOMER and ORDER).
  • Foreign key is made on the many side (CUSTOMER has many ORDERS, therefore ORDER carries CustID as FK to show which Customer places that Order)

IS 431 : Lecture 5

foreign key
Foreign Key

CUSTOMER

ORDER

1:M Relationship

CUSTOMER

CustomerID

ORDER

OrderID

CustomerID

Primary Key

Foreign Key

IS 431 : Lecture 5

foreign keys in relational database33
Foreign Keys in Relational Database. . .
  • In M:N relationship, the associative/junction table with a composite key will be used to capture the relationship.
    • ORDER involved many PRODUCTS, PRODUCT involved in many ORDERS. Composite key ProductID-OrderID for LINE ITEM to indicate which product involves in which sales
  • Each part of the composite key serves like a foreign key.
  • Sometimes, a “surrogate” key (RecordNo) is used as primary key to simplify the identification of record.

IS 431 : Lecture 5

composite key
Composite Key

ORDER

PRODUCT

M:N Relationship

ORDER

OrderID

PRODUCT

ProductID

Primary Key

LINE_ITEM

RecordNo

OrderID

ProductID

Composite Key

JUNCTION TABLE

IS 431 : Lecture 5

slide35

ERD Case Studies

  • ERD 1: Group Consultants Database
  • ERD 2: Video Rental Database
  • ERD 3: Health Club Database
  • ERD 4: Music Store Database

to be discussed in class

IS 431 : Lecture 5

convert erd to relational data model
Convert ERD to Relational Data Model
  • Legend
    • E1, E2, E3 , … represent entities
    • a1, a2, a3, …, b1, b2, b3, … represent entity attributes
    • R represents a relationship
    • r represents one or many attributes that can be carried by a relationship

IS 431 : Lecture 5

slide37

Convert ERD to Relational Data Model …

  • Pattern #1

E1

- a1

- a2

E1( a1, a2 )

TASK

- TaskID

- TaskName

TASK( TaskID , TaskName )

IS 431 : Lecture 5

slide38

Convert ERD to Relational Data Model …

  • Pattern #2

1

E1

- a1

- a2

E1( a1, a2 )

R( a1, a1’, r)

R

r

1

1

INDIVIDUAL

- SSN

- Name

INDIVIDUAL ( SSN , Name)

MARRY ( SSN , SSN’, Date)

MARRY

Date

1

Husband

Wife

IS 431 : Lecture 5

slide39

Convert ERD to Relational Data Model …

  • Pattern #3

1

E1

- a1

- a2

E1( a1, a2 )

R( a1’, a1 )

R

M

1

INDIVIDUAL

- SSN

- Name

INDIVIDUAL ( SSN , Name)

LEAD ( SSN’ , SSN )

LEAD

M

Member

Leader

IS 431 : Lecture 5

slide40

Convert ERD to Relational Data Model …

  • Pattern #4

M

E1

- a1

- a2

E1( a1, a2 )

R( a1, a1’, r)

R

r

N

M

TOWN

- TownID

- Name

TOWN ( TownID , Name)

TOUR ( TownID, TownID’ , Date )

Tour

Date

N

IS 431 : Lecture 5

slide41

Convert ERD to Relational Data Model …

  • Pattern #5

E1( a1, a2 ) E2( b1, b2 , b3 , a1)

E1

- a1

- a2

E2

- b1

- b2

- b3

R

1

1

OR

E1( a1, a2 , b1 ) E2( b1, b2 , b3 )

PROJECT

- ProID

- ProName

- ProDue

EMPLOYEE

- EmpID

- EmpName

1

1

MANAGE

EMPLOYEE(EmpID, EmpName) PROJECT(ProID, ProName, ProDue, EmpID)

OR

EMPLOYEE(EmpID, EmpName, ProID) PROJECT(ProID, ProName, ProDue )

IS 431 : Lecture 5

slide42

Convert ERD to Relational Data Model …

  • Pattern #6

E1

- a1

- a2

E2

- b1

- b2

- b3

E1( a1, a2 ) E2( b1, b2 , b3 , a1)

R

M

1

CUSTOMER

- CustID

- CustName

PURCHASE

- PurchID

- Item

- Quantity

1

M

INVOLVE

CUSTOMER (CustID, CustName) PURCHASE(PurchID, Item, Quantity, CustID)

IS 431 : Lecture 5

slide43

Convert ERD to Relational Data Model …

  • Pattern #7

E1

- a1

- a2

E2

- b1

- b2

- b3

E1( a1, a2 ) E2( b1, b2 , b3) R( a1, b1, r)

R

N

M

r

CUSTOMER

- CustID

- CustName

SALESREP

- RepID

- RepName

M

N

SALE

Date

CUSTOMER (CustID, CustName) SALESREP (RepID, RepName) SALE(CustID, RepID, Date)

IS 431 : Lecture 5

slide44

Convert ERD to Relational Data Model …

  • Pattern #8

E1

- a1

- a2

E2

- b1

- b2

- b3

R

N

1

r

P

E3

- c1

- c2

- c3

E1( a1, a2 ) E2( b1, b2 , b3) E3( c1, c2 , c3 ) R( b1, c1 , a1, r)

IS 431 : Lecture 5

slide45

Convert ERD to Relational Data Model …

  • Pattern #8 (example)

EXECUTIVE

- ExecID

- ExecName

DEPT

- DeptID

- DeptName

RESPONSIBLE

N

1

AppointDate

P

PROJECT

- ProjectID

EXECUTIVE( ExecID, ExecName) DEPT( DeptID, DeptName) PROJECT (ProjectID) RESPONSIBLE (DeptID, ProjectID, ExecID, AppointDate )

IS 431 : Lecture 5

slide46

Convert ERD to Relational Data Model …

  • Pattern #9

E1

- a1

- a2

E2

- b1

- b2

- b3

R

N

M

r

P

E3

- c1

- c2

- c3

E1( a1, a2 ) E2( b1, b2 , b3) E3( c1, c2 , c3 ) R(a1, b1, c1 , r)

IS 431 : Lecture 5

slide47

Convert ERD to Relational Data Model …

  • Pattern #9 (example)

EMPLOYEE

- EmpID

- EmpName

TASK

- TaskID

ASSIGN

N

M

Date

P

PROJECT

- ProjectID

EMPLOYEE( EmpID, EmpName ) TASK( TaskID) PROJECT( ProjectID) ASSIGN(EmpID, TaskID, ProjectID, Date)

IS 431 : Lecture 5

generalization
Generalization
  • Generalization is a technique wherein the attributes that are common to several types of an entity are grouped into their own entity, called a supertype.
  • An entity supertype is an entity whose instances store attributes that are common to one or more entity subtypes.
  • An entity subtype is an entity whose instances inherit some common attributes from an entity supertype and then add other attributes that are unique to an instance of the subtype.

IS 431 : Lecture 5

type subtypes
Type & Subtypes

Employee

Name DOB SSN DateHire

Executive

Foreperson

Worker

Rank Wage Rate

Position Location DateAppoint Salary

Factory Station DateAppointWage Rate

IS 431 : Lecture 5

generalization hierarchy
Generalization Hierarchy

IS 431 : Lecture 5

data analysis normalization
Data Analysis & Normalization

Data analysis is a process that prepares a data model for implementation as a simple, non-redundant, flexible, and adaptable database. The specific technique is called normalization.

Normalization is a technique to organize data attributes such that they are grouped to form non-redundant, stable, flexible, and adaptive entities.

IS 431 : Lecture 5

normalization in latin
Normalization (in Latin)

First normal form (1NF) – an entity whose attributes have no more than one value for a single instance of that entity

  • Any attributes that can have multiple values actually describe a separate entity, possibly an entity and relationship.

Second normal form (2NF) – an entity whose nonprimary-key attributes are dependent on the full primary key.

  • Any nonkey attributes that are dependent on only part of the primary key should be moved to any entity where that partial key is actually the full key. This may require creating a new entity and relationship on the model.

Third normal form (3NF) – an entity whose nonprimary-key attributes are not dependent on any other non-primary key attributes.

  • Any nonkey attributes that are dependent on other nonkey attributes must be moved or deleted. Again, new entities and relationships may have to be added to the data model.

IS 431 : Lecture 5

normalization in plain english
Normalization (in Plain English !!!)
  • First normal form (1NF) :
    • No repeating group of a same attribute (multi-valued attribute)
    • If not: create a new record / entity for this group.
  • Second normal form (2NF)
    • Attributes should depend on the whole (composite) key, not part of it (partial functional dependency).
    • If not: create a new entity for these partial depended attributes
  • Third normal form (3NF)
    • Attributes should depend on the (primary) key only, not on each other (transitive dependency)
    • If not: create new entity for these partial depended attributes

IS 431 : Lecture 5

un normalized relation
Un-normalized Relation

Observation: Repeating groups / multi-value attributes !!!

IS 431 : Lecture 5

relation in 1nf
Relation in 1NF

Observation: Attributes still depend on a part of the key !!!

IS 431 : Lecture 5

relations in 2nf
Relations in 2NF

Observation: Attributes still depend on a non-key attribute !!!

IS 431 : Lecture 5

relations in 3nf
Relations in 3NF

Observation: Now each table stores data about one thing only.

IS 431 : Lecture 5

data dictionary

EMPLOYEE

Attributes

Type

Size

Description

Authorization

EmpID

Numeric

6

Identifier

HR Manager

EmpFirstName

Text

10

Employee First Name

HR Manager

EmpLastName

Text

10

Employee Last Name

HR Manager

Address

Text

50

Employee Address

HR Manager

City

Text

10

Employee City

HR Manager

State

Text

2

Employee Last Name

HR Manager

Zip

Text

XXXXX

Employee Last Name

HR Manager

Phone

Text

XXX-XXX-XXXX

Employee Last Name

HR Manager

Date Hired

Date

MM/DD/YY

Date Hired Employee

HR Manager

Position

Text

15

Position of Employee

HR Manager

EXPENSES

Attributes

Types

Size

Description

Authorization

EntryNumber

Numeric

6

Identifier

Project Manager

EntryDate

Date

MM/DD/YY

Date of Entry

Project Manager

HoursWorked

Numeric

3

Hours on Task

Project Manager

HotelExpense

Currency

4

Fund Spent on Hotel

Project Manager

TravelExpense

Currency

4

Fund Spent on Travel

Project Manager

FoodExpense

Currency

4

Fund Spent on Food

Project Manager

Approved

Y/N

1

Approved / Not Yet

Project Manager

Data Dictionary

IS 431 : Lecture 5

data to process matrix
Data to Process Matrix

IS 431 : Lecture 4

data to location crud matrix
Data-to-LocationCRUD Matrix

IS 431 : Lecture 5