sakila_dwh_schema
sakila_dwh_schema为sakila样例数据库的一个星型的数据仓库实现。
Mysql版本
create_sakila_dwh_account
CREATE USER sakila_dwh IDENTIFIED BY 'sakila_dwh'; GRANT ALL PRIVILEGES ON sakila_dwh.* TO sakila_dwh;
sakila_dwh_schema.sql
1 -- MySQL dump 10.13 Distrib 5.1.37, for debian-linux-gnu (i486) 2 -- 3 -- Host: localhost Database: sakila_dwh 4 -- ------------------------------------------------------ 5 -- Server version 5.1.37-1ubuntu5.1 6 7 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; 8 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; 9 /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; 10 /*!40101 SET NAMES utf8 */; 11 /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; 12 /*!40103 SET TIME_ZONE='+00:00' */; 13 /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; 14 /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; 15 /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; 16 /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; 17 18 -- 19 -- Current Database: `sakila_dwh` 20 -- 21 22 CREATE DATABASE /*!32312 IF NOT EXISTS*/ `sakila_dwh` /*!40100 DEFAULT CHARACTER SET utf8 */; 23 24 USE `sakila_dwh`; 25 26 -- 27 -- Table structure for table `dim_actor` 28 -- 29 30 DROP TABLE IF EXISTS `dim_actor`; 31 /*!40101 SET @saved_cs_client = @@character_set_client */; 32 /*!40101 SET character_set_client = utf8 */; 33 CREATE TABLE `dim_actor` ( 34 `actor_key` int(10) NOT NULL AUTO_INCREMENT, 35 `actor_last_update` datetime NOT NULL, 36 `actor_last_name` varchar(45) NOT NULL, 37 `actor_first_name` varchar(45) NOT NULL, 38 `actor_id` int(11) NOT NULL, 39 PRIMARY KEY (`actor_key`) 40 ) ENGINE=MyISAM AUTO_INCREMENT=201 DEFAULT CHARSET=utf8; 41 /*!40101 SET character_set_client = @saved_cs_client */; 42 43 -- 44 -- Table structure for table `dim_customer` 45 -- 46 47 DROP TABLE IF EXISTS `dim_customer`; 48 /*!40101 SET @saved_cs_client = @@character_set_client */; 49 /*!40101 SET character_set_client = utf8 */; 50 CREATE TABLE `dim_customer` ( 51 `customer_key` int(8) NOT NULL AUTO_INCREMENT, 52 `customer_last_update` datetime NOT NULL DEFAULT '1970-01-01 00:00:00', 53 `customer_id` int(8) DEFAULT NULL, 54 `customer_first_name` varchar(45) DEFAULT NULL, 55 `customer_last_name` varchar(45) DEFAULT NULL, 56 `customer_email` varchar(50) DEFAULT NULL, 57 `customer_active` char(3) DEFAULT NULL, 58 `customer_created` date DEFAULT NULL, 59 `customer_address` varchar(64) DEFAULT NULL, 60 `customer_district` varchar(20) DEFAULT NULL, 61 `customer_postal_code` varchar(10) DEFAULT NULL, 62 `customer_phone_number` varchar(20) DEFAULT NULL, 63 `customer_city` varchar(50) DEFAULT NULL, 64 `customer_country` varchar(50) DEFAULT NULL, 65 `customer_version_number` smallint(5) DEFAULT NULL, 66 `customer_valid_from` date DEFAULT NULL, 67 `customer_valid_through` date DEFAULT NULL, 68 PRIMARY KEY (`customer_key`), 69 KEY `customer_id` (`customer_id`) USING BTREE 70 ) ENGINE=MyISAM AUTO_INCREMENT=601 DEFAULT CHARSET=utf8; 71 /*!40101 SET character_set_client = @saved_cs_client */; 72 73 -- 74 -- Table structure for table `dim_date` 75 -- 76 77 DROP TABLE IF EXISTS `dim_date`; 78 /*!40101 SET @saved_cs_client = @@character_set_client */; 79 /*!40101 SET character_set_client = utf8 */; 80 CREATE TABLE `dim_date` ( 81 `date_key` int(8) NOT NULL, 82 `date_value` date NOT NULL, 83 `date_short` char(12) NOT NULL, 84 `date_medium` char(16) NOT NULL, 85 `date_long` char(24) NOT NULL, 86 `date_full` char(32) NOT NULL, 87 `day_in_year` smallint(5) NOT NULL, 88 `day_in_month` tinyint(3) NOT NULL, 89 `is_first_day_in_month` char(10) NOT NULL, 90 `is_last_day_in_month` char(10) NOT NULL, 91 `day_abbreviation` char(3) NOT NULL, 92 `day_name` char(12) NOT NULL, 93 `week_in_year` tinyint(3) NOT NULL, 94 `week_in_month` tinyint(3) NOT NULL, 95 `is_first_day_in_week` char(10) NOT NULL, 96 `is_last_day_in_week` char(10) NOT NULL, 97 `month_number` tinyint(3) NOT NULL, 98 `month_abbreviation` char(3) NOT NULL, 99 `month_name` char(12) NOT NULL, 100 `year2` char(2) NOT NULL, 101 `year4` smallint(5) NOT NULL, 102 `quarter_name` char(2) NOT NULL, 103 `quarter_number` tinyint(3) NOT NULL, 104 `year_quarter` char(7) NOT NULL, 105 `year_month_number` char(7) NOT NULL, 106 `year_month_abbreviation` char(8) NOT NULL, 107 PRIMARY KEY (`date_key`), 108 UNIQUE KEY `date` (`date_value`) USING BTREE, 109 UNIQUE KEY `date_value` (`date_value`) 110 ) ENGINE=MyISAM DEFAULT CHARSET=utf8; 111 /*!40101 SET character_set_client = @saved_cs_client */; 112 113 -- 114 -- Table structure for table `dim_film` 115 -- 116 117 DROP TABLE IF EXISTS `dim_film`; 118 /*!40101 SET @saved_cs_client = @@character_set_client */; 119 /*!40101 SET character_set_client = utf8 */; 120 CREATE TABLE `dim_film` ( 121 `film_key` int(8) NOT NULL AUTO_INCREMENT, 122 `film_last_update` datetime NOT NULL, 123 `film_title` varchar(64) NOT NULL, 124 `film_description` text NOT NULL, 125 `film_release_year` smallint(5) NOT NULL, 126 `film_language` varchar(20) NOT NULL, 127 `film_original_language` varchar(20) NOT NULL, 128 `film_rental_duration` tinyint(3) DEFAULT NULL, 129 `film_rental_rate` decimal(4,2) DEFAULT NULL, 130 `film_duration` int(8) DEFAULT NULL, 131 `film_replacement_cost` decimal(5,2) DEFAULT NULL, 132 `film_rating_code` char(5) DEFAULT NULL, 133 `film_rating_text` varchar(30) DEFAULT NULL, 134 `film_has_trailers` char(4) DEFAULT NULL, 135 `film_has_commentaries` char(4) DEFAULT NULL, 136 `film_has_deleted_scenes` char(4) DEFAULT NULL, 137 `film_has_behind_the_scenes` char(4) DEFAULT NULL, 138 `film_in_category_action` char(4) DEFAULT NULL, 139 `film_in_category_animation` char(4) DEFAULT NULL, 140 `film_in_category_children` char(4) DEFAULT NULL, 141 `film_in_category_classics` char(4) DEFAULT NULL, 142 `film_in_category_comedy` char(4) DEFAULT NULL, 143 `film_in_category_documentary` char(4) DEFAULT NULL, 144 `film_in_category_drama` char(4) DEFAULT NULL, 145 `film_in_category_family` char(4) DEFAULT NULL, 146 `film_in_category_foreign` char(4) DEFAULT NULL, 147 `film_in_category_games` char(4) DEFAULT NULL, 148 `film_in_category_horror` char(4) DEFAULT NULL, 149 `film_in_category_music` char(4) DEFAULT NULL, 150 `film_in_category_new` char(4) DEFAULT NULL, 151 `film_in_category_scifi` char(4) DEFAULT NULL, 152 `film_in_category_sports` char(4) DEFAULT NULL, 153 `film_in_category_travel` char(4) DEFAULT NULL, 154 `film_id` int(11) NOT NULL, 155 PRIMARY KEY (`film_key`) 156 ) ENGINE=MyISAM AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8; 157 /*!40101 SET character_set_client = @saved_cs_client */; 158 159 -- 160 -- Table structure for table `dim_film_actor_bridge` 161 -- 162 163 DROP TABLE IF EXISTS `dim_film_actor_bridge`; 164 /*!40101 SET @saved_cs_client = @@character_set_client */; 165 /*!40101 SET character_set_client = utf8 */; 166 CREATE TABLE `dim_film_actor_bridge` ( 167 `film_key` int(8) NOT NULL, 168 `actor_key` int(10) NOT NULL, 169 `actor_weighting_factor` decimal(3,2) NOT NULL, 170 PRIMARY KEY (`film_key`,`actor_key`), 171 KEY `dim_actor_dim_film_actor_bridge_fk` (`actor_key`) 172 ) ENGINE=MyISAM DEFAULT CHARSET=utf8; 173 /*!40101 SET character_set_client = @saved_cs_client */; 174 175 -- 176 -- Table structure for table `dim_staff` 177 -- 178 179 DROP TABLE IF EXISTS `dim_staff`; 180 /*!40101 SET @saved_cs_client = @@character_set_client */; 181 /*!40101 SET character_set_client = utf8 */; 182 CREATE TABLE `dim_staff` ( 183 `staff_key` int(8) NOT NULL AUTO_INCREMENT, 184 `staff_last_update` datetime NOT NULL DEFAULT '1970-01-01 00:00:00', 185 `staff_first_name` varchar(45) DEFAULT NULL, 186 `staff_last_name` varchar(45) DEFAULT NULL, 187 `staff_id` int(8) DEFAULT NULL, 188 `staff_store_id` int(8) DEFAULT NULL, 189 `staff_version_number` smallint(5) DEFAULT NULL, 190 `staff_valid_from` date DEFAULT NULL, 191 `staff_valid_through` date DEFAULT NULL, 192 `staff_active` char(3) DEFAULT NULL, 193 PRIMARY KEY (`staff_key`), 194 KEY `staff_id` (`staff_id`) USING BTREE 195 ) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; 196 /*!40101 SET character_set_client = @saved_cs_client */; 197 198 -- 199 -- Table structure for table `dim_store` 200 -- 201 202 DROP TABLE IF EXISTS `dim_store`; 203 /*!40101 SET @saved_cs_client = @@character_set_client */; 204 /*!40101 SET character_set_client = utf8 */; 205 CREATE TABLE `dim_store` ( 206 `store_key` int(8) NOT NULL AUTO_INCREMENT, 207 `store_last_update` datetime NOT NULL DEFAULT '1970-01-01 00:00:00', 208 `store_id` int(8) DEFAULT NULL, 209 `store_address` varchar(64) DEFAULT NULL, 210 `store_district` varchar(20) DEFAULT NULL, 211 `store_postal_code` varchar(10) DEFAULT NULL, 212 `store_phone_number` varchar(20) DEFAULT NULL, 213 `store_city` varchar(50) DEFAULT NULL, 214 `store_country` varchar(50) DEFAULT NULL, 215 `store_manager_staff_id` int(8) DEFAULT NULL, 216 `store_manager_first_name` varchar(45) DEFAULT NULL, 217 `store_manager_last_name` varchar(45) DEFAULT NULL, 218 `store_version_number` smallint(5) DEFAULT NULL, 219 `store_valid_from` date DEFAULT NULL, 220 `store_valid_through` date DEFAULT NULL, 221 PRIMARY KEY (`store_key`), 222 KEY `store_id` (`store_id`) USING BTREE 223 ) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; 224 /*!40101 SET character_set_client = @saved_cs_client */; 225 226 -- 227 -- Table structure for table `dim_time` 228 -- 229 230 DROP TABLE IF EXISTS `dim_time`; 231 /*!40101 SET @saved_cs_client = @@character_set_client */; 232 /*!40101 SET character_set_client = utf8 */; 233 CREATE TABLE `dim_time` ( 234 `time_key` int(8) NOT NULL, 235 `time_value` time NOT NULL, 236 `hours24` tinyint(3) NOT NULL, 237 `hours12` tinyint(3) DEFAULT NULL, 238 `minutes` tinyint(3) DEFAULT NULL, 239 `seconds` tinyint(3) DEFAULT NULL, 240 `am_pm` char(3) DEFAULT NULL, 241 PRIMARY KEY (`time_key`), 242 UNIQUE KEY `time_value` (`time_value`) 243 ) ENGINE=MyISAM DEFAULT CHARSET=utf8; 244 /*!40101 SET character_set_client = @saved_cs_client */; 245 246 -- 247 -- Table structure for table `fact_rental` 248 -- 249 250 DROP TABLE IF EXISTS `fact_rental`; 251 /*!40101 SET @saved_cs_client = @@character_set_client */; 252 /*!40101 SET character_set_client = utf8 */; 253 254 255 256 257 -- ---------------------------- 258 -- Table structure for fact_rental 259 -- ---------------------------- 260 DROP TABLE IF EXISTS `fact_rental`; 261 CREATE TABLE `fact_rental` ( 262 `customer_key` int(8) NOT NULL, 263 `staff_key` int(8) NOT NULL, 264 `film_key` int(8) NOT NULL, 265 `store_key` int(8) NOT NULL, 266 `rental_date_key` char(8) NOT NULL, 267 `return_date_key` char(10) NOT NULL, 268 `rental_time_key` char(8) NOT NULL, 269 `count_returns` int(10) NOT NULL, 270 `count_rentals` int(8) DEFAULT NULL, 271 `rental_duration` int(11) DEFAULT NULL, 272 `rental_last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 273 `rental_id` int(11) DEFAULT NULL, 274 KEY `dim_store_fact_rental_fk` (`store_key`), 275 KEY `dim_staff_fact_rental_fk` (`staff_key`), 276 KEY `dim_time_fact_rental_fk` (`rental_time_key`), 277 KEY `dim_film_fact_rental_fk` (`film_key`), 278 KEY `dim_date_fact_rental_fk` (`rental_date_key`), 279 KEY `dim_customer_fact_rental_fk` (`customer_key`) 280 ) ENGINE=MyISAM DEFAULT CHARSET=utf8; 281 /*!40101 SET character_set_client = @saved_cs_client */; 282 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; 283 284 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; 285 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; 286 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; 287 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; 288 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; 289 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; 290 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; 291 292 -- Dump completed on 2010-03-04 1:00:33
sakila_dwh_data
Oracle版本
create_sakila_dwh_account
/*第1步：创建临时表空间 */ create temporary tablespace odi_temp tempfile 'C:\app\ORACLE\oradata\orcl\odi_temp.dbf' size 50m autoextend on next 50m maxsize 20480m extent management local; /*第2步：创建数据表空间 */ create tablespace odi_data logging datafile 'C:\app\ORACLE\oradata\orcl\odi_data.dbf' size 50m autoextend on next 50m maxsize 20480m extent management local; /*第3步：创建用户并指定表空间 */ create user sakila_dwh identified by sakila_dwh default tablespace odi_data temporary tablespace odi_temp quota unlimited on odi_data profile default ; /*第4步：给用户授予权限 */ grant connect,resource to sakila_dwh; grant create view to sakila_dwh;
sakila_dwh_schema.sql
1 /* 2 Oracle Client Version : 12.1.0.1.0 3 4 Source Server : sakila_dwh 5 Source Server Version : 120100 6 Source Host : localhost:1521 7 Source Schema : SAKILA_DWH 8 9 Date: 2015-04-18 18:37:10 10 */ 11 12 13 -- ---------------------------- 14 -- Table structure for DIM_ACTOR 15 -- ---------------------------- 16 DROP TABLE "SAKILA_DWH"."DIM_ACTOR"; 17 CREATE TABLE "SAKILA_DWH"."DIM_ACTOR" ( 18 "ACTOR_KEY" NUMBER(11) GENERATED ALWAYS AS IDENTITY NOT NULL , 19 "ACTOR_LAST_UPDATE" DATE NOT NULL , 20 "ACTOR_LAST_NAME" VARCHAR2(45) NOT NULL , 21 "ACTOR_FIRST_NAME" VARCHAR2(45) NOT NULL , 22 "ACTOR_ID" NUMERIC NOT NULL , 23 PRIMARY KEY ("ACTOR_KEY") 24 ); 25 26 -- ---------------------------- 27 -- Table structure for DIM_CUSTOMER 28 -- ---------------------------- 29 DROP TABLE "SAKILA_DWH"."DIM_CUSTOMER"; 30 CREATE TABLE "SAKILA_DWH"."DIM_CUSTOMER" ( 31 "CUSTOMER_KEY" NUMBER(11) GENERATED ALWAYS AS IDENTITY NOT NULL , 32 "CUSTOMER_LAST_UPDATE" DATE DEFAULT TO_DATE ('1970-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS') NOT NULL , 33 "CUSTOMER_ID" NUMBER(11) , 34 "CUSTOMER_FIRST_NAME" VARCHAR2(45) , 35 "CUSTOMER_LAST_NAME" VARCHAR2(45) , 36 "CUSTOMER_EMAIL" VARCHAR2(50) , 37 "CUSTOMER_ACTIVE" CHAR(3) , 38 "CUSTOMER_CREATED" DATE , 39 "CUSTOMER_ADDRESS" VARCHAR2(64) , 40 "CUSTOMER_DISTRICT" VARCHAR2(20) , 41 "CUSTOMER_POSTAL_CODE" VARCHAR2(10) , 42 "CUSTOMER_PHONE_NUMBER" VARCHAR2(20) , 43 "CUSTOMER_CITY" VARCHAR2(50) , 44 "CUSTOMER_COUNTRY" VARCHAR2(50) , 45 "CUSTOMER_VERSION_NUMBER" NUMBER(6) , 46 "CUSTOMER_VALID_FROM" DATE , 47 "CUSTOMER_VALID_THROUGH" DATE , 48 PRIMARY KEY ("CUSTOMER_KEY") 49 ); 50 -- ---------------------------- 51 -- Indexes structure for table DIM_CUSTOMER 52 -- ---------------------------- 53 CREATE INDEX "SAKILA_DWH"."IDX_CUSTOMER_ID" ON "SAKILA_DWH"."DIM_CUSTOMER" ("CUSTOMER_ID"); 54 55 56 -- ---------------------------- 57 -- Table structure for DIM_DATE 58 -- ---------------------------- 59 DROP TABLE "SAKILA_DWH"."DIM_DATE"; 60 CREATE TABLE "SAKILA_DWH"."DIM_DATE" ( 61 "DATE_KEY" NUMBER(11) NOT NULL , 62 "DATE_VALUE" DATE NOT NULL , 63 "DATE_SHORT" CHAR(12) NOT NULL , 64 "DATE_MEDIUM" CHAR(16) NOT NULL , 65 "DATE_LONG" CHAR(24) NOT NULL , 66 "DATE_FULL" CHAR(32) NOT NULL , 67 "DAY_IN_YEAR" NUMBER(6) NOT NULL , 68 "DAY_IN_MONTH" NUMBER(4) NOT NULL , 69 "IS_FIRST_DAY_IN_MONTH" CHAR(10) NOT NULL , 70 "IS_LAST_DAY_IN_MONTH" CHAR(10) NOT NULL , 71 "DAY_ABBREVIATION" CHAR(3) NOT NULL , 72 "DAY_NAME" CHAR(12) NOT NULL , 73 "WEEK_IN_YEAR" NUMBER(4) NOT NULL , 74 "WEEK_IN_MONTH" NUMBER(4) NOT NULL , 75 "IS_FIRST_DAY_IN_WEEK" CHAR(10) NOT NULL , 76 "IS_LAST_DAY_IN_WEEK" CHAR(10) NOT NULL , 77 "MONTH_NUMBER" NUMBER(4) NOT NULL , 78 "MONTH_ABBREVIATION" CHAR(3) NOT NULL , 79 "MONTH_NAME" CHAR(12) NOT NULL , 80 "YEAR2" CHAR(2) NOT NULL , 81 "YEAR4" NUMBER(6) NOT NULL , 82 "QUARTER_NAME" CHAR(2) NOT NULL , 83 "QUARTER_NUMBER" NUMBER(4) NOT NULL , 84 "YEAR_QUARTER" CHAR(7) NOT NULL , 85 "YEAR_MONTH_NUMBER" CHAR(7) NOT NULL , 86 "YEAR_MONTH_ABBREVIATION" CHAR(8) NOT NULL , 87 PRIMARY KEY ("DATE_KEY") 88 ); 89 90 -- ---------------------------- 91 -- Indexes structure for table DIM_DATE 92 -- ---------------------------- 93 CREATE INDEX "SAKILA_DWH"."IDX_DATE_VALUE" ON "SAKILA_DWH"."DIM_DATE" ("DATE_VALUE" ); 94 95 96 97 -- ---------------------------- 98 -- Table structure for DIM_FILM 99 -- ---------------------------- 100 DROP TABLE "SAKILA_DWH"."DIM_FILM"; 101 CREATE TABLE "SAKILA_DWH"."DIM_FILM" ( 102 "FILM_KEY" NUMBER(11) GENERATED ALWAYS AS IDENTITY NOT NULL , 103 "FILM_LAST_UPDATE" DATE NOT NULL , 104 "FILM_TITLE" VARCHAR2(64) NOT NULL , 105 "FILM_DESCRIPTION" NCLOB NOT NULL , 106 "FILM_RELEASE_YEAR" NUMBER(6) NOT NULL , 107 "FILM_LANGUAGE" VARCHAR2(20) NOT NULL , 108 "FILM_ORIGINAL_LANGUAGE" VARCHAR2(20) NOT NULL , 109 "FILM_RENTAL_DURATION" NUMBER(4) , 110 "FILM_RENTAL_RATE" NUMBER , 111 "FILM_DURATION" NUMBER(11) , 112 "FILM_REPLACEMENT_COST" NUMBER , 113 "FILM_RATING_CODE" CHAR(5) , 114 "FILM_RATING_TEXT" VARCHAR2(30) , 115 "FILM_HAS_TRAILERS" CHAR(4) , 116 "FILM_HAS_COMMENTARIES" CHAR(4) , 117 "FILM_HAS_DELETED_SCENES" CHAR(4) , 118 "FILM_HAS_BEHIND_THE_SCENES" CHAR(4) , 119 "FILM_IN_CATEGORY_ACTION" CHAR(4) , 120 "FILM_IN_CATEGORY_ANIMATION" CHAR(4) , 121 "FILM_IN_CATEGORY_CHILDREN" CHAR(4) , 122 "FILM_IN_CATEGORY_CLASSICS" CHAR(4) , 123 "FILM_IN_CATEGORY_COMEDY" CHAR(4) , 124 "FILM_IN_CATEGORY_DOCUMENTARY" CHAR(4) , 125 "FILM_IN_CATEGORY_DRAMA" CHAR(4) , 126 "FILM_IN_CATEGORY_FAMILY" CHAR(4) , 127 "FILM_IN_CATEGORY_FOREIGN" CHAR(4) , 128 "FILM_IN_CATEGORY_GAMES" CHAR(4) , 129 "FILM_IN_CATEGORY_HORROR" CHAR(4) , 130 "FILM_IN_CATEGORY_MUSIC" CHAR(4) , 131 "FILM_IN_CATEGORY_NEW" CHAR(4) , 132 "FILM_IN_CATEGORY_SCIFI" CHAR(4) , 133 "FILM_IN_CATEGORY_SPORTS" CHAR(4) , 134 "FILM_IN_CATEGORY_TRAVEL" CHAR(4) , 135 "FILM_ID" NUMBER(11) NOT NULL , 136 PRIMARY KEY ("FILM_KEY") 137 ); 138 139 -- ---------------------------- 140 -- Table structure for DIM_FILM_ACTOR_BRIDGE 141 -- ---------------------------- 142 DROP TABLE "SAKILA_DWH"."DIM_FILM_ACTOR_BRIDGE"; 143 CREATE TABLE "SAKILA_DWH"."DIM_FILM_ACTOR_BRIDGE" ( 144 "FILM_KEY" NUMBER(11) NOT NULL , 145 "ACTOR_KEY" NUMBER(11) NOT NULL , 146 "ACTOR_WEIGHTING_FACTOR" NUMBER NOT NULL , 147 PRIMARY KEY ("FILM_KEY", "ACTOR_KEY") 148 ); 149 150 -- ---------------------------- 151 -- Indexes structure for table DIM_FILM_ACTOR_BRIDGE 152 -- ---------------------------- 153 CREATE INDEX "SAKILA_DWH"."IDX_ACTOR_KEY" ON "SAKILA_DWH"."DIM_FILM_ACTOR_BRIDGE" ("ACTOR_KEY" ); 154 155 156 157 -- ---------------------------- 158 -- Table structure for DIM_STAFF 159 -- ---------------------------- 160 DROP TABLE "SAKILA_DWH"."DIM_STAFF"; 161 CREATE TABLE "SAKILA_DWH"."DIM_STAFF" ( 162 "STAFF_KEY" NUMBER(11) GENERATED ALWAYS AS IDENTITY NOT NULL , 163 "STAFF_LAST_UPDATE" DATE DEFAULT TO_DATE ('1970-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS') NOT NULL , 164 "STAFF_FIRST_NAME" VARCHAR2(45) , 165 "STAFF_LAST_NAME" VARCHAR2(45) , 166 "STAFF_ID" NUMBER(11) , 167 "STAFF_STORE_ID" NUMBER(11) , 168 "STAFF_VERSION_NUMBER" NUMBER(6) , 169 "STAFF_VALID_FROM" DATE , 170 "STAFF_VALID_THROUGH" DATE , 171 "STAFF_ACTIVE" CHAR(3) , 172 PRIMARY KEY ("STAFF_KEY") 173 ); 174 175 -- ---------------------------- 176 -- Indexes structure for table DIM_STAFF 177 -- ---------------------------- 178 CREATE INDEX "SAKILA_DWH"."IDX_STAFF_ID" ON "SAKILA_DWH"."DIM_STAFF" ("STAFF_ID"); 179 180 181 182 -- ---------------------------- 183 -- Table structure for DIM_STORE 184 -- ---------------------------- 185 DROP TABLE "SAKILA_DWH"."DIM_STORE"; 186 CREATE TABLE "SAKILA_DWH"."DIM_STORE" ( 187 "STORE_KEY" NUMBER(11) GENERATED ALWAYS AS IDENTITY NOT NULL , 188 "STORE_LAST_UPDATE" DATE DEFAULT TO_DATE ('1970-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS') NOT NULL , 189 "STORE_ID" NUMBER(11) , 190 "STORE_ADDRESS" VARCHAR2(64) , 191 "STORE_DISTRICT" VARCHAR2(20) , 192 "STORE_POSTAL_CODE" VARCHAR2(10) , 193 "STORE_PHONE_NUMBER" VARCHAR2(20) , 194 "STORE_CITY" VARCHAR2(50) , 195 "STORE_COUNTRY" VARCHAR2(50) , 196 "STORE_MANAGER_STAFF_ID" NUMBER(11) , 197 "STORE_MANAGER_FIRST_NAME" VARCHAR2(45) , 198 "STORE_MANAGER_LAST_NAME" VARCHAR2(45) , 199 "STORE_VERSION_NUMBER" NUMBER(6) , 200 "STORE_VALID_FROM" DATE , 201 "STORE_VALID_THROUGH" DATE , 202 PRIMARY KEY ("STORE_KEY") 203 ); 204 205 -- ---------------------------- 206 -- Indexes structure for table DIM_STORE 207 -- ---------------------------- 208 CREATE INDEX "SAKILA_DWH"."IDX_STORE_ID" ON "SAKILA_DWH"."DIM_STORE" ("STORE_ID"); 209 210 -- ---------------------------- 211 -- Table structure for DIM_TIME 212 -- ---------------------------- 213 DROP TABLE "SAKILA_DWH"."DIM_TIME"; 214 CREATE TABLE "SAKILA_DWH"."DIM_TIME" ( 215 "TIME_KEY" NUMBER(11) NOT NULL , 216 "TIME_VALUE" VARCHAR2(255 BYTE) NOT NULL , 217 "HOURS24" NUMBER(4) NOT NULL , 218 "HOURS12" NUMBER(4) , 219 "MINUTES" NUMBER(4) , 220 "SECONDS" NUMBER(4) , 221 "AM_PM" CHAR(3) , 222 PRIMARY KEY ("TIME_KEY") 223 ); 224 -- ---------------------------- 225 -- Indexes structure for table DIM_TIME 226 -- ---------------------------- 227 CREATE INDEX "SAKILA_DWH"."IDX_TIME_VALUE" ON "SAKILA_DWH"."DIM_TIME" ("TIME_VALUE"); 228 229 230 -- ---------------------------- 231 -- Table structure for FACT_RENTAL 232 -- ---------------------------- 233 DROP TABLE "SAKILA_DWH"."FACT_RENTAL"; 234 CREATE TABLE "SAKILA_DWH"."FACT_RENTAL" ( 235 "CUSTOMER_KEY" NUMBER(11) NOT NULL , 236 "STAFF_KEY" NUMBER(11) NOT NULL , 237 "FILM_KEY" NUMBER(11) NOT NULL , 238 "STORE_KEY" NUMBER(11) NOT NULL , 239 "RENTAL_DATE_KEY" CHAR(8) NOT NULL , 240 "RETURN_DATE_KEY" CHAR(10) NOT NULL , 241 "RENTAL_TIME_KEY" CHAR(8) NOT NULL , 242 "COUNT_RETURNS" NUMBER(11) NOT NULL , 243 "COUNT_RENTALS" NUMBER(11) NOT NULL , 244 "RENTAL_DURATION" NUMBER(11) , 245 "RENTAL_LAST_UPDATE" DATE , 246 "RENTAL_ID" NUMBER(11) 247 ); 248 249 -- ---------------------------- 250 -- Indexes structure for table FACT_RENTAL 251 -- ---------------------------- 252 CREATE INDEX "SAKILA_DWH"."DIM_CUSTOMER_FACT_RENTAL_FK" 253 ON "SAKILA_DWH"."FACT_RENTAL" ("CUSTOMER_KEY" ) 254 ; 255 CREATE INDEX "SAKILA_DWH"."DIM_DATE_FACT_RENTAL_FK" 256 ON "SAKILA_DWH"."FACT_RENTAL" ("RENTAL_DATE_KEY" ) 257 ; 258 CREATE INDEX "SAKILA_DWH"."DIM_FILM_FACT_RENTAL_FK" 259 ON "SAKILA_DWH"."FACT_RENTAL" ("FILM_KEY" ) 260 ; 261 CREATE INDEX "SAKILA_DWH"."DIM_STAFF_FACT_RENTAL_FK" 262 ON "SAKILA_DWH"."FACT_RENTAL" ("STAFF_KEY" ) 263 ; 264 CREATE INDEX "SAKILA_DWH"."DIM_STORE_FACT_RENTAL_FK" 265 ON "SAKILA_DWH"."FACT_RENTAL" ("STORE_KEY" ) 266 ; 267 CREATE INDEX "SAKILA_DWH"."DIM_TIME_FACT_RENTAL_FK" 268 ON "SAKILA_DWH"."FACT_RENTAL" ("RENTAL_TIME_KEY" ) 269 ;
