-- #######################################################
-- auto generated ddl-script #############################
-- generated sql creation script for ER model
-- database-##############################################
drop database if exists school_careerdb_20240813;
create database school_careerdb_20240813;
\c school_careerdb_20240813;
-- 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 generated by default as identity (start with 1000) ,
     s_firstname varchar(100) not null,
     s_lastname varchar(100) not null,
     primary key(s_studentnumber)
);

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

-- table g_graduated
insert into g_graduated(g_s_studentnumber,g_i_institutenumber,g_date) values
		('1000','4702','1989-11-11'),
		('1001','8852','2017-2-8'),
		('1002','8725','2014-2-1'),
		('1003','2786','1961-5-14'),
		('1004','4809','2008-9-18'),
		('1005','9893','2016-1-16'),
		('1006','3198','1951-2-17'),
		('1007','2780','1955-8-1'),
		('1008','5156','2015-6-12'),
		('1009','9209','1985-7-7');

-- table i_institution
insert into i_institution(i_institutenumber,i_name,i_monthlytopay) values
		('4702','ygpietfnrm','61305.92'),
		('8852','mpyoxqmucp','1037.58'),
		('8725','tqbbozpsak','57310.43'),
		('2786','yijddrtcaf','55143.79'),
		('4809','pmioimrkkq','2238.74'),
		('9893','pszsogzaos','29110.50'),
		('3198','rmqflkzoqw','61001.66'),
		('2780','aphfrnoxei','84256.61'),
		('5156','krrozzvjve','66569.71'),
		('9209','saxhxklavo','10176.6');

-- table s_student
insert into s_student(s_studentnumber,s_firstname,s_lastname) values
		('1000','Kurt','Pomaroli'),
		('1001','Ilona','Entner'),
		('1002','Josef','Fürth'),
		('1003','Ewald','Zellot'),
		('1004','Karl','Pirker'),
		('1005','Christine','Kober'),
		('1006','Christian','Bulfon'),
		('1007','Sabrina','Jäger'),
		('1008','Ulrike','Klug'),
		('1009','Florian','Mayerhofer'),
		('1010','Sabine','Pernter'),
		('1011','Verena','Krippner'),
		('1012','Jolanda','Grabner'),
		('1013','Marianne','Kuranda'),
		('1014','Robert','Eugen Bösch'),
		('1015','Alois','Eich'),
		('1016','Josef','Waldhäusl'),
		('1017','Iring','Römer'),
		('1018','Franz','Weikhart'),
		('1019','Herta','Eichinger'),
		('1020','Ana','Mayer'),
		('1021','Sepp','Widmayer'),
		('1022','Johann','Dvorak'),
		('1023','Georg','Wallner'),
		('1024','Petra','Wolfmayr'),
		('1025','Hans','Grimling'),
		('1026','Kristina','Rieder'),
		('1027','Cordelia','Kaiser'),
		('1028','Franz','Stadler'),
		('1029','Franz','Schnofl'),
		('1030','Petra','Elmecker'),
		('1031','Gertrude','Bernkopf'),
		('1032','Sabine','Brunauer'),
		('1033','Margarete','Sonnberger'),
		('1034','Peter','Fahrner'),
		('1035','Friedrich','Markowitz'),
		('1036','Bernhard','Achleitner'),
		('1037','Stefan','Korun'),
		('1038','Josef','Hartmann'),
		('1039','Karl','Schweiger-Stenzel'),
		('1040','Erich','Leichtfried'),
		('1041','Franz','Doppler'),
		('1042','Theodor','Seliger'),
		('1043','Ludwig','Tödling'),
		('1044','Karl','Kadenbach'),
		('1045','Walter','Adler'),
		('1046','Felix','Scheibenreif'),
		('1047','Hermann','Posch'),
		('1048','Josef','Oberländer'),
		('1049','Stefanie','Fazekas'),
		('1050','Robert','Höll'),
		('1051','Herwig','Thurnher'),
		('1052','Johannes','Silhavy'),
		('1053','Mauritius','Mairinger'),
		('1054','Josef','Morawitz'),
		('1055','Brunhilde','Czernin'),
		('1056','Christian','Tonn'),
		('1057','Ernst','Pitschmann'),
		('1058','Karl','Berl'),
		('1059','Bernd','Lugar'),
		('1060','Friedrich','Rumpold'),
		('1061','Johann','Kogler'),
		('1062','Gerhard','Herold'),
		('1063','Otto','Peck'),
		('1064','Johann','Fichtenbauer'),
		('1065','Alois','Wutte'),
		('1066','Karl','Parnigoni'),
		('1067','Herbert','Gaisbichler'),
		('1068','Karl','Bramböck'),
		('1069','Hubert','Unterwurzacher'),
		('1070','Oskar','Letmaier'),
		('1071','Anton','Michalek'),
		('1072','Bettina','Konir'),
		('1073','Franz','Sattlberger'),
		('1074','Harald','Krismanich'),
		('1075','Norbert','Vonwald'),
		('1076','Oswald','Rapatz'),
		('1077','Ignaz','Mikola'),
		('1078','Dominika','Deutsch'),
		('1079','Franz','Mendelsburg'),
		('1080','Hubert','Pischl'),
		('1081','Gerd','Forstner'),
		('1082','Hermine','Brandl'),
		('1083','Fritz','Kostelecky'),
		('1084','Johann','Fauland'),
		('1085','Günther','Zaun'),
		('1086','Anton','Mertel'),
		('1087','Johann','Eder'),
		('1088','Erwin','Bischof'),
		('1089','Kurt','Steidl'),
		('1090','Franz','Hahn'),
		('1091','Franz','Herold'),
		('1092','Hans','Wittmann'),
		('1093','Johann','Ulbrich'),
		('1094','Paul','Rauchenberger'),
		('1095','Gustav','Eugen Bösch'),
		('1096','Josef','Nigl'),
		('1097','Viktor','Nagele'),
		('1098','Ludwig','Dvorak'),
		('1099','Raimund','Mayerhofer');

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