WanderLog - Relational Database System

A robust relational database system built with MySQL, designed to support WanderLog, a digital platform for cataloging, organizing, and sharing virtual travel experiences.

This project covers the complete database lifecycle: from requirement engineering and conceptual modeling (ER Diagrams) to logical normalization (3NF) and physical implementation with advanced SQL features.

🚀 Key Features

• Complete Relational Architecture: Highly structured database handling Users, Trips, Destinations, Multimedia, Comments, and Favorites, strictly normalized up to the Third Normal Form (3NF) to prevent data redundancy.
• Advanced SQL Implementation: Includes custom Stored Procedures, Functions, and Triggers to enforce complex business rules and data integrity directly at the database level.
• Performance Optimization: Strategic implementation of Indexes (both simple and composite) to optimize query execution times, particularly for grouping and filtering large datasets.
• Data Abstraction: Implementation of multiple SQL Views to simplify data access, hiding underlying relational complexity for the application layer.
• Capacity Planning: Includes detailed data sizing calculations and yearly growth projections based on expected system usage.

🗄️ Database Schema & Entities

The system architecture revolves around the following core entities:

• Utilizador (User): Authentication and profile management.
• Viagem (Trip): Core entity storing travel experiences, dates, and ratings.
• Destino (Destination): Geographical categorization (Continent, Country, City).
• Multimedia: Storage references for photos and videos associated with trips.
• Comentario & Favorito (Comments & Favorites): Social interaction entities.

💻 Tech Stack & Tools

• Database Engine: MySQL 8.0
• Conceptual Modeling: BrModelo (Chen's ER Notation)
• Logical & Physical Modeling: MySQL Workbench
• Query Language: SQL (DDL, DML, DCL)

⚙️ Advanced SQL Highlights

This repository contains scripts for:

• Triggers: e.g., Validating trip dates (end date cannot be prior to start date) and rating bounds (1 to 5).
• Functions: e.g., Calculating the average rating of trips per user.
• Stored Procedures: e.g., Secure insertion of new trips and comments.
• Complex Queries: Using JOIN, GROUP BY, HAVING, and subqueries for data analysis (e.g., identifying top destinations and most active users).

---

Note: This project was developed within the scope of the Bases de Dados (Databases) course at the University of Minho. Grade: 15/20