Authors: Launay-Schapman Alexis & Mouriès Pierre
Course: INT3
Project Type: SQL Mini-Project
Project title: 777 Detailing
A car detailer (luxury car cleaning service) wants to create a database
to manage its clients, cars, appointments, and services.
Our mission was to design and implement this database to ensure it's
well-structured and adapted to the company's needs.
- Analysis of the project\
- E/A (Entity--Association) model design\
- Relational schema definition\
- Constraint identification and SQL creation
- Populating the database (30 min)\
- Writing and testing SQL queries (1.5 h)\
- Finalizing the report (1 h)
- Client
- First name, last name, address, phone, email, fidelity points
- Can own multiple cars
- Car
- Size category, description, brand, model
- Each belongs to one client
- Service
- Name, cost, time needed
- Appointment
- Linked to one car and one or more services
- Includes bill, start date, end date
- Each client has a unique ID\
- Addresses are stored in a separate relation (1NF compliance)\
- Cars and services have their own relations\
- Appointments link cars and services
Initially, appointments were modeled as a link between cars and
services, but this limited reusability (only one combination possible).
Therefore, we created a dedicated appointment relation.
Service = (ID_Service, Service_Name, Price, Time_Needed)
Address = (ID_Address, Address_nb, Address_Name, Address_Type, City_Name, ZIP_Code, Country)
Model = (ID_Model, Size_Category, Brand, Model_Name)
Client = (ID_Client, First_Name, Last_Name, Phone_nb, Mail, Fidelity_Points, #ID_Address)
Client_Car = (ID_Car, Description, #ID_Model, #ID_Client)
Appointment = (ID_Appointment, Beginning_Date, End_Date, #ID_Car)
Take_Place = (#ID_Service, #ID_Appointment)
- Primary keys: All IDs\
- Foreign keys: As per relational model\
- Phone: Unique, not null,
VARCHAR(10)\ - Email: Valid format (
LIKEcheck for@and.)\ - Positive values: Fidelity points, price, address number\
- Date logic:
End_Date > Beginning_Date\ - Not Null: Except address info, description, mail, model name\
- Cascade deletions:
- Deleting a car removes its appointments\
- Deleting a service removes related entries in
Take_Place\ - Deleting a client removes their appointments
We used Mistral AI (Le Chat) to generate realistic tuples, based on our SQL schema and constraints.
Prompt used: > "I want to populate a database about a car detailer (luxury car cleaning service) who is based in Thionville, France. Could you create tuples corresponding to the following schema and respecting these constraints?"
Then we’ve interrogated the database by imagining different scenarios. You can find
them in the exploration.sql file.
Our database effectively stores client and service information for a
small detailing business.
Future improvements could include: - Adding an Employee table for
multi-worker operations\
- Auto-calculating service prices based on car material or size
This project strengthened our SQL skills, understanding of constraints, and ability to model real-world systems in relational databases.