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

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

load data local infile 'C:/Users/actio/MyEasyDB/import/MySQL/school_careerdb/g_graduated.csv'
into table g_graduated
fields terminated by '|' enclosed by '"' lines terminated by '\r\n' ignore 1 lines 
(g_s_studentnumber,g_i_institutenumber,@g_date)
set g_date= STR_TO_DATE(@g_date,'%d-%m-%Y');

load data local infile 'C:/Users/actio/MyEasyDB/import/MySQL/school_careerdb/i_institution.csv'
into table i_institution
fields terminated by '|' enclosed by '"' lines terminated by '\r\n' ignore 1 lines 
(i_institutenumber,i_name,@i_monthlytopay)
set i_monthlytopay= CAST(@i_monthlytopay as decimal(7,2));

load data local infile 'C:/Users/actio/MyEasyDB/import/MySQL/school_careerdb/s_student.csv'
into table s_student
fields terminated by '|' enclosed by '"' lines terminated by '\r\n' ignore 1 lines 
(s_studentnumber,s_firstname,s_lastname);

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