← Back to Projects

Property Management
Database System

Fully normalized relational database for property leasing and maintenance operations

Course: MIS 150 Individual Project Duration: 10 weeks Year: 2025

Project Overview

The Challenge

Design and implement a comprehensive database system to manage property leasing operations, maintenance requests, tenant information, and financial transactions. The system needed to support complex queries, maintain data integrity, and provide operational insights for property managers.

The Solution

Built a fully normalized relational database in MySQL following database design best practices. Created comprehensive ER diagrams, implemented referential integrity constraints, developed stored procedures and triggers for automation, and wrote 17+ complex SQL queries for reporting and analytics.

The Impact

Developed a scalable database architecture supporting multiple properties, tenants, and maintenance workflows. Implemented automated data validation through constraints and triggers, created efficient query operations using joins and indexes, and demonstrated proficiency in database normalization and SQL development.

Key Achievements

17+
Complex SQL Queries
10+
Database Tables
3NF
Normalized Design
5+
Automated Triggers

Database Design & Architecture

Designed a comprehensive relational database following SDLC standards and database normalization principles:

Entity-Relationship Model

  • Created detailed ER diagrams showing entities and relationships
  • Identified primary and foreign keys for all tables
  • Defined cardinality and participation constraints
  • Mapped conceptual design to logical schema

Normalization

  • Achieved Third Normal Form (3NF) across all tables
  • Eliminated data redundancy and update anomalies
  • Ensured functional dependencies were preserved
  • Balanced normalization with query performance

Data Integrity

  • Implemented CHECK constraints for data validation
  • Created foreign key relationships with referential integrity
  • Designed NOT NULL constraints for required fields
  • Used UNIQUE constraints to prevent duplicates

Automation & Logic

  • Developed stored procedures for common operations
  • Created triggers to automate business rules
  • Implemented calculated fields and aggregations
  • Built views for simplified data access

Visual Documentation

Complete database design documentation including ER diagrams, schema diagrams, and query examples:

Entity-Relationship Diagram

Entity-Relationship Diagram

Complete ER diagram showing all entities, attributes, and relationships in the property management system

Database Schema

Database Schema

Normalized database schema (3NF) with primary keys, foreign keys, and referential integrity constraints

SQL Query Example: Inspector Performance Tracking

SQL Query Example

Complex SQL query using INNER JOINs, date functions, and multi-table operations to track inspector performance

Core Database Tables

Properties

Property details, addresses, types, and specifications

Tenants

Tenant information, contact details, and rental history

Leases

Lease agreements, terms, rent amounts, and payment schedules

Maintenance Requests

Service requests, priorities, assignments, and status tracking

Payments & Transactions

Rent payments, late fees, deposits, and financial records

Technologies & Concepts

MySQL SQL Queries Joins & Subqueries Stored Procedures Triggers ER Diagrams Database Normalization SDLC

SQL Query Examples

Developed 17+ complex SQL queries demonstrating proficiency in various database operations:

Joins & Aggregations

  • Multi-table joins to combine property, tenant, and lease data
  • Aggregate functions (SUM, AVG, COUNT) for financial reporting
  • GROUP BY and HAVING clauses for data summarization
  • Complex WHERE conditions for filtered results

Subqueries & Views

  • Nested subqueries for complex filtering logic
  • Correlated subqueries for row-by-row comparisons
  • Views to simplify frequently used queries
  • Inline views in FROM clauses for data transformation

Data Manipulation

  • INSERT statements with multiple rows and constraints
  • UPDATE operations with conditional logic
  • DELETE with referential integrity considerations
  • Transaction management for data consistency

Advanced Features

  • Date/time functions for lease expiration tracking
  • String manipulation for data formatting
  • CASE statements for conditional logic
  • Window functions for ranking and analytics

Implemented Business Logic

1

Automated Rent Calculations

Created triggers to automatically calculate total rent including utilities and fees. Updated lease totals when payment terms changed. Maintained historical records of all rent modifications.

2

Late Fee Management

Implemented stored procedures to calculate and apply late fees based on business rules. Automated fee application when payments exceeded due dates. Maintained audit trail of all fee assessments.

3

Maintenance Workflow

Designed system to track maintenance requests from submission to completion. Automated status updates and assignment notifications. Enabled priority-based request management.

4

Lease Expiration Alerts

Created queries to identify leases expiring within specified timeframes. Generated reports for proactive lease renewal management. Tracked renewal rates and tenant retention metrics.

5

Financial Reporting

Developed queries for revenue analysis by property and time period. Created views for outstanding balances and payment history. Generated occupancy and vacancy reports for portfolio management.

Project Outcomes & Skills Demonstrated

Database Design

  • Created comprehensive ER diagrams using industry standards
  • Applied normalization principles to eliminate redundancy
  • Designed scalable schema supporting business growth
  • Documented all relationships and constraints clearly

SQL Proficiency

  • Wrote complex queries with multi-table joins
  • Used subqueries and aggregate functions effectively
  • Implemented indexes for query optimization
  • Demonstrated proficiency in DDL and DML operations

Data Integrity

  • Enforced business rules through constraints
  • Maintained referential integrity across all tables
  • Prevented invalid data entry through validation
  • Ensured data consistency with transactions

Systems Thinking

  • Analyzed business requirements and translated to database design
  • Applied SDLC methodology to database development
  • Considered performance implications in design decisions
  • Created maintainable and well-documented solution

Key Learnings

Database Normalization

Gained deep understanding of normal forms and their importance in database design. Learned to balance normalization with practical query performance needs. Understood how to eliminate update, insert, and delete anomalies through proper design.

SQL Query Optimization

Learned to write efficient queries using appropriate join types and indexes. Understood query execution plans and how to optimize performance. Discovered the importance of proper indexing for large datasets.

Business Rule Implementation

Learned to translate business requirements into database constraints and logic. Understood when to use triggers vs. stored procedures vs. application logic. Gained experience in maintaining data integrity through database features.

Real-World Applications

Understood how database design principles apply to actual business scenarios. Learned to anticipate future requirements and design for scalability. Gained appreciation for documentation and clear naming conventions.

Want to discuss database design and SQL?

I'd be happy to share more details about this project or discuss database concepts.

Get In Touch View More Projects