Online Auction System

Developed as the final project for the Database Systems course (Basi di Dati) – Bachelor's degree, University of Rome Tor Vergata, A.Y. 2024/2025

Online Auction System is a command-line application for an online auction house. Administrators publish items and manage the whole auction lifecycle; registered users browse active auctions and place bids, including automatic counter-bids that outbid competitors on their behalf up to a chosen ceiling.

The project covers the full database design process (requirements analysis → conceptual E-R design → logical design with workload/cost analysis → physical design on MySQL) and a Java client that talks to the database exclusively through stored procedures, using a separate least-privilege DB account for each role.

🚀 Overview

Most of the business logic lives in the database: stored procedures implement every operation, triggers enforce the business rules, and scheduled events keep auction states up to date. The Java client is a thin MVC layer that calls these procedures through DAOs and renders a text-based UI.

Key Features

1. Role-based access with least privilege: three MySQL users (aste_login, aste_admin, aste_user) each have EXECUTE rights only on the procedures of their role. After login, the client switches to the connection of the authenticated role.
2. Full auction lifecycle: admins create items and auctions (1–7 days, scheduled or immediate), monitor active/scheduled/ended auctions, inspect bids and close auctions early. Auction states (FUTURA → ATTIVA → TERMINATA) are updated every minute by a MySQL event.
3. Hierarchical categories: up to three levels, implemented as a self-referencing table. Deleting a category cascades to its sub-tree and reassigns orphaned auctions to a Default category inside a single transaction.
4. Bidding rules enforced in the database: triggers reject bids below the base price, increments smaller than €0.50 and bids on ended auctions; the aggiungiOfferta procedure (run at SERIALIZABLE isolation) also stops users from outbidding themselves.
5. Automatic counter-bids: a user can set a maximum amount; whenever someone outbids them, the system re-bids current + €0.50 until the ceiling is reached. Every automatic bid is stored with microsecond timestamps, and the cascade runs on a background thread so the UI never blocks.
6. Controlled redundancy: NumOfferte and OffertaMassima on Asta are kept up to date by an AFTER INSERT trigger. The workload analysis showed that dropping them would raise the daily access count by ~38% (58,100 vs 42,100).
7. Data protection basics: passwords and card numbers are stored as SHA-256 hashes, and a monthly event purges data older than 10 years.

🧠 Architecture

graph TD
    U([User / Admin]) <--> V[Views<br/>CLI menus]
    V <--> C[Controllers<br/>Main · Login · User · Admin]
    C --> D[DAOs<br/>one per stored procedure]
    D --> CF[ConnectionFactory<br/>role-based JDBC connection]
    CF -->|aste_login| P1[(login · registerUser<br/>dettagli_utente_cf)]
    CF -->|aste_user| P2[(aggiungiOfferta · visualizzaAsteFiltrate<br/>asteVinteUtente · ...)]
    CF -->|aste_admin| P3[(creaAsta · chiudiAsta<br/>aggiungiCat · eliminaCat · ...)]
    P1 & P2 & P3 --> DB[(MySQL 8 – schema aste<br/>tables · triggers · events · view)]

Loading

See docs/ARCHITECTURE.md for the full breakdown and docs/DATABASE_DESIGN.md for the E-R model, relational schema and design decisions.

🛠️ Tech Stack

• Language: Java 21
• Database: MySQL 8 (schema forward-engineered with MySQL Workbench)
• Database access: JDBC with MariaDB Connector/J, CallableStatement only
• Build: Maven (fat jar via maven-shade-plugin)
• Local environment: Docker Compose (MySQL 8.4)

📖 Documentation

• Architecture Overview
• Database Design
• Requirements
• Full project report (Italian, PDF, 70 pages)

🚦 Getting Started

Prerequisites

• Java 21+
• Maven 3.9+
• Docker with Docker Compose, or a local MySQL 8.x server

Installation

1. Clone the repository

   git clone https://github.com/DeveloperMatt02/Online-Auction-System.git
   cd Online-Auction-System

2. Start the database

   docker compose up -d --build

   On first start, sql/db_init.sql creates the aste schema, procedures, triggers, events, the three application users and the seed data. Wait until docker compose ps reports the container as healthy (about 20–30 seconds). If it stays unhealthy, check docker compose logs db; after fixing the cause, run docker compose down -v to wipe the half-initialised volume and start again.

   Without Docker: run the script as root on your own MySQL 8 server (mysql -u root -p < sql/db_init.sql) and make sure the event scheduler is on. On Linux the server must also be initialised with lower_case_table_names=1, because the script refers to some tables with mixed case.

3. Configure the connection

   cp resources/db.properties.example resources/db.properties

   The defaults match the Docker setup. Change CONNECTION_URL if your server isn't on localhost:3306.

4. Build

   mvn package

Running the App

Run it from the repository root, because the app reads resources/db.properties relative to the working directory:

java -jar target/online-auction-system.jar

Demo accounts

The seed data includes 20 administrators (admin1 … admin20, password adminpass1 … adminpass20) and a three-level category tree. To try the user side, choose Registrazione from the login menu and create a user: you need a username of at least 4 characters, a password of at least 8, a 16-character fiscal code (codice fiscale) and a 16-digit card number with a future expiry date (any test value works).

The CLI is in Italian, matching the original course requirements.

📂 Project Structure

Online-Auction-System/
├── pom.xml
├── docker-compose.yml                  # Local MySQL 8.4, auto-runs db_init.sql
├── docker/db.Dockerfile                # MySQL image with the init script baked in
├── resources/
│   └── db.properties.example           # Connection URL + per-role DB credentials
├── sql/
│   └── db_init.sql                     # Schema, procedures, triggers, events, users, seed data
├── src/main/java/it/uniroma2/asteonline/
│   ├── Main.java                       # Entry point
│   ├── controller/                     # Application flow (Main, Login, User, Admin)
│   ├── view/                           # CLI menus and forms
│   ├── model/
│   │   ├── dao/                        # One DAO per stored procedure
│   │   └── domain/                     # Asta, Offerta, Categoria, Utente, ...
│   ├── factory/ConnectionFactory.java  # Role-aware JDBC connections
│   ├── exception/                      # DAOException
│   └── utils/                          # Session (LoggedUser), enums
└── docs/
    ├── ARCHITECTURE.md
    ├── DATABASE_DESIGN.md
    ├── REQUIREMENTS.md
    ├── images/                         # E-R diagrams
    └── report/project-report-it.pdf    # Full design report (Italian)

👤 Author

• Matteo Trossi – @DeveloperMatt02

📄 License

This project is licensed under the Apache License 2.0 - see the LICENSE file for details.