This content can't be printed from the browser. Use Export PDF with Pro for an offline copy.
Question 1
Brindle Repair stores details of technicians and repair jobs in a relational database. The tables Technician and Repair are shown below.
Technician table
| technicianId (PK) | technicianName |
|---|---|
| 1 | Aisha Khan |
| 2 | Ben Carter |
| 3 | Chen Wei |
| 4 | Dara Singh |
Repair records at Brindle Repair.
| repairId (PK) | technicianId (FK) | itemDescription | quotedCost |
|---|---|---|---|
| 11 | 3 | Laptop screen | 28 |
| 12 | 1 | Tablet battery | 65 |
| 13 | 3 | Phone charging port | 35 |
| 14 | 2 | Printer paper feed | 30 |
| 15 | 1 | Desktop fan | 42 |
| 16 | 2 | Router power supply | 18 |
In Technician, technicianId is the primary key. In Repair, repairId is the primary key and technicianId is a foreign key referencing Technician. The quotedCost field stores a whole number of currency units.
Outline why adding a new column to Repair is a data definition language (DDL) operation rather than a data manipulation language (DML) operation.
[2]
State two DML statements, other than SELECT and UPDATE, that staff could use to change the records in Repair.
[2]
Construct an SQL statement to change quotedCost to 45 for the repair with repairId 14. No other repair must be changed.
[3]
Construct an SQL query to display technicianName, itemDescription and quotedCost for repairs with a quotedCost greater than 30. Use a join between the two tables and display the results in descending order of quotedCost.
[5]
Question 2
Ternway Deliveries stores parcel details in the Parcel table shown below. Each parcel is assigned to a depot, and massKg stores its mass as a whole number of kilograms.
Parcel table; parcelId is the primary key and massKg is indexed.
| parcelId (PK) | depotName | destinationDistrict | massKg (indexed) |
|---|---|---|---|
| P001 | East Central | Riverside | 2 |
| P002 | North Depot | Hillview | 1 |
| P003 | East Hub | Riverside | 4 |
| P004 | West Depot | Lakeside | 6 |
| P005 | East Central | Old Town | 5 |
| P006 | South Depot | Hillview | 3 |
| P007 | East Hub | Lakeside | 2 |
| P008 | Central Depot | Riverside | 7 |
The primary key is parcelId. An additional index is maintained on massKg.
Outline why maintaining the index on massKg can add processing work when staff correct a parcel's mass.
[2]
State the SQL statement used to create a new table and the SQL statement used to remove a table's definition and all its records.
[2]
Construct an SQL query to display the destinationDistrict values for parcels with massKg between 2 and 5, including both endpoints. Each destination district must appear only once.
[3]
Construct an SQL query using GROUP BY and HAVING to display one row for each depotName that begins with 'East'. Display only depotName and arrange the results in ascending alphabetical order.
[5]
Question 3
Brindle Repairs maintains a relational database of technicians and repair jobs. The tables show an extract from the database; technicianID in REPAIR is a foreign key referencing TECHNICIAN, and hours records the time spent on a repair.
TECHNICIAN
TECHNICIAN table extract
| technicianID (PK) | technicianName |
|---|---|
| T101 | Aisha Khan |
| T102 | Daniel Wu |
| T103 | Elena García |
| T104 | Marcus Bell |
REPAIR
REPAIR table extract
| repairID (PK) | technicianID (FK) | hours | fee | status |
|---|---|---|---|---|
| R001 | T101 | 3 | 85 | Ready |
| R002 | T102 | 2 | 120 | In progress |
| R003 | T101 | 4 | 150 | Ready |
| R004 | T103 | 6 | 95 | In progress |
| R005 | T102 | 3 | 110 | Ready |
| R006 | T103 | 2 | 175 | Ready |
| R007 | T104 | 5 | 70 | In progress |
Construct an SQL query to list repairID and fee for repairs whose status is 'Ready', in descending order of fee.
[3]
Construct an SQL query using both tables to list each technician's name and total hours spent on repairs. Include only technicians whose total hours are at least 6. Each technician must appear in a separate result row.
[5]
The manager frequently reads a summary of repair hours. A materialized view stores this summary and is refreshed overnight. Describe how this view affects the performance and currency of the information available to the manager.
[3]
Two staff members concurrently apply different increases to the fee for the same repair. Each transaction reads the existing fee, calculates a new fee and writes it back. Outline how isolation can prevent one increase from being lost.
[3]
The Ultimate All-in-One
IB Platform
All content on this website has been developed independently from and is not endorsed by the International Baccalaureate Organization. International Baccalaureate and IB are registered trademarks owned by the International Baccalaureate Organization.