Connect the relational model
The supplied schema uses five core tables: Account, Customer, Artist, Track, and Review. Primary and foreign keys connect the entities, while checks restrict account roles and review ratings.
PROJECT 05 / Database Engineering
Sydney Music Database System
A music catalogue with role-based accounts, track reviews, and a Python data-access layer.
WHEN
September 2025 – November 2025
CONTRIBUTION
Python functions, integration & code review
TECHNOLOGIES
PythonPostgreSQLpsycopg2Flask
DATABASE ENGINEERING05
5
Core relational tablesAccounts, tracks & reviews
Pythonpsycopg2PostgreSQL
MY CONTRIBUTIONUser & review operations
Conceptual data-access flow
THE PROJECT AT A GLANCE
Five connected tables. One working data layer.
Accounts, customers, artists, tracks, and reviews form the assignment’s relational model.
- Core relational tables
- 5
- Functions I implemented
- 3
- Account roles
- 3
01 / OBJECTIVE
What the project set out to do
Build the database-backed operations for Sydney Music, connecting a Flask-based interface to PostgreSQL so users can browse tracks, search the catalogue, and manage accounts and reviews.
02 / MY CONTRIBUTION
My part in the work
I implemented list_users, list_reviews, and update_user in the Python data-access layer. I also reviewed and debugged SQL and Python across the project, tested how the modules worked together, and completed the final review of the code and documentation.
This was a three-person assignment. My contribution focused on user and review operations, integration, testing, and code review. Other team members implemented the core track operations and stored functions.
03 / TECHNICAL APPROACH
How it came together
Build user operations
Implemented list_users to return account details ordered by role and login, and update_user to change profile details with a parameterised, case-insensitive login match.
Make reviews useful to the interface
Implemented list_reviews with joins to track and account information, formatted review dates, and ordering by date. The function returns dictionaries the web interface can consume.
Verify the integration
Reviewed SQL and Python together, tested interactions between modules, and checked transaction handling, exception handling, and connection cleanup across the data-access layer.
04 / OUTCOMES
What the work produced
- Implemented user listing, review listing, and profile updates in Python.
- Integrated user and review data through joins, parameterised queries, and structured return values.
- Contributed debugging, module testing, and final code and documentation review to the team submission.
An academic team project built around a supplied music-system schema and web interface.
Want to talk about this project?
Get in touch