SQL
The main point of the course was introduction to SQL syntax, advanced queries, optimization via indexing, normalization and set theory.
MSQL, SQLite and DB Browser
This project was developed first using SQLite syntax and the database was built using DB Browser for SQLite. At a later stage, the database needed to be refactored and uploaded to an external MSQL database container.
Data Transformation
We needed to clean and truncate the dataset for this project with simple queries.
Interface Design
The final stage of this project required us to build an interface that can interact with the database through complex queries.
The point of the introduction to databases course at the University of Manitoba is to continuously work on a project throughout the semester - applying new things as we learn them in class and submitting portions of the project as per the deadlines.
This project is done in a team of 3 people, where we begin by selecting a dataset and making entity relation diagrams around it, modifying the dataset slightly as we see fit so that it meets the required complexity and has learning value. For our project, we picked a dataset from Kaggle based around vehicle accidents in the UK: https://www.kaggle.com/datasets/tsiaras/uk-road-safety-accidents-and-vehicles
Our Dataset ERD
It's very messy and some parts of it got scrapped as we realized we'd have to make major changes to the dataset and come up with a lot of artificial data to make things work. The final version of all the tables is a bit simpler than this diagram.After that, we convert the ERD into a relational diagram, documenting our normalization process from 1NF to 2NF to 3NF and finally, to BCNF.
We then generated some potential queries that may be useful to someone doing statistical research using our database. The point of these queries was to prepare us for the interface implementation and practice writing complicated queries in a familiar database - if we are to come up with decent queries now, we would have an easier time transposing them into the Java interface at the end of the course.
To ensure that everything works as intended, this entire time I was implementing the database in SQLite using DB Browser. This was not for marks, but it ensured that we would have a working project by the end of the course.
The reason I say this is because the final part of the project is by far the most tedious - we had to take everything we've designed (or built) and move it into an MS SQL database that's hosted on the University of Manitoba servers. For security purposes, we are not allowed to mass insert CSV files into it, so I had to make a quick program that would generate a few hundred thousand insert statements from our original database implemented in SQLite. This data was cleaned with simple queries and modified slightly to fit our project needs.
After transforming the entire SQLite database into insert statements, I then had to wait as it slowly populates the university MS SQL database. Due to the amount of statements and restrictions on communication speed, this process would take me about 2 days.
During this time my teammates put together the initial Java interface that would send and process queries through a simple terminal.
I joined their efforts shortly before we had to present the project to debug their work and add a few new features.
We ended up testing and implementing everything just in time to hand in a working project.
Although it was a lot of work since none of us knew what was going on and what we were going towards by the end of the course, having to deal with a slightly complicated dataset allowed us to tackle various design issues and learn how we can overcome them.
This project was my first real exposure to working and coding in a team. While it was not the best experience, it did allow me to learn how to better communicate with others to get work done efficiently.
Another major issue that we ended up running into is the fact that all 3 of us had different amounts of time we were willing to commit to the project. If you pair this up with the lack of version control tools you'd end up with our problem of only certain team members understanding certain parts of the project. I ended up taking over most of the work for database implementation, which left the other 2 members having to read my code from scratch and work around it. This severely slowed down our work as a team, but has served as a valuable lesson for our future projects.