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

-- load from csv -#################################################

copy g_graduated
 from  'C:/Users/actio/MyEasyDB/import/PostgreSQL/school_careerdb/g_graduated.csv'
 with (format csv, header true, delimiter '|', quote '"');

copy i_institution
 from  'C:/Users/actio/MyEasyDB/import/PostgreSQL/school_careerdb/i_institution.csv'
 with (format csv, header true, delimiter '|', quote '"');

copy s_student
 from  'C:/Users/actio/MyEasyDB/import/PostgreSQL/school_careerdb/s_student.csv'
 with (format csv, header true, delimiter '|', quote '"');

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