The Entity Relationship Model Part II Instructor Mohamed

The Entity. Relationship Model Part II. Instructor: Mohamed Eltabakh meltabakh@cs. wpi. edu CS 3431: C-Term 2013 1

Entities with Different Attribute Types (Recap) Multivalued Attribute: major Composite Attribute: address Derived Attribute: Age 2

Binary Relationships (Recap) 3

Multi-Way Relationships (Recap) Model the relationship Supplier supplies Products to Consumers Ternary relationship (three-way) 4

Recursive Relationship Types and Roles Refer to the same entity set in the relationship Recursive relationship type : Part-Subpart Roles: There are Parts that play the role of super. Part There are Parts that play the role of sub. Part If two entities in the same entity set have a relationship Recursive relationship 5

Recursive Relationships: Another Example l Employees & Managers ID Employee Name supervisor Supervise supervised 6

More Elements in ER Model l Key Constraints l Cardinality Constraints l Weak Entities l Subclass Entities (ISA Relationships) l Principles for Good Design 7

Keys of Entity Sets l Remember entity set is a group of entities with the same type l Key of Entity Set l l Examples: l l l Set of attributes that uniquely identify each entity “Car” VIN “Person” SSN “WPI Student” University ID “US Student” University. Name + Univesity. ID A key has to be unique within the scope of your application l Does not have to be globally unique 8

Types of Keys l A super key of an entity set is a set of one or more attributes whose values uniquely determine each entity l l l “Person” SSN, SSN + First. Name “Account” Account. Number + Account. Type A candidate key of an entity set is a minimal super key l l l “Person” SSN “Account” Account. Number “US Student” SSN, University. Name + Univesity. ID l Each candidate key is a super key but not vice versa l A primary key is one from, possibly several, candidate keys Pick one and declare it as “primary key” l “Student” SSN, Student. ID, First. Name + Middle. Name + Last. Name 9

Keys Summary l Key combination of one or more attributes that uniquely identify an entity l Types: l l l Super key Candidate key Primary key Only primary keys are modeled in ERD 10

Primary Keys in ERD l Select only one key to be the primary key l Primary key is modeled by “underline” under its set of attributes l Good Practice: l Select singleton and number fields whenever possible 11

Multi-Attributes Primary Key for Movie is <title, year> Key for Student is s. Number We can represent key for entity set consisting of more than one attribute (e. g. : Movie) 12

Keys of Relationships l Relationship without attributes l l The combination of primary keys of the participating entity sets forms a key of a relationship set (customer_id, load_number) is the key of borrower 13

Keys of Relationships (Cont’d) l Relationship with attributes l l Attributes of the relationship may (not always) participate inside the key + the external keys (s. Number, c. Number) is the key of Taken 14

Keys of Relationships (Cont’d) l Relationship with attributes l l Attributes of the relationship may (not always) participate inside the key + the external keys (s. Number, c. Number, Date) is the key of Taken 15

More Elements in ER Model l Key Constraints l Cardinality Constraints l Weak Entities l Subclass Entities (ISA Relationships) l Principles for Good Design 16

Cardinality Constraints l Express the number of entities to which another entity can be associated via a relationship set l Most useful in describing binary relationship sets l For a binary relationship set the mapping cardinality must be one of the following types: l l One to one One to many Many to one Many to many 17

Mapping Cardinalities 18

Mapping Cardinalities (Cont’d) 19

Representing Cardinalities in ERD l In a relationship: l l l “ “ “ ” : Represent “many” (including 0) ” : Represent “one” (including 0) ”: Represent “one” (must be one) A course can be taken by “many” students. A student is taking “many” courses. 20

One-To-Many Relationship 21

One-To-Many Relationship l In the one-to-many relationship a loan is associated with at most one customer via borrower, a customer is associated with many (including 0) loans via borrower A customer can take many loans A loan can be taken by one (and at least one) customer 22

Many-To-One Relationship 23

Many-To-One Relationship l In a many-to-one relationship a loan is associated with many (including 0) customers via borrower, a customer is associated with at most one loan via borrower A customer can take at most one loan A loan can be taken by many customers 24

Many-To-Many Relationship l In a many-to-many relationship a loan is associated with many (including 0) customers via borrower, a customer is associated with many loan via borrower 25

Degree of Cardinalities How : Expressed using (min, max) l Student can take many courses, and a course can be taken by many students l Student can take 0 to 5 courses, and a course can be taken by 3 to 60 students 26

Cardinality Constraints for Recursive Relationships p. Number p. Name Part super. Part sub. Part Contains quantity A Part may contain many subparts A Part can be subpart in many super. Parts 27

Cardinality Constraints for Recursive Relationships A Part can have many sub. Parts A Part can be subpart for at most one super. Part 28

Revisit this example… l Employees & Managers …. . Add cardinalities ID Employee Name supervisor Supervise supervised Semantics: l Manager can supervise many employees l Employee is supervised by one manager 29

Cardinality Constraints for Multi-way Relationships p. Number p. Name Product s. Name c. Name Supply Supplier Consumer s. Loc c. Loc price qty Every Supplier supplies some Product to some Consumer To add degree constraints, introduce a new entity set and create multiple binary relationships !!! 30

Adding Cardinality Constraints to Multi-way Relationships p. Number p. Name Product c. Name s. Name in Supplier Consumer s. Loc consumes supplies c. Loc Supp_Cons_ Prod price qty What is the key of this entity ? ? ? (Weak Entity) 31

More Elements in ER Model l Key Constraints l Cardinality Constraints l Weak Entities l Subclass Entities (ISA Relationships) l Principles for Good Design 32

Weak Entity Sets l An entity set that does not have a primary key is referred to as a weak entity set l l Its attributes are not enough to form a key The existence of a weak entity set depends on the existence of an identifying entity set l It must relate to the identifying entity set via a total, one-to-many relationship set from the identifying to the weak entity set Identifying entity set Weak entity set Course number is unique only within the department 33

Weak Entity Sets l Discriminator (or partial key) of a weak entity set l l Primary key of a weak entity set l l The composition of the primary key of the identifying entity set + the weak entity set’s discriminator Identifying entity has to exist for each weak entity l l The set of attributes that uniquely identify a weak entity given its identifying entity Cannot have a course without a corresponding department (d. Number, c. Number) is the primary key for Course discriminator 34

Representing a Weak Entity Set l Weak entity set is represented by double rectangles l Weak relationship (supporting relationship) is represented by double diamonds l Weak relationship is one-many from the weak entity to the identifying entity 35

Again: It Depends on Your Application/Assumptions l If you assume the course number is unique within a department l l “Course” is a weak entity set If you assume the course number is unique across all departments l “Course” is a strong entity set 36

Revisit Previous Example … p. Number p. Name Product c. Name s. Name in Supplier Consumer s. Loc consumes supplies c. Loc Supp_Cons_ Prod Weak Entity price qty 37

What about an Exercise !!! Lets interactively design a database for a Hotel 38

Example: Hotel Database l A Hotel has many branches l l l Each branch has many rooms with different types and numbers. A room type defines l l l Hotel name, logo, address of HQ, Tel. , manager, star rating Branch Id, address, Tel. , Total capacity Room size, Number of beds Has TV or not, Has Balcony or not Guests can stay in a hotel for a period of time l l Guests have unique ID, name, address, Tel. We need to capture, the length of the stay, start date, end date, money paid 39

HQ Add. Ver. 1 Manager Name Rating Tel. Hotel Num Beds Capacity Has TV Capacity Room Has Balcony Tel. Branch Type Add. ID Observations: • Room type is modeled as attribute (causes redundancy) • Room number, is it numeric like 1001? If so, how come to be unique across branches? 40

HQ Add. Ver. 2 Manager Name Num Beds Rating Tel. Hotel Capacity Has TV Has Balcony Type Capacity Tel. Branch Room Add. Num ID Observations: • Lets add relationships 41

HQ Add. Ver. 3 Manager Name Num Beds Rating Tel. Capacity Hotel Has TV Type Has Balcony has Type Capacity Of type Tel. Branch Add. contains Room Num ID Common mistake: Do not add “Branch ID” as an attribute to “Room” entity set. It is already captured by the weak relationship “contains”. 42

Back to the Requirements l A Hotel has many branches l l l Each branch has many rooms with different types and numbers. A room type defines l l l Hotel name, logo, address of HQ, Tel. , manager, star rating Branch Id, address, Tel. , Total capacity Room size, Number of beds Has TV or not, Has Balcony or not Guests can stay in a hotel for a period of time l l Guests have unique ID, name, address, Tel. We need to capture, the length of the stay, start date, end date, money paid 43

HQ Add. Ver. 4 Manager Name Num Beds Rating Tel. Capacity Hotel Has TV Has Balcony Type has Type Capacity Of type Tel. contains Branch Room Add. Num Observations: • “Stay” attributes should not be part of “Guest” ID Start date Length of stay End date ID Guest Add. Money Paid Name Tel. 44

HQ Add. Ver. 5 Manager Name Num Beds Rating Tel. Capacity Hotel Has TV Has Balcony Type has Type Capacity Of type Tel. contains Branch Room Num Add. ID Length of stay Start date Observations: • Still not quite right. . • “Stays-in” 1 -M or M-M? ? (Guest should be able to stay in diff. rooms) ID Stays in End date Money Paid Guest Add. Name Tel. 45

HQ Add. Ver. 6 Manager Name Num Beds Rating Tel. Capacity Hotel Has TV Has Balcony Type has Type Capacity Of type Tel. contains Branch Room Num Add. ID Length of stay Start date Observations: • Not done yet… • In this model, a guest cannot stay in the same room over diff visits!!! Stays in End date Money Paid Guest ID Add. Name Tel. 46

HQ Add. Ver. 7 Manager Name Num Beds Rating Tel. Capacity Hotel Has TV Has Balcony Type has Type Capacity Of type Tel. contains Branch Room Num Add. ID Length of stay Start date Observations: • Start_date part of key • Length of stay derived attribute ID Stays in End date Money Paid Guest Add. Name Tel. 47

More Elements in ER Model l Key Constraints l Cardinality Constraints l Weak Entities l Subclass Entities (ISA Relationships) l Principles for Good Design 48

ISA Relationship Types l Similar to “subclass” concept in Object-Oriented languages l Entity sets share some common attributes but differ in others l Sometimes called “Specialization/Generalization” l Example l Students can be UGStudents or Grad. Students l l l UGStudents take undergrad Classes Grad. Students can be TAs or RAs Grad. Students are advised by Professors 49

ISA Example l All attributes of “student” are inherited in the other entity sets l Each entity set, e. g. , “Freshman”, can have its own additional attributes 50

ISA Relationship Types (Cont’d) l Top-down design process l Build entities with the common attributes, then build sub-entities with distinctive attributes from other entities in the set l These sub-entities become lower-level entity sets that have attributes or participate in relationships that do not apply to the general higher-level entity set l In ERD, represented by a triangle component labeled ISA (E. g. customer “is a” person) l Attribute inheritance l Lower-level entity set inherits all the attributes and relationship participation of the higher-level entity set to which it is linked 51

More Complete Example 52

More Complete Example Attributes of Person: SSN, Name, DOB Attributes of Student: SSN, Name, DOB, GPA, Start. Date Attributes of Technician: SSN, Name, DOB, Salary, Department, Specialization 53

Multiple ISA Relationships l Can have multiple specializations of an entity set based on different features Permanent Emp ISA Temporary Emp 54

ISA Relationship: Constraints l Three types of constraints l Membership: To which entity set an entity belongs l Overlapping: can an entity belong to multiple subclasses or not l Completeness: Does each super entity have to belong to one (or more) subclasses 55

ISA Relationship: Membership l Constraint on which entities can be members of a given lower-level entity set l Denoted in ERD on the ISA edge Year = 1 Year = 4 Year = 2 Year = 3 56

ISA Relationship: Overlapping l Constraint on whether or not entities may belong to more than one lower-level entity set within a single generalization. l Disjoint l l Overlapping l l An entity can belong to only one lower-level entity set An entity can belong to more than one lower-level entity set Denoted in ERD by writing “disjoint” or “overlapping” next to ISA triangle, by default “disjoint” disjoint 57

ISA Relationship: Completeness l Specifies whether or not an entity in the higher-level entity set must belong to at least one of the lower-level entity sets within a generalization l Total : An entity must belong to one of the lower-level entity sets l Partial: An entity need not belong to one of the lower-level entity sets Total 58

Another Example Partial, Overlapping 59

ISA Relationship: Multiplicity l ISA relationship is always 1 -1 (even though its notation is arrows without heads) 60

ISA Relationship: Keys l Key of sub-entities is inherited from the super entities SSN is the primary key for Person, Student, Employee, Freshman, Technician, and all other sub-entities 61

More Elements in ER Model l Key Constraints l Cardinality Constraints l Weak Entities l Subclass Entities (ISA Relationships) l Principles for Good Design 62

Summary of Symbols used in ERD 63

Coming up with a good design for your application l No single right design, there can be many… l Put clear, reasonable assumptions and make a design that captures the assumptions l Without stating the assumptions, others can claim your design is wrong !!! l It is like art, common sense and experience make a difference l The simplest design that captures the requirements is the best 64

Guidelines Toward a Good Design (I) l Convey “real” application requirements l Utilize meaningful names for l Entity sets, attributes, relationships l Avoid redundancy, do not store the same data in multiple places l Be as precise as possible (E. g. , cardinality constraints) l Don’t over specify (limits input) l Know when to add attributes to entity sets vs. relationships 65

Examples Num Beds Num SSN Capacity Room take Has TV Loan Type offer Customer lend Bank - The room “capacity, Num Beds, has TV” attributes they all depend on the type. So why repeat them with each room. - The “type” should be a separate entity set (slide 42) X ID - The relationship “lend” is redundant and should not be there - The relation between a customer and a bank is already captured by the two other relationships 66

M-M Relationships vs. An Entity Set l M-M Relationship between E 1 and E 2 can be always broken to: l l A new entity set E 3 (usually weak entity set) 1 -M relationship between E 1 and E 3 1 -M relationship between E 2 and E 3 Both are correct use either one Date Num grade Num ID Course taking Course ID Student include Involve Registration Date grade 67

Do not overuse ISA relationship • There always some commonalities between things this does not mean they should inherit from common ancestor • Use it only if there is a substantial overlap in attributes (and possibly relationships) Prof Student - No need for an entity set “Person” from which both “Prof” and “Student” inherit 68

Strong vs. Weak Entity Sets l l Avoiding weak entities is better (If no semantics is lost) You may add unique keys Name ID Hotel Name Hotel has Branch ID - Should always favor the left design over the right one (unless explicitly stated otherwise) 69

Do not overuse multi-way relationships l l They are harder to understand interpret Can be broken by introducing new entity set and several 1 M relationships Avoid multi-way relationship Avoid weak entity set

ERD Cannot Capture Everything… l Some business constraints will not be captured in the design. For example: l For a customer to get a load, the sum of the previous loans to him/her must be < Max. Loan l A student cannot take the same course more than 2 times l A student cannot re-take a course that (s)he already passed 71

Find the wrong things ? ? ? 72

From the Previous Example l Color. Id & Color. Name (cause redundancy & inconsistency) l Car can have one feature (wrong cardinality)---should be many l Car-feature has one attribute (should not be an entity)---make it attr. l Car. Miles should be attached to the car (not to the relationship) l Age should be a derived attribute l A car should be bought by one (or zero) customers (the arrow head should be closed) l Loan and Car are not linked together (buys should be 3 -way) l Or create a new entity set “Contract” and link it to the three entity sets 73

Summary of ER Model l Concepts l l Entity, Entity Sets, Weak Entity Sets Relationships Types l l binary, ternary, multi-way, recursive, weak, ISA Attributes l For entity sets or relationship types l Simple, composite, derived, multi-valued l Constraints – key, cardinality l Guidelines for Good Design 74