School of Science/ Computer Science and Information Technology
ISYS3412PracticalDatabase Concepts Week 5: Tute/Lab – Entity Relationship Modeling
Semester 1 2020
1 Objective
The objectives of this tute/lab session are:
● Learn about design choices you make while building an entityrelationship model;
● Learn how to build an entityrelationship model;
● Learn how to apply cardinality and participation correctly;
1.1 Preambletothisweek’swork
This Tute/ Lab session is an extension to the previous week’s activity where you had developed a few ER models for given scenarios. While you were doing that group activity, you had encountered several occasions where you were required to make some choices: is it an entity in its own right or an attribute of another entity? Is it an simple attribute or a composite attribute? Is it a simple attribute or a multivalued attribute? Is it an entity or a relationship? Is it a onetomany relationship or a manytomany relationship?
Today we explore further these aspects, and learn how to make better choices.
Consider following exercise (week 4, activity 1 (b)).
Specifications state that:
“Students are allowed to register in only one program at any given time. They enrol in
courses in each semester. Students can enrol in the same course multiple times across different semesters. The system keeps track of marks obtained in each enrolment.”
When we attempted this question, without any doubt, we have identified “student”, “program”, and “Course” as the entities.
Next, we focused on identifying relationships among these entities. The relationship between “student” and “Program” (registers relationship) was a straightforward one, with both the cardinality and participation clearly started in the specifications. The relationship between “student” and “course” (enrols relationship) required some care. The first solution we came up is simply draw up a manytomany relationship between “student” and “course”, as follows.
School/Department/Area
Document: ER Model Tute 2 V2.0.docx Author: Santha Sumanasekara Save Date: 25/03/2017 Page 1 of 11
In this model, we denote “semester” and “marks” as relationship attributes of the “enrols” relationship.
Although this model is capable of representing almost all aspects of the system, it lacks one major constraint. It was required to allow students to enrol same course in different semesters, and store marks associated with each enrolment.
Course has a unique CourseCode, student has a unique StudentNo. According to business rules, students can enrol in same course multiple times. So, student <> course pairings cannot be uniquely identified with
Then, your solution would be like this.
This diagram correctly represents all the requirements in the specifications. However, it has
Document: ER Model Tute 2 V2.0.docx Author: Santha Sumanasekara Save Date: 25/03/2017 School/Department/Area Page 2 of 11
one ternary relationship. We always try to avoid having higherdegree relationships, unless they are absolutely required.
So, we revisit our specifications and try to interpret them other ways (if possible).
In university systems, we have two concepts – courses and their semester offerings. What students enrol in are CourseOfferings, not in Courses. The given specifications are vague here. It doesn’t distinguish these two concepts. Normally, courses have unique course codes. However, we cannot use CourseCode to uniquely identify Course offerings. In the CourseOfferings entity, we cannot find any attribute that can be used as primary key. The SemesterNo can be unique within a subset of course offerings related to one course, that’s not unique across the whole set of course offerings. What that tells us is that, CourseOffering can be a weak entity where SemesterNo can act as a partial key.
In the next iteration of the diagram, we separate these two entities (Course as a strong entity and CourseOffering as a weak entity). Then, enrols relationship becomes a relationship between Student and CourseOffering entities.
[At the ER→ Relational Schema mapping process, the weak entity CourseOffering will become a relation with
Then, the relationship between Student and CourseOffering will be able to uniquely identify enrolments uniquely.
[At the ER → Relational Schema mapping process, the enrols relationship will become a relation of its own with
The ER model using this approach will be like this:
Between different iterations, what changes were made?
1. Changing a relationship attribute to a separate entity
2. Changing a binary relationship to a ternary relationship
School/Department/Area
Document: ER Model Tute 2 V2.0.docx Author: Santha Sumanasekara Save Date: 25/03/2017 Page 3 of 11
3. Introduce a new entity, identify its type and identify the partial key (in the absence of a primary key) of the new entity
4. Changing the ternary relationship back into a binary relationship.
Review the iterative process you have followed and apply that experience into this week’s activities.
Document: ER Model Tute 2 V2.0.docx Author: Santha Sumanasekara Save Date: 25/03/2017 School/Department/Area Page 4 of 11
2 Activity:
2.1 BankERModel
Consider the following ER model developed to describe a part of a bank operation.
1. List concisely the requirements that led to this ER model. i.e. outline business rules that governs the bank operations.
Answer: Bank has branches. Banks are uniquely identified by BankNo.
Branches do not have any attribute that can be used as a primary key. We have BranchNo, but that’s not unique across the board. So, BankBranch is a weak entity. Customers can open accounts with a branch of the bank, so do take loans.
Every account is associated with one and only one branch. Customers can have multiple accounts. We may have a customer who may not have a single account. Same constraints applicable to loans.
2. Is there a weak entity? If so, give its name, partial key and the identifying relationship.
Answer: Yes, BankBranch is a weak entity. It does not have an attribute that uniquely identify its entities. BranchNo may be unique within the corresponding bank, but not across the board. Note that, this BranchNo is not the same as BSB used in Australian banking sector.
So, BankBranch is a weak entity.
Its partial key is BranchNo.
Its identifying relationship is ‘has’, which connects it to its parent entity Bank.
3. What constraints do the partial key and the identifying relationship of the weak entity specified in this diagram?
Answer: A bank can have one or more branches. A branch is owned by one and
Document: ER Model Tute 2 V2.0.docx Author: Santha Sumanasekara Save Date: 25/03/2017
School/Department/Area Page 5 of 11
only one bank.
So, cardinalitywise, this is a onetomany relationship.
Participationwise, both entities are participating mandatory.
(note: If you consider banks with no branches, participation become partial on bank side).
4. List the multiplicity of each of relationship in this diagram. Justify your choices.
Answer:
One example.
Interpret “Maintain” relationship.
Read forward direction, “a branch maintains zero or many accounts”. So, you put 0..N at the end.
Read it backwards, “an account is maintained by one and only one branch”. So, you put 1..1 at the end.
So, cardinalitywise (looking at second digits at each end), this is a onetomany relationship.
Participationwise, this is mandatory from account side and partial from branch side.
5. Identify a major shortcoming of this model. What changes do you propose to overcome this issue.
Answer: The major shortcoming is that this assumes that all accounts are singlecustomer accounts. Ditto for loans. As you know any bank would allow joint accounts where multiple customers can jointly own and operate.
To accommodate onetomany relationship between Customer and Account should be replaced by a manytomany relationship. Same should apply to Takes relationship.
2.2 CompanyERModel
Consider the Company ER model we discussed in the lectures.
School/Department/Area
Document: ER Model Tute 2 V2.0.docx Author: Santha Sumanasekara Save Date: 25/03/2017 Page 6 of 11
In this company, departments have multiple locations. Employees are assigned to departments, however, each employee is physically located in one branch/ location of the department.
The current ER model is limited, in the sense that we cannot determine the location of an employee. It will be able to tell the possible locations the employee might be located – i.e. we know which department he/she works and the locations that department has branches. So, it should be one of these locations. But, there is no way we can find the definitive location.
1. Discuss the problem highlighted above and, as a group, work on an alternative solution capable of storing required information.
Answer: I think the problem is quite selfexplanatory. For a given employee, we cannot find his working location/ branch. We would expect that this system should be able to answer such queries, but a relational database built using this ER model would not be able to do so.
Solution to this problem is to rearrange the entities around (also make the DeptLocations an entity of its own). Connect Employee entity to DeptLocation, and then, DeptLocation to Department.
By doing so, we will be able to answer queries, such as, “Find the location for employee 12345678?” or “Find the list of employees of location “Washington”.
2. Present your solution as an entityrelationship model. You are not required to draw the full diagram, only the changes you made to the above diagram will be sufficient.
Document: ER Model Tute 2 V2.0.docx Author: Santha Sumanasekara Save Date: 25/03/2017
School/Department/Area Page 7 of 11
3. Identify any other limitations in the above model.
Answer: Employees are assigned to departments. Projects are also managed by departments. However, if employees are only allowed to work in projects managed by his/ her department, we cannot enforce such a constraint.
Note: This limitation in ER model is called a “Fan Trap”. A fan trap may exist where two or more 1..N relationships fan out from the same entity. This model represents the facts that a single department operates one or more branches/ locations and has one or more staff. However, a problem arises when we want to know which members of staff work at a particular branch.
2.3 LibraryERModel
Consider the question 4 of the last week’s tutorial. It states that:
The library provides books to borrowers. Each book is described by title, edition and year of publication and is uniquely identified using the ISBN. Each borrower is described by his or her name and address and is uniquely identified using a borrower number. The library provides one or more copies of each book and each copy is uniquely identified using a copy number, status indicating if the book is available for loan and the allowable loan period for a given copy. A borrower may loan one or many books and the date each book is loaned out and is returned is recorded. Loan number uniquely identifies each book loan.
Based this description, the following ER model has been built.
School/Department/Area
Document: ER Model Tute 2 V2.0.docx Author: Santha Sumanasekara Save Date: 25/03/2017 Page 8 of 11
1. Identify a misrepresentation of facts in this ER model.
Answer: Problem specification states that each book copy has its own load period, and each loan record corresponds to one book copy loan. That way, when a book copy is loaned, we can refer to its loan period, calculate the return date and add it to the loan record.
So, each loan record is related to one and only one boo copy. This model has a wrong cardinality to the consists relationship. It is be a onetomany relationship. A BookCopy can be in zero or many BookLoans. One BookLoan rfers to one and only one BookCopy.
2. Redraw the ER diagram to address this shortcoming.
School/Department/Area
Document: ER Model Tute 2 V2.0.docx Author: Santha Sumanasekara Save Date: 25/03/2017 Page 9 of 11
2.4 MappingERmodeltoRelationalDatabaseModel
Consider the following ER model developed to represented the Essendon airport operations, depicted in Q5 (last tutorial).
Map this ER diagram into a relational database schema. Show every step of the mapping. No marks are awarded to the final schema if you do not show the partiallybuilt schema at the end of each step. Indicate the primary key (underlined) and foreign key (with an asterisk) in each relation.
Answer:
Step 1: Strong Entities
Model (ModelNo, Capacity)
Plane (RegNo)
Technician (SSN, name, address, phone number, and salary) Test(TestNo, name, MaxScore)
Step 2: Weak Entities Nothing to do
Step 3: Onetoone relationships
None exists.
So no action.
However, if they exist, a foreign key is introduced. Direction is determined based on which entity participated mandatory.
Step 4: Onetomany relationships
PlaneModel is a one to many relationship, where “Plane” sits on many side. So, primary key of the model (ModelNo) is imported to Plane relation as a foreign key.
Document: ER Model Tute 2 V2.0.docx Author: Santha Sumanasekara Save Date: 25/03/2017 School/Department/Area Page 10 of 11
Plane (RegNo, ModelNo*)
Step 5: Manytomany relationships
ModelTechnician (ExpertIn) is a manytomany relationship.
So, we introduce a new relation to represent this relationship.
Primary keys of participating entities together become the new primary key of this relation. ExpertIn (SSN*, ModelNo*)
Step 6: Multivalued attributes.
None here.
But, for example, if we have a clause that states “Technicians can have multiple trade qualifications”, then qualification become a multivalued attribute in Technician entity. Then, that has to be resolved in this step.
Step 7: Highdegree relationships.
We have a ternary relationship in this model (TestEvent), that associates a Test, a Technician, and a Plane.
In this case, we introduce a new relation to represent this relationship.
Primary keys of ALL participating entities together become the new primary key of this relation. Also, add other attributes describing the TestEvent become simple attributes of this relation.
TestEvent (TestNo*, SSN*, RegNo*, Date, Score, Hours)
So, final relational database schema is:
Model (ModelNo, Capacity)
Technician (SSN, name, address, phone number, and salary) Test(TestNo, name, MaxScore)
Plane (RegNo, ModelNo*)
ExpertIn (SSN*, ModelNo*)
TestEvent (TestNo*, SSN*, RegNo*, Date, Score, Hours)
School/Department/Area
Document: ER Model Tute 2 V2.0.docx Author: Santha Sumanasekara Save Date: 25/03/2017 Page 11 of 11