PROJECT 04 / DATABASE DESIGN & SQL
Watersports Rental
Database
A relational MySQL database for tracking equipment, customers, employees and rental transactions—with analytical queries built around real operational questions.
Project overview
In a five-person database management course team, we designed a relational database to support a watersports rental operation. The model links each rental transaction to a customer, the employee handling it and the rented equipment. Beyond storing records, the coursework asked how these relationships could answer business questions about equipment use, revenue, staff commissions and customer activity.
We used MySQL and MySQL Workbench for entity–relationship modeling and SQL implementation. Python-generated synthetic records provided repeatable sample data for querying and demonstration.
Relational schema
The original MySQL Workbench model contains four tables: customer, employee, equipment and rental. The rental table has three foreign keys, each referencing one of the other entities, allowing one customer, employee or equipment item to participate in many separate rental records.
names · contact information
date of birth · emergency contact
name · role
commission
name · price · condition
status
FK customer_id · FK employee_id · FK equipment_id
date · start/end time · total price
Redrawn from the original course ERD and forward-engineered DDL. The original schema spells the equipment primary key equpiment_id; the rental foreign key equipment_id references that field. This is an original naming inconsistency, not an additional entity.
From design to implementation
Model the business rules
Identify the four core entities, primary keys and relationships. Define each rental as belonging to one customer, one employee and one equipment item.
Create and populate the schema
Generate MySQL DDL from the Workbench model, including foreign-key constraints and supporting indexes. Use Python-generated sample data covering 25 customers, 4 employees, 30 equipment items and 50 rentals.
Query operational questions
Apply SQL joins, GROUP BY, SUM and subqueries to report on equipment rental history, employee commissions and customer activity.
Selected SQL work
Employee commission report
The saved query joins employee and rental records on employee_id, groups by employee, and calculates a commission total using SUM and CAST. This connects transaction values to a staff-level financial question.
Equipment rented by each customer
A multi-table INNER JOIN links customer → rental → equipment, producing an equipment history ordered by customer surname.
Most recent rental by equipment
A LEFT JOIN and correlated MAX(rental_date) subquery aim to show each item’s most recent rental while retaining items with no rental history. The archived query uses a later equipment_id name that does not match the original DDL; it should be reconciled before being treated as a directly runnable script.
| Task | SQL tools |
|---|---|
| Revenue and employee commissions | INNER JOIN, SUM, GROUP BY, CAST |
| Customer equipment history | Multi-table INNER JOIN, ORDER BY |
| Latest rental per equipment item | LEFT JOIN, correlated subquery, MAX |
Engineering challenges and refinements
The project involved troubleshooting schema definitions, foreign-key naming, data population and JOIN conditions. The original forward-engineered DDL also stores money, rental dates and times as VARCHAR, rather than DECIMAL, DATE and TIME. That is a limitation for financial arithmetic, validation and chronological sorting, and would be a clear target for a production-oriented revision.
The saved scripts and presentation document a trigger and a JDBC-based customer-rental lookup demonstration. The full trigger and Java application source were not preserved in the materials reviewed, so those parts are not presented as independently reproducible implementations.
Team context and available evidence
This was a five-person academic team project. The original presentation, ERD, MySQL Workbench model, schema-creation SQL and analytical query files substantiate the database work. A complete contribution-by-person record is not available; the schema and overall system are therefore described as team work rather than the sole work of one developer.
Project artifacts: The original Workbench model, forward-engineered schema and three selected query files were reviewed when preparing this page. The Python data-generation source and complete JDBC source are not currently available. No real customer or payment information is included in this portfolio case study.
What this demonstrates
- Translating business operations into a relational database design
- Defining entity relationships with primary and foreign keys
- Working with MySQL Workbench and SQL schema generation
- Analyzing rental transactions using joins, grouping, aggregate functions and subqueries
- Recognizing design limitations and identifying schema improvements