lecture5 ERD

67 %
33 %
Information about lecture5 ERD

Published on August 8, 2007

Author: GenX

Source: authorstream.com

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

Add a comment

Related presentations

Related pages

CTEC323 Lecture 5 - Daniel Zingaro

I Create the ERD to show the relationships among EMPLOYEE, BENEFIT, JOB, and PLAN. I Create the relational diagram to show the relationships among
Read more

REU’09 Transfinite Combinatorics Lecture5

REU’09·Transfinite Combinatorics·Lecture5 Instructor: L´aszl´o Babai Scribe: ... We can use the Konig path lemma to prove the Erd˝os-de Bruijn theorem
Read more

Vorlesung V. Das Sonnensystem

Hypothetisches Sonnensystem: die Erde und die Sonne r2 GM M F S E G = x,y F x F y θ Die einzige Kraft ist die Gravitation G ist die Schwerkraftskonstante ...
Read more

Institut für Planetologie (vormals Institut für Geologie ...

Dynamik der Erde – Uni Tü WS12/13 "Ein Planet organisiert sich selbst" ...
Read more

Introduction to normalization - College of Business « UNT

BCIS 5420 - UNT 1 Introduction to normalization Lecture 4 Instructor Anna Sidorova Agenda Presentation Review of relational models, in class exersise
Read more

SQL Overview , Oracle Data Type , DDL and Constraints

Lecture5: SQL Overview , Oracle Data Type , DDL and Constraints. ... Conceptual model (ERD) Relational Data Model. Create schema (DDL) Load Data (DML)
Read more

IS1201 - Information Systems - Lecture 5 - MMU SCMDT ...

Draw ERD; Figure 8. Big Jim's First System. Each Customer can buy many bikes, Each bike is bought by one customer
Read more