Auto Repair Database
Database design and implementation for an automobile repair shop management system
Project Role - Project Analyst
​​​
Project Year - 2023
​​​​
Project Type - Student Project (Group)
​​
Project Location - Santa Clara, CA​​​
Result
The group successfully produced key database design documents: an ERD and a Relational Schema. Furthermore, a functional SQL script was delivered, capable of creating the database structure and populating it with initial data for customers, automobiles, employees, parts, equipment, repairs, and estimates in a relational database system.
Overview
S
Situation
As part of a database management course, our team needed to design and implement a system for managing the operations of an hypothetical automobile repair business. The goal was to efficiently track customers, vehicles, repairs, estimates, employees, parts, and equipment.
T
Task
The primary task was to develop a comprehensive relational database solution. This involved understanding the implicitly presented business requirements, modeling the data relationships, defining the database structure logically and physically, and creating scripts to build and populate the database within a system like PostgreSQL.
A
Action
The team collaboratively designed an Entity-Relationship Diagram (ERD) to model entities like Customer, Automobile, Repair, Employee, and their relationships. This ERD was then translated into a detailed Relational Schema, defining tables, primary keys, foreign keys, and attributes. Finally, SQL scripts were written to create the necessary tables with appropriate constraints and insert sample data.
R
My Highlights
Database Modeling
-
Participated in group efforts to define entities and relationships, leading to the documented ERD and Relational Schema deliverables.
SQL Scripting
​
-
Worked with the team to translate the relational schema into functional SQL code, ensuring successful table creation and data insertion.
Ensured Project Completion
​
-
Actively engaged in the team process across all project phases, contributing to the successful delivery of all required database components.
Skills
SQL Scripting
The team developed SQL CREATE TABLE and INSERT INTO statements to implement the schema.
Collaborated Skill
Database / Data-Model Design
Designed ERD and relational schema translating business entities into tables, keys, and a full data model.
Collaborated Skill
Collaboration & Teamwork
All deliverables represent a combined group effort in this project.
Collaborated Skill
Details
Details
Table of Contents
- Project Overview & Goals -
​​
- ​My Work -
​​
- Methodology & Approach -
​​
- Key Activities & Execution -
​​
- Deliverables & Outcomes -
​​
- Tools & Technologies Used -
Project Overview & Goals
​Project Overview
This project was undertaken as a requirement for a database management course. The central objective was to design, develop, and implement a relational database system capable of managing the core operations of a hypothetical automobile repair business. The problem being addressed was the need for a structured, efficient, and reliable method to store and retrieve information related to various facets of the repair shop's activities, moving away from potentially manual or less integrated systems.
​Project Goals
The system needed to capture key entities and their interactions. The primary goals were to accurately model and manage:
​
-
Customers: Storing details like name, phone number, and address. Each customer could own multiple automobiles. Customers request and approve repair estimates.
-
Automobiles: Tracking vehicles by VIN, including year, make, model, and linking them to their respective owners (Customers). Each automobile could be associated with multiple repair estimates and repairs over time.
-
Employees: Maintaining records of employees, including their ID, name, phone number, hire date, and type (e.g., Technician 'T', Manager 'M'). Employees were further categorized into Managers and Technicians.
​
-
Repairs & Estimates: Managing the lifecycle of a repair, starting from a Repair Estimate (including description, amount, completion date, requesting/approving customer, associated vehicle, and supervising manager) to the actual Repair (with details like description, initiation/completion dates, deposit/total amounts, associated customer, vehicle, and originating estimate ID).
​
-
Skills & Labor: Defining specific skills required for repairs (e.g., Engine Mechanic, Tire Changer) and linking these skills to technicians who possess them. The system also tracked estimated labor hours per skill for each repair estimate and the actual shifts worked by technicians on specific repairs.
​
-
Parts & Inventory: Managing repair parts, including description, unit cost, and quantity on hand. The database tracked the quantity of each part required for a repair estimate and the quantity consumed during an actual repair, along with the consumption date.
​
-
Equipment: Cataloging repair equipment with description, manufacturer, and model. The system recorded the estimated hours each piece of equipment was needed for a repair estimate and tracked the actual check-in/check-out times and the technician using the equipment for a specific repair.
​
-
Supervision & Relationships: Defining relationships such as managers supervising repairs and technicians utilizing equipment and labor.
​
The ultimate goal was to create a normalized, consistent, and functional database schema supported by SQL scripts for its creation and initial population, demonstrating proficiency in database design principles and implementation techniques learned in the course.
My Work
​Background
This project was a highly collaborative group effort. Specific tasks or deliverables were not formally assigned to individual team members, and precise individual contributions are limited due to the integrated nature of the teamwork. My contributions were integrated within the team's collective effort across all project phases. This involved participating in group discussions for requirements analysis, contributing to the iterative design of the ERD and Relational Schema, collaboratively writing and debugging parts of the SQL script for table creation and data insertion, and ensuring consistency between the different components (diagrams, dictionary, script). I was an active participant in the development process that led to the final group deliverables.
​
-
Tools & Methods Used: Collaboratively used the tools and methods employed by the team, including Lucidchart selected by the group for the ERD and Relational Schema, and SQL for script development, within a PostgreSQL environment. Methods included group brainstorming, collaborative design sessions, peer review of schema components, and joint SQL script writing/testing.
​
-
Linkage/Enablement: My participation in the collaborative design and implementation process directly contributed to the team's ability to finalize the ERD, Relational Schema, and the functional SQL script. Contributions in discussions helped refine the models, and involvement in scripting assisted in the successful creation and population of the database, enabling the completion of the overall project requirements as a group.
​​
Methodology & Approach
The team followed a standard database design lifecycle methodology:
​
​Conceptual Diagram (ERD)
The first step involved analyzing the requirements of the auto repair shop scenario, which was provided as initial background and instructions, and creating a conceptual model. This resulted in the Entity-Relationship Diagram (ERD). This diagram visually represents the key entities (Customer, Automobile, Repair, Employee, etc.), their attributes, and the relationships between them (e.g., one-to-many, many-to-many). Cardinality constraints are depicted using Crow's Foot notation, indicating how many instances of one entity can relate to instances of another. Associative entities (like QUANTITY REQUIRED, HOURS REQUIRED, POSSESS) were used to resolve many-to-many relationships identified during modeling (e.g., between Repair Estimate and Part, Repair Estimate and Skill, Skill and Employee/Technician).
​
Logical Design (Relational Schema)
The conceptual ERD was then translated into a logical model, the Relational Schema. This involved mapping entities to tables, attributes to columns, and relationships to foreign keys. Primary Keys (PK) and Foreign Keys (FK) were explicitly identified for each table to enforce entity integrity and referential integrity, respectively. Composite Primary Keys (CPK) were used for associative entities (e.g., Shift_Info, Part_Consumption, Equipment_Utilized) to uniquely identify rows based on combinations of foreign keys referencing the related entities. Data types for attributes were considered at this stage, although fully detailed in the Data Dictionary and SQL script. Normalization principles were applied implicitly during this translation to reduce data redundancy and improve data integrity, aiming for at least Third Normal Form (3NF), as is standard practice.
​
Data Dictionary Definition
The team defined metadata for the database schema. This includes table names, column names, data types (e.g., BIGINT, VARCHAR, CHAR, DATE, INT), constraints (NOT NULL, CHECK constraints like CustomerID > 0, Primary Key, Foreign Key), and descriptions for each element, ensuring clarity and consistency.
​
Physical Design & Implementation (SQL Script)
The logical schema and data dictionary definitions were used to create the physical database structure using SQL's Data Definition Language (DDL). The team wrote CREATE TABLE statements in SQL, specifying column names, data types, and constraints (PRIMARY KEY, FOREIGN KEY with REFERENCES, NOT NULL, CHECK). Following table creation, SQL's Data Manipulation Language (DML) was used, specifically INSERT INTO statements, to populate the tables with sample data, demonstrating the database's functionality and providing test cases. The specific SQL syntax used aligns with standard SQL, compatible with systems like PostgreSQL.
Key Activities & Execution
The team executed the following key activities collaboratively:
​
Entity Identification
The group identified the core business objects needing representation in the database: customers, their vehicles, the repairs performed, employees involved (technicians, managers), necessary parts and equipment, and the skills required.​
​
Relationship Modeling
The team defined the connections between these entities. This involved determining cardinality (one-to-one, one-to-many, many-to-many) and participation constraints. For instance, modeling that one customer can have multiple cars, one repair estimate can require multiple parts and skills, and one technician can possess multiple skills. Resolving M:N relationships through associative tables (like POSSESS linking Skill and Technician, or PartQuantityRequired linking RepairEstimate and Part ) was a crucial activity. The bulk of this modeling was completed via Lucidchart.
Attribute Definition​
For each entity (table), the team specified the necessary attributes (columns) to store relevant information, such as CustomerName, AutomobileVIN, RepairDescription, PartUnitCost, SkillName, etc.
Schema Mapping
The ERD was systematically translated into relational tables. This involved creating tables for each entity and an associative table, assigning primary keys (single or composite), and establishing foreign key relationships to link related tables, ensuring referential integrity (e.g., CustomerID in Automobile_T referencing Customer_T ).
Constraint Specification​
The team defined rules to maintain data integrity using SQL constraints. This included PRIMARY KEY constraints for unique identification, FOREIGN KEY constraints to enforce relationships, NOT NULL constraints to ensure required fields are filled, and CHECK constraints to validate data values (e.g., IDs > 0).
SQL Script Development
The group authored the SQL code (CREATE TABLE, INSERT INTO statements) necessary to build the database structure and populate it. This involved translating the table definitions, column types, and constraints from the logical design into precise SQL syntax.
Data Population
Sample data was curated and inserted into the tables using INSERT INTO statements. This data covered various scenarios, including multiple customers, vehicles, employees, parts, repairs, etc., allowing for testing and demonstration of the database functionality.​
Deliverables & Outcomes:
The project resulted in the following documented deliverables produced by the team:
​Entity-Relationship Diagram (ERD)
A visual diagram illustrating the entities, attributes, and relationships within the auto repair shop domain. This served as the blueprint for the database structure.
​
​Relational Schema​
A diagram detailing the structure of the database tables, including table names, column names, primary keys (PK), foreign keys (FK), and composite primary keys (CPK). This represented the logical design derived from the ERD.
Data Dictionary
A document intended to provide detailed descriptions, data types, and constraints for all tables and columns in the schema.
​
SQL Implementation Script
A complete script containing SQL DDL (CREATE TABLE) statements to construct all necessary tables with their constraints, and SQL DML (INSERT INTO) statements to populate the database with sample data representing customers, vehicles, employees, repairs, parts, equipment, and their relationships.
​
The primary outcome was a functional, normalized relational database design and implementation for the specified scenario, demonstrating the team's collective understanding and application of database management principles. The SQL script allows for the recreation and population of the database in a compatible environment (like PostgreSQL).
Tools & Technologies Used
Conceptual/Logical Modeling Tool:
Lucidchart was used to create the ERD and Relational Schema diagrams.
​SQL (Structured Query Language)
Used for defining (CREATE TABLE) and manipulating (INSERT INTO) data within the database.
PostgreSQL​
The SQL syntax in the script is pulled from PostgreSQL (e.g., use of BIGINT, VARCHAR, CHAR, DATE, standard constraint syntax).
​
Word Processing/Document Software
Google Docs was used to culminate various related documents.
​
Communication Tools
Video conferencing was utilized for routine group meetings.​
​
Collaboration Tools
Google Drive and Google Docs were the primary collaboration tools.
Documents