drop TABLE PUBLIC.`account`;
drop TABLE PUBLIC.`agg_c_10_sales_fact_1997`;
drop TABLE PUBLIC.`agg_c_14_sales_fact_1997`;
drop TABLE PUBLIC.`agg_c_special_sales_fact_1997`;
drop TABLE PUBLIC.`agg_g_ms_pcat_sales_fact_1997`;
drop TABLE PUBLIC.`agg_l_03_sales_fact_1997`;
drop TABLE PUBLIC.`agg_l_04_sales_fact_1997`;
drop TABLE PUBLIC.`agg_l_05_sales_fact_1997`;
drop TABLE PUBLIC.`agg_lc_06_sales_fact_1997`;
drop TABLE PUBLIC.`agg_lc_100_sales_fact_1997`;
drop TABLE PUBLIC.`agg_ll_01_sales_fact_1997`;
drop TABLE PUBLIC.`agg_pl_01_sales_fact_1997`;
drop TABLE PUBLIC.`category`;
drop TABLE PUBLIC.`currency`;
drop TABLE PUBLIC.`customer`;
drop TABLE PUBLIC.`days`;
drop TABLE PUBLIC.`department`;
drop TABLE PUBLIC.`employee`;
drop TABLE PUBLIC.`employee_closure`;
drop TABLE PUBLIC.`expense_fact`;
drop TABLE PUBLIC.`inventory_fact_1997`;
drop TABLE PUBLIC.`inventory_fact_1998`;
drop TABLE PUBLIC.`position`;
drop TABLE PUBLIC.`product`;
drop TABLE PUBLIC.`product_class`;
drop TABLE PUBLIC.`promotion`;
drop TABLE PUBLIC.`region`;
drop TABLE PUBLIC.`reserve_employee`;
drop TABLE PUBLIC.`salary`;
drop TABLE PUBLIC.`sales_fact_1997`;
drop TABLE PUBLIC.`sales_fact_1998`;
drop TABLE PUBLIC.`sales_fact_dec_1998`;
drop TABLE PUBLIC.`store`;
drop TABLE PUBLIC.`store_ragged`;
drop TABLE PUBLIC.`time_by_day`;
drop TABLE PUBLIC.`warehouse`;
drop TABLE PUBLIC.`warehouse_class`;
CREATE TABLE PUBLIC.`account`(
`account_id` INT NOT NULL,
`account_parent` INT DEFAULT NULL,
`account_description` VARCHAR(30) DEFAULT NULL,
`account_type` VARCHAR(30) NOT NULL,
`account_rollup` VARCHAR(30) NOT NULL,
`Custom_Members` VARCHAR(255) DEFAULT NULL
);
CREATE TABLE PUBLIC.`agg_c_10_sales_fact_1997`(
`month_of_year` SMALLINT NOT NULL,
`quarter` VARCHAR(30) NOT NULL,
`the_year` SMALLINT NOT NULL,
`store_sales` DECIMAL(10, 4) NOT NULL,
`store_cost` DECIMAL(10, 4) NOT NULL,
`unit_sales` DECIMAL(10, 4) NOT NULL,
`customer_count` INT NOT NULL,
`fact_count` INT NOT NULL
);
CREATE TABLE PUBLIC.`agg_c_14_sales_fact_1997`(
`product_id` INT NOT NULL ,
`customer_id` INT NOT NULL ,
`store_id` INT NOT NULL ,
`promotion_id` INT NOT NULL ,
`month_of_year` SMALLINT NOT NULL ,
`quarter` VARCHAR(30) NOT NULL ,
`the_year` SMALLINT NOT NULL ,
`store_sales` DECIMAL(10, 4) NOT NULL ,
`store_cost` DECIMAL(10, 4) NOT NULL ,
`unit_sales` DECIMAL(10, 4) NOT NULL ,
`fact_count` INT NOT NULL
);
CREATE TABLE PUBLIC.`agg_c_special_sales_fact_1997`(
`product_id` INT NOT NULL ,
`promotion_id` INT NOT NULL ,
`customer_id` INT NOT NULL ,
`store_id` INT NOT NULL ,
`time_month` SMALLINT NOT NULL ,
`time_quarter` VARCHAR(30) NOT NULL ,
`time_year` SMALLINT NOT NULL ,
`store_sales_sum` DECIMAL(10, 4) NOT NULL ,
`store_cost_sum` DECIMAL(10, 4) NOT NULL ,
`unit_sales_sum` DECIMAL(10, 4) NOT NULL ,
`fact_count` INT NOT NULL
);
CREATE TABLE PUBLIC.`agg_g_ms_pcat_sales_fact_1997`(
`gender` VARCHAR(30) NOT NULL ,
`marital_status` VARCHAR(30) NOT NULL ,
`product_family` VARCHAR(30) DEFAULT NULL ,
`product_department` VARCHAR(30) DEFAULT NULL ,
`product_category` VARCHAR(30) DEFAULT NULL ,
`month_of_year` SMALLINT NOT NULL ,
`quarter` VARCHAR(30) NOT NULL ,
`the_year` SMALLINT NOT NULL ,
`store_sales` DECIMAL(10, 4) NOT NULL ,
`store_cost` DECIMAL(10, 4) NOT NULL ,
`unit_sales` DECIMAL(10, 4) NOT NULL ,
`customer_count` INT NOT NULL ,
`fact_count` INT NOT NULL
);
CREATE TABLE PUBLIC.`agg_l_03_sales_fact_1997`(
`time_id` INT NOT NULL ,
`customer_id` INT NOT NULL ,
`store_sales` DECIMAL(10, 4) NOT NULL ,
`store_cost` DECIMAL(10, 4) NOT NULL ,
`unit_sales` DECIMAL(10, 4) NOT NULL ,
`fact_count` INT NOT NULL
);
CREATE TABLE PUBLIC.`agg_l_04_sales_fact_1997`(
`time_id` INT NOT NULL,
`store_sales` DECIMAL(10, 4) NOT NULL,
`store_cost` DECIMAL(10, 4) NOT NULL,
`unit_sales` DECIMAL(10, 4) NOT NULL,
`customer_count` INT NOT NULL,
`fact_count` INT NOT NULL
);
CREATE TABLE PUBLIC.`agg_l_05_sales_fact_1997`(
`product_id` INT NOT NULL ,
`customer_id` INT NOT NULL ,
`promotion_id` INT NOT NULL ,
`store_id` INT NOT NULL ,
`store_sales` DECIMAL(10, 4) NOT NULL ,
`store_cost` DECIMAL(10, 4) NOT NULL ,
`unit_sales` DECIMAL(10, 4) NOT NULL ,
`fact_count` INT NOT NULL
);
CREATE TABLE PUBLIC.`agg_lc_06_sales_fact_1997`(
`time_id` INT NOT NULL ,
`city` VARCHAR(30) NOT NULL ,
`state_province` VARCHAR(30) NOT NULL ,
`country` VARCHAR(30) NOT NULL ,
`store_sales` DECIMAL(10, 4) NOT NULL ,
`store_cost` DECIMAL(10, 4) NOT NULL ,
`unit_sales` DECIMAL(10, 4) NOT NULL ,
`fact_count` INT NOT NULL
);
CREATE TABLE PUBLIC.`agg_lc_100_sales_fact_1997`(
`product_id` INT NOT NULL ,
`customer_id` INT NOT NULL ,
`quarter` VARCHAR(30) NOT NULL ,
`the_year` SMALLINT NOT NULL ,
`store_sales` DECIMAL(10, 4) NOT NULL ,
`store_cost` DECIMAL(10, 4) NOT NULL ,
`unit_sales` DECIMAL(10, 4) NOT NULL ,
`fact_count` INT NOT NULL
);
CREATE TABLE PUBLIC.`agg_ll_01_sales_fact_1997`(
`product_id` INT NOT NULL ,
`time_id` INT NOT NULL ,
`customer_id` INT NOT NULL ,
`store_sales` DECIMAL(10, 4) NOT NULL ,
`store_cost` DECIMAL(10, 4) NOT NULL ,
`unit_sales` DECIMAL(10, 4) NOT NULL ,
`fact_count` INT NOT NULL
);
CREATE TABLE PUBLIC.`agg_pl_01_sales_fact_1997`(
`product_id` INT NOT NULL ,
`time_id` INT NOT NULL ,
`customer_id` INT NOT NULL ,
`store_sales_sum` DECIMAL(10, 4) NOT NULL ,
`store_cost_sum` DECIMAL(10, 4) NOT NULL ,
`unit_sales_sum` DECIMAL(10, 4) NOT NULL ,
`fact_count` INT NOT NULL
);
CREATE TABLE PUBLIC.`category`(
`category_id` VARCHAR(30) NOT NULL,
`category_parent` VARCHAR(30) DEFAULT NULL,
`category_description` VARCHAR(30) NOT NULL,
`category_rollup` VARCHAR(30) DEFAULT NULL
);
CREATE TABLE PUBLIC.`currency`(
`currency_id` INT NOT NULL,
`date` DATE NOT NULL,
`currency` VARCHAR(30) NOT NULL,
`conversion_ratio` DECIMAL(10, 4) NOT NULL
);
CREATE TABLE PUBLIC.`customer`(
`customer_id` INT NOT NULL ,
`account_num` BIGINT NOT NULL ,
`lname` VARCHAR(30) NOT NULL ,
`fname` VARCHAR(30) NOT NULL ,
`mi` VARCHAR(30) DEFAULT NULL ,
`address1` VARCHAR(30) DEFAULT NULL ,
`address2` VARCHAR(30) DEFAULT NULL ,
`address3` VARCHAR(30) DEFAULT NULL ,
`address4` VARCHAR(30) DEFAULT NULL ,
`city` VARCHAR(30) DEFAULT NULL ,
`state_province` VARCHAR(30) DEFAULT NULL ,
`postal_code` VARCHAR(30) NOT NULL ,
`country` VARCHAR(30) NOT NULL ,
`customer_region_id` INT NOT NULL ,
`phone1` VARCHAR(30) NOT NULL ,
`phone2` VARCHAR(30) NOT NULL ,
`birthdate` DATE NOT NULL ,
`marital_status` VARCHAR(30) NOT NULL ,
`yearly_income` VARCHAR(30) NOT NULL ,
`gender` VARCHAR(30) NOT NULL ,
`total_children` SMALLINT NOT NULL ,
`num_children_at_home` SMALLINT NOT NULL ,
`education` VARCHAR(30) NOT NULL ,
`date_accnt_opened` DATE NOT NULL ,
`member_card` VARCHAR(30) DEFAULT NULL ,
`occupation` VARCHAR(30) DEFAULT NULL ,
`houseowner` VARCHAR(30) DEFAULT NULL ,
`num_cars_owned` INT DEFAULT NULL ,
`fullname` VARCHAR(60) NOT NULL
);
CREATE TABLE PUBLIC.`days`(
`day` INT NOT NULL,
`week_day` VARCHAR(30) NOT NULL
);
CREATE TABLE PUBLIC.`department`(
`department_id` INT NOT NULL,
`department_description` VARCHAR(30) NOT NULL
);
CREATE TABLE PUBLIC.`employee`(
`employee_id` INT NOT NULL,
`full_name` VARCHAR(30) NOT NULL,
`first_name` VARCHAR(30) NOT NULL,
`last_name` VARCHAR(30) NOT NULL,
`position_id` INT DEFAULT NULL,
`position_title` VARCHAR(30) DEFAULT NULL,
`store_id` INT NOT NULL,
`department_id` INT NOT NULL,
`birth_date` DATE NOT NULL,
`hire_date` DATETIME DEFAULT NULL,
`end_date` DATETIME DEFAULT NULL,
`salary` DECIMAL(10, 4) NOT NULL,
`supervisor_id` INT DEFAULT NULL,
`education_level` VARCHAR(30) NOT NULL,
`marital_status` VARCHAR(30) NOT NULL,
`gender` VARCHAR(30) NOT NULL,
`management_role` VARCHAR(30) DEFAULT NULL
);
CREATE TABLE PUBLIC.`employee_closure`(
`employee_id` INT NOT NULL ,
`supervisor_id` INT NOT NULL ,
`distance` INT DEFAULT NULL
);
CREATE TABLE PUBLIC.`expense_fact`(
`store_id` INT NOT NULL ,
`account_id` INT NOT NULL ,
`exp_date` DATETIME NOT NULL ,
`time_id` INT NOT NULL ,
`category_id` VARCHAR(30) NOT NULL ,
`currency_id` INT NOT NULL ,
`amount` DECIMAL(10, 4) NOT NULL
);
CREATE TABLE PUBLIC.`inventory_fact_1997`(
`product_id` INT NOT NULL ,
`time_id` INT DEFAULT NULL ,
`warehouse_id` INT DEFAULT NULL ,
`store_id` INT DEFAULT NULL ,
`units_ordered` INT DEFAULT NULL ,
`units_shipped` INT DEFAULT NULL ,
`warehouse_sales` DECIMAL(10, 4) DEFAULT NULL ,
`warehouse_cost` DECIMAL(10, 4) DEFAULT NULL ,
`supply_time` SMALLINT DEFAULT NULL ,
`store_invoice` DECIMAL(10, 4) DEFAULT NULL
);
CREATE TABLE PUBLIC.`inventory_fact_1998`(
`product_id` INT NOT NULL ,
`time_id` INT DEFAULT NULL ,
`warehouse_id` INT DEFAULT NULL ,
`store_id` INT DEFAULT NULL ,
`units_ordered` INT DEFAULT NULL ,
`units_shipped` INT DEFAULT NULL ,
`warehouse_sales` DECIMAL(10, 4) DEFAULT NULL ,
`warehouse_cost` DECIMAL(10, 4) DEFAULT NULL ,
`supply_time` SMALLINT DEFAULT NULL ,
`store_invoice` DECIMAL(10, 4) DEFAULT NULL
);
CREATE TABLE PUBLIC.`position`(
`position_id` INT NOT NULL,
`position_title` VARCHAR(30) NOT NULL,
`pay_type` VARCHAR(30) NOT NULL,
`min_scale` DECIMAL(10, 4) NOT NULL,
`max_scale` DECIMAL(10, 4) NOT NULL,
`management_role` VARCHAR(30) NOT NULL
);
CREATE TABLE PUBLIC.`product`(
`product_class_id` INT NOT NULL,
`product_id` INT NOT NULL,
`brand_name` VARCHAR(60) DEFAULT NULL,
`product_name` VARCHAR(60) NOT NULL,
SKU BIGINT NOT NULL,
SRP DECIMAL(10, 4) DEFAULT NULL,
`gross_weight` DOUBLE DEFAULT NULL,
`net_weight` DOUBLE DEFAULT NULL,
`recyclable_package` TINYINT DEFAULT NULL,
`low_fat` TINYINT DEFAULT NULL,
`units_per_case` SMALLINT DEFAULT NULL,
`cases_per_pallet` SMALLINT DEFAULT NULL,
`shelf_width` DOUBLE DEFAULT NULL,
`shelf_height` DOUBLE DEFAULT NULL,
`shelf_depth` DOUBLE DEFAULT NULL
);
CREATE TABLE PUBLIC.`product_class`(
`product_class_id` INT NOT NULL,
`product_subcategory` VARCHAR(30) DEFAULT NULL,
`product_category` VARCHAR(30) DEFAULT NULL,
`product_department` VARCHAR(30) DEFAULT NULL,
`product_family` VARCHAR(30) DEFAULT NULL
);
CREATE TABLE PUBLIC.`promotion`(
`promotion_id` INT NOT NULL,
`promotion_district_id` INT DEFAULT NULL,
`promotion_name` VARCHAR(30) DEFAULT NULL,
`media_type` VARCHAR(30) DEFAULT NULL,
`cost` DECIMAL(10, 4) DEFAULT NULL,
`start_date` DATETIME DEFAULT NULL,
`end_date` DATETIME DEFAULT NULL
);
CREATE TABLE PUBLIC.`region`(
`region_id` INT NOT NULL,
`sales_city` VARCHAR(30) DEFAULT NULL,
`sales_state_province` VARCHAR(30) DEFAULT NULL,
`sales_district` VARCHAR(30) DEFAULT NULL,
`sales_region` VARCHAR(30) DEFAULT NULL,
`sales_country` VARCHAR(30) DEFAULT NULL,
`sales_district_id` INT DEFAULT NULL
);
CREATE TABLE PUBLIC.`reserve_employee`(
`employee_id` INT NOT NULL,
`full_name` VARCHAR(30) NOT NULL,
`first_name` VARCHAR(30) NOT NULL,
`last_name` VARCHAR(30) NOT NULL,
`position_id` INT DEFAULT NULL,
`position_title` VARCHAR(30) DEFAULT NULL,
`store_id` INT NOT NULL,
`department_id` INT NOT NULL,
`birth_date` DATETIME NOT NULL,
`hire_date` DATETIME DEFAULT NULL,
`end_date` DATETIME DEFAULT NULL,
`salary` DECIMAL(10, 4) NOT NULL,
`supervisor_id` INT DEFAULT NULL,
`education_level` VARCHAR(30) NOT NULL,
`marital_status` VARCHAR(30) NOT NULL,
`gender` VARCHAR(30) NOT NULL
);
CREATE TABLE PUBLIC.`salary`(
`pay_date` DATETIME NOT NULL ,
`employee_id` INT NOT NULL ,
`department_id` INT NOT NULL ,
`currency_id` INT NOT NULL ,
`salary_paid` DECIMAL(10, 4) NOT NULL ,
`overtime_paid` DECIMAL(10, 4) NOT NULL ,
`vacation_accrued` DOUBLE NOT NULL ,
`vacation_used` DOUBLE NOT NULL
);
CREATE TABLE PUBLIC.`sales_fact_1997`(
`product_id` INT NOT NULL ,
`time_id` INT NOT NULL ,
`customer_id` INT NOT NULL ,
`promotion_id` INT NOT NULL ,
`store_id` INT NOT NULL ,
`store_sales` DECIMAL(10, 4) NOT NULL ,
`store_cost` DECIMAL(10, 4) NOT NULL ,
`unit_sales` DECIMAL(10, 4) NOT NULL
);
CREATE TABLE PUBLIC.`sales_fact_1998`(
`product_id` INT NOT NULL ,
`time_id` INT NOT NULL ,
`customer_id` INT NOT NULL ,
`promotion_id` INT NOT NULL ,
`store_id` INT NOT NULL ,
`store_sales` DECIMAL(10, 4) NOT NULL ,
`store_cost` DECIMAL(10, 4) NOT NULL ,
`unit_sales` DECIMAL(10, 4) NOT NULL
);
CREATE TABLE PUBLIC.`sales_fact_dec_1998`(
`product_id` INT NOT NULL ,
`time_id` INT NOT NULL ,
`customer_id` INT NOT NULL ,
`promotion_id` INT NOT NULL ,
`store_id` INT NOT NULL ,
`store_sales` DECIMAL(10, 4) NOT NULL ,
`store_cost` DECIMAL(10, 4) NOT NULL ,
`unit_sales` DECIMAL(10, 4) NOT NULL
);
CREATE TABLE PUBLIC.`store`(
`store_id` INT NOT NULL,
`store_type` VARCHAR(30) DEFAULT NULL,
`region_id` INT DEFAULT NULL,
`store_name` VARCHAR(30) DEFAULT NULL,
`store_number` INT DEFAULT NULL,
`store_street_address` VARCHAR(30) DEFAULT NULL,
`store_city` VARCHAR(30) DEFAULT NULL,
`store_state` VARCHAR(30) DEFAULT NULL,
`store_postal_code` VARCHAR(30) DEFAULT NULL,
`store_country` VARCHAR(30) DEFAULT NULL,
`store_manager` VARCHAR(30) DEFAULT NULL,
`store_phone` VARCHAR(30) DEFAULT NULL,
`store_fax` VARCHAR(30) DEFAULT NULL,
`first_opened_date` DATETIME DEFAULT NULL,
`last_remodel_date` DATETIME DEFAULT NULL,
`store_sqft` INT DEFAULT NULL,
`grocery_sqft` INT DEFAULT NULL,
`frozen_sqft` INT DEFAULT NULL,
`meat_sqft` INT DEFAULT NULL,
`coffee_bar` TINYINT DEFAULT NULL,
`video_store` TINYINT DEFAULT NULL,
`salad_bar` TINYINT DEFAULT NULL,
`prepared_food` TINYINT DEFAULT NULL,
`florist` TINYINT DEFAULT NULL
);
CREATE TABLE PUBLIC.`store_ragged`(
`store_id` INT NOT NULL,
`store_type` VARCHAR(30) DEFAULT NULL,
`region_id` INT DEFAULT NULL,
`store_name` VARCHAR(30) DEFAULT NULL,
`store_number` INT DEFAULT NULL,
`store_street_address` VARCHAR(30) DEFAULT NULL,
`store_city` VARCHAR(30) DEFAULT NULL,
`store_state` VARCHAR(30) DEFAULT NULL,
`store_postal_code` VARCHAR(30) DEFAULT NULL,
`store_country` VARCHAR(30) DEFAULT NULL,
`store_manager` VARCHAR(30) DEFAULT NULL,
`store_phone` VARCHAR(30) DEFAULT NULL,
`store_fax` VARCHAR(30) DEFAULT NULL,
`first_opened_date` DATETIME DEFAULT NULL,
`last_remodel_date` DATETIME DEFAULT NULL,
`store_sqft` INT DEFAULT NULL,
`grocery_sqft` INT DEFAULT NULL,
`frozen_sqft` INT DEFAULT NULL,
`meat_sqft` INT DEFAULT NULL,
`coffee_bar` TINYINT DEFAULT NULL,
`video_store` TINYINT DEFAULT NULL,
`salad_bar` TINYINT DEFAULT NULL,
`prepared_food` TINYINT DEFAULT NULL,
`florist` TINYINT DEFAULT NULL
);
CREATE TABLE PUBLIC.`time_by_day`(
`time_id` INT NOT NULL,
`the_date` DATETIME DEFAULT NULL,
`the_day` VARCHAR(30) DEFAULT NULL,
`the_month` VARCHAR(30) DEFAULT NULL,
`the_year` SMALLINT DEFAULT NULL,
`day_of_month` SMALLINT DEFAULT NULL,
`week_of_year` INT DEFAULT NULL,
`month_of_year` SMALLINT DEFAULT NULL,
`quarter` VARCHAR(30) DEFAULT NULL,
`fiscal_period` VARCHAR(30) DEFAULT NULL
);
CREATE TABLE PUBLIC.`warehouse`(
`warehouse_id` INT NOT NULL,
`warehouse_class_id` INT DEFAULT NULL,
`stores_id` INT DEFAULT NULL,
`warehouse_name` VARCHAR(60) DEFAULT NULL,
`wa_address1` VARCHAR(30) DEFAULT NULL,
`wa_address2` VARCHAR(30) DEFAULT NULL,
`wa_address3` VARCHAR(30) DEFAULT NULL,
`wa_address4` VARCHAR(30) DEFAULT NULL,
`warehouse_city` VARCHAR(30) DEFAULT NULL,
`warehouse_state_province` VARCHAR(30) DEFAULT NULL,
`warehouse_postal_code` VARCHAR(30) DEFAULT NULL,
`warehouse_country` VARCHAR(30) DEFAULT NULL,
`warehouse_owner_name` VARCHAR(30) DEFAULT NULL,
`warehouse_phone` VARCHAR(30) DEFAULT NULL,
`warehouse_fax` VARCHAR(30) DEFAULT NULL
);
CREATE TABLE PUBLIC.`warehouse_class`(
`warehouse_class_id` INT NOT NULL,
`description` VARCHAR(30) DEFAULT NULL
);
saiku官方例子,mysql建表语句
最新推荐文章于 2023-12-16 18:02:58 发布