SET QUOTED_IDENTIFIER OFF
go

use master
go

CREATE DATABASE wroxdb
COLLATE SQL_Latin1_General_CP1_CS_AS
go

ALTER DATABASE wroxdb
SET RECOVERY SIMPLE
go

use wroxdb
go

CREATE TABLE employees ( 
   emp_no           int           NOT NULL,
   name             varchar(30)   NOT NULL,
   commission_rate  float         NOT NULL
CONSTRAINT PK_employees PRIMARY KEY CLUSTERED (emp_no)   
)  
go

CREATE TABLE products ( 
   prod_id           int          NOT NULL,
   descr             varchar(50)  NOT NULL,
   price             money        NOT NULL,
   cost              money        NOT NULL
CONSTRAINT PK_products PRIMARY KEY CLUSTERED (prod_id)   
)  
go

CREATE TABLE orders ( 
   order_no          int IDENTITY(1000,1) NOT NULL,
   order_date        datetime     NOT NULL,
   emp_no            int          NOT NULL,
   customer          varchar(30)  NOT NULL,
   deliver           tinyint      NOT NULL,
   shipping_charge   money        NOT NULL,
   ship_date         datetime     NULL,
   adm_rev_no        int          NOT NULL,
   adm_time          datetime     NOT NULL
CONSTRAINT PK_orders PRIMARY KEY CLUSTERED (order_no)   
)  
go

CREATE TABLE lines ( 
   order_no          int          NOT NULL,
   line_no           smallint     NOT NULL,
   prod_id           int          NOT NULL,
   qty               int          NOT NULL,
   unit_price        money        NOT NULL,
   unit_cost         money        NOT NULL,
   commission        money        NOT NULL
CONSTRAINT PK_lines PRIMARY KEY CLUSTERED (order_no, line_no)   
)  
go

CREATE TABLE payments ( 
   order_no          int          NOT NULL,
   pmt_no            smallint     NOT NULL,
   pmt_type          char(1)      NOT NULL,
   amount            money        NOT NULL
CONSTRAINT PK_payments PRIMARY KEY CLUSTERED (order_no, pmt_no)   
)  
go

CREATE TABLE pmt_types ( 
   pmt_type          char(1)      NOT NULL,
   descr             varchar(15)  NOT NULL
CONSTRAINT PK_pmt_types PRIMARY KEY CLUSTERED (pmt_type)   
)  
go


-- ADD FOREIGN KEYS
ALTER TABLE payments
	ADD CONSTRAINT orders_payments_FK1 foreign key (
		order_no)
	 references orders (
		order_no)  
go

ALTER TABLE payments
	ADD CONSTRAINT pmt_types_payments_FK1 foreign key (
		pmt_type)
	 references pmt_types (
		pmt_type)  
go

ALTER TABLE lines
	ADD CONSTRAINT orders_lines_FK1 foreign key (
		order_no)
	 references orders (
		order_no)  
go

ALTER TABLE lines
	ADD CONSTRAINT products_lines_FK1 foreign key (
		prod_id)
	 references products (
		prod_id)  
go

ALTER TABLE orders
	ADD CONSTRAINT employees_orders_FK1 foreign key (
		emp_no)
	 references employees (
		emp_no)  
go

