S_Madanska

13

Feb 13th, 2021 (edited)
152
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. --създаваме база данни
  2. CREATE DATABASE TRADE_MASTERS
  3. --достъпваме база данни
  4. USE TRADE_MASTERS
  5.  
  6. --настройваме колацията на конкретната база да е кирилица
  7. ALTER DATABASE CURRENT COLLATE CYRILLIC_GENERAL_CI_AI;
  8.  
  9. CREATE TABLE REGIONS
  10. (
  11.     REGION_ID SMALLINT NOT NULL PRIMARY KEY IDENTITY(1,1),
  12.     NAME VARCHAR(25) NOT NULL UNIQUE
  13. )
  14.  
  15.  
  16. CREATE TABLE COUNTRIES
  17. (
  18.     COUNTRY_ID CHAR(2) NOT NULL,
  19.     NAME VARCHAR(40) NOT NULL,
  20.     REGION_ID SMALLINT NULL,
  21.  
  22.     CONSTRAINT PK_COUNTRIES PRIMARY KEY (COUNTRY_ID),
  23.     CONSTRAINT FK_COUNTRIES_REGIONS FOREIGN KEY (REGION_ID)
  24.     REFERENCES REGIONS (REGION_ID)
  25. )
  26.  
  27. CREATE TABLE CUSTOMERS
  28. (
  29.     CUSTOMER_ID NUMERIC(6) NOT NULL,
  30.     COUNTRY_ID CHAR(2) NOT NULL,
  31.     FNAME VARCHAR(20) NOT NULL,
  32.     LNAME VARCHAR(20) NOT NULL,
  33.     ADDRESS TEXT NULL,
  34.     EMAIL VARCHAR(30) NULL,
  35.     GENDER CHAR(1) NULL DEFAULT 'M'
  36.     CONSTRAINT CUST_GENDER CHECK( GENDER IS NULL OR (GENDER IN('M','F'))),
  37.     CONSTRAINT OK_CUSTOMERS PRIMARY KEY(CUSTOMER_ID)
  38. )
  39.  
  40. ALTER TABLE CUSTOMERS
  41. ADD CONSTRAINT FK_CUSTOMERS_COUNTRIES FOREIGN KEY (COUNTRY_ID)
  42. REFERENCES COUNTRIES(COUNTRY_ID)
  43.  
  44. CREATE TABLE JOBS
  45. (
  46.     JOB_ID VARCHAR(10) NOT NULL PRIMARY KEY,
  47.     JOB_TITLE VARCHAR(35) NOT NULL,
  48.     MIN_SALARY NUMERIC(6) NULL,
  49.     MAX_SALARY NUMERIC(6) NULL
  50. )
  51.  
  52. CREATE TABLE EMPLOYEES
  53. (
  54.     EMPLOYEE_ID INT NOT NULL,
  55.     FNAME VARCHAR(20) NOT NULL,
  56.     LNAME VARCHAR(25) NOT NULL,
  57.     EMAIL VARCHAR(40) NOT NULL,
  58.     PHONE VARCHAR(20) NULL,
  59.     HIRE_DATE DATETIME NOT NULL,
  60.     SALARY NUMERIC(8,2) NOT NULL CONSTRAINT SALARY_CHECK CHECK(SALARY>0),
  61.     JOB_ID VARCHAR(10) NOT NULL,
  62.     MANAGER_ID INT NULL,
  63.     DEPARTMENT_ID INT NULL,
  64.     CONSTRAINT PK_EMPLOYEES PRIMARY KEY(EMPLOYEE_ID),
  65.     CONSTRAINT UK_EMAIL UNIQUE(EMAIL),
  66.     CONSTRAINT FK_EMPLOYEES_JOBS FOREIGN KEY (JOB_ID)
  67.         REFERENCES JOBS(JOB_ID),
  68.     CONSTRAINT FK_EMPLOYEES_MANAGERS FOREIGN KEY (MANAGER_ID)
  69.         REFERENCES EMPLOYEES(EMPLOYEE_ID)
  70. )
  71.  
  72. CREATE TABLE DEPARTMENTS
  73. (
  74.     DEPARTMENT_ID INT NOT NULL,
  75.     NAME VARCHAR(30) NOT NULL,
  76.     MANAGER_ID INT NULL,
  77.     COUNTRY_ID CHAR(2) NOT NULL,
  78.     CITY VARCHAR(30) NOT NULL,
  79.     STATE VARCHAR(25),
  80.     ADDRESS VARCHAR(40),
  81.     POSTAL_CODE VARCHAR(12),
  82.     CONSTRAINT PK_DEPARTMENTS PRIMARY KEY(DEPARTMENT_ID),
  83.     CONSTRAINT FK_DEPT_MGR FOREIGN KEY (MANAGER_ID)
  84.         REFERENCES EMPLOYEES(EMPLOYEE_ID),
  85.     CONSTRAINT FK_DEPT_COUNTRIES FOREIGN KEY (COUNTRY_ID)
  86.         REFERENCES COUNTRIES(COUNTRY_ID)
  87. )
  88.  
  89. ALTER TABLE EMPLOYEES
  90. ADD CONSTRAINT FK_EMPLOYEE_DEPARTMENTS FOREIGN KEY (DEPARTMENT_ID)
  91. REFERENCES DEPARTMENTS
  92.  
  93.  
  94. CREATE TABLE ORDERS
  95. (
  96.     ORDER_ID INT NOT NULL PRIMARY KEY,
  97.     ORDER_DATE DATETIME NOT NULL,
  98.     CUSTOMER_ID NUMERIC(6) NOT NULL FOREIGN KEY REFERENCES CUSTOMERS,
  99.     EMPLOYEE_ID INT NOT NULL FOREIGN KEY REFERENCES EMPLOYEES,
  100.     SHIP_ADDRESS VARCHAR(150)
  101. )
  102.  
  103. CREATE TABLE PRODUCTS
  104. (
  105.     PRODUCT_ID INT NOT NULL PRIMARY KEY,
  106.     NAME VARCHAR(70) NOT NULL,
  107.     PRICE NUMERIC(8,2) NOT NULL,
  108.     DESCR VARCHAR(2000) NULL
  109. )
  110.  
  111. CREATE TABLE ORDER_ITEMS
  112. (
  113.     ORDER_ID INT NOT NULL,
  114.     PRODUCT_ID INTEGER NOT NULL,
  115.     UNIT_PRICE NUMERIC(8,2) NOT NULL,
  116.     QUANTITY NUMERIC(8) NOT NULL,
  117.  
  118.     CONSTRAINT PK_ORDER_ITEMS PRIMARY KEY(ORDER_ID, PRODUCT_ID),
  119.     CONSTRAINT FK_OI_ORDERS FOREIGN KEY(ORDER_ID)
  120.         REFERENCES ORDERS(ORDER_ID) ON UPDATE CASCADE ON DELETE CASCADE,
  121.     CONSTRAINT FK_OI_PRODUCTS FOREIGN KEY(PRODUCT_ID)
  122.         REFERENCES PRODUCTS(PRODUCT_ID)
  123. )
RAW Paste Data Copied