Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE DATABASE TRADECOMPANY
- USE TRADECOMPANY
- ALTER DATABASE CURRENT COLLATE CYRILLIC_GENERAL_CI_AI
- CREATE TABLE REGIONS
- (
- REGION_ID SMALLINT NOT NULL PRIMARY KEY IDENTITY(1,1),
- NAME VARCHAR(25) NOT NULL UNIQUE
- )
- CREATE TABLE COUNTRIES
- (
- COUNTRY_ID CHAR(2) NOT NULL,
- NAME VARCHAR(40) NOT NULL,
- REGION_ID SMALLINT NULL,
- CONSTRAINT PK_COUNTRIES PRIMARY KEY (COUNTRY_ID),
- CONSTRAINT FK_COUNTRIES_REGIONS FOREIGN KEY (REGION_ID)
- REFERENCES REGIONS(REGION_ID)
- )
- CREATE TABLE CUSTOMERS
- (
- CUSTOMER_ID NUMERIC(6) NOT NULL PRIMARY KEY,
- COUNTRY_ID CHAR(2) NOT NULL,
- FNAME VARCHAR(20) NOT NULL,
- LNAME VARCHAR(20) NOT NULL,
- ADDRESS TEXT NULL,
- EMAIL VARCHAR(30) NULL,
- GENDER CHAR(1) NULL DEFAULT 'M'
- CHECK(GENDER IS NULL OR GENDER IN('M','F'))
- )
- ALTER TABLE CUSTOMERS
- ADD CONSTRAINT FK_CUSTOMERS_COUNTRIES FOREIGN KEY (COUNTRY_ID)
- REFERENCES COUNTRIES(COUNTRY_ID)
- CREATE TABLE JOBS
- (
- JOB_ID VARCHAR(10) NOT NULL PRIMARY KEY,
- JOB_TITLE VARCHAR(35) NOT NULL,
- MIN_SALARY NUMERIC(6) NULL,
- MAX_SALARY NUMERIC(6) NULL
- )
- CREATE TABLE EMPLOYEES
- (
- EMPLOYEE_ID INT NOT NULL PRIMARY KEY,
- FNAME VARCHAR(20) NOT NULL,
- LNAME VARCHAR(25) NOT NULL,
- EMAIL VARCHAR(40) NOT NULL UNIQUE,
- PHONE VARCHAR(20) NULL,
- HIRE_DATE DATETIME NOT NULL,
- SALARY NUMERIC(8,2) NOT NULL CHECK(SALARY>0),
- JOB_ID VARCHAR(10) NOT NULL,
- MANAGER_ID INT NULL,
- DEPARTMENT_ID INT NULL,
- CONSTRAINT FK_EMP_JOB FOREIGN KEY (JOB_ID)
- REFERENCES JOBS(JOB_ID),
- CONSTRAINT FK_EMP_MNG FOREIGN KEY (MANAGER_ID)
- REFERENCES EMPLOYEES(EMPLOYEE_ID)
- )
- CREATE TABLE DEPARTMENTS
- (
- DEPARTMENT_ID INT NOT NULL PRIMARY KEY,
- NAME VARCHAR(30) NOT NULL,
- MANAGER_ID INT NULL,
- COUNTRY_ID CHAR(2) NOT NULL,
- CITY VARCHAR(30) NOT NULL,
- STATE VARCHAR(25) NULL,
- ADDRESS VARCHAR(40) NULL,
- POSTAL_CODE VARCHAR(12) NULL,
- CONSTRAINT FK_DEPT_COUNTRY FOREIGN KEY (COUNTRY_ID)
- REFERENCES COUNTRIES(COUNTRY_ID),
- CONSTRAINT FK_DEPT_MNG FOREIGN KEY (MANAGER_ID)
- REFERENCES EMPLOYEES(EMPLOYEE_ID)
- )
- ALTER TABLE EMPLOYEES
- ADD CONSTRAINT FK_EMP_DEPT FOREIGN KEY (DEPARTMENT_ID)
- REFERENCES DEPARTMENTS(DEPARTMENT_ID)
- CREATE TABLE PRODUCTS
- (
- PRODUCT_ID INT NOT NULL PRIMARY KEY,
- NAME VARCHAR(70) NOT NULL,
- PRICE NUMERIC(8,2) NOT NULL,
- DESCR VARCHAR(2000) NULL
- )
- CREATE TABLE ORDERS
- (
- ORDER_ID INT NOT NULL PRIMARY KEY,
- ORDER_DATE DATETIME NOT NULL,
- CUSTOMER_ID NUMERIC(6) NOT NULL FOREIGN KEY REFERENCES CUSTOMERS,
- EMPLOYEE_ID INT NOT NULL FOREIGN KEY REFERENCES EMPLOYEES,
- SHIP_ADDRESS VARCHAR(150) NULL
- --CONSTRAINT FK_O_CUST FOREIGN KEY (CUSTOMER_ID)
- --REFERENCES CUSTOMERS(CUSTOMER_ID),
- --CONSTRAINT FK_O_EMP FOREIGN KEY (EMPLOYEE_ID)
- --REFERENCES EMPLOYEES (EMPLOYEE_ID)
- )
- CREATE TABLE ORDER_ITEMS
- (
- ORDER_ID INT NOT NULL,
- PRODUCT_ID INTEGER NOT NULL,
- PRIMARY KEY(ORDER_ID , PRODUCT_ID),
- UNIT_PRICE NUMERIC(8,2) NOT NULL,
- QUANTITY NUMERIC(8) NOT NULL,
- CONSTRAINT FK_OI_PRODUCTS FOREIGN KEY (PRODUCT_ID)
- REFERENCES PRODUCTS(PRODUCT_ID),
- CONSTRAINT FK_OI_ORDERS FOREIGN KEY (ORDER_ID)
- REFERENCES ORDERS (ORDER_ID)
- ON UPDATE CASCADE ON DELETE CASCADE
- )
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement