ICT330: National Intercity Train Services provides intercity travel by trains, and offers several routes, each identified by a route code: Database Management Systems Assignment, SUSS, Singapore

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.

Question 2 
National Intercity Train Services also employs mechanics to help service its trains. A mechanic is identified by mechanicId, a train is identified by a trainId and a service is identified by a serviceId. During a service, the action performed on a component of a train being serviced as well as recommendations, if any, are recorded. At the end of each train servicing, the supervising mechanic who supervises the mechanics during a servicing will perform a check to ensure the train is properly serviced.

A supervising mechanic is also a mechanic. You are given the relation:
TrainServicing(trainId, make, model, purchaseDate, serviceId, serviceDate, componentId, action, recommendation, mechanicId, name, dateJoined, supervising MechanicId) State your assumption(s) for only data requirements that are not specified.
(a) Formulate and list the functional and multi-valued dependencies.

(b) Draw dependency graph and propose candidate key(s) for the relation. Show each
composite key, if any, in brackets.

(c)
(i) For each MVD in answer to part a), state whether it is subsumed.
(ii) Normalise the table to BCNF and 4NF by applying the BCNF approach
covered in the course text.

Show how you apply the steps to arrive at the relations and explain whether
each derived relation at each iteration is already in BCNF and 4NF (if
applicable).

The post ICT330: National Intercity Train Services provides intercity travel by trains, and offers several routes, each identified by a route code: Database Management Systems Assignment, SUSS, Singapore appeared first on Singapore Assignment Help.