use master
go

if exists ( select name from sys.databases where name = 'C2013_bd_lab01') begin
      drop database C2013_bd_lab01
end
go

--
-- CREANDO LA BASE DE DATOS
--

create database C2013_bd_lab01
go
use C2013_bd_lab01

go

-----
-----  PROCEDIMIENTOS
-----
go
CREATE PROCEDURE usp_showerrorinfo AS
   SELECT ERROR_NUMBER() AS [Numero de Error],
          ERROR_STATE() AS [Estado del Error],
          ERROR_SEVERITY() AS [Severidad del Error],
          ERROR_LINE() AS [Linea],
          ISNULL(ERROR_PROCEDURE(), 'No esta en un proc') AS [Procedimiento],
          ERROR_MESSAGE() AS [Mensaje]

GO

-----
-----  FUNCIONES
-----
  
---   create FUNCTION dbo.numeroAlquileres (@idCopia integer) RETURNS integer AS
--   BEGIN 
--         return (select isnull (count (*) ,0)
--	    		from alquileres a
---		   	    where a.refCopia = @idCopia)
	  
--   END

--go


----
---- TABLAS DE LA BASE DE DATOS
----
  
CREATE TABLE dbo.generos (
  idGenero INTEGER NOT NULL primary key,
  descGenero VARCHAR(100) NOT NULL)
 
go


INSERT INTO dbo.generos (idGenero, descGenero) VALUES 
  (1,'Accion'),
  (2,'Drama'),
  (3,'Suspense'),
  (4,'Comedia');
go


   ALTER TABLE GENEROS ADD columnaEliminar char(1)
   
--- Nuevo para incluir el alquiler de juegos

CREATE TABLE dbo.generosJuego (
  idGeneroJuego INTEGER NOT NULL primary key,
  descGenero VARCHAR(100) NOT NULL)
 
go

INSERT INTO dbo.generosJuego (idGeneroJuego, descGenero) VALUES 
  (1,'Carreras'),
  (2,'Aventura'),
  (3,'Habilidad'),
  (4,'Estrategia');
go

CREATE TABLE dbo.consolas (
  idConsola INTEGER NOT NULL primary key,
  descConsola CHAR(20) NOT NULL)
 
go

INSERT INTO dbo.consolas (idConsola, descConsola) VALUES 
  (1,'PS3'),
  (2,'WII'),
  (3,'XBOX');
go

create table dbo.tarifas (
   codigo char(2) not null primary key,
   descripcion char (20) not null,
   importe decimal(4,2) not null default 0
)

go

insert into dbo.tarifas values 
  ('A1','Novedades', 2.5),
  ('B1','Normal', 2.0),
  ('C1','Archivo', 2.0)
go

CREATE TABLE dbo.juegos (
  idJuego INTEGER NOT NULL primary key,
  nombreJuego VARCHAR(200) NOT NULL,
  fechaEstreno DATE NOT NULL,
  refGeneroJuego INTEGER,
  refConsola integer,
  sinopsis VARCHAR(1000),
  refTarifa char(2) not null,	
  CONSTRAINT juegos_fk1 FOREIGN KEY (refGeneroJuego) REFERENCES generosJuego (idGeneroJuego) on update cascade on delete no action,
  CONSTRAINT juegos_fk2 FOREIGN KEY (refConsola) REFERENCES consolas(idConsola) on update cascade on delete no action,
  CONSTRAINT juegos_fk3 FOREIGN KEY (refTarifa) REFERENCES tarifas (codigo) on update cascade on delete no action
)

go

set dateformat ymd
go

INSERT INTO dbo.Juegos (idJuego, nombreJuego, fechaEstreno,  
                       refGeneroJuego, refConsola,sinopsis,refTarifa) VALUES
(1,
'Chess 3D',
'2012/01/13',
 3,
 1,
 'Juego de ajedrez en 3d',
 'A1' ),

(2,
'Bloques',
'2012/01/14',
 3,
 1,
 'Coloca los bloques y evita llenar la pantalla',
 'A1' ),
 
 (3,
'Lemmings 3D',
'2012/01/15',
 3,
 2,
 'Evita perder el menor número posible de seguidores.',
 'B1' );

go

create table dbo.roles (
   codigo char(3) not null primary key,
   descripcion char (30) not null 
)

go

insert into dbo.roles values ('ACT', 'Actor'),
     ('DIR', 'Director')
go     

Create table dbo.artistas (
   identificador integer identity (1,1) not null primary key,
   Nombre char(40) not null ,
   Apellido1 char(50) not null,
   Apellido2 char (50) 
)

go
set identity_insert dbo.artistas on 
 
insert into dbo.artistas (identificador,nombre,apellido1, apellido2) values 
    (1,'JOSE', 'MOTA',null),
    (2, 'SALMA', 'HAYEK',null ),
    (3, 'JUAN LUIS', 'GALIARDO', 'COMES'), 
    (4, 'FERNANDO', 'TEJERO',null ), 
    (5, 'MANUEL', 'TALLAFE', 'LEON'),
    (6 ,'EMILE', 'HIRSCH',null), 
    (7,'OLIVIA' ,'THIRLBY',null),
(8,'STELLAN', 'SKARSGARD', null), 
(9,'DANIEL', 'CRAIG',null), 
(10,'CHRISTOPHER', 'PLUMMER',null),
(11, 'ROONEY', 'MARA',null),
 (12, 'MICHAEL', 'REILLY', 'BURKE'),
 (13, 'JOSH', 'STEWART', NULL),
  (14,'MADELINE', 'ZIMA',NULL),
 (15,'PATRICK' ,'DEMPSEY',NULL) , 
 (16, 'ASHLEY', 'JUDD',NULL), 
 (17, 'TIM', 'BLAKE', 'NELSON'), 
 (18, 'JEFFREY' ,'TAMBOR',NULL),
 (19,'LAURA' ,'ALVEA', 'PEREZ'),
 (20, 'ALEJANDRO', 'BRUGUES', 'SELEME'),
 (21,'ALEX', 'DE LA IGLESIA',NULL),
 (22,'CHRIS', 'GORAK',NULL),
 (23,'DAVID', 'FINCHER',NULL),
 (24,'MARCUS', 'DUNSTAN',NULL),
 (25,'ROB', 'MINKOFF', NULL)
 
    
set identity_insert artistas off

GO
CREATE TABLE dbo.peliculas (
  idPelicula INTEGER NOT NULL primary key,
  nombrePelicula VARCHAR(200) NOT NULL,
  fechaEstreno DATE NOT NULL,
  refGenero INTEGER,
  sinopsis VARCHAR(1000),
  refTarifa char(2) not null,	
  CONSTRAINT peliculas_fk1 FOREIGN KEY (refGenero) REFERENCES generos (idGenero) on update cascade on delete no action,
  CONSTRAINT peliculas_fk2 FOREIGN KEY (refTarifa) REFERENCES tarifas (codigo) on update cascade on delete no action
)

go

set dateformat ymd
go

INSERT INTO dbo.peliculas (refTarifa,idPelicula, nombrePelicula, fechaEstreno,  
                       refGenero, sinopsis) VALUES
('A1',1,
'LA CHISPA DE LA VIDA',
'2012/01/13',
 3,
 'Roberto es un publicista en paro que queda atrapado en un accidente de una forma en la que nadie se pone de acuerdo en la manera de rescatarlo. Una situación absurda y dramática que llama la atención de los medios de comunicación, que convierten la tragedia en un espectáculo. En semejante tesitura, Roberto decide sacar partido y vender la exclusiva para solucionar los problemas económicos de su familia de una vez por todas.' ),
 
('A1',2,
'LA HORA MAS OSCURA 3D',
'2012/01/13',
 1,
 'Los termómetros marcan 40 grados durante la ola de calor más intensa de la historia de Moscú. Dos jóvenes buscan cobertura bajo un coche de policía abandonado en la Plaza Roja, ahora completamente desierta. No están buscando una sombra para resguardarse del sofocante calor. Tratan de evitar que les localicen unos alienígenas camuflados que han colonizado la ciudad. Desde hace algunos días, estos jóvenes y sus compañeros se han visto obligados a buscar refugio bajo tierra, ya que la ciudad, como el resto del mundo, ha sido diezmada por una amenaza alienígena. Ahora han de esconderse, cuentan con pocos suministros y buscan desesperadamente seguridad, algo difícil de encontrar incluso en la mayor atracción turística moscovita.' ),

('A1',3,
'MILLENNIUM: LOS HOMBRES QUE NO AMABAN A LAS MUJERES',
'2012/01/13',
 3,
 'En el laberinto de la historia de “Millennium: Los hombres que no amaban a las mujeres” hallamos asesinatos, corrupción, secretos familiares y los demonios internos de dos inesperados socios en búsqueda de la verdad sobre un misterio oculto durante 40 años.' ),

('A1',4,
'THE COLLECTOR',
'2012/01/13',
 3,
 'Para el manitas y ex-timador Arkin, un hogar tranquilo y una familia de vacaciones es una "oportunidad". Porque en el interior de la casa hay una caja fuerte con joyas, y dentro de la caja hay una rara gema, -su única esperanza para pagar la deuda de su ex esposa y mantener intacto lo que queda de su familia-. Desgraciadamente para Arkin, dentro de la casa hay también una caja que contiene el último espécimen de una colección catalogada como sangre, huesos y lágrimas... un espécimen humano denominado "cebo".' ),

('A1',5,
'ATRACO POR DUPLICADO',
'2012/01/13',
 2,
 'Tripp Kennedy (Patrick Dempsey) entra tranquilamente en una sucursal bancaria poco antes de la hora de cierre, justo en el momento en que dos bandas distintas convergen por casualidad con intención cada una de realizar un atraco. Se produce un tiroteo y Tripp se abalanza sobre la guapa e inteligente cajera, Kaitlin (Ashley Judd), a fin de protegerla. Las dos bandas, una compuesta claramente por profesionales y la otra formada por un par de payasos llamados Mantequilla (Tim Blake Nelson) y Mermelada (Pruitt Taylor Vince), se encuentran estancadas en un punto muerto. El sistema de seguridad del banco inicia su procedimiento de cierre habitual al final de cada día y deja a todo el mundo atrapado dentro del edificio. A medida que avanza la noche, se pondrá en marcha un divertidísimo juego del gato y el ratón mientras Tripp y Kaitlin intentan salvar la situación, eludir la muerte y evitar enamorarse... o casi' ),

('A1',6,
'JUAN DE LOS MUERTOS',
'2012/01/13',
 1,
 '50 años después de la Revolución Cubana, otra nueva Revolución llega a La Habana. Una misteriosa infección está convirtiendo a sus habitantes en muertos vivientes sedientos de carne humana.Juan, como buen cubano, decide montar un negocio para sacar partido de la situación: "Juan de los Muertos, matamos a sus seres queridos". Eliminando a los infectados, Juan y sus amigos comienzan a hacer fortuna.' )

go

create table dbo.peliculaArtista (
   refArtista integer  not null,
   refPelicula integer not null,
   refRol char(3) not null,
   primary key (refArtista,refPelicula,refRol),
   constraint fkRol foreign key  (refRol) references roles(codigo) on update cascade,
   constraint fkpel2 foreign key  (refArtista) references artistas(identificador) on update cascade,
   constraint fkpel3 foreign key  (refPelicula) references peliculas(idPelicula) on update cascade
   
   
)
GO    
insert into dbo.peliculaArtista (refPelicula,refArtista,refRol) values 
(1,1,'ACT'),
(1,2,'ACT'),
(1,3,'ACT'),      
(1,4,'ACT'),
(1,5,'ACT'),
(2,6,'ACT'),
(2,7,'ACT'),
(3,8,'ACT'),
(3,9,'ACT'),      
(3,10,'ACT'),
(3,11,'ACT'),
(4,12,'ACT'),      
(4,13,'ACT'),
(4,14,'ACT'),
(5,15,'ACT'),
(5,16,'ACT'),      
(5,17,'ACT'),
(5,18,'ACT'),
(6,19,'ACT'),
(6,20,'ACT'),
(1,21,'DIR'),
(2,22,'DIR'),
(3,23,'DIR'),
(4,24,'DIR'),
(5,25,'DIR')
GO


CREATE TABLE dbo.copias (
	 IdCopia integer identity (1,1) not null primary key,
     refPelicula INTEGER,
     refJuego integer,
     fechaAlta date not null,
     fechaBaja date null,
  --   cantAlquileres as dbo.numeroAlquileres(idCopia),
     constraint ch_fechaAlt CHECK (fechaBaja is null or (fechaBaja >= fechaAlta)),
     constraint ch_Copia CHECK ((refPelicula is null and refJuego is not null) or 
                                (refPelicula is not null and refJuego is null)),
     CONSTRAINT copiasfk1 FOREIGN KEY (refPelicula) REFERENCES peliculas (idPelicula) on update cascade on delete no action,
     CONSTRAINT copiasfk2 FOREIGN KEY (refJuego) REFERENCES juegos (idJuego) on update no action on delete no action
  
  )

go

set dateformat ymd;
go
set identity_insert dbo.copias on
 
insert into dbo.copias (IdCopia,refPelicula,refJuego,fechaAlta) values

(1,1,null,'2012/01/02'), 
(2,2,null,'2012/01/02'),
(3,3,null,'2012/01/02'), 
(4,4,null,'2012/01/02'),
(5,5,null,'2012/01/02'), 
(6,6,null,'2012/01/02'),
(7,1,null,'2012/01/02'), 
(8,2,null,'2012/01/02'),
(9,3,null,'2012/01/02'), 
(10,4,null,'2012/01/02'),
(11,5,null,'2012/01/02'), 
(12,1,null,'2012/01/02'),
(13,null,1,'2012/01/22'), 
(14,null,2,'2012/01/22'),
(15,null,3,'2012/02/22'), 
(16,null,1,'2012/02/22');

set identity_insert dbo.copias off
go

CREATE TABLE dbo.Localidades (
      idLocalidad char (3)not null primary key,
      Nombre char (30) not null
)
go

insert into dbo.Localidades (idLocalidad,Nombre) values 
('001', 'Santander'),
('002', 'Torrelavega'),
('003', 'Laredo'),
('004', 'San Vicente');

go

CREATE TABLE  dbo.socios  (
     idSocio  integer identity(1,1) NOT NULL primary key,
     Nif  char(10) NOT NULL,
     Nombre char (100) NOT NULL,
     Apellido1 char (100) NOT NULL,
     Apellido2 char (100) NOT NULL,
     Dirección char (100) NOT NULL,
     refLocalidad char (3) NOT NULL,
     Telefono  char(12),
     Email char(30) check (Email like '%@%'),
     fechaAlta date not null,
     fechaBaja date null,
     constraint ch_fechaAlta CHECK (fechaBaja is null or (fechaBaja >= fechaAlta)),
     FOREIGN KEY  (refLocalidad) references 
                 Localidades (idLocalidad) on update cascade on delete no action
   )
go

set identity_insert dbo.socios  on;

set dateformat ymd;

insert into dbo.socios (idSocio, Nif, Nombre,Apellido1, Apellido2,
                        Dirección, refLocalidad, Telefono, Email,fechaAlta)
                       values 
(1,'13775935S','Manuel', 'Pérez', 'Alonso','Paseo del pino 5', '001', null,null,'2012/01/01'),                              
(2,'14775935S','José Antonio', 'Carrillo', 'Alonso','Avda. Reina Victoria 2', '001', null,null,'2012/01/01'),
(3,'13445935S','Miguel Angel', 'Castro', 'Alonso','Paseo Pereda 5', '001', null,null,'2012/01/01'),
(4,'22775935S','Paloma', 'Gutierrez', 'Del Pino','Lealtad 55', '001', null,null,'2012/01/01'),
(5,'42775935S','Angel', 'Gutierrez', 'Alonso','Tetuan 23', '001', null,null,'2012/01/01'),
(6,'45775935S','Angela', 'Gutierrez', 'Noriega','Lealtad 34', '001', null,null,'2012/01/01'), 
(7,'56775935S','Angeles', 'López', 'López','Lealtad 70', '001', null,null,'2012/01/01'), 
(8,'27788935S','Pedro', 'López', 'Jimeze','Calle alta', '001', null,null,'2012/01/01'), 
(9,'88788935S','Martin Pedro', 'Matinez', 'Castro', 'Toreviejo 34', '002', null,null,'2012/01/01'); 

set identity_insert dbo.socios off;

go

create table dbo.tarjetas (
   numero  integer identity(1,1) NOT NULL primary key,
   refsocio integer not null,
   pin integer not null,
   fechaAlta date not null,
   fechaCaducidad date null,
   constraint ch_fechaCad CHECK (fechaCaducidad is null or (fechaCaducidad >= fechaAlta)),
   constraint ch_pin CHECK (pin >= 1000 and pin <= 9999),
   constraint tarjetas_socios foreign key (refsocio) references socios(idSocio)
) 
go

set dateformat ymd;
go
set identity_insert dbo.tarjetas  on;

insert into dbo.tarjetas (numero, refsocio, pin, fechaAlta)
                       values 
(1,1,1000,'2012/04/01'),                              
(2,2,1000,'2012/04/01'),
(3,3,1000,'2012/04/01'),
(4,4,1000,'2012/04/01'),
(5,5,1000,'2012/04/01'),
(6,6,1000,'2012/04/01'), 
(7,7,1000,'2012/04/01'), 
(8,8,1000,'2012/04/01'), 
(9,9,1000,'2012/04/01'); 

set identity_insert dbo.tarjetas off;

go

CREATE TABLE dbo.conceptos(
  idConcepto INTEGER NOT NULL primary key,
  descripcion VARCHAR(100) NOT NULL)
 
go

INSERT INTO dbo.conceptos (idConcepto, descripcion) VALUES 
  (1,'Carga inicial'),
  (2,'Abono de alquiler'),
  (3,'Recarga');
go

create table dbo.movimientos (
   numero  integer identity(1,1) NOT NULL primary key,
   refTarjeta integer not null,
   refConcepto integer not null,
   importe decimal(6,2) not null default 0,
   tipo char(1) not null default 'A' check(tipo in ('C','A')), -- C es cargo (+) y A abono (-)
   fecha date not null default getdate(),
   constraint fkMovimientos foreign key (refTarjeta) references tarjetas(numero),
   constraint fkMovimientos2 foreign key (refConcepto) references conceptos(idConcepto)
)

insert into dbo.movimientos (refTarjeta,refConcepto,importe,tipo,fecha) values
(1,1,10,'C','2011/12/01'),                              
(2,1,10,'C','2011/12/01'),
(3,1,10,'C','2011/12/01'),
(4,1,10,'C','2011/12/01'),
(5,1,10,'C','2011/12/01'),
(6,1,10,'C','2011/12/01'), 
(7,1,10,'C','2011/12/01'), 
(8,1,10,'C','2011/12/01'), 
(9,1,10,'C','2011/12/01'),
(7,2,2.5,'A', '2012/01/15'),
(6,2,2.5,'A', '2012/01/15'),
(5,2,2.5,'A', '2012/01/15'),
(4,2,2.5,'A', '2012/01/15'),
(3,2,2.5,'A', '2012/01/15'),
(1,1,20,'C','2012/01/01'),

(5,2,2.5,'A', '2012/01/16');




go

CREATE TABLE dbo.alquileres (
     idAlquiler INTEGER NOT NULL primary key,
     refSocio INTEGER NOT NULL,
     refCopia INTEGER NOT NULL,
     fechaAlquiler DATETIME NOT NULL,
     fechaDevolucion dateTIME,
     importe decimal (6,2) not null default 0,
     constraint ch_fecha CHECK (fechaDevolucion is null or fechaDevolucion >= fechaAlquiler),
     constraint alquileres_socios_fk1 foreign key (refsocio) references socios(idsocio),
     CONSTRAINT alquileres_copias_fk2 FOREIGN KEY (refCopia) REFERENCES copias (idCopia),
   )
go

insert into dbo.alquileres (idAlquiler,refSocio, refCopia, 
                            fechaAlquiler,fechaDevolucion,importe) values
(1,1,16, '2012/01/14','2012/01/15',2.5),
(2,2,15, '2012/01/14','2012/01/15',2.5),
(3,3,7, '2012/01/14','2012/01/15',2.5),
(4,4,6, '2012/01/14','2012/01/15',2.5),
(5,5,5, '2012/01/14','2012/01/15',2.5),
(6,6,4, '2012/01/14','2012/01/15',2.5),
(7,7,3, '2012/01/14','2012/01/15',2.5),
(8,1,15, '2012/01/15',null,0),
(9,2,6, '2012/01/15',null,0),
(10,3,7, '2012/01/15',null,0),
(11,4,2, '2012/01/15',null,0),
(12,5,1, '2012/01/15',null,0);