the entity-relationship model continued - virginia...
Post on 21-Apr-2020
2 Views
Preview:
TRANSCRIPT
The Entity-Relationship Model Continued
T. M. Murali
September 27, 2010
T. M. Murali September 27, 2010 CS 4604: E/R model
Roles in Relationships
I Can the same entity set appear more than once in the samerelationship?
I Prerequisite relationship between two Courses
PreReqCourses
Enrollment
ClassroomName
I Which course is the pre-requisite?
T. M. Murali September 27, 2010 CS 4604: E/R model
Roles in Relationships
I Can the same entity set appear more than once in the samerelationship?
I Prerequisite relationship between two Courses
PreReqCourses
Enrollment
ClassroomName
I Which course is the pre-requisite?
T. M. Murali September 27, 2010 CS 4604: E/R model
Roles in Relationships
I Which course is the pre-requisite?
I Label the lines connecting entity to relationship with the role of theentity.
Requirement
Requirer
PreReqCourses
Enrollment
ClassroomName
T. M. Murali September 27, 2010 CS 4604: E/R model
Parallel Relationships
I Can there be more than one relationship between the same pair ofentities?
I TA and Take relationship between Students and Classes
Courses
TA
Take
Students
T. M. Murali September 27, 2010 CS 4604: E/R model
Parallel Relationships
I Can there be more than one relationship between the same pair ofentities?
I TA and Take relationship between Students and Classes
Courses
TA
Take
Students
T. M. Murali September 27, 2010 CS 4604: E/R model
Are Attributes on Relationships Needed?
Students Courses Take
Name Address
Enrollment
ClassroomNameGrade
I We can convert the attribute to an entity and make the relationshipmultiway.
Students Courses Take
Name Address
Enrollment
ClassroomName
GradeGrade
T. M. Murali September 27, 2010 CS 4604: E/R model
Are Attributes on Relationships Needed?
Students Courses Take
Name Address
Enrollment
ClassroomNameGrade
I We can convert the attribute to an entity and make the relationshipmultiway.
Students Courses Take
Name Address
Enrollment
ClassroomName
GradeGrade
T. M. Murali September 27, 2010 CS 4604: E/R model
Multiway Relationships
I Relationships may connect more than two entity sets.I Consider scenario two in the handout: ≥ 1 professor can teach a
course but each student evaluates each professor separately.
I Three-way Evaluation relationship between Students, Professors, andClasses.
Grade
Courses
Evaluation
Students
Professors
T. M. Murali September 27, 2010 CS 4604: E/R model
Multiway Relationships
I Relationships may connect more than two entity sets.I Consider scenario two in the handout: ≥ 1 professor can teach a
course but each student evaluates each professor separately.
I Three-way Evaluation relationship between Students, Professors, andClasses.
Grade
Courses
Evaluation
Students
Professors
T. M. Murali September 27, 2010 CS 4604: E/R model
Another Multiway Relationship
I Consider scenario three in the handout: ≥ 1 professor can teach acourse but each student taught by at most one professor, and eachstudent only evaluates that professor.
I Add arrow directed towards Professors.
Grade
Courses
Evaluation
Students
Professors
T. M. Murali September 27, 2010 CS 4604: E/R model
Another Multiway Relationship
I Consider scenario three in the handout: ≥ 1 professor can teach acourse but each student taught by at most one professor, and eachstudent only evaluates that professor.
I Add arrow directed towards Professors.
Grade
Courses
Evaluation
Students
Professors
T. M. Murali September 27, 2010 CS 4604: E/R model
Multiplicity in Multiway Relationships
I An arrow pointing to an entity set E ⇒ if we select an entity fromeach of the other entity sets, the selected entities are related to atmost one entity in E.
Grade
Courses
Evaluation
Students
Professors
Student Course Professor Grade
Hermione Grainger Potions Snape F-
Draco Malfoy Potions Snape A*
Harry Potter Potions Lupin A+
Ron Weasley Potions Lupin B+
I E/R diagram forbids connections between “Hermione Grainger,”“Potions” and two different professors.
T. M. Murali September 27, 2010 CS 4604: E/R model
Multiplicity in Multiway Relationships
I An arrow pointing to an entity set E ⇒ if we select an entity fromeach of the other entity sets, the selected entities are related to atmost one entity in E.
Grade
Courses
Evaluation
Students
Professors
Student Course Professor Grade
Hermione Grainger Potions Snape F-
Draco Malfoy Potions Snape A*
Harry Potter Potions Lupin A+
Ron Weasley Potions Lupin B+
I E/R diagram forbids connections between “Hermione Grainger,”“Potions” and two different professors.
T. M. Murali September 27, 2010 CS 4604: E/R model
Converting Multiway Relationships to Binary
I It is easy to convert a multiway relationship to multiple binaryrelationships.
I Create a new connecting entity set. Think of its entities as the tuplesin the relationship set for the multiway relationship.
I Introduce relationships from the connecting entity set to each of theentities in the original relationship.
I If an entity set plays > 1 role, create a relationship for each role.
Grade
Courses
Evaluation
Students
Professors
Students Courses
Evaluation
Professors
Student−of
Professor−of
Course−of
I What is the multiplicity of each of the new relationships?
T. M. Murali September 27, 2010 CS 4604: E/R model
Converting Multiway Relationships to Binary
I It is easy to convert a multiway relationship to multiple binaryrelationships.
I Create a new connecting entity set. Think of its entities as the tuplesin the relationship set for the multiway relationship.
I Introduce relationships from the connecting entity set to each of theentities in the original relationship.
I If an entity set plays > 1 role, create a relationship for each role.
Grade
Courses
Evaluation
Students
Professors
Students Courses
Evaluation
Professors
Student−of
Professor−of
Course−of
I What is the multiplicity of each of the new relationships?
T. M. Murali September 27, 2010 CS 4604: E/R model
Example of the Conversion
I Instance of Evaluation (ternary) relationship before conversion:
Student Course Professor Grade
Hermione Grainger Potions Snape F-
Draco Malfoy Potions Snape A*
Harry Potter Potions Lupin A+
Ron Weasley Potions Lupin B+
I Evaluation entity set afterconversion:
Eval Id Grade
e1 F-
e2 A*
e3 A+
e4 B+
I Student of entity set afterconversion:
Eval Id Student
e1 Hermione Grainger
e2 Draco Malfoy
e3 Harry Potter
e4 Ron WeasleyT. M. Murali September 27, 2010 CS 4604: E/R model
Details of the Conversion
I Create an entity in the new Evaluation entity set for each instance(row) in the ternary Evaluation relationship.
I In the Student of relationship, relate each entity in the Evaluationentity set with the corresponding student entity.
I How many students can the Student of relationship relate anEvaluation entity to?
Only one!
I Therefore, the multiplicity of Student of is many-to-one fromEvaluation to Student.
Students Courses
Evaluation
Professors
Student−of
Professor−of
Course−of
Eval_id
T. M. Murali September 27, 2010 CS 4604: E/R model
Details of the Conversion
I Create an entity in the new Evaluation entity set for each instance(row) in the ternary Evaluation relationship.
I In the Student of relationship, relate each entity in the Evaluationentity set with the corresponding student entity.
I How many students can the Student of relationship relate anEvaluation entity to? Only one!
I Therefore, the multiplicity of Student of is many-to-one fromEvaluation to Student.
Students Courses
Evaluation
Professors
Student−of
Professor−of
Course−of
Eval_id
T. M. Murali September 27, 2010 CS 4604: E/R model
Subclasses in the E/R Model
I A subclass of an entity set E is an entity set F such that
I each entity in F is an entity in E .I the entity set F must have at least one attribute or participate in at
least one relationship that E does not.
I Connect E to F using an isa relationship denoted by a triangle.
I Convention is to draw E above F .
I Each isa relationship is one-one but we do not draw the arrows.
I The set of isa relationships must form a tree.
T. M. Murali September 27, 2010 CS 4604: E/R model
Example of Subclasses
I University Employees, Handout 2.
Staff
Grant
Teachers Researchers
9 month
Grant
ID
isa
isa
isa
isa
isa
Employees
Professors
Relationshipswith Courses
T. M. Murali September 27, 2010 CS 4604: E/R model
Example of Subclasses
I University Students, Handout 2 (ignore underlines, double shapes).
Students
ID Name
Undergraduates
UTA_for
Graduates
GTA_for
Masters PhDs
Courses
Major Major
DepartmentsOffered_by
Name
Name
Number
Thesis_title_PhDThesis_title_MS
T. M. Murali September 27, 2010 CS 4604: E/R model
E/R vs. OO Subclasses
I In object-oriented programming languages, each object is in only oneclass.
I A subclass inherits variables and methods from the superclasses.
I In an E/R diagram, an entity has components in all the subclasses towhich it belongs.
I If an entity e has a component in an subclass, then e has a componentin the superclass.
I Does e have a component in the root?
I The attributes of e are the union of the attributes of its components.I e participates in all the relationships its components participate in.
T. M. Murali September 27, 2010 CS 4604: E/R model
Components of an Entity
I Prof. Fingers InMany Pies has a 9-month appointment, teaches inone semester every year, and does not teach in the other semester.
I In the other semester, his research grant pays his salary.I Which entity sets does he have components in? (using a different isa
hierarchy)
Staff
ID
Dept
GrantGrant
Calendar Research9 month
isa isa isa
isaisa
Employees
Professors
T. M. Murali September 27, 2010 CS 4604: E/R model
Components of an Entity
I How do we represent students enrolled in combinedBachelors-Masters programmes?
I Such a student has components in multiple entity sets.
Students
ID Name
Undergraduates
UTA_for
Graduates
GTA_for
Masters
Thesis_title_MS
PhDs
Thesis_title_PhD
Courses
T. M. Murali September 27, 2010 CS 4604: E/R model
Components of an Entity
I How do we represent students enrolled in combinedBachelors-Masters programmes?
I Such a student has components in multiple entity sets.
Students
ID Name
Undergraduates
UTA_for
Graduates
GTA_for
Masters
Thesis_title_MS
PhDs
Thesis_title_PhD
Courses
T. M. Murali September 27, 2010 CS 4604: E/R model
top related