Base de Datos de Programación de Vuelos - Script
create database T3_Proyecto_G4;
use T3_Proyecto_G4;
create table Empresa(
Ruc_Emp int primary key,
RazonSocial varchar(25),
Dir_Emp varchar(20),
tel_Emp varchar(9),
Correo_Emp varchar(25)
);
create table empleado(
dni_empl int primary key,
nom_empl varchar(25) not null,
ape_empl varchar(25) not null,
edad_empl int,
cel varchar(9) not null,
Ruc_Emp int
);
create table Cliente(
id_cliente int primary key,
nom_cliente varchar(25) not null,
ape_cli varchar(25) not null,
edad_cli varchar(3) not null,
sexo_cli varchar(5),
dni_empl int
);
create table Programacion(
cod_program int primary key,
fech_program date not null,
Matri_avion int
);
create table avion(
Matri_avion int primary key,
modelo_avion varchar(25),
Nro_tirpulante int
);
create table vuelo(
num_vuelo int primary key,
Nom_piloto varchar(25),
clase_vuelo varchar(25),
Matri_avion int
);
create table Asiento(
Nro_asiento int primary key,
Estado_Asiente varchar(15),
Tipo_Asiento varchar(15),
Matri_avion int
);
create table Reserva(
cod_reserva varchar(10) primary key,
cod_program int,
id_cliente int,
cod_destino int,
fecha_reserva date not null,
precio double
);
create table Destino(
cod_destino int primary key,
Lugar_Destino varchar(15)
);
-- select From---------------------------
select * from Empresa;
select * from empleado;
select * from cliente;
select * from Programacion;
select * from avion;
select * from vuelo;
select * from Reserva;
select * from Destino;
select * from asiento;
-- Insert ----------------------------------
Insert into Empresa values ("60496", "Av.brasil 1345","Jesus Maria", "988565225","josef@gmail.com");
Insert into Empresa values ("64387", "Av.brasil 1345","Jesus Maria", "988565225","jose_1@gmail.com");
Insert into Empresa values("91042", "Av.Canada 9832","San Isidro","988565443","raul@gmail.com");
Insert into Empresa values("97488", "Av.Beach 2221","San Borja", "988565678","ernestina@gmail.com");
Insert into Empresa values("37347", "Av.Bogota 5841","Surquillo", "988456465","maria@gmail.com");
Insert into empleado values('9834221','Ximena','Ronceros','19','990435789',"60496");
Insert into empleado values('9834222', 'Josefa','Perez', '20', '987354214',"60496");
Insert into empleado values('9834223', 'Paola','Torres', '21', '976034278',"97488");
Insert into empleado values('9834224', 'Omar','Flores', '22', '987142398',"37347");
Insert into cliente values (7543,"Ariana","Salinas","25","F",'9834224');
Insert into cliente values (2344,"Valentino","Martinez","30","M",'9834222');
Insert into cliente values (7546,"Marco","Montaner","23","M",'9834222');
Insert into cliente values (9423,"Anita","Fuente","21","F",'9834221');
Insert into destino values (001,"Miami");
Insert into destino values (002,"Bogota");
Insert into destino values (003,"Madrid");
Insert into destino values (004,"Londres");
Insert into destino values (005,"La FLorida");
Insert into avion values (1001,"Boeing 737",615);
Insert into avion values (1002,"Antonov 225",615);
Insert into avion values (1003,"Airbus 300",615);
Insert into avion values (1004,"Airbus 330",615);
#DATE YYYY-MM-DD
insert into reserva values ('3897','1010',7543,001,'2020-06-10','250.00');
insert into reserva values ('2899','2024',7543,003,'2020-05-25','220.00');
insert into reserva values ('2837','4830',7546,004,'2020-02-01','260.00');
insert into reserva values ('2844','2683',9423,005,'2020-07-12','300.00');
insert into reserva values ('2847','2024',2344,002,'2020-11-24','400.00');
insert into programacion values ('1010','2020-06-10',1001);
insert into programacion values ('1020','2020-05-25',1003);
insert into programacion values ('2024','2020-02-01',1002);
insert into programacion values ('4830','2020-07-12',1001);
insert into programacion values ('2683','2020-11-24',1004);
Insert into Asiento values(5677,"Ocupado", "Clase media",1001);
Insert into Asiento values(5673,"Libre", "Clase vip",1002);
Insert into Asiento values(5676,"Libre", "Clase media",1004);
Insert into Vuelo values (2345,"Ana Castillo ", "Economica",1001) ;
Insert into Vuelo values (2645,"Ramon Toledo ", "Primera Clase",1002) ;
Insert into Vuelo values (2984,"Jesus Reategui", "Ejecutiva",1004) ;
-- Operadores -------------------------
-- consulta Empresa
SELECT Ruc_Emp, tel_Emp
FROM empresa
WHERE Ruc_Emp ="37347" or tel_Emp = "988565225";
-- Consulta Empleado
SELECT Nom_Empl, cel
FROM empleado
WHERE Nom_Empl ="Ximena" and cel ="990435789";
-- Consulta Cliente
select id_cliente , dni_empl
from cliente
where id_cliente = 2344 or dni_empl = '9834222';
-- Consulta Destino
SELECT cod_destino, Lugar_Destino
FROM destino
WHERE cod_destino=004 and Lugar_Destino='Londres';
-- Consulta Avion
SELECT Matri_avion, modelo_avion
FROM avion
WHERE Matri_avion='1003' and modelo_avion='Airbus 300';
-- Consulta Reserva
SELECT cod_program, fecha_reserva
FROM reserva
WHERE cod_program='4830' or fecha_reserva='2020-11-24';
-- Consulta Programacion
SELECT cod_program, fech_program
FROM programacion
WHERE cod_program='2024' or fech_program='2020-11-24';
-- Consulta Asiento
SELECT Nro_asiento, Estado_Asiente
FROM asiento
WHERE Nro_asiento= 5673 and Estado_Asiente='Libre';
-- Consulta Vuelo
SELECT num_vuelo, Nom_piloto
FROM vuelo
WHERE num_vuelo= 2984 or Nom_piloto="Jesus Reategui";
-- select--------------
-- Operador IGUAL (=)
SELECT * FROM vuelo WHERE clase_vuelo = 'Economica';
SELECT * FROM vuelo WHERE num_vuelo = '2645';
SELECT * FROM avion WHERE Matri_avion = 1002;
-- Operador MAYOR QUE o MAYOR IGUAL QUE (>=) O MAYOR(>) o MENOR(<
SELECT * FROM reserva WHERE precio >= '260';
SELECT * FROM reserva WHERE fecha_reserva > '2020-07-12';
SELECT * FROM programacion WHERE fech_program <= '2020-07-12';
SELECT * FROM reserva WHERE precio < '300';
-- Operador DIFERENTE (<>)
SELECT * FROM cliente WHERE edad_cli <> 23 ;
SELECT * FROM destino WHERE Lugar_Destino <> 'Londres';
SELECT * FROM destino WHERE Lugar_Destino != 'Bogota';
-- Relaciones-----------------------------
ALTER TABLE empleado ADD CONSTRAINT empleado_Empresa FOREIGN KEY
(Ruc_Emp) REFERENCES Empresa (Ruc_Emp);
ALTER TABLE Cliente ADD CONSTRAINT Cliente_empleado FOREIGN KEY
(dni_empl) REFERENCES empleado (dni_empl);
ALTER TABLE Reserva ADD CONSTRAINT Reserva_cod_program_fk FOREIGN KEY
(cod_program) REFERENCES Programacion (cod_program);
ALTER TABLE Reserva ADD CONSTRAINT Reserva_Cliente_fk FOREIGN KEY
(id_cliente) REFERENCES Cliente (id_cliente);
ALTER TABLE Reserva ADD CONSTRAINT Reserva_destino_fk FOREIGN KEY
(cod_destino) REFERENCES Destino (cod_destino);
ALTER TABLE Programacion ADD CONSTRAINT Programacion_avion_fk FOREIGN KEY
(Matri_avion) REFERENCES avion (Matri_avion);
ALTER TABLE Asiento ADD CONSTRAINT Asiento_avion_fk FOREIGN KEY
(Matri_avion) REFERENCES avion (Matri_avion);
ALTER TABLE vuelo ADD CONSTRAINT vuelo_avion_fk FOREIGN KEY
(Matri_avion) REFERENCES avion (Matri_avion);
------------------------------------------------------------------------------------
-- USUARIOS ------------------
------------------------------------------------------------------------------------
show databases;
use mysql;
show full tables;
select * from user;
------------------------------------------------------------------------------------
-- ADMINISTRADOR
------------------------------------------------------------------------------------
create user 'Administrador'@'localhost' identified by 'admin';
show grants for 'Administrador'@'localhost';
grant select, insert, delete, update on t3_proyecto_g4.empresa to 'Administrador'@'localhost';
grant select, insert, delete, update on t3_proyecto_g4.empleado to 'Administrador'@'localhost';
----------------------------------------------------------------------------------
-- EMPLEADO
----------------------------------------------------------------------------------
create user 'Empleado'@'localhost' identified by 'empleado';
show grants for 'Empleado'@'localhost';
grant select, insert, delete, update on t3_proyecto_g4.programacion to 'Empleado'@'localhost';
grant select, insert, delete, update on t3_proyecto_g4.reserva to 'Empleado'@'localhost';
grant select, insert, delete, update on t3_proyecto_g4.cliente to 'Empleado'@'localhost';
grant select, insert, delete, update on t3_proyecto_g4.vuelo to 'Empleado'@'localhost';
grant select, insert, delete, update on t3_proyecto_g4.avion to 'Empleado'@'localhost';
------------------------------------------------------------------------------------
-- CLIENTE
------------------------------------------------------------------------------------
create user 'Cliente'@'localhost' identified by 'cliente';
show grants for 'Cliente'@'localhost';
grant select, insert, delete, update on t3_proyecto_g4.programacion to 'Cliente'@'localhost';
grant select, insert, delete, update on t3_proyecto_g4.reserva to 'Cliente'@'localhost';
grant select, insert, delete, update on t3_proyecto_g4.asiento to 'Cliente'@'localhost';
grant select, insert, delete, update on t3_proyecto_g4.destino to 'Cliente'@'localhost';
----------------------------------------------------------------------------------------
Comentarios
Publicar un comentario