Domain Modeling Part1: Entity Relationship Diagram

1. Domain Modeling Part1: Entity Relationship Diagram Chapter 4 pp. 92-101

2. "Things" or "Nouns" • Define system requirements by: • understanding “things” or info that needs to be stored (within the problem domain) Classification Scheme

3. How to Identify "Things" or "Nouns" • From various sources: • Current system – forms, reports, procedures • Interviews • Use cases, use case descriptions, actors • Refine the list • Include if: • Within scope • Need to know about it • Exclude if it is: • A Synonym • Derived • Research others

4. Domain Modeling • Modeling “Things” • …people, places, events, etc (nouns) • …the system needs to store data about (scope) • Two Techniques • Entity Relationship Diagram (ERD) • Domain Class Diagram

5. Entity Relationship DiagramERD

6. Entity Relationship Diagram • ERD - a data modeling technique • Entity • A “Thing” (in the scope) • Person, place, object, event, etc. Employee

7. ERD: Attributes • Attribute • A characteristic • Information about the entity that is within the scope Employee LastName FirstName SSNBirthDate JobTitle Salary • Separate Address StreetAddr, City, State, Zip • Don't separateStreetAddr  houseNo& streetName

8. ERD: Primary Key • An attribute (or a group of attributes) that uniquely identifies an instance • 3 Rules for a good Primary Key is… • Unique - no duplicate values • Required - cannot be null • Static – does not change (e.g. build name – poor PK) • Candidate Key • Multiple unique attributes may exist, if so pick: • Attribute that is used most often Employee EmpID* LastName FirstName SSNBirthDate JobTitle Salary • Tip: Try to match the PK name with the Entity Name • Order  OrderNo • Dept  DeptCode

9. ERD: Relationships • Relationship • An association between entities  represents a "NEED TO KNOW" • Occurs in two directions employs Department Employee Left to Right: A Department employs _?_ Employees Right to Left: An Employee is employed by _?_ Department

10. ERD: Relationships • Cardinality • # of association between instances • Minimum – 0 or 1 (optional or required, respectively) • Maximum – 1 or many • Use “Crows foot” Notation Employee Parking Space assigned Department Employee employs Employee Project assigned

11. ERD: Relationships • Type of Relationship • Determined by the _____________ Cardinality • One to One • One to Many • Many to Many Employee Parking Space assigned Department Employee employs Employee Project assigned

12. ERD: Relationships • Can have >1 relationship between the same 2 entities • Thus, Use precise relationship names! Employee Project assigned manages

13. Practice: ERD • Student "registers for" • Student "member of" • Student Organization is "sponsored by" • Course is "offered by" • Faculty "teaches" • Course "has" • Faculty "works for" Academic Department Student Organization Student Course Faculty Section

14. Associative Entity • Where do we place these attributes? • Employee and Project • Add StartDate– the date an employee started working on a project • Employee and SkillType • Add ProfLevel – how proficient is the employee at that skill • Student and Section • Add Grade – the grade the student made in a particular section • Adding an Associative Entity • Know when to add an associative entity • Name it appropriately • Determine the cardinalities • Determine the concatenated PK • Add any non-key attributes

15. Typical Example • Customer  Order  OrderItem

16. Understand these!

17. ERD: Relationships • Degree of the Relationship • Refers to how many ___________ participate in the relationship. • Unary Relationship (aka: Recursive Relationship) • Binary Relationship • Ternary Relationship • n-ary Relationship

18. ERD General Tips A B 18