Garage Management System: Expanded Scenario and Data

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:

$1.$ Develop an Entity$-$Relationship Diagram $($ERD$)$ representing the entities mentioned in the

scenario and their relationships. Ensure it adheres to normalization principles.

$2.$ Create tables in your chosen DBMS $($e$.$g$.,$ MySQL$)$ based on the ERD, defining appropriate data

types, constraints, primary$/$foreign keys, and relationships.

$3.$ Populate the database with realistic dummy data covering customers, vehicles, services,

invoices, inventory $($limited scope for this project$),$ and employees $($limited scope$).$ Ensure the data represents the diverse clientele, service offerings, and employee roles outlined in the

scenario.

$4.$ Develop code $($e$.$g$.,$ Python with pandas$)$ to read the Excel data, transform it if necessary, and

insert it into the database tables efficiently. Include error handling in your code.

Database Optimization:

$1.$ Identify frequently used columns in queries and create indexes on them to improve query

performance. Use code $($e$.$g$.,$ SQL queries$)$ to create and manage indexes.

$2.$ Evaluate the trade$-$offs between data redundancy and query performance for specific scenarios.

Justify any strategic denormalization you implement while maintaining data integrity. Use code

to implement the changes.

$3.$ Analyze slow queries using tools like EXPLAIN PLAN. Use code $($e$.$g$.,$ SQL queries$)$ to rewrite

inefficient queries with techniques like joins optimization and subquery elimination.

Automation Tasks:

$1.$ Implement automated data backups periodically $($e$.$g$.,$ daily$)$ using code $($e$.$g$.,$ shell scripts$)$ and

tools like mysqldump. Automate restoration scripts for potential disaster recovery.

$2.$ Develop code $($e$.$g$.,$ Python, SQL$)$ to automatically generate invoices upon service completion

based on service and parts data. Integrate with payment gateways for automated billing.

$3.$ Create triggers or stored procedures to track inventory levels and automatically generate

reorder points when stock falls below a threshold. Use code to automate purchase orders when

triggered.

Disaster Recovery:

$1.$ Set up database replication to a secondary server for redundancy and failover in case of primary

server failure. Use code $($e$.$g$.,$ mysqldump $+$ scripts$)$ to automate replication setup and

management.

$2.$ Explore high availability $($HA$)$ solutions like clustering for further resilience. Research tools like

Galera Cluster or DRBD for MySQL clustering and discuss their feasibility.

$3.$ Develop a comprehensive disaster recovery plan documenting steps to recover from various

scenarios $($e$.$g$.,$ hardware failure, data corruption$).$ Include database restoration, application

recovery, and data synchronization procedures.

Mark Allocation:

Database Design & Development:

$$ ERD clarity, completeness, and adherence to normalization principles

$$ Table structure, data types, constraints, and relationships

$$ Dummy data realism, quantity, and coverage of entities

$$ Data import code efficiency and error handling

Database Optimization :

$$ Indexing strategy and impact on query performance

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

$$ Query optimization techniques and performance improvement

Automation Tasks:

$$ Data backup & restore automation effectiveness and reliability

$$ Invoice generation code functionality and integration

$$ Inventory management triggers$/$procedures and purchase order automation

Disaster Recovery:

$$ Replication setup and management using chosen tools

$$ High availability considerations and potential solutions

$$ Disaster recovery plan comprehensiveness and recovery procedures