Advertisement
S_Madanska

tradecompany_week2

Oct 6th, 2021
1,167
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- create database tradecompany       -- създава база от данни
  2. -- use tradecompany                   -- достъпва създадената база данни
  3. -- (но в нашия случай е създадена предварително - индивидуално с факултетен номер)
  4. --  т.е. минаваме дректно към създаване на създаване на таблици
  5. -- execute всяка заявка поотделно
  6. -----------------------------------------------------------------------------------
  7. CREATE TABLE REGIONS
  8. (
  9.      REGION_ID SMALLINT NOT NULL PRIMARY KEY IDENTITY(1, 1),
  10.      NAME VARCHAR(25) NOT NULL UNIQUE
  11. )
  12. -----------------------------------------------------------------------------------
  13. CREATE TABLE COUNTRIES
  14. (
  15.      COUNTRY_ID CHAR(2) NOT NULL,
  16.      NAME VARCHAR(40) NOT NULL,
  17.      REGION_ID SMALLINT NULL,
  18.      CONSTRAINT PK_COUNTRY PRIMARY KEY (COUNTRY_ID),
  19.      CONSTRAINT FK_COUNTRIES_REGIONS FOREIGN KEY (REGION_ID)
  20.      REFERENCES REGIONS (REGION_ID)
  21. )
  22. -----------------------------------------------------------------------------------
  23. CREATE TABLE CUSTOMERS
  24. (
  25.      CUSTOMER_ID NUMERIC(6) NOT NULL,
  26.      COUNTRY_ID CHAR(2) NOT NULL,
  27.      FNAME VARCHAR(20) NOT NULL,
  28.      LNAME VARCHAR(20) NOT NULL,
  29.      ADDRESS TEXT NULL,
  30.      EMAIL VARCHAR(30) NULL,
  31.      GENDER CHAR(1) NULL DEFAULT 'M'
  32.      CONSTRAINT CUST_GENDER CHECK (GENDER IS NULL OR (GENDER IN ('M','F'))),
  33.      CONSTRAINT PK_CUSTOMERS PRIMARY KEY (CUSTOMER_ID)
  34. )
  35. -----------------------------------------------------------------------------------
  36. ALTER TABLE CUSTOMERS
  37.  ADD CONSTRAINT FK_CUSTOMER_COUNTRIES FOREIGN KEY (COUNTRY_ID)
  38.  REFERENCES COUNTRIES (COUNTRY_ID)
  39. -----------------------------------------------------------------------------------
  40. create table JOBS
  41. (
  42.     JOB_ID varchar(10) not null primary key,        
  43.     JOB_TITLE varchar(35) not null,    
  44.     MIN_SALARY numeric(6),
  45.     MAX_SALARY numeric(6)
  46. )
  47. -----------------------------------------------------------------------------------
  48. CREATE TABLE EMPLOYEES
  49. (
  50.      EMPLOYEE_ID INT NOT NULL,
  51.      FNAME VARCHAR(20) NOT NULL,
  52.      LNAME VARCHAR(25) NOT NULL,
  53.      EMAIL VARCHAR(40) NOT NULL,
  54.      PHONE VARCHAR(20) NULL,
  55.      HIRE_DATE DATETIME NOT NULL,
  56.      SALARY NUMERIC(8,2) NOT NULL
  57.      CONSTRAINT SALARY_CHECK CHECK (SALARY > 0),
  58.      JOB_ID VARCHAR(10) NOT NULL,
  59.      MANAGER_ID INT NULL,
  60.      DEPARTMENT_ID INT NULL,
  61.      CONSTRAINT PK_EMPLOYEES PRIMARY KEY (EMPLOYEE_ID),
  62.      CONSTRAINT UK_EMAIL UNIQUE (EMAIL),
  63.      CONSTRAINT FK_EMPLOYEE_JOBS FOREIGN KEY (JOB_ID) REFERENCES JOBS (JOB_ID),
  64.      CONSTRAINT FK_EMPLOYEE_MANAGERS FOREIGN KEY (MANAGER_ID)
  65.      REFERENCES EMPLOYEES (EMPLOYEE_ID)
  66. )
  67. -----------------------------------------------------------------------------------
  68. CREATE TABLE DEPARTMENTS
  69. (
  70.      DEPARTMENT_ID INT NOT NULL,
  71.      NAME VARCHAR(30) NOT NULL,
  72.      MANAGER_ID INT NULL,
  73.      COUNTRY_ID CHAR(2) NOT NULL,
  74.      CITY VARCHAR(30) NOT NULL,
  75.      STATE VARCHAR(25) NULL,
  76.      ADDRESS VARCHAR(40) NULL,
  77.      POSTAL_CODE VARCHAR(12) NULL,
  78.      CONSTRAINT PK_DEPT PRIMARY KEY (DEPARTMENT_ID),
  79.      CONSTRAINT FK_DEPT_MGR FOREIGN KEY (MANAGER_ID)
  80.      REFERENCES EMPLOYEES (EMPLOYEE_ID),
  81.      CONSTRAINT FK_DEPT_COUNTRIES FOREIGN KEY (COUNTRY_ID)
  82.      REFERENCES COUNTRIES (COUNTRY_ID)
  83. )
  84. -----------------------------------------------------------------------------------
  85. ALTER TABLE EMPLOYEES
  86. ADD CONSTRAINT FK_EMPLOYEE_DEPARTMENTS FOREIGN KEY (DEPARTMENT_ID)
  87. REFERENCES DEPARTMENTS
Advertisement
RAW Paste Data Copied
Advertisement