The Entity-Relationship Model

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

Entities with Different Attribute Types (Recap) Multivalued Attribute: major Primitive Attribute: sNumber Composite Attribute: address Student entity type with all its attributes DoB Age sNumber Derived Attribute: Age Age

Binary Relationships (Recap) pNumber sName sPrice product date quantity supplies buys sName cName supplier consumer sLoc cLoc Attributes can be attached to Entity Sets or Relationships

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

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 superPart There are Parts that play the role of subPart If two entities in the same entity set have a relationship  Recursive relationship

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

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

Keys of Entity Sets • Remember entity set is a group of entitieswith the same type • Key of Entity Set • Set of attributes that uniquely identify each entity • Examples: • “Car” VIN • “Person” SSN • “WPI Student” University ID • “US Student”  UniversityName + UnivesityID • A key has to be unique within the scope of your application • Does not have to be globally unique Customer Car

Types of Keys • A super key of an entity set is a set of one or more attributes whose values uniquely determine each entity • “Person” SSN, SSN + FirstName • “Account”  AccountNumber + AccountType • A candidate key of an entity set is a minimal super key • “Person” SSN • “Account”  AccountNumber • “US Student” SSN, UniversityName + UnivesityID • Each candidate key is a super key but not vice versa • A primary key is one from, possibly several, candidate keys  Pick one and declare it as “primary key” • “Student”  SSN, StudentID, FirstName + MiddleName + LastName

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

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

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

Keys of Relationships • Relationship without attributes • 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

Keys of Relationships (Cont’d) • Relationship with attributes • Attributes of the relationship may (not always) participate inside the key + the external keys • (sNumber, cNumber) is the key of Taken Grade Date project

Keys of Relationships (Cont’d) • Relationship with attributes • Attributes of the relationship may (not always) participate inside the key + the external keys • (sNumber, cNumber, Date) is the key of Taken Grade Date project In this ERD: student can take the same course on different dates

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

Mapping Cardinalities

Mapping Cardinalities (Cont’d)

Representing Cardinalities in ERD • In a relationship: • “” : 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.

One-To-Many Relationship

One-To-Many Relationship • In the one-to-many relationship a loan is associated with at most onecustomer 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

Many-To-One Relationship

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

Many-To-Many Relationship • 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

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

pNumber pName Part superPart subPart Contains quantity Cardinality Constraints for Recursive Relationships A Part may contain manysubparts A Part can be subpart in manysuperParts

Cardinality Constraints for Recursive Relationships A Part can have manysubParts A Part can be subpart for at most onesuperPart

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

Cardinality Constraints for Multi-way Relationships pNumber pName Product sName cName Supply Supplier Consumer sLoc cLoc price qty Every Supplier supplies some Product to some Consumer To add degree constraints, introduce a new entity set and create multiple binary relationships !!!

Adding Cardinality Constraints to Multi-way Relationships pNumber pName Product cName sName in Consumer Supplier cLoc sLoc consumes supplies Supp_Cons_ Prod What is the key of this entity ??? (Weak Entity) price qty

Weak Entity Sets • An entity set that does not have a primary key is referred to as a weak entity set • 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 • It must relate to the identifying entity set via a total, one-to-many relationship set from the identifying to the weak entity set Course number is unique only within the department Weak entity set Identifying entity set

Weak Entity Sets • Discriminator(or partial key) of a weak entity set • The set of attributes that uniquely identify a weak entity given its identifying entity • Primary key of a weak entity set • 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 • Cannot have a course without a corresponding department • (dNumber, cNumber) is the primary key for Course discriminator

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

Again: It Depends on Your Application/Assumptions • If you assume the course number is unique within a department • “Course” is a weak entity set • If you assume the course number is unique across all departments • “Course”  is a strong entity set offers Course Stating your assumptions in text is very important !!!

Revisit Previous Example … pNumber pName Product cName sName in Consumer Supplier cLoc sLoc consumes supplies Supp_Cons_ Prod Weak Entity price qty

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

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

Ver. 1 Name Branch Room Hotel Num Beds Capacity Capacity Has Balcony Has TV Manager Rating Tel. HQ Add. Tel. Add. Type ID Num • 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?

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

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

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

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

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

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

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

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

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

The Entity-Relationship Model