University Library Catalog Project

During my second year of university, I was tasked with completing three projects for three different subjects: Database Management, UI/UX Design, and a "Programming" class. I had the ingenious idea to combine them all into one. A functional library management system. The source code is available on GitHub.

Project Overview

The goal was to build a desktop application to handle core library operations like managing books, staff, and loans. The app needed to connect to a database, pull in extra data from the web, and have a graphical user interface.

I'm writing this as if the features were set in stone from the start, but in reality, they were cobbled together as I submitted different parts of the project for each class. The final product was a Frankenstein's monster of all these requirements.

For context, the requirements could be summarized as: Create a db with at least 12 entities that follows the 3rd normal form. Create a comprehensive figma file containing at least 5 pages of the application. And create an application using Windows Forms that has to modify and save data.

Cool Architecture Diagram

Database Design

A massive compromise was the database design. Due to deadlines, I had to design the database before I started any other part of the project, and I couldn't change it later. This meant the final application had a bunch of unused tables and weird workarounds to make everything fit into a presentable package.

I designed the relational database in PostgreSQL using a convenient tool from CTU to store all the essential information I thought the application would need. And then some more, since the project required at least 12 entities. The core of the data access was then later handled by an AppDbContext class in the Windows Forms application, which defined entity models for tables like Book, Author, Staff, and Loan.

SQL
Copy code
1CREATE TABLE Books (
2    BookID SERIAL PRIMARY KEY,
3    Title VARCHAR(255) NOT NULL,
4    AuthorID INT REFERENCES Authors(AuthorID)
5);
6
7CREATE TABLE Staff (
8    StaffID SERIAL PRIMARY KEY,
9    FirstName VARCHAR(100),
10    LastName VARCHAR(100)
11);
12

Windows Forms Application

The application's logic was written in C# using the .NET Framework. I used Entity Framework Core to bridge the gap between my C# app and the PostgreSQL database. I also created a Library.Components namespace to hold reusable helper classes, like a DataGridBinder to link data to UI grids and a DataGridStyle to apply consistent styling.

Writing a context for Entity Framework for an existing database was an experience and a half. I had to manually map all of the relationships and properties, which through a trial and error ended up taking me a whole day of work. For reference, here's how I loaded a book's author information from the database:

CSHARP
Copy code
1using (var context = new AppDbContext())
2{
3  var book = context.Books.Include(b => b.BookAuthors)
4    .ThenInclude(ba => ba.Author)
5    .Single(b => b.BookId == selectedBook.BookId);
6
7  lbAuthor.Text = $@"{book.BookAuthors.First().Author.AuthorFirstName} {book.BookAuthors.First().Author.AuthorLastName}";
8}
9

Google Custom Search API

To make the catalog a bit richer, I used the Google Custom Search API. This was handled by a GoogleCustomSearch component that would fetch extra info like book covers or descriptions based on a book's title or ISBN. Since my lack of foresight meant I hadn't planned for this data when designing the database, I had to come up with some creative solutions to incorporate it.

UI/UX Design

The user interface was built with Windows Forms, which was... an experience. At the very least, it made creating the Figma mockups for my UI/UX class a lot easier. The main window served as the entry point to other forms for managing books, staff, and loans. Data was mostly displayed in DataGridView controls, and I used my helper components to keep things from looking too chaotic.

Conclusion

This university project was a lesson. It probably simulated a real-world scenario with its poor planning and oddly structured deadlines. However, it was a foundational experience that taught me about the complexities of software architecture, the limitations of rigid design, and the pain of using outdated UI frameworks.