RQfqK6Vk

1CREATE DATABASE dw_youtube_videos ; 
2USE dw_youtube_videos ; 
3
4CREATE TABLE dimDuration(
5	duration_key INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
6    duration INT NOT NULL
7);
8
9CREATE TABLE dimVideo_in(
10	video_in_key INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
11    bitrate INT NOT NULL,
12    height INT NOT NULL,
13    width INT NOT NULL,
14    frame_rate INT NOT NULL
15);
16
17CREATE TABLE dimVideo_out(
18	video_out_key INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
19    o_bitrate INT NOT NULL,
20    o_height INT NOT NULL,
21    o_width INT NOT NULL,
22    o_frame_rate INT NOT NULL
23);
24
25CREATE TABLE dimFrameType(
26	frametype_key INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
27    i INT NOT NULL,
28    p INT NOT NULL,
29    b INT NOT NULL,
30    i_size INT NOT NULL,
31    p_size INT NOT NULL,
32    b_size INT NOT NULL,
33    size INT NOT NULL
34);
35
36CREATE TABLE dimResources(
37	resources_key INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
38	utime FLOAT NOT NULL,
39    umem INT NOT NULL
40);
41
42
43
44CREATE TABLE factTranscoding(
45	transcoding_key INT NOT NULL PRIMARY KEY,
46    duration_key INT NOT NULL,
47    video_in_key INT NOT NULL,
48    video_out_key INT NOT NULL,
49    resources_key INT NOT NULL,
50    frameType_key INT NOT NULL,
51    FOREIGN KEY (duration_key) REFERENCES dimDuration(duration_key),
52    FOREIGN kEY (video_in_key) REFERENCES dimVideo_in(video_in_key),
53    FOREIGN kEY (video_out_key) REFERENCES dimVideo_out(video_out_key),
54    FOREIGN kEY (resources_key) REFERENCES dimResources(resources_key),
55    FOREIGN kEY (frameType_key) REFERENCES dimFrameType(frameType_key)
56);
57
58
59
60
61
62-- -----------------------------------------------------
63-- Table `video_transcoding`.`dimCategory`
64-- -----------------------------------------------------
65CREATE TABLE IF NOT EXISTS `dw_youtube_videos`.`dimCategory` (
66  `category_key` INT NOT NULL,
67  `name` VARCHAR(50) NOT NULL,
68  PRIMARY KEY (`category_key`))
69ENGINE = InnoDB;
70
71
72
73
74-- -----------------------------------------------------
75-- Table `video_transcoding`.`dimVideoData`
76-- -----------------------------------------------------
77CREATE TABLE IF NOT EXISTS `dw_youtube_videos`.`dimVideoData` (
78  `video_data_key` INT NOT NULL,
79  `bitrate` INT NOT NULL,
80  `height` INT NOT NULL,
81  `width` INT NOT NULL,
82  `frame_rate` FLOAT NOT NULL,
83  PRIMARY KEY (`video_data_key`))
84ENGINE = InnoDB;
85
86
87CREATE TABLE IF NOT EXISTS `dw_youtube_videos`.`dimCodec` (
88  `codec_key` INT NOT NULL AUTO_INCREMENT,
89  `name` VARCHAR(10) NOT NULL,
90  PRIMARY KEY (`codec_key`))
91ENGINE = InnoDB;
92
93-- -----------------------------------------------------
94-- Table `video_transcoding`.`factVideo`
95-- -----------------------------------------------------
96CREATE TABLE IF NOT EXISTS `dw_youtube_videos`.`factVideo` (
97 `id` INT NOT NULL AUTO_INCREMENT, 
98  `video_key` VARCHAR(20) NOT NULL,
99  `duration` INT NOT NULL,
100  `data_key` INT NOT NULL COMMENT '	\n',
101  `category_key` INT NOT NULL,
102  `in_codec_key`INT NOT NULL,
103  `o_codec_key`INT NOT NULL,
104  PRIMARY KEY (`id`),
105  INDEX `category_key_idx` (`category_key` ASC),
106  INDEX `codec_key_idx` (`codec_key` ASC),
107  INDEX `video_data_key_idx` (`data_key` ASC),
108  CONSTRAINT `category_key`
109    FOREIGN KEY (`category_key`)
110    REFERENCES `dw_youtube_videos`.`dimCategory` (`category_key`)
111    ON DELETE NO ACTION
112    ON UPDATE NO ACTION,
113  CONSTRAINT `in_codec_key`
114    FOREIGN KEY (`in_codec_key`)
115    REFERENCES `dw_youtube_videos`.`dimCodec` (`codec_key`)
116    ON DELETE NO ACTION
117    ON UPDATE NO ACTION,
118  CONSTRAINT `o_codec_key`
119    FOREIGN KEY (`o_codec_key`)
120    REFERENCES `dw_youtube_videos`.`dimCodec` (`codec_key`)
121    ON DELETE NO ACTION
122    ON UPDATE NO ACTION,
123  CONSTRAINT `video_data_key`
124    FOREIGN KEY (`data_key`)
125    REFERENCES `dw_youtube_videos`.`dimVideoData` (`video_data_key`)
126    ON DELETE NO ACTION
127    ON UPDATE NO ACTION)
128ENGINE = InnoDB;