BIS 360 – Lecture Six (Part 2)

BIS 360 – Lecture Six (Part 2) Conceptual Data Modeling (Chapter 10 and partial Chapter 12)

1. Determine system requirements (ch. 7) 2. Structure system requirements (ch. 8-10) 3. Generate alternative for selection (ch. 11) Project ID and Selection Project Initiation & Planning Analysis Data Modeling Processing Modeling Logic Modeling LogicalDesign Maintenance PhysicalDesign Implementation SDLCWhere are we?

Differences between Modeling • Data Modeling - Using Entity-Relationship Diagram (ER diagram or ERD) • identify What (types of data) needed in the system • Process Modeling - Using Data Flow Diagram (DFD) • identify Where and How (which pieces of data) to be used • Logic Modeling - Using Structured English, Decision Table, and Decision Tree • detail System Logic and Functional Structure

Data Model (ER Diagram) Data Modeling (ER Diagramming) Process Model (Data Flow Diagram) Process Modeling (Data Flow Diagramming) ERD and DFD (more static) (more dynamic)

Some Basic Jargons Model: Anabstract representation of “reality” Data Model: An organization of data to represent the “reality.” Data Modeling: a techniqueused to create a “Data Model.” Conceptual Data Modeling: a technique to capture both “data” and its “meaning” required by an organization

Conceptual Data Modeling It is: • presenting what data are needed in the system • independent from “how” data are stored • Independent from which DBMS to be used Its deliverables: • A preliminary ER model (address only Entities and Relationships) • A refined ER diagram (with detailed attributes for each entity)

An Example-- A Preliminary Entity-Relationship Model

An Example-- A Refined Entity-Relationship Diagram

ERD - basic concepts EntityA thing (tangible/intangible) that can be uniquely identified -- indicated by a noun, it could be a(n): • Person • Object (e.g., machine, tool, equipment, document) • Concept (e.g., position, movie, etc.) Symbol: a symbol for an Entity

ERD - basic concepts Relationship: • An association between one or more entities -- described by a verb phrase; it is bi-directional Symbol: A Line (with Crow’s foot)

ERD - basic conceptsEntity Type vs. Entity Instance • Entity Type - the object set that shares a common group of characteristics, e.g., STUDENT (SID, Name, Phone, Major) • Entity instance - an occurrence of an Entity Type, e.g., (1234, Mike Taylor, 387-5428, CIS)

Date completes EMPLOYEE COURSE ERD - Extended Concepts Associative Entity: a special relationship that contains its characteristics to associate instances of one or more entity types. Symbol:

Weak Entity SPOUSE EMPLOYEE ERD - Extended Concepts Weak Entity: an “entity” depends on another entity for its existence. Symbol:

(Optional one) (Mandatory one) (Optional Many) (Mandatory Many) How to Characterize a Relationship? Cardinality: The number of occurrences (instances) involved at each side of a relationship • zero or One • one and only one • zero or many • one or many

Relationship: Extended Concepts Degree of a Relationship: The number of entity types involved in a relationship. • Unary - a R within an entity itself • Binary - a R between two different entity types • Ternary - a R exists among three entity types

Binary Relationships 1 to 1 (1:1) 1 to many (1:M) many to many (M:N) is a spouse of Employee Spouse is married to Vehicle Type has Model is of is registered by registers Student Course

is married to Person 1 to 1 is a spouse of consists of Employee Part 1 to M M to N is managed by is a component of Unary Relationships manages

is married to Person 1 to 1 is a spouse of manages consists of Employee Part 1 to M M to N is managed by is a component of Unary Relationships (a complete graph)

Employee 1 to M is manages by Unary Relationships • Each Employee may manage one or more Employees. • Each Employee must be managed by one and only one Employee. manages

is a prerequisite of Course has a prerequisite More Unary Relationship! Q. What is the cardinality for the above unary relationship?

Course is a prerequisite of has a prerequisite More Unary Relationship! (A complete graph) Why? Can you explain the above diagram?

Ternary Relationship A relationship that simultaneously involves 3 entities!

How to Characterize an Entity? Attribute A characteristic (property)of an entity or a relationship • Key (identifier) • Candidate key • Non-key attribute Examples: EMP (EmpID,Name, Address, DOB) DEPT (DeptID,DeptName,DeptAddress)

EMP DEPT Foreign Key Foreign key: It is an attribute ( or a set of attributes) that is used as a Primary Key in some other entity. EMP (EmpID,Name, Address, DOB) DEPT (DeptID,DeptName,DeptAddress)

SUPPLIER PART SID PID Sup_PART Use the two PKs as the PK and FKs in the associative entity How to Resolve a M:N Relationship? It is quite common to encounter a M:N relationship - which needs to be represented as an “Associative entity”. Most DBMS can not represent a M:N relationship, we need to ...

Descrip PID Name SSN Phone Addr SID SUPPLIER PART Sup_PART Price An Example for an M:N Relationship Associative Entity SUPPLIER(SID, Name, SSN, Phone, Addr) PART (PID, Description) Sup_PART (SID, PID, Price) Associative Entity

How to Conduct the ER Diagramming? Step 1 - Identify all interested Entities Step 2 - Identify proper relationships between entities Step 3 - identify the Key and non-key attributes for each entity Step 4 -Verify the validity of the final ER diagram

Validation of ERD Rules: • The entity name must be a noun • The entity name must be unique • The entity must have an identification or primary key • Each relationship is described by a verb (phrase)

BIS 360 – Lecture Six (Part 2)