M141 LB 3 T.B

Platform

To avoid confusion and potential hickups in the future I've mentioned what system this was done on incase it does not work on other ones.

• OS : MacOS Sonoma 14.4.1
• Shell : ZSH 5.9 Apple Darwin
• MariaDB : 11.4.2-MariaDB Homebrew

Additional installated

Note

All steps done in folder : work_dir

LB3

Importing the files

Note : Make sure that you have Adminstrative Permissions for the MariaDB server

Importing the SQL file

1. Geting the SQL file that is to be imported

curl -O https://gitlab.com/ch-tbz-it/Stud/m141/m141/-/raw/main/LB3-Praxisarbeit/backpacker_ddl_lb3.sql

2. Log into MariaDB

mysql #my user is set to be an administrator for mariadb

3. Make new Databse

CREATE DATABASE IF NOT EXISTs backpacker_lb3;

4. MariaDB Verlassen

exit

5. Datenbank Importieren

mysql backpacker_lb3 < backpacker_ddl_lb3.sql

Importing the CSV file

1. Getting the CSV files to be imported, unzipping it, delete the MacOS metadata folder and the nolonger neeeded zip file then "rename" the folder to csv.

curl -O https://gitlab.com/ch-tbz-it/Stud/m141/m141/-/raw/main/LB3-Praxisarbeit/backpacker_lb3.csv.zip; unzip backpacker_lb3.csv.zip; rm -rf __MACOSX; rm backpacker_lb3.csv.zip; mv backpacker_lb3.csv csv

2. Get the current working directory and copy it to the clipboard

pwd

3. Log into MariaDB

mysql

4. Change to backpacker database

use backpacker_lb3

5. Import the CSV files Note : I've already looked at the csv's and the tables existing hence why I've added rules to the default import to avoid any issues from accuring Keep in mind this command has to be run for each table/csv!

load data local infile '/Users/crt/Documents/TBZ/M141/m141-lb3/work_dir/csv/backpacker_lb3_table_tbl_positionen.csv' into table tbl_ppositionen fields terminated by ',' enclosed by '"' lines terminated by '\n' ignore 1 rows;

Making changes to the database

From now on it us assumed that were already logged in to MariaDB and have selected the correct database

Hashing the Password of the users

update tbl_benutzer set Password = MD5(Password);

Changing table names

rename table tbl_positionen to tbl_position;
rename table tbl_personen to tbl_person;

Changing Attributes

alter table tbl_buchung change column Buchungs_ID Buchung_ID INT;
alter table tbl_buchung change column Personen_FS Person_FS INT;
alter table tbl_person change column Personen_ID Person_ID INT;
alter table tbl_position change column Positions_ID Position_ID INT;
alter table tbl_position change column Buchungs_FS Buchung_FS INT;

Adding Indexes

alter table tbl_buchung add index idx_Person_FS (Person_FS);
alter table tbl_buchung add index idx_Land_FS (Land_FS);
alter table tbl_position add index idx_Buchung_FS (Buchung_FS);
alter table tbl_position add index idx_Benutzer_FS (Benutzer_FS);
alter table tbl_position add index idx_Leistung_FS (Leistung_FS);

Adding Indexing to frequently used Columns

alter table tbl_benutzer add index idx_Benutzername (Benutzername);
alter table tbl_benutzer add index idx_aktiv (aktiv);
alter table tbl_buchung add index idx_Ankunft (Ankunft);
alter table tbl_buchung add index idx_Abreise (Abreise);
alter table tbl_person add index idx_Name (Name(20));
alter table tbl_position add index idx_erfasst (erfasst);

Linking foreign keys

For tbl_buchung

alter table tbl_buchung add constraint fk_person_buchung foreign key (Person_FS) references tbl_person (Person_ID);
alter table tbl_buchung add constraint fk_land_buchung foreign key (Land_FS) references tbl_land (Land_ID);

For tbl_position

alter table tbl_position add constraint fk_buchung_position foreign key (Buchung_FS) references tbl_buchung (Buchung_ID);
alter table tbl_position add constraint fk_benutzer_position foreign key (Benutzer_FS) references tbl_benutzer (Benutzer_ID);
alter table tbl_position add constraint fk_leistung_position foreign key (Leistung_FS) references tbl_leistung (LeistungID);

Optimizing the Database

analyze table tbl_benutzer, tbl_buchung, tbl_land, tbl_leistung, tbl_person, tbl_position;
optimize table tbl_benutzer, tbl_buchung, tbl_land, tbl_leistung, tbl_person, tbl_position;

Making users and managing permissions

Create users

create user if not exists 'benutzer'@'localhost' identified by 'Example_User_Password123';
create user if not exists 'management'@'localhost' identified by 'Example_Mgmt_Password123';

Set "benutzer" Priviliges

grant select, update on tbl_person to 'benutzer'@'localhost';
grant select, insert, update (Benutzer_ID, Benutzername, Vorname, Name, Benutzergruppe, erfasst, aktiv) 
on tbl_benutzer to 'benutzer'@'localhost';
grant select (deaktiviert) on tbl_benutzer to 'benutzer'@'localhost';
grant select, insert, update, delete on tbl_buchung to 'benutzer'@'localhost';
grant select, insert, update, delete on tbl_position to 'benutzer'@'localhost';
grant select on tbl_land to 'benutzer'@'localhost';
grant select on tbl_leistung to 'benutzer'@'localhost';

Set "management" Priviliges

grant select on tbl_position to 'management'@'localhost';
grant select on tbl_buchung to 'management'@'localhost';
grant select, insert, update, delete on tbl_person to 'management'@'localhost';
grant select, insert, update, delete on tbl_benutzer to 'management'@'localhost';
grant select, insert, update, delete on tbl_land to 'management'@'localhost';
grant select, insert, update, delete on tbl_leistung to 'management'@'localhost';

Reload priviliges

flush privileges;

Were done! All requirements are fullfilled!

A dump of the ddatabase can be found in this directory : t.b.lb3.sql (Done with : mysqldump backpacker_lb3 > t.b.lb3.sql