ICT330: Drivers Motors Services and Repairs owns several workshops which carry out vehicle servicing and repair work: Database Management Systems Assignment, SUSS

Question 1

Drivers Motors Services and Repairs owns several workshops which carry out vehicle servicing and repair work. Each workshop is identified by a workshop code and has an address and a contact number. A system is required to support the activities of the workshops.

The company wishes to build a knowledge base consisting of online technical specifications and repair and servicing notes for various vehicle designs, identified by vehicle make, model and edition year. Whenever a vehicle arrives at a workshop, mechanics identify the vehicle design and check whether an entry for the design already exists in the knowledge base.

If it exists, new insights gained are added as repair or servicing notes. If there is no match, a new entry is first made for the vehicle design. The date of entry creation, date of each note added, a note description as well as the mechanic who adds the note are captured.

The company provides several service packages. Each service package is identified by a service package name, the cost of the package and a list of service items. Some service items may be offered in more than one service packages e.g., change engine oil with synthetic oil.

A service item is identified by service item code and has a name and a description. A vehicle visit to a workshop is identified by the vehicle registration number and time of visit.

Each visit may include vehicle servicing with a service package or without a package, in which  case, individually priced service items can be selected. A customer may also select a service package and then add on more individually priced service items.

A visit may also include one or more vehicle repair items. When a visit includes repair items, an overall repair labour cost is included to the total cost of the visit. A repair item is identified by a running number which is unique to a vehicle visit. A repair item has a description and a cost which is the total cost of parts used for the repair item.

The unit cost of parts and quantity of parts used for a repair item should be itemised in the customer bill. Each part is identified by a part code. Each part has a name, description and a unit cost. The charges incurred for a visit therefore, may include cost of a service package, cost of individually priced service items, cost of labour for repair work and cost and quantity of parts used in repair items, each itemised for billing and accounting purposes.

A team of mechanics, each identified by the team number is assigned to handle all servicing and repair works for a vehicle visit. A team is headed by a senior mechanic and consists of 2 or more other mechanics, all of whom are attached to the same workshop.

When a mechanic transfers to a new workshop, he joins a new team. Each mechanic is identified by a staff number, and is described by a name, date joined, and the dates he joins and leaves a team, where applicable. It is also necessary to record the headship of a team, that is, the date a senior mechanic starts to head a team and the date on which he steps down as head.

The system should record customer details such as customer name and contact and vehicle details such as make, model, edition, vehicle registration number, engine number and chassis number. Other details include the vehicle condition at time of visit (e.g., locations of dents and scratches), the mileage, the date and time the vehicle is handed over to the workshop and the date and time the vehicle is returned.

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

You are given the table:

RepairWork (vehicleRegNumber, dateRepair, vehicleModel, vehicleMake, countryEstablished, yearEstablished, problemCode, problemCategory, componentCode, problemStatus) vehicleRegNumber is the vehicle registration number of a vehicle sent for repair on a date, dateRepair.

The vehicle make refers to the vehicle manufacturer which is established in a country, the country established and in the year, year established. The vehicle manufacturer may produce many vehicle models. A vehicle model is unique to a vehicle manufacturer, that is, a vehicle model does not repeat across vehicle manufacturers.

Different models can have common components and common vehicle problems. Components are identified by component codes and vehicle problems are identified by problem codes. Some vehicle manufacturers provide a guide listing component against a set of problems that a component may contribute to. In general, a component may be a source of several vehicle problems and a vehicle problem can be caused by several components. Each problem has a category e.g., electrical, transmission etc.

A vehicle may have one or more vehicle problems. On a date of repair, the vehicle is matched to a vehicle model and make and the manufacturer guide is used to help identify faulty components. At the end of the repair process, the status for each vehicle problem is indicated as either fixed, temporarily fixed or unresolved.

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

(a) Formulate and list the functional and multi-valued dependencies. Do not give transitive functional dependencies.

(b) Draw a dependency graph and propose candidate key(s) for the table. Show composite keys in brackets.

(c) (i) For each MVD in answer to Q2(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 tables and explain whether each derived table at each iteration is already in BCNF and 4NF (if applicable).

(d) Assemble your BCNF and 4NF tables and constraints clearly by giving each table and the referential integrity constraint(s), if any, in the forms described below: TABLE-NAMEx(col-name1, col-name2, …, col-name n)

indicate (PK) against the primary key and
indicate (FK) against the foreign keys, if any, together with the foreign key

constraint in this form:

TABLE-NAMEx.col-namex1 …col-namexn must exist in
TABLE-NAMEy.col-namey1…col-nameyn

Question 3

(a) (i) Transform the ERD in Figure Q3(a)(i) into a logical model.

(ii) Refer to the description of the columns and relationships in Table 3(a), and then write the appropriate SQL statements to create the tables, derived from the ERD in Figure Q3(a)(i). You must use the same table names and the same column names as given in Figure Q3(a)(i) or your statements will be marked as incorrect. List your SQL statements in the order that they should be executed.

(iii) Use the data in Figure Q3(a)(iii) to populate the tables created in Q3(a)(ii). List your SQL statements in the order that they should be executed.

(b) Formulate SQL queries to display the given resultsets:

(i) Components that are more likely to be problematic where problematic is defined as components that have been fixed or replaced at least twice, when repairs for all vehicles are considered.

(ii) Customer with their vehicle registration number, the number of times the vehicles have been sent for repairs, and the total cost for all the repairs made on each vehicle, ordered by descending number of times the vehicles have been sent for repairs and ascending customer name if the number of times the vehicles have been sent for repairs are the same.

(iii) The busiest day(s) on which the most number of vehicles came in for repair. No mark will be given to answers that sort and extract the first row as in general, it is possible that the answer has more than one row.

(iv) Customers and their vehicles currently in workshop but without component recorded for those vehicles yet.

(c) Write a trigger to implement the following actions:

Ensure that a vehicle has not been returned to a customer when additional
components are to be added.
Any faulty component can be repaired with the action ‘fixed’ at most twice and if it subsequently becomes faulty, it should only be replaced. After a component is replaced, it can be ‘fixed’ for another two times before it has to be replaced. A component can be adjusted as long as it is not been fixed twice, and it can be replaced without having it to be fixed twice first.
Print appropriate messages for successful and failure giving sufficient details in the error message regarding the success or failure. Test your triggers completely with these test cases:
Vehicle is already out of the workshop (returned to customer).
Component in a vehicle has never been replaced and has been fixed twice.
Component in a vehicle has never been replaced and has been fixed fewer than twice.
Component in a vehicle has been replaced and after the replacement, it has been fixed twice.
Component in a vehicle has been replaced and after the replacement, it has been
fixed fewer than twice.
Show screenshots of your test queries and output. State any assumptions you made in implementing the trigger.

Question 4

(a) Demonstrate the basic techniques of concurrency control and their application. Suppose a workshop for vehicle servicing and repair has 20 appointment slots available each day.

Suppose transaction T1 is executing a stored procedure to place a booking for an appointment, transaction T2 is executing a stored procedure to change appointment slot and transaction T3 is executing a stored procedure to cancel appointment.

What is the minimum isolation level that T1, T2 and T3 should be running at given that they can run at different isolation level? Justify your answer.

(b) Consider the tables in Question 3. Describe TWO (2) permissions that should be given to mechanics who needs to maintain repair records. Describe two permissions that should not be given to mechanics.

(c) Given Figure Q4(c), explain what the database recovery procedure will do for the transactions T1 to T5. Explain also how the transaction log help ensure the ACID properties of T1 to T5.

The post ICT330: Drivers Motors Services and Repairs owns several workshops which carry out vehicle servicing and repair work: Database Management Systems Assignment, SUSS appeared first on Singapore Assignment Help.