1 / 16

Car Maintenance Example

Car Maintenance Example. Solution. CAR. Tables. PART. MAINTENANCE_RECORD. Tables (cont’d ). REPAIR. Questions. Display all the part names and their prices where the cost of the part is greater than GhȻ20.00

Télécharger la présentation

Car Maintenance Example

E N D

Presentation Transcript

1. Car Maintenance Example Solution

2. CAR Tables PART

3. MAINTENANCE_RECORD Tables(cont’d) REPAIR

4. Questions • Display all the part names and their prices where the cost of the part is greater than GhȻ20.00 • List the car registration and model details and part numbers for all cars where the model year is 2007, where an inspection was carried out after 01/03/2008, which resulted in a part being required for the repair.

5. Question 1: Solution

6. Question 2: Solution STEP1: • The first part of the query states that we need the attributes REGISTRATION and CAR_MODEL from the CAR relation. • Further, we are only interested in cars whose MODEL_YEAR is 2007

7. Question 2: Solution STEP1:

8. Question 2: Solution STEP2: • Next, the query requires information about inspections which were carried out after 01/03/2008. Information about inspections is stored in the MAINTENANCE_RECORD relation. • Since the query is not asking for specific attributes, we assume that the values of all attributes in the MAINTENANCE_RECORD is required. • Further, we must restrict the query to only tuples where the INSPECTION_DATE > 01/03/2008.

9. Question 2: Solution STEP2:

10. Question 2: Solution STEP3: • Next, perform a NATURAL JOIN on the query results from STEPs 1 & 2 with the common column (REGISTRATION) in both the CAR and MAINTENANCE_RECORD relations. • STEP3a: • In performing the NATURAL JOIN first perform the Cartesian Product on CAR and MAINTENANCE_RECORD relations. Prefix each attribute in the CAR and MAINTENANCE_RECORD relations with C and M respectively.

11. Question 2: Solution Cartesian Product: X CAR STEP3a:

12. Question 2: Solution SELECT only the rows for which the REGISTRATION values are equal STEP3b:

13. Question 2: Solution Perform a PROJECT on either C.REGISTRATION or M.REGISTRATION to the result in STEP3b and drop the prefix’s C or M in the final relation. STEP3c:

14. Question 2: Solution STEP4: • The query further requires that the information be restricted for cars where a part was needed for a repair. This information can be found in the REPAIR relation by looking for a PART_NO in the REPAIR relation, which corresponds to a specific INSPECTION_CODE in the MAINTENANCE_RECORD relation.

15. Question 2: Solution Perform a NATURAL JOIN with the common column (INSPECTION_CODE) in both the REPAIR and Query Result of STEP3c. STEP4: QueryResult

16. Question 2: Solution Finally, the original query requested that we list “the car registration, model details and part numbers.” This requires that we perform a PROJECT on the query result of STEP4. STEP5:

More Related