Course: EGEN5208W Databases for Soft Engineers Instructor: Abdelghny Orogat Department: Department of Systems and Computer Engineering University: Carleton University
Required Operations
You must implement exactly eight (8) application operations.
Each operation:
Must execute one or more SQL statements
Must interact with actual database tables
Must handle both success and failure cases
Must be executable independently of other operations
Implementing more than 8 operations will not earn additional marks.
Role
Operations
Count
Member
User Registration, Profile Management, Health History, Dashboard
4
Trainer
Set Availability, Schedule View
2
Administrative Staff
Room Booking, Equipment Maintenance
2
Total
8
Member Functions (4 Operations)
1. User Registration
Create a new member account.
The system must:
Accept the following inputs:
Full name
Email address (must be unique)
Date of birth
Gender
Phone number
Automatically store the registration timestamp
Reject duplicate email registrations with a clear error message
Required SQL behavior:INSERT to create the member record
2. Profile Management
Allow a logged-in member to manage their own profile and health data.
The system must allow:
Updating basic profile information (e.g., name, phone)
Creating or updating fitness goals (e.g., target weight)
Inserting new health metric records
Health metrics:
Must be stored as append-only records
Must include:
Metric type (e.g., weight, heart rate)
Value
Timestamp
Existing health records must never be overwritten
Required SQL behavior:UPDATE for profile data, INSERT for goals, INSERT for health metrics
3. Health History
Display the complete health metric history for the logged-in member.
The system must:
Retrieve all health metric records for the member
Display:
Metric type
Value
Timestamp
Sort results consistently by timestamp
Display a message if no records exist
No modification operations are allowed here.
Required SQL behavior:SELECT, WHERE (filter by member), ORDER BY
4. Dashboard
Display a summary view for the logged-in member.
The dashboard must show:
The most recent value for each health metric type
All active fitness goals
The total number of group classes attended
A list of upcoming personal training sessions, including:
Date
Start time
End time
Trainer name
Charts, trends, predictions, or visual analytics are not required.
Required SQL behavior:SELECT with JOINs, COUNT, subqueries or ORDER BY … LIMIT 1
Trainer Functions (2 Operations)
5. Set Availability
Allow trainers to define when they are available.
Availability:
Is represented as individual time slots
Each slot includes:
Date
Start time
End time
Recurring availability is not required
The system must:
Prevent overlapping availability slots for the same trainer
Validate that end time is after start time
Allow updating or deleting existing slots
Required SQL behavior:SELECT to detect overlap, INSERT, UPDATE, DELETE
6. Schedule View
Display the trainer's upcoming assignments.
The system must show:
Personal training sessions:
Date, time, member name, room
Group fitness classes:
Date, time, class name, room
Only future sessions
Results sorted by date and start time
Required SQL behavior:SELECT, JOIN, WHERE, ORDER BY
Administrative Staff Functions (2 Operations)
7. Room Booking
Allow administrators to assign rooms to sessions or classes.
The system must:
Prevent assigning the same room to overlapping time periods
Allow modifying an existing room assignment
Reject conflicts with a clear error message
Room availability display:
May be a simple list of rooms not already booked in a given time window
Required SQL behavior:SELECT to detect conflicts, UPDATE to assign or change rooms
8. Equipment Maintenance
Allow administrators to manage equipment records.
The system must support:
Logging a maintenance issue
Updating equipment status:
operational
under repair
out of service
Viewing all equipment
Filtering equipment requiring maintenance
Equipment must be associated with a room or location.
Required SQL behavior:INSERT, UPDATE, SELECT with WHERE
Implementation Notes
Each operation must be demonstrated with:
One successful execution
One failure case
All validation must be enforced through:
SQL constraints, and/or
Triggers, and/or
Application-level checks
No hard-coded data is allowed. Errors must be handled gracefully.