Download Report

ACS-2814
Lab 5
June 4, 2014
Create an Entity Relationship Diagram (ERD) in Visual Paradigm
1. You can do this lab elsewhere if you have the facilities. You must hand in or email the lab result
before the lab period ends.
2. Start VP-UML and select a workspace folder for this lab.
3. Select File > New Project to create a project and name it Bus Route Management.
4. Create an Entity Relationship Diagram: Right click on Entity Relationship Diagram in
Diagram Navigator and select New Diagram from the popup menu.
5. A blank ERD will be created. Name the diagram Bus Route Management at the top left corner
of the diagram, and press Enter.
The ERD you will create is for a system that manages bus routes:
 For each bus route there is an identifier and a fare to be charged.
 When a bus travels the route there will be many places (bus stops) where it stops to let
people on or off.
 Each bus stop has an identifier and a name.
 A bus stop may be on more than one bus route.
 Typically, several times a day, a bus is scheduled to travel a bus route. Each schedule
must list the time when a bus will start on the route (departure time) and when it will
reach the destination (arrival time).
Page 1
ACS-2814
Lab 5
June 4, 2014
6. We start by creating the first entity type Route. Select Entity in the diagram toolbar and click
on the diagram to create it. Name the entity type Route and press Enter to confirm.
7. Create columns in Route. Let's start with a primary key. Right click on Route and select New
Column from popup menu.
8. Enter +id : varchar(10) and press Enter. Note the + sign means that the column is a primary
key. Varchar is a data type and 10 is the length.
9. Enter fare : float and press Enter, then press Esc to not create another column.
Page 2
ACS-2814
Lab 5
June 4, 2014
10. Create a many-to-many relationship & a new entity type Stop: A bus route has many bus stops,
and a stop can be on many routes. Therefore, there is a many-to-many relationship between
Route and Stop. Move the mouse pointer over Route and press on the resource icon: Many-toMany Relationship.
11. Drag it out and release the mouse button. Name the new entity Stop, You will see another entity
type Route_Stop is automatically created between Route and Stop, with a foreign key, RouteId,
referencing Route.
12. Create the following columns in Stop:
Key
Name
PK
id
int(10)
name
varchar(255)
Page 3
Type
ACS-2814
Lab 5
June 4, 2014
13. The diagram should now be the following - note the two foreign keys in Route_Stop, and its
composite primary key.
14. A route will be travelled many times. Create an entity Schedule from Route with a one-to-many
relationship. Move the mouse pointer to Route, Make use of the resource icon: One-to-Many
Relationship to create entity Schedule.
15. Create the additional columns in Schedule:
Key
PK
Name
Type
id
int(10)
departure
date
arrive
date
Page 4
ACS-2814
Lab 5
June 4, 2014
16. Create a note on your diagram - type your name and the date into it. Print your ERD and hand
it in before the lab ends, or, create an image and email the image to your instructor before the
lab ends.
17. Create the database in MS Access for the design you have just created. Include a relationships
diagram where you enforce referential integrity.
Populate your tables with the following information:
 Suppose we have two routes:
 PP to UW, $5.00
 UW to BB, $7.50

Suppose we have five stops:
 1, Polo Park
 2, University of Winnipeg
 3, Blue Bomber Stadium
 4, 777 Portage Avenue
 5, 1400 Pembina Hwy

Suppose we have the schedules:
Id
1
2
3


Depart
9:00am
11:00am
1:30pm
Arrive
10:00am
12:30pm
3:00pm
Route id
1
1
2
Suppose the bus stops for route 1 are: 1, 4, 2
Suppose the bus stops for route 2 are: 2, 5, 3
18. Hand in or email the page from Step 16 and email the database to the 2814 gmail account.
Page 5