Data Modeling using Draw.io

BTM311 Asgn B (Data Modeling using Draw.io): CityTrain Transit System (CTTransit)

“Cityville is home to millions of people: many use our CityTrain Transit (aka CTTransit) system every day. Our vision is to increase

ridership due to innovative use of technology”, said Darren Winsure, general manager of the Transportation department. “We are

replacing the traditional ‘ticket’ with a computer based ‘pass’ system.” Credit-card sized thin plastic passes (with a machine-readable

bar-code that contains the pass’s ID number), will account for all sales. Passengers will be able use money-accepting machines to

increase any pass’s credit-balance: this balance will be used to pay for trips.”

“Each pass will be of one of eleven pass-types, as shown in the chart below.”

Pass type

code

Pass type

Description

Suggested

list price

Initial

balance amt

Reduced Fare

Indicator

Days before

expiry

Rcha Rechargeable: adult 0.00 0 N 9999999

Rchr Rechargeable: reduced-fare 0.00 0 Y 9999999

Rchg Rechargeable: adult-gift 90.00 100.00 N 9999999

Alda All-day-except-airport: adult 15.00 9999999 N 1

Aldr All-day-except-airport: reduced-fare 7.50 9999999 Y 1

Alwa All-week-except-airport: adult 50.00 9999999 N 7

Alwr All-week-except-airport: reduced-fare 25.00 9999999 Y 7

Mtha Monthly-except-airport: adult 120.00 9999999 N 28

Mthr Monthly-except-airport: reduced-fare 60.00 9999999 Y 28

T3da Tourist-3-day-except-airport: adult 30.00 9999999 N 3

T3dr Tourist-3-day-except-airport: reduced-fare 15.00 9999999 Y 3

“Passes can be purchased and/or charged-with-credit at any of 700 machines we call ‘Point of Sale’ (located in stations or in selected

convenience stores). We track each Point of Sale’s ID number, its station-code (if applicable), a description of its location, the name of

a contact-person (if applicable), a Y/N indicator of whether it is self-operated, and the date it was last replenished with blank passes.”

“Each of our 30 CTTransit stations is identified by a unique code. For each station, we also track its name, a text-description of its

location, its area in square meters and its quantity of public elevators. Each station will normally have within its perimeter one or

more platforms (where a passenger may board trains), as well as many Point of Sale machines and, on average, 15 turnstile machines.

For each turnstile, we track its CTTransit-created ID number, its station, a short text-description of its exact floor-location, and a short

indicator of whether it is an entry or exit turnstile.

Each CTTransit platform is located at one station and is identified by a CTTransit-created code. We also track the platform’s name

(eg. “Central CrossTown Westbound”), the date it was completed, its depth underground in meters, and an indicator of whether the

platform is two-way (Y) or one-way (N). Each of our 71 platforms is assigned to one train “line”: there are five such lines in the

CTTransit system. For each line, we track its identifying code, its name (eg. “Crosstown Line”), the date it was first opened, and the

target number of trips the line would ideally convey each day.

“Each passenger (passengers are not tracked) must have their own pass. If their pass has enough credit, the turnstile allows entry to its

station and we create a “trip” record on the database. If an employee is supervising the entry turnstile at that moment, we also record

their number. We do not track specific trains or which trains the passenger takes to their destination: instead we record on each trip

the entry and exit turnstiles: the database then determines and records the trip’s fare-charged (based on start and end-station) and subtracts

that fare from the pass’s balance. As each trip is completed, the pass’s balance is reduced until it has no balance left. If any pass has a

zero credit-balance or is expired, it does not allow passage through turnstiles: in fact, the turnstile will keep the pass for recycling.”

“If a passenger disputes the fares charged against their pass, we can print out a report, like for Pass 27311 and its three trips below.”

Pass

Number:

__27311_

Purchase

Date:

_2022-Aug-23_

Expiry

Date:

____________

Cancelled

Date:

_________

Point of

Sale #:

_1017_

POS location:

_QwikMart

102St.__

PassType

Code:

_Rcha_

Type Desc:

Rechargeable

: adult _

Selling Emp#: _547_

Emp name:

__Li__

Credit Bal:

_43.60_

Trip# Enter

Turnstile#

Enter

Date-Time

Exit

Turnstile#

Exit

Date-Time

Cancelled

Date-Time

Allowing

Emp#

Fare

Charged

3140917 1202 8/23/2022-19:57 0779 8/23/2022-20:27 3.20

3140923 2371 8/24/2022-20:12 2372 8/24/2022-20:13 0.00

3140929 0451 8/27/2022-20:25 0492 8/27/2022-21:09 3.20

Trips: 3 Total: 6.40

“To determine the fare to charge for any trip, the system must consult our table of possible fares. Each possible fare must have the

station-code for the trip-start, the station-code for the trip-end, and the date-time when the fare became effective: these three uniquely

identify each possible fare. We also track each fare’s regular fare-amount to be charged, the reduced fare-amount to be charged (used

for those with special passes), and the date-time this possible-fare will expire. For each combination of start-station and end-station,

we track one active possible-fare and the next-most-recent (expired) possible-fare. Any older fares are archived (not stored).

“As for administering our employees (200 in all), we assign an employee id-number and a supervisor to each new employee. A

sample chart of some employees is shown below:”

Emp# Emp

Name

Hire Date Job Desc Salary Supervisor

Emp#

89 Marks 2011/04/22 Group Leader 2900 100

102 Alou 2012/11/05 Train Operator 2500 89

101 Peters 2015/01/14 Train Operator 2200 102

547 Li 2018/12/02 Public Service Rep 1700 89

“We estimate that 500,000 passes will be created in the first month of service. After that, 400,000 new passes will need to be created

every month to replace expired or zero-credit passes. We estimate that, on average, the CTTransit system will serve 300,000 trips per

day. We will only keep records for the most recent 3 years of passes and trips: anything older will be archived (not stored).”

Requirements: Using Draw.io and the attached “CTTransit_StartingPoint” Draw.io file as your starting point, create an Entity Relationship Diagram

(ie, data model) of the CTTransit business, as shown in the case above. Starting from the provided “starting point” diagram,

completely and correctly specify all Entities, Relationships and Attributes as described in the lecture and lab materials on Entity

Relationship Diagramming. Include all relevant facts from the case into the model, including primary keys, volume estimates, data-

types for all fields, descriptive labels on relationships, and italicization of foreign key fields. Ensure that your relationship lines do

NOT cross or overlay, as this makes them hard to read. There is no need to create or assume any new business entities or attributes

other than those required for the case above. Your final diagram should have exactly TEN entities and, if printed (not required),

should fit on ONE 8.5” x 11” page. NOTE: In the “starting point” diagram, the number of lines with ‘x’s indicates the total number

of expected attributes (including all keys). Do NOT re-organize this diagram.

All work is to be done individually. Do not copy, in whole or in part, the work of others, including paper printouts, electronic files or

computer programs. Do not use the work of others as a starting point and then modify it. All work submitted under your name must

be yours and yours alone.

Recommended approach: A) Produce an interim model, by reviewing the information provided in the CTTRANSIT case above, concentrating on entities,

primary keys, volumes, relationships and foreign keys. Include all relevant facts. Use good, consistent naming conventions. Label

all entities, keys and relationships properly. You do not have to deliver this interim model.

B) Produce a final model by adding attributes to your interim model, taken from the case above. Because this work is BOTTOM-UP,

there is a risk that attributes will be added to the “wrong” entities. Use normalization rules to ensure attributes are added to the “best”

entities, including the creation of new entities if necessary. Use good naming conventions. Deliver this final model.

Marking Scheme: Asgn B: DATA MODEL

-Correct Entities (Well named, identified, volume estimates)

-Correct Relationships (Connected to correct entities, cardinalities (1-M? M-1?) correct, well described)

-Correct Attributes (Each in the correct entity, well named, correct data type, primary keys underlined and foreign keys italicized)

-Submitted file created using draw.io, named correctly, delivered electronically through eClass, on time.

Cheating and Plagiarism: All work is to be done individually. Do not copy, in whole or in part, the work of others, including paper printouts, electronic files or

computer programs. Do not use the work of others as a starting point and then modify it. All work submitted under your name must

be yours and yours alone.

The University of Alberta is committed to the highest standards of academic integrity and honesty. Students are expected to be

familiar with these standards regarding academic honesty and to uphold the policies of the University in this respect. Students are

particularly urged to familiarize themselves with the provisions of the Code of Student Behavior (online at

www.ualberta.ca/secretariat/appeals.htm) and avoid any behavior that could potentially result in suspicions of cheating, plagiarism,

misrepresentation of facts and/or participation in an offence. Academic dishonesty is a serious offence and can result in suspension or

expulsion from the University.

Submission Instructions: Look to the eClass Assignment B (ERD) object for detailed submission instructions.