-- #######################################################
-- auto generated ddl-script #############################
-- generated sql creation script for ER model
-- database-##############################################
drop database if exists 20240813_school_careerdb;
create database 20240813_school_careerdb;
use 20240813_school_careerdb;
-- switch autocommit off
set autocommit=0;
-- to allow load data from any directory
set global local_infile=1;
-- tables-################################################
-- table g_graduated
create table g_graduated(
     g_s_studentnumber bigint not null,
     g_i_institutenumber int not null,
     g_date date not null,
     primary key(g_s_studentnumber,g_i_institutenumber)
);

-- table i_institution
create table i_institution(
     i_institutenumber int not null,
     i_name varchar(100) not null,
     i_monthlytopay decimal(7,2) not null,
     primary key(i_institutenumber)
);

-- table s_student
create table s_student(
     s_studentnumber bigint not null auto_increment ,
     s_firstname varchar(100) not null,
     s_lastname varchar(100) not null,
     primary key(s_studentnumber)
)auto_increment=1000;

-- insert-#################################################

-- table g_graduated
insert into g_graduated(g_s_studentnumber,g_i_institutenumber,g_date) values
		('1000','912','1959-2-10'),
		('1001','8917','1979-6-17'),
		('1002','9156','2005-2-3'),
		('1003','3589','1954-11-28'),
		('1004','7895','2008-11-8'),
		('1005','1935','1992-6-1'),
		('1006','9959','1957-11-1'),
		('1007','7098','2011-4-2'),
		('1008','3964','1956-5-10'),
		('1009','7047','2012-4-7');

-- table i_institution
insert into i_institution(i_institutenumber,i_name,i_monthlytopay) values
		('912','wmalblamaj','71767.47'),
		('8917','znpvbouuyw','50739.89'),
		('9156','jzeyipuhmn','79990.56'),
		('3589','edwuvxdckh','93241.62'),
		('7895','itvqzccfdo','86243.82'),
		('1935','stkgbrdnae','15619.11'),
		('9959','nbdobjlurw','24313.16'),
		('7098','vhsbkyjfnb','30547.70'),
		('3964','baprzpzabs','11218.55'),
		('7047','negucaiatu','35709.79');

-- table s_student
insert into s_student(s_studentnumber,s_firstname,s_lastname) values
		('1000','Felix','Peter'),
		('1001','Ambros','Klaus'),
		('1002','Paul','Aigner'),
		('1003','Franz','Scheuch'),
		('1004','Gerhard','Rossmann'),
		('1005','Ingrid','Jannach'),
		('1006','Otto','Scheibenreif'),
		('1007','Emmerich','Guggenberger'),
		('1008','Hermine','Müller'),
		('1009','Georg','Buder'),
		('1010','Karl','Schönbauer'),
		('1011','Walter','Schabes'),
		('1012','Adalbert','Ludwig'),
		('1013','Josef','Herbert'),
		('1014','Karl-Heinz','Hofer'),
		('1015','Viktor','Prechtl'),
		('1016','Franz','Cortolezis-Schlager'),
		('1017','Franz','Karlsson'),
		('1018','Erich','König'),
		('1019','Gerrit','Partik'),
		('1020','Anton','Groß'),
		('1021','Johann','Kuhn'),
		('1022','Karl','Jury'),
		('1023','Johanna','Ebner'),
		('1024','Hans','Rehrl'),
		('1025','Michael','Steinegger'),
		('1026','Patrick','Kerber'),
		('1027','Claudia','Margaretha'),
		('1028','Julius','Muhr'),
		('1029','Paul','Bulfon'),
		('1030','Heinrich','Pisec'),
		('1031','Reinhold','Mayrhofer'),
		('1032','Stefan','Krammer'),
		('1033','Werner','Katschthaler'),
		('1034','Michael','Rinner'),
		('1035','Sophie','Kanovsky-Wintermann'),
		('1036','Franz','Schieder'),
		('1037','Andreas','Konecny'),
		('1038','Gerhard','Prammer'),
		('1039','Hermann','Diwald'),
		('1040','Brunhilde','Binder-Maier'),
		('1041','Johann','Ermacora'),
		('1042','Leopold','Peter Schieder'),
		('1043','Heinz','Stöger'),
		('1044','Josef','Ettl'),
		('1045','Alois','Pirker'),
		('1046','Gudrun','Hagen'),
		('1047','Ridi','Bauer'),
		('1048','Anton','Walcher'),
		('1049','Josef','Gruber'),
		('1050','Albrecht','Weinberger'),
		('1051','Ottilie','Tschitschko'),
		('1052','Benita','Grimling'),
		('1053','Norbert','Hietl'),
		('1054','Angelika','Bures'),
		('1055','Edith','Eggendorfer'),
		('1056','Johann','Mathis'),
		('1057','Adolf','Brinek'),
		('1058','Alois','Pleyl'),
		('1059','MMichael','Unterkircher'),
		('1060','Georg','Unterkircher'),
		('1061','Karl','Einwallner'),
		('1062','Walter','Heinz'),
		('1063','Christine','Wallner'),
		('1064','Franz','Mühlbachler'),
		('1065','Karl','Hartl'),
		('1066','Anton','Walla'),
		('1067','Richard','Afritsch'),
		('1068','Karl','Beck'),
		('1069','Georg','Verzetnitsch'),
		('1070','Anton','Lengauer'),
		('1071','Franz','Leithenmayr'),
		('1072','Hans','Bachner'),
		('1073','Alois','Haunschmid'),
		('1074','Gerd','Mayr'),
		('1075','Josef','Krusche'),
		('1076','Ignaz','Kallir'),
		('1077','Gerhard','Fürst'),
		('1078','Leopold','Waldbrunner'),
		('1079','Hermann','Fachleutner'),
		('1080','Josef','Hiermann'),
		('1081','Josef','Emhart'),
		('1082','Inge','Genner'),
		('1083','Josef','Minkowitsch'),
		('1084','Hubert','Zangerl'),
		('1085','Franz','Hüttenberger'),
		('1086','Ferdinand','Seidinger'),
		('1087','Robert','Hillegeist'),
		('1088','Josef','Rehrl'),
		('1089','Helmut','Fritz Eckert'),
		('1090','Ferdinand','Jedek'),
		('1091','Franz','Pernter'),
		('1092','Pietro','Hofstetter'),
		('1093','Koloman','Kemetter'),
		('1094','Martin','Guggi'),
		('1095','Alois','Böhacker'),
		('1096','Gabriel','Hopfer'),
		('1097','Hans','Novak'),
		('1098','Josef','Neuhauser'),
		('1099','Wilhelm','Knirsch');

-- foreign keys-#################################################
alter table g_graduated
add foreign key (g_i_institutenumber) references i_institution(i_institutenumber) on delete restrict on update restrict,
add foreign key (g_s_studentnumber) references s_student(s_studentnumber) on delete restrict on update restrict;
-- commit all changes
commit;