ICT330 Database Management Systems Assignment

150 views 8:56 am 0 Comments October 18, 2023

Question 1 (30 marks)
National Intercity Train Services provides intercity travel by trains, and offers several routes, each identified by a route code. A route has a route name, a starting station, and a terminal station, and makes stops at various stations. A route is scheduled to run on different days of a  week, possibly at different times on those days of the week. On some days of a week e.g., on weekends, the route may run more than once a day. Each scheduled route is identified by a route code and a running number starting from 1 for each route. The estimated travel duration from the starting station to each station of a route is recorded.

Stations are identified by a station id. In addition, the name and address of the station are available to anyone checking on a route. The stations on a route are also given a running number starting from 1, sequenced in order from the starting station so that a route schedule with time reflected on the stations in sequence can be displayed. To be employed by National Intercity Train Services as a train driver, a person must first possess a car driving license. Upon employment, National Intercity Train Services trains its train driver and conducts qualification tests on the different train models that National Intercity Train Services purchases.

Each train model is identified by a model and a make, and includes the model specifications. Each train has a train number and a purchase reference number. A train driver must pass the qualification test for the train model of the train assigned for a journey. Each qualification test has a one year validity period. A test result and test details are recorded for each test. On the scheduled day of week, the actual time the train departs is recorded. When the journey reaches the terminal station, the time is again recorded.

Unfortunately, train incidences do happen, and National Intercity Train Services wants to keep track of them for maintenance scheduling as well as for driver retraining purposes. When an incident happens, an investigating officer is assigned to the case. The incident is given an incident number. The date and time of incident, a description and the incident status must be
recorded.

The station related to the incident, if applicable, is also recorded. The investigating officer should record how the station has contributed to the incident. Each staff employed by National Intercity Train Services has a staff id, name, gender, date of birth, start date of employment, a job title, and one or more educational qualification. The awarding institution and date of award are recorded for each educational qualification.

Construct a conceptual (ER) model from the statements of requirements to represent the data model, showing
(i) entities, with entities name, relevant attributes and identifier,
(ii) relationships with maximum and minimum cardinalities and relationship name.
State your assumption(s) for only data requirements that are not specified.