CSE 480: Database Systems

1. CSE 480: Database Systems • Lecture 3: Entity-Relationship Diagram • Reference: • Read Chapter 3 (5th Edition) or 7 (6th edition)

2. Review • ER diagram • Entity types, attributes, and their constraints • Key, domain, and null constraints • Relationship types • Today’s lecture • Relationship constraints • Weak entity types • Ternary relationship types • Announcement: homework 1 is posted!

3. Constraints on Relationship Types Constraint on the number of times an entity participates in a relationship type

4. WORKS_ON PROJECT EMPLOYEE Constraints on Relationship Types • Two types of constraints: • Cardinality Ratio (specifies maximum participation) • Participation constraint (specifies minimum participation) • Examples: • Can an employee work on more than one project? • Can a project have more than one employee? • Must every employee work on a project? • Must a project have at least one employee?

5. Cardinality Ratios for Binary Relationships • Possible cardinality ratios: • 1:1 (one-to-one) • 1:N (one-to-many) • N:1 (many-to-one) • M:N (many-to-many) 1 1 1 N N 1 M N

6. Cardinality Ratios for Binary Relationships DEPARTMENT • 1:1 (one-to-one) • An employee manages at most 1 department • Each department has at most 1 manager EMPLOYEE 1 1 MANAGES

7. Cardinality Ratios for Binary Relationships DEPARTMENT • 1:N (one-to-many)N:1 (many-to-one) • A department can have more than one employees • An employee works for at most 1 department EMPLOYEE N 1 WORKS_FOR Be careful where you put the 1 and N!!

8. SUPERVISION Cardinality Ratios for Binary Relationships N • 1:N (one-to-many)N:1 (many-to-one) • An employee can supervise many subordinates • An employee has at most one supervisor 1 Roles: 1: supervisor 2: subordinate

9. Cardinality Ratios for Binary Relationships • M:N (many-to-many) • An employee can work on more than one project • A project can have more than one employee working on it PROJECT EMPLOYEE M N WORKS_ON

10. Participation Constraints • Specifies the minimum cardinality constraint • This also determines whether the existence of an entity depends on it being related to another entity via the relationship type • Two types: • Total participation (also called existence dependency) • Partial participation Every entity must participate in at least 1 relationship R E Some entities do not participate in any relationship E R

11. WORKS_FOR EMPLOYEE DEPARTMENT N 1 Participation Constraint & Cardinality Ratio • Every employee must work for at least one department • Each department must have at least one employee WORKS_FOR EMPLOYEE DEPARTMENT • Each employee works for exactly one department • Each department has at least one employee

12. MANAGES EMPLOYEE DEPARTMENT 1 1 Participation Constraint & Cardinality Ratio • Some employees do not manage any department • Every department must have at least one manager MANAGES EMPLOYEE DEPARTMENT • Some employees manages a department and others don’t • Every department has exactly one employee managing it

13. SUPERVISION SUPERVISION Participation Constraint & Cardinality Ratio • Some employees are not supervisors • Some employees are not subordinates N 1 • Some employees supervise one or more employees; others do not • Some employees have a supervisor; others do not

14. WORKS_FOR EMPLOYEE DEPARTMENT N 1 (min, max) notation • Specify the minimum and maximum participation of an entity type in a relationship type is equivalent to: • Default(no constraint): min=0, max=n (signifying no limit)

15. MANAGES EMPLOYEE DEPARTMENT 1 1 (min,max) notation is equivalent to:

16. SUPERVISION SUPERVISION (min,max) notation N 1 is equivalent to: (0,1) (0,N)

17. COMPANY ER Diagram using (min, max)

18. CONTROLS DEPARTMENT PROJECT 1 N DEPENDENTS_OF EMPLOYEE DEPENDENT 1 N Strong vs Weak Entity Types Strong entity type • Every project must have a controlling department • If a department “dissolves”, project info may still exist Weak entity type • Every dependent must be a dependent of an employee • If an employee leaves, dependent info no longer exists

19. Weak Entity Types • A weak entity must participate in an identifying relationship type with its owner or identifying entity type • A weak entity type does not have a key attribute. It is identified by the combination of: • A partial key of the weak entity type • The key attributes of the entities it is related to in the identifying entity type • Example: • A DEPENDENT entity is identified by the dependent’s first name and the specific EMPLOYEE with whom the dependent is related • Name of DEPENDENT is the partial key • DEPENDENT is a weak entity type • EMPLOYEE is its identifying entity type via the identifying relationship type DEPENDENT_OF

20. Name Weak Entity Type Total participation Weak entity type Owner entity type DEPENDENTS_OF EMPLOYEE DEPENDENT 1 N Relationship Birth_date SSN Sex Identifying Relationship Partial key Key for Dependent entity is (SSN, Name)

21. key Strong Entity Type Strong entity type CONTROLS DEPARTMENT PROJECT 1 N Location SSN Name Number Key for Project entity is either Project Name or Project Number

22. Summary of notation for ER diagrams

23. Exercise • You have been hired to develop a social networking Web site that allows users to meet and make friends with other users. The Web site records the personal profile of each user including full name, email address, year of birth, hometown, and interests (both in music and movies). Each user is identified by a unique user id. A privacy setting is provided for each user. If the privacy setting is low (which is the default setting), their full profile (with the exception of password) will be accessible by all other users. If privacy setting is high, only those who are friends with the user may view the full profile. • Each user has a blog, which has a URL and contains zero or more blog postings. A blog posting has a timestamp and a text message. A user may add comments to the blog postings of another user only if the blogger’s privacy setting allows the user to view his/her postings. The database keeps track of the timestamp of each comment • A user can send request to be friends with other users. Once the request has been sent, the recipient must accept the request in order to establish a “friendship” relation. • Users can post announcements of events. Each event is characterized by a unique event id, a time and place where the event will be held, and a short description of the event.

24. Exercise

25. Relationships of Higher Degree • So far, we’ve looked at binary relationship types • Relationship types of degree 3 are called ternary • Relationship types of degree n are called n-ary

26. Example of a ternary relationship SUPPLY relationship instance: (s,j,p) A supplier s supplies part p to project j

27. Ternary vs Binary Relationship Types 3 binary relationships Suppose (s,j), (j,p), and (s,p) participate in the relationships SUPPLIES, USES, and CAN_SUPPLY, respectively Is this equivalent to the instance (s,j,p) in ternary relationship?

28. Example of a ternary relationship

29. Ternary Relationships 3 binary relationships may represent different info than a single ternary relationship TAUGHT_DURING and OFFERED_DURING are redundant, but CAN_TEACH is not redundant

30. M 1 N Cardinality Ratio of Ternary Relationships A supplier can supply same part to more than 1 project There is 1 supplier for every (project, part) combination A supplier can supply more than 1 part to a project