DROP TABLE Tenttii
;
DROP TABLE Kurssi
;
DROP TABLE Opiskelija
;
DROP TABLE Tiedekunta
;
DROP TABLE Laitos
;

CREATE TABLE Tiedekunta (
Nimi VARCHAR(100) NOT NULL,
TiedekuntaID INTEGER NOT NULL,

CONSTRAINT Tiedekunta_PK 
	PRIMARY KEY (TiedekuntaID)
);

CREATE TABLE Laitos (
Nimi VARCHAR(100) NOT NULL,
LaitosID INTEGER NOT NULL,
TiedekuntaID INTEGER NOT NULL,

CONSTRAINT Laitos_PK 
	PRIMARY KEY (LaitosID),
CONSTRAINT Laitos_TiedekuntaID 
	FOREIGN KEY (TiedekuntaID)
	REFERENCES Tiedekunta (TiedekuntaID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE
);





CREATE TABLE Kurssi (
Nimi VARCHAR(100) NOT NULL,
Kurssikoodi CHAR(6) NOT NULL,
Laajuus NUMERIC(3,1) NOT NULL,
LaitosID INTEGER NOT NULL,

CONSTRAINT Kurssi_PK 
	PRIMARY KEY (Kurssikoodi),
CONSTRAINT Kurssi_LaitosID 
	FOREIGN KEY (LaitosID)
	REFERENCES Laitos (LaitosID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE
);



CREATE TABLE Opiskelija (
Hetu CHAR(11) NOT NULL,
Sahkopostiosoite VARCHAR(100) NOT NULL,
Etunimi VARCHAR(40) NOT NULL,
Sukunimi VARCHAR(100) NOT NULL,
Lahiosoite VARCHAR(150) NOT NULL,
Postinumero CHAR(5) NOT NULL,
Postitoimipaikka VARCHAR(60) NOT NULL,
Puhelinnumero VARCHAR(40) NULL,
Aloitusvuosi INTEGER NOT NULL,
LaitosID INTEGER NOT NULL,

CONSTRAINT Opiskelija_PK 
	PRIMARY KEY (Hetu),
CONSTRAINT Opiskelija_LaitosID 
	FOREIGN KEY (LaitosID)
	REFERENCES Laitos (LaitosID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE
);



CREATE TABLE Tenttii (
Paivamaara DATE NOT NULL,
Arvosana NUMERIC(2,1) NOT NULL,
Kurssi_Kurssikoodi CHAR(6) NOT NULL,
Opiskelija_Hetu CHAR(11) NOT NULL,

CONSTRAINT Tenttii_PK
	PRIMARY KEY (Kurssi_Kurssikoodi,Opiskelija_Hetu,Paivamaara),
CONSTRAINT Tenttii_Kurssikoodi 
	FOREIGN KEY (Kurssi_Kurssikoodi)
	REFERENCES Kurssi (Kurssikoodi)
		ON DELETE NO ACTION
		ON UPDATE CASCADE,
CONSTRAINT Tenttii_Hetu 
	FOREIGN KEY (Opiskelija_Hetu)
	REFERENCES Opiskelija (Hetu)
		ON DELETE NO ACTION
		ON UPDATE CASCADE
);