-- #######################################################
-- auto generated ddl-script #############################
-- generated sql creation script for ER model
-- switch autocommit off
set implicit_transactions off;
-- database-##############################################
use master;
drop database if exists school_careerdb_20240813;
create database school_careerdb_20240813;

begin transaction 
go 
use 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 identity (1000,1) ,
     s_firstname varchar(100) not null,
     s_lastname varchar(100) not null,
     primary key(s_studentnumber)
);

-- insert-#################################################
begin transaction;

-- table g_graduated
insert into g_graduated(g_s_studentnumber,g_i_institutenumber,g_date) values
		('1000','483','2004-12-3'),
		('1001','5684','2013-4-30'),
		('1002','4123','2011-11-11'),
		('1003','451','2014-6-5'),
		('1004','4312','1941-12-18'),
		('1005','9002','2015-6-18'),
		('1006','1309','1972-3-28'),
		('1007','5384','2007-8-10'),
		('1008','8762','1979-10-24'),
		('1009','9814','1945-2-12');

-- table i_institution
insert into i_institution(i_institutenumber,i_name,i_monthlytopay) values
		('483','zukbegapvs','16764.79'),
		('5684','fnhypgncps','1754.64'),
		('4123','zsdzctrgeu','71754.59'),
		('451','bfxsycgkms','95494.14'),
		('4312','vpgqtzfoyy','38078.28'),
		('9002','sgxsqxlrud','38895.30'),
		('1309','enozmeumpe','32087.56'),
		('5384','dscmplfkro','22328.81'),
		('8762','kmwutfmasc','56080.37'),
		('9814','knvkpazjhq','79823.90');

-- table s_student
set identity_insert s_student on;

insert into s_student(s_studentnumber,s_firstname,s_lastname) values
		('1000','Paul','Polster'),
		('1001','Theodor','Gföhler'),
		('1002','Florian','Rosenberger'),
		('1003','Hans','Mühlberghuber'),
		('1004','Josef','Schönsteiner'),
		('1005','Otto','Dinkhauser'),
		('1006','Gerhard','Aburumieh'),
		('1007','Anton','Tonn'),
		('1008','Karl','Rapatz'),
		('1009','Ernst','Hösele'),
		('1010','Simon','Friedmann'),
		('1011','Sina','Bachner'),
		('1012','Helmut','Gantner'),
		('1013','Josef','Obrutschka'),
		('1014','Adam','Aigner'),
		('1015','Albrecht','Rainer'),
		('1016','Hans','Stendebach'),
		('1017','Hermann','Pittermann'),
		('1018','Herbert','Leichtfried'),
		('1019','Alois','Kraker'),
		('1020','Josef','Bures'),
		('1021','Rudolf','Juen'),
		('1022','Hermann','Sattlberger'),
		('1023','Franz','Hainzl'),
		('1024','Gustav','Hahn'),
		('1025','Manuel','Musiol'),
		('1026','Eva','Wunsch'),
		('1027','Hans','Kuchner'),
		('1028','Johanna','Schönpass'),
		('1029','Georg','Seilinger'),
		('1030','Rudolf','Buttinger'),
		('1031','Johann','Iro'),
		('1032','Paula','Jäger'),
		('1033','Walter','Eugen Veselsky'),
		('1034','Marijana','Durry'),
		('1035','Rainer','Bernkopf'),
		('1036','Karl','Guggi'),
		('1037','Alfred','Staffaneller'),
		('1038','Eva-Maria','Winter'),
		('1039','Alois','Fasslabend'),
		('1040','Ilse','Leutner'),
		('1041','Heinrich','Michlmayr'),
		('1042','Wanda','Kolb'),
		('1043','Leo','Ermacora'),
		('1044','Wolfgang','Hums'),
		('1045','Klaus','Reismann'),
		('1046','Harald','John Oppenheimer'),
		('1047','Karl','Hagen'),
		('1048','Ludwig','Gruber'),
		('1049','Hermann','Eisenschenk'),
		('1050','Sebastian','Tiefnig'),
		('1051','Josef','Mosbacher'),
		('1052','Friedrich','Binder-Maier'),
		('1053','Eleonora','Ruttenstorfer'),
		('1054','Thomas','Pohl'),
		('1055','Gerd','Häuser'),
		('1056','Hans','Brandl'),
		('1057','Severin','Hartinger'),
		('1058','Johann','Wille'),
		('1059','Hannes','Gauster'),
		('1060','Paul','Scherrer'),
		('1061','Alois','Hueber'),
		('1062','Herbert','Graenitz'),
		('1063','Laurenz','Murer'),
		('1064','Anton','Burket'),
		('1065','Lutz','Kulman'),
		('1066','Josef','Frieser'),
		('1067','Edmund','Benya'),
		('1068','Max','Wagner'),
		('1069','Otto','Sattlberger'),
		('1070','Ottilie','Pokorny'),
		('1071','Johann','Moser'),
		('1072','Karl','Swoboda'),
		('1073','Rudolf','Konrad'),
		('1074','Barbara','Riedler'),
		('1075','Yvonne',' Gall'),
		('1076','Heidelore','Hötzendorfer'),
		('1077','Karl','Bauchinger'),
		('1078','Christof','Ledolter'),
		('1079','Alois','Broda'),
		('1080','Rudolf','Kaltenbacher'),
		('1081','Rudolf','Lampel'),
		('1082','Marylin','Nagl'),
		('1083','Franz','Füller'),
		('1084','Franz','Gisel'),
		('1085','Anton','Markowitz'),
		('1086','Helmut','Broukal'),
		('1087','Rudolf','Egghart'),
		('1088','Wolfram','Rabl'),
		('1089','Kurt','Ebner'),
		('1090','Walter','Scheibner'),
		('1091','Norbert','Pohl'),
		('1092','Arthur','Blenk'),
		('1093','Vanessa','Gradischnik'),
		('1094','Alfred','Seeber'),
		('1095','Johann','Zankl'),
		('1096','Gerhard','Rüdiger Starhemberg'),
		('1097','Anton','Dersch'),
		('1098','Hans','Eisl'),
		('1099','Herwig','Adler');

set identity_insert s_student off;

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