CREATE TABLE customers
    ( cust_id                NUMBER       
    , cust_first_name        VARCHAR2(20) 
        CONSTRAINT           customer_fname_nn NOT NULL
    , cust_last_name         VARCHAR2(40) 
        CONSTRAINT           customer_lname_nn NOT NULL
    , cust_gender	     CHAR(1)
    , cust_year_of_birth     NUMBER(4)
    , cust_marital_status    VARCHAR2(20)
    , cust_street_address    VARCHAR2(40) 
        CONSTRAINT           customer_st_addr_nn NOT NULL
    , cust_postal_code       VARCHAR2(10) 
        CONSTRAINT           customer_pcode_nn NOT NULL
    , cust_city              VARCHAR2(30) 
        CONSTRAINT           customer_city_nn NOT NULL
    , cust_state_province    VARCHAR2(40)
    , country_id             CHAR(2) 
        CONSTRAINT           customer_country_id_nn NOT NULL
    , cust_main_phone_number VARCHAR2(25)
    , cust_income_level      VARCHAR2(30)
    , cust_credit_limit      NUMBER
    , cust_email             VARCHAR2(30)
    );


CREATE TABLE products
    ( prod_id              NUMBER(6)      
    , prod_name            VARCHAR2(50)   
	CONSTRAINT 	   products_prod_name_nn NOT NULL
    , prod_desc            VARCHAR2(4000) 
	CONSTRAINT 	   products_prod_desc_nn NOT NULL
    , prod_subcategory     VARCHAR2(50)  
	CONSTRAINT	   products_prod_subcat_nn NOT NULL
    , prod_subcat_desc     VARCHAR2(2000)  
	CONSTRAINT	   products_prod_subcatd_nn NOT NULL
    , prod_category        VARCHAR2(50)  
	CONSTRAINT	   products_prod_cat_nn NOT NULL
    , prod_cat_desc        VARCHAR2(2000)  
	CONSTRAINT	   products_prod_catd_nn NOT NULL
    , prod_weight_class    NUMBER(2)
    , prod_unit_of_measure VARCHAR2(20)
    , prod_pack_size       VARCHAR2(30)
    , supplier_id          NUMBER(6)
    , prod_status          VARCHAR2(20)
	CONSTRAINT	   products_prod_stat_nn NOT NULL
    , prod_list_price      NUMBER(8,2) 
	CONSTRAINT	   products_prod_list_price_nn NOT NULL
    , prod_min_price       NUMBER(8,2) 
	CONSTRAINT	   products_prod_min_price_nn NOT NULL
     );

CREATE TABLE times
    ( 
      time_id                 DATE
    , day_name                VARCHAR2(9)  
        CONSTRAINT 	      tim_day_name_nn           NOT NULL   
    , day_number_in_month     NUMBER(2)
        CONSTRAINT 	      tim_day_in_month_nn       NOT NULL    
    , calendar_month_number   NUMBER(2)
        CONSTRAINT 	      tim_cal_month_number_nn   NOT NULL    
    , calendar_year           NUMBER(4)  
        CONSTRAINT            tim_cal_year_nn           NOT NULL 
   
    );

CREATE TABLE sales
    ( prod_id        NUMBER(6)
        CONSTRAINT   sales_product_nn     NOT NULL
    , cust_id        NUMBER
        CONSTRAINT   sales_customer_nn    NOT NULL
    , time_id        DATE
        CONSTRAINT   sales_time_nn        NOT NULL  
    , quantity_sold  NUMBER(3)
        CONSTRAINT   sales_quantity_nn    NOT NULL
    , amount_sold         NUMBER(10,2)
        CONSTRAINT   sales_amount_nn      NOT NULL
    );

 