lecture5 ERD

Views:
 
     
 

Presentation Description

No description available.

Comments

By: saimaalim (37 month(s) ago)

Pls allow to download dis ppt.....

By: jhunzhel (37 month(s) ago)

can i download this ppt ? thanks

By: jhunzhel (37 month(s) ago)

nice one... can i download this ppt? thanks

By: jhunzhel (37 month(s) ago)

nice one... can i download this ppt? thanks

By: hassan1377 (41 month(s) ago)

i need to download it for myself

See all

Presentation Transcript

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 Data modeling 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 Data entities Relationships 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 Inheritance: 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