At the end of May, just after finishing my university semester, I wanted to keep learning and decided to continue explore SQL on my own. I hadn’t yet taken CS50’s Introduction to Databases with SQL (that came later in June and July), so this was my first real attempt at building something independently.
Rather than working with a generic dataset, I wanted something I’d enjoy using. I chose to design a small library system, with a catalogue made up of Poirot, Miss Marple, and Sherlock Holmes stories.
With this project I aimed to:
- Get hands-on experience with creating tables and schemas
- Try out inserts, joins, and aggregates on my own
- Practise writing queries that returned meaningful results
- Build confidence before moving on to more advanced study
Step 1: Creating the Database
I started in MySQL Workbench, creating a new schema called library_demo. The database was structured around three tables:
- book – holding title, author, series, and year published
- patron – representing library members
- loan – connecting patrons to books, with loan and return dates
This was my first time setting up a schema fully by myself, so even small things like defining primary keys and foreign keys felt like important milestones.
Step 2: Inserting the Data
I populated the book table with titles from Poirot, Miss Marple, and Sherlock Holmes. I also added a few patrons and sample loans so I could test queries on realistic data.
Step 3: Running Queries
Once the tables were populated, I wrote queries and views to explore the data. This gave me practice with JOINs, aggregates, and CTEs (common table expressions).
What I Learned
- How to design and normalise a small relational schema (books, patrons, loans)
- The importance of primary and foreign keys for relationships
- Writing queries that join multiple tables to return useful results
- Using views and aggregates to summarise and simplify complex queries
If you’d like to explore the full project files, they are available here: github.com/deancoles/library_demo.
This project gave me the confidence to move onto more advanced SQL study. By the time I started CS50’s Introduction to Databases with SQL a few weeks later, I already had practical experience creating and querying my own database from scratch.
