MIS485 2

An.

ConceptualDataModeling-EERDiagramHandout.pdf

Home >Information Systems homework help >MIS485 2

MIS485: Capstone Project in MIS

2MGT 400 - Project Management

Textbook: Farrell, P. J., (2017). IT Capstone

Project (3rd Edition), Kendall Hunt

Publishing.

Structuring System Requirements: Conceptual Data

modeling- EER Diagram

Data Modeling for Structured Analysis

Conceptual Data Modeling

• Conceptual data modeling: a detailed model that captures the overall structure of data in an organization • Independent of any database management system

(DBMS) or other implementation considerations

Conceptual Data Modeling

 Representation of organizational data

 Purpose is to show rules about the meaning and interrelationships among data

 Enhanced/ Entity-Relationship (E-R) diagrams are commonly used to show how data are organized

 Main goal of conceptual data modeling is to create accurate E-R diagrams

 Methods such as interviewing, questionnaires, and JAD are used to collect information

 Consistency must be maintained among process flow, and data modeling descriptions

The Process of Conceptual Data Modeling • First step is to develop a data model for the system

being replaced

• Next, a new conceptual data model is built that includes all the requirements of the new system

• In the design stage, the conceptual data model is translated into a physical design

• Project repository links all design and data modeling steps performed during SDLC

Deliverables and Outcome

• Primary deliverable is the entity-relationship diagram or UML class diagram

• Entities (or classes) – categories of data, represented as rectangles

• Relationships (or associations) – lines between the entities

• There may be as many as 4 EER diagrams produced and analyzed during conceptual data modeling

› Covers just data needed in the project’s application

› EER diagram for system being replaced

› An EER diagram for the whole database from which the new application’s data are extracted

› An EER diagram for the whole database from which data for the application system being replaced are drawn

Gathering Information for Conceptual Data Modeling

• Two Perspectives: • Top-down

• Data model is derived from an intimate understanding of the business

• Bottom-up • Data model is derived by reviewing specifications and business

documents

Gathering Information for Conceptual Data Modeling (Continued)

• Requirements Determination Questions for Data Modeling: • What are subjects/objects of the business?

• Data entities and descriptions • What unique characteristics distinguish between

subjects/objects of the same type?

• Primary keys

Gathering Information for Conceptual Data Modeling (Continued)

• What characteristics describe each subject/object? • Attributes and secondary keys

• How do you use the data? • Security controls and user access privileges

• Who knows the meaning of the data? • Over what period of time are you interested in the data?

• Cardinality and time dimensions

Gathering Information for Conceptual Data Modeling (Continued)

• Are all instances of each object the same? • Supertypes, subtypes, and aggregations

• What events occur that imply associations between objects? • Relationships and cardinalities

• Are there special circumstances that affect the way events are handled? • Integrity rules, minimum and maximum cardinalities, time

dimensions

Introduction to Entity-Relationship Modeling

• Notation uses three main constructs • Data entities

• Relationships

• Attributes

• Entity-Relationship (E-R) Diagram • A detailed, logical, and graphical representation of the

entities, associations and data elements for an organization or business

Entity-Relationship (E-R) Modeling Key Terms

• Entity • A person, place, object, event or concept in the user environment

about which the organization wishes to maintain data

• Represented by a rectangle in E-R diagrams

• Entity Type • A collection of entities that share common properties or

characteristics

• Entity Instance • Single occurrence of an entity type

Entity-Relationship (E-R) Modeling Key Terms

• An entity type name should be: • A singular noun.

• Descriptive and specific to the organization.

• Concise.

• Event entity type should be named for the result of the event, not the activity or process of the event.

Entity-Relationship (E-R) Modeling (continued) Key Terms

• Attribute • A named property or characteristic of an entity that is of interest to an

organization.

• Naming an attribute: i.e. Vehicle_ID

• Place its name inside the rectangle for the associated entity in the E-R diagram.

• An attribute name is a noun and should be unique.

• To make an attribute name unique and for clarity, each attribute name should follow a standard format.

• Similar attributes of different entity types should use similar but distinguishing names.

Entity-Relationship (E-R) Modeling (continued) Key Terms

• Candidate Keys and Identifiers • Each entity type must have an attribute or set of attributes that

distinguishes one instance from other instances of the same type

 Candidate key • Attribute (or combination of attributes) that uniquely identifies each

instance of an entity type

Entity-Relationship (E-R) Modeling (continued) Key Terms

 Identifier (Key identifier) • A candidate key that has been selected as the unique

identifying characteristic for an entity type

• Selection rules for an identifier 1. Choose a candidate key that will not change its value

2. Choose a candidate key that will never be null

3. Avoid using intelligent keys

4. Consider substituting single value surrogate keys for large composite keys

Entity-Relationship (E-R) Modeling (continued) Key Terms

Multivalued Attribute • An attribute that may take on more than one

value for each entity instance

Weak Entity • Represented on E-R diagram in

• double-lined ellipse

Other Attribute Types

• Required attribute: an attribute that must have a value for every entity instance

• Optional attribute: an attribute that may not have a value for every entity instance

• Composite attribute: an attribute that has meaningful component parts

• Derived attribute: an attribute whose value can be computed from related attribute values

Entity-Relationship (E-R) Modeling (continued) Key Terms

• Relationship • An association between the instances of one or more

entity types that is of interest to the organization • Association indicates that an event has occurred or that

there is a natural link between entity types • Relationships are always labeled with verb phrases

Relationship type and

instances

(a) Relationship type

(Completes)

(b) Relationship

instances

Degree of Relationship

 Degree › Number of entity types that participate in a relationship

 Three Cases: › Unary relationship  A relationship between the instances of one entity type

› Binary relationship  A relationship between the instances of two entity types

› Ternary relationship  A simultaneous relationship among the instances of three entity

types  Not the same as three binary relationships

Cardinality

The number of instances of entity B that can be associated with each instance of entity A

Minimum Cardinality › The minimum number of instances of entity B that may be

associated with each instance of entity A

Maximum Cardinality › The maximum number of instances of entity B that may be

associated with each instance of entity A

Cardinality

Mandatory vs. Optional Cardinalities  Specifies whether an instance must exist or can be absent in the relationship

Examples of cardinality constraints

(a) Mandatory cardinalities

(b) One optional, one mandatory cardinality

Naming and Defining Relationships

• A relationship name is a verb phrase; avoid vague names.

• A relationship definition: • Explains what action is to be taken and possibly why it is

important.

• Gives examples to clarify the action.

Associative Entity

• An entity type that associates the instances of one or more entity types and contains attributes that are irregular to the relationship between those entity instances

An associative entity

Database Design

• Entity: A thing of independent existence on which you may wish to hold data on

• Example: an Employee, a Department

• Relationships: The relations between entities is defined as an interaction (connection) that exist between entities.

• Student is enrolled on a course;

• Course has students;

• Course consists of assessments;

• Unit belongs to a course;

The relations between entities in the real world, tell us how the database needs to be modeled.

Entity Relationship Notation

• Entity: is modeled as a square

• Relationship: is modeled as a link between the two entities.

Entity Relationship: Example

Student Major

Entity Relationship:

Student Major Enrolls

has

•Define the relationship:

•has to be a verb

•Recommended to be one word

•has to be clear

This reads like this: A student enrolls in a Major. A Major has students.

E-R: Cardinality

• Minimum: specifies the minimum number of instances of the related entity.

• Maximum: specifies the maximum number of instances of the related entity.

E-R: Cardinality

• Minimum specifies the minimum number of instances of the related entity.

• Maximum specifies the maximum number of instances of the related entity.

A B

Cardinatlit y

Optionality

Lets look at some examples:

• A Student can enroll in one Major at a time only.

• A Major can have one to many Students at a time.

• A Lecturer can teach in zero to many Majors at a time.

• A Major would have one or more Lectures teaching in that course at anytime.

Entity

Constraint

Constraints:

• one

• one to many

• zero to many

(Min One, Max One)

(Min One, Max Many)

(Min Zero, Max Many)

Lets see how it is implemented:

• A Student can enroll in one Major at a time only.

• A Major can have one to many Students at a time.

• A Lecturer can teach in zero to many Majors at a time.

• A Major would have one or more Lectures teaching at it at anytime.

Student Major

Lecturer

The relations between entities in the real world, tell us how the database needs to be modeled.

Check the following examples:

• What do you think the relationship constraint might be in the following relationships:

• Employee to a Department.

• Department to Employee

• Employee to Project

• Project to Employee

Answers to the example:

• Employee to a Department.

• Department to Employee

• Employee to Project

• Project to Employee

• Employee would belong to one and only one Department at a time.

• Department can have one to many Employees.

• An Employee can work on zero to many Projects.

• A Project can have one to many Employees.

This of the following examples:

• Employee would belong to one Department at a time.

• Department can have many Employees.

• An Employee can work on many Projects.

• A Project can have many Employees.

Employee Department

Project

Problem

Many to Many relationships problem!

• Many to many relationship cannot be directly implemented in a database

• So at E-R level, if we have a many to many relationship we need to resolve it!!

Employee

Project

The problem

101 Fadi Salmya B

102 Hamid Hawali C

103 Lana Egaela B

Employee(ID, Name, Address, Grade)

05 Financial 02/2014 20,000

06 Marketing 04/2014 10,000

07 IT 07/2014 50,000

Project (ID, Type, Date,Budget )

EmployeeProject

The solution

101 Fadi Salmya B

102 Hamid Hawali C

103 Lana Egaela B

Employee(ID, Name, Address, Grade)

05 Financial 02/2014 20,000

06 Marketing 04/2014 10,000

07 IT 07/2014 50,000

Project (ID, Type, Date,Budget )

Proj_Emp (ID, Proj_ID, Emp_ID)

1 05 101

2 06 101

3 07 101

4 05 103

5 06 103

EmployeeProject Proj_Emp

Summary of Conceptual Data Modeling with E-R Diagrams

• The purpose of E-R diagramming is to capture the richest possible understanding of the meaning of the data necessary for an information system or organization.

Simple Entity Relationship Diagram (ERD)

Customer Order

Inventory

Customer Order

Inventory

Problem

Simple Entity Relationship Diagram (ERD)

Customer Order

Inventory

Order-Product

Simple Entity Relationship Diagram (ERD)

Customer Order

Inventory

Ordered Product

Simple Entity Relationship Diagram (ERD)

Advance Entity Relationship Diagram (ERD)

Enhanced E-R diagram (EER)

• Define terms

• Understand use of supertype/subtype relationships

• Understand use of specialization and generalization techniques

• Specify completeness and disjointness constraints

• Develop supertype/subtype hierarchies for realistic business situations

Supertypes and Subtypes

• Enhanced ER model: extends original ER model with new modeling constructs

• Subtype: A subgrouping of the entities in an entity type that has attributes distinct from those in other subgroupings

• Supertype: A generic entity type that has a relationship with one or more subtypes

• Attribute Inheritance: • Subtype entities inherit values of all

attributes of the supertype

• An instance of a subtype is also an instance of the supertype

Employee supertype with three subtypes

All employee subtypes will have

employee number, name, address,

and date hired

Each employee subtype will also

have its own attributes

Different modeling tools may have different notation for the same modeling constructs.

Basic notation for supertype/subtype notation (cont.)

Relationships and Subtypes

• Relationships at the supertype level indicate that all subtypes will participate in the relationship

• The instances of a subtype may participate in a relationship unique to that subtype. In this situation, the relationship is shown at the subtype level

• Let us see it in an example:

Supertype/subtype relationships in a hospital

Generalization and Specialization

•Generalization: The process of defining a more general entity type from a set of more specialized entity types. BOTTOM-UP

•Specialization: The process of defining one or more subtypes of the supertype and forming supertype/subtype relationships. TOP-DOWN

Example of generalization

a) Three entity types: CAR, TRUCK, and MOTORCYCLE

All these types of vehicles have common attributes

Example of generalization (cont.)

So we put the

shared attributes

in a supertype

Note: no subtype for motorcycle, since it has no unique attributes

b) Generalization to VEHICLE supertype

Example of specialization

a) Entity type PART

Only applies to manufactured parts

Applies only to purchased parts

b) Specialization to MANUFACTURED PART and PURCHASED PART

Note: multivalued composite attribute was replaced by an associative

entity relationship to another entity

Created 2

subtypes

Example of specialization (cont.)

Constraints in Supertype/Subtype Relationships

•Completeness Constraints: Whether an instance of a supertype must also be a member of at least one subtype • Total Specialization Rule: Yes (double line) • Partial Specialization Rule: No (single line)

Examples of completeness constraints

a) Total specialization rule

b) Partial specialization rule

Examples of completeness constraints (cont.)

•Disjointness Constraints: Whether an instance of a supertype may simultaneously be a member of two (or more) subtypes • Disjoint Rule: An instance of the supertype can be only ONE of

the subtypes • Overlap Rule: An instance of the supertype could be more than

one of the subtypes

Constraints in Supertype/Subtype Relationships (Cont.)

a) Disjoint rule

Examples of disjointness constraints

b) Overlap rule

Examples of disjointness constraints (cont.)

• Subtype Discriminator: An attribute of the supertype whose values determine the target subtype(s)

• Disjoint – a simple attribute with alternative values to indicate the possible subtypes

• Overlapping – a composite attribute whose subparts pertain to different subtypes. Each subpart contains a Boolean value to indicate whether or not the instance belongs to the associated subtype

Constraints in Supertype/SUBTYPE RELATIONSHIPS (Cont.)

Introducing a subtype discriminator (disjoint rule)

Subtype discriminator (overlap rule)

Example of supertype/subtype hierarchy

PVF WebStore: Conceptual Data Modeling

• Conceptual data modeling for Internet applications is no different than the process followed for other types of applications

• Pine Valley Furniture WebStore • Four entity types defined

• Customer

• Inventory

• Order

• Shopping cart

Summary

• Process of Conceptual Data Modeling

• Deliverables

• Gathering information

• Entity-Relationship Modeling

• Entities

• Attributes

• Candidate keys and identifiers

• Multivalued attributes

• Degree of Relationship

• Cardinality

• Associative Entities

• Supertype/ subtype relationships

• Specialization and generalization techniques

• Completeness and disjointness constraints