-- 
--  1. DROP all tables
-- 

--  1.1 Relationship tables

select 'Dropping tables JOINSIN, ENROLLED, TEACHESD' as " " from dual;

drop table JOINSIN;
drop table ENROLLED;
drop table TEACHES;

--  1.2 Entity tables

select 'Dropping tables STUDENTS, COURSES, ACTIVITIES, TEACHERS' as " " from dual;

drop table STUDENTS;
drop table COURSES;
drop table ACTIVITIES;
drop table TEACHERS;

-- 
--  2. CREATE database tables
-- 

--  2.1 Entity tables

select 'Creating table STUDENTS' as " " from dual;

create table STUDENTS (
    SID     int,                     --  ID
    SNAME   varchar(20) not null,    --  last name
    SFNAME  varchar(20) not null,    --  first name
    BIRTHD  date not null,           --  birth date
    WEIGHT  decimal(3,1),            --  weight
    SYEAR   int not null,            --  year, e.g. 4 (= M1) 
    constraint STUDENTS_PK primary key (SID)
);

select 'Creating table COURSES' as " " from dual;

create table COURSES (
    CID     int,                     --  ID
    CNAME   varchar(20) not null,    --  name, e.g. 'Database'
    LENGTH  int,                     --  duration in hours, e.g. 24 (h)
    CYEAR   int,                     --  year, e.g. 4 (= M1) 
    constraint COURSES_PK primary key (CID)
);

select 'Creating table ACTIVITIES' as " " from dual;

create table ACTIVITIES (
    ANAME   varchar(20),             --  name, e.g. 'Theater'
    ALEVEL  int,                     --  level, e.g. 1 (beginners), 2 (advanced), etc.
    TEAM    varchar(20),             --  team, for team activities
    constraint ACTIVITIES_PK primary key (ANAME, ALEVEL)
);

select 'Creating table TEACHERS' as " " from dual;

create table TEACHERS (
    TID     int,                     --  ID
    TNAME   varchar(20) not null,    --  name
    SPEC    varchar(20),             --  specialty, e.g 'Math' 
    HIRED   date not null,           --  hire date
    RAISED  date,                    --  (latter) raise date
    HIRESAL decimal(6,2) not null,   --  hire salary
    CURRSAL decimal(6,2) not null,   --  current salary
    constraint TEACHERS_PK primary key (TID)
);

--  2.2 Relationship tables

--  2.2.a JOINSIN: describes the student-activity relationships

select 'Creating table JOINSIN' as " " from dual;

create table JOINSIN (		
    SID     int,                     --  student ID
    ANAME   varchar(20),             --  activity name
    ALEVEL  int,                     --  activity level
    constraint JOINSIN_PK primary key (SID, ANAME, ALEVEL),
    constraint JOINSIN_FK_SID foreign key (SID) references STUDENTS(SID),
    constraint JOINSIN_FK_AID foreign key (ANAME, ALEVEL) references ACTIVITIES(ANAME, ALEVEL)
);

--  2.2.b ENROLLED: describes the student-course relationships

select 'Creating table ENROLLED' as " " from dual;

create table ENROLLED (
    SID     int,                     --  student ID
    CID     int,                     --  course ID
    MARK    decimal(3,1),            --  mark, e.g. 12.5 (/20)
    constraint ENROLLED_PK primary key (SID, CID),
    constraint ENROLLED_FK_SID foreign key (SID) references STUDENTS(SID),
    constraint ENROLLED_FK_CID foreign key (CID) references COURSES(CID)
);

--  2.2.c TEACHES: describes the teacher-course relationships

select 'Creating table TEACHES' as " " from dual;

create table TEACHES (
    CID     int,                     --  course ID
    TID     int,                     --  teacher ID
    constraint TEACHES_PK primary key (CID, TID),
    constraint TEACHES_FK_CID foreign key (CID) references COURSES(CID),
    constraint TEACHES_FK_TID foreign key (TID) references TEACHERS(TID)
);


-- 
--  3. POPULATE database tables
-- 

--  3.1 STUDENTS table

select 'Populating table STUDENTS' as " " from dual;

insert into STUDENTS
values (1, 'Brisefer', 'Benoit', date '1994-12-10', 35, 1);

insert into STUDENTS
values (2, 'Parent', 'Olivier', date '1994-04-10', 42, 1);

insert into STUDENTS
values (3, 'Jourdan', 'Gil', date '1990-06-28', 72, 2);

insert into STUDENTS
values (4, 'Spring', 'Jerry', date '1990-02-16', 78, 2);

insert into STUDENTS
values (5, 'Tsuno', 'Yoko', date '1993-10-29', 45, 1);

insert into STUDENTS
values (6, 'Lebut', 'Marc', date '1990-04-29', 75,2);

insert into STUDENTS
values (7, 'Lagaffe', 'Gaston', date '1991-04-08', 61,1);

insert into STUDENTS
values (8, 'Dubois', 'Robin', date '1992-04-20', 60, 2);

insert into STUDENTS
values (9, 'Walther', 'Natacha', date '1993-09-07', 59,1);

insert into STUDENTS
values (10, 'Danny', 'Buck', date '1989-02-15', 82, 2);


--  3.2 COURSES table

select 'Populating table COURSES' as " " from dual;

insert into COURSES
values (1, 'Reseau', 15, 1);

insert into COURSES
values (2, 'SGBD', 30, 1);

insert into COURSES
values (3, 'Programmation C', 15, 1);

insert into COURSES
values (4, 'Systeme', 30, 2);

insert into COURSES
values (5, 'Analyse', 60, 2);


--  3.3 ACTIVITIES table

select 'Populating table ACTIVITIES' as " " from dual;

insert into ACTIVITIES (ALEVEL, ANAME, TEAM)
values (1, 'Mini foot', 'Amc Indus');

insert into ACTIVITIES (ALEVEL, ANAME, TEAM)
values (1, 'Surf', 'Les planchistes');

insert into ACTIVITIES (ALEVEL, ANAME, TEAM)
values (2, 'Tennis', 'Ace Club');

insert into ACTIVITIES (ALEVEL, ANAME, TEAM)
values (3, 'Tennis', 'Ace Club');

insert into ACTIVITIES (ALEVEL, ANAME, TEAM)
values (1, 'Volley ball', 'Avs80');

insert into ACTIVITIES (ALEVEL, ANAME, TEAM)
values (2, 'Mini foot', 'Les as du ballon');

insert into ACTIVITIES (ALEVEL, ANAME, TEAM)
values (2, 'Volley ball', 'Smash');


--  3.4 TEACHERS table

select 'Populating table TEACHERS' as " " from dual;

insert into TEACHERS
values (1, 'Bottle', 'Poesie', date '1970-01-01', date '1988-10-01', 2000.0, 2600.0);

insert into TEACHERS
values (2, 'Bolenov', 'Reseau', date '1968-11-15', date '1998-10-01', 1900, 2468);

insert into TEACHERS
values (3, 'Tonilaclasse', 'POO', date '1979-10-01', date '1989-01-01', 1900, 2360);

insert into TEACHERS
values (4, 'PasteCIDv', 'SQL', date '1975-10-01', null, 2500, 2500);

insert into TEACHERS
values (5, 'Selector', 'SQL', date '1982-10-15', date '1988-10-01', 1900, 1900);

insert into TEACHERS
values (6, 'Vilplusplus', 'POO', date '1990-04-25', date '1994-06-05', 1900, 2200);

insert into TEACHERS
values (7, 'Francesca', null, date '1975-10-01', date '1998-01-11', 2000, 3200);

insert into TEACHERS
values (8, 'Pucette', 'SQL', date '1988-12-06', date '1996-02-29', 2000, 2500);


--  3.5 JOINSIN table

select 'Populating table JOINSIN' as " " from dual;

insert into JOINSIN (SID, ALEVEL, ANAME)
values (1, 1, 'Mini foot');

insert into JOINSIN (SID, ALEVEL, ANAME)
values (1, 1, 'Surf');

insert into JOINSIN (SID, ALEVEL, ANAME)
values (1, 1, 'Volley ball');

insert into JOINSIN (SID, ALEVEL, ANAME)
values (1, 2, 'Tennis');

insert into JOINSIN (SID, ALEVEL, ANAME)
values (2, 1, 'Mini foot');

insert into JOINSIN (SID, ALEVEL, ANAME)
values (2, 2, 'Tennis');

insert into JOINSIN (SID, ALEVEL, ANAME)
values (3, 2, 'Mini foot');

insert into JOINSIN (SID, ALEVEL, ANAME)
values (3, 2, 'Tennis');

insert into JOINSIN (SID, ALEVEL, ANAME)
values (3, 2, 'Volley ball');

insert into JOINSIN (SID, ALEVEL, ANAME)
values (4, 1, 'Surf');

insert into JOINSIN (SID, ALEVEL, ANAME)
values (4, 2, 'Mini foot');

insert into JOINSIN (SID, ALEVEL, ANAME)
values (5, 1, 'Mini foot');

insert into JOINSIN (SID, ALEVEL, ANAME)
values (5, 1, 'Surf');

insert into JOINSIN (SID, ALEVEL, ANAME)
values (5, 1, 'Volley ball');

insert into JOINSIN (SID, ALEVEL, ANAME)
values (8, 1, 'Mini foot');

insert into JOINSIN (SID, ALEVEL, ANAME)
values (8, 1, 'Volley ball');

insert into JOINSIN (SID, ALEVEL, ANAME)
values (8, 2, 'Volley ball');

insert into JOINSIN (SID, ALEVEL, ANAME)
values (9, 1, 'Mini foot');

insert into JOINSIN (SID, ALEVEL, ANAME)
values (9, 2, 'Volley ball');

insert into JOINSIN (SID, ALEVEL, ANAME)
values (10, 1, 'Mini foot');

insert into JOINSIN (SID, ALEVEL, ANAME)
values (10, 2, 'Tennis');

insert into JOINSIN (SID, ALEVEL, ANAME)
values (10, 2, 'Volley ball');


--  3.6 ENROLLED table

select 'Populating table ENROLLED' as " " from dual;

insert into ENROLLED
values (1, 1, null);

insert into ENROLLED
values (1, 2, 10.5);

insert into ENROLLED
values (1, 4, 8);

insert into ENROLLED
values (1, 5, 20);

insert into ENROLLED
values (2, 1, 13.5);

insert into ENROLLED
values (2, 2, 12);

insert into ENROLLED
values (2, 4, null);

insert into ENROLLED
values (2, 5, 1.5);

insert into ENROLLED
values (3, 1, 14);

insert into ENROLLED
values (3, 2, 15);

insert into ENROLLED
values (3, 4, 16);

insert into ENROLLED
values (3, 5, 20);

insert into ENROLLED
values (4, 1, 16.5);

insert into ENROLLED
values (4, 2, 14);

insert into ENROLLED
values (4, 4, 11);

insert into ENROLLED
values (4, 5, 8);

insert into ENROLLED
values (5, 1, 5);

insert into ENROLLED
values (5, 2, 6.5);

insert into ENROLLED
values (5, 4, 13);

insert into ENROLLED
values (5, 5, 13);

insert into ENROLLED
values (6, 1, 15);

insert into ENROLLED
values (6, 2, 3.5);

insert into ENROLLED
values (6, 4, 16);

insert into ENROLLED
values (6, 5, 5);

insert into ENROLLED
values (7, 1, 2.5);

insert into ENROLLED
values (7, 2, 18);

insert into ENROLLED
values (7, 4, 11);

insert into ENROLLED
values (7, 5, 10);

insert into ENROLLED
values (8, 1, 16);

insert into ENROLLED
values (8, 2, 0);

insert into ENROLLED
values (8, 4, 6);

insert into ENROLLED
values (8, 5, 11.5);

insert into ENROLLED
values (9, 1, 20);

insert into ENROLLED
values (9, 2, 20);

insert into ENROLLED
values (9, 4, 14);

insert into ENROLLED
values (9, 5, 9.5);

insert into ENROLLED
values (10, 1, 3);

insert into ENROLLED
values (10, 2, 12.5);

insert into ENROLLED
values (10, 4, 0);

insert into ENROLLED
values (10, 5, 16);


--  TEACHES table

select 'Populating table TEACHES' as " " from dual;

insert into TEACHES (TID, CID)
values (1, 1);

insert into TEACHES (TID, CID)
values (1, 4);

insert into TEACHES (TID, CID)
values (2, 1);

insert into TEACHES (TID, CID)
values (3, 2);

insert into TEACHES (TID, CID)
values (3, 4);

insert into TEACHES (TID, CID)
values (3, 5);

insert into TEACHES (TID, CID)
values (4, 2);

insert into TEACHES (TID, CID)
values (7, 4);

insert into TEACHES (TID, CID)
values (8, 1);

insert into TEACHES (TID, CID)
values (8, 2);

insert into TEACHES (TID, CID)
values (8, 3);

insert into TEACHES (TID, CID)
values (8, 4);

insert into TEACHES (TID, CID)
values (8, 5);

commit;

-- 
--  4. CONFIGURE environment (DBMS-specific)
-- 

select 'Configuring environment.' as " " from dual;

-- Oracle
-- prevents spurious page breaks
set pages 10000
-- prevents spurious line breaks
set linesize 250
-- displays null as NULL instead of void
set null NULL
-- useless: adhere to SQL standard 
-- alter session set nls_date_format = 'DD-MM-YYYY';

select 'Done.' as " " from dual;

