Library Demo SQL Project

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.

Entity Relationship Diagram (book, patron, loan)

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.

Books Catalogue

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).

Query: Book Availability (Available / On Loan)

Query: Current Loans

Query: Average Loan Duration

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.

Foundation Degree Complete

Back in June I officially completed the second year of my studies and graduated with a Foundation Degree of Science in Software Engineering (Game Development) from Blackpool and The Fylde College, validated by Lancaster University. 

At the time, graduation felt like the end of an important chapter. But now that my certificate has arrived, it feels as though the achievement has really been stamped and sealed. Holding it in my hands, and seeing it with Distinction written, is a reminder of the work, effort, and persistence that went into the past two years. 

This foundation degree has been more than just assignments and exams. It’s been about growing in confidence, adapting to new technologies, and discovering where my real interests lie. From games programming to databases, I’ve found areas I’m eager to explore further as I move into the next stage of my degree. 

Although I’m already looking ahead to completing my BSc (Hons) in Software Engineering at Lancaster University in 2026, I wanted to take a moment to acknowledge this milestone properly. The certificate might just be a piece of paper, but it represents the journey so far, the challenges, the lessons, and the successes.

 

Completing CS50’s Introduction to Databases with SQL

At the end of May, just after finishing my university semester, I wanted to keep learning and stay active. After a small SQL project intended as a refresher (which I may post about later), I decided to push myself further by starting CS50’s Introduction to Databases with SQL. From early June to early July, I worked through the course and completed it, my first big step in strengthening my database skills in a structured way.

Unlike a one-off tutorial, CS50 gave me a steady routine of SQL practice over several weeks. That consistency helped turn what I’d learned previously into habits I could build on.

Why I Took It

• I wanted a structured way to reinforce the basics I’d started exploring on my own.
• I learn best by doing, so the problem sets gave me a steady rhythm of practice.
• I wanted a recognised milestone I could include in my portfolio and CV.

How I Studied

• I set aside small blocks of time most days rather than long weekend sessions.
• I kept simple notes as I went, just enough to remind myself of the “why,” not just the “how.”
• When I got stuck, I rebuilt small examples from scratch until the concepts felt natural.

Final Project

The course ends with a small project. For mine, I created an enhanced version of my Caves & Creatures database — this time improving schema design, constraints, and more structured queries than in my first attempt.

If you’d like to see any of the exercises or projects, I’ve uploaded them here: github.com/deancoles/CS50_SQL.

What I Learned

• Foundations that stick - setting up the right keys and constraints early makes later queries much easier.
• Thinking in relationships - planning how tables connect is just as important as writing the SQL itself.
• Writing for clarity - views and CTEs don’t just solve problems, they make queries easier to maintain and understand.
• Confidence - regular practice over a month gave me the rhythm I needed to feel at home with SQL.

What’s Next

• Work on more small projects that showcase practical query design and reporting.
• Strengthen my analysis skills further by combining SQL with tools like Power BI.
• Apply what I’ve learned in my wider software engineering work, so both paths develop together.

CS50’s Introduction to Databases with SQL

Database Concepts and Programming

This post is a reflection of a module I completed in the first year of my degree. It was by far my personal favourite module to do and was also my highest marked work overall. While I had used Microsoft Access as part of my previous work and was therefore familiar with basic aspects of databases such as Primary and Foreign Keys, this module introduced more complexity.

Assignment 1 (A+)

The first assignment focused on planning out the entire database structure. I had to design everything from the ground up, including an entity-relationship diagram (ERD), data dictionaries, business rules, and a breakdown showing how the database met third normal form (3NF). I also created a short video presentation where I discussed the legal and security concerns involved in storing user data — including GDPR, hashing passwords, and protecting sensitive information.

This task was a great introduction to how much thought goes into designing a system before any code is written. It helped me think carefully about relationships between tables, primary keys, and what kind of data each entity should store. The video side of things also pushed me to communicate technical concepts more clearly, which is a skill I have enhanced over my two years of the degree.

Entity Definitions

Data Dictionary for Characters table

Relationship Definitions

Business Rules and Assumptions

 

Initial ERD

Assignment 2 (A+)

For the second assignment, it was time to actually build the database based on my plans. I used a database management system to set everything up, applied the ACID principles for data integrity, and tested the system using SQL queries — including JOINs, LIKE statements, statistics, updates, and deletes. I also carried out backup and recovery tasks to make sure the database could handle any disasters.

This part of the module really boosted my confidence with SQL. Writing and running queries helped me understand how a well-planned database works in practice. Doing backup and recovery tasks also showed me how important it is to keep systems secure and recoverable, especially if you’re dealing with real user data.

Updated ERD

Complete Database

Conclusion

Looking back, there are definitely things I could improve or add, but overall, I’m really proud of what I achieved in this module. From the start, I put a lot of time and effort into making sure the work was as detailed and polished as possible. Our tutor informed us that the A+ grade wasn’t just about ticking boxes, but about going above and beyond in terms of effort and attention to detail. Knowing that, I made it my goal to give every task my full focus, and I’m glad that hard work paid off. Getting an A+ overall felt like a real reward for the time I put in.

Steam Collection

Recently, I decided to create a database of all the games I own on Steam — not just a simple list, but something I could sort, search, and even track my playtime with. I didn’t want anything overly complicated or online — just something simple I could run locally on my computer, where I could:

• See all my Steam games
• Track how long I’ve played each one
• Know when I last played them
• Sort or search through them however I like

Obviously I did not wish to have to manually put in data for 600+ Steam games into a database, and I also wanted a fast method of updating this data when required.

 

Step 1: Setting Up XAMPP

To run a database and PHP scripts on my computer, I installed a free tool called XAMPP. It acts as a local server, letting you run websites and databases on your own PC without needing to go online.

Once XAMPP was installed, I opened its Control Panel and started two services:

• Apache – to run PHP scripts
• MySQL – to handle my database

XAMPP Control Panel

Step 2: Creating My Database

With MySQL running, I opened phpMyAdmin, which is a browser-based interface for managing databases (available at http://localhost/phpmyadmin once Apache is active).

Inside phpMyAdmin, I:

•     Created a new database called steam_collection
•     Made a table called games
•     Added columns for:
•         game_id (the unique number Steam gives each game)
•         name (the game’s title)
•         playtime_forever (how long I’ve played the game in total)
•         playtime_2weeks (how long I’ve played it in the past 2 weeks)
•         last_played (the last date I launched it)

Later, I added some extra columns to store these playtimes in minutes for better sorting.

 

Step 3: Accessing My Steam Library

Steam doesn’t automatically give you your library data — but it does offer a Web API, which lets you retrieve your own data.

To retrieve this data I:

•     Signed in at Steam’s Developer site
•     Requested a free API key
•     Found my SteamID64 (a long number that identifies your account — I used steamid.io to find it)

These let me use Steam’s API to fetch all my owned games, along with my playtime data.

 

 

Step 4: Writing the Import Script (in PHP)

Next, I created a small PHP file called steam_import.php — a script that:

•     Connects to the Steam API using my API key and SteamID
•     Pulls all my owned games and playtimes
•     Connects to my MySQL database
•     Inserts (or updates) each game into the table

I saved this file into XAMPP’s htdocs folder (where websites and scripts are stored), so it could run on my local server.

Part of my import script

 

Step 5: Running the Script

To activate the script, I simply opened my browser and visited http://localhost/steam_import.php to begin the script. The last part of this URL had to match the file name exactly in order to work.

Behind the scenes, this:

•     Pulled my entire Steam library from the API
•     Inserted each game into the steam_collection database
•     Updated any existing records with the latest playtime or last played date

 

Confirmation of a successful update.

Step 6: Making the Data Easier to Read

Steam reports playtime in minutes, which isn’t very friendly. So I added some code to convert it into hours and minutes, such as “05 hrs 30 mins”.

I also cleaned up the last_played date so it only showed the date, not the full timestamp (no one needs to know I last played Lord of the Rings Online at exactly 3:42 AM).

I chose to store the raw minutes separately and use the formatted text just for display. This way, I could still sort and filter by playtime properly.

 

Step 7: Viewing My Collection

With everything imported, I went back into phpMyAdmin to view the table. It looked great! Each row shows:

•     The game’s name
•     Total playtime (formatted)
•     Recent playtime (last 2 weeks)
•     When I last played it

And because the minute values are stored in a separate column, I can sort by most played games, recent activity, or even filter out games I haven’t touched in years.

Most played games of all time

 

 

 

Most played games of the last two weeks

Games played in 2025

Automatically Refreshing the Data

Any time I want to update my collection, I just revisit:

http://localhost/steam_import.php

This reruns the script and pulls in my latest playtimes — quick and easy.

What I Learned

Even though I was already comfortable with SQL and using XAMPP, this project helped me connect the dots between different parts of the development process — turning individual tools I knew into a complete, working system.

• Working with a live API like Steam’s was a big step forward. I’d never used an external API in a personal project before, and it was eye-opening to see how accessible and flexible they can be. Understanding how to structure the request, handle the JSON response, and extract the data I needed added a real-world element to my database knowledge.
  
• Automating the data import with PHP gave me a new appreciation for how back-end scripting can complement a database. I didn’t just run queries manually — I wrote logic that could run over and over, fetching fresh data and updating my tables dynamically.
  
• Formatting raw data for display and usability — like converting playtime from minutes to a readable format, or cleaning up date outputs — made me think more about how data is consumed, not just stored. It’s one thing to collect information; it’s another to make it useful and readable.
• Designing with filtering and sorting in mind pushed me to consider the structure of my database more carefully. Separating raw values (like total minutes) from display values ensured I could sort accurately without sacrificing readability.
  
• Using views and calculated fields reminded me how much power SQL has for presenting data cleanly. Instead of reformatting everything manually every time, I used views to simplify repeated queries, helping me streamline the workflow.
  
  

This project wasn’t just a fun personal tracker — it was a way to reinforce what I already knew and push further, integrating API handling, automation, and thoughtful database design into a single, cohesive process.

Hercule Poirot Catalogue

A recent piece of work I was asked to do involved the creation of a lookup application prototype involving a theme of our choice. I love databases and I love Poirot so what could be better than an application that involves both things?

This application was designed for both Windows and Android platforms within Visual Studio Community 2022 using .NET MAUI.

The Prototype

The basic function of this application is to allow a user to catalogue their collection of Hercule Poirot novels. I would like them to be able to see which books they own, which they do not own and also a way to see all books. I also want to include the option of having multiple users, as a way to create multiple sets of data.

The Database

As this is just a basic prototype and I only need to include basic features I decided to make my database simple. It only really needs two things, books and users who collect them. So I created a table with the books and a table with the users.

My users table only needs two different users and usernames to display in the application itself. I included password fields in the event I resume work on this at a later date and have therefore given them placeholder data.

 

My table for the books is more detailed as it needs to have fileds for the various information that can be filtered and sorted, such as release date, titles and it's owned status. I decided to create one table with the primary key being the User, allowing for a title such as Murder on the Orient Express to appear twice with the same BookID and details. Doing the table this way also prevents the need for creating Junction or Join tables. For an actual application I plan to release I would of course do this in that way, but for my current needs this method is sufficent.

 

Features

I wanted a basic and simple screen with a simple and tidy background. I included a picture of Poirot in the corner and the amazing theme of the television series plays (which you of course won't see in the screenshots).

 

I added placeholder text for buttons that would not be used at this stage.

 

On the main browsing page I added options to sort titles by year or by title.

 

 

 

 

 

 

 

 

 

 

 

 

I added the option to change user also.

 

Next Steps

Were I to continue developing this application I would add the ability to add or remove titles, give UI customisation features to the user, add an offline system to this application and possibly add tags to individual books to allow for example users to enter the word 'Train' and get Murder on the Orient Express to appear or 'Hastings' to return titles he featured in.

Conclusion

While this was a simple application for me to make in terms of the database elements. The UI parts had me use .NET MAUI for the very first ocassion and I can see why it is very useful for cross-platform development and it certainly saved me time when creating something designed for both Windows and Android.

Overall I throughly enjoyed making this application and it was nice to make something that was more code based instead of the more Blueprint focused projects I have been given lately for my degree.

John Lemon's Haunted Jaunt

One of my recent assignments involved the use of a Unity and Unreal template and then adding on a mechanic of our choice to it and providing a short gameplay demo. My preferred engine is Unity due to my age giving me a great nostalgia for 2D titles which I feel Unity is best suited for, so quite naturally my template was a 3D game.

The Template

This was based on the John Lemon's Haunted Jaunt: 3D Beginner from Unity Learn. The project files were available to download here but I chose to create them from scratch by following the pathway as a way to increase my experience during this process.

The Mechanic

As the goal of the John Lemon game is to stealthily sneak past ghosts and gargoyles my idea for this project was to simply add a pickup that allowed the player character to become invisible to enemies, no matter if they are in view or not. 

First I'll show what happens when John Lemon walks into the sight of an enemy.

As you can see, this triggers the caught image and the level restarted. Now we want to introduce the pickup and temporarily block the enemies vision. We also need a visual indicator for the player in order to show if the invisibility is active. For this I feel the best way is to change the character model temporarily.

This is fine, however I also would like to demonstrate the behaviour when the effect wears off and the player returns to their normal status.

The first pickup expired (I set this to be twenty seconds) and he reverted back to being visible. However, John was very fortunate to have another invisibility bone right next to him! Now we can finish the level and allow John to escape.

Our level is now complete and our Lemon is free.

Other Mechanics Considered

At various points I considered ideas such as having a pickup that removed one random enemy, having a mode where you control one of the ghosts and pickups that do random effects (increase or decrease enemies, John or Ghosts invisible etc.), adding a timer, adding difficulty modes which change enemy number and their visiual ranges, and including a second exit. These could all have been great mechanics, but the time taken to implement would have impacted on my other modules currently. However when I have free time there is always the possibility of implementing some of thes emechanics, and if I do I will post about it here.

Conclusion

I had a lot of enjoyment when working on this, and I am a really big fan in general of the Unity engine and I particularly enjoy working with C# which I consider one of my best languages, alongside SQL.I was really pleased with how this turned out and the only challenge I found was keeping it simple, as per the brief I was given.