14.       Write the SQL Script to create the tables.

 

CREATE TABLE team

( teamID        CHAR(5),

  teamname      CHAR(20),

  PRIMARY KEY (teamID));

 

CREATE TABLE game

( gamenumber    CHAR(5),

  hometeam      CHAR(5)   NOT NULL,

  visitingteam  CHAR(5)   NOT NULL,

  HTScore       SMALLINT  NOT NULL,

  VTScore       SMALLINT  NOT NULL,

  gamedate      DATE      NOT NULL,

  PRIMARY KEY (gamenumber),

  FOREIGN KEY (hometeam) REFERENCES team (teamID),

  FOREIGN KEY (visitingteam) REFERENCES team (teamID));

 

CREATE TABLE player

( playerID      CHAR(5),

  playerName    Char(30)  NOT NULL,

  position      char(20)  NOT NULL,

  PRIMARY KEY (playerID));

 

CREATE TABLE employs

( playerID      CHAR(5),

  teamID        CHAR(5),

  Startdate     DATE,

  Enddate       DATE     DEFAULT NULL,

  PRIMARY KEY (playerID, teamID, Startdate),

  FOREIGN KEY (PlayerID) REFERENCES player (playerID),

  FOREIGN KEY (teamID) REFERENCES team (TeamID));