PBMiner

• Sources
• pb9webeasy
• DBInstall
• create_Tables.sql

 
CREATE TABLE contact (
  id int identity NOT NULL,
  last_name varchar(15) NOT NULL,
  first_name varchar(15) NOT NULL,
  title varchar(2) NOT NULL,
  street varchar(30) NOT NULL,
  city varchar(20) NOT NULL,
  state varchar(2) NOT NULL,
  zip varchar(5) NOT NULL,
  phone varchar(10),
  fax varchar(10),
CONSTRAINT contact_x PRIMARY KEY NONCLUSTERED (id)) ;
 
CREATE TABLE customer (
  id int identity NOT NULL, 
  fname varchar(15) NOT NULL,
  lname varchar(20) NOT NULL,
  address varchar(35) NOT NULL,
  city varchar(20) NOT NULL,
  state varchar(2) NOT NULL,
  zip varchar(10) NOT NULL,
  phone varchar(12) NOT NULL,
  company_name varchar(35),
CONSTRAINT customer_x PRIMARY KEY NONCLUSTERED (id)) ;
 
CREATE TABLE department (
  dept_id int NOT NULL,
  dept_name varchar(40) NOT NULL,
  dept_head_id int,
CONSTRAINT department_x PRIMARY KEY NONCLUSTERED (dept_id));
 
CREATE TABLE employee (
  emp_id int NOT NULL,
  manager_id int,
  emp_fname varchar(20) NOT NULL,
  emp_lname varchar(20) NOT NULL,
  dept_id int NOT NULL,
  street varchar(40) NOT NULL,
  city varchar(20) NOT NULL,
  state varchar(4) NOT NULL,
  zip_code varchar(9) NOT NULL,
  phone varchar(10),
  status varchar(1),
  ss_number varchar(11) NOT NULL,
  salary numeric(20,3) NOT NULL,
  start_date datetime NOT NULL,
  termination_date datetime,
  birth_date datetime,
  bene_health_ins varchar(1),
  bene_life_ins varchar(1),
  bene_day_care varchar(1),
  sex varchar(1),
CONSTRAINT employee_x PRIMARY KEY NONCLUSTERED (emp_id)) ;
 
CREATE TABLE fin_code (
  code varchar(2) NOT NULL,
  type varchar(10) NOT NULL,
  description varchar(50),
CONSTRAINT fin_code_x PRIMARY KEY NONCLUSTERED (code)) ;
 
CREATE TABLE fin_data (
  year varchar(4) NOT NULL,
  quarter varchar(2) NOT NULL,
  code varchar(2) NOT NULL,
  amount numeric(9,0),
CONSTRAINT fin_data_x PRIMARY KEY NONCLUSTERED (year, quarter, code)) ;
 
CREATE TABLE product (
  id int NOT NULL,
  name varchar(15) NOT NULL,
  description varchar(30) NOT NULL,
  prod_size varchar(18) NOT NULL,
  color varchar(6) NOT NULL,
  quantity int NOT NULL,
  unit_price numeric(15,2) NOT NULL,
  picture_name varchar(255),
CONSTRAINT product_x PRIMARY KEY NONCLUSTERED (id)) ;
 
CREATE TABLE sales_order (
  id int NOT NULL,
  cust_id int NOT NULL,
  order_date datetime NOT NULL,
  fin_code_id varchar(2),
  region varchar(7),
  sales_rep int NOT NULL,
CONSTRAINT sales_order_x PRIMARY KEY NONCLUSTERED (id)) ;
 
CREATE TABLE sales_order_items (
  id int NOT NULL,
  line_id smallint NOT NULL,
  prod_id int NOT NULL,
  quantity int NOT NULL,
  ship_date datetime NOT NULL,
CONSTRAINT sales_order_items_x PRIMARY KEY NONCLUSTERED (id, line_id)) ;
 
CREATE TABLE sales_regions (
  region varchar(7) NOT NULL,
CONSTRAINT sales_regions_x PRIMARY KEY NONCLUSTERED (region)) ;
 
CREATE TABLE states (
  state_id varchar(3) NOT NULL,
  state_name varchar(24) NOT NULL,
  state_capital varchar(24) NOT NULL,
  country varchar(3) NOT NULL,
  region varchar(7),
CONSTRAINT states_x PRIMARY KEY NONCLUSTERED (state_id)) ;