Question 1 

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 the 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 d. 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 the 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 the 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, job title, and one or more educational qualifications. 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 entity names, relevant attributes, and identifiers,

(ii) relationships with maximum and minimum cardinalities and relationship names.

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 mechanical, a train is identified by a trained, and a service is identified by a service.

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(trained, make, model, purchase date, serviceId, service date, component, action, recommendation, mechanized, name, date joined, supervisingMechanicId)

State your assumption(s) for only data requirements that are not specified.

(a) Formulate and list the functional and multi-valued dependencies.

(b) Draw a 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).

(d) Assemble your BCNF and 4NF relations and constraints clearly by giving each relation and the referential integrity constraint(s), if any, in the forms described below:

RELATION-NAMEx(col-name x1, col-name x2, …, col-name xn)
Where RELATION -NAMEx.col-namex1 …col-names must exist in
RELATION -NAMEy.col-namey1…col-name
 indicate (PK) against the primary key,
 indicate (FK) against the foreign keys, if any.
 indicate referential integrity constraint(s), if any, together with the child
relation in this form: