School of Science/ Computer Science and Information Technology
ISYS3412 Practical Database Concepts Week 4: Tute/Lab – Entity-Relationship (ER) Model
Semester 1 2020
Objective
The objectives of this tute/lab session are:
• Learn about the entity-relationship model;
• Learn how to build an entity-relationship model;
• Learn how to apply cardinality and participation correctly;
• Learn how to use LucidChart to draw ER models
Preparation Tasks
Task 1: Create an account with LucidChart
For this activity you are required to have an account in LucidChart application. LucidChart is a cloud-based solution for diagramming. It is a browser-based application, which is compatible with all modern browsers, including Firefox, Chrome, and Safari.
If you have been using LucidChart in other courses (such as Software Engineering Fundamentals) you can use your existing account. If not, create a free academic account using your RMIT email address. Please be aware that there are two main restrictions with the free academic accounts:
Only ten active diagrams at a time
Storage space of 100mb.
However, a free account will suffice for the requirements of this course.
You can create an academic account at: https://www.lucidchart.com/pages/usecase/education
If you haven’t used it before, perhaps it is worth investing some time watching the following YouTube video tutorial, which covers the basic diagramming tasks with LucidChart.
Task 2: Build your own Shapes Library for ER Modeling using UML Notation.
LucidChart has an ER Modeling library for ER modeling. However, it uses Crow’s Foot Notation. In this course, we use UML notation. Therefore, you may save a lot of time if you have your own Shapes Library.
Follow these steps.
Step 1: Log in to LucidChart and open your Documents Folder.
School/Department/Area
Document: Week 4Tute-Lab V1.0.docx Author: Santha Sumanasekara Save Date: 19/03/2020 Page 1 of 9
On the above screen, click on the “Down Arrow” button next to “+ Document” button. Then it will open Standard Templates. Click on “UML” in the Template Categories list. From the choice of UML templates, choose “Blank UML” template.
This will open your new editing window (on a new tab on your browser).
Science of Science/ Computer Science and IT
Document: Week 4Tute-Lab V1.0.docx Author: Santha Sumanasekara Save Date: 19/03/2020 Page 2 of 9
You will see a number of Shapes libraries on the left-hand pane. For these labs and assignment work, you will only require Standard Shapes and UML Class Diagrams library. You can hide excess libraries by clicking the X on top right corner of these libraries. Once clean-up is done, your left pane will look like the above.
From UML Class Diagram Collection, drag the Interface shape (see the circled icon on the above screenshot) into Editing area. Do that twice.
Remove the default text within each of these rectangles.
Then, hover the mouse over the edges of a rectangle. This will show you’re the connector points (denoted as a red dot). Once it is highlighted, drag it to the next rectangle. This will draw a connector line between two rectangles.
Once the connection is completed, highlight the line, which brings in Line Dialog box. Click on “Add Multiplicities” button within this dialog box. By default, that will add “0 . . *” multiplicity to both ends of the relationship.
Document: Week 4Tute-Lab V1.0.docx Author: Santha Sumanasekara Save Date: 19/03/2020 Science of Science/ Computer Science and IT Page 3 of 9
You will need one basic rectangle shape for relationship attributes. Choose “Simple Class” rectangle from the UML Class Diagram Library.
Now you have four elements in your editing area.
• Two Interface shapes, which we will use for Entities.
• One connector line which we use for relationships (One line type will be sufficient for our
library. When we generate diagrams, we can edit multiplicity for each relationship by
clicking on “Add Multiplicities” button)
• A Simple Class shape, which will be used for relationship attributes.
Then, click on “+ Shapes” button on left pane, to bring up “Manage Library” dialog box.
On “Manage Library” dialog box, scroll down to the bottom of the list and add a new Custom Library called “My ER Tools”. And then click on “+” sign next to the new label. Then, Save.
The new library (still empty) should appear on the Shapes Libraries area.
Document: Week 4Tute-Lab V1.0.docx Author: Santha Sumanasekara Save Date: 19/03/2020 Science of Science/ Computer Science and IT Page 4 of 9
Next, drag and drop the shapes from your work area into this newly created library. Interestingly, rectangles will appear as small icons within the library, but, the connector line does not appear as a line, even if it does exist. You will see it when you hover the mouse over the “My ER Tools” library area. (I’d guess this is a bug in LucidChart).
Your library is now ready.
Go back to Documents list (in the previous tab on your browser) and delete the (untitled or called Blank UML) diagram. Or, if you wish, rename it and use it for further activities.
Why don’t we use built-in ER Modeling Template?
Yes, you can use it. However, then you have to use Crow’s Foot notation to denote multiplicity. In this course we use UML notation (as 0 . . N, 1 . . N, etc). The lines in built-in ER modelling template do not have this functionality (i.e. it won’t have “Add Multiplicities” button for connectors).
Activity 1: Using LucidChart to draw ER diagrams.
The objective of this activity is to familiarize yourself with the LucidChart interface. You will use “My ER Tools” library you prepared in the above preparation activity.
Task 1: Draw yourself the ER diagram used to represent the conceptual design for Company Database (that was used in the week 3 lecture).
The complete diagram comprises of three strong entities, one weak entity, one relationship attribute, and several relationships. Your final diagram should be similar to the following.
Document: Week 4Tute-Lab V1.0.docx Author: Santha Sumanasekara Save Date: 19/03/2020 Science of Science/ Computer Science and IT Page 5 of 9
Step 1:
Drag and drop “Entities” from your “My ER Tools” into working area.
Step 2: (optional):
Use “Line Colour” and “Fill Colour” buttons on top toolbar to change look-and-feel of the entities. Colour coding (of entities) is useful when your ER model is big and complex and have entities belong to different sub-sections of the model.
Step 3:
Hover the mouse over the edges of rectangles (entities) to highlight connecting points and then drag them to connecting entities. If you connect them to an incorrect entity (or incorrect edge of an entity) simply right click on the connector line and delete it.
When you draw the first line, it may (or may not, depend on your settings) appear as an arrow. If it appears as an arrow, go to top tool bar, and change both end points to “None”. See below.
Once you set both end points as “None” for your first line, make it as your default line type. Right- click on the line and choose “Set Default Style”.
Document: Week 4Tute-Lab V1.0.docx Author: Santha Sumanasekara Save Date: 19/03/2020 Science of Science/ Computer Science and IT Page 6 of 9
Step 4:
Set Multiplicity constraints (both cardinality and participation) for each end of connector line. Click on the line first, then click on “Add Multiplicities” button.
Step 5:
Drag and drop “Relationship Attribute” shape onto the working area. Then, connect it to the corresponding relationship line. For that, use a simple arrow shape from “Standard” shapes library. Then, change its settings to “no arrow heads” and “broken line” on top tool bar.
Step 6:
Give your diagram a suitable title, say “Company ER Model”. Click on the default title of the diagram (“Blank UML Model”) on to left corner of your browser tab and enter the new title. Finally, download the diagram as a PDF file into your local computer.
FileàDownload As.
Step 7:
Open another new blank UML model and play around with other settings and options available on the interface.
Also, try with other templates.
Science of Science/ Computer Science and IT
Document: Week 4Tute-Lab V1.0.docx Author: Santha Sumanasekara Save Date: 19/03/2020 Page 7 of 9
Activity 2: ER Modeling Exercise.1
1. [Easy] For each of the following description, draw an ER diagram showing entities, attributes, and relationships and their multiplicity. Where necessary state any assumptions.
a. Customers identified by a name and have an address. They purchase items. Items are identified by a number and have a colour. The quantity bought of each item is recorded.
b. 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.
c. A real estate agency keeps track of rental properties their agents manage. Each property has a unique address and other attributes, such as no. of bedrooms, no. of car spaces and type (unit |house| semi-detached). Each property is owned by one or more landlords. Landlords are uniquely identified by their email address. Each property is assigned to an agent. At any given point in time, there can be only one active rental contract but, it is required to retain all expired contracts as well. There could be one or more tenants party to a contract. A contract has a unique contract number, monthly rental, bond amount and start and end dates.
2. [Easy] The officials at Essendon Airport have decided that all information related to the airport should be organized using a DBMS, and you’ve been hired to design the database. Your first task is to organize the information about all the airplanes that are stationed and maintained at the airport. The relevant information is as follows:
a. Every airplane has a registration number, and each airplane is of a specific model.
b. The airport accommodates a number of airplane models, and each model is
identified by a model number (e.g., DC-10) and has a capacity and a weight.
c. A number of technicians work at the airport. You need to store the name, SSN,
address, phone number, and salary of each technician.
d. Each technician is an expert on one or more plane model(s), and his or her
expertise may overlap with that of other technicians. This information about
technicians must also be recorded.
e. The airport has a number of tests that are used periodically to ensure that airplanes
are still airworthy. Each test has a Civil Aviation Safety Authority (CASA) test
number, a name, and a maximum possible score.
f. The CASA requires the airport to keep track of each time that a given airplane is
tested by a given technician using a given test. For each testing event, the information needed is the date, the number of hours the technician spent doing the test, and the score that the airplane received on the test.
3. [Easy] Create an ER model for each of the following descriptions:
a. A large organization has several parking lots, which are used by staff.
b. Each parking lot has a unique name, location, capacity, and number of floors (where
appropriate).
c. Each parking lot has parking spaces, which are uniquely identified using a space
number.
d. Members of staff can request the sole use of a single parking space. Each member
of staff has a unique number, name ,telephone extension number, and vehicle license number.
1 Some questions were adopted from following text books: (1) Database Management Systems by Raghu Ramakrishnan and Johannes Gehrke, McGraw Hill Publications; (2) Database Systems: A Practical Approach to Design, Implementation and Management by Thomas Connolly and Caroline Begg, Pearson Education.
Document: Week 4Tute-Lab V1.0.docx Author: Santha Sumanasekara Save Date: 19/03/2020 Science of Science/ Computer Science and IT Page 8 of 9
e. Represent all the ER models described in parts (a), (b), (c), and (d) as a single ER model. Provide any assumptions necessary to support your model.
4. [Moderate] Create an ER model to represent the data use by the library.
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.
5. [Hard] Read the following case study, which describes the data requirements for a DVD rental company. The DVD rental company has several branches throughout the USA. The data held on each branch is the branch address made up of street, city, state, and zip code, and the telephone number. Each branch is given a branch number, which is unique throughout the company. Each branch is allocated staff, which includes a Manager. The Manager is responsible for the day-to-day running of a given branch. The data held on a member of staff is his or her name, position, and salary. Each member of staff is given a staff number, which is unique throughout the company. Each branch has a stock of DVDs. The data held on a DVD is the catalog number, DVD number, title, category, daily rental, cost, status, and the names of the main actors, and the director. The catalog number uniquely identifies each DVD. However, in most cases, there are several copies of each DVD at a branch, and the individual copies are identified using the DVD number. A DVD is given a category such as Action, Adult, Children, Drama, Horror, or Sci-Fi. The status indicates whether a specific copy of a DVD is available for rent. Before hiring a DVD from the company, a customer must first register as a member of a local branch. The data held on a member is the first and last name, address, and the date that the member registered at a branch. Each member is given a member number, which is unique throughout all branches of the company. Once registered, a member is free to rent DVDs, up to maximum of ten at any one time. The data held on each DVD rented is the rental number, the full name and number of the member, the DVD number, title, and daily rental, and the dates the DVD is rented out and date returned. The rental number is unique throughout the company.
a. Identify the main entity types of the DVD rental company.
b. Identify the main relationship types between the entity types described in (a) and
represent each relationship as an ER diagram.
c. Determine the multiplicity constraints for each relationship described in (b).
Represent the multiplicity for each relationship in the ER diagrams created in (b).
d. Identify attributes and associate them with entity or relationship types. Represent
each attribute in the ER diagrams created in (c).
e. Determine primary key attributes for each (strong) entity type.
f. Using your answers (a) to (e) attempt to represent the data requirements of the
DVD rental company as a single ER diagram. State any assumptions necessary to support your design.
Document: Week 4Tute-Lab V1.0.docx Author: Santha Sumanasekara Save Date: 19/03/2020 Science of Science/ Computer Science and IT Page 9 of 9