School of Science/ Computer Science and Information Technology
ISYS3412 Practical Database Concepts Week 5: Tute/Lab – Entity Relationship Modelling – Part 2
Semester 1 2020
1 Objective
The objectives of this tute/lab session are:
• Learn about design choices you make while building an entity-relationship model;
• Learn how to build an entity-relationship 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 a simple attribute or a composite attribute? Is it a simple attribute or a multi-valued attribute? Is it an entity or a relationship? Is it a one-to-many relationship or a many-to-many 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 straight-forward 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 many-to-many relationship between “student” and “course”, as follows.
In this model, we denote “semester” and “marks” as relationship attributes of the “enrols” relationship.
Document: Week 5 TuteLab 2 V1.0.docx Author: Santha Sumanasekara Save Date: 19/03/202020 School/Department/Area Page 1 of 7
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 one ternary relationship. We always try to avoid having higher-degree 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
Document: Week 5 TuteLab 2 V1.0.docx Author: Santha Sumanasekara Save Date: 19/03/202020 School/Department/Area Page 2 of 7
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
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: Week 5 TuteLab 2 V1.0.docx Author: Santha Sumanasekara Save Date: 19/03/202020 School/Department/Area Page 3 of 7
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.
2. Is there a weak entity? If so, give its name, partial key and the identifying relationship.
3. What constraints do the partial key and the identifying relationship of the weak entity
specified in this diagram?
4. List the multiplicity of each of relationship in this diagram. Justify your choices.
5. Identify a major shortcoming of this model. What changes do you propose to overcome this
issue.
2.2 CompanyERModel
Consider the Company ER model we discussed in the lectures.
School/Department/Area
Document: Week 5 TuteLab 2 V1.0.docx Author: Santha Sumanasekara Save Date: 19/03/202020 Page 4 of 7
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.
2. Present your solution as an entity-relationship model. You are not required to draw the full diagram, only the changes you made to the above diagram will be sufficient.
3. Identify any other limitations in the above model.
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.
Document: Week 5 TuteLab 2 V1.0.docx Author: Santha Sumanasekara Save Date: 19/03/202020 School/Department/Area Page 5 of 7
1. Identify a misrepresentation of facts in this ER model.
2. Re-draw the ER diagram to address this shortcoming.
2.4 MappingERmodeltoRelationalDatabaseModel
Consider the following ER model developed to represented the Essendon airport operations, depicted in Q5 (last tutorial).
Document: Week 5 TuteLab 2 V1.0.docx Author: Santha Sumanasekara Save Date: 19/03/202020 School/Department/Area Page 6 of 7
Map this E-R 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 partially-built schema at the end of each step. Indicate the primary key (underlined) and foreign key (with an asterisk) in each relation.
Document: Week 5 TuteLab 2 V1.0.docx Author: Santha Sumanasekara Save Date: 19/03/202020 School/Department/Area Page 7 of 7