Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- create database tradecompany -- създава база от данни
- -- use tradecompany -- достъпва създадената база данни
- -- (но в нашия случай е създадена предварително - индивидуално с факултетен номер)
- -- т.е. минаваме дректно към създаване на създаване на таблици
- -- execute всяка заявка поотделно
- -----------------------------------------------------------------------------------
- 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_COUNTRY 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,
- 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'
- CONSTRAINT CUST_GENDER CHECK (GENDER IS NULL OR (GENDER IN ('M','F'))),
- CONSTRAINT PK_CUSTOMERS PRIMARY KEY (CUSTOMER_ID)
- )
- -----------------------------------------------------------------------------------
- ALTER TABLE CUSTOMERS
- ADD CONSTRAINT FK_CUSTOMER_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),
- MAX_SALARY numeric(6)
- )
- -----------------------------------------------------------------------------------
- CREATE TABLE EMPLOYEES
- (
- EMPLOYEE_ID INT NOT NULL,
- FNAME VARCHAR(20) NOT NULL,
- LNAME VARCHAR(25) NOT NULL,
- EMAIL VARCHAR(40) NOT NULL,
- PHONE VARCHAR(20) NULL,
- HIRE_DATE DATETIME NOT NULL,
- SALARY NUMERIC(8,2) NOT NULL
- CONSTRAINT SALARY_CHECK CHECK (SALARY > 0),
- JOB_ID VARCHAR(10) NOT NULL,
- MANAGER_ID INT NULL,
- DEPARTMENT_ID INT NULL,
- CONSTRAINT PK_EMPLOYEES PRIMARY KEY (EMPLOYEE_ID),
- CONSTRAINT UK_EMAIL UNIQUE (EMAIL),
- CONSTRAINT FK_EMPLOYEE_JOBS FOREIGN KEY (JOB_ID) REFERENCES JOBS (JOB_ID),
- CONSTRAINT FK_EMPLOYEE_MANAGERS FOREIGN KEY (MANAGER_ID)
- REFERENCES EMPLOYEES (EMPLOYEE_ID)
- )
- -----------------------------------------------------------------------------------
- CREATE TABLE DEPARTMENTS
- (
- DEPARTMENT_ID INT NOT NULL,
- 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 PK_DEPT PRIMARY KEY (DEPARTMENT_ID),
- CONSTRAINT FK_DEPT_MGR FOREIGN KEY (MANAGER_ID)
- REFERENCES EMPLOYEES (EMPLOYEE_ID),
- CONSTRAINT FK_DEPT_COUNTRIES FOREIGN KEY (COUNTRY_ID)
- REFERENCES COUNTRIES (COUNTRY_ID)
- )
- -----------------------------------------------------------------------------------
- ALTER TABLE EMPLOYEES
- ADD CONSTRAINT FK_EMPLOYEE_DEPARTMENTS FOREIGN KEY (DEPARTMENT_ID)
- REFERENCES DEPARTMENTS
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement