-- #######################################################
-- 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','8822','1993-10-14'),
		('1001','2784','1994-8-22'),
		('1002','6367','1960-12-7'),
		('1003','4598','1943-4-19'),
		('1004','705','1971-5-14'),
		('1005','6146','1948-6-12'),
		('1006','7441','1973-7-6'),
		('1007','5280','1948-1-16'),
		('1008','6589','1999-4-30'),
		('1009','3539','1984-11-14');

-- table i_institution
insert into i_institution(i_institutenumber,i_name,i_monthlytopay) values
		('8822','qeamanmgee','51329.23'),
		('2784','iqwaxbucax','77850.17'),
		('6367','odofrpofbu','7322.78'),
		('4598','hkimntmwss','19805.91'),
		('705','lsmwawvbyn','60998.16'),
		('6146','psbxjmllsx','82384.20'),
		('7441','jexuutrdqm','30602.45'),
		('5280','bosgiddgqj','49330.4'),
		('6589','qkbojijawp','44249.83'),
		('3539','drzvmlwuhq','17903.56');

-- table s_student
insert into s_student(s_studentnumber,s_firstname,s_lastname) values
		('1000','Alois','Dittrich'),
		('1001','Leopold','Gföllner'),
		('1002','Roman','Krämer'),
		('1003','Kamill','Renoldner'),
		('1004','Felix','Wieser'),
		('1005','Adolf','Boden'),
		('1006','Sonja','Ebner'),
		('1007','Helga','Roitinger'),
		('1008','Oskar','Wotawa'),
		('1009','Franz','Schweigkofler'),
		('1010','Stefanie','Frizberg'),
		('1011','Ruth','Rudel-Zeynek'),
		('1012','Karl','Leibetseder'),
		('1013','Karel','Frisch'),
		('1014','Otto','Wagner'),
		('1015','Bernhard','Schleinzer'),
		('1016','Johann','Holper'),
		('1017','Otto','Weinberger'),
		('1018','Elfriede','Reismann'),
		('1019','Karl','Haimbuchner'),
		('1020','Bernhard','Rehrl'),
		('1021','Helmut','Maier'),
		('1022','Eduard','Schlager'),
		('1023','Josefine','Prader'),
		('1024','Walter','Richau'),
		('1025','Kurt','Klamt'),
		('1026','Matthias','Schnell'),
		('1027','Hans','Hatzl'),
		('1028','Friedrich','Pröll'),
		('1029','Alois','Zangel'),
		('1030','Reinhold','Sponner'),
		('1031','Karl','Grillitsch'),
		('1032','Josef','Rosenkranz'),
		('1033','Josef','Neugebauer'),
		('1034','Vinzenz','Glockner'),
		('1035','Alois','Niedrist'),
		('1036','Edith','Pleyl'),
		('1037','Hermann','Schweitzer'),
		('1038','Wilhelm','Mohnl'),
		('1039','Walter','Fohringer'),
		('1040','Melike','Bednar'),
		('1041','Franz','Pichler'),
		('1042','Christiane','Deutsch'),
		('1043','Johann','Gartlehner'),
		('1044','Günter','Witzany'),
		('1045','Friedrich','Gugg'),
		('1046','Gerhard','Lichtenberger'),
		('1047','Elisabeth','Steinmaßl'),
		('1048','Karl','Knoll'),
		('1049','Josef','Böhacker'),
		('1050','Rupert','Kittinger'),
		('1051','Hans','Haslauer'),
		('1052','Karl','Schausberger'),
		('1053','Matthias','Gumplmayer'),
		('1054','Ludwig','Fazekas'),
		('1055','Heinrich','Grießner'),
		('1056','Eduard','Wagner'),
		('1057','Hans','Tomaschitz'),
		('1058','Gerald','Karl'),
		('1059','Gerald','Molinari'),
		('1060','Wilhelm','Höfinger'),
		('1061','Elfriede','Waneck'),
		('1062','Astrid','Plassnik'),
		('1063','Josef','Hochsteiner'),
		('1064','Engelbert','Schürer'),
		('1065','Erich','Wedra'),
		('1066','Hans','Schwemlein'),
		('1067','Josef','Paulitsch'),
		('1068','Kerstin','Geisler'),
		('1069','Karlheinz','Androsch'),
		('1070','Kurt','Weichenberger'),
		('1071','Karl','Roth-Halvax'),
		('1072','Helmuth','Kabesch'),
		('1073','Hans','Nußbaumer'),
		('1074','Rudolf','Gabriel'),
		('1075','Josef','Oberhammer'),
		('1076','Friedrich','Berger'),
		('1077','Fritz','Pelikan'),
		('1078','Christof','Hammerstorfer'),
		('1079','Wilhelm','Steinhuber'),
		('1080','Maria','Povysil'),
		('1081','Peter','Mader'),
		('1082','Oswald','Tumpel'),
		('1083','Christoph','Walch'),
		('1084','Willi','Muchitsch'),
		('1085','Ilse','Graf'),
		('1086','Freda','Mayrhofer'),
		('1087','Max','Thalhammer'),
		('1088','Johanna','Auinger'),
		('1089','Hans','Winsauer'),
		('1090','Franz','Nürnberger'),
		('1091','Franz','Grasser'),
		('1092','Hans-Joachim','Hosch'),
		('1093','Bruno','Kernmaier'),
		('1094','Josef','Schacherl'),
		('1095','Siegfried','Kreilmeir'),
		('1096','Günther','Dostal'),
		('1097','Brunhilde','Todt'),
		('1098','Odo','Danneberg'),
		('1099','Klara','Mairinger');

-- 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;