sakila-dwh-schema文件

最新推荐文章于 2023-12-04 18:02:41 发布

joe_yee

最新推荐文章于 2023-12-04 18:02:41 发布

阅读量1.8k

点赞数 6

分类专栏： db

本文链接：https://blog.youkuaiyun.com/u013434493/article/details/45620111

版权

db 专栏收录该内容

0 篇文章

订阅专栏

本文介绍了一个基于MySQL的数据仓库（Sakila DWH）的搭建过程，包括了多个维度表如演员、客户、电影等的设计详情，以及事实表如租赁记录的设计。该数据仓库还涉及用户权限配置。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

sakila-dwh-schema文件

-- MySQL dump 10.13  Distrib 5.1.37, for debian-linux-gnu (i486)
--
-- Host: localhost    Database: sakila_dwh
-- ------------------------------------------------------
-- Server version    5.1.37-1ubuntu5.1

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Current Database: 'sakila_dwh'
--

DROP SCHEMA IF EXISTS sakila_dwh;
CREATE SCHEMA sakila_dwh;
USE sakila_dwh;

--
-- Table structure for table 'dim_actor'
--

DROP TABLE IF EXISTS dim_actor;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE dim_actor (
  actor_key int(10) NOT NULL AUTO_INCREMENT,
  actor_last_update datetime NOT NULL,
  actor_last_name varchar(45) NOT NULL,
  actor_first_name varchar(45) NOT NULL,
  actor_id int(11) NOT NULL,
  PRIMARY KEY (`actor_key`)
) ENGINE=MyISAM AUTO_INCREMENT=201 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table 'dim_customer'
--

DROP TABLE IF EXISTS dim_customer;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE dim_customer (
  customer_key int(8) NOT NULL AUTO_INCREMENT,
  customer_last_update datetime NOT NULL DEFAULT '1970-01-01 00:00:00',
  customer_id int(8) DEFAULT NULL,
  customer_first_name varchar(45) DEFAULT NULL,
  customer_last_name varchar(45) DEFAULT NULL,
  customer_email varchar(50) DEFAULT NULL,
  customer_active char(3) DEFAULT NULL,
  customer_created date DEFAULT NULL,
  customer_address varchar(64) DEFAULT NULL,
  customer_district varchar(20) DEFAULT NULL,
  customer_postal_code varchar(10) DEFAULT NULL,
  customer_phone_number varchar(20) DEFAULT NULL,
  customer_city varchar(50) DEFAULT NULL,
  customer_country varchar(50) DEFAULT NULL,
  customer_version_number smallint(5) DEFAULT NULL,
  customer_valid_from date DEFAULT NULL,
  customer_valid_through date DEFAULT NULL,
  PRIMARY KEY (`customer_key`),
  KEY customer_id (`customer_id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=601 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table 'dim_date'
--

DROP TABLE IF EXISTS dim_date;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE dim_date (
  date_key int(8) NOT NULL,
  date_value date NOT NULL,
  date_short char(12) NOT NULL,
  date_medium char(16) NOT NULL,
  date_long char(24) NOT NULL,
  date_full char(32) NOT NULL,
  day_in_year smallint(5) NOT NULL,
  day_in_month tinyint(3) NOT NULL,
  is_first_day_in_month char(10) NOT NULL,
  is_last_day_in_month char(10) NOT NULL,
  day_abbreviation char(3) NOT NULL,
  day_name char(12) NOT NULL,
  week_in_year tinyint(3) NOT NULL,
  week_in_month tinyint(3) NOT NULL,
  is_first_day_in_week char(10) NOT NULL,
  is_last_day_in_week char(10) NOT NULL,
  month_number tinyint(3) NOT NULL,
  month_abbreviation char(3) NOT NULL,
  month_name char(12) NOT NULL,
  year2 char(2) NOT NULL,
  year4 smallint(5) NOT NULL,
  quarter_name char(2) NOT NULL,
  quarter_number tinyint(3) NOT NULL,
  year_quarter char(7) NOT NULL,
  year_month_number char(7) NOT NULL,
  year_month_abbreviation char(8) NOT NULL,
  PRIMARY KEY (`date_key`),
  UNIQUE KEY `date` (`date_value`) USING BTREE,
  UNIQUE KEY `date_value` (`date_value`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table 'dim_film'
--

DROP TABLE IF EXISTS dim_film;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE dim_film (
  film_key int(8) NOT NULL AUTO_INCREMENT,
  film_last_update datetime NOT NULL,
  film_title varchar(64) NOT NULL,
  film_description text NOT NULL,
  film_release_year smallint(5) NOT NULL,
  film_language varchar(20) NOT NULL,
  film_original_language varchar(20) NOT NULL,
  film_rental_duration tinyint(3) DEFAULT NULL,
  film_rental_rate decimal(4,2) DEFAULT NULL,
  film_duration int(8) DEFAULT NULL,
  film_replacement_cost decimal(5,2) DEFAULT NULL,
  film_rating_code char(5) DEFAULT NULL,
  film_rating_text varchar(30) DEFAULT NULL,
  film_has_trailers char(4) DEFAULT NULL,
  film_has_commentaries char(4) DEFAULT NULL,
  film_has_deleted_scenes char(4) DEFAULT NULL,
  film_has_behind_the_scenes char(4) DEFAULT NULL,
  film_in_category_action char(4) DEFAULT NULL,
  film_in_category_animation char(4) DEFAULT NULL,
  film_in_category_children char(4) DEFAULT NULL,
  film_in_category_classics char(4) DEFAULT NULL,
  film_in_category_comedy char(4) DEFAULT NULL,
  film_in_category_documentary char(4) DEFAULT NULL,
  film_in_category_drama char(4) DEFAULT NULL,
  film_in_category_family char(4) DEFAULT NULL,
  film_in_category_foreign char(4) DEFAULT NULL,
  film_in_category_games char(4) DEFAULT NULL,
  film_in_category_horror char(4) DEFAULT NULL,
  film_in_category_music char(4) DEFAULT NULL,
  film_in_category_new char(4) DEFAULT NULL,
  film_in_category_scifi char(4) DEFAULT NULL,
  film_in_category_sports char(4) DEFAULT NULL,
  film_in_category_travel char(4) DEFAULT NULL,
  film_id int(11) NOT NULL,
  PRIMARY KEY (`film_key`)
) ENGINE=MyISAM AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table 'dim_film_actor_bridge'
--

DROP TABLE IF EXISTS dim_film_actor_bridge;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE dim_film_actor_bridge (
  film_key int(8) NOT NULL,
  actor_key int(10) NOT NULL,
  actor_weighting_factor decimal(3,2) NOT NULL,
  PRIMARY KEY (`film_key`,`actor_key`),
  KEY dim_actor_dim_film_actor_bridge_fk (`actor_key`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table 'dim_staff'
--

DROP TABLE IF EXISTS dim_staff;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE dim_staff (
  staff_key int(8) NOT NULL AUTO_INCREMENT,
  staff_last_update datetime NOT NULL DEFAULT '1970-01-01 00:00:00',
  staff_first_name varchar(45) DEFAULT NULL,
  staff_last_name varchar(45) DEFAULT NULL,
  staff_id int(8) DEFAULT NULL,
  staff_store_id int(8) DEFAULT NULL,
  staff_version_number smallint(5) DEFAULT NULL,
  staff_valid_from date DEFAULT NULL,
  staff_valid_through date DEFAULT NULL,
  staff_active char(3) DEFAULT NULL,
  PRIMARY KEY (`staff_key`),
  KEY staff_id (`staff_id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table 'dim_store'
--

DROP TABLE IF EXISTS dim_store;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE dim_store (
  store_key int(8) NOT NULL AUTO_INCREMENT,
  store_last_update datetime NOT NULL DEFAULT '1970-01-01 00:00:00',
  store_id int(8) DEFAULT NULL,
  store_address varchar(64) DEFAULT NULL,
  store_district varchar(20) DEFAULT NULL,
  store_postal_code varchar(10) DEFAULT NULL,
  store_phone_number varchar(20) DEFAULT NULL,
  store_city varchar(50) DEFAULT NULL,
  store_country varchar(50) DEFAULT NULL,
  store_manager_staff_id int(8) DEFAULT NULL,
  store_manager_first_name varchar(45) DEFAULT NULL,
  store_manager_last_name varchar(45) DEFAULT NULL,
  store_version_number smallint(5) DEFAULT NULL,
  store_valid_from date DEFAULT NULL,
  store_valid_through date DEFAULT NULL,
  PRIMARY KEY (`store_key`),
  KEY store_id (`store_id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table 'dim_time'
--

DROP TABLE IF EXISTS dim_time;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE dim_time (
  time_key int(8) NOT NULL,
  time_value time NOT NULL,
  hours24 tinyint(3) NOT NULL,
  hours12 tinyint(3) DEFAULT NULL,
  minutes tinyint(3) DEFAULT NULL,
  seconds tinyint(3) DEFAULT NULL,
  am_pm char(3) DEFAULT NULL,
  PRIMARY KEY (`time_key`),
  UNIQUE KEY time_value (`time_value`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table 'fact_rental'
--

DROP TABLE IF EXISTS fact_rental;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE fact_rental (
  customer_key int(8) NOT NULL,
  staff_key int(8) NOT NULL,
  film_key int(8) NOT NULL,
  store_key int(8) NOT NULL,
  rental_date_key int(8) NOT NULL,
  return_date_key int(10) NOT NULL,
  rental_time_key int(8) NOT NULL,
  count_returns int(10) NOT NULL,
  count_rentals int(8) NOT NULL,
  rental_duration int(11) DEFAULT NULL,
  rental_last_update datetime DEFAULT NULL,
  rental_id int(11) DEFAULT NULL,
  KEY dim_store_fact_rental_fk (`store_key`),
  KEY dim_staff_fact_rental_fk (`staff_key`),
  KEY dim_time_fact_rental_fk (`rental_time_key`),
  KEY dim_film_fact_rental_fk (`film_key`),
  KEY dim_date_fact_rental_fk (`rental_date_key`),
  KEY dim_customer_fact_rental_fk (`customer_key`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2010-03-04  1:00:33

创建用户：

CREATE USER sakila IDENTIFIED BY 'sakila';
GRANT ALL PRIVILEGES ON sakila.* TO sakila;

CREATE USER sakila_dwh IDENTIFIED BY 'sakila_dwh';
GRANT ALL PRIVILEGES ON sakila_dwh.* TO sakila_dwh;