Garage Management System: Expanded Scenario and Data

$100$ Marks

Joe's Garage is a bustling family$-$owned business operating in Pretoria, South Africa, for over $20$ years.

They cater to a diverse clientele, from individual car owners to corporate fleets. However, their aging

Excel system struggles to manage:

$$ Growing customer base: Over $2,000$ customers with varying vehicle types and service histories.

$$ Complex service offerings: Including routine maintenance, repairs, diagnostics, performance

upgrades, and tire services.

$$ Expanding inventory: Parts, tools, and consumables across various brands and categories.

$$ Multiple employees: Mechanics, technicians, service advisors, and administrative staff with

varying roles and permissions.

$$ Financial tracking: Invoicing, payments, accounts payable$/$receivable$,$ and payroll management.

Data Expansion:

$$ Customers: Include loyalty programs, contact preferences, vehicle purchase history, and service

preferences.

$$ Vehicles: Add VIN numbers, model years, mileage, fuel types, and service records with dates,

descriptions, and costs.

$$ Services: Expand with specific labor costs, parts used $($linked to inventory$),$ and warranty

information.

$$ Invoices: Include payment methods, discounts, taxes, and technician assigned.

$$ Inventory: Implement categories, suppliers, minimum$/$maximum stock levels, reorder points,

and purchase orders.

$$ Employees: Include roles, departments, salaries, work schedules, and access control levels.

Database Design & Development $(30)$:

$$ ERD clarity, completeness, and adherence to normalization principles $(5)$

$$ Table structure, data types, constraints, and relationships $(10)$

$$ Dummy data realism, quantity, and coverage of entities $(10)$

$$ Data import code efficiency and error handling $(5)$

Database Optimization $(25)$:

$$ Indexing strategy and impact on query performance $(10)$

$$ Denormalization justification and implementation $(\%)$

$$ Query optimization techniques and performance improvement $(10)$

$$

Automation Tasks $(20)$:

$$ Data backup & restore automation effectiveness and reliability $(8)$

$$ Invoice generation code functionality and integration $(6)$

$$ Inventory management triggers$/$procedures and purchase order automation $(6)$

$$

Disaster Recovery $(25)$:

$$ Replication setup and management using chosen tools $(8)$

$$ High availability considerations and potential solutions $(5)$

$$ Disaster recovery plan comprehensiveness and recovery procedures $(12)$