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

CREATE TABLE Postinumero (
Postinumero      CHAR(5)     NOT NULL,
Postitoimipaikka VARCHAR(64) NOT NULL,
CONSTRAINT Postinumero_PK
   PRIMARY KEY (Postinumero)
);

CREATE TABLE Tiedekunta (
TiedekuntaID      INTEGER     NOT NULL,
Nimi              VARCHAR(64) NOT NULL,
CONSTRAINT Tiedekunta_PK
   PRIMARY KEY (TiedekuntaID)
);

CREATE TABLE Laitos (
LaitosID          INTEGER     NOT NULL,
Nimi              VARCHAR(64) NOT NULL,
Tiedekunta        INTEGER     NOT NULL,
CONSTRAINT Laitos_PK
   PRIMARY KEY (LaitosID),
CONSTRAINT Laitos_FK_T
   FOREIGN KEY (Tiedekunta)
   REFERENCES Tiedekunta (TiedekuntaID)
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
);

CREATE TABLE Opiskelija (
Sotu         CHAR(11)     NOT NULL,
Sahkopostiosoite              VARCHAR(64),
Etunimi              VARCHAR(32) NOT NULL,
Sukunimi              VARCHAR(64) NOT NULL,
Lahiosoite              VARCHAR(64) NOT NULL,
Postinumero              CHAR(5) NOT NULL,
Aloitusvuosi            SMALLINT NOT NULL,
Laitos             INTEGER NOT NULL,
CONSTRAINT Opiskelija_PK
   PRIMARY KEY (Sotu),
CONSTRAINT Opiskelija_FK_L
   FOREIGN KEY (Laitos)
   REFERENCES Laitos (LaitosID)
	ON DELETE NO ACTION
	ON UPDATE NO ACTION,
CONSTRAINT Opiskelija_FK_P
   FOREIGN KEY (Postinumero)
   REFERENCES Postinumero (Postinumero)
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
);

CREATE TABLE Puhelinnumero (
Sotu             CHAR(11)    NOT NULL,
Puhelinnumero VARCHAR(32) NOT NULL,
CONSTRAINT Puhelinnumero_PK
   PRIMARY KEY (Sotu, Puhelinnumero),
CONSTRAINT Puhelinnumero_FK_L
   FOREIGN KEY (Sotu)
   REFERENCES Opiskelija (Sotu)
	ON DELETE CASCADE
	ON UPDATE NO ACTION
);

CREATE TABLE Kurssi (
Kurssikoodi             CHAR(6)    NOT NULL,
Nimi VARCHAR(64) NOT NULL,
Laajuus DOUBLE NOT NULL,
Laitos INTEGER NOT NULL,
CONSTRAINT Kurssi_PK
   PRIMARY KEY (Kurssikoodi),
CONSTRAINT Kurssi_FK_L
   FOREIGN KEY (Laitos)
   REFERENCES Laitos (LaitosID)
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
);

CREATE TABLE Tenttii (
Opiskelija             CHAR(11)    NOT NULL,
Kurssi CHAR(6) NOT NULL,
Paivamaara DATE NOT NULL,
Arvosana DOUBLE NOT NULL,
CONSTRAINT Tenttii_PK
   PRIMARY KEY (Opiskelija, Kurssi, Paivamaara),
CONSTRAINT Tenttii_FK_O
   FOREIGN KEY (Opiskelija)
   REFERENCES Opiskelija (Sotu)
	ON DELETE NO ACTION
	ON UPDATE NO ACTION,
CONSTRAINT Tenttii_FK_K
   FOREIGN KEY (Kurssi)
   REFERENCES Kurssi (Kurssikoodi)
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
);