2023-12-16 09:58:41 +01:00
|
|
|
CREATE TABLE `fact_service` (
|
|
|
|
`service_id` int PRIMARY KEY,
|
|
|
|
`inventory_id` int ,
|
|
|
|
`service_date` int ,
|
|
|
|
`store_id` int ,
|
|
|
|
`service_cost` DECIMAL ,
|
|
|
|
`service_type_id` int
|
|
|
|
);
|
|
|
|
|
|
|
|
CREATE TABLE `dim_customer` (
|
|
|
|
`customer_id` int PRIMARY KEY,
|
2023-12-16 10:14:53 +01:00
|
|
|
`name` varchar(64) ,
|
2023-12-16 09:58:41 +01:00
|
|
|
`birth_date` DATE ,
|
|
|
|
`email` varchar(64) ,
|
|
|
|
`address` varchar(64) ,
|
|
|
|
`city` varchar(64) ,
|
|
|
|
`country` varchar(64) ,
|
|
|
|
`postal_code` varchar(64)
|
|
|
|
);
|
|
|
|
|
|
|
|
CREATE TABLE `dim_calendar` (
|
|
|
|
`date_id` int PRIMARY KEY,
|
|
|
|
`date` DATE ,
|
|
|
|
`day_name` varchar(64) ,
|
|
|
|
`day_abbrv` varchar(64) ,
|
|
|
|
`calendar_month` int ,
|
|
|
|
`calendar_quarter` int ,
|
|
|
|
`calendar_year` int ,
|
|
|
|
`calendar_month_year` int ,
|
|
|
|
`is_weekend` BOOLEAN ,
|
|
|
|
`month_end` BOOLEAN ,
|
|
|
|
`week_num` int
|
|
|
|
);
|
|
|
|
|
|
|
|
CREATE TABLE `fact_rentals` (
|
|
|
|
`rental_id` int PRIMARY KEY,
|
|
|
|
`customer_id` int ,
|
|
|
|
`store_id` int ,
|
|
|
|
`staff_id` int ,
|
|
|
|
`inventory_id` int ,
|
|
|
|
`equipment_hash_FK` INT ,
|
|
|
|
`rent_start` int ,
|
|
|
|
`rent_end` int ,
|
|
|
|
`payment_date` int ,
|
|
|
|
`payment_deadline` int ,
|
|
|
|
`rent_value` DECIMAL
|
|
|
|
);
|
|
|
|
|
|
|
|
CREATE TABLE `dim_equipment` (
|
|
|
|
`equipment_id` int PRIMARY KEY,
|
|
|
|
`name` TEXT ,
|
|
|
|
`type` TEXT ,
|
|
|
|
`version` TEXT
|
|
|
|
);
|
|
|
|
|
|
|
|
CREATE TABLE `dim_store` (
|
2023-12-16 10:28:29 +01:00
|
|
|
`store_key` int PRIMARY KEY,
|
|
|
|
`store_id` int ,
|
|
|
|
`store_manager_name varchar(64) ,
|
|
|
|
`active_from` DATE ,
|
|
|
|
`active_to` DATE ,
|
|
|
|
`is_active` varchar(64) ,
|
2023-12-16 09:58:41 +01:00
|
|
|
`store_address` varchar(64) ,
|
|
|
|
`store_postal_code` varchar(64) ,
|
|
|
|
`store_city` varchar(64) ,
|
|
|
|
`store_country` varchar(64)
|
|
|
|
);
|
|
|
|
|
|
|
|
CREATE TABLE `bridge_equipment` (
|
|
|
|
`equipment_hash_DK` int PRIMARY KEY,
|
|
|
|
`id_FK` int
|
|
|
|
);
|
|
|
|
|
|
|
|
CREATE TABLE `dim_service_type` (
|
|
|
|
`service_type_id` INT PRIMARY KEY,
|
|
|
|
`service_name` varchar(64)
|
|
|
|
);
|
|
|
|
|
|
|
|
CREATE TABLE `dim_staff` (
|
2023-12-16 11:18:50 +01:00
|
|
|
`id` int,
|
2023-12-16 09:58:41 +01:00
|
|
|
`staff_id` int PRIMARY KEY,
|
2023-12-16 10:31:26 +01:00
|
|
|
`staff_full_name` varchar(64) , -- zawiera first_name i last_name
|
|
|
|
`staff_address` varchar(64) , -- złączenie address oraz address2
|
2023-12-16 10:20:32 +01:00
|
|
|
`staff_city` varchar(64) ,
|
|
|
|
`staff_country` varchar(64) ,
|
2023-12-16 10:26:49 +01:00
|
|
|
`staff_email` varchar(64) ,
|
|
|
|
`staff_manager_name` varchar(64)
|
2023-12-16 09:58:41 +01:00
|
|
|
);
|
|
|
|
|
2023-12-16 10:12:08 +01:00
|
|
|
CREATE TABLE `dim_car` (
|
2023-12-16 09:58:41 +01:00
|
|
|
`inventory_id` int PRIMARY KEY,
|
|
|
|
`production_year` int ,
|
|
|
|
`model` varchar(64) ,
|
|
|
|
`producer` varchar(64) ,
|
|
|
|
`fuel_type` varchar(64) ,
|
|
|
|
`license_plate` varchar(64) ,
|
|
|
|
`store_id` int ,
|
2023-12-16 10:12:08 +01:00
|
|
|
`is_rented` varchar(64) ,
|
|
|
|
`is_sold` varchar(64)
|
2023-12-16 09:58:41 +01:00
|
|
|
);
|
|
|
|
|
|
|
|
ALTER TABLE `fact_service` ADD CONSTRAINT `fact_service_fk0` FOREIGN KEY (`inventory_id`) REFERENCES `dim_cars`(`inventory_id`);
|
|
|
|
|
|
|
|
ALTER TABLE `fact_service` ADD CONSTRAINT `fact_service_fk1` FOREIGN KEY (`service_date`) REFERENCES `dim_calendar`(`date_id`);
|
|
|
|
|
|
|
|
ALTER TABLE `fact_service` ADD CONSTRAINT `fact_service_fk2` FOREIGN KEY (`store_id`) REFERENCES `dim_store`(`sk_store_id`);
|
|
|
|
|
|
|
|
ALTER TABLE `fact_service` ADD CONSTRAINT `fact_service_fk3` FOREIGN KEY (`service_type_id`) REFERENCES `dim_service_type`(`service_type_id`);
|
|
|
|
|
|
|
|
ALTER TABLE `fact_rentals` ADD CONSTRAINT `fact_rentals_fk0` FOREIGN KEY (`customer_id`) REFERENCES `dim_customer`(`customer_id`);
|
|
|
|
|
|
|
|
ALTER TABLE `fact_rentals` ADD CONSTRAINT `fact_rentals_fk1` FOREIGN KEY (`store_id`) REFERENCES `dim_store`(`sk_store_id`);
|
|
|
|
|
|
|
|
ALTER TABLE `fact_rentals` ADD CONSTRAINT `fact_rentals_fk2` FOREIGN KEY (`staff_id`) REFERENCES `dim_staff`(`staff_id`);
|
|
|
|
|
|
|
|
ALTER TABLE `fact_rentals` ADD CONSTRAINT `fact_rentals_fk3` FOREIGN KEY (`inventory_id`) REFERENCES `dim_cars`(`inventory_id`);
|
|
|
|
|
|
|
|
ALTER TABLE `fact_rentals` ADD CONSTRAINT `fact_rentals_fk4` FOREIGN KEY (`equipment_hash_FK`) REFERENCES `bridge_equipment`(`equipment_hash_DK`);
|
|
|
|
|
|
|
|
ALTER TABLE `fact_rentals` ADD CONSTRAINT `fact_rentals_fk5` FOREIGN KEY (`rent_start`) REFERENCES `dim_calendar`(`date_id`);
|
|
|
|
|
|
|
|
ALTER TABLE `fact_rentals` ADD CONSTRAINT `fact_rentals_fk6` FOREIGN KEY (`rent_end`) REFERENCES `dim_calendar`(`date_id`);
|
|
|
|
|
|
|
|
ALTER TABLE `fact_rentals` ADD CONSTRAINT `fact_rentals_fk7` FOREIGN KEY (`payment_date`) REFERENCES `dim_calendar`(`date_id`);
|
|
|
|
|
|
|
|
ALTER TABLE `fact_rentals` ADD CONSTRAINT `fact_rentals_fk8` FOREIGN KEY (`payment_deadline`) REFERENCES `dim_calendar`(`date_id`);
|
|
|
|
|
|
|
|
ALTER TABLE `bridge_equipment` ADD CONSTRAINT `bridge_equipment_fk0` FOREIGN KEY (`id_FK`) REFERENCES `dim_equipment`(`equipment_id`);
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|