ER Diagram for Vehicle Fuel & Service Tracking

Design an ER Diagram (Logical Model) for “Vehicle fuel consumption & service reminder” application under following requirements

1. Register any number of Sri Lankan vehicles under unique identifier
2. System should be able to keep records about a. Cost per one litter on each refilling with number of litters count b. Gasoline Refilling point address c. Mileage d. Refilling type (patrol, diesel or any other like super diesel)
3. Service station records about a. Vehicle service date b. Next recommend service date or mileage Details of the evidence requirements are specified below:
4. Student should able to design Entity-Relationship Diagrams using normalization forms by analyzing the requirements for a given scenario as an individual project with the following aspects
5. The diagrams that are designed by the student should ensure the correct cardinality of a table in relation to another table
6. Students should use accurate database notation in the ER diagram.
7. Student should be able to develop ER diagrams using an ER modeling software tool
   (eg: MySQL Workbench )
8. Student should mention assumptions made during the development of the ER diagram in the documentation

ER Diagram for Vehicle Fuel Consumption & Service Reminder Application

This ER diagram models a system for tracking vehicle fuel consumption and service reminders for Sri Lankan vehicles.

Assumptions:

• A vehicle can have multiple fuel refills and services.
• A fuel refill happens at a specific gasoline station.
• The fuel type is standardized (e.g., "Petrol 92," "Diesel," "Super Diesel").
• Service recommendations are based either on date or mileage, not both simultaneously.

Entities and Attributes:

• Vehicle:

  • vehicle_id (PK, INT, Auto-increment) - Unique identifier for the vehicle.
  • registration_number (VARCHAR(20), Unique) - Sri Lankan vehicle registration number.
  • make (VARCHAR(50)) - Vehicle manufacturer (e.g., Toyota, Honda).
  • model (VARCHAR(50)) - Vehicle model (e.g., Corolla, Civic).
  • year (INT) - Year of manufacture.

• FuelType:

  • fuel_type_id (PK, INT, Auto-increment) - Unique identifier for the fuel type.
  • fuel_type_name (VARCHAR(50), Unique) - Name of the fuel type (e.g., Petrol 92, Diesel, Super Diesel).

• FuelRefill:

  • refill_id (PK, INT, Auto-increment) - Unique identifier for each refill.
  • vehicle_id (FK, INT) - Foreign key referencing Vehicle.
  • fuel_type_id (FK, INT) - Foreign key referencing FuelType.
  • refill_date (DATE) - Date of refilling.
  • cost_per_liter (DECIMAL(10,2)) - Cost of one liter of fuel.
  • liters (DECIMAL(10,2)) - Number of liters refilled.
  • mileage (INT) - Vehicle mileage at the time of refilling.
  • gas_station_address (VARCHAR(255)) - Address of the gas station.

• Service:

  • service_id (PK, INT, Auto-increment) - Unique identifier for each service.
  • vehicle_id (FK, INT) - Foreign key referencing Vehicle.
  • service_date (DATE) - Date of service.
  • next_service_date (DATE) - Recommended date for the next service (can be NULL).
  • next_service_mileage (INT) - Recommended mileage for the next service (can be NULL).

Relationships:

• Vehicle 1:N FuelRefill: A vehicle can have multiple fuel refills.
• Vehicle 1:N Service: A vehicle can have multiple services.
• FuelType 1:N FuelRefill: A fuel type can be used in multiple refills.

ER Diagram (Visual Representation):

    Vehicle *---1 FuelRefill
    (vehicle_id, registration_number, make, model, year)   (refill_id, vehicle_id, fuel_type_id, refill_date, cost_per_liter, liters, mileage, gas_station_address)

    Vehicle *---1 Service
    (vehicle_id, registration_number, make, model, year)   (service_id, vehicle_id, service_date, next_service_date, next_service_mileage)

    FuelType 1---* FuelRefill
    (fuel_type_id, fuel_type_name)                       (refill_id, vehicle_id, fuel_type_id, refill_date, cost_per_liter, liters, mileage, gas_station_address)

(Note: This is a textual representation. Use a tool like MySQL Workbench to create a visual ER diagram with proper notation, including primary keys, foreign keys, and relationship cardinalities.)

This design ensures data integrity and avoids redundancy by normalizing the data into separate tables with appropriate relationships. The use of foreign keys enforces referential integrity, ensuring that related records are consistent. The separate FuelType table avoids data duplication and allows for easy addition of new fuel types. The assumptions made clarify the scope and limitations of the model.