Relaatioiden luominen SQL:llä - vastaukset

• Opiskelijatietokanta
• Yritystietokanta
• Futistietokanta

Opiskelijatietokanta

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
)
;

Yritystietokanta

DROP TABLE Tekee
;
DROP TABLE Henkilo
;
DROP TABLE Projekti
;
DROP TABLE Tehtava
;
DROP TABLE Nimike
;

CREATE TABLE Nimike (
NimikeID INTEGER     NOT NULL,
Nimike   VARCHAR(64) NOT NULL,
CONSTRAINT Nimike_PK
   PRIMARY KEY (NimikeID)
)
;
CREATE TABLE Tehtava (
TehtavaID   INTEGER     NOT NULL,
TehtavaNimi VARCHAR(64) NOT NULL,
CONSTRAINT Tehtava_PK
   PRIMARY KEY (TehtavaID)
)
;
CREATE TABLE Projekti (
ProjektiID   INTEGER     NOT NULL,
ProjektiNimi VARCHAR(64) NOT NULL,
Alkamispvm   DATE        NOT NULL,
Paattymispvm DATE        NOT NULL,
CONSTRAINT Projekti_PK
   PRIMARY KEY (ProjektiID)
)
;
CREATE TABLE Henkilo (
Email     VARCHAR(64) NOT NULL, 
Etunimi   VARCHAR(32) NOT NULL,
Sukunimi  VARCHAR(32) NOT NULL, 
PuhNro    VARCHAR(32) DEFAULT '-',
Tyonimike INTEGER     NOT NULL, 
CONSTRAINT Henkilo_PK
   PRIMARY KEY (Email),
CONSTRAINT Henkilo_FK_T
   FOREIGN KEY (Tyonimike)
   REFERENCES Nimike (NimikeID)
      ON UPDATE CASCADE
      ON DELETE NO ACTION
)
;
CREATE TABLE Tekee (
Henkilo  VARCHAR(64) NOT NULL, 
Tehtava  INTEGER     NOT NULL,
Projekti INTEGER     NOT NULL,
CONSTRAINT Tekee_PK
   PRIMARY KEY (Henkilo, Tehtava, Projekti),
CONSTRAINT Tekee_FK_H
   FOREIGN KEY (Henkilo)
   REFERENCES Henkilo (Email)
      ON UPDATE CASCADE
      ON DELETE NO ACTION,
CONSTRAINT Tekee_FK_T
   FOREIGN KEY (Tehtava)
   REFERENCES Tehtava (TehtavaID)
      ON UPDATE CASCADE
      ON DELETE NO ACTION,
CONSTRAINT Tekee_FK_P
   FOREIGN KEY (Projekti)
   REFERENCES Projekti (ProjektiID)
      ON UPDATE CASCADE
      ON DELETE NO ACTION
)
;

Futistietokanta

DROP TABLE Tapahtuu
;
DROP TABLE Peli
;
DROP TABLE Pelaaja
;
DROP TABLE Joukkue
;
DROP TABLE Tapahtuma
;
DROP TABLE Maa
;

CREATE TABLE Maa (
MaaID   INTEGER     NOT NULL,
MaaNimi VARCHAR(64) NOT NULL,
CONSTRAINT Maa_PK
   PRIMARY KEY (MaaID)
)
;
CREATE TABLE Tapahtuma (
TapahtumaID   INTEGER     NOT NULL,
TapahtumaNimi VARCHAR(64) NOT NULL,
CONSTRAINT Tapahtuma_PK
   PRIMARY KEY (TapahtumaID)
)
;
CREATE TABLE Joukkue (
JoukkueID   INTEGER     NOT NULL,
JoukkueNimi VARCHAR(64) NOT NULL,
Kotoisin    INTEGER     NOT NULL,
CONSTRAINT Joukkue_PK
   PRIMARY KEY (JoukkueID),
CONSTRAINT Joukkue_FK_K
   FOREIGN KEY (Kotoisin)
   REFERENCES Maa (MaaID)
      ON UPDATE CASCADE
      ON DELETE NO ACTION
)
;
CREATE TABLE Pelaaja (
PelaajaID   INTEGER     NOT NULL,
Etunimi     VARCHAR(32) NOT NULL,
Sukunimi    VARCHAR(64) NOT NULL,
Pelinro     SMALLINT    NOT NULL DEFAULT 1,
Syntymaaika DATE        NOT NULL,
Pelaa       INTEGER NOT NULL,
CONSTRAINT Pelaaja_PK
   PRIMARY KEY (PelaajaID),
CONSTRAINT Pelaaja_FK_P
   FOREIGN KEY (Pelaa)
   REFERENCES Joukkue (JoukkueID)
      ON UPDATE CASCADE
      ON DELETE NO ACTION
)
;
CREATE TABLE Peli (
PeliID INTEGER   NOT NULL,
Aika   TIMESTAMP NOT NULL,
Koti   INTEGER   NOT NULL,
Vieras INTEGER   NOT NULL,
CONSTRAINT Peli_PK
   PRIMARY KEY (PeliID),
CONSTRAINT Peli_FK_K
   FOREIGN KEY (Koti)
   REFERENCES Joukkue (JoukkueID)
      ON UPDATE CASCADE
      ON DELETE NO ACTION,
CONSTRAINT Peli_FK_V
   FOREIGN KEY (Vieras)
   REFERENCES Joukkue (JoukkueID)
      ON UPDATE CASCADE
      ON DELETE NO ACTION
)
;
CREATE TABLE Tapahtuu (
Aika      TIME    NOT NULL,
Peli      INTEGER NOT NULL,
Pelaaja   INTEGER NOT NULL,
Tapahtuma INTEGER NOT NULL,
CONSTRAINT Tapahtuu_PK
   PRIMARY KEY (Aika, Peli, Pelaaja, Tapahtuma),
CONSTRAINT Tapahtuu_FK_Peli
   FOREIGN KEY (Peli)
   REFERENCES Peli (PeliID)
      ON UPDATE CASCADE
      ON DELETE NO ACTION,
CONSTRAINT Tapahtuu_FK_P
   FOREIGN KEY (Pelaaja)
   REFERENCES Pelaaja (PelaajaID)
      ON UPDATE CASCADE
      ON DELETE NO ACTION,
CONSTRAINT Tapahtuu_FK_T
   FOREIGN KEY (Tapahtuma)
   REFERENCES Tapahtuma (TapahtumaID)
      ON UPDATE CASCADE
      ON DELETE NO ACTION
)
;