Arbeidskrav 2

Sandra Brattli-Aspvik, Backend-Programmering, høst 2025
Tema 2 Databasesystemer

---

📌 Table of Contents

• Project Overview
  • Readmes
  • Language Choice
• Setup
• Modules/Libraries
• Queries
• Functions
• Sources of information
• Reflections
• Summary
• License

---

Overview

This is the second obligatory set of tasks for year 1 Backend Programming.
The set focuses on database, SQL, setup, queries, and Python backend.

It is a set of 4 main tasks, as follows:

1. SQL script with db definitions and data inserts
2. A Readme.md file explaining DB design and structure, etc
   (created separate readme for this)
3. SQL script with queries
4. A .py file with functions and function calls

I have also added a requirements.txt file for easy setup:
Requirements

---

Readme files

This is the main README.

AI Use README

Database Design and Structure README

Troubleshooting .venv and interpreter issues README

Troubleshooting encoding issues when creating tables README

---

Back to top

---

Language Choice

I have chosen a combination of Norwegian and English during this assignment.
The assignment has stated some default parameters for the task, including Norwegian named
database, tables, and table data.
I have kept Norwegian data (inserts, db name, tables etc), but in general I am using
English to describe functions, write Readmes, etc.
Outputs will also be in English.
In short - I have used Norwegian and English where it has made sense for me to do so,
and to reduce my own confusion as much as possible.
There will in other words be a mix of Norwegian and English in this project.
In a real world setting, the language used would be influenced by the democracy of the team.
That being said, I prefer to work in English - during tasks, coding, queries, setups, and in general.

This for clarification.

---

Back to top

---

🚀 Setup:

This project was created using docker-compose, running the database in a
Docker Desktop Container on a Windows System.
However, you can also run the queries in SQL_generate
to build the database.
Using Docker is just my personal preference.

Docker Setup:

Build and start the container from the project directory:

docker-compose up -d

Start without rebuilding:

docker-compose start <container_name>

or

docker-compose up -d

Podman can be used for some Linux environments.

For making the init run in some Linux environments

You might have to change the mount like this:

volumes:
      - gabib-vol:/var/lib/mysql
      - ./initdb:/docker-entrypoint-initdb.d:Z

Some Windows specific items in requirements.txt have been changed
to include a system check before trying to install.

---

General setup:

Activate the .venv:

For Linux/macOS:

source .venv/bin/activate

For Windows:

.venv\Scripts\activate.bat

Using GitBash:

source .venv/Scripts/activate

---

1. Install requirements from "requirements.txt"

pip install -r requirements.txt

---

Back to top

---

Modules/Libraries

The file requirements.txt lists all packages
used in this project.

---

Queries

All Queries for this assignment are located here: Queries

---

Functions

All functions for this assignment are located here:
Functions

---

🔼 Back to top

---

Sources

Primary keys in relational Databases:
https://stackoverflow.com/questions/60416962/best-practice-to-choose-primary-keys-in-a-relational-db-what-is-the-smartest-so

https://stackoverflow.com/questions/60416962/best-practice-to-choose-primary-keys-in-a-relational-db-what-is-the-smartest-so#comment106888746_60417420

Normalization:
https://www.geeksforgeeks.org/dbms/introduction-of-database-normalization/

https://www.geeksforgeeks.org/dbms/difference-between-normalization-and-denormalization/

Markdown / tables:
https://www.codecademy.com/resources/docs/markdown/tables

Left join:
https://www.w3schools.com/mysql/mysql_join_left.asp

Insert:
https://www.w3schools.com/python/python_mysql_insert.asp

Update:
https://stackoverflow.com/questions/6503824/update-a-column-in-mysql

https://www.w3schools.com/mysql/mysql_update.asp

MySQL Connector:
https://pypi.org/project/mysql-connector-python/

https://www.w3schools.com/python/python_mysql_getstarted.asp

https://www.geeksforgeeks.org/python/python-mysql/

Tabulate:
https://pypi.org/project/tabulate/

https://www.geeksforgeeks.org/python/introduction-to-python-tabulate-library/

https://www.datacamp.com/tutorial/python-tabulate

---

🔼 Back to top

---

Reflections

What did I learn?

• Never use hyphens when naming a database.

• Specify which version of mysql you are using.

• Understanding how to create a database, and how it
  works in the background (using localhost, for instance).

• That docker is very useful and allows for a lot
  of practice without having to worry about breaking things.

• A lot more practice writing a useful Readme. (several, in fact.)

• How to use Copilot for assistance with things like readmes and
  easy population of databases with a predetermined set of data, .md formatting, table formatting etc.

• Encoding errors and troubleshooting.

• Learned how useful the requirements.txt is.

• Understanding that all you need is one field that references another table in order to be able
  to work with any data from that table.

• Learned a lot about databases, syntax, the backend of mysql, how to write queries, how to use Python to
  properly interact with a database.

• Learned about the tabulate function.

• I have been reminded that it is a good idea to test your code on different machines before
  calling it a day.

• I have read about junction tables and entity tables, and learned that an entity table represents
  a thing - like a person, or a book, and that a junction table creates a relationshop between tables.
  The table utlån in this project is a junction table.

---

🔼 Back to top

---

What challenges did I face?

• Not understanding what goes on in the background when using docker, and working with databases locally.
  This caused me to worry that I might be messing up a database for someone else
  when it was really only running locally in my Docker Desktop.

• I spent an embarrasing amount of time figuring out how to be able to use special characters
  like æ, ø and å (specified to be used in the assignment) during table creation, and had a hard
  time finding any resources about it. Even Copilot took forever to finally be able to help me solve it.

• Understanding the logic behind JOINS and subqueries, and how relations work between
  tables.

• Going back to Python Syntax after having worked almost exclusively with SQL queries for a while.

• Had some trouble figuring out how to make sure I avoided duplicates and was able to change the "Levert"
  status on new "utlån". That took some brain gymnastics before I got it right!

• Not having a lot of time to finish the arbeidskrav due to a sudden health related situation.

• Writing more informative commit messages when I push changes to my repos.

• A bit unrelated, but I realized virtualization within virtual environments is a bit more tricky than
  virtualization on a physical host (I tried to run docker desktop in a virtual ubuntu machine)

---

🔼 Back to top

---

What would I improve next time?

• I hope that next time nothing happens that steals time from me
  so that I could have spent diving deeper into the subjects without stressing.
  I had to work a bit faster than I would have liked in order to manage the deadline.

• I might make the AI Use documentation shorter, unless it is okay to keep it how it is now.
  I have gotten into the habit of asking it to list what it has helped me with so that it is possible to shorten it down.
  However I also think it's important to add the pieces of code that I already have at the
  when I ask AI for help with something to show that I am doing my own work and using
  it as a learning tool. I am not sure which route to go, so I would love
  feedback on this.

• Next time I will continue to strive to do my very best and learn as much as possible in the process.

• Improved coding skills

• Improved understanding

• Improved Readmes

---

🔼 Back to top

---

Summary:

Despite the fact that I lost a lot of time due to an impromptu wisdom tooth extraction with
lots of complications, I still feel like this set of assignments helped me learn a lot.
While I would have preferred having the full allotted time, I work very well under pressure, and I
believe that I have not lost a large amount of learning.
I am an avid user of Copilot, and have had it create many practice tasks for me that have helped
me learn more quickly and specified about a subject that I was unsure about.
For instance I created a restaurant database and practiced the use of Foreign Keys and test
queries in order to ensure the tables were allowed to interact.

I have to admit that during the last few weeks, since we started getting into MySQL and
databases, I have almost solely focused on that, so my system received a small shock when I
returned to Python syntax. Not to say that was a bad thing, but I noticed it took some
tweaking and reading to get back on track with Python.

As a large bonus, this arbeidskrav has also led me back to experimenting with Linux distros,
as a step towards making it more of a daily driver when it comes to programming to avoid some
of the Windows bugs and hiccups.
It is also useful to see how things work on systems that I am not used to.

All in all, I have to say that I am pleased with the outcome, while I would wish that I had had
more time to tweak it even further in a positive direction. There is always room for improvement.

I will be playing around a lot with databases for leisure, as I can see an ocean of good
reasons to know this subject well and use it in daily life.

Note:
About the README directory:
I decided to split my readme up into several smaller ones, as it would have become quite long
and more difficult to traverse if I had kept everything in one place.

This way it should be quite straightforward to find the subject one is looking for by
browsing the README directory.

I am of course open to any constructive criticism on this.

Note 2:
I have also tried to reduce the amount of comments in the actual code. The comments present
are by and large for my own learning, for later reference, and to make it easier to follow for
somebody else who reads my code.

Note 3:
During the writing of insert I created a custom loaner with a custom loan. I am not sure if this is
allowed, but since some of my code is using that specific custom insert, I have chosen to
keep it as I really did not consider this until I had done all of the assignments and rigorous testing. You will find that the person I added was myself, so it should be easy to notice in the data.

---

Back to top

---

License

This project is for personal educational purposes only.

---

Back to top

---