the entity-relationship model
DESCRIPTION
The Entity-Relationship Model. Instructor: Mohamed Eltabakh [email protected]. Part II. Entities with Different Attribute Types (Recap). Multivalued Attribute: major. Primitive Attribute: sNumber. Composite Attribute: address. Student entity type with all its attributes. DoB. Age. - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/1.jpg)
CS3431: C-Term 2013 1
The Entity-Relationship Model
Part II.
Instructor: Mohamed Eltabakh [email protected]
![Page 2: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/2.jpg)
2
Entities with Different Attribute Types (Recap)
Composite Attribute: address
Multivalued Attribute: major
major
sta testree t
address
city
Derived Attribute: Age
AgeAge
Student entity typewith all its attributes
S tudent
sN am esN um ber
sA ge
m ajor
sta testree t
address
city
AgeAge
DoBDoB
Primitive Attribute: sNumber
sNumbersNumber
![Page 3: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/3.jpg)
Binary Relationships (Recap)
3
suppliersupplier
sNamesName
sLocsLoc
consumerconsumer
cNamecName
cLoccLoc
productproduct
pNumber
pNumber sNamesName
sPricesPrice
suppliessupplies buysbuys
datedatequantityquantity
Attributes can be attached to Entity Sets or Relationships
![Page 4: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/4.jpg)
4
Multi-Way Relationships (Recap)
Supplier
sName
sLoc
Consumer
cName
cLoc
Supply
price
Product
pName pNumber
qty
Model the relationship Supplier supplies Products to Consumers
Ternary relationship(three-way)
![Page 5: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/5.jpg)
5
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 superPartThere are Parts that play the role of subPart
Contains
Part
pName pNumber
subPartsuperPart
quantity
If two entities in the same entity set have a relationship Recursive relationship
If two entities in the same entity set have a relationship Recursive relationship
![Page 6: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/6.jpg)
Recursive Relationships: Another Example
6
Employees & Managers
Employee
ID
Name
Supervisesupervisor
supervised
![Page 7: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/7.jpg)
More Elements in ER Model
Key Constraints
Cardinality Constraints
Weak Entities
Subclass Entities (ISA Relationships)
Principles for Good Design
7
![Page 8: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/8.jpg)
Keys of Entity Sets
Remember entity set is a group of entities with 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
8
Customer
Car
![Page 9: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/9.jpg)
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
9
![Page 10: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/10.jpg)
Keys Summary
Key combination of one or more attributes that uniquely identify an entity
Types: Super key Candidate key Primary key
10
Only primary keys are modeled in ERDOnly primary keys are modeled in ERD
![Page 11: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/11.jpg)
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
11
![Page 12: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/12.jpg)
12
Multi-Attributes Primary Key
Key for Student is sNumber
Student
sNumber
sName
Key for Movie is <title, year>
Movie
title
year
We can represent key for entity set consisting of more than one attribute (e.g.: Movie)
![Page 13: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/13.jpg)
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
13
![Page 14: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/14.jpg)
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
14
Student
sNumber
sName
Course
cNumber
title
HasTaken
DateDateprojectproject
GradeGrade
![Page 15: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/15.jpg)
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
15
Student
sNumber
sName
Course
cNumber
title
HasTaken
DateDateprojectproject
GradeGrade
In this ERD: student can take the same course on different dates
In this ERD: student can take the same course on different dates
![Page 16: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/16.jpg)
More Elements in ER Model
Key Constraints
Cardinality Constraints
Weak Entities
Subclass Entities (ISA Relationships)
Principles for Good Design
16
![Page 17: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/17.jpg)
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
17
![Page 18: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/18.jpg)
Mapping Cardinalities
18
![Page 19: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/19.jpg)
Mapping Cardinalities (Cont’d)
19
![Page 20: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/20.jpg)
Representing Cardinalities in ERD
In a relationship: “ ” : Represent “many” (including 0) “ ” : Represent “one” (including 0) “ ”: Represent “one” (must be one)
20
Student
sNumber
sName
Course
cNumber
title
HasTaken
A student is taking “many” courses.
A course can be taken by “many” students.
![Page 21: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/21.jpg)
One-To-Many Relationship
21
![Page 22: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/22.jpg)
One-To-Many Relationship
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
22
A customer can take many loans
A loan can be taken by one (and at least one) customer
![Page 23: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/23.jpg)
Many-To-One Relationship
23
![Page 24: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/24.jpg)
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 one loan via borrower
24
A customer can take at most one loan
A loan can be taken by many customers
![Page 25: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/25.jpg)
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
25
![Page 26: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/26.jpg)
26
Degree of Cardinalities
How : Expressed using (min, max)
Student
sNumber
sName
Course
cNumber
title
HasTaken
(2, 3) (0, *)
Student can take many courses, and a course can be taken by many students
Student can take 0 to 5 courses, and a course can be taken by 3 to 60 students
(3, 60)(3, 60) (0, 5)(0, 5)
![Page 27: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/27.jpg)
27
Cardinality Constraints for Recursive Relationships
A Part may contain many subpartsA Part can be subpart in many superParts
Contains
Part
pName pNumber
subPartsuperPart
quantity
![Page 28: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/28.jpg)
28
Cardinality Constraints for Recursive Relationships
A Part can have many subPartsA Part can be subpart for at most one superPart
Contains
Part
pName pNumber
Is-subpartsuperPart
quantity
(0, 1)(0, *)
![Page 29: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/29.jpg)
Revisit this example…
29
Employees & Managers ….. Add cardinalities
Employee
ID
Name
Supervisesupervisor
supervised
Semantics:Manager can supervise many employeesEmployee is supervised by one manager
![Page 30: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/30.jpg)
30
Cardinality Constraints for Multi-way Relationships
Every Supplier supplies some Product to some Consumer
Supplier
sName
sLoc
Consumer
cName
cLoc
Supply
price
Product
pName pNumber
qty
To add degree constraints, introduce a new entity set and create multiple binary relationships !!!
To add degree constraints, introduce a new entity set and create multiple binary relationships !!!
![Page 31: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/31.jpg)
Adding Cardinality Constraints to Multi-way Relationships
31
Supplier
sName
sLoc
Consumer
cName
cLoc
price
Product
pName pNumber
qty
Supp_Cons_Prod
What is the key of this entity ??? (Weak Entity)
suppliesconsumes
in
![Page 32: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/32.jpg)
More Elements in ER Model
Key Constraints
Cardinality Constraints
Weak Entities
Subclass Entities (ISA Relationships)
Principles for Good Design
32
![Page 33: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/33.jpg)
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
33
Course number is unique only within the departmentWeak entity set
Identifying entity set
Dept Offers Course
dNamedNumber cNamecNumber
(1, 1)(0, *)
![Page 34: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/34.jpg)
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
34
discriminator
Dept Offers Course
dNamedNumber cNamecNumber
(1, 1)(0, *)
![Page 35: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/35.jpg)
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
35
Dept Offers Course
dNamedNumber cNamecNumber
(1, 1)(0, *)
![Page 36: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/36.jpg)
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
36
Dept Offers Course
dNamedNumber cNamecNumber
(1, 1)(0, *)CourseCourseoffersoffers
Stating your assumptions in text is very important !!!Stating your assumptions in text is very important !!!
![Page 37: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/37.jpg)
Revisit Previous Example …
37
Supplier
sName
sLoc
Consumer
cName
cLoc
price
Product
pName pNumber
qty
Supp_Cons_Prod
Weak Entity
suppliesconsumes
in
![Page 38: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/38.jpg)
What about an Exercise !!!
Lets interactively design a database for a Hotel
38
![Page 39: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/39.jpg)
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
39
![Page 40: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/40.jpg)
40
Hotel
NameHQ Add. Manager
RatingTel.
Branch
ID
Add.
Tel.
Capacity
Room
Num
Type
Num Beds
Capacity
Has TV
Has Balcony
Ver. 1
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?
![Page 41: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/41.jpg)
41
Hotel
NameHQ Add. Manager
RatingTel.
Branch
ID
Add.
Tel.
Capacity
Type
Type
Num Beds
Capacity
Has TV
Has Balcony
Ver. 2
Observations:•Lets add relationships
RoomNum
![Page 42: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/42.jpg)
42
Hotel
NameHQ Add. Manager
RatingTel.
Branch
ID
Add.
Tel.
Capacity
Type
Type
Num Beds
Capacity
Has TV
Has Balcony
Ver. 3
RoomNum
has
Of type
contains
Common mistake: Do not add “Branch ID” as an attribute to “Room” entity set. It is already captured by the weak relationship “contains”.
![Page 43: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/43.jpg)
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
43
![Page 44: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/44.jpg)
44
Hotel
NameHQ Add. Manager
RatingTel.
Branch
ID
Add.
Tel.
Capacity
Type
Type
Num Beds
Capacity
Has TV
Has Balcony
Ver. 4
RoomNum
has
Of type
contains
Guest
IDAdd.
Tel.
Name
Money Paid
Length of stayStart date
End date
Observations:•“Stay” attributes should not be part of “Guest”
![Page 45: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/45.jpg)
45
Hotel
NameHQ Add. Manager
RatingTel.
Branch
ID
Add.
Tel.
Capacity
Type
Type
Num Beds
Capacity
Has TV
Has Balcony
Ver. 5
RoomNum
has
Of type
contains
Guest
IDAdd.
Tel.
Name
Money Paid
Length of stayStart date
End dateStays inObservations:
•Still not quite right..•“Stays-in” 1-M or M-M??(Guest should be able to stay in diff. rooms)
![Page 46: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/46.jpg)
46
Hotel
NameHQ Add. Manager
RatingTel.
Branch
ID
Add.
Tel.
Capacity
Type
Type
Num Beds
Capacity
Has TV
Has Balcony
Ver. 6
RoomNum
has
Of type
contains
Guest
IDAdd.
Tel.
Name
Money Paid
Length of stayStart date
End dateStays inObservations:
•Not done yet…•In this model, a guest cannot stay in the same room over diff visits!!!
![Page 47: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/47.jpg)
47
Hotel
NameHQ Add. Manager
RatingTel.
Branch
ID
Add.
Tel.
Capacity
Type
Type
Num Beds
Capacity
Has TV
Has Balcony
Ver. 7
RoomNum
has
Of type
contains
Guest
IDAdd.
Tel.
Name
Money Paid
Length of stayStart date
End dateStays inObservations:
•Start_date part of key•Length of stay derived attribute
![Page 48: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/48.jpg)
More Elements in ER Model
Key Constraints
Cardinality Constraints
Weak Entities
Subclass Entities (ISA Relationships)
Principles for Good Design
48
![Page 49: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/49.jpg)
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
49
![Page 50: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/50.jpg)
ISA Example
50
All attributes of “student” are inherited in the other entity sets
Each entity set, e.g., “Freshman”, can have its own additional attributes
![Page 51: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/51.jpg)
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 triangle component 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
51
![Page 52: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/52.jpg)
More Complete Example
52
![Page 53: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/53.jpg)
More Complete Example
53
Attributes of Person:
Attributes of Student:
Attributes of Technician:
SSN, Name, DOB
SSN, Name, DOB, GPA, StartDate
SSN, Name, DOB, Salary, Department, Specialization
![Page 54: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/54.jpg)
Multiple ISA Relationships Can have multiple specializations of an entity set
based on different features
54
Permanent Emp
Permanent Emp
Temporary Emp
Temporary Emp
ISA
![Page 55: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/55.jpg)
ISA Relationship: Constraints
Three types of constraints Membership: To which entity set an entity belongs
Overlapping: can an entity belong to multiple subclasses or not
Completeness: Does each super entity have to belong to one (or
more) subclasses
55
![Page 56: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/56.jpg)
ISA Relationship: Membership Constraint on which entities can be members of a given lower-level
entity set Denoted in ERD on the ISA edge
56
Year = 1
Year = 2 Year = 3
Year = 4
Year
![Page 57: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/57.jpg)
ISA Relationship: Overlapping 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”
57
disjoint
![Page 58: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/58.jpg)
ISA Relationship: Completeness
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
58
Total
![Page 59: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/59.jpg)
Another Example
59
Partial, Overlapping
![Page 60: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/60.jpg)
ISA Relationship: Multiplicity
ISA relationship is always 1-1 (even though its notation is arrows without heads)
60
![Page 61: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/61.jpg)
ISA Relationship: Keys Key of sub-entities is inherited from the super entities
61
SSN is the primary key for Person, Student, Employee, Freshman, Technician, and all other sub-entities
![Page 62: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/62.jpg)
More Elements in ER Model
Key Constraints
Cardinality Constraints
Weak Entities
Subclass Entities (ISA Relationships)
Principles for Good Design
62
![Page 63: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/63.jpg)
Summary of Symbols used in ERD
63
![Page 64: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/64.jpg)
64
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
The simplest design that captures the requirements is the best
![Page 65: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/65.jpg)
65
Guidelines Toward a Good Design (I)
Convey “real” application requirements
Utilize meaningful names for Entity sets, attributes, relationships
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)
Know when to add attributes to entity sets vs. relationships
![Page 66: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/66.jpg)
Examples
66
Room
Num
Type
Num Beds
Capacity
Has TV
- 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)
- 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)
CustomerLoan
Bank
take
offer
Num SSN
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
- 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
lendX
![Page 67: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/67.jpg)
M-M Relationships vs. An Entity Set
67
StudentCourse
taking
Num
M-M Relationship between E1 and E2 can be always broken to: A new entity set E3 (usually weak entity set) 1-M relationship between E1 and E3 1-M relationship between E2 and E3
Both are correct use either one
ID
Date grade
StudentCourse
Num ID
Date grade
Registration
Involveinclude
![Page 68: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/68.jpg)
Do not overuse ISA relationship
68
• There are 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)
StudentProf
- No need for an entity set “Person” from which both “Prof” and “Student” inherit- No need for an entity set “Person” from which both “Prof” and “Student” inherit
![Page 69: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/69.jpg)
Strong vs. Weak Entity Sets Avoiding weak entities is better (If no semantics is lost) You may add unique keys
69
Hotel
Branch
has
ID
Name Hotel
Branch
has
ID
Name
- Should always favor the left design over the right one (unless explicitly stated otherwise)- Should always favor the left design over the right one (unless explicitly stated otherwise)
![Page 70: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/70.jpg)
Do not overuse multi-way relationships
They are harder to understand and interpret Can be broken by introducing new entity set and several 1-
M relationships
Avoid multi-way relationship
Avoid weak entity set
![Page 71: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/71.jpg)
ERD Cannot Capture Everything…
Some business constraints will not be captured in the design. For example: For a customer to get a load, the sum of the previous loans
to him/her must be < MaxLoan
A student cannot take the same course more than 2 times
A student cannot re-take a course that (s)he already passed
71
![Page 72: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/72.jpg)
Find the wrong things ???
72
carcar
ColorNameColorName
colorIDcolorID
VINVINMakeMake ModelModel
CustomerCustomer
IDIDDoBDoB NameName
Car-featureCar-feature
FeatureNameFeatureName
containscontains
buysbuys
DateDate
CarMilesCarMiles
AgeAge
PricePrice
LoanLoan
amountamount
numbernumber
BankBank
takestakes
DateDate
= A customer can buy many cars= A customer may take a loan to buy a specific car
![Page 73: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/73.jpg)
From the Previous Example ColorId & ColorName (cause redundancy & inconsistency)
Car can have one feature (wrong cardinality)---should be many
Car-feature has one attribute (should not be an entity)---make it attr.
CarMiles should be attached to the car (not to the relationship)
Age should be a derived attribute
A car should be bought by one (or zero) customers (the arrow head should be closed)
Loan and Car are not linked together (buys should be 3-way) Or create a new entity set “Contract” and link it to the three entity sets
73
![Page 74: The Entity-Relationship Model](https://reader036.vdocuments.us/reader036/viewer/2022062408/568138a8550346895da06689/html5/thumbnails/74.jpg)
74
Summary of ER 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