Team course project (4 people)
Université de Technologie de Compiègne · NF18 Relational and Non-Relational Database Design (spring 2024)
Designed the vehicleShare platform from requirements and UML to a PostgreSQL implementation: 18 tables, integrity constraints, a Python/psycopg2 command-line application, and a NoSQL variant storing insurance, invoices and reviews in JSON columns.
vehicleShare is a peer-to-peer car-rental platform: owners publish listings and availability windows, individual or corporate tenants book online, sign a rental contract, inspect the car at pick-up and return, and finally receive an invoice covering rent, excess mileage and missing fuel. Beyond price, the platform also handles ratings, reports, insurance, options and the countries where the car may be driven.
This was the four-person NF18 “Relational and Non-Relational Database Design” course project at UTC, completed in spring 2024. The brief was not a working CRUD demo: it was the whole path from requirements to database — UML class diagram, relational model (MLD), PostgreSQL tables and constraints, a Python command-line application, a normalisation analysis, and finally a NoSQL variant storing semi-structured data in JSON columns.
The model has four parts:
Utilisateur table (login, password, type, active flag) and subtype tables Proprietaire, Locataire, Entreprise; companies also own Conducteur records.Vehicule is keyed by registration number and linked to its owner, insurance contract, availability periods, options and permitted countries.Annonce → Contrat_location → Etat_des_lieux (one at pick-up, one at return) + Facture + Commentaire.Est_disponible, Peut_circuler and Possede, each with a composite primary key.CHECK (telephone LIKE '__________'), age >= 18, rating 0..5, fuel 0..1; a contract must be either an individual or a company: CHECK ((locataire IS NOT NULL AND entreprise IS NULL) OR (locataire IS NULL AND entreprise IS NOT NULL)).CHECK (NOT(nombre_signalement >= 3 AND activite)). No application path can bypass it.%s placeholders; the application handles menus, permissions and date validation, while the database enforces structure, foreign keys and row-level consistency.Vehicule.contrat_assurance, Contrat_location.commentaire and Contrat_location.facture become JSON columns; the three corresponding tables disappear and queries use ->>, for example WHERE Contrat_assurance->>'type' = 'Tous risques'.The repository keeps the five formal submissions (requirements, UML/MLD, SQL, Python application, normalisation) plus the NoSQL extension. Read in order, it shows a database moving from an idea to a queryable structure.
Looking back, the valuable part is not one SQL statement but a few decisions:
Source, SQL scripts, report and NoSQL variant: https://github.com/yicongl1/Vehicle-Share-Database