Data Modeling

1. ECIS466: Database Management Systems Class 2 Data Modeling Entity-Relationship Diagrams

2. Traditional Systems Development Lifecycle Planning Analysis Logical Design Physical Design Implementation

3. Traditional Systems Development Lifecycle Planning Analysis Logical Design Physical Design Implementation

4. The Analysis Stage • Requirements Determination • Requirements Structuring • Process Modeling (Data Flow Diagrams) • Logic Modeling (structured English) • Conceptual Modeling (ER Diagrams) • Alternative Generation

5. Man is a knot, a web, a mesh into which relationships are tied. Only those relationships matter.- Saint-Exupery in Flight to Arras

6. E-R Models • Type of conceptual data model • Most common tool for user-designer communication(Analysis Phase) • Easy, good CASE-support, natural way to model a user’s reality • Expressed in terms of entities, relationships, and attributes • Expressed graphically using E-R diagrams

7. Major Terminology • Entities • person, place, object, event, or concept in the user environment about which the organization wishes to maintain data • usually a noun • instance of entity Vs. entity class • example of class = CUSTOMER • example of instance = “Bob Knight”

8. Attributes • Also called properties • Describe entity characteristics • Examples for the entity class CUSTOMER • Custname • Custnumber • Address • All entity instances share same attributes

9. Identifiers • Each instance has unique identifying attribute(s). • Examples • Custnumber (for CUSTOMER) • SSN# (for STUDENT) • FacName and FacOffice (for FACULTY)

10. Relationships • Association between entities • A relationship can include many entities (degree of relationship) • Most relationships are binary

11. Relationships • Association between entities • A relationship can include many entities (degree of relationship) • Most relationships are binary • Represented as Team • Player

12. Unary Relationships • Also calledrecursive relationships • Relationships between instances of single entity class

13. Unary Relationships • Also calledrecursive relationships • Relationships between instances of single entity class is married to Person

14. Binary Relationships • Between instances of two entity classes • Most common relationship • Also referred to as HAS-A relationships

15. Binary Relationships • Between instances of two entity classes • Most common relationship • Also referred to as HAS-A relationships Order Salesperson

16. Ternary Relationships • Involves three entities • Relationship must be simultaneous • Different from three separate binary relationships

17. Ternary Relationships • Involves three entities • Relationship must be simultaneous • Different from three separate binary relationships Parts Warehouse Vendors ships

18. Cardinality • The number of instances of Entity B that can (or must be) associated with each instance of Entity A • Types • Maximum cardinality • Minimum cardinality

19. Cardinality • The number of instances of Entity B that can (or must be) associated with each instance of Entity A • Types • Maximum cardinality • Minimum cardinality Entity A Entity B

20. Three Binary Relationships • One-to-one (1:1) • One-to-many (1:N) • Many-to-many (N:M)

21. One-to-One Relationships • Each instance of Entity B matches to one instance of Entity A.

22. One-to-One Relationships • Each instance of Entity B matches to one instance of Entity A. Professor Office 1:1

23. One-to-One Relationships • Each instance of Entity B matches to one instance of Entity A. Professor Office 1:1 1:1 Employee Parking Space

24. One-to-Many Relationships • Each instance of Entity A matches to many instances of Entity B

25. One-to-Many Relationships • Each instance of Entity A matches to many instances of Entity B Student 1:N Academic Advisor

26. One-to-Many Relationships • Each instance of Entity A matches to many instances of Entity B Student 1:N AcademicAdvisor Employees Department 1:N

27. Many-to-Many Relationships • Each instance of Entity A matches to many instances of Entity B and each instance of Entity B matches to many instances of Entity A

28. Many-to-Many Relationships • Each instance of Entity A matches to many instances of Entity B and each instance of Entity B matches to many instances of Entity A N:M Student Course

29. Many-to-Many Relationships • Each instance of Entity A matches to many instances of Entity B and each instance of Entity B matches to many instances of Entity A N:M Student Course N:M Actor Movie

30. Other Representations 1:1 Office Professor 1:N Department Employee N:M Movie Actor

31. Minimum Cardinality • The minimum number of instances of Entity B that may be associated with each instance of Entity A. Customer Order Student Dormitory

32. Generalization • Some entities are subtypes of other, more general entities • Also called IS-A relationships Employee Consultant Hourly Salaried

33. Exclusive relationship • Subtypes of supertype are mutually exclusive Teacher Part-time Full-time

34. Non-Exclusive Relationship • Subtypes may overlap Client Personal Computers Client- Server Mainframe

35. Inheritance • Property by which all the attributes of the supertype are inherited by each subtype • Example • STUDENT: SSUID, Name, Address, Phone, Email • GA: SSUID, Name, Faculty, Email, Address, Phone • UGA: SSUAID, Name, Location, Supervisor, Email, Phone, Address

36. STUDENT: SSUID, Name, Address, Phone, Email • GA: SSUID, Faculty • UGA:SSUID, Location, Supervisor Name Phone Address Email AUID Student AUID AUID Location UGA GA Supervisor Faculty

37. Multivalued Attributes • Can have more than one value for each entity instance Employee SKILL EmpNo Name

38. Quick Recap • Entities • Attributes • Relationships • unary • binary • ternary • Cardinality • maximum • minimum • Generalization • exclusive • non-exclusive • Inheritance