The Entity-Relationship Model

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

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 (Recap) Refer to the same entity 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

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” WPI ID • A key has to be unique within the scope of your application • Does not have to be globally unique • Example: • “US Student” SSN, UniversityName + UnivesityID

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

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 type 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, Date) is the key of Taken Grade Date project What if we do not underline “Date” attribute ??

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 student is taking “many” courses. A course can be taken by “many” students.

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 One-to-Many from Customer to Loan

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

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

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 !!!

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

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 Relationship Types (Cont’d) • Top-down design process • Build entities with the common attributes, then build sub-entities with distinctive attributes from other entities in the set • 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 • In ERD, represented by a trianglecomponent labeled ISA (E.g. customer “is a”person) • Attribute inheritance • Lower-level entity set inherits all the attributes and relationship participation of the higher-level entity set to which it is linked

ISA Relationship: Example I

ISA Relationship: Example II

ISA Relationship: Generalization • Bottom-up design process • Combine a number of entity sets that share the same features into a higher-level entity set • Specialization and generalization are simple inversions of each other • They are represented in an E-R diagram in the same way • The terms specialization and generalization are used interchangeably.

Multiple ISA Relationships • Can have multiple specializations of an entity set based on different features • E.g. permanent_employeevs. temporary_employee, in addition to officer vs. secretary vs. teller • Each particular employee would be • A member of one of permanent_employeeor temporary_employee, • And also a member of one of officer, secretary, or teller

Multiple ISA Relationships: Example permanent ISA temporary

ISA Relationship: Constraints • Constraint on which entities can be members of a given lower-level entity set • Example: all customers over 65 years are members of senior-citizen entity set; senior-citizen ISA person • Denoted in ERD on the ISA edge • Constraint on whether or not entities may belong to more than one lower-level entity set within a single generalization. • Disjoint • An entity can belong to only one lower-level entity set • Overlapping • 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”

ISA Relationship: Constraints (Cont’d) • Completeness constraint -- 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 • Total: An entity must belong to one of the lower-level entity sets • Partial: An entity need not belong to one of the lower-level entity sets

Example Overlapping & Partial

ISA Relationship: Keys & Multiplicity • Key of sub-entities is inherited from the super-entities • Multiplicity is 1:1 person_id is the primary key

Summary of Symbols used in ERD

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

Guidelines Toward a Good Design (I) • Convey “real” application requirements • Utilize meaningful names • Try simpler construct first • Avoid redundancy, do not store the same data in multiple places • Be as precise as possible (E.g., cardinality constraints) • Don’t over specify (limits input)

Guidelines Toward a Good Design (II) • Do not overuse non-binary relationships • They are harder to understand and interpret • Do not create entity sets with single attributes • They may be better as attributes of other entity sets • Do not overuse ISA relationships • There are always some commonalities between things  this does not mean they should inherit from common ancestor

Guidelines Toward a Good Design (III) • Choose meaningful relationships • Know when to add attributes to entity sets vs. relationships • Some business constraints will not be captured in the design • E.g., For a customer to get a load, the sum of the previous loans to him/her must be < MaxLoan

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

The Entity-Relationship Model