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

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 UPDATE CASCADE
      ON DELETE 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 UPDATE CASCADE
      ON DELETE NO ACTION,
CONSTRAINT Opiskelija_FK_P
   FOREIGN KEY (Postinumero)
   REFERENCES Postinumero (Postinumero)
      ON UPDATE CASCADE
      ON DELETE NO ACTION,
CONSTRAINT Opiskelija_Check_A
   CHECK (Opiskelija.Aloitusvuosi >= 1990)
)
;
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 UPDATE CASCADE
      ON DELETE CASCADE

)
;
CREATE TABLE Kurssi (
Kurssikoodi    CHAR(6)              NOT NULL,
Nimi           VARCHAR(64)          NOT NULL,
Laajuus        DOUBLE PRECISION     NOT NULL DEFAULT 1,
Laitos         INTEGER              NOT NULL,
CONSTRAINT Kurssi_PK
   PRIMARY KEY (Kurssikoodi),
CONSTRAINT Kurssi_FK_L
   FOREIGN KEY (Laitos)
   REFERENCES Laitos (LaitosID)
      ON UPDATE CASCADE
      ON DELETE NO ACTION
)
;
CREATE TABLE Tenttii (
Opiskelija   CHAR(11)           NOT NULL,
Kurssi       CHAR(6)            NOT NULL,
Paivamaara   DATE               NOT NULL, 
Arvosana     DOUBLE PRECISION   NOT NULL DEFAULT 1,
CONSTRAINT Tenttii_PK
   PRIMARY KEY (Opiskelija, Kurssi, Paivamaara),
CONSTRAINT Tenttii_FK_O
   FOREIGN KEY (Opiskelija)
   REFERENCES Opiskelija (Sotu)
      ON UPDATE CASCADE
      ON DELETE NO ACTION,
CONSTRAINT Tenttii_FK_K
   FOREIGN KEY (Kurssi)
   REFERENCES Kurssi (Kurssikoodi)
      ON UPDATE CASCADE
      ON DELETE NO ACTION
)
;