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, 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 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).

Answer rating: 100% (QA)

Lets break this down into the required steps making necessary assumptions to flesh out the scenario Assumptions Unique Identifiers Well assume that tr... View the full answer