Introduction to data flow modeling: Introduction to data flow modeling Lecture 5
February 20, 2007 Schedule for the next few weeks: Schedule for the next few weeks Agenda: Agenda Case discussion
Introduction to Oracle designer
Case discussion: Case discussion What is the company and the problem discussed in this case?
What software acquisition options were considered?
Describe the vendor selection process.
Describe the strategic alliance and how it was formed.
Describe key challenges of the project.
What, in your opinion, should have been done differently?
What recommendations do you have for Bill Brewer? Conceptual Data Modeling: Conceptual Data Modeling Slide6: 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 Slide8: Introduction to Entity-Relationship (E-R) Modeling: Introduction to Entity-Relationship (E-R) Modeling Entity-Relationship (E-R) Diagram
A detailed, logical representation of the entities, associations and data elements for an organization or business
Notation uses three main constructs
Attributes Slide10: Entity - person, place, object, event or concept about which data is to be maintained
Entity type: collection of entities with common characteristics
Entity instance: single entity
Relationship - association between the instances of one or more entity types
Attribute - named property or characteristic of an entity
Components of an ERD Entity and Attribute Example: Entity and Attribute Example Simple attributes Identifier attribute… each employee has a unique ID. Multivalued attribute… an employee may have more than one skill. Slide12: Relationships: degree and cardinality Cardinality Symbols: Cardinality Symbols Slide14: A relationship that itself is related to other entities via another relationship must be represented as an associative entity. ERD Animated Example: ERD Animated Example Problem Description: Problem Description 1. A company has many employees. We store each employee's name, SSN, address, salary, sex, and birth date.
2. The company is organized into departments. Each department has a unique name, a unique number, and a particular employee who manages the department. The department may have several locations.
3. An employee is assigned to one department, and we keep track of the employee assignments. We also keep track of the direct supervisor of each employee.
4. Also, we want to keep track of the dependents of each employee for insurance purposes. We keep each dependent's name, sex, birth date, and relationship to the employee. Draw as complete an ERD as you can. You must show all cardinalities (low and high) and name the relationship. Let’s identify all entitles and their attributes …: Let’s identify all entitles and their attributes … 1. EMPLOYEE: name, SSN, address, salary, sex, and birth date
2. DEPARTMENT: unique name, a unique number, a location and manager (?)
3. DEPENDENT: name, sex, birth date, and relationship to the employee
Let’s draw the entitles and their attributes …: Let’s draw the entitles and their attributes … Let’s identify and draw the relationships …: Let’s identify and draw the relationships … Slide20: 1. An employee may have min. zero, max. many dependents. Each dependent has to belong to exactly one (min. one, max. one) employee. Slide21: 2. Each department has min. 1, max. many employees. Each employee works for exactly one (min. one, max. one) department. Slide22: 3. Each department has exactly one manager. Each employee managers min. zero, max. one department. Slide23: 4. Each employee is supervised by exactly one other employee. An employee may be a supervisor for min. zero, max. many other employees. ATTENTION: This is a unary relationship! It is a relationship between instances of EMPLOYEE entity type! Subtypes and supertypes: Subtypes and supertypes Supertypes and Subtypes: Supertypes and Subtypes Subtype: A subgrouping of the entities in an entity type which has attributes that are distinct from those in other subgroupings
Supertype: An generic entity type that has a relationship with one or more subtypes
Subtype entities inherit values of all attributes of the supertype
An instance of a subtype is also an instance of the supertype Slide26: Employee supertype with three subtypes All employee subtypes will have emp nbr, name, address, and date-hired Each employee subtype will also have its own attributes Relationships and Subtypes: 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 Slide28: Supertype/subtype relationships in a hospital Both outpatients and resident patients are cared for by a responsible physician Only resident patients are assigned to a bed Constraints in Supertype/ Completeness Constraint: Constraints in Supertype/ Completeness Constraint 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) Slide30: Examples of completeness constraints (a) Total specialization rule Slide31: Partial specialization rule Examples of completeness constraints Constraints in Supertype/ Disjointness constraint: Constraints in Supertype/ Disjointness constraint 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 Slide33: (a) Disjoint rule Examples of disjointness constraints Slide34: (b) Overlap rule Examples of disjointness constraints