CREATE DATABASE project;
USE project;

CREATE TABLE person (
	id_person SMALLINT PRIMARY KEY,
	firstname CHAR(15) NOT NULL,
	lastname CHAR(15) NOT NULL,
	city VARCHAR(20),
	birth_year SMALLINT,
	salary DOUBLE
) ENGINE=InnoDB;

CREATE TABLE project (
	id_project SMALLINT PRIMARY KEY,
	pname VARCHAR(20) NOT NULL,
	place VARCHAR(20)
) ENGINE=InnoDB;

CREATE TABLE hour (
	id_project SMALLINT NOT NULL,
	id_person SMALLINT NOT NULL,
	work_hour INTEGER,
	PRIMARY KEY(id_project, id_person),
	FOREIGN KEY (id_project)
		REFERENCES project(id_project)
		ON DELETE RESTRICT ON UPDATE CASCADE,
	FOREIGN KEY (id_person)
		REFERENCES person (id_person)
		ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;

	INSERT INTO person VALUES (201,'Jim','Morrison','TURKU',1985,44);
	INSERT INTO person VALUES (203,'Liisa','River','HELSINKI',1991,39);
	INSERT INTO person VALUES (206,'Matt','Daniels','TAMPERE',1991,34);
	INSERT INTO person VALUES (202,'Jim','Smith','TURKU',1988,39);
	INSERT INTO person VALUES (205,'Lisa','Simpson','HELSINKI',1985,36);
	INSERT INTO person VALUES (204,'Ann','Jones','TURKU',1979,44);


	INSERT INTO project VALUES(101,'Bookkeeping','TURKU');
	INSERT INTO project VALUES(102,'Billing','HELSINKI');
	INSERT INTO project VALUES(103,'Store','HELSINKI');
	INSERT INTO project VALUES(104,'Selling','TURKU');
	INSERT INTO project VALUES(105,'Customers','KUOPIO');
	INSERT INTO project VALUES(106,'Statistics',NULL);


	INSERT INTO hour VALUES(101,201,300);
	INSERT INTO hour VALUES(101,202,200);
	INSERT INTO hour VALUES(101,203,200);
	INSERT INTO hour VALUES(101,204,100);
	INSERT INTO hour VALUES(101,205,100);
	INSERT INTO hour VALUES(101,206,400);
	INSERT INTO hour VALUES(102,201,300);
	INSERT INTO hour VALUES(102,203,400);
	INSERT INTO hour VALUES(103,203,200);
	INSERT INTO hour VALUES(104,202,300);
	INSERT INTO hour VALUES(104,203,200);
	INSERT INTO hour VALUES(104,205,400);