From 4c00ac22729c3f354df423373da8da6fa22617ba Mon Sep 17 00:00:00 2001 From: lonkaars Date: Sat, 8 Oct 2022 17:04:43 +0200 Subject: alle opdrachten van eindopdracht behalve applicatie maken zelf af --- .gitignore | 8 +- common.mk | 2 +- eindopdracht/b5.sql | 42 + eindopdracht/eindopdracht.m4 | 52 + eindopdracht/imgs.drawio | 1 + eindopdracht/imgs_database_diagram.svg | 1760 ++++++++++++++++++++++++++++++++ eindopdracht/imgs_erd.svg | 3 + eindopdracht/imgs_strokendiagram.svg | 3 + eindopdracht/import.js | 47 + eindopdracht/makefile | 16 + eindopdracht/q8.sql | 12 + eindopdracht/reset.sql | 6 + eindopdracht/t8.sql | 1 + eindopdracht/t9.sql | 9 + opdracht-1/reset.sql | 8 +- 15 files changed, 1962 insertions(+), 8 deletions(-) create mode 100644 eindopdracht/b5.sql create mode 100644 eindopdracht/eindopdracht.m4 create mode 100644 eindopdracht/imgs.drawio create mode 100644 eindopdracht/imgs_database_diagram.svg create mode 100644 eindopdracht/imgs_erd.svg create mode 100644 eindopdracht/imgs_strokendiagram.svg create mode 100755 eindopdracht/import.js create mode 100644 eindopdracht/makefile create mode 100644 eindopdracht/q8.sql create mode 100644 eindopdracht/reset.sql create mode 100644 eindopdracht/t8.sql create mode 100644 eindopdracht/t9.sql diff --git a/.gitignore b/.gitignore index 5c49fdf..54e250c 100644 --- a/.gitignore +++ b/.gitignore @@ -1,7 +1,9 @@ -opdracht-*/*.md -opdracht-*/.tmp.sql +**/*.md +**/.tmp.sql opdracht-*/opdracht.pdf -opdracht-*/opdracht-*.md +eindopdracht/nfs.xlsx +eindopdracht/nfs.csv +eindopdracht/q5.sql # latex files **/*.aux diff --git a/common.mk b/common.mk index 3770740..20b0474 100644 --- a/common.mk +++ b/common.mk @@ -24,6 +24,6 @@ reset: .tmp.sql $(TARGET).md: $(TESTS_MD) $(TARGET).m4 $(M4) $(TARGET).m4 > $(TARGET).md -clean: +clean:: $(RM) $(wildcard *.md) .tmp.sql diff --git a/eindopdracht/b5.sql b/eindopdracht/b5.sql new file mode 100644 index 0000000..fa7a548 --- /dev/null +++ b/eindopdracht/b5.sql @@ -0,0 +1,42 @@ +create table if not exists Land ( + ID int unsigned not null auto_increment, + Naam varchar(90) not null, + primary key (ID), + unique(Naam) +); + +create table if not exists Locatie ( + ID int unsigned not null auto_increment, + LandID int unsigned not null, + Naam varchar(90) not null, + primary key (ID), + foreign key (LandID) references Land(ID), + unique(Naam) +); + +create table if not exists Merk ( + ID int unsigned not null auto_increment, + Naam varchar(90) not null, + primary key (ID), + unique(Naam) +); + +create table if not exists Hoofdkantoor ( + ID int unsigned not null auto_increment, + LocatieID int unsigned not null, + MerkID int unsigned not null, + primary key (ID), + foreign key (LocatieID) references Locatie(ID), + foreign key (MerkID) references Merk(ID) +); + +create table if not exists `Type` ( + ID int unsigned not null auto_increment, + Naam varchar(90) not null, + MerkID int unsigned not null, + Vermogen int unsigned not null, + primary key (ID), + foreign key (MerkID) references Merk(ID), + unique(Naam) +); + diff --git a/eindopdracht/eindopdracht.m4 b/eindopdracht/eindopdracht.m4 new file mode 100644 index 0000000..ce4d795 --- /dev/null +++ b/eindopdracht/eindopdracht.m4 @@ -0,0 +1,52 @@ +changequote(`{{', `}}') +include({{../common.m4}}) + +# Eindopdracht + +## Opdracht 1 + +1. Data bekijken en redundante kolommen in eigen tabellen zetten +2. Strokendiagram maken +3. SQL script maken om de database tabellen te initialiseren +4. SQL scripts maken om de tabellen te vullen met data uit de spreadsheet + +## Opdracht 2 + +- Hoofdkantoren kunnen in een eigen tabel, en naar gerefereerd worden via id + nummers +- Merken kunnen in een eigen tabel, en naar gerefereerd worden via id nummers +- Types kunnen in een eigen tabel opgeslagen worden en een referentie hebben + naar een merk +- Vermogen wordt in twee eenheden opgeslagen terwijl de ene eenheid van de + andere afgeleid kan worden + +## Opdracht 3 + +![](imgs_erd.svg) + +## Opdracht 4 + +![](imgs_strokendiagram.svg) + +De keuze om bij de `Hoofdkantoor` tabel geen alternate key boven de `LocatieID` en +`MerkID` te zetten is een bewuste, op deze manier kan er namelijk onderscheid +blijven tussen meerdere bedrijven die in verschillende delen van een +kantoorgebouw zitten. + +q_norm(5) + +De `insert` commando's worden door een script gegenereerd die de excel +spreadsheet als csv inneemt, zie makefile voor implementatie. + +## Opdracht 6 + +![](imgs_database_diagram.svg) + +## Opdracht 7 + +De primary/foreign key relaties zijn al aangelegd door de database initalisatie +query. + +q_with_test(8) +q_with_output(9) + diff --git a/eindopdracht/imgs.drawio b/eindopdracht/imgs.drawio new file mode 100644 index 0000000..ff79943 --- /dev/null +++ b/eindopdracht/imgs.drawio @@ -0,0 +1 @@ +7Vpbb5swFP41PKaCALk8Nrd2XbJlTbd2e5k8cAAFMDImCf31M8VcTdIsaoBIk6KI89k+xp8/n3MgEeSxs7/DwDMXSIe20BX1vSBPhG532OvT7wgIY0AdMsDAlh5DUgasrFfIQJGhgaVDv9CRIGQTyyuCGnJdqJECBjBGu2K3NbKLs3rAgByw0oDNo8+WTswYHahiht9DyzCTmSWRtTgg6cwA3wQ62hUguCcz5BJ2i0uIHeBCl9CWBcAbiAV1ahISrfRW6M7oZx31vjEQMmwIPMu/0ZBDYc2nXWZr4Fh2RHPO0Yg5otPJU0EeY4RIfOXsx9COtirZhvieZgdaUx5w5PeEAc+LX9v+bvwj3E0+r7x7uTMSv3e6sZctsAPG7wLiDeOHhAnpGAWuDiNHoiCPdqZF4MoDWtS6oyqjmEkcm1oSvWQuISZwf/BepZQBKlSIHEhwSLuwAV2Fbcgu2+I+g8zc7soMA2zHjNRTRgS9YFz8Ay8yx8tT6MHmeGmKB4Xj4R6htb4BLj30uHGdKCqvk/TA10KQyhHkYaQHGqTL4ukxkfMn8GuhJqUhT41UJzU9jhoTwnWzrMgVgqn1QPU5Ujg+oE4THjPpsiwSPkIbEAu506xlFGUelp2lbpEn6Oq3Uaal5vSRJh79qxuNKB5O6hmHL3njZ2TcqIk52ecbJ2Fi7S3ykkxEr3OjqJUNioxkDEuuOpfZS9tGaUAB1uD7540AbEDyXmI7Gkcr40QK4jfKt8UbrlIDm2OJrLc6IUlgiaiSKqukp3idbFA+aZf8yCU/nbKjmAjO0Zs203WfL9dB3XJFLnxCC+CGhwUrnSdYsd2Clf8L9iMEK0ktVOx1hdjeiYpVmlWsLBaVppypWKXkp9O7mGKX8sN68zRffpqLndeOtr0zto8dXrBzpFFiGnzaYAN6w/qKpUpq+KdTnwAQ7Z3l8uzUWEaqA56ZQZ3M8M+nF45y75aR15WVT41xTZeRg2Jsks4NcvLgeLC8cIzjXyM0LdfWpuRjgTAv18O5pDm5KsrHpGRVaTYl8y91LizXa37qOVWwbawhr1CwQF0M5+Fv/dss+OI/BLNvClxV1JCW33yV1K+oHy9VJVXSwtePc5oXeE5qrquHFa9mL1VXV/LSvuqxten42HHLR7fD+msuug1KUWl4ZnQblqObVG90q716vOZ0fKpg21g/tl+w1Mx+sY+7Z/+ykKd/AQ==7Vxbd9o4EP41PIbjK9iPAUKbLsk2TdPd7EuPsAU4GIsjRMD59ZWxhG1J0Cw3OwW/xBpLsvTNfDPSyKFmtifLTxhMR3fIh2HN0PxlzezUDEN3HJf+SSRxKmlYeioY4sBnlTLBY/AGmVBj0nngw1mhIkEoJMG0KPRQFEGPFGQAY7QoVhugsPjWKRhCSfDogVCW/hP4ZJRKHVvL5J9hMBzxN+saezIBvDITzEbAR4ucyLypmW2MEEnvJss2DBPwOC5pu+6Gp+uBYRiR9zT4YQ0XV7D3BLrL70/tB7+10PtXrJdXEM7ZhG87bLgk5hhgNI98mHSj1czWYhQQ+DgFXvJ0QbVOZSMyCWlJp7fysPg7ICZwmROxYX6CaAIJjmkV9nSt/JhbESsvMg00mWiUA58jDZjOh+ueM1joDUPmf6BkSCjdAzApHyenYjjplgTUHcRjCSg6P1JEY0YwGsM2ChGmkghFtGZrEIShIAJhMIxoEaezaSVgBZSt10w+CXw/eYsS/aJ+DqGApqAAW1aAc0oFWI6kAAl8GPnXiWOkJS8Es1ngrfAHmMjinIbeCR70C/5Uhi4Hja2AhsswDAEJXoteWIUXe8NXFNCRrDVjGkXNGJZbbzaKvczQHHuQNcw7TrEvV+7LdYt9UfiGkEh9rZS4nvweenUlvQJZsRT6HujTAFxQHKeMR1UF8TbOYDgL3kB/1V+i5GkyndUE7VbN7lBJmHTfQtiHmBOzZpjd1ZVYEeVbEA2/MXWqDWW74YrcW0d2Nq5C8CwY1q4Ww6ugwWAG99WeeQOul90Xvz+P+y68XzrduytF+DhMkFWAJTFyo+8SXZd2utihBMk8WozdCyZpLXJCnP6ybsJ5l4wH3Z+vnVvDm/wdg6Ot2HZDibvExulQ+foQwy/9peeY5nOn4b9cP4Inbj2CN3xkRYTJCA1RBMKbTCpEtKxOD6Epg+YFEhKzTQmYE1QEDi4D8m/SvG6z0jPrLLnvLPOFmBciOt1co6T4zPtLClmzVYm3U0ZXZQROw9oWC2KLtDRk/c5t7eFn9zJzeSXZQx59L6ye6yzd+BVrvIvxU7X85zq338xm8Dp++zkat++n9zPuPvPGv3ljfALj3zZIYRtVAcvXrfJMX4lUJZdW601MVVCq6NpK2qSVjZMcdL7HUyjhVKH0xV74W6KdWjL+x0pfKAOZdQlk7w5k9okC2arpNcYgzlVgmYGN6RfLsIuWZQq5aaE+NcVt9elNOoJdt+XbEKxelJUSTmX7xYaE1A+IJ2gIo9KxshoVW4zLViU7tcNlYH/vSsrKwEqLoH0ysIps7vEysEq1yhSYKoNVRROw786rFi1os4VXNP+qHHDzQsmUNuJ5YVNwgu/moy11VKchO7uMk3JTPvT6UIcjh+Rm86NxUz7YOlNuiucMu3JTQfIyucmndQmczNY/Ejl1ORF5nuwUMv6OW3d2Y6eYQHW1ulYiOY0LOYu2/qHYKad1z5OdMqkOEzrFfo7NRjn9fLbr2NS2K8pG5Wn5xgNrSYV/yvGBoRXpwoNkWacH+tmn3nh6zN7RAYofNtjlbh0uOTfBtivqD5Uf8MiLk88IDfwxiAiiGB7eKYZwUKJPFA6uynOCcrKzAida4gpNgdJJD2l0OWVYiS8ixCxQ6Thd0nNqA9bdetPNXba9W8QVFW5odc3I9ds0TxpyuSFdQi63/YqGXLX2Lum6TazakZ2OxM7CelgXsoDHZqecrjvbBMEmA6swO+UVcQ9EvqTAPy49cIKlsPPk9Uevi29krs/tQc9++AJvL5/FZ6L814RKrBSfxW/ZXuxBu700Kse3hEEV3N8c8b/EaDH7zYDUN2W/vGDe/AI= \ No newline at end of file diff --git a/eindopdracht/imgs_database_diagram.svg b/eindopdracht/imgs_database_diagram.svg new file mode 100644 index 0000000..77c5adb --- /dev/null +++ b/eindopdracht/imgs_database_diagram.svg @@ -0,0 +1,1760 @@ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + diff --git a/eindopdracht/imgs_erd.svg b/eindopdracht/imgs_erd.svg new file mode 100644 index 0000000..3edc377 --- /dev/null +++ b/eindopdracht/imgs_erd.svg @@ -0,0 +1,3 @@ + + +
Merk
Merk
Type
Type
Hoofdkantoor
Hoofdkantoor
produceert
produceert
heeft
heeft
Locatie
Locatie
staat in
staat in
is in
is in
Land
LandText is not SVG - cannot display \ No newline at end of file diff --git a/eindopdracht/imgs_strokendiagram.svg b/eindopdracht/imgs_strokendiagram.svg new file mode 100644 index 0000000..913550d --- /dev/null +++ b/eindopdracht/imgs_strokendiagram.svg @@ -0,0 +1,3 @@ + + +
ID
ID
Naam
Naam
Merk
Merk
a
a
ID
ID
Naam
Naam
ID
ID
LocatieID
LocatieID
MerkID
MerkID
ID
ID
Naam
Naam
Type
Type
MerkID
MerkID
Vermogen
Vermogen
p
p
a
a
p
p
p
p
a
a
Locatie
Locatie
p
p
Hoofdkantoor
Hoofdkantoor
ID
ID
Naam
Naam
p
p
a
a
Land
Land
LandID
LandIDText is not SVG - cannot display \ No newline at end of file diff --git a/eindopdracht/import.js b/eindopdracht/import.js new file mode 100755 index 0000000..7f553b5 --- /dev/null +++ b/eindopdracht/import.js @@ -0,0 +1,47 @@ +#!/bin/node + +var fs = require("fs"); +var lines = fs.readFileSync(process.argv[2]).toString().trim().split("\n"); +var records = []; + +function objArrToSQLInsertArr(objArr) { + var temp = JSON.stringify(objArr).replaceAll("[", "(").replaceAll("]", ")"); + return temp.substr(1, temp.length - 2) +} + +lines.forEach(line => { + var columns = line.split("\t"); + var plaats = columns[4].split(","); + records.push({ + "Merk": columns[0], + "Type": columns[1], + "Vermogen": Number(columns[3]), + "Locatie": plaats[0].trim(), + "Land": plaats[1].trim(), + }); +}); + +var landen = []; +records.forEach(record => landen.push(record.Land)); +landen = Array.from(new Set(landen)); +console.log(`insert into Land (Naam) values ${objArrToSQLInsertArr(landen.map(i => [i]))};`); + +var merken = []; +records.forEach(record => merken.push(record.Merk)); +merken = Array.from(new Set(merken)); +console.log(`insert into Merk (Naam) values ${objArrToSQLInsertArr(merken.map(i => [i]))};`); + +var locaties = []; +records.forEach(record => locaties.push(JSON.stringify([record.Locatie, landen.indexOf(record.Land) + 1]))); +locaties = Array.from(new Set(locaties)).map(i => JSON.parse(i)); +console.log(`insert into Locatie (Naam, LandID) values ${objArrToSQLInsertArr(locaties)};`); + +var hoofdkantoren = []; +records.forEach(record => hoofdkantoren.push(JSON.stringify([locaties.indexOf(locaties.find(x => x[0] == record.Locatie)) + 1, merken.indexOf(record.Merk) + 1]))); +hoofdkantoren = Array.from(new Set(hoofdkantoren)).map(i => JSON.parse(i)); +console.log(`insert into Hoofdkantoor (LocatieID, MerkID) values ${objArrToSQLInsertArr(hoofdkantoren)};`); + +var types = []; +records.forEach(record => types.push(JSON.stringify([merken.indexOf(record.Merk) + 1, record.Type, record.Vermogen]))); +types = Array.from(new Set(types)).map(i => JSON.parse(i)); +console.log(`insert into \`Type\` (MerkID, Naam, Vermogen) values ${objArrToSQLInsertArr(types)};`); diff --git a/eindopdracht/makefile b/eindopdracht/makefile new file mode 100644 index 0000000..3bac82c --- /dev/null +++ b/eindopdracht/makefile @@ -0,0 +1,16 @@ +RM = rm -f +TARGET := eindopdracht +QUERY_SQL += q5.sql + +all: $(TARGET).md + +nfs.csv: nfs.xlsx + xlsx2csv -dtab -qnone $< | sed '1d' > $@ + +q5.sql: nfs.csv + ./import.js $< | cat b5.sql - > $@ + +clean:: + rm q5.sql nfs.csv + +include ../common.mk diff --git a/eindopdracht/q8.sql b/eindopdracht/q8.sql new file mode 100644 index 0000000..4a1151a --- /dev/null +++ b/eindopdracht/q8.sql @@ -0,0 +1,12 @@ +create or replace view NFS_Excel as +select + Merk.Naam as "Merk", + `Type`.Naam as "Type", + round(`Type`.Vermogen * 1.3637) as "Vermogen [pk]", + `Type`.Vermogen as "Vermogen [kW]", + concat(Locatie.Naam, ", ", Land.Naam) as "Hoofdkantoor" +from `Type` +left join Merk on Merk.ID = `Type`.MerkID +left join Hoofdkantoor on Hoofdkantoor.MerkID = Merk.ID +left join Locatie on Locatie.ID = Hoofdkantoor.LocatieID +left join Land on Land.ID = Locatie.LandID; diff --git a/eindopdracht/reset.sql b/eindopdracht/reset.sql new file mode 100644 index 0000000..306d94b --- /dev/null +++ b/eindopdracht/reset.sql @@ -0,0 +1,6 @@ +drop table if exists Hoofdkantoor; +drop table if exists `Type`; +drop table if exists Merk; +drop table if exists Locatie; +drop table if exists Land; + diff --git a/eindopdracht/t8.sql b/eindopdracht/t8.sql new file mode 100644 index 0000000..cebeddf --- /dev/null +++ b/eindopdracht/t8.sql @@ -0,0 +1 @@ +select * from NFS_Excel; diff --git a/eindopdracht/t9.sql b/eindopdracht/t9.sql new file mode 100644 index 0000000..6c72b90 --- /dev/null +++ b/eindopdracht/t9.sql @@ -0,0 +1,9 @@ +select + Land.Naam, + count(`Type`.ID) as "Aantal types" +from `Type` +left join Merk on Merk.ID = `Type`.MerkID +left join Hoofdkantoor on Hoofdkantoor.MerkID = Merk.ID +left join Locatie on Locatie.ID = Hoofdkantoor.LocatieID +left join Land on Land.ID = Locatie.LandID +group by Land.ID; diff --git a/opdracht-1/reset.sql b/opdracht-1/reset.sql index c2f5ee9..097194f 100644 --- a/opdracht-1/reset.sql +++ b/opdracht-1/reset.sql @@ -1,5 +1,5 @@ -drop table OrderProduct; -drop table `Order`; -drop table Product; -drop table Klant; +drop table if exists OrderProduct; +drop table if exists `Order`; +drop table if exists Product; +drop table if exists Klant; -- cgit v1.2.3