SBD2 - FOS Create Table

CREATE TABLE USERS (
  LOGIN         VARCHAR(15) NOT NULL,
  PASSWORD      VARCHAR(25) NOT NULL,
  TYPE          VARCHAR(7) NOT NULL,
  MAIL          VARCHAR(40),
  CITY          VARCHAR(20),
  STREET        VARCHAR(20),
  HOUSE_NUMBER  VARCHAR(8),
  FLAT_NUMBER   NUMBER(5),

  PRIMARY KEY (LOGIN),

  CONSTRAINT USER_TYPE CHECK (
    (
      TYPE = 'CLIENT' AND
      MAIL IS NOT NULL AND
      CITY IS NOT NULL AND
      STREET IS NOT NULL AND
      HOUSE_NUMBER IS NOT NULL
    )
    OR
    (
      TYPE = 'MANAGER' AND
      MAIL IS NULL AND
      CITY IS NULL AND
      STREET IS NULL AND
      HOUSE_NUMBER IS NULL AND
      FLAT_NUMBER IS NULL
    )
  )
)



CREATE TABLE DISTRICTS (
  NAME        VARCHAR(20) NOT NULL,
  STREET      VARCHAR(20) NOT NULL,
  CITY        VARCHAR(20) NOT NULL,

  PRIMARY KEY (STREET, CITY)
)



CREATE TABLE RESTAURANTS (
  ID            INT NOT NULL AUTO_INCREMENT,
  NAME          VARCHAR(20) NOT NULL,
  CUISINE       VARCHAR(20) NOT NULL,
  CITY          VARCHAR(20) NOT NULL,
  STREET VAR    CHAR(20) NOT NULL,
  HOUSE_NUMBER  VARCHAR(8) NOT NULL,
  FLAT_NUMBER   VARCHAR(5),
  LOGO          BLOB,

  PRIMARY KEY(ID)
)



CREATE TABLE DISHES (
  NAME            VARCHAR(20) NOT NULL,
  DESCRIPTION     VARCHAR(250) NOT NULL,
  PRICE           NUMBER(5,2) NOT NULL,
  TYPE            VARCHAR(10) NOT NULL,
  VEGETARIAN      BOOLEAN,
  SPICY           BOOLEAN,
  FISH            BOOLEAN,
  GARLIC          BOOLEAN,
  RESTAURANT_ID   INT NOT NULL,

  FOREIGN KEY (RESTAURANT_ID) REFERENCES RESTAURANTS(ID),
  PRIMARY KEY (RESTAURANT_ID, NAME),

  CONSTRAINT DISH_TYPE CHECK (
    TYPE IN (
      'STARTER', 'SOUP', 'MAIN_COURSE', 'PASTA',
      'PIZZA', 'FISH', 'DESSERT', 'DRINK'
    )
  )
)



CREATE TABLE RATES (
  ID              INT NOT NULL AUTO_INCREMENT,
  STARS           NUMBER(5) NOT NULL,
  CLIENT_LOGIN    VARCHAR(15) NOT NULL,
  RESTAURANT_ID   INT NOT NULL,

  FOREIGN KEY (CLIENT_LOGIN)  REFERENCES (USERS(LOGIN)),
  FOREIGN KEY (RESTAURANT_ID) REFERENCES RESTAURANTS (ID),
  PRIMARY KEY (ID)
)



CREATE TABLE ORDERS (
  ID              INT NOT NULL AUTO_INCREMENT,
  STATUS          BOOLEAN NOT NULL,
  ESTIMATED_TIME  NUMBER(5),
  CLIENT_LOGIN    VARCHAR(15) NOT NULL,
  RESTAURANT_ID   INT NOT NULL,

  FOREIGN KEY (CLIENT_LOGIN)  REFERENCES (USERS(LOGIN)),
  FOREIGN KEY (RESTAURANT_ID) REFERENCES (RESTAURANTS(ID)),
  PRIMARY KEY (ID)
)