Advertisement
S_Madanska

_

Jan 28th, 2022
965
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE TABLE REGIONS
  2. (
  3.     REGION_ID SMALLINT NOT NULL PRIMARY KEY IDENTITY(1,1),
  4.     NAME VARCHAR(25) NOT NULL UNIQUE
  5. )
  6.  
  7. CREATE TABLE COUNTRIES
  8. (
  9.     COUNTRY_ID CHAR(2) NOT NULL,
  10.     NAME VARCHAR(40) NOT NULL,
  11.     REGION_ID SMALLINT,
  12.  
  13.     CONSTRAINT PK_COUNTRIES PRIMARY KEY (COUNTRY_ID),
  14.     CONSTRAINT FK_COUNTRIES_REGIONS FOREIGN KEY (REGION_ID)
  15.         REFERENCES REGIONS (REGION_ID)
  16. )
  17.  
  18. CREATE TABLE CUSTOMERS
  19. (
  20.     CUSTOMER_ID NUMERIC(6) NOT NULL PRIMARY KEY,
  21.     COUNTRY_ID CHAR(2) NOT NULL,
  22.     FNAME VARCHAR(20) NOT NULL,
  23.     LNAME VARCHAR(20) NOT NULL,
  24.     ADDRESS TEXT,
  25.     EMAIL VARCHAR(30),
  26.     GENDER CHAR(1) DEFAULT 'M'
  27.         CHECK(GENDER IS NULL OR (GENDER IN('M','F')))
  28. )
  29.  
  30. ALTER TABLE CUSTOMERS
  31. ADD CONSTRAINT FK_CUSTOMERS_COUNTRIES FOREIGN KEY(COUNTRY_ID)
  32.     REFERENCES COUNTRIES(COUNTRY_ID)
  33.  
  34. CREATE TABLE JOBS
  35. (
  36.     JOB_ID VARCHAR(10) NOT NULL PRIMARY KEY,
  37.     JOB_TITLE VARCHAR(35) NOT NULL,
  38.     MIN_SALARY NUMERIC(6),
  39.     MAX_SALARY NUMERIC(6),
  40. )
  41.  
  42. CREATE TABLE EMPLOYEES
  43. (
  44.     EMPLOYEE_ID INT NOT NULL PRIMARY KEY,
  45.     FNAME VARCHAR(20) NOT NULL,
  46.     LNAME VARCHAR(25) NOT NULL,
  47.     EMAIL VARCHAR(40) NOT NULL UNIQUE,
  48.     PHONE VARCHAR(20),
  49.     HIRE_DATE DATETIME NOT NULL,
  50.     SALARY NUMERIC(8,2) NOT NULL CHECK(SALARY>0),
  51.     JOB_ID VARCHAR(10) NOT NULL REFERENCES JOBS,
  52.     MANAGER_ID INT,
  53.     DEPARTMENT_ID INT,
  54.  
  55.     CONSTRAINT FK_EMPLOYEE_MANAGERS FOREIGN KEY(MANAGER_ID)
  56.         REFERENCES EMPLOYEES(EMPLOYEE_ID)
  57. )
  58.  
  59. CREATE TABLE DEPARTMENTS
  60. (
  61.     DEPARTMENT_ID INT NOT NULL,
  62.     NAME VARCHAR(30) NOT NULL,
  63.     MANAGER_ID INT,
  64.     COUNTRY_ID CHAR(2) NOT NULL,
  65.     CITY VARCHAR(30) NOT NULL,
  66.     STATE VARCHAR(25),
  67.     ADDRESS VARCHAR(40),
  68.     POSTAL_CODE VARCHAR(12),
  69.  
  70.     CONSTRAINT PK_DEPARTMENTS PRIMARY KEY (DEPARTMENT_ID),
  71.     CONSTRAINT FK_DEPT_COUNTRIES FOREIGN KEY (COUNTRY_ID)REFERENCES COUNTRIES(COUNTRY_ID),
  72.     CONSTRAINT FK_DEPT_MANGR FOREIGN KEY (MANAGER_ID)REFERENCES EMPLOYEES(EMPLOYEE_ID)
  73. )
  74.  
  75. ALTER TABLE EMPLOYEES
  76. ADD CONSTRAINT FK_EMPL_DEPT FOREIGN KEY (DEPARTMENT_ID)
  77. REFERENCES DEPARTMENTS(DEPARTMENT_ID)
  78.  
  79. CREATE TABLE ORDERS
  80. (
  81.     ORDER_ID INT NOT NULL PRIMARY KEY,
  82.     ORDER_DATE DATETIME NOT NULL,
  83.     CUSTOMER_ID NUMERIC(6) NOT NULL REFERENCES CUSTOMERS,
  84.     EMPLOYEE_ID INT NOT NULL REFERENCES EMPLOYEES,
  85.     SHIP_ADDRESS VARCHAR(150)
  86. )
  87.  
  88. CREATE TABLE PRODUCTS
  89. (
  90.     PRODUCT_ID INT NOT NULL PRIMARY KEY,
  91.     NAME VARCHAR(70) NOT NULL,
  92.     PRICE NUMERIC(8,2) NOT NULL,
  93.     DESCR VARCHAR(2000)
  94. )
  95.  
  96. CREATE TABLE ORDER_ITEMS
  97. (
  98.     ORDER_ID INT NOT NULL,
  99.     PRODUCT_ID INTEGER NOT NULL,
  100.     UNIT_PRICE NUMERIC(8,2) NOT NULL,
  101.     QUANTITY NUMERIC(8) NOT NULL,
  102.  
  103.     CONSTRAINT PK_OI PRIMARY KEY(ORDER_ID, PRODUCT_ID),
  104.  
  105.     CONSTRAINT FK_OI_O FOREIGN KEY (ORDER_ID)REFERENCES ORDERS (ORDER_ID)
  106.             ON UPDATE CASCADE ON DELETE CASCADE,
  107.  
  108.     CONSTRAINT FK_OI_P FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCTS(PRODUCT_ID)
  109. )
Advertisement
Advertisement
Advertisement
RAW Paste Data Copied
Advertisement