Introduction to project

• Course: "Utveckling mot Databaser"
• Period: 14/11-22 - 15/1-23
• Education: Java developer
• School: IT-Högskolan Stockholm

During this course the following technologies have been touched:

bash, docker, mySQL, Vim, markdown, git, mermaid, gradle, SpringBoot, jdbc, jpa, normalization and ACID.

Project

The CRUD-application will create, update and delete a new album for the band U2. It also shows the relation between the table Artist and table Album which will be displayed as "Album number".

Normalization of data: removing redudant data and anomalies by creatating new enitites and if needed a so-called connection if two entities have many-to-many relation . By doing so future updates, insertations and deletions will be much easier handled.

Prerequisites

• Bash
• Docker Desktop
• Git
• Gradle

Step 1 - Create and run container

Start docker desktop and bash. Copy following commands:

1. get latest mySQL image

docker pull mysql/mysql-server:latest

2. create mySQL container

docker run --name iths-mysql\
	-e MYSQL_ROOT_PASSWORD=root\
	-e MYSQL_USER=iths\
	-e MYSQL_PASSWORD=iths\
	-e MYSQL_DATABASE=iths\
	-p 3306:3306\
	--tmpfs /var/lib/mysql\
	-d mysql/mysql-server:latest

exclude if error occurs : --tmpfs /var/lib/mysql\

3. run mySQL container:

docker start iths-mysql

Container should now be compatible and ready to display project.

Step 2 - git clone

Copy following commands.

1. clone project locally

git clone https://github.com/AmandaEmilia/DB-2022.git

2. go to project

cd DB-2022

Step 3 - Prepare container

1. grant privileges to user iths

docker exec -i iths-mysql mysql -uroot -proot <<< "GRANT ALL ON Chinook.* TO 'iths'@'%'"

2. load Chinook script

docker exec -i iths-mysql mysql -uroot -proot < Chinook.sql

Step 4 CRUD-application

1. grant privileges to run project

chmod +x gradlew

2. run project

gradle bootRun

Steg 5 Normalization of data

1. download studentdata csv-file

curl -L https://gist.githubusercontent.com/AmandaEmilia/f2802188f5f930c75f90a4f78353ede3/raw/denormalized-data.csv -o denormalized-data.csv

2. copy studentdata csv-file to database library

docker cp denormalized-data.csv iths-mysql:/var/lib/mysql-files

3. load normalizing of studentdata script

docker exec -i iths-mysql mysql -uroot -proot < normalized.sql

Entity-Relation Diagram

erDiagram
Student ||--o{ StudentSchool : attends
StudentSchool }o--|| School : enter
Student ||--o{ StudentGrade : has
StudentGrade }o--|| Grade : has
Student ||--o{ StudentPhone : has
Student ||--o{ StudentHobby : has
StudentHobby }o--|| Hobby : contains
    Student {
        int StudentId
        string FirstName
        string LastName
    }

    StudentSchool {
        int StudentId
        int SchoolId
    }
    School {
        int SchoolId
        string Name
        string City
    }

    StudentGrade {
        int StudentId
        int GradeId
    }
    Grade {
        int GradeId
        String Grade
    }

    StudentPhone {
        int PhoneId
        int StudentId
        String Type
        String Number
    }

    StudentHobby {
        int StudentId
        int HobbyId
    }

    Hobby {
        int HobbyId
        String Type
    }

Loading

4. open bash in container

docker exec -it iths-mysql bash

PC users add winpty before

5. log on to mySQL

mysql -uiths -piths

6. SQL-Commands

use iths;
show tables;
SELECT * FROM CONCLUSION;
SELECT * FROM Grade;

Overview

• table -> entity with unique Id
• connection - > for two entites with many-to-many relation
• view -> saved select-statement

Name	table	view	description
CONCLUSION		X	shows improved version similar to UNF
Grade	X
GradeList		X
Hobby	X
HobbyList		X
HobbyTemp		X
PhoneList		X
School	X
SchoolList		X
Student	X
StudentGrade	X		connection - student o grade
StudentHobby	X		connection - student o hobby
StudentPhone	X
StudentSchool	X		connection - student o grade
UNF	X		denormalized data-set