-- Created on: 16/01/2023 -- @author: Andoni Aranguren Ubierna -- Adaptations: 02/2023 @author: Sergio Campos use urbanite_recommender; CREATE TABLE `action` ( `Action_id` int(11) NOT NULL AUTO_INCREMENT, `Action_name` varchar(255) DEFAULT NULL, `lat` decimal(10,8) NOT NULL, `lon` decimal(10,8) NOT NULL, `fecha_hora` date DEFAULT NULL, `popularity` int(11) DEFAULT '0', PRIMARY KEY (`Action_id`), UNIQUE KEY `lat` (`lat`,`lon`,`Action_name`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; CREATE TABLE `chosen_action` ( `plan_id` int(11) NOT NULL, `time_slot` int(11) NOT NULL, `Action_id` int(11) NOT NULL, `order_position` int(11) DEFAULT NULL, PRIMARY KEY (`plan_id`,`time_slot`,`Action_id`), UNIQUE KEY `plan_id` (`plan_id`,`time_slot`,`Action_id`), KEY `Action_id` (`Action_id`), CONSTRAINT `chosen_action_ibfk_1` FOREIGN KEY (`Action_id`) REFERENCES `action` (`Action_id`), CONSTRAINT `chosen_action_ibfk_2` FOREIGN KEY (`plan_id`, `time_slot`) REFERENCES `plan_detail` (`plan_id`, `time_slot`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; CREATE TABLE `kpi` ( `KPI_id` int(11) NOT NULL AUTO_INCREMENT, `KPI_name` varchar(50) NOT NULL, `popularity` int(11) DEFAULT '0', `KPI_level` int(11) DEFAULT NULL, `KPI_level_0` varchar(45) DEFAULT NULL, `KPI_level_1` varchar(45) DEFAULT NULL, `KPI_level_2` varchar(45) DEFAULT NULL, `Absolute` tinyint(4) DEFAULT NULL, `Relative` tinyint(4) DEFAULT NULL, `Geographical` tinyint(4) DEFAULT NULL, `Zone_Id` int(11) DEFAULT NULL, `Pilot_Id` varchar(45) DEFAULT NULL, `Use_case_Id` varchar(6) DEFAULT NULL, PRIMARY KEY (`KPI_id`), UNIQUE KEY `KPI_name` (`KPI_name`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; CREATE TABLE `kpi_action` ( `Action_id` int(11) NOT NULL, `kpi_id` int(11) NOT NULL, PRIMARY KEY (`Action_id`,`kpi_id`), UNIQUE KEY `Action_id` (`Action_id`,`kpi_id`), CONSTRAINT `kpi_action_ibfk_1` FOREIGN KEY (`Action_id`) REFERENCES `action` (`Action_id`), CONSTRAINT `kpi_action_ibfk_2` FOREIGN KEY (`Action_id`) REFERENCES `kpi` (`KPI_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; CREATE TABLE `plan` ( `plan_id` int(11) NOT NULL AUTO_INCREMENT, `plan_date` datetime DEFAULT NULL, PRIMARY KEY (`plan_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; CREATE TABLE `plan_detail` ( `plan_id` int(11) NOT NULL, `time_slot` int(11) NOT NULL, `fecha_hora` time DEFAULT NULL, `arrive_by` tinyint(1) DEFAULT NULL, `from_lat` float NOT NULL, `from_lon` float NOT NULL, `to_lat` float NOT NULL, `to_lon` float NOT NULL, `bike_safety_index` int(11) DEFAULT NULL, `bike_safety_aggregate` int(11) DEFAULT NULL, `bikeability_index` int(11) DEFAULT NULL, `bikeability_aggregate` int(11) DEFAULT NULL, `vehicle_count_per_link` int(11) DEFAULT NULL, `vehicle_count` int(11) DEFAULT NULL, `capacity_to_moyua` int(11) DEFAULT NULL, `average_bus_speedl` int(11) DEFAULT NULL, `pedestrial_travel_time` int(11) DEFAULT NULL, `link_average_speed` int(11) DEFAULT NULL, `line_near_capacity` int(11) DEFAULT NULL, `congested_lines` int(11) DEFAULT NULL, `public_transport_user` int(11) DEFAULT NULL, `bicycle_user` int(11) DEFAULT NULL, `emission_total` int(11) DEFAULT NULL, `noise_total` int(11) DEFAULT NULL, `noise_global` int(11) DEFAULT NULL, PRIMARY KEY (`plan_id`,`time_slot`), CONSTRAINT `plan_detail_ibfk_1` FOREIGN KEY (`plan_id`) REFERENCES `plan` (`plan_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;