Set 3: Data Modeling Using the Entity-Relationship (ER) Model

1. Set 3: Data Modeling Using the Entity-Relationship (ER) Model Chapter 7, first part

2. Overview of Database Design Process Figure 7.1 in 6th edition

3. Requirements collection and analysis • Database designers interview prospective database users to understand and document data requirements • Result: • data requirements • Functional requirements of the applications

4. Conceptual schema • Conceptual design • Description of data requirements • Includes detailed descriptions of the entity types, relationships, and constraints • Transformed from high-level data model (in our case, the E-R model) into implementation data model (in our case, the relational model)

5. Logical design or data model mapping • Result is a database schema in implementation data model of DBMS • Physical design phase • Internal storage structures, file organizations, indexes, access paths, and physical design parameters for the database files specified

6. Example Database Application • COMPANY • Employees, departments, and projects • Company is organized into departments • Department controls a number of projects • Employee: store each employee’s name, Social Security number, address, salary, sex (gender), and birth date • Keep track of the dependents of each employee

7. E-R diagram for the company database

8. Entity Types, Entity Sets, Attributes, and Keys • ER model describes data as: • Entities • Relationships • Attributes

9. Entities and Attributes • Entity • Thing in the real world with independent existence • Attribute • Particular property that describes an entity • Types of attributes: • Simple (atomic) versus composite attributes • Single-valued versus multivalued attributes • Stored versus derived attributes • NULL values • Complex attributes

10. Entities and Attributes (cont’d.)

11. ER Diagrams for entities entity simple attribute (key attribute is underlined) multivalued attribute compound attribute derived attribute

12. Initial Conceptual Design of the COMPANY Database

13. Relationship Types, Relationship Sets, Roles, and Structural Constraints • Relationship • When you want to express a concept that spans two or more entity types • When an attribute of one entity type refers to another entity type • Represent references as relationships not attributes employee Works_For department

14. Binary Relationship –instances Figure 7.9 in 6th edition

15. Relationship Types, Sets, and Instances • Relationship typeR among n entity types E1, E2, ..., En • Defines a set of associations among entities from these entity types • Relationship instanceri • Each ri associates n individual entities (e1, e2, ..., en) • Each entity ej in ri is a member of entity set Ej

16. Relationship Degree • Degree of a relationship type • Number of participating entity types • Binary, ternary Supplier Supply Part Project

18. Role Names and Recursive Relationships • Role names • Role name signifies role that a participating entity plays in each relationship instance • Recursive relationships • Same entity type participates more than once in a relationship type in different roles • Must specify role name Employee Supervisor Supervisee Supervision

19. Instances

20. Constraints on Binary Relationship Types • Cardinality ratio for a binary relationship • Specifies maximum number of relationship instances that an entity can participate in • choices are 1:1, 1:n and n:m • Participation constraint • Types: total and partial • if each department must have a manager, then that side is total • if an employee can work at the company but not be assigned to a department, then that is partial participation of employees in the “what dept. they work for” relationship

21. Attributes of Relationships • There can be values describing a relationship • e.g. suppose employee:department has a StartDate describing it. • the employee may have worked in several departments so it does not describe the employee • the department will have many employees, so StartDate does not describe a department • it describes what you get when one instance of employee is associated with one instance of department

22. Entity Types, Entity Sets, Keys, and Value Sets • Entity type • The entity type in the schema gives a Templateor Constructorfor instances, or • Collection (or set) of entities that have the same attributes

23. Entity Types, Entity Sets, Keys, and Value Sets (cont’d.) • Key or uniqueness constraint • Attributes whose values are distinct for each individual entity in an entity set • Key attribute • Uniqueness property must hold for every entity set of the entity type • Value sets (or domain of values) • Specifies set of values that may be assigned to that attribute for each individual entity

24. Weak Entity Types • Do not have key attributes of their own • Identified by being related to specific entities from another entity type • Specifies that the existence of the weak entity depends on its being related to another entity • Identifying relationship • Relates a weak entity type to its owner • Always has a total participation constraint, because it needs the identifying entity connection to be in the database. • Usually the weak entity instances will be deleted if the regular entity they are connected to is deleted from the database (they don’t have existence on their own)

25. Refining the ER Design for the COMPANY Database • Change attributes that represent relationships into relationship types • Determine cardinality ratio and participation constraint of each relationship type

26. ER Diagrams, Naming Conventions, and Design Issues

27. Proper Naming of Schema Constructs • Choose names that convey meanings attached to different constructs in schema • Entity type names are usually Nouns • Relationship type names are often Verbs • Choose binary relationship names to make ER diagram readable from left to right and from top to bottom

28. Design Choices for ER Conceptual Design • Model concept first as an attribute • Refined into a relationship if attribute is a reference to another entity type • Attribute that exists in several entity types may be elevated to an independent entity type • Can also be applied in the inverse

29. Alternative Notations for ER Diagrams • Can use 1:1, 1:n or n:m on the edges • Can add to this a double line from E1 to R if every instance of E1 must participate in this relationship or • The alternative is the (min, max) notation on the edge from E1 to R. This says every instance of E1 must participate in min occurrences of the relationship, and less than or equal to max occurrences, where 0 ≤ min ≤ max and max ≥ 1 • “must participate” translates into (1,n), min of 1

30. Alternative notations for relationship edges(can also include role names, not shown) Works _for Employee Department This says “every employee must be in a department” used together n Works _for 1 Employee Department This shows the 1:n relationship with no participation constraints. (1,1) (0,n) Works _for Employee Department This is called the (min, max) notation: one employee must work in a department and only one department. Depts. may have 0, 1, ... n employees

31. E-R diagram for the company database

33. Relationship Types of DegreeHigher than Two • Degree of a relationship type • Number of participating entity types • Binary • Relationship type of degree two • Ternary • Relationship type of degree three

34. Choosing between Binary and Ternary (or Higher-Degree) Relationships • Some database design tools permit only binary relationships • Ternary relationship must be represented as a weak entity type • No partial key and three identifying relationships • Represent ternary relationship as a regular entity type • By introducing an artificial or surrogate key

35. and (b) describe different information. Usually (a) is better than (c).

36. Constraints on Ternary (or Higher-Degree) Relationships • Notations for specifying structural constraints on n-ary relationships • Should be used if it is important to fully specify structural constraints • n:m:p shows many-to-many-to-many n p Supplier Supply Part m Project