The objective of this project was to design, develop, and evaluate a functional database for the Greek apartment rental market. The system manages the full lifecycle of a rental transaction, including property listings, owner/tenant demographics, rental agreements, and user feedback.
The schema is built on 5 core entities:
- OWNERS: Stores personal details, contact information, and Tax Identification Numbers (TIN) for property owners.
- APARTMENTS: Captures location data (Street, City, Floor), daily rental prices, and owner associations.
- TENANTS: Manages tenant demographics, including gender, country of origin, and occupancy counts.
- RENTALS: Tracks rental durations with built-in logic to ensure start dates precede end dates.
- REVIEWS: A feedback system allowing tenants to provide standardized star ratings (1-5) and text reviews.
The Entity Relationship Diagram (ERD) is shown below:
The implementation is divided into five specialized scripts for modularity and testing:
- 01_create_tables: DDL queries for defining tables and sequences (e.g., SEQ_OWNERS).
- 02_add_constraints: Implementation of Primary Keys, Foreign Keys, and CHECK constraints (e.g., gender validation, date logic).
- 03_insert_data: Sample data population using various insertion methods.
- 04_drop_tables: Cleanup scripts to safely remove constraints and tables.
- 05_queries: A library of DML queries for system evaluation.
- Revenue Analysis: Annual total revenue calculations based on daily price and stay duration.
- Geographic Insights: Tracking tenant distribution by country.
- Performance Metrics: Identifying the most popular apartments and highly-rated properties using aggregations and joins.
- User Management: Dynamic generation of system usernames using string manipulation.