Advertisement
S_Madanska

tradecompany

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