· 6 years ago · May 05, 2019, 05:48 PM
1-- Sakila Sample Database Schema
2-- Version 1.0
3
4-- Copyright (c) 2006, 2015, Oracle and/or its affiliates.
5-- All rights reserved.
6
7-- Redistribution and use in source and binary forms, with or without
8-- modification, are permitted provided that the following conditions are met:
9
10-- * Redistributions of source code must retain the above copyright notice,
11-- this list of conditions and the following disclaimer.
12-- * Redistributions in binary form must reproduce the above copyright notice,
13-- this list of conditions and the following disclaimer in the documentation
14-- and/or other materials provided with the distribution.
15-- * Neither the name of Oracle nor the names of its contributors may be used
16-- to endorse or promote products derived from this software without specific
17-- prior written permission.
18
19-- THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
20-- AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
21-- IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
22-- ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE
23-- LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
24-- CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
25-- SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
26-- INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
27-- CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
28-- ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
29-- POSSIBILITY OF SUCH DAMAGE.
30
31SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
32SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
33SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
34
35DROP DATABASE IF EXISTS netflix;
36CREATE DATABASE netflix CHARACTER SET utf8mb4;
37USE netflix;
38
39CREATE TABLE actor (
40 id_actor SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
41 nombre VARCHAR(45) NOT NULL,
42 apellidos VARCHAR(45) NOT NULL,
43 ultima_actualizacion TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
44 PRIMARY KEY (id_actor)
45)ENGINE=InnoDB DEFAULT CHARSET=utf8;
46
47
48CREATE TABLE direccion (
49 id_direccion SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
50 direccion VARCHAR(50) NOT NULL,
51 direccion2 VARCHAR(50),
52 distrito VARCHAR(20) NOT NULL,
53 id_ciudad SMALLINT UNSIGNED NOT NULL,
54 codigo_postal VARCHAR(10) DEFAULT NULL,
55 telefono VARCHAR(20) NOT NULL,
56 ultima_actualizacion TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
57 PRIMARY KEY (id_direccion),
58 KEY idx_fk_id_ciudad (id_ciudad),
59 CONSTRAINT `fk_direccion_ciudad` FOREIGN KEY (id_ciudad) REFERENCES ciudad (id_ciudad) ON DELETE RESTRICT ON UPDATE CASCADE
60)ENGINE=InnoDB DEFAULT CHARSET=utf8;
61
62
63CREATE TABLE categoria (
64 id_categoria TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
65 nombre VARCHAR(25) NOT NULL,
66 ultima_actualizacion TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
67 PRIMARY KEY (id_categoria)
68)ENGINE=InnoDB DEFAULT CHARSET=utf8;
69
70
71CREATE TABLE ciudad (
72 id_ciudad SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
73 nombre VARCHAR(50) NOT NULL,
74 id_pais SMALLINT UNSIGNED NOT NULL,
75 ultima_actualizacion TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
76 PRIMARY KEY (id_ciudad),
77 KEY idx_fk_id_pais (id_pais),
78 CONSTRAINT `fk_ciudad_pais` FOREIGN KEY (id_pais) REFERENCES pais (id_pais) ON DELETE RESTRICT ON UPDATE CASCADE
79)ENGINE=InnoDB DEFAULT CHARSET=utf8;
80
81
82CREATE TABLE pais (
83 id_pais SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
84 nombre VARCHAR(50) NOT NULL,
85 ultima_actualizacion TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
86 PRIMARY KEY (id_pais)
87)ENGINE=InnoDB DEFAULT CHARSET=utf8;
88
89
90CREATE TABLE cliente (
91 id_cliente SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
92 id_almacen TINYINT UNSIGNED NOT NULL,
93 nombre VARCHAR(45) NOT NULL,
94 apellidos VARCHAR(45) NOT NULL,
95 email VARCHAR(50) DEFAULT NULL,
96 id_direccion SMALLINT UNSIGNED NOT NULL,
97 activo BOOLEAN NOT NULL DEFAULT TRUE,
98 fecha_creacion DATETIME NOT NULL,
99 ultima_actualizacion TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
100 PRIMARY KEY (id_cliente),
101 KEY idx_fk_id_almacen (id_almacen),
102 KEY idx_fk_id_direccion (id_direccion),
103 CONSTRAINT fk_customer_address FOREIGN KEY (id_direccion) REFERENCES direccion (id_direccion) ON DELETE RESTRICT ON UPDATE CASCADE,
104 CONSTRAINT fk_customer_store FOREIGN KEY (id_almacen) REFERENCES almacen (id_almacen) ON DELETE RESTRICT ON UPDATE CASCADE
105)ENGINE=InnoDB DEFAULT CHARSET=utf8;
106
107
108CREATE TABLE pelicula (
109 id_pelicula SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
110 titulo VARCHAR(255) NOT NULL,
111 descripcion TEXT DEFAULT NULL,
112 anyo_lanzamiento YEAR DEFAULT NULL,
113 id_idioma TINYINT UNSIGNED NOT NULL,
114 id_idioma_original TINYINT UNSIGNED DEFAULT NULL,
115 duracion_alquiler TINYINT UNSIGNED NOT NULL DEFAULT 3,
116 rental_rate DECIMAL(4,2) NOT NULL DEFAULT 4.99,
117 duracion SMALLINT UNSIGNED DEFAULT NULL,
118 replacement_cost DECIMAL(5,2) NOT NULL DEFAULT 19.99,
119 clasificacion ENUM('G','PG','PG-13','R','NC-17') DEFAULT 'G',
120 caracteristicas_especiales SET('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') DEFAULT NULL,
121 ultima_actualizacion TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
122 PRIMARY KEY (id_pelicula),
123 KEY idx_fk_id_idioma (id_idioma),
124 KEY idx_fk_id_idioma_original (id_idioma_original),
125 CONSTRAINT fk_film_idioma FOREIGN KEY (id_idioma) REFERENCES idioma (id_idioma) ON DELETE RESTRICT ON UPDATE CASCADE,
126 CONSTRAINT fk_film_idioma_original FOREIGN KEY (id_idioma_original) REFERENCES idioma (id_idioma) ON DELETE RESTRICT ON UPDATE CASCADE
127)ENGINE=InnoDB DEFAULT CHARSET=utf8;
128
129
130CREATE TABLE pelicula_actor (
131 id_actor SMALLINT UNSIGNED NOT NULL,
132 id_pelicula SMALLINT UNSIGNED NOT NULL,
133 ultima_actualizacion TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
134 PRIMARY KEY (id_actor, id_pelicula),
135 CONSTRAINT fk_film_actor_actor FOREIGN KEY (id_actor) REFERENCES actor (id_actor) ON DELETE RESTRICT ON UPDATE CASCADE,
136 CONSTRAINT fk_film_actor_film FOREIGN KEY (id_pelicula) REFERENCES pelicula (id_pelicula) ON DELETE RESTRICT ON UPDATE CASCADE
137)ENGINE=InnoDB DEFAULT CHARSET=utf8;
138
139
140CREATE TABLE pelicula_categoria (
141 id_pelicula SMALLINT UNSIGNED NOT NULL,
142 id_categoria TINYINT UNSIGNED NOT NULL,
143 ultima_actualizacion TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
144 PRIMARY KEY (id_pelicula, id_categoria),
145 CONSTRAINT fk_film_category_film FOREIGN KEY (id_pelicula) REFERENCES pelicula (id_pelicula) ON DELETE RESTRICT ON UPDATE CASCADE,
146 CONSTRAINT fk_film_category_category FOREIGN KEY (id_categoria) REFERENCES categoria (id_categoria) ON DELETE RESTRICT ON UPDATE CASCADE
147)ENGINE=InnoDB DEFAULT CHARSET=utf8;
148
149
150CREATE TABLE film_text (
151 film_id SMALLINT NOT NULL,
152 title VARCHAR(255) NOT NULL,
153 description TEXT,
154 PRIMARY KEY (film_id)
155)ENGINE=InnoDB DEFAULT CHARSET=utf8;
156
157
158CREATE TABLE inventario (
159 id_inventario MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
160 id_pelicula SMALLINT UNSIGNED NOT NULL,
161 id_almacen TINYINT UNSIGNED NOT NULL,
162 ultima_actualizacion TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
163 PRIMARY KEY (id_inventario),
164 KEY idx_fk_film_id (id_pelicula),
165 KEY idx_store_id_film_id (id_almacen, id_pelicula),
166 CONSTRAINT fk_inventory_store FOREIGN KEY (id_almacen) REFERENCES almacen (id_almacen) ON DELETE RESTRICT ON UPDATE CASCADE,
167 CONSTRAINT fk_inventory_film FOREIGN KEY (id_pelicula) REFERENCES pelicula (id_pelicula) ON DELETE RESTRICT ON UPDATE CASCADE
168)ENGINE=InnoDB DEFAULT CHARSET=utf8;
169
170
171CREATE TABLE idioma (
172 id_idioma TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
173 nombre CHAR(20) NOT NULL,
174 ultima_actualizacion TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
175 PRIMARY KEY (id_idioma)
176)ENGINE=InnoDB DEFAULT CHARSET=utf8;
177
178
179CREATE TABLE pago (
180 id_pago SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
181 id_cliente SMALLINT UNSIGNED NOT NULL,
182 id_empleado TINYINT UNSIGNED NOT NULL,
183 id_alquiler INT DEFAULT NULL,
184 total DECIMAL(5,2) NOT NULL,
185 fecha_pago DATETIME NOT NULL,
186 ultima_actualizacion TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
187 PRIMARY KEY (id_pago),
188 KEY idx_fk_staff_id (id_empleado),
189 KEY idx_fk_customer_id (id_cliente),
190 CONSTRAINT fk_payment_rental FOREIGN KEY (id_alquiler) REFERENCES alquiler (id_alquiler) ON DELETE SET NULL ON UPDATE CASCADE,
191 CONSTRAINT fk_payment_customer FOREIGN KEY (id_cliente) REFERENCES cliente (id_cliente) ON DELETE RESTRICT ON UPDATE CASCADE,
192 CONSTRAINT fk_payment_staff FOREIGN KEY (id_empleado) REFERENCES empleado (id_empleado) ON DELETE RESTRICT ON UPDATE CASCADE
193)ENGINE=InnoDB DEFAULT CHARSET=utf8;
194
195
196CREATE TABLE alquiler (
197 id_alquiler INT NOT NULL AUTO_INCREMENT,
198 fecha_alquiler DATETIME NOT NULL,
199 id_inventario MEDIUMINT UNSIGNED NOT NULL,
200 id_cliente SMALLINT UNSIGNED NOT NULL,
201 fecha_devolucion DATETIME DEFAULT NULL,
202 id_empleado TINYINT UNSIGNED NOT NULL,
203 ultima_actualizacion TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
204 PRIMARY KEY (id_alquiler),
205 UNIQUE KEY (fecha_alquiler, id_inventario, id_cliente),
206 KEY idx_fk_inventory_id (id_inventario),
207 KEY idx_fk_customer_id (id_cliente),
208 KEY idx_fk_staff_id (id_empleado),
209 CONSTRAINT fk_rental_staff FOREIGN KEY (id_empleado) REFERENCES empleado (id_empleado) ON DELETE RESTRICT ON UPDATE CASCADE,
210 CONSTRAINT fk_rental_inventory FOREIGN KEY (id_inventario) REFERENCES inventario (id_inventario) ON DELETE RESTRICT ON UPDATE CASCADE,
211 CONSTRAINT fk_rental_customer FOREIGN KEY (id_cliente) REFERENCES cliente (id_cliente) ON DELETE RESTRICT ON UPDATE CASCADE
212)ENGINE=InnoDB DEFAULT CHARSET=utf8;
213
214
215CREATE TABLE empleado (
216 id_empleado TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
217 nombre VARCHAR(45) NOT NULL,
218 apellidos VARCHAR(45) NOT NULL,
219 id_direccion SMALLINT UNSIGNED NOT NULL,
220 imagen BLOB DEFAULT NULL,
221 email VARCHAR(50) DEFAULT NULL,
222 id_almacen TINYINT UNSIGNED NOT NULL,
223 activo BOOLEAN NOT NULL DEFAULT TRUE,
224 username VARCHAR(16) NOT NULL,
225 password VARCHAR(40) BINARY DEFAULT NULL,
226 ultima_actualizacion TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
227 PRIMARY KEY (id_empleado),
228 KEY idx_fk_store_id (id_almacen),
229 KEY idx_fk_address_id (id_direccion),
230 CONSTRAINT fk_staff_store FOREIGN KEY (id_almacen) REFERENCES almacen (id_almacen) ON DELETE RESTRICT ON UPDATE CASCADE,
231 CONSTRAINT fk_staff_address FOREIGN KEY (id_direccion) REFERENCES direccion (id_direccion) ON DELETE RESTRICT ON UPDATE CASCADE
232)ENGINE=InnoDB DEFAULT CHARSET=utf8;
233
234
235CREATE TABLE almacen (
236 id_almacen TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
237 id_empleado_jefe TINYINT UNSIGNED NOT NULL,
238 id_direccion SMALLINT UNSIGNED NOT NULL,
239 ultima_actualizacion TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
240 PRIMARY KEY (id_almacen),
241 UNIQUE KEY idx_unique_manager (id_empleado_jefe),
242 KEY idx_fk_address_id (id_direccion),
243 CONSTRAINT fk_store_staff FOREIGN KEY (id_empleado_jefe) REFERENCES empleado (id_empleado) ON DELETE RESTRICT ON UPDATE CASCADE,
244 CONSTRAINT fk_store_address FOREIGN KEY (id_direccion) REFERENCES direccion (id_direccion) ON DELETE RESTRICT ON UPDATE CASCADE
245)ENGINE=InnoDB DEFAULT CHARSET=utf8;
246
247
248SET SQL_MODE=@OLD_SQL_MODE;
249SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
250SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;