Guest User

Untitled

a guest
Jun 19th, 2018
117
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 1.62 KB | None | 0 0
  1. CREATE TABLE Train
  2. (
  3.   trainID   CHAR(10) NOT NULL UNIQUE,
  4.   departure TIMESTAMP(0),
  5.   arrival   TIMESTAMP(0),
  6.   trainStatus   CHAR(20),
  7.   pairedWith    CHAR(10),
  8.   confirmed BOOLEAN,
  9.  
  10.   PRIMARY KEY (trainID),
  11.   FOREIGN KEY (pairedWith) REFERENCES Train (trainID)
  12. );
  13.  
  14.  
  15. *
  16. CREATE TABLE Car
  17. (
  18.   carID     CHAR(10) NOT NULL UNIQUE,
  19.   description   VARCHAR,
  20.  
  21.   PRIMARY KEY (carID),
  22. );
  23.  
  24.  
  25. *
  26. CREATE TABLE Route
  27. (
  28.   routeID   CHAR(4) NOT NULL UNIQUE,
  29.   trainRatios   CHAR(20),
  30.  
  31. PRIMARY KEY (routeID)
  32. );
  33.  
  34.  
  35.  
  36. CREATE TABLE Section
  37. (
  38.   sectionID     SERIAL,
  39.   westCity  CHAR(20),
  40.   eastCity  CHAR(20),
  41.  
  42.   PRIMARY KEY (sectionID),
  43. );
  44.  
  45.  
  46.  
  47. CREATE TABLE Travels
  48. (
  49.   trainID   CHAR(10) NOT NULL,
  50.   routeID   CHAR(4) NOT NULL,
  51.  
  52.   FOREIGN KEY (trainID) REFERENCES Train (trainID),
  53.   FOREIGN KEY (routeID) REFERENCES Route (routeID)
  54. );
  55.  
  56.  
  57.  
  58. CREATE TABLE PartOf
  59. (
  60.   routeID   CHAR(4) NOT NULL,
  61.   sectionID INTEGER NOT NULL,
  62.  
  63.   PRIMARY KEY (routeID, sectionID),
  64.   FOREIGN KEY (routeID) REFERENCES Route (routeID),
  65.   FOREIGN KEY (sectionID) REFERENCES Section(routeID)
  66. );
  67.  
  68.  
  69.  
  70. CREATE TABLE OnBoard
  71. (
  72.   trainID   CHAR(10) NOT NULL,
  73.   sectionID INTEGER NOT NULL,
  74.   date      TIMESTAMP(0) NOT NULL,
  75.   redBkng   INTEGER,
  76.   silverBkng    INTEGER,
  77.   goldBkng  INTEGER,
  78.  
  79.   PRIMARY KEY (trainID, sectionID, date),
  80.   FOREIGN KEY (trainID) REFERENCES Train (trainID),
  81.   FOREIGN KEY (sectionID) REFERENCES Section(routeID)
  82. );
  83.  
  84.  
  85.  
  86. CREATE TABLE ConsistsOf
  87. (
  88.   trainID   CHAR(10) NOT NULL,
  89.   carID     CHAR(10) NOT NULL,
  90.  
  91.   PRIMARY KEY (trainID, carID),
  92.   FOREIGN KEY (trainID) REFERENCES Train (trainID),
  93.    FOREIGN KEY (carID) REFERENCES Car(carID)
  94. );
Add Comment
Please, Sign In to add comment