Advertisement
patryk

SBD2 - FOS Create Table

Oct 28th, 2015
446
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 2.45 KB | None | 0 0
  1. CREATE TABLE USERS (
  2.   LOGIN         VARCHAR(15) NOT NULL,
  3.   PASSWORD      VARCHAR(25) NOT NULL,
  4.   TYPE          VARCHAR(7) NOT NULL,
  5.   MAIL          VARCHAR(40),
  6.   CITY          VARCHAR(20),
  7.   STREET        VARCHAR(20),
  8.   HOUSE_NUMBER  VARCHAR(8),
  9.   FLAT_NUMBER   NUMBER(5),
  10.  
  11.   PRIMARY KEY (LOGIN),
  12.  
  13.   CONSTRAINT USER_TYPE CHECK (
  14.     (
  15.       TYPE = 'CLIENT' AND
  16.       MAIL IS NOT NULL AND
  17.       CITY IS NOT NULL AND
  18.       STREET IS NOT NULL AND
  19.       HOUSE_NUMBER IS NOT NULL
  20.     )
  21.     OR
  22.     (
  23.       TYPE = 'MANAGER' AND
  24.       MAIL IS NULL AND
  25.       CITY IS NULL AND
  26.       STREET IS NULL AND
  27.       HOUSE_NUMBER IS NULL AND
  28.       FLAT_NUMBER IS NULL
  29.     )
  30.   )
  31. )
  32.  
  33.  
  34.  
  35. CREATE TABLE DISTRICTS (
  36.   NAME        VARCHAR(20) NOT NULL,
  37.   STREET      VARCHAR(20) NOT NULL,
  38.   CITY        VARCHAR(20) NOT NULL,
  39.  
  40.   PRIMARY KEY (STREET, CITY)
  41. )
  42.  
  43.  
  44.  
  45. CREATE TABLE RESTAURANTS (
  46.   ID            INT NOT NULL AUTO_INCREMENT,
  47.   NAME          VARCHAR(20) NOT NULL,
  48.   CUISINE       VARCHAR(20) NOT NULL,
  49.   CITY          VARCHAR(20) NOT NULL,
  50.   STREET VAR    CHAR(20) NOT NULL,
  51.   HOUSE_NUMBER  VARCHAR(8) NOT NULL,
  52.   FLAT_NUMBER   VARCHAR(5),
  53.   LOGO          BLOB,
  54.  
  55.   PRIMARY KEY(ID)
  56. )
  57.  
  58.  
  59.  
  60. CREATE TABLE DISHES (
  61.   NAME            VARCHAR(20) NOT NULL,
  62.   DESCRIPTION     VARCHAR(250) NOT NULL,
  63.   PRICE           NUMBER(5,2) NOT NULL,
  64.   TYPE            VARCHAR(10) NOT NULL,
  65.   VEGETARIAN      BOOLEAN,
  66.   SPICY           BOOLEAN,
  67.   FISH            BOOLEAN,
  68.   GARLIC          BOOLEAN,
  69.   RESTAURANT_ID   INT NOT NULL,
  70.  
  71.   FOREIGN KEY (RESTAURANT_ID) REFERENCES RESTAURANTS(ID),
  72.   PRIMARY KEY (RESTAURANT_ID, NAME),
  73.  
  74.   CONSTRAINT DISH_TYPE CHECK (
  75.     TYPE IN (
  76.       'STARTER', 'SOUP', 'MAIN_COURSE', 'PASTA',
  77.       'PIZZA', 'FISH', 'DESSERT', 'DRINK'
  78.     )
  79.   )
  80. )
  81.  
  82.  
  83.  
  84. CREATE TABLE RATES (
  85.   ID              INT NOT NULL AUTO_INCREMENT,
  86.   STARS           NUMBER(5) NOT NULL,
  87.   CLIENT_LOGIN    VARCHAR(15) NOT NULL,
  88.   RESTAURANT_ID   INT NOT NULL,
  89.  
  90.   FOREIGN KEY (CLIENT_LOGIN)  REFERENCES (USERS(LOGIN)),
  91.   FOREIGN KEY (RESTAURANT_ID) REFERENCES RESTAURANTS (ID),
  92.   PRIMARY KEY (ID)
  93. )
  94.  
  95.  
  96.  
  97. CREATE TABLE ORDERS (
  98.   ID              INT NOT NULL AUTO_INCREMENT,
  99.   STATUS          BOOLEAN NOT NULL,
  100.   ESTIMATED_TIME  NUMBER(5),
  101.   CLIENT_LOGIN    VARCHAR(15) NOT NULL,
  102.   RESTAURANT_ID   INT NOT NULL,
  103.  
  104.   FOREIGN KEY (CLIENT_LOGIN)  REFERENCES (USERS(LOGIN)),
  105.   FOREIGN KEY (RESTAURANT_ID) REFERENCES (RESTAURANTS(ID)),
  106.   PRIMARY KEY (ID)
  107. )
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement