CSE 480 Database Systems Lecture 3 EntityRelationship Diagram

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

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

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

Constraints on Relationship Types l Two types of constraints: – Cardinality Ratio (specifies maximum participation) – Participation constraint (specifies minimum participation) EMPLOYEE WORKS_ON PROJECT – Examples: u u 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? 4

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

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

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

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

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

Participation Constraints l 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 l Two types: – Total participation (also called existence dependency) E R Every entity must participate in at least 1 relationship R Some entities do not participate in any relationship – Partial participation E 10

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

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

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

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

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

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

COMPANY ER Diagram using (min, max) 17

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

Weak Entity Types l l 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 l 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 19

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

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

Summary of notation for ER diagrams 22

Exercise l l 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. 23

Exercise 24

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

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

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? 27

Example of a ternary relationship Supplier Project Supplier Part Ace R 1 Ace T 1 Supplier Project Part Ace R 2 Ace T 2 Ace R 1 T 1 Acme R 1 Acme T 2 Ace R 2 T 2 Acme R 2 Acme T 1 Acme R 1 T 2 Lowes R 1 Lowes T 1 Acme R 2 T 1 Lowes R 2 Lowes T 2 Lowes R 1 T 1 Lowes R 2 T 2 Project Part R 1 T 1 R 1 T 2 R 2 T 1 R 2 T 2 28

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 29

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 M 1 N A supplier can supply more than 1 part to a project 30