
IF EXISTS
  (SELECT * FROM sys.databases WHERE name = 'SodaDB')
DROP DATABASE SodaDB
GO

CREATE DATABASE SodaDB
GO

USE SodaDB
GO

CREATE TABLE product (
 product_id INT PRIMARY KEY,
 description NVARCHAR(100)
)
GO

CREATE TABLE [dbo].[inventory] (
 product_id INT PRIMARY KEY,
 units_in_stock INT
)

ALTER TABLE [dbo].[inventory]  
	WITH CHECK ADD  CONSTRAINT [FK_inventory_product] 
	FOREIGN KEY([product_id])
	REFERENCES [dbo].[product] ([product_id])

CREATE TABLE customer (
 customer_id BIGINT PRIMARY KEY,
 customer_name NVARCHAR(100)
)

CREATE TABLE orders (
 order_id BIGINT IDENTITY PRIMARY KEY,
 customer_id BIGINT,
 order_date DATETIME
)

ALTER TABLE [dbo].[orders]  
	WITH CHECK ADD  CONSTRAINT [FK_orders_customer] 
	FOREIGN KEY([customer_id])
	REFERENCES [dbo].[customer] ([customer_id])
GO

CREATE TABLE order_line_item (
 order_id BIGINT PRIMARY KEY,
 product_id INT,
 quantity INT,
 unit_price DECIMAL(7,2),
 in_stock BIT
)

ALTER TABLE [dbo].[order_line_item]  
	WITH CHECK ADD  CONSTRAINT [FK_order_line_item_orders] 
	FOREIGN KEY([order_id])
	REFERENCES [dbo].[orders] ([order_id])
GO

ALTER TABLE [dbo].[order_line_item]  
	WITH CHECK ADD  CONSTRAINT [FK_order_line_item_product] 
	FOREIGN KEY([product_id])
	REFERENCES [dbo].[product] ([product_id])
GO

CREATE TABLE order_form (
 order_id BIGINT PRIMARY KEY,
 order_form_xml XML
)

ALTER TABLE [dbo].[order_form]  
	WITH CHECK ADD  CONSTRAINT [FK_order_form_orders] 
	FOREIGN KEY([order_id])
	REFERENCES [dbo].[orders] ([order_id])
GO

CREATE TABLE invrequest_form (
 inv_req_number UNIQUEIDENTIFIER PRIMARY KEY,
 order_id BIGINT,
 inventory_req XML
)

ALTER TABLE [dbo].[invrequest_form]  
	WITH CHECK ADD  CONSTRAINT [FK_invrequest_form_order_form] 
	FOREIGN KEY([order_id])
	REFERENCES [dbo].[order_form] ([order_id])
GO

INSERT product VALUES(134,  'Cola')
INSERT product VALUES(285,  'Drinking Straw')
INSERT product VALUES(1040, 'Sofa')
INSERT product VALUES(1500, 'Comfy Chair')
INSERT product VALUES(1580, 'Desk Chair')
INSERT product VALUES(2100, 'Desk Lamp')
INSERT product VALUES(2250, 'File Cabinet')
INSERT product VALUES(2253, 'Bookcase')
GO

INSERT inventory VALUES(134, 10000)
INSERT inventory VALUES(285, 20000)
INSERT inventory VALUES(1040, 1000)
INSERT inventory VALUES(1500, 2000)
INSERT inventory VALUES(1580, 2500)
INSERT inventory VALUES(2100, 1000)
INSERT inventory VALUES(2250, 750)
INSERT inventory VALUES(2253, 3000)
GO

INSERT customer VALUES(347, 'Acme Company')
INSERT customer VALUES(358, 'Bob''s Books')
INSERT customer VALUES(429, 'John Smith')
INSERT customer VALUES(445, 'Kevin Woo')
INSERT customer VALUES(501, 'Jane Smith')
GO