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

joan-benjamin
Download Presentation

Car Maintenance Example

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

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