Entity Relationship Diagrams

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