DROP TABLE Osan_toimitus
;
DROP TABLE Toimittaa
;
DROP TABLE Tekee
;
DROP TABLE Projekti
;
DROP TABLE Koostuu
;
DROP TABLE Osa
;
DROP TABLE Toimittaja
;
DROP TABLE Lapsi
;
DROP TABLE Puhelinnumero
;
DROP TABLE Tyontekija
;
DROP TABLE Osasto
;


CREATE TABLE Osasto (
OsastoID	INTEGER NOT NULL,
OsastoNimi	VARCHAR(32) NOT NULL,
CONSTRAINT Osasto_PK
	PRIMARY KEY (OsastoID)
);


CREATE TABLE Tyontekija (
TyontekijaID	INTEGER NOT NULL,
Etunimi		VARCHAR(32) NOT NULL,
Sukunimi	VARCHAR(64) NOT NULL,
Palkka		DOUBLE PRECISION NOT NULL DEFAULT 10000,
Syntymaaika	DATE NOT NULL DEFAULT '1970-1-23',
Osasto		INTEGER NOT NULL DEFAULT 1,
CONSTRAINT Tyontekija_Palkka
	CHECK ( palkka > 0 ),
CONSTRAINT Tyontekija_Syntymaaika
	CHECK (syntymaaika > '1900-1-1'),
CONSTRAINT Tyontekija_PK
	PRIMARY KEY (TyontekijaID),
CONSTRAINT   T_FK_Osasto
	FOREIGN KEY (Osasto)
	REFERENCES Osasto (OsastoID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE
);



CREATE TABLE Puhelinnumero (
Tyontekija	INTEGER NOT NULL,
Puhelinnumero	VARCHAR(32) NOT NULL,
CONSTRAINT Puhelinnumero_PK
	PRIMARY KEY (Tyontekija,Puhelinnumero),
CONSTRAINT   Puh_Puhnro
	UNIQUE ( Puhelinnumero ),
CONSTRAINT   Puh_FK_Tyontekija
	FOREIGN KEY (Tyontekija)
	REFERENCES Tyontekija (TyontekijaID)
		ON DELETE CASCADE
		ON UPDATE CASCADE
);


CREATE TABLE Lapsi (
Huoltaja	INTEGER NOT NULL,
Syntymaaika	DATE NOT NULL,
CONSTRAINT Lapsi_PK
	PRIMARY KEY (Huoltaja, Syntymaaika),
CONSTRAINT   Lapsi_FK_Huoltaja
	FOREIGN KEY (Huoltaja)
	REFERENCES Tyontekija (TyontekijaID)
		ON DELETE CASCADE
		ON UPDATE CASCADE
);


CREATE TABLE Toimittaja (
ToimittajaID	INTEGER NOT NULL,
ToimittajaNimi	VARCHAR(64) NOT NULL,
CONSTRAINT Toimittaja_PK
	PRIMARY KEY (ToimittajaID)
);


CREATE TABLE Osa (
OsaID	INTEGER NOT NULL,
OsaNimi	VARCHAR(64) NOT NULL,
CONSTRAINT Osa_PK
	PRIMARY KEY (OsaID)
);

CREATE TABLE Koostuu (
K_OsaID		INTEGER NOT NULL,
OsaID		INTEGER NOT NULL,
Lukumaara	SMALLINT NOT NULL,
CONSTRAINT Koostuu_PK
	PRIMARY KEY (K_OsaID, OsaID),
CONSTRAINT   Koostuu_FK_1
	FOREIGN KEY (K_OsaID)
	REFERENCES Osa (OsaID)
		ON DELETE CASCADE
		ON UPDATE CASCADE,
CONSTRAINT   Koostuu_FK_2
	FOREIGN KEY (OsaID)
	REFERENCES Osa (OsaID)
		ON DELETE CASCADE
		ON UPDATE CASCADE
);


CREATE TABLE Projekti (
ProjektiID		INTEGER NOT NULL,
ProjektiNimi		VARCHAR(64) NOT NULL,
Projektipaallikko	INTEGER NOT NULL,
CONSTRAINT Projekti_PK
	PRIMARY KEY (ProjektiID),
CONSTRAINT   Projekti_FK_P
	FOREIGN KEY (Projektipaallikko)
	REFERENCES Tyontekija (TyontekijaID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE
);


CREATE TABLE Tekee (
TyontekijaID	INTEGER NOT NULL,
ProjektiID	INTEGER NOT NULL,
CONSTRAINT Tekee_PK
	PRIMARY KEY (TyontekijaID, ProjektiID),
CONSTRAINT   Tekee_FK_1
	FOREIGN KEY (TyontekijaID)
	REFERENCES Tyontekija (TyontekijaID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE,
CONSTRAINT   Tekee_FK_2
	FOREIGN KEY (ProjektiID)
	REFERENCES Projekti (ProjektiID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE
);

CREATE TABLE Toimittaa (
ToimittajaID	INTEGER NOT NULL,
OsaID		INTEGER NOT NULL,
CONSTRAINT Toimittaa_PK
	PRIMARY KEY (ToimittajaID, OsaID),
CONSTRAINT   Toimittaa_FK_1
	FOREIGN KEY (ToimittajaID)
	REFERENCES Toimittaja (ToimittajaID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE,
CONSTRAINT   Tekee_FK_2
	FOREIGN KEY (OsaID)
	REFERENCES Osa (OsaID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE
);

CREATE TABLE Osan_toimitus (
ProjektiID	INTEGER NOT NULL,
ToimittajaID	INTEGER NOT NULL,
OsaID		INTEGER NOT NULL,
Lukumaara	SMALLINT NOT NULL,
CONSTRAINT OsaT_PK
	PRIMARY KEY (ProjektiID, ToimittajaID, OsaID),
CONSTRAINT   OSaT_FK_1
	FOREIGN KEY (ToimittajaID)
	REFERENCES Toimittaja (ToimittajaID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE,
CONSTRAINT   OsaT_FK_2
	FOREIGN KEY (OsaID)
	REFERENCES Osa (OsaID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE,
CONSTRAINT   OsaT_FK_3
	FOREIGN KEY (ProjektiID)
	REFERENCES Projekti (ProjektiID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE
);