1 / 55

Database Fundamentals-IS 380 Entity Relationship Diagram

Database Fundamentals-IS 380 Entity Relationship Diagram Chapter 4 : Lecture Notes for Asela M. Thomason. Topic Objectives. Understand and use the basic constructs of entity modeling: Entity Relationship Attribute Additional concepts: Subtypes/Super-types

ljohn
Télécharger la présentation

Database Fundamentals-IS 380 Entity Relationship Diagram

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database Fundamentals-IS 380 Entity Relationship Diagram Chapter 4 : Lecture Notes for Asela M. Thomason IS 380, A. M. Thomason

  2. Topic Objectives • Understand and use the basic constructs of entity modeling: • Entity • Relationship • Attribute • Additional concepts: • Subtypes/Super-types • Composite, Associative, Recursive, Exclusive, etc. • Optional/Mandatory • Cardinality IS 380, A. M. Thomason

  3. Entity Relationship Diagram Data Model Components Data Model Business Data Documentation i.e., policy, constraints, etc. Data Dictionary Decision Log Model Set Integration IS 380, A. M. Thomason

  4. What is Entity Modeling? • Entity modeling is a formal process of describing data in terms of entities, attributes, and relationships. IS 380, A. M. Thomason

  5. What is a Business Rule? • A business policy is a required course of action shown to guide decisions. • A business rule is a business condition under which: • Data items are created, related, and maintained. • Business processes are executed. IS 380, A. M. Thomason

  6. What is an Entity? • An entity is a business object important to the organization and describes a person, place, thing, or event about which data is stored. • May refer to a tangible object in the real world, such as CUSTOMER or PRODUCT • May refer to an intangible business concept, such as ORDER or INVOICE. IS 380, A. M. Thomason

  7. Diagramming Entities CUSTOMER IS 380, A. M. Thomason

  8. Naming Entities Data Entity Name Noun (singular) Adjective (optional) IS 380, A. M. Thomason

  9. Entity Definitions • A good entity definition says only what the entity is. • It excludes how the entity is used. IS 380, A. M. Thomason

  10. Entity Examples Order Processing CUSTOMER ORDER PRODUCT SALESPERSON INVOICE Purchasing VENDOR PART PURCHASE ORDER INVOICE PURCHASING AGENT Project Management PROJECT CLIENT EMPLOYEE Banking BANK HOUSEHOLD CUSTOMER ACCOUNT IS 380, A. M. Thomason

  11. Entity Type Instances Entity Instance Customer # N Customer # 5 Customer # 4 Customer # 3 Customer # 2 Customer # 1 Entity Type CUSTOMER IS 380, A. M. Thomason

  12. Entity Type Vs. Entity Instance TYPE INSTANCE Data Modeling Sybase Concepts Process Dynamic Modeling COURSE IS 380, A. M. Thomason

  13. Identify Entity Relationships is assigned to PROJECT EMPLOYEE is staffed by IS 380, A. M. Thomason

  14. Identify Entity Relationships is assigned to PROJECT EMPLOYEE is staffed by drives EMPLOYEE VEHICLE is driven by teaches TEACHER STUDENT is taught by IS 380, A. M. Thomason

  15. Relationships Should Be Meaningful fashions DESIGNER GARMENT is designed by wears DESIGNER GARMENT is worn by IS 380, A. M. Thomason

  16. Diagramming Relationships CUSTOMER Read left to right above the line (CUSTOMER owns INSURANCE POLICY) owns owns INSURANCE POLICY is owned by CUSTOMER is owned by Read right to left below the line (INSURANCE POLICY is owned by CUSTOMER) INSURANCE POLICY Clockwise Convention Holds When Rotated 90 Degrees IS 380, A. M. Thomason

  17. Types of Connectivity A B One-to-one Relationship (1:1) C D One-to-many Relationship (1:M), or (M:1) many-to-one E F Many-to-many Relationship (M:N) IS 380, A. M. Thomason

  18. Connectivity – One-to-one maintains CURRICULUM INSTRUCTOR is maintained by • An instructor maintains one curriculum • A curriculum is maintained by one instructor. IS 380, A. M. Thomason

  19. Connectivity – One-to-many is offered by CLASS COURSE offers • A course is offered by many classes • A class offers one course. IS 380, A. M. Thomason

  20. Connectivity – Many-to-many consists of COURSE CURRICULUM is part of • A curriculum consists of many courses • A course is part of many curriculum. IS 380, A. M. Thomason

  21. Maximum and Minimum Instances Relationship Participation: Optional/Mandory is placed by CUSTOMER ORDER places Maximum = Many Minimum = 0 Maximum = Many Minimum = 0 Cardinality ( min. , max.) (0,10) Maximum = 10 Minimum = 0 IS 380, A. M. Thomason

  22. Exercise – Reading Maximums & Minimums has scheduled is taught by COURSE CLASS INSTRUCTOR is scheduled for teaches consists of is part of is maintained by CURRICULUM maintains IS 380, A. M. Thomason

  23. Exercise – Reading Cardinality casts MOVIE ACTOR acts in is made into is filmed from teaches learns from DRAMA TEACHER SCRIPT IS 380, A. M. Thomason

  24. Possible Solution Identifying Maximums and Minimums casts MOVIE ACTOR acts in is made into is filmed from teaches learns from DRAMA TEACHER SCRIPT IS 380, A. M. Thomason

  25. Naming Attributes Attribute Name Qualifier Class IS 380, A. M. Thomason

  26. Identifying Attributes What are the attributes of the following: “Data Modeling is a three-day course.” IS 380, A. M. Thomason

  27. Exercise – Identifying Maximums and Minimums MOVIE ACTOR DRAMA TEACHER SCRIPT IS 380, A. M. Thomason

  28. Assigning Attributes To determine where an attribute lives, ask: “What primary key determines the value of this attribute?” OR “What entity does this attribute describe?” IS 380, A. M. Thomason

  29. Choosing Primary Keys EMPLOYEE: PRODUCT: CUSTOMER: SIN CHECKING ACCOUNT: IS 380, A. M. Thomason

  30. Entity Identifiers • A Key is one or more attributes that will uniquely identify an instance of an entity. COURSE INSTRUCTOR is taught by Title Department Duration Name teaches IS 380, A. M. Thomason

  31. Primary Key Guidelines • Identify all candidate keys • Select one to be the primary key • If no good candidate exists, a non-intelligent key can be generated (Customer ID, Order Number). IS 380, A. M. Thomason

  32. Foreign Keys Examples EMPLOYEE: Foreign Key Primary Key EMPLOYEE # NAME ADDRESS JOB CODE DEPARTMENT # DEPARTMENT: EMPLOYEE DEPARTMENT Primary Key DEPARTMENT # DEPARTMENT NAME FIXED ASSET: Foreign Key FIXED ASSET Primary Key INVENTORY # DESCRIPTION ORIGINAL COST DEPARTMENT # IS 380, A. M. Thomason

  33. Defining Foreign Keys COURSE CLASS has scheduled Course ID (PK) is scheduled for Class ID (PK) consists of is part of CURRICULUM INSTRUCTOR is maintained by Curriculum ID (PK) Instructor ID (PK) maintains IS 380, A. M. Thomason

  34. Defining Relationship Instances has scheduled COURSE CLASS is scheduled for Class Name (PK) Course ID (FK) Course ID (PK) IS 380, A. M. Thomason

  35. Resolving Many-to-Many Relationships COURSE CLASS has scheduled Course ID (PK) is scheduled for Class ID (PK) consists of is part of CURRICULUM INSTRUCTOR is maintained by Curriculum ID (PK) Instructor ID (PK) maintains IS 380, A. M. Thomason

  36. Why Is It Important to Resolve Many-to-many Relationships? is assigned to EMPLOYEE PROJECT has assigned EMPLOYEE: PROJECT: Primary Key Primary Key PROJ # DESCRIPTION EMPLOYEE # NAME P1 P2 P3 ORDER PROCESSING MORTAGE LOANS PAYROLL E2 E3 E7 MCELREATH TAMMI SAVIANO IS 380, A. M. Thomason

  37. How are Many-to-many Relationships Resolved? is assigned to EMPLOYEE PROJECT has assigned EMPLOYEE ASSIGNMENT PROJECT Primary key: Employee# Primary key: Employee# Project# Primary key: Project# Associative Entity (Composite Entity) IS 380, A. M. Thomason

  38. Relational Table View Primary Key EMPLOYEE: EMPLOYEE # NAME EMPLOYEE E2 E3 E7 MCELREATH TAMMI SAVIANO ASSIGNMENT: Primary Key EMP # PROJ # START DATE HOURS ASSIGNMENT E2 E2 E3 E4 E7 P1 P2 P1 P3 P2 02/89 01/90 06/89 11/89 07/89 100 50 500 75 10 PROJECT: Primary Key PROJECT PROJ # DESCRIPTION P1 P2 P3 ORDER PROCESSING MORTAGE LOANS PAYROLL IS 380, A. M. Thomason

  39. What is a Subtype? LOAN CUSTOMER COMMERCIAL LOAN COMMERCIAL CUSTOMER Subtype (Child) RESIDENTIAL LOAN RESIDENTIAL CUSTOMER Supertype (Parent) IS 380, A. M. Thomason

  40. Entity Subtyping Rules • Entity subtypes are subdivisions of an entity that represent the same object as the original entity. BANK ACCOUNT BANK ACCOUNT CHECKING ACCOUNT OR is a is a SAVINGS ACCOUNT CHECKING ACCOUNT SAVINGS ACCOUNT IS 380, A. M. Thomason

  41. Exercise - Subtyping • Subtypes and draw each of the following entities once by life cycle state and once by variety: • EMPLOYEE • INSURANCE POLICY • FAMILY MEMBER IS 380, A. M. Thomason

  42. Possible Solution - Subtyping EMPLOYEE INSURANCE POLICY FAMILY MEMBER MANAGEMENT EMPLOYEE TERM LIFE PARENT VARIETY UNION EMPLOYEE VARIABLE LIFE CHILD EMPLOYEE INSURANCE POLICY FAMILY MEMBER ACTIVE EMPLOYEE ACTIVE POLICY LIFE CYCLE STATE ADULT RETIRED EMPLOYEE LAPSED POLICY MINOR IS 380, A. M. Thomason

  43. Subtyping Strategies An entity can be subtyped by: Life Cycle State Variety STUDENT BOOK MYSTERY GRADUATE ADVENTURE UNDER- GRADUATE ADVENTURE IS 380, A. M. Thomason

  44. When is Subtyping Useful? • When the business views subsets of an entity differently; for instance, when business policies are different for different subsets. • When only certain subsets of an entity have a relationship with another entity. • When the cardinality between two entities is different for one or more subsets. • When the attributes of an entity vary significantly from subset to subset. IS 380, A. M. Thomason

  45. Entity Subtypes Example OWNER is owned by owns is accom- modating is parked in DOCK VEHICLE GARAGE is moored at contains BECOMES Parent (Supertype) OWNER Child (Subtype) is owned by owns is parked in is accom- modating VEHICLE GARAGE AUTO BOAT DOCK contains is moored at IS 380, A. M. Thomason

  46. Resolving Entity Subtypes Subtypes must be resolved before creating your Physical ERD, model. There are 3 different options: Option 1: Create dependent entities and preserve the parent entity. ( a dependent entity contains the primary key of another entity, or has an existence dependency on that entity) BOAT AUTO VEHICLE IS 380, A. M. Thomason

  47. Resolving Entity Subtypes Option 2: Create an entity for every subtype and eliminate the parent. The vehicle information will be duplicated in auto and boat. BOAT AUTO IS 380, A. M. Thomason

  48. Resolving Entity Subtypes Option 3: Create an entity that will store all the information for both the supertype and subtype. VEHICLE IS 380, A. M. Thomason

  49. Data Model Types Conceptual (First draft) Logical Physical (Access/Oracle) Note: These three models do not match the Database book in class. Similar but not exact. IS 380, A. M. Thomason

  50. Why is Conceptual Entity Modeling Important? • It identifies the high-level data requirements of each major business function. • It portrays the business view of the data. • It illustrates important entity relationships. • It forces you to question and crystallize business policy. IS 380, A. M. Thomason

More Related