DBMS Project - Logistics Fleet Management

Copyright Notice

© 2024 Larragueta César - Maulard Jules - François Mathieu - Derache Cédric. All rights reserved.

This project was developed as part of an academic assignment by a team of four students. No part of this project may be reproduced, distributed, or transmitted in any form or by any means without the prior written permission of the authors.

Description

This project consists of designing a database for managing a fleet of trucks in a freight transportation company. The objective is to implement DBMS concepts such as data modeling, creating a relational database, and implementing various operations (queries, updates, etc.).

The application allows users to view information about trucks, drivers, depots, and deliveries, as well as perform statistics and updates on the database.

Entity-Relationship Diagram

Conceptual Data Model (CDM)

Here is the data modeling diagram (Entity-Relationship) of the project.

Note: This diagram represents the main entities (trucks, drivers, depots, products, deliveries) as well as their relationships (for example, a driver can drive multiple trucks, a truck can transport multiple products, etc.).

Relational Model

Tables

The relational model of the project is based on the following tables:

1. Truck

   • license_plate (PK)
   • brand
   • type
   • model
   • service_date
   • purchase_date
   • mileage
   • capacity
   • condition

2. Driver

   • driver_id (PK)
   • last_name
   • first_name
   • address
   • city
   • seniority
   • license_type
   • performance_rating

3. Depot

   • depot_id (PK)
   • number
   • name
   • city

4. Product

   • product_id (PK)
   • product_name
   • weight

5. Delivery

   • delivery_id (PK)
   • delivery_date
   • truck_id (FK)
   • driver_id (FK)
   • departure_depot_id (FK)
   • arrival_depot_id (FK)

6. Product_Delivery (Association table between Delivery and Product)

   • delivery_id (FK)
   • product_id (FK)

Integrity Constraints

• Primary and foreign keys to ensure referential integrity.
• Check constraints on columns such as mileage, capacity, and truck condition.
• Performance indexes to optimize queries on trucks and drivers.

Relational Schema in 3rd Normal Form

All tables have been normalized to 3NF to ensure absence of redundancies and guarantee good data organization.

Installation and Usage

Prerequisites

• Node.js: Version >= 14
• DBMS: MySQL, PostgreSQL (or any other SQL-compatible relational system)
• Framework: Using Node.js with a simple architecture for the interface.

Installation Instructions

1. Clone the repository

   Clone this project to your local machine with the following command:

   git clone https://github.com/project-name.git
   cd project-name

2. Install dependencies

3. Configure the database Make sure you have a MySQL or PostgreSQL database server running. Create an empty database, then execute the creation scripts to set up the database. For MySQL, connect to your database and execute:

   SOURCE path/to/your/sql/create.sql;

SQL Queries

The following scripts are included to manipulate the database:

1. create.sql

Creates the database and its tables.

2. insert.sql

Inserts sample data into the database.

3. select.sql

Contains examples of query operations.

4. update.sql

Contains examples of update operations.

5. drop.sql

Drops the database.