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.