create distinct type image as varchar(254);

create schema authorization swp

create table item 
(
 i_id		numeric(10,0),      -- Unique ID of Item
 i_title	varchar(60),	    -- Title of Item 
 i_a_id		numeric(10,0),	    -- Author ID of Item
 i_pub_date	date,		    -- Date of Release of the Product
 i_publisher	varchar(60),	    -- Publisher of Item
 i_subject	varchar(60),	    -- Subject of Book
 i_desc		lvarchar,	    -- Descriptor of Item
 i_related1	numeric(10,0),	    -- Unique Item ID (i_id)
 i_related2	numeric(10,0),	    -- Unique Item ID (i_id)
 i_related3	numeric(10,0),	    -- Unique Item ID (i_id)
 i_related4	numeric(10,0),	    -- Unique Item ID (i_id)
 i_related5	numeric(10,0),	    -- Unique Item ID (i_id)
 i_thumbnail	image,		    -- Thumbnail Image of Item or Pointer to Thumbnail Image 
 i_image	image,		    -- Image of Item or Pointer to Image 
 i_srp		numeric(15,2),	    -- Suggested Retail Price
 i_cost		numeric(15,2),	    -- Cost of Item
 i_avail	date,		    -- When Item is Available
 i_isbn		char(13),	    -- Product ISBN
 i_page		numeric(4,0),	    -- Number of Pages of Book 
 i_backing	varchar(15),	    -- Type of Book, Paper, Hard Back 
 i_dimensions  	varchar(25),	    -- Size of Book in Inches

primary key(i_id),
foreign key(i_a_id) references author(a_id)       
)

create table country
(
 co_id		numeric(4,0),	-- Unique Country ID
 co_name	varchar(50),	-- Name of Country
 co_exchange	numeric(8,2),	-- Exchange Rate to US Dollars
 co_currency	varchar(15),	-- Name of Currency 
  
primary key(co_id)
)

create table author 
(
 a_id      numeric(10,0),	-- Unique Author ID
 a_fname   varchar(20),		-- First Name of Author
 a_lname   varchar(20),		-- Last Name of Author
 a_mname   varchar(20),		-- Middle Name of Author
 a_dob     date,		-- Date of Birth of Author
 a_bio     lvarchar,		-- About the Author

primary key(a_id)
)

create table customer 
(
 c_id		numeric(10,0),		  -- Unique UD per Customer
 c_uname	varchar(23),		  -- Unique User Name for Customer 
 c_passwd	varchar(14),		  -- User Password for Customer
 c_fname	varchar(15),		  -- First Name of Customer
 c_lname	varchar(15),		  -- Last Name of Customer
 c_addr_id	numeric(10,0),		  -- Address ID of Customer
 c_phone	varchar(16),		  -- Phone number of customer
 c_email	varchar(50),		  -- For Sending Purchase Confirmations
 c_since	date,			  -- Customer Member Since
 c_last_visit	date,			  -- Time of Last Visit
 c_login	datetime year to second,  -- Start of Current Customer Session
 c_expiration   datetime year to second,  -- Current Customer Session Expiry
 c_discount	numeric(3,2),		  -- Percentage Discount for Customer
 c_balance	numeric(15,2),		  -- Balance of Customer
 c_ytd_pmt	numeric(15,2),		  -- YTD Payment of Customer
 c_birthdate	date,			  -- In Case Forget Password
 c_data		lvarchar,		  -- Misc Information

primary key(c_id),
foreign key(c_addr_id) references address(addr_id)       
)

create table orders 
(
 o_id		  numeric(10,0),		-- Unique ID per Order
 o_c_id		  numeric(10,0),		-- Customer ID of Order
 o_date		  datetime year to second,	-- Order Date
 o_sub_total	  numeric(15,2),		-- Subtotal of All Order-Line Items
 o_tax		  numeric(15,2),		-- Tax over the Subtotal
 o_total	  numeric(15,2),		-- Total for this Order
 o_ship_type	  varchar(10),			-- Method of Delivery
 o_ship_date	  datetime year to second,	-- Order Ship Date
 o_bill_addr_id   numeric(10,0),		-- Address ID to Bill
 o_ship_addr_id   numeric(10,0),		-- Address ID to Ship Order
 o_status         varchar(15),			-- Order Status

primary key(o_id),
foreign key(o_c_id) references customer(c_id),
foreign key(o_bill_addr_id) references address(addr_id),
foreign key(o_ship_addr_id) references address(addr_id)
)

create table order_line
(
 ol_id		numeric(10,0),	-- Unique Order Line Item ID
 ol_o_id	numeric(10,0),	-- Order ID of Order Line
 ol_i_id	numeric(10,0),	-- Unique Item ID (i_id)
 ol_qty		numeric(3,0),	-- Quantity of Item
 ol_discount	numeric(3,2),	-- Percentage Discount off of i_srp
 ol_comments    lvarchar,	-- Special Instructions

primary key(ol_id),
foreign key(ol_i_id) references item(i_id),
foreign key(ol_o_id) references orders(o_id)
)

create table cc_xacts
(
 cx_o_id        numeric(10,0),		  -- Unique Order ID (O_ID)
 cx_type	varchar(10),		  -- Credit Card Type
 cx_num		numeric(16,0),		  -- Credit Card Number
 cx_name	varchar(30),		  -- Name on Credit Card
 cx_expiry	date,			  -- Expiration Date of Credit Card
 cx_auth_id	char(15),		  -- Authorization for Transaction Amount
 cx_xact_amt	numeric(15,2),		  -- Amount for this Transaction
 cx_xact_date	datetime year to second,  -- Date of Authorization
 cx_co_id       numeric(4,0),		  -- Country Where Transaction Originated

primary key(cx_o_id),
foreign key(cx_o_id)  references orders(o_id),
foreign key(cx_co_id) references country(co_id)	
)

create table address 
(
addr_id		numeric(10,0),	-- Unique Address ID
addr_street1	varchar(40),	-- Street Address Line 1 
addr_street2	varchar(40),	-- Street Address Line 2
addr_city	varchar(30),	-- Name of City
addr_state	varchar(20),	-- Name of State
addr_zip	varchar(10),	-- Zip code or Postal Code
addr_co_id	numeric(4,0),	-- Unique ID of Country

primary key(addr_id),
foreign key(addr_co_id) references country(co_id)	
);