Primero ASIR/GBD/Proyecto base de datos

De 33system wiki
Saltar a: navegación, buscar

<< GBD

Código_trabajo1

create database CINE;

use CINE

create table actor(
ID_actor int auto_increment,
nombre varchar(30) not null,
apellido_1 varchar(30) not null,
apellido_2 varchar(30),
sexo set ("hombre","mujer") not null,
est_civil enum ("soltero","casado") not null default "soltero",
edad int,
pais_origen varchar(30),
idioma varchar(30) default "Inglés",
primary key(ID_actor)
);

create table productor(
ID_productor int auto_increment,
nombre varchar(30) not null,
pais_origen varchar(30) not null,
calificacion enum ("0","1","2","3","4","5","6","7","8","9","10") not null default "5",
primary key(ID_productor)
);

create table director(
ID_director int auto_increment,
nombre varchar(30) not null,
apellido_1 varchar(30) not null,
apellido_2 varchar(30),
sexo set ("hombre","mujer") not null,
primary key(ID_director)
);

create table financiero(
ID_financiero int auto_increment,
nombre varchar(30) not null,
apellido_1 varchar(30) not null,
apellido_2 varchar(30),
sexo set ("hombre","mujer") not null,
dinero int,
primary key(ID_financiero)
);

create table pelicula(
ID_pelicula int auto_increment,
titulo varchar(30) not null,
categoria_pelicula set ("drama","comedia","terror","fantasia","ciencia ficcion","historica","otros") not null default "otros",
saga enum ("si","no") not null default "no",
libro varchar(30),
beneficio int,
fecha_estreno date,
duracion int not null,
primary key(ID_pelicula)
);

create table escuela(
ID_escuela int auto_increment,
lugar varchar(30),
especialidad enum ("arte","danza","interpretacion","artes marciales") not null,
primary key(ID_escuela)
);

create table hace(
ID_actor int,
ID_pelicula int,
fecha_I date not null,
fecha_F date not null,
numero_dias_trabajado int,
sueldo int not null,
primary key(ID_actor,ID_pelicula),
foreign key(ID_actor)
references actor(ID_actor)
on delete cascade
on update cascade,
foreign key(ID_pelicula)
references pelicula(ID_pelicula)
on delete cascade
on update cascade
);

create table pagan(
ID_financiero int,
ID_pelicula int,
fecha_pago date not null,
cantidad int not null,
primary key(ID_financiero,ID_pelicula),
foreign key(ID_financiero)
references financiero(ID_financiero)
on delete cascade
on update cascade,
foreign key(ID_pelicula)
references pelicula(ID_pelicula)
on delete cascade
on update cascade
);

**********************************************************
**********************************************************
alter table actor
add column
graduacion date not null;

alter table actor
add column
numero_cursos int not null default "1";

alter table actor
add column
coste int not null;

alter table actor
add column
ID_escuela int not null;

alter table actor
add constraint
foreign key(ID_escuela)
references escuela(ID_escuela)
on delete cascade
on update cascade;

alter table pelicula
add column
ID_director int not null;

alter table pelicula
add constraint
foreign key(ID_director)
references director(ID_director)
on delete cascade
on update cascade;

alter table pelicula
add column
ID_productor int;

alter table pelicula
add constraint
foreign key(ID_productor)
references productor(ID_productor)
on delete cascade
on update cascade;


alter table pelicula
change categoria_pelicula
categoria set ("drama","comedia","terror","fantasia","ciencia ficcion","historica","otros") not null default "otros";

**********************************************************
**********************************************************
insert into actor(nombre,apellido_1,apellido_2,sexo,est_civil,edad,pais_origen,graduacion,numero_cursos,coste,ID_escuela)
values("Jhony","Deep",NULL,"hombre","soltero",48,"USA","1980-12-01",3,20000,2);

insert into actor (ID_actor,nombre,apellido_1,apellido_2,sexo,est_civil,edad,pais_origen,idioma,graduacion,numero_cursos,coste,ID_escuela)
values('auto',"Antonio","Dominguez","Banderas","hombre","casado",51,"Espanna","castellano","1977-02-01",5,40000,1);

insert into actor (ID_actor,nombre,apellido_1,apellido_2,sexo,est_civil,edad,pais_origen,idioma,graduacion,numero_cursos,coste,ID_escuela)
values('auto',"Brad","Pit",Null,"hombre","casado",48,"USA",'auto',"1981-05-031",3,15000,3);

insert into actor (ID_actor,nombre,apellido_1,apellido_2,sexo,est_civil,edad,pais_origen,idioma,graduacion,numero_cursos,coste,ID_escuela)
values('auto',"Angelina","Jolie","Voight","mujer","casado",36,"USA",'auto',"1995-04-01",2,80000,4);

insert into actor (ID_actor,nombre,apellido_1,apellido_2,sexo,est_civil,edad,pais_origen,idioma,graduacion,numero_cursos,coste,ID_escuela)
values('auto',"Willard","Christopher","Smith","hombre","casado",48,"USA",'auto,"1980-02-25",1,10000,3);

insert into actor (ID_actor,nombre,apellido_1,apellido_2,sexo,est_civil,edad,pais_origen,idioma,graduacion,numero_cursos,coste,ID_escuela)
values('auto',"Walter","Bruce","Willis","hombre","casado",56,"Alemania",'auto',"1975-04-01",5,60000,5);


insert into productor values('auto',"Guillermo del Toro","Mexico","7");
insert into productor values('auto',"Quentin Tarantino","USA","10");
insert into productor values('auto',"Mel Gibson","USA",default);

...

**********************************************************
**********************************************************
select nombre
from actor
where nombre LIKE ('A%');

select nombre
from director
where ID_director=(
        select ID_director
        from  pelicula
        where titulo=(
                select titulo
                from hace
                where ID_actor=(
                        select ID_actor
                        from actor
                        where nombre="Walter"
)));

select nombre
from productor
where ID_productor=(
        select ID_productor
        from pelicula
        where ID_pelicula=(
                select ID_pelicula
                from pagan
                where cantidad > 30000
));

select count(*)
as "Numero de peliculas Producidas"
from pelicula
where ID_productor=(
        select ID_productor
        from productor
        where nombre="Quentin Tarantino"
);

create view actor_simp
as select nombre,apellido_1,apellido_2
from actor;

tablas

  • actor: ID_actor, nombre, apellido_1, apellido_2, sexo, est_civil, edad, pais_origen, idioma, anno_graduacion, n_cursos,coste, ID_escuela
  • productor: ID_productor, nombre, pais_origen, calificacion
  • director: ID_director, nombre, apellido_1, apellido_2, sexo
  • Financiero: ID_financiero, nombre, apellido_1, apellido:2, sexo, dinero
  • Pelicula: ID_pelidula, titulo, categoria, saga, libro, beneficio, fecha_estreno, duracion, ID_director, ID_productor
  • Escuela: ID_escuela, lugar, especialidad
  • Hace: ID_actor, ID_pelicula, fecha_I, fecha_F,numero_dias_trabajado, sueldo
  • Pagan: ID_financiero, ID_pelicula, fecha_pago, cantidad


Código_trabajo2

create table Proyecto(
ID_proyecto int auto_increment,
nombre varchar(40) not null,
tipo set ("mantenimiento","implantacion","reparacion","consultoria") not null default "mantenimiento",
Fecha_I date,
Fecha_F date,
Estado set ("activo","pausado","finalizado"),
primary key (ID_proyecto)
);

create table Equipo(
ID_equipo int auto_increment,
Nombre varchar(40),
Tipo varchar(40),
primary key(ID_equipo)
);

create table Trabajador(
ID_trabajador int auto_increment,
Nombre varchar(40), 
Apellidos varchar(40), 
Telefono int, 
primary key(ID_trabajador)
);

create table Cliente(
ID_cliente int auto_increment,
Nombre varchar(40),
Telefono int,
primary key(ID_cliente)
);

create table Proyecto_tiene_Responsable(
ID_responsable int,
ID_proyecto int,
Fecha_I date,
Fecha_F date,
primary key(ID_responsable,ID_proyecto),
foreign key (ID_responsable) 
references Responsable(ID_responsable) 
on delete cascade 
on update cascade,
foreign key (ID_proyecto) 
references Proyecto(ID_proyecto) 
on delete cascade 
on update cascade
);

create table Proyecto_tiene_Trabajador(
ID_trabajador int,
ID_proyecto int,
Fecha_I date,
Fecha_F date,
N_horas int not null,
Precio_hora decimal (4,2) not null,
Salario decimal (7,2) default 0 not null,
primary key(ID_trabajador,ID_proyecto),
foreign key (ID_trabajador)
references Trabajador(ID_trabajador)
on delete cascade
on update cascade,
foreign key (ID_proyecto)
references Proyecto(ID_proyecto)
on delete cascade
on update cascade
);


create table Responsable(
ID_responsable int auto_increment,
Nombre varchar(40),
Apellidos varchar(40),
Sexo set ("hombre","mujer") not null,
Edad int,
Especializacion varchar(40),
primary key(ID_responsable)
);

tablas

AUN QUEDA REVISARLAS

  • Proyecto:ID_proyecto, Nombre, Tipo, Fecha_I, Fecha_F, Estado,ID_cliente
  • Equipo:ID_equipo, Nombre, Tipo, ID_proyecto
  • Trabajador:ID_trabajador, Nombre, Apellidos, Sexo, Edad,
  • Cliente:ID_cliente, Nombre, Telefono, Responsable
  • Responsable:ID_responsable, Nombre, Apellidos, Sexo, Edad, Especializacion,
  • Proyecto_tiene_trabajador:ID_proyecto, ID_trabajador, Fecha_I, Fecha_F, N_horas, Precio_hora, Salario,
  • Proyecto_tiene_Responsable:ID_cliente, ID_responsable, Fecha_I, Fecha_F,

modificaciones

alter table Proyecto
add column ID_Cliente int;

alter table Proyecto
add constraint
foreign key(ID_Cliente)
references Cliente(ID_cliente)
on delete cascade
on update cascade;

alter table Equipo
add column ID_proyecto int;

alter table Equipo
add constraint 
foreign key(ID_proyecto)
references Proyecto(ID_proyecto)
on delete cascade
on update cascade;

insert

insert into Equipo values ('auto',"Servidor","Servidor Datos",1);
insert into Equipo values ('auto',"Servidor","Servidor FTP",1);
insert into Equipo values ('auto',"Servidor","Servidor Aplicacion",2);
insert into Equipo values ('auto',"Servidor","Servidor Servicios",3);
insert into Equipo values ('auto',"Servidor","Servidor Streaming",2);
insert into Equipo values ('auto',"Servidor","Servidor Datos",1);
insert into Equipo values ('auto',"Servidor","Servidor FTP",2);
insert into Equipo values ('auto',"Servidor","Servidor FTP",1);
insert into Equipo values ('auto',"Servidor","Servidor Web",4);
insert into Equipo values ('auto',"Servidor","Servidor FTP",4);
insert into Equipo values ('auto',"Portatil","Ordenador Portatil",1);
insert into Equipo values ('auto',"Portatil","Ordenador Portatil",2);
insert into Equipo values ('auto',"Portatil","Ordenador Portatil",2);
insert into Equipo values ('auto',"Portatil","Ordenador Portatil",3);
insert into Equipo values ('auto',"Portatil","Ordenador Portatil",3);
insert into Equipo values ('auto',"Portatil","Ordenador Portatil",3);
insert into Equipo values ('auto',"Portatil","Ordenador Portatil",4);
insert into Equipo values ('auto',"Portatil","Ordenador Portatil",4);
insert into Equipo values ('auto',"Portatil","Ordenador Portatil",4);
insert into Equipo values ('auto',"Portatil","Ordenador Portatil",4);
insert into Equipo values ('auto',"PC","Ordenador Sobremesa",1);
insert into Equipo values ('auto',"PC","Ordenador Sobremesa",2);
insert into Equipo values ('auto',"PC","Ordenador Sobremesa",2);
insert into Equipo values ('auto',"PC","Ordenador Sobremesa",1);
insert into Equipo values ('auto',"PC","Ordenador Sobremesa",1);
insert into Equipo values ('auto',"PC","Ordenador Sobremesa",1);
insert into Equipo values ('auto',"PC","Ordenador Sobremesa",1);
insert into Equipo values ('auto',"PC","Ordenador Sobremesa",4);

insert into Trabajador values ('auto',"Andres","Asustado",611611611);
insert into Trabajador values ('auto',"Beatriz","Blanco",622622622);
insert into Trabajador values ('auto',"Cesar","Calvo",633633633);
insert into Trabajador values ('auto',"David","Dieguez",644644644);
insert into Trabajador values ('auto',"Enrique","Ers",655655655);
insert into Trabajador values ('auto',"Fermin","Facha",666666666);
insert into Trabajador values ('auto',"Gerardo","Granado",667667667);
insert into Trabajador values ('auto',"Heinrich","Himler",688688688);
insert into Trabajador values ('auto',"Isabel","Iluminada",699699699);
insert into Trabajador values ('auto',"Javier","Jurado",600600600);
insert into Trabajador values ('auto',"Kristoh","Krieg",610610610);

insert into Cliente values ('auto',"El Corte Ingles",900900900);
insert into Cliente values ('auto',"Repsol",911911911);
insert into Cliente values ('auto',"Endesa",922922922);
insert into Cliente values ('auto',"Acisa",933933933);
insert into Cliente values ('auto',"Movistar",944944944);
insert into Cliente values ('auto',"Ono",955955955);

insert into Proyecto values ('auto',"M1",default,01/01/2001,NULL,"activo",1);
insert into Proyecto values ('auto',"M2",default,02/02/2002,NULL,"pausado",2);
insert into Proyecto values ('auto',"M3",default,03/03/2003,NULL,"pausado",2);
insert into Proyecto values ('auto',"M4",default,04/04/2004,NULL,"activo",3);
insert into Proyecto values ('auto',"I1","implantacion",05/05/2005,06/06/2006,"finalizado",1);
insert into Proyecto values ('auto',"I2","implantacion",06/06/2006,NULL,"activo",4);
insert into Proyecto values ('auto',"R1","reparacion",07/07/2007,NULL,"activo",1);
insert into Proyecto values ('auto',"R2","reparacion",08/08/2008,NULL,"activo",4);
insert into Proyecto values ('auto',"R3","reparacion",09/09/2009,NULL,"activo",3);
insert into Proyecto values ('auto',"M1",default,10/10/2010,NULL,"finalizado",5);
insert into Proyecto values ('auto',"M1",default,11/11/2011,11/12/2011,"finalizado",2);

insert into Responsable values ('auto',"Alvaro","Anton","hombre",25,"Ingeniero Agricola");
insert into Responsable values ('auto',"Baxter","Benington","hombre",26,"Ingeniero Basilicas");
insert into Responsable values ('auto',"Clarin","Cerrajero","hombre",27,"Ingeniero Cortijos");
insert into Responsable values ('auto',"Diana","Demetria","mujer",28,"Ingeniero Detonacion");
insert into Responsable values ('auto',"Ester","Esmeralda","mujer",29,"Ingeniero Estetica");
insert into Responsable values ('auto',"Felipe","Ferrer","hombre",30,"Formacion Profesional");
insert into Responsable values ('auto',"Gerardo","Griñon","hombre",31,"Generador Electrico");
insert into Responsable values ('auto',"Hernesto","Hijo","hombre",32,"Hidraulicas");
insert into Responsable values ('auto',"Isabela","Isilla","mujer",33,"Itinerante");


insert into Proyecto_tiene_Responsable values (1,1,01/01/2001,NULL);
insert into Proyecto_tiene_Responsable values (2,2,02/02/2001,NULL);
insert into Proyecto_tiene_Responsable values (3,3,03/03/2002,NULL);
insert into Proyecto_tiene_Responsable values (4,4,04/04/2002,NULL);
insert into Proyecto_tiene_Responsable values (5,5,05/05/2002,06/06/2006);
insert into Proyecto_tiene_Responsable values (6,6,06/06/2001,NULL);
insert into Proyecto_tiene_Responsable values (8,1,07/07/2005,NULL);
insert into Proyecto_tiene_Responsable values (9,5,08/08/2005,NULL);
insert into Proyecto_tiene_Responsable values (11,3,09/09/2001,NULL);

insert into Proyecto_tiene_Trabajador values (1,1,01/01/2001,01/01/2002,80,"15.5",0);
insert into Proyecto_tiene_Trabajador values (1,2,01/01/2001,01/01/2002,15,"5.5",0);
insert into Proyecto_tiene_Trabajador values (2,5,01/11/2001,03/01/2002,20,"11.2",0);
insert into Proyecto_tiene_Trabajador values (3,2,11/01/2001,01/01/2003,35,"25.3",0);
insert into Proyecto_tiene_Trabajador values (4,1,01/01/2001,01/01/2003,40,"31.5",0);
insert into Proyecto_tiene_Trabajador values (5,8,01/10/2001,04/01/2002,90,"100.9",0);
insert into Proyecto_tiene_Trabajador values (6,3,01/01/2001,01/01/2002,200,"7.5",0);
insert into Proyecto_tiene_Trabajador values (7,11,01/01/2001,01/01/2002,60,"8.5",0);
insert into Proyecto_tiene_Trabajador values (2,1,01/01/2002,01/01/2002,80,"25.5",0);
insert into Proyecto_tiene_Trabajador values (3,9,02/01/2001,01/07/2002,80,"3.5",0);
insert into Proyecto_tiene_Trabajador values (3,6,03/01/2001,01/01/2002,80,"15.7",0);
insert into Proyecto_tiene_Trabajador values (1,10,01/03/2006,01/07/2002,80,"4.5",0);
insert into Proyecto_tiene_Trabajador values (8,10,01/01/2001,10/01/2002,80,"54.5",0);
insert into Proyecto_tiene_Trabajador values (3,1,07/01/2001,01/01/2002,80,"25.5",0);
insert into Proyecto_tiene_Trabajador values (1,6,01/01/2011,01/06/2011,80,"12.9",0);
insert into Proyecto_tiene_Trabajador values (1,7,2011-11-11,2002-12-12,25,"14.9",0);


modificaciones

update Proyecto_tiene_Trabajador
set Salario=N_horas*Precio_hora
where Salario="NULL";

create view Max_salario
as select max(Salario)
from Proyecto_tiene_Trabajador;

create view Max_salario1
as select Trabajador.Nombre, Trabajador.Apellidos, Proyecto_tiene_Trabajador.Salario
from Trabajador, Proyecto_tiene_Trabajador
where Trabajador.ID_trabajador=Proyecto_tiene_Trabajador.ID_trabajador;

select max(salario), Nombre, Apellidos
from Max_salario1;

select *
from Proyecto
where Nombre like 'M%'; 

CREATE TRIGGER trigger_asignacion AFTER INSERT ON Trabajador
FOR EACH ROW
INSERT INTO Proyecto_tiene_Trabajador
VALUES (new.ID_trabajador,6,Now(),NULL,10,"15.4",0 );

select * from Proyecto_tiene_Trabajador;
select * from Trabajador;

insert into Trabajador values ('auto',"Zoraida","Zote",621621621);

select * from Proyecto_tiene_Trabajador;