Data Modeling using ER-Diagram

1. Data Modeling using ER-Diagram Indra Budi indra@cs.ui.ac.id

2. 2 Data Modeling Process of creating a logical representation of the structure of the database The most important task in database development

3. 3 The ER Model and its extensions ER Diagrams-Notation Example Database Application (COMPANY) ER Model Concepts Entities and Attributes Entity Types, Value Sets, and Key Attributes Relationships and Relationship Types Weak Entity Types Roles and Attributes in Relationship Types Relationships of Higher Degree Notation is based on : R. Elmasri and S.B. Navathe, � Fundamentals of Database Systems,� Ed. 3., Addison Wesley, 2000, Chapters 3 and 4.

4. 4 Summary of ER-Diagram Notation Meaning ENTITY TYPE WEAK ENTITY TYPE RELATIONSHIP TYPE IDENTIFYING RELATIONSHIP TYPE ATTRIBUTE KEY ATTRIBUTE MULTIVALUED ATTRIBUTE COMPOSITE ATTRIBUTE DERIVED ATTRIBUTE TOTAL PARTICIPATION OF E2 IN R CARDINALITY RATIO 1:N FOR E1:E2 IN R STRUCTURAL CONSTRAINT (min, max) ON PARTICIPATION OF E IN R

5. 5 Example COMPANY Database Requirements of the Company (Oversimplified for Illustrative Purposes) Company is Organized into Departments Each Department has a Name, Number and an Employee Who Manages the Department We Track of the Start Date of the Department Manager Each Department Controls a Number of Projects Each Project has a Name, Number and is Located at a Single Location

6. 6 Example COMPANY Database (Cont.) Store Each Employee�s Social Security Number, Address, Salary, Sex, and Birthdate Each Employee Works for One Department but May Work on Several Projects We Track of the Number of Hours Per Week that an Employee Currently Works on Each Project We Track of the Direct Supervisor of Each Employee Each Employee May have a Number of Dependents For Each Dependent, We Track of their Name, Sex, Birthdate, and Relationship to Employee

7. 7 ER Diagram for the Company Database

8. 8 ER Model Concepts: Entities and Attributes Entities - Specific Objects or Things in the Mini-world that are Represented in the Database EMPLOYEE John Smith Research DEPARTMENT Productx PROJECT Attributes are Properties Used to Describe an Entitye.g., an EMPLOYEE Entity may have a Name, SSN, Address, Sex, Birthdate A Specific Entity (Instance) has a Value for Each of its Attributes Specific Employee Entity May Have Name=�John Smith�, SSN=�123456789�, Address=�731 Fondren, Houston, TX�, Sex=�m�, Birthdate=�09-jan-55�

9. 9 Types of Attributes Simple: Single Atomic Value for the Attribute SSN or Sex or State or Salary or ... Composite: Attribute Composed of Many Components Address (Apt#, House#, Street, City, State, Zipcode, Country) or Name(Fname, MI, Lname) Composition May form a Hierarchy where Some Components are Themselves Composite Multi-Valued: Entity may have Multiple Values for That Attribute - Like an Set Type CAR {Color} or STUDENT {Previousdegrees} Composite and Multi-valued Attributes may be Nested Arbitrarily to any Number of Levels (Rare) Previousdegrees of a STUDENT is a Composite Multi-valued Attribute Denoted by {Previousdegrees(college, Year, Degree, Field)}

10. 10 Types of Attributes (cont.) Stored vs Derived Attributes We can know Age attribute (derived) from BirthDate attribute (stored) NumberOfEmployee of a department can be derived from counting the number of employees related to (working for) that department Null Values In some cases, a particular entity may not have an applicable value for an attribute. ApartmentNumber only applies to address of Apartment and not to other residences A special value created ? Null

11. 11 Entities with Attribute Values

12. 12 Entity Types and Key Attributes Entities with the Same Basic Attributes Are Grouped or Typed into an Entity Type EMPLOYEE Entity Type or PROJECT Type Attribute of Entity Type for which Each Entity Must Have a Unique Value is Called a Key Attribute SSN of EMPLOYEE, ISBN of BOOK A Key Attribute may be Composite VehicleTagNumber is a key of the CAR entity type with components (Number, State). An Entity Type may have More than One Key CAR Entity Type May Have Two Keys: VehicleIdentificationNumber (popularly called VIN) and VehicleTagNumber (Number, State), also known as license_plate number.

13. 13 Entity Type CAR with Attributes

14. 14 Two Other Entity Types

15. 15 Relationships and Relationship Types A Relationship Relates Two or More Distinct Entities With a Specific Meaning EMPLOYEE John Smith Works on the Productx PROJECT EMPLOYEE Franklin Wong Manages the Research DEPARTMENT Relationship - Instance Level Relationships of the Same Type are Grouped or Typed Into a Relationship Type WORKS_ON Relationship Type in Which Employees and Projects Participate MANAGES Relationship Type in Which Employees and Departments Participate Analogous to Reference or List in Programming

16. 16 The WORKS_ON Relationship

17. 17 Relationships and Relationship Types Degree of a Relationship Type is the Number of Participating Entity Types Both MANAGES and WORKS_ON are Binary Relationships What is a possible Ternary Relationship? More Than One Relationship Type Can Exist With the Same Participating Entity Types MANAGES and WORKS_FOR are Distinct Relationships Between EMPLOYEE and DEPARTMENT Entity Types Relationships are Directional SUPPLIES: SUPPLIER to PARTS SUPPLIERS: PARTS to SUPPLIER

18. 18 E-R Diagrams

19. 19 Weak Entity Types Entity that Does Not have a Key Attribute Weak Entity Must Participate in an Identifying Relationship Type with an Owner or Identifying Entity Type Entities are Identified by the Combination of: A Partial Key of the Weak Entity Type Particular Entity they Are Related to in the Identifying Entity Type Example: A DEPENDENT Entity is Identified by Dependent�s First Name and Birthdate, and the EMPLOYEE That the Dependent is Related to DEPENDENT is a Weak Entity Type With EMPLOYEE as its Identifying Entity Type Via the Identifying Relationship Type DEPENDENT_OF

20. 20 ER Model and Data Abstraction Abstraction Classification Aggregation Identification Generalization ER Model Concept Entity Type - a Grouping of Member Entities Relationship Type - a Grouping of Member Relationships Relationship Type is an Aggregation of (Over) Its Participating Entity Types Weak Entity Type and Attribute Key ????????

21. 21 Constraints on Aggregation Cardinality Constraints on Relationship Types AKA Ratio Constraints Maximum Cardinality One-to-One One-to-Many Many-to-Many Minimum Cardinality (AKA Participation or Existence Dependency Constraints) Zero (Optional Participation, Not Existence-Dependent) One or More (Mandatory, Existence-Dependent)

22. 22 One-to-many(1:N) or Many-to-one (N:1)

23. 23 MANY-TO-MANY(M:N)

24. 24 One-to-One WORKS_ON Relationship

25. 25 One-to-One Relationship Each Instance of One Entity Class E1 Can Be Associated with Exactly One Instance of Another Entity Class E2 and Vice Versa. Example: Each Employee Can Work in Exactly One Project and Each Project Employs Exactly One Employee

26. 26 One-to-Many WORKS_ON Relationship

27. 27 Many-to-One Relationship Each Instance of One Entity Class E1 can be Associated with Zero or More Instances of Another Entity Class E2, but Each Instance of E2 can be Associated With at Most 1 Instance of E1 Example : Each Employee Can Work in Exactly One Project Each Project Can Employ Many Engineers

28. 28 Many-to-Many WORKS_ON Relationship

29. 29 Many-to-Many Relationship Each Instance of One Entity Class Can Be Associated with Many Instances of Another Entity Class, and vice versa Example: Each Employee Can Work in Many ProjectsEach Project Can Employ Many Employees

30. 30 Structural Constraints Structural Constraints on a Relationship are One Way to Express the Semantics of a Relationship Cardinality Ratio (of a Binary Relationship): 1:1, 1:N, N:1, or M:N Shown by Placing Apropos Number on the Link Participation Constraint (on Each Entity Type): Total (Called Existence Dependency) or Partial Shown By Double Lining The Link NOTE: Easy to Specify for Binary Relationship Types Do Not Be Misled by Obscure Notations to Specify Above Constraints for Higher Order Relationships

31. 31 Alternative (min, max) Notation Alternative (Min, Max) Notation for Relationship Structural Constraints Introduces Limits Specified on Each Participation of an Entity Type E in a Relationship Type R Specifies That Each Entity E in Participates in at Least Min and at Most Max Relationship Instances in R Default (no Constraint): Min = 0, Max = n Must Have Min ? max, Min ? 0, Max ?1 Derived From the Knowledge of Mini-World Constraints

32. 32 Examples: Alternative (min, max) Notation A Department has Exactly One Manager and an Employee Can Manage at most One Department. Specify (0, 1) for Participation of EMPLOYEE in MANAGES Specify (1, 1) for Participation of DEPARTMENT in MANAGES An Employee can Work for Exactly One Department but a Dept. can have any Number of Employees Specify (1, 1) for Participation of EMPLOYEE in WORKS_FOR Specify (0, n) for Participation of DEPARTMENT in WORKS_FOR

33. 33 Examples: Alternative (min, max) Notation

34. 34 Relationships of Higher Degree Relationship Types of Degree 2 Are Called Binary Relationship Types of Degree 3 Are Called Ternary There is a Concrete Relationship Instance that Involves all Three Entity Types These are Not Separate Relationships! Relationship Types of Degree N Are Called N-ary Again - Concrete n-Participation Relationship In General, an N-ary Relationship is Not Equivalent to N Binary Relationships Rather - it is more Analogous to the Grouping of N-Binary Relationships into a N-ary Relationship

35. 35 Ternary Relationships

36. 36 Ternary Relationships

37. 37 Ternary vs. Binary Relationships

38. 38 Constraints on Higher Order Relationships

39. 39 Higher Order (min,max) Examples

40. 40 Ternary Relationships - Instances

41. 41 Modified Earlier Example

42. 42 Another ER Diagram - Bank Example

43. 43 ER Data Modeling Tools Many Popular Existing Tools Advantages: Documentation of Application Requirements User Interface - Mostly Graphics Editor Support Disadvantages: Poor Diagramming To Avoid Layout Algorithms and Aesthetics of Diagrams, They Prefer Boxes and Lines and Typically Only Represent (Primary-foreign Key) Relationships Among Resulting Tables Lack of Built-in Methodology Support Poor Tradeoff Analysis/User-driven Design Preferences Poor Design Verification/Suggestions for Improvement

44. 44 Current ER Modeling Tools

45. 45 Design Techniques Avoid redundancy. Limit the use of weak entity sets. Don�t use an entity set when an attribute will do.

46. 46 Avoiding Redundancy Redundancy occurs when we say the same thing in two different ways. Redundancy wastes space and (more importantly) encourages inconsistency. The two instances of the same fact may become inconsistent if we change one and forget to change the other, related version.

47. 47 Example: Good

48. 48 Example: Bad

49. 49 Example: Bad

50. 50 Entity Sets Versus Attributes An entity set should satisfy at least one of the following conditions: It is more than the name of something; it has at least one nonkey attribute. or It is the �many� in a many-one or many-many relationship.

51. 51 Example: Good

52. 52 Example: Bad

53. 53 Example: Good

54. 54 Don�t Overuse Weak Entity Sets Beginning database designers often doubt that anything could be a key by itself. They make all entity sets weak, supported by all other entity sets to which they are linked. In reality, we usually create unique ID�s for entity sets. Examples include social-security numbers, automobile VIN�s etc.

55. 55 When Do We Need Weak Entity Sets? The usual reason is that there is no global authority capable of creating unique ID�s. Example: it is unlikely that there could be an agreement to assign unique player numbers across all football teams in the world.

56. 56 What are Problems with ER Notation? Historically, ER Model 1st Proposed in 1976 P. Chen, ''The Entity-Relationship Model - Toward a Unified View of Data,'' ACM Trans. on Database Systems, Vol. 1, No. 1, March 1976. However, ER Model in this Original Form Did Not Support the Generalization Abstraction (Inheritance) In Databases, Inheritance 1st Proposed in 1977 J. Smith and D. Smith, ''Database Abstractions: Aggregation and Generalization,'' ACM Trans. on Database Systems, Vol. 2, No. 2, June 1977. Thus, Extended ER Evolved through 1980s with the Focus on �Semantic Data Models� M. Hammer and D. McLeod, ''Database Descriptions with SDM: A Semantic Data Model,'' ACM Trans. on Database Systems, Vol. 6, No. 3, Sept. 1981.