--script to create Jobseeker database
-- Date : 27/4/19

DROP TABLE jsqualification CASCADE CONSTRAINTS;
DROP TABLE institution CASCADE CONSTRAINTS;
DROP TABLE qualification CASCADE CONSTRAINTS;
DROP TABLE workexperience CASCADE CONSTRAINTS;
DROP TABLE employerlist CASCADE CONSTRAINTS;
DROP TABLE jsvacancy CASCADE CONSTRAINTS;
DROP TABLE jobvacancy CASCADE CONSTRAINTS;
DROP TABLE category CASCADE CONSTRAINTS;
DROP TABLE employer CASCADE CONSTRAINTS;
DROP TABLE jobseeker CASCADE CONSTRAINTS;
DROP TABLE town CASCADE CONSTRAINTS;
DROP TABLE district CASCADE CONSTRAINTS;

CREATE TABLE district
(district_id NUMBER(5),
district_name varchar2(20),
CONSTRAINT district_district_id_pk PRIMARY KEY (district_id));

CREATE TABLE town
(town_id NUMBER(5),
town_name VARCHAR2(30),
district_id NUMBER(5),
CONSTRAINT town_town_id_pk PRIMARY KEY(town_id),
CONSTRAINT town_district_id_fk FOREIGN KEY (district_id) REFERENCES district(district_id));

CREATE TABLE jobseeker
(js_id NUMBER(5),
js_fname VARCHAR2(50) NOT NULL,
js_lname VARCHAR2(50) NOT NULL,
js_nid CHAR(14) NOT NULL,
js_street VARCHAR2(100),
town_id NUMBER(5),
js_phone NUMBER(8),
js_email VARCHAR2(30),
js_dob DATE,
jb_gender CHAR(1) NOT NULL,
js_enable NUMBER(1),
js_username VARCHAR2(10) NOT NULL UNIQUE,
js_password VARCHAR2(10) NOT NULL,
js_dateregistered DATE,
CONSTRAINT jobseeker_js_id_pk PRIMARY KEY (js_id),
CONSTRAINT jobseeker_js_nid_uk UNIQUE (js_nid),
CONSTRAINT jobseeker_id_town_fk FOREIGN KEY (town_id) REFERENCES town(town_id));

CREATE TABLE employer
(emp_id NUMBER(5),
emp_name VARCHAR2(100) NOT NULL UNIQUE,
emp_contactperson VARCHAR2(50) NOT NULL,
emp_phone NUMBER(8),
emp_email VARCHAR2(30),
emp_website VARCHAR2(50),
emp_desc VARCHAR2(100),
emp_street VARCHAR2(100),
town_id NUMBER(5),
emp_incorporationno VARCHAR2(10) NOT NULL UNIQUE,
emp_dateincorporation DATE NOT NULL,
emp_brn NUMBER(7) NOT NULL UNIQUE,
emp_enable NUMBER(1),
emp_username VARCHAR2(10) NOT NULL UNIQUE,
emp_password VARCHAR2(10) NOT NULL,
emp_dateregistered DATE,
CONSTRAINT employer_emp_id_pk PRIMARY KEY (emp_id),
CONSTRAINT employer_town_id_fk FOREIGN KEY (town_id) REFERENCES town(town_id));

CREATE TABLE category
(
cat_id NUMBER(5),
cat_name VARCHAR2(100) NOT NULL UNIQUE,
CONSTRAINT category_cat_id_pk PRIMARY KEY (cat_id)
);

CREATE TABLE jobvacancy
(jv_id NUMBER(5),
jv_jobtitle VARCHAR2(30) NOT NULL,
jv_desc VARCHAR2(100),
jv_startdate DATE NOT NULL,
jv_closingdate DATE NOT NULL,
jv_yrexperience NUMBER(2),
jv_requirement VARCHAR2(100),
jv_salary NUMBER(7),
jv_durationtype CHAR(1),
cat_id NUMBER(5),
emp_id NUMBER(5),
jv_dateadvert DATE,
CONSTRAINT jobvacancy_jv_id_pk PRIMARY KEY (jv_id),
CONSTRAINT jobvacancy_cat_id_fk FOREIGN KEY (cat_id) REFERENCES category(cat_id),
CONSTRAINT jobvacancy_emp_id_fk FOREIGN KEY (emp_id) REFERENCES employer(emp_id));


CREATE TABLE jsvacancy
(jsv_id NUMBER(5),
js_id NUMBER(5),
jv_id NUMBER(5),
jsv_dateapplication DATE,
CONSTRAINT jsvacancy_jsv_id_pk PRIMARY KEY(jsv_id),
CONSTRAINT jsvacancy_jsid_jvid_uk UNIQUE (js_id, jv_id),
CONSTRAINT jsvacancy_js_id_fk FOREIGN KEY (js_id) REFERENCES jobseeker(js_id),
CONSTRAINT jsvacancy_jv_id_fk FOREIGN KEY (jv_id) REFERENCES jobvacancy(jv_id)
);

CREATE TABLE employerlist
(el_id NUMBER(5),
el_name VARCHAR2(100) NOT NULL,
CONSTRAINT employerlist_el_id_pk PRIMARY KEY (el_id));

CREATE TABLE workexperience
(we_id NUMBER(5),
we_jobtitle VARCHAR2(100) NOT NULL,
we_startdate DATE,
we_enddate DATE,
js_id NUMBER(5),
el_id NUMBER(5),
CONSTRAINT workexperience_we_id_pk PRIMARY KEY (we_id),
CONSTRAINT workexperience_js_id_fk FOREIGN KEY (js_id) REFERENCES jobseeker(js_id),
CONSTRAINT workexperience_el_id_fk FOREIGN KEY (el_id) REFERENCES employerlist(el_id));

CREATE TABLE qualification
(
qu_id NUMBER(5),
qu_name VARCHAR2(100) NOT NULL,
CONSTRAINT qualification_qu_id_pk PRIMARY KEY(qu_id)
);

CREATE TABLE institution
(ins_id NUMBER(5),
ins_name VARCHAR2(100) NOT NULL,
CONSTRAINT institution_ins_id_pk PRIMARY KEY (ins_id));

CREATE TABLE jsqualification
(jsq_id NUMBER(5),
js_id NUMBER(5),
qu_id NUMBER(5),
ins_id NUMBER(5),
jsq_dategraduated DATE,
CONSTRAINT jsqualification_jsq_id_pk PRIMARY KEY(jsq_id),
CONSTRAINT jsqualification_js_id_fk FOREIGN KEY (js_id) REFERENCES jobseeker(js_id),
CONSTRAINT jsqualification_qu_id_fk FOREIGN KEY (qu_id) REFERENCES qualification(qu_id),
CONSTRAINT jsqualification_ins_id_fk FOREIGN KEY (ins_id) REFERENCES institution(ins_id));

---inserting records into district
INSERT INTO district VALUES (1, 'Black River');
INSERT INTO district VALUES (2, 'Flacq');
INSERT INTO district VALUES (3, 'Grand Port');
INSERT INTO district VALUES (4, 'Moka');
INSERT INTO district VALUES (5, 'Pamplemousses');
INSERT INTO district VALUES (6, 'Plaines-Wilhems');
INSERT INTO district VALUES (7, 'Port-Louis');
INSERT INTO district VALUES (8, 'Rivière du Rempart');
INSERT INTO district VALUES (9, 'Savanne');

---inserting records into town
INSERT INTO town VALUES (1, 'Goodlands', 2);
INSERT INTO town VALUES (2, 'Union Park', 3);
INSERT INTO town VALUES (3, 'Reduit', 4);
INSERT INTO town VALUES (4, 'Volmar', 1);
INSERT INTO town VALUES (5, 'bambous', 1);

---inserting records into jobseeker

INSERT INTO jobseeker VALUES
(1, 'Peerboccus', 'Nasreen','N1504907192829', 'Nissan Road', 1, '57745308', 'pnsreen@live.com', to_date('4/15/1990', 'mm/dd/yyyy'), 'F',  1, 'xtrang', 'whites', to_date('2/20/2019', 'mm/dd/yyyy'));

INSERT INTO jobseeker VALUES
(2, 'Sujeeun', 'Vinay','V2010002457152', 'Kentury  Road', 2, '59758456', 'vinays@hotmail.com', to_date('10/20/2000', 'mm/dd/yyyy'), 'M',  1, 'toomuch', 'finger', to_date('2/20/2019', 'mm/dd/yyyy'));

INSERT INTO jobseeker VALUES
(3, 'Paul', 'Jean','J0312752435648', 'Pink Street', 3, '58542367', 'jeanpaul@gmail.com', to_date('12/3/1975', 'mm/dd/yyyy'), 'M',  1, 'skyblue', 'blue3b', to_date('4/3/2019', 'mm/dd/yyyy'));

INSERT INTO jobseeker VALUES
(4, 'Kim', 'Mary','M2305804578545', 'Railroad Ave 3', 2, '59863528', 'kim123@outlook.com', to_date('5/23/1980', 'mm/dd/yyyy'), 'F',  1, 'global', 'rainbow', to_date('5/4/2018', 'mm/dd/yyyy'));

INSERT INTO jobseeker VALUES
(5, 'Cox', 'Matheo','M1011955874526', 'Lagrement', 5, '57892369', 'coxma45@um.net', to_date('11/10/1995', 'mm/dd/yyyy'), 'M',  1, 'attack', 'plus4u', to_date('5/10/2018', 'mm/dd/yyyy'));


---inserting records into employer
INSERT INTO employer VALUES
(1, 'IBL', 'Mr. Raj Hamgobin', '2064589', 'ibl@hotmail.com', 'http://ibl.com', 'Management', 'Inova Riche Terre Business Park',  2, 'L120364778', to_date('02/25/2000', 'mm/dd/yyyy'), '2458762', 1, 'tracywong', 'wong123',to_date('2/10/2019', 'mm/dd/yyyy') );

INSERT INTO employer VALUES
(2, 'Adecco Mauritius', 'Mrs. Amrita Bunjun', '4069604', 'contact@adecco.mu', 'http://www.adecco.mu', 'Call Centre', '111, 1st Floor',  4, 'A566339994', to_date('06/13/1999', 'mm/dd/yyyy'), '1457458', 1, 'bigbrother', 'brozer21',to_date('2/12/2019', 'mm/dd/yyyy') );

INSERT INTO employer VALUES
(3, 'Exotic Design Ltd', 'Mr. khan Alan Wan', '4671132', 'vacancies@exoticgroup.net', 'http://exotic.com', 'Construction', 'Rue de la Canelle',  3, 'E777123455', to_date('1/16/2009','mm/dd/yyyy'), '7562354', 1, 'elohibro', 'brohello',to_date('4/23/2019', 'mm/dd/yyyy') );

INSERT INTO employer VALUES
(4, 'Linkbynet Indian Ocean', 'Miss. Alice Anderson', '6605247', 'lio-rh@linkbynet.com', 'http://jobs.linkbynet.com', 'IT', 'Block B, La Tour Koenig',  1, 'L456752963', to_date('09/28/2012','mm/dd/yyyy'), '4574568', 1, 'fishhunger', 'hungry1',to_date('6/4/2018', 'mm/dd/yyyy') );

INSERT INTO employer VALUES
(5, 'Infomil (Mauritius) Ltd', 'Mrs. Kiran Emrith', '4648884', 'infomil@live.com', 'http://www.infomil.mu', 'IT', 'Eben Road, CyberTower1',  1, 'I235678963', to_date('03/17/2013','mm/dd/yyyy'), '4512458', 0, 'totototo', 'tomsawyer',to_date('8/10/2018', 'mm/dd/yyyy') );


---inserting records into category
INSERT INTO category VALUES (1, 'ICT / IT / Web');
INSERT INTO category VALUES (2, 'Human Resource');
INSERT INTO category VALUES (3, 'Science');
INSERT INTO category VALUES (4, 'Engineering / Electronics / Mechanics Jobs');
INSERT INTO category VALUES (5, 'Marketing / Sales Jobs');

---inserting records into jobvacancy
INSERT INTO jobvacancy VALUES
(1, 'IT Engineer', 'To manage all our Servers', to_date('03/25/2019', 'mm/dd/yyyy'), to_date('04/24/2019', 'mm/dd/yyyy'), 2, 'Degree in IT', '40000', 'P', 1, 1, to_date('3/3/2019', 'mm/dd/yyyy') );

INSERT INTO jobvacancy VALUES
(2, 'Laboratory Technician', 'To assist the Laboratory Manager in his duties', to_date('4/3/2019', 'mm/dd/yyyy'), to_date('04/26/2019', 'mm/dd/yyyy'), 1, 'Diploma / Degree in Science / Chemistry', '25000', 'P', 3, 2, to_date('2/25/2019', 'mm/dd/yyyy') );

INSERT INTO jobvacancy VALUES
(3, 'Marketing Officer', 'To create marketing campaign for the company', to_date('04/17/2019', 'mm/dd/yyyy'), to_date('05/17/2019', 'mm/dd/yyyy'), 2, 'Diploma in Sales / Marketing', '20000', 'T', 5, 3, to_date('4/15/2019', 'mm/dd/yyyy') );

INSERT INTO jobvacancy VALUES
(4, 'HR Manager', 'To recruit staff, To ensure staff welfare', to_date('04/16/2019', 'mm/dd/yyyy'), to_date('04/23/2019', 'mm/dd/yyyy'), 5, 'Master Degree in HR', '60000', 'P', 2, 1, to_date('4/10/2019', 'mm/dd/yyyy') );

INSERT INTO jobvacancy VALUES
(5, 'Biology Teacher', 'To teach from Grade 10 - Grade 15', to_date('04/15/2019', 'mm/dd/yyyy'), to_date('05/15/2019', 'mm/dd/yyyy'), 0, 'Degree in Biology', '23000', 'T', 3, 4, to_date('4/10/2019', 'mm/dd/yyyy') );

---inserting records into jsvacancy
INSERT INTO jsvacancy VALUES (1, 1, 2, to_date('3/29/2019', 'mm/dd/yyyy'));
INSERT INTO jsvacancy VALUES (2, 3, 1, to_date('4/18/2019', 'mm/dd/yyyy'));
INSERT INTO jsvacancy VALUES (3, 5, 2, to_date('3/30/2019', 'mm/dd/yyyy'));
INSERT INTO jsvacancy VALUES (4, 1, 5, to_date('4/20/2019', 'mm/dd/yyyy'));

---inserting records into employerlist
INSERT INTO employerlist VALUES (1, 'Global Insurance Ltd');
INSERT INTO employerlist VALUES (2, 'Marketing Board');
INSERT INTO employerlist VALUES (3, 'National Empowerment Foundation');
INSERT INTO employerlist VALUES (4, 'Lolipop Kindergarten ');
INSERT INTO employerlist VALUES (5, 'Airport of Mauritius Ltd');

---inserting records into workexperience
INSERT INTO workexperience VALUES
(1, 'Health and Safety Specialist', to_date('03/25/2019', 'mm/dd/yyyy'), to_date('04/24/2019', 'mm/dd/yyyy'), 1, 1);

INSERT INTO workexperience VALUES
(2, 'Supervisor - Mobile Phone Repairs', to_date('4/16/2010', 'mm/dd/yyyy'), to_date('04/23/2017', 'mm/dd/yyyy'), 2, 1);

INSERT INTO workexperience VALUES
(3, 'Senior Software Test Engineer', to_date('4/30/2017', 'mm/dd/yyyy'), to_date('04/26/2019', 'mm/dd/yyyy'), 2, 2);

INSERT INTO workexperience VALUES
(4, 'Human Resource Officer', to_date('4/17/2018', 'mm/dd/yyyy'), to_date('05/17/2019', 'mm/dd/yyyy'), 4, 1);

INSERT INTO workexperience VALUES
(5, 'Sales Executive', to_date('4/30/2018', 'mm/dd/yyyy'), to_date('4/15/2019', 'mm/dd/yyyy'), 5, 5);

---inserting records into qualification
INSERT INTO qualification VALUES (1, 'Degree in Computer Science');
INSERT INTO qualification VALUES (2, 'Degree in Biology');
INSERT INTO qualification VALUES (3, 'Master Degree in Human Resource');
INSERT INTO qualification VALUES (4, 'Diploma in Human Anatomy');
INSERT INTO qualification VALUES (5, 'Post-Graduate Diploma in Robot Technology');

---inserting records into institution
INSERT INTO institution VALUES (1, 'University of Maurtius');
INSERT INTO institution VALUES (2, 'University of Tecnology');
INSERT INTO institution VALUES (3, 'Open University of Mauritius');
INSERT INTO institution VALUES (4, 'Université des Mascareignes');
INSERT INTO institution VALUES (5, 'Middlesex University in Mauritius');

---inserting records into jsqualification
INSERT INTO jsqualification VALUES (1, 1, 2, 1, to_date('1/30/2015', 'mm/dd/yyyy'));
INSERT INTO jsqualification VALUES (2, 1, 5, 2, to_date('4/25/2018', 'mm/dd/yyyy'));
INSERT INTO jsqualification VALUES (3, 3, 1, 2, to_date('9/15/2000', 'mm/dd/yyyy'));
INSERT INTO jsqualification VALUES (4, 4, 4, 2, to_date('3/3/2014' , 'mm/dd/yyyy'));
INSERT INTO jsqualification VALUES (5, 5, 2, 4, to_date('11/2/2018', 'mm/dd/yyyy'));

COMMIT;