create table schools 
( school_id integer primary key 
, school_name varchar2(200) not null 
, constraint school_name_uk unique (school_name) 
)

create table campuses 
( campus_id integer primary key 
, school_id integer not null 
, campus_name varchar2(200) not null 
, constraint campus_name_uk unique (school_id, campus_name) 
)

create table budgets 
( budget_id integer primary key 
, school_id integer not null /*VPD policy*/ 
, cal_year varchar2(4) not null 
, budget_name varchar2(200) not null 
, status_code varchar2(100) default 'DRAFT' not null 
, approved_dt date 
, constraint budget_name_uk unique (school_id, cal_year, budget_name) 
)

create table departments 
( department_id integer primary key 
, budget_id integer not null 
, school_id integer not null /*VPD policy*/ 
, department_name varchar2(200) not null 
, campus_id integer not null 
, status_code varchar2(100) default 'DRAFT' not null 
, submitted_dt date 
, approved_dt date 
, constraint department_name_uk unique (school_id, budget_id, department_name) 
)

create table subjects 
( subject_id integer primary key 
, department_id integer not null 
, school_id integer not null /*VPD policy*/ 
, subject_name varchar2(200) not null 
, constraint subject_name_uk unique (school_id, department_id, subject_name) 
)

create table year_levels 
( year_level_id integer primary key 
, budget_id integer not null 
, school_id integer not null /*VPD policy*/ 
, year_level_name varchar2(100) not null 
, constraint year_level_uk unique (school_id, budget_id, year_level_name) 
)

create table cohorts 
( cohort_id integer primary key 
, budget_id integer not null 
, school_id integer not null /*VPD policy*/ 
, subject_id integer not null 
, year_level_id integer not null 
, est_student_count number 
, constraint cohort_uk unique (subject_id, year_level_id) 
)

create table budget_lines 
( budget_line_id integer primary key 
, budget_id integer not null 
, school_id integer not null /*VPD policy*/ 
, department_id integer not null 
, campus_id integer /*a budget might be non-campus-specific*/ 
, subject_id integer /*a budget might not be relevant to a subject*/ 
, year_level_id integer /*a budget might be for all year levels*/ 
, line_type_code varchar2(100) not null 
, item_name varchar2(200) not null 
, description varchar2(4000) 
, cost_centre_code varchar2(100) 
, project_code varchar2(100) 
, account_code varchar2(100) 
, fund_code varchar2(100) 
, activity_code varchar2(100) 
, total_amt number 
, budget_amt number 
, cost_ps_amt number 
, contributions_ps_amt number 
, charges_ps_amt number 
, other_columns varchar2(4000) 
, notes varchar2(4000) 
)

alter table budgets add constraint budgets_school_fk foreign key (school_id) references schools (school_id)

alter table departments add constraint departments_budget_fk foreign key (budget_id) references budgets (budget_id)

alter table departments add constraint departments_school_fk foreign key (school_id) references schools (school_id)

alter table departments add constraint departments_campus_fk foreign key (campus_id) references campuses (campus_id)

alter table subjects add constraint subjects_dept_fk foreign key (department_id) references departments (department_id)

alter table subjects add constraint subjects_school_fk foreign key (school_id) references schools (school_id)

alter table year_levels add constraint year_levels_budget_fk foreign key (budget_id) references budgets (budget_id)

alter table year_levels add constraint year_levels_school_fk foreign key (school_id) references schools (school_id)

alter table cohorts add constraint cohorts_budget_fk foreign key (budget_id) references budgets (budget_id)

alter table cohorts add constraint cohorts_school_fk foreign key (school_id) references schools (school_id)

alter table cohorts add constraint cohorts_subject_fk foreign key (subject_id) references subjects (subject_id)

alter table cohorts add constraint cohorts_year_level_fk foreign key (year_level_id) references year_levels (year_level_id)

alter table budget_lines add constraint budget_lines_school_fk foreign key (school_id) references schools (school_id)

alter table budget_lines add constraint budget_lines_budget_fk foreign key (budget_id) references budgets (budget_id)

alter table budget_lines add constraint budget_lines_dept_fk foreign key (department_id) references departments (department_id)

alter table budget_lines add constraint budget_lines_campus_fk foreign key (campus_id) references campuses (campus_id)

alter table budget_lines add constraint budget_lines_subject_fk foreign key (subject_id) references subjects (subject_id)

alter table budget_lines add constraint budget_lines_year_level_fk foreign key (year_level_id) references year_levels (year_level_id)

insert into schools (school_id, school_name) values (1000, 'Gooseberry Primary School')

insert into schools (school_id, school_name) values (2000, 'Harrington High School')

insert into campuses (campus_id, school_id, campus_name) values (1010,1000,'Main Campus')

insert into campuses (campus_id, school_id, campus_name) values (2010,2000,'High Road Campus')

insert into campuses (campus_id, school_id, campus_name) values (2020,2000,'Low Road Campus')

insert into budgets (budget_id, school_id, cal_year, budget_name) values (1001, 1000, '2018', 'Budget')

insert into budgets (budget_id, school_id, cal_year, budget_name) values (2001, 2000, '2018', 'Budget')

insert into departments (department_id,budget_id,school_id,campus_id,department_name) values (10101,1001,1000,1010,'Maths')

insert into departments (department_id,budget_id,school_id,campus_id,department_name) values (10102,1001,1000,1010,'Language')

insert into departments (department_id,budget_id,school_id,campus_id,department_name) values (20101,2001,2000,2010,'Maths')

insert into departments (department_id,budget_id,school_id,campus_id,department_name) values (20102,2001,2000,2010,'Science')

insert into departments (department_id,budget_id,school_id,campus_id,department_name) values (20201,2001,2000,2020,'Music')

insert into subjects (subject_id,department_id,school_id,subject_name) values (1,10101,1000,'Arithmetic')

insert into subjects (subject_id,department_id,school_id,subject_name) values (2,10101,1000,'Geometry')

insert into subjects (subject_id,department_id,school_id,subject_name) values (3,10102,1000,'French')

insert into subjects (subject_id,department_id,school_id,subject_name) values (4,10102,1000,'Japanese')

insert into year_levels (year_level_id,budget_id,school_id,year_level_name) values (1,1001,1000,'Preschool')

insert into year_levels (year_level_id,budget_id,school_id,year_level_name) values (2,1001,1000,'Years 1-3')

insert into year_levels (year_level_id,budget_id,school_id,year_level_name) values (3,1001,1000,'Years 4-6')

insert into year_levels (year_level_id,budget_id,school_id,year_level_name) values (4,2001,2000,'Year 7')

insert into year_levels (year_level_id,budget_id,school_id,year_level_name) values (5,2001,2000,'Year 8')

insert into year_levels (year_level_id,budget_id,school_id,year_level_name) values (6,2001,2000,'Year 9')

insert into year_levels (year_level_id,budget_id,school_id,year_level_name) values (7,2001,2000,'Year 10')

insert into year_levels (year_level_id,budget_id,school_id,year_level_name) values (8,2001,2000,'Year 11')

insert into year_levels (year_level_id,budget_id,school_id,year_level_name) values (9,2001,2000,'Year 12')

insert into cohorts (cohort_id,budget_id,school_id,subject_id,year_level_id,est_student_count) values (1,1001,1000,1,1,30)

insert into cohorts (cohort_id,budget_id,school_id,subject_id,year_level_id,est_student_count) values (2,1001,1000,1,2,22)

insert into cohorts (cohort_id,budget_id,school_id,subject_id,year_level_id,est_student_count) values (3,1001,1000,1,3,25)

insert into cohorts (cohort_id,budget_id,school_id,subject_id,year_level_id,est_student_count) values (4,1001,1000,2,1,17)

insert into cohorts (cohort_id,budget_id,school_id,subject_id,year_level_id,est_student_count) values (5,1001,1000,2,2,19)

insert into cohorts (cohort_id,budget_id,school_id,subject_id,year_level_id,est_student_count) values (6,1001,1000,2,3,24)

insert into budget_lines (budget_line_id,budget_id,school_id,department_id,campus_id,subject_id,line_type_code,item_name,budget_amt) 
values (1,1001,1000,10101,1010,1,'EXPENSE','Photocopying',500.0)

insert into budget_lines (budget_line_id,budget_id,school_id,department_id,campus_id,subject_id,line_type_code,item_name,cost_ps_amt) 
values (2,1001,1000,10101,1010,1,'EXPENSE','Materials',5.0)