TASK A and TASK B
Assumptions – Task A:
1. Each Service is assigned exactly one Bus and one Driver.
2. A Bus can be used for multiple Services, but not at the same time.
3. A Driver can operate multiple Services on different dates or time slots, but only one at a time.
4. Each Route passes through multiple Stops, and each Stop can belong to multiple Routes.
5. The order of Stops in a Route is maintained using a SequenceOrder attribute in RouteStop.
6. Each Bus belongs to exactly one Depot.
7. A Bus may support multiple fuel types; this is modeled using the BusFuelType table.
8. Each Payslip is generated monthly for a single Driver.
9. Each Accident is linked to exactly one Service and may have multiple Repairs.
10. Depot amenities are stored as a comma-separated list (not normalized).
Assumptions – Task B (Contractor Extension):
1. ContractorDrivers are modeled in a separate table from full-time Drivers to distinguish their employment type.
2. Each ContractorDriver is affiliated with exactly one Agency.
3. ContractorDrivers receive Payslips similar to full-time Drivers, with DailyRate and DistanceRate used instead of HourlyWage.
4. Either DriverID or ContractorID is filled in the Payslip table, but not both.
5. ContractorDrivers are not required to undergo safety training or medical checkups.
TASK C
Analysis 1: Bus Maintenance Records in May 2025 (City Depot)
To determine which buses from the “City” depot underwent maintenance in May 2025, the following ERD-based procedure is used:
1. Use the Depot table to identify the DepotID for the depot with Location or Name = “City”.
→ In our data, "City" corresponds to DepotID = 1.
2. Use the Bus table to find all buses where DepotID matches the City depot, where DepotID = 1.
→ This includes BusID: 101, 102, and 104.
3. Use the Maintenance table to select records where the BusID belongs to one of those buses and the MaintenanceDate falls within May 2025. The following maintenance records meet the condition:
BusID 101 → Maintenance on 2025-05-10 and 2025-05-30
BusID 102 → Maintenance on 2025-05-20
BusID 104 → Maintenance on 2025-06-05 (excluded as not in May)
Therefore, the buses from the City depot that had maintenance in May 2025 are: Bus 101 and Bus 102, with three maintenance activities in total.
Entry Sets Used: Depot, Bus, Maintenance
Sample Data: We generated at least five records for each of these tables, linking Depot, Bus, and Maintenance accordingly.
In conclusion, based on the ERD, this method allows a manager to identify all maintenance activities for the current month within a specific depot.
Analysis 2: Accident Frequency and Repair Cost by Route
To evaluate which route has the highest number of accidents and the associated repair cost, we follow these steps based on the ERD:
1. From the Route table, retrieve all RouteIDs.
→ In our data, we have RouteID 301 (Route R1) and 302 (Route R2).
2. Use the Service table to find all services assigned to each Route.
→ Route 301 has ServiceIDs 401, 402, 405; Route 302 has ServiceIDs 403, 404.
3. Use the Accident table to count how many accidents occurred for each Service.
→ Service 401: 2 accidents (AccidentID 501, 504)
→ Service 402: 1 accident (AccidentID 502)
→ Service 403: 1 accident (AccidentID 505)
→ Service 404: 1 accident (AccidentID 503)
4. Use the Repair table to calculate the total cost of repairs associated with those accidents.
→ Repairs for R1: $500 (501) + $1200 (502) + $450 (504) = $2150
→ Repairs for R2: $600 (505) + $300 (503) = $900
By grouping the accidents by Route and aggregating the count and repair costs, we can determine which route is most incident-prone and its financial impact.
Entity Sets Used: Route, Service, Accident, Repair
Sample Data: We generated at least five records for each table to reflect meaningful relationships like multiple services per route, accidents per service, and repairs per accident.
In conclusion, based on the sample data, Route 301 had the highest number of accidents (3 total) and accumulated a total repair cost of $2,150. This suggests that Route R1 may require further investigation or additional safety measures.
TASK D
Five methods of controlling data integrity for the Bus relation
1. Entities' Integrity
BusID, the primary key, must be unique and not NULL.
This ensures that each bus record has a unique identity.
2. Domain Integrity
The NumberOfSeats attribute requires a positive value.
For example, a constraint such as CHECK (NumberOfSeats > 0) ensures realistic data entry.
3. Reference Integrity
DepotID is a foreign key in the Bus table that refers to the Depot database.
The Depot. DepotID must be the value of the DepotID field.
4. Honour as defined by the user
To enforce CHECK (FuelType IN ('Diesel', 'Electric', 'Hybrid')), the FuelType in the BusFuelType database must be one of the three designated, allowed values: 'Diesel', 'Electric', or 'Hybrid'
5. Integrity of Transactions
When adding a new maintenance record for a bus, the transaction should include the bus's status update as well as the maintenance data. This guarantees the system state's atomicity and consistency.
TASK E
The new ER model lists drivers and payments in the Driver and Payslip tables. If HR or management often generates pay reports with driver names, influencing performance, the system must integrate the two databases.
1. Normalised Table Structure – Fully Normalised (3NF)
The Driver database includes driver data, whereas the Payslip table contains normalised pay cheque data. Foreign key DriverID connects these tables. This split saves data duplication and quickly updates future joins for driver name changes.
2. Denormalised Table Structure – With Redundant Data
DriverName appears in denormalised Payslip tables immediately. This field transfers drivers' names from Driver databases to payslips. Reporting without a join saves time but may produce data inconsistencies if the driver's name changes.
Pros
1. Improved Query Performance
Reduces JOIN operations when generating payroll reports.
2. Simplified Report Generation
Makes it easier to retrieve and export payslip data with names.
3. Historical Data Stability
Retains the driver’s name as it was at the time of payment, even if it changes later.
Cons
1. Data Redundancy
Driver name is stored in multiple locations, which violates 3NF.
2. Storage Usage
For large batches of records, redundant text fields will increase the storage volume.