1 / 37

Entity Relationship Diagrams

Entity Relationship Diagrams. Conceptual Data Modeling. Database Development Process. Business Information Requirements. Business view. Systems view. Logical Database Design. Physical Database Build. Operational Database. Terminology. CONCEPTUAL. LOGICAL. (Business view).

jack-park
Download Presentation

Entity Relationship Diagrams

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. Entity Relationship Diagrams

  2. Conceptual Data Modeling Database Development Process Business Information Requirements Business view Systems view Logical Database Design Physical Database Build Operational Database

  3. Terminology CONCEPTUAL LOGICAL (Business view) (Systems view) ANALYSIS DESIGN ENTITY TABLE RELATIONSHIP FOREIGN KEY ATTRIBUTE COLUMN PRIMARY KEY UNIQUE IDENTIFIER UNIQUE KEY

  4. Information Models • Organize thought processes • Accurately model business data • Communicate with stakeholders • Analyze the scope • Provide sound basis for system design

  5. CS 450 ERD Conventions attribute Cardinality (degree) empnum key M 1 Works For EMPLOYEE DEPARTMENT entity relationship Participation Requirements (optionality)

  6. COMPANY Super-type held by SUPPLIER Sub-type the holder of OTHER COMPANY composed of Recursive Relationship part of Exclusive Arc Main Elements of an Oracle ERD Relationship Unique Identifier CUSTOMER # * customer number * first name * last name o other initials held by Entity MEMBERSHIP Attributes the holder of

  7. Agreement Item1 Item 2 Product X Product X Hardware and Software Independence ENTITY RELATIONSHIP MODEL Agreement Term Product NETWORK DATABASE HIERARCHICAL DATABASE Agreement Agreement Product x Product y Item 1 Item 2 RELATIONAL DATABASE Code Date Customer Code Description Quantity Agreement Number Product

  8. Relationship Definitions • The way one entity relates to another • The business rules that link together business information needs • What one thing has to do with another • A named association between entities

  9. A line between two entities Lower case relationship name Optionality (Minimum cardinality) Degree (Maximum cardinality) Relationship Diagramming Conventions Mandatory - must be Optional - may be One or more One and only one

  10. COPY TITLE Relationship Diagramming Conventions many (crow’s foot) optional one mandatory

  11. Relationship Syntax Entity 2 Entity 1 must be or may be one or more or one and only one relationship name Each entity 1 entity 2 Object entity Name Subject entity Optionality Degree

  12. How do you read this? assigned to EMPLOYEE DEPARTMENT

  13. How do you read this? assigned to EMPLOYEE DEPARTMENT Each EMPLOYEE must be assigned to one and only one DEPARTMENT

  14. How do you read this? EMPLOYEE DEPARTMENT responsible for

  15. How do you read this? EMPLOYEE DEPARTMENT responsible for Each DEPARTMENT may be responsible for one or more EMPLOYEES

  16. Full Reading of Relationship assigned to EMPLOYEE DEPARTMENT EMPLOYEE DEPARTMENT responsible for Each EMPLOYEE must be assigned to one and only one DEPARTMENT Each DEPARTMENT may be responsible for one or more EMPLOYEES

  17. How do you read this? enrolled in COURSE STUDENT taken by

  18. enrolled in COURSE STUDENT taken by How do you read this? Each STUDENT may be enrolled in one or more COURSES Each COURSE may be taken by one or more STUDENTS

  19. Analyzing and Modeling Relationships • Determine the existence of a relationship • Name each direction of the relationship • Determine the degree of each direction of the relationship • Determine the optionality of each direction of the relationship • Read the relationship aloud to validate it

  20. Oracle’s Layout Guidelines Parent Entity (1) Child Entity Dead Crows Fly East ! Parent Entity (2)

  21. Badge Number - Identifies an employee Name - Qualifies an employee Payroll category (weekly or salaried) - Classifies an employee Date of birth - Quantifies an employee Employment status (active, leave, terminated) - Classifies of an employee Attributes

  22. Finding Attributes Is this attribute really needed ? Beware of obsolete requirements from previous systems Beware of derived data

  23. Attribute Diagramming Conventions • Inside the entity's soft box • Singular • Lowercase EMPLOYEE badge num first name last name payroll num date of birth employment status

  24. ITEM ITEM type vendor num code Meaningful Components PERSON PERSON last name first name name Break down aggregate attributes

  25. RENTAL transaction date total amount paid item RENTAL ITEM RENTAL item num transaction date total amount paid Verify for Single Value Can an attribute have more than one value for one instance of the entity? Yes, more than one item may be rented at a time. An entity is missing.

  26. TITLE REVIEW product code title description review details author comment date recorded Attributes Which have Attributes TITLE Does information need to be stored about any of the attributes? product code title description review details Yes, review details. An entity is missing.

  27. 12 08 30 22---- 72---- Finding Common or Derived Data • Count • Total • Maximum, Minimum, Average • Calculation Derived attributes are redundant and can lead to inconsistent values

  28. A value must bestored for each entity instance Tagged with A value may bestored for each entity instance Tagged with o Attribute Optionality Mandatory Attributes * Optional Attributes

  29. Attribute Optionality EMPLOYEE * badge num * first name * last name o title o weight

  30. Attribute Details and Volumes Attribute - * Engine Size Format Type Number Maximum length 4 Average length 4 Decimal place 1 Unit of measure cc Allowable values 900,1000,1500,1800,2000 Volume Initial 100%

  31. Using a Domain Movie Mono AUDIO Stereo MON STE SUR Audio Game Surround Sound

  32. Creating an Entity For DB Planning

  33. Adding Details

  34. Domains

  35. Creating Relationships

  36. Diagrammer Behavior • Definition changes are stored in Repository even if the diagram is not saved. • Saving a diagram saves the layout. • Diagrams are not synchronized automatically. • Requerying is your responsibility. • Diagrams can be exported to, for example, Word documents.

  37. Editing in the RON

More Related