PCT.sql

create database PCT;

use PCT;

create table REGIONS
(
    REGION_ID smallint not null,
	NAME varchar(25) not null,
	constraint PK_REGIONS primary key (REGION_ID)
)

create table COUNTRIES
(
	COUNTRY_ID char(2) not null,
	NAME varchar(40) not null,
	REGION_ID smallint not 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,
  COUNTRY_ID char(2) not null,
  FNAME varchar(20) not null,
  LNAME varchar(20) not null,
  ADDRESS text null,
  EMAIL varchar(30),
  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_CUSTOMERS_COUNTRIES foreign key (COUNTRY_ID) references COUNTRIES(COUNTRY_ID);

create table ORDERS
(
	ORDER_ID int not null,
	ORDER_DATE datetime not null,
	CUSTOMER_ID numeric(6) not null,
	EMPLOYEE_ID int not null,
	SHIP_ADDRESS varchar(150) null,
	constraint PK_ORDERS primary key (ORDER_ID),
	constraint FK_ORDERS_CUSTOMERS foreign key (CUSTOMER_ID) references CUSTOMERS(CUSTOMER_ID)
)

alter table ORDERS
add constraint FK_ORDERS_EMPLOYEES foreign key (EMPLOYEE_ID) references EMPLOYEES(EMPLOYEE_ID)

create table PRODUCTS
(
	PRODUCT_ID int not null,
	NAME varchar(50) not null,
	PRICE numeric(8,2) not null,
	DESCR varchar(2000) null,
	constraint PK_PRODUCTS primary key (PRODUCT_ID),
)

create table ORDER_ITEMS
(
	ORDER_ID int not null,
	PRODUCT_ID integer not null,
	UNIT_PRICE numeric(8,2) not null,
	QUANTITY numeric(8) not null,
	constraint PK_ORDER_ITEMS primary key(ORDER_ID, PRODUCT_ID),
	constraint FK_ORDERS_PRODUCTS foreign key (PRODUCT_ID) references PRODUCTS(PRODUCT_ID),
	constraint FK_ORDERITEMS_ORDERS foreign key (ORDER_ID) references ORDERS(ORDER_ID) on delete cascade
)

create table JOBS
(
	JOB_ID varchar(10) not null,
	JOB_TITLE varchar(35) not null,
	MIN_SALARY numeric(6) null,
	MAX_SALARY numeric(6) null,
	constraint PK_JOBS primary key (JOB_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_DEPARTMENTS primary key (DEPARTMENT_ID)
)

alter table DEPARTMENTS
add constraint FK_DEPT_COUNTRIES foreign key (COUNTRY_ID) references COUNTRIES(COUNTRY_ID);

create table EMPLOYEES
(
	EMPLOYEE_ID int not null,
	FNAME varchar(20) not null,
	LNAME varchar(25) not null,
	EMAIL varchar(25) not null,
	PHONE varchar(20) null,
	HIRE_DATE datetime  not  null,
	SALARY numeric(8,2) not null constraint MIN_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 UQ_EMAIL unique(EMAIL),
	constraint FK_EMP_JOBS foreign key (JOB_ID) references JOBS(JOB_ID),
	constraint FK_EMP_DEPT foreign key (DEPARTMENT_ID) references DEPARTMENTS(DEPARTMENT_ID),
	-- a foreign key that references a column within the same table
	constraint FK_EMP_MNG foreign key (MANAGER_ID) references EMPLOYEES(EMPLOYEE_ID)
)

alter table DEPARTMENTS add constraint FK_DEPT_MANAGER foreign key (MANAGER_ID) references EMPLOYEES(EMPLOYEE_ID);