Skip to content

PROJECT_OVERVIEW.md

Latest commit

 

History

History
174 lines (140 loc) · 6.6 KB

PROJECT_OVERVIEW.md

File metadata and controls

174 lines (140 loc) · 6.6 KB

Airbnb Database Design Project - Overview

Last Updated: 15/10/2025
Project Status: Phase 2 Completed
Student: Nikolas Daniel Vincenti
Course: Build a Data Mart in SQL (DLBDSPBDM01)

Project Summary

This project implements a comprehensive database design for an Airbnb-like platform, following academic standards and industry best practices. The database supports a complete vacation rental ecosystem with user management, property listings, booking systems, financial transactions, and review mechanisms.

Project Phases

Phase 1: Conceptual Design (Completed 2025-10-05)

  • Deliverables: ER Model with 25+ entities
  • Relationships: 3 triple relationships, 1 recursive relationship
  • Documentation: Complete data dictionary and design rationale
  • Tools: Draw.io for ER diagrams, IEEE notation

Phase 2: Database Implementation (Completed 2025-10-15)

  • Deliverables: Complete SQL implementation with 27 entities
  • Features: 89+ constraints, comprehensive sample data, performance optimization
  • Quality: Error-free installation, bulletproof sample data
  • Testing: Complete test suite with presentation queries

Phase 3: Final Documentation (In Progress)

  • Deliverables: Final reports, presentation materials
  • Documentation: Complete project documentation
  • Submission: Academic report with proper citations

Technical Specifications

Database System

  • Primary System: MySQL 8.0+
  • Compliance: ANSI SQL:2016 standards
  • Normalization: 3NF/BCNF achieved
  • Performance: Strategic indexing for common queries

Architecture Highlights

  • Entity Count: 27 comprehensive entities
  • Relationships: 45+ foreign key relationships
  • Constraints: 89+ business rule constraints
  • Sample Data: 25+ countries, 25+ users, 20+ properties
  • Triple Relationships: 3 complex many-to-many-to-many relationships
  • Recursive Relationships: 1 self-referencing relationship

Key Features

User Management System

  • Multi-Role Support: Users can be guests, hosts, and admins
  • Profile Management: Separate guest and host profiles
  • Verification System: Identity and phone verification
  • Preferences: Language and currency preferences

Property Management

  • Comprehensive Listings: Properties with amenities and photos
  • Pricing Rules: Dynamic pricing with date ranges
  • Location Services: Full address hierarchy (country → city → address)
  • Amenity System: Many-to-many property-amenity relationships

Booking System

  • Complete Workflow: From search to check-out
  • Status Tracking: Comprehensive booking status management
  • Modification Support: Booking change tracking
  • Capacity Management: Guest count validation

Financial System

  • Payment Processing: Multiple payment methods
  • Payout Management: Host payout tracking
  • Transaction Chain: Complete financial audit trail
  • Service Fees: Platform fee management

Review System

  • Multi-Criteria Reviews: Detailed rating categories
  • User Feedback: Guest and host reviews
  • Rating Aggregation: Host performance metrics
  • Quality Control: Review validation rules

Communication System

  • Message Threads: Conversation management
  • Notifications: System-wide notification system
  • User Interaction: Guest-host communication

Quality Assurance

Code Quality

  • Error-Free Execution: All SQL scripts execute without errors
  • Comprehensive Comments: Detailed documentation for all code
  • Consistent Formatting: Professional code presentation
  • Constraint Validation: All business rules enforced

Data Integrity

  • Referential Integrity: Complete foreign key relationships
  • Domain Integrity: CHECK constraints for business rules
  • Entity Integrity: Primary keys and NOT NULL constraints
  • Unique Constraints: Natural keys and business rules

Performance Optimization

  • Strategic Indexing: Optimized for common query patterns
  • Query Performance: Efficient data retrieval
  • Scalability: Designed for growth
  • Resource Management: Optimized storage usage

Documentation Standards

Academic Compliance

  • Citation Format: APA 7th Edition with IU modifications
  • Writing Style: Academic tone with professional presentation
  • Figure Standards: High-resolution diagrams with proper legends
  • Table Standards: Professional formatting with clear captions

Version Control

  • Change Tracking: Comprehensive changelog maintenance
  • File Organization: Logical directory structure
  • Naming Conventions: Consistent file naming standards
  • Documentation Updates: Regular maintenance and updates

Project Structure

airbnb-clone-database/
├── docs/                    # Comprehensive documentation
│   ├── guidelines/          # Technical standards and best practices
│   ├── changelog/           # Version history and change tracking
│   ├── quality/             # Quality standards and evaluation
│   └── submission/          # Submission requirements
├── sql/                     # SQL implementation
│   ├── dev/                 # Development scripts
│   └── phase2_submitted/    # Final submission files
├── diagrams/                # ER diagrams and schemas
├── reports/                 # Academic reports
└── data/                    # Sample data files

Success Metrics

Technical Achievements

  • ✅ 27 entities with comprehensive relationships
  • ✅ 3 triple relationships implemented
  • ✅ 1 recursive relationship implemented
  • ✅ 89+ constraints enforcing business rules
  • ✅ Error-free installation and execution
  • ✅ Comprehensive sample data
  • ✅ Performance optimization

Academic Standards

  • ✅ APA 7th Edition citation format
  • ✅ Professional documentation
  • ✅ Academic writing standards
  • ✅ Proper figure and table formatting
  • ✅ Complete reference management

Industry Best Practices

  • ✅ Database normalization (3NF/BCNF)
  • ✅ Strategic indexing
  • ✅ Comprehensive constraints
  • ✅ Performance optimization
  • ✅ Security considerations
  • ✅ Scalability design

Next Steps

  1. Final Documentation: Complete academic reports
  2. Presentation Preparation: Create presentation materials
  3. Quality Review: Final quality assurance check
  4. Submission: Prepare final deliverables
  5. Evaluation: Project presentation and assessment

Project Status: Phase 2 Completed Successfully
Quality Level: Industry Standard
Documentation: Comprehensive and Professional
Next Phase: Final Documentation and Submission