
--DROP TABLE project;
--DROP TABLE empPayment;
--DROP TABLE employee;
--DROP TABLE orgUnit;

SET DateStyle ='SQL, DMY';

CREATE TABLE orgUnit (
orgUnitID int CONSTRAINT pkorgUnit PRIMARY KEY,
orgUnitName CHAR(25) NOT NULL);

CREATE TABLE employee (
empID 	int CONSTRAINT pkemployee PRIMARY KEY,
FName 	CHAR(25) NOT NULL,
LName 	CHAR(25) NOT NULL,
orgUnitID 		int 	 NOT NULL CONSTRAINT fkemployeeOJ REFERENCES orgUnit(orgUnitID),
supEmpId     	int 	          CONSTRAINT fkDjelNadDjel REFERENCES employee(empID)
);

CREATE TABLE empPayment (
empID 			int CONSTRAINT fkempPaymentD REFERENCES employee(empID) ,
paymentDate 	DATE,
amount 			numeric (9,2) NOT NULL
, CONSTRAINT pkempPayment PRIMARY KEY (empID, paymentDate));


CREATE TABLE project (
projectID int,
respEmpID int CONSTRAINT fkprojectDjel REFERENCES employee(empID) ,
beginDate DATE,
endDate DATE,
CONSTRAINT pkproject PRIMARY KEY (projectID, respEmpID));

INSERT INTO orgUnit VALUES (1, 'Grocery in Radnička');
INSERT INTO orgUnit VALUES (2, 'Grocery in Vukovarska');
INSERT INTO orgUnit VALUES (3, 'Grocery in Ilica');

INSERT INTO employee VALUES (1, 'Ana'    , 'Par'   , 1, null);
INSERT INTO employee VALUES (2, 'Šime'   , 'Knez'  , 1, 1);
INSERT INTO employee VALUES (3, 'Petra'  , 'Pilić' , 1, 1);
INSERT INTO employee VALUES (4, 'Anita'  , 'Kralj' , 2, 2);
INSERT INTO employee VALUES (5, 'Marko'  , 'Jelić' , 2, 2);
INSERT INTO employee VALUES (6, 'Marija' , 'Burić' , 3, 4);
INSERT INTO employee VALUES (7, 'Slavica', 'Rendić', 3, 6);
INSERT INTO employee VALUES (8, 'Petar'  , 'Milić' , 3, 7);

INSERT INTO empPayment VALUES (1, '01/01/2013', 4700.00);
INSERT INTO empPayment VALUES (1, '01/02/2013', 4700.00);
INSERT INTO empPayment VALUES (1, '01/03/2013', 4700.00);
INSERT INTO empPayment VALUES (1, '01/04/2013', 4700.00);
INSERT INTO empPayment VALUES (1, '01/05/2013', 4750.00);
INSERT INTO empPayment VALUES (1, '01/06/2013', 4750.00);
INSERT INTO empPayment VALUES (1, '01/07/2013', 4900.00);
INSERT INTO empPayment VALUES (1, '01/08/2013', 4900.00);
INSERT INTO empPayment VALUES (1, '01/09/2013', 4900.00);

INSERT INTO empPayment VALUES (2, '01/01/2013', 3770.00);
INSERT INTO empPayment VALUES (2, '01/02/2013', 3770.00);
INSERT INTO empPayment VALUES (2, '01/03/2013', 4000.00);
INSERT INTO empPayment VALUES (2, '01/04/2013', 4770.00);
INSERT INTO empPayment VALUES (2, '01/05/2013', 4770.00);
INSERT INTO empPayment VALUES (2, '01/06/2013', 4770.00);
INSERT INTO empPayment VALUES (2, '01/07/2013', 5000.00);
INSERT INTO empPayment VALUES (2, '01/08/2013', 5000.00);
INSERT INTO empPayment VALUES (2, '01/09/2013', 5000.00);

INSERT INTO empPayment VALUES (3, '01/01/2013', 3500.00);
INSERT INTO empPayment VALUES (3, '01/02/2013', 3500.00);
INSERT INTO empPayment VALUES (3, '01/03/2013', 3500.00);
INSERT INTO empPayment VALUES (3, '01/04/2013', 3500.00);
INSERT INTO empPayment VALUES (3, '01/05/2013', 3500.00);
INSERT INTO empPayment VALUES (3, '01/06/2013', 3500.00);
INSERT INTO empPayment VALUES (3, '01/07/2013', 3800.00);
INSERT INTO empPayment VALUES (3, '01/08/2013', 3800.00);
INSERT INTO empPayment VALUES (3, '01/09/2013', 3800.00);

INSERT INTO empPayment VALUES (4, '01/01/2013', 8700.00);
INSERT INTO empPayment VALUES (4, '01/02/2013', 8700.00);
INSERT INTO empPayment VALUES (4, '01/03/2013', 8700.00);
INSERT INTO empPayment VALUES (4, '01/04/2013', 8700.00);
INSERT INTO empPayment VALUES (4, '01/05/2013', 8700.00);
INSERT INTO empPayment VALUES (4, '01/06/2013', 8700.00);
INSERT INTO empPayment VALUES (4, '01/07/2013', 8900.00);
INSERT INTO empPayment VALUES (4, '01/08/2013', 8900.00);
INSERT INTO empPayment VALUES (4, '01/09/2013', 8900.00);

INSERT INTO empPayment VALUES (5, '01/01/2013', 7500.00);
INSERT INTO empPayment VALUES (5, '01/02/2013', 7500.00);
INSERT INTO empPayment VALUES (5, '01/03/2013', 7500.00);
INSERT INTO empPayment VALUES (5, '01/04/2013', 7500.00);
INSERT INTO empPayment VALUES (5, '01/05/2013', 7500.00);
INSERT INTO empPayment VALUES (5, '01/06/2013', 7500.00);
INSERT INTO empPayment VALUES (5, '01/07/2013', 7500.00);
INSERT INTO empPayment VALUES (5, '01/08/2013', 7500.00);
INSERT INTO empPayment VALUES (5, '01/09/2013', 7500.00);

INSERT INTO empPayment VALUES (6, '01/01/2013', 10700.00);
INSERT INTO empPayment VALUES (6, '01/02/2013', 10700.00);
INSERT INTO empPayment VALUES (6, '01/03/2013', 10700.00);
INSERT INTO empPayment VALUES (6, '01/04/2013', 10700.00);
INSERT INTO empPayment VALUES (6, '01/05/2013', 10700.00);
INSERT INTO empPayment VALUES (6, '01/06/2013', 10700.00);
INSERT INTO empPayment VALUES (6, '01/07/2013', 10900.00);
INSERT INTO empPayment VALUES (6, '01/08/2013', 10900.00);
INSERT INTO empPayment VALUES (6, '01/09/2013', 10900.00);

INSERT INTO empPayment VALUES (7, '01/01/2013', 6070.00);
INSERT INTO empPayment VALUES (7, '01/02/2013', 6070.00);
INSERT INTO empPayment VALUES (7, '01/03/2013', 6070.00);
INSERT INTO empPayment VALUES (7, '01/04/2013', 6070.00);
INSERT INTO empPayment VALUES (7, '01/05/2013', 6070.00);
INSERT INTO empPayment VALUES (7, '01/06/2013', 6070.00);
INSERT INTO empPayment VALUES (7, '01/07/2013', 6000.00);
INSERT INTO empPayment VALUES (7, '01/08/2013', 6000.00);
INSERT INTO empPayment VALUES (7, '01/09/2013', 6000.00);

INSERT INTO empPayment VALUES (8, '01/01/2013', 2900.00);
INSERT INTO empPayment VALUES (8, '01/02/2013', 2900.00);
INSERT INTO empPayment VALUES (8, '01/03/2013', 2900.00);
INSERT INTO empPayment VALUES (8, '01/04/2013', 2900.00);
INSERT INTO empPayment VALUES (8, '01/05/2013', 2900.00);
INSERT INTO empPayment VALUES (8, '01/06/2013', 2900.00);
INSERT INTO empPayment VALUES (8, '01/07/2013', 2900.00);
INSERT INTO empPayment VALUES (8, '01/08/2013', 2900.00);
INSERT INTO empPayment VALUES (8, '01/09/2013', 2900.00);

INSERT INTO project VALUES (345, 1, '01/3/2013', '20/07/2013');
INSERT INTO project VALUES (746, 2, '05/4/2013', '15/08/2013');
