Team course project (4 people)

Vehicle-sharing platform database (UTC-NF18)

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.

Background

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.

Data model

The model has four parts:

  • Users: a parent Utilisateur table (login, password, type, active flag) and subtype tables Proprietaire, Locataire, Entreprise; companies also own Conducteur records.
  • Vehicles: Vehicule is keyed by registration number and linked to its owner, insurance contract, availability periods, options and permitted countries.
  • Transaction chain: Annonce → Contrat_location → Etat_des_lieux (one at pick-up, one at return) + Facture + Commentaire.
  • Many-to-many relations: Est_disponible, Peut_circuler and Possede, each with a composite primary key.

Implementation highlights

  • Push integrity into the database. Phone format with 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)).
  • Business rules can be constraints too. A listing with three reports must be inactive, expressed as CHECK (NOT(nombre_signalement >= 3 AND activite)). No application path can bypass it.
  • Parameterised queries with psycopg2. Login, duplicate checks, inserts and deletes mostly use %s placeholders; the application handles menus, permissions and date validation, while the database enforces structure, foreign keys and row-level consistency.
  • The NoSQL variant changes only what it should. 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'.

Outcome and reflection

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:

  • which rules must live in the database and which belong in the application;
  • when to model inheritance with subtype tables and when to use one table with a type field;
  • JSON columns remove tables, but they also remove constraints, indexes and easy aggregation.

Source, SQL scripts, report and NoSQL variant: https://github.com/yicongl1/Vehicle-Share-Database

Technical notes

01 From UML to BCNF: how the 18 tables of the vehicle-sharing database were decided User inheritance, vehicles and the transaction chain, composite keys, CHECK constraints and normalisation — and which rules belong in the database rather than the application. 02 JSON columns in a relational database: when is it worth embedding invoices and reviews? The vehicleShare NoSQL variant: three JSON columns replacing three tables, ->> queries, the cost in indexes and constraints, and parameterised queries with psycopg2.

Related skills

  • SQL
  • Python
  • Git