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

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

bulk insert g_graduated
from 'C:/Users/actio/MyEasyDB/import/MSSQL/school_careerdb/g_graduated.csv'
with (format = 'CSV', 
fieldquote = '"',
fieldterminator = '|',
rowterminator = '\n', 
firstrow=2);

bulk insert i_institution
from 'C:/Users/actio/MyEasyDB/import/MSSQL/school_careerdb/i_institution.csv'
with (format = 'CSV', 
fieldquote = '"',
fieldterminator = '|',
rowterminator = '\n', 
firstrow=2);

bulk insert s_student
from 'C:/Users/actio/MyEasyDB/import/MSSQL/school_careerdb/s_student.csv'
with (format = 'CSV', 
fieldquote = '"',
fieldterminator = '|',
rowterminator = '\n', 
firstrow=2);

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