Final

Extraído de: https://sistemasremington.webnode.com/base-de-datos/proyecto-final/

Enunciado:

Se va a sistematizar una base de datos de mensajería donde el cliente va a tener la mayor facilidad de poder mandar un paquete o documento a cualquier parte de la ciudad o municipios aledaños a la ciudad de medellín.

La base de datos va a tener las siguientes tablas:

Diagrama Relacional:

En este archivo de excel esta el modelo entidad relación, Diagrama Relacional y el diccionario de datos

DESCARGAR Final.xlsx

Comandos utilizados:

-Crear tablas a la base de datos:

tabla compania:

create table Compania(IdMensajeria int not null primary key,Nit varchar(15) not null,Nombre Varchar(150) not null,Direccion varchar(150) not null,Telefono varchar(20) not null);

Create table Cliente(ClienteId int not null primary key,Documento varchar(20) not null,Nombre Varchar(100) not null,Apellido Varchar(100) not null,Sexo boolean not null,Telefono varchar(20) not null,Estado boolean not null);

Create table Mensajero(MensajeroId varchar(3) not null primary key,Nombre Varchar(50) Not null,Direccion varchar(50) not null,Telefono varchar(20) not null);

create table TipoTrayecto(TipoId varchar(3) NOT NULL PRIMARY KEY,PuntoAPunto Varchar(50) NOT NULL,ValoraPagar int(50) not null);

create table ContactoDireccion(ClienteId int,TipoId varchar(3) NOT NULL, Telefono Varchar(20) NOT NULL, Direccion Varchar(250) NOT NULL, MunicipioId Varchar(10) NOT NULL,

-> FOREIGN KEY (ClienteId) References Cliente (ClienteId) ON DELETE CASCADE ON UPDATE CASCADE,

-> FOREIGN KEY (TipoId) REFERENCES TipoTrayecto (TipoId) ON DELETE CASCADE ON UPDATE CASCADE);

create table AplicacionMovil(MensajeriaId int not null, ClienteId int, MensajeroId varchar(3) NOT NULL,

-> FOREIGN KEY (MensajeriaId) References compania (MensajeriaId) ON DELETE CASCADE ON UPDATE CASCADE,

-> FOREIGN KEY (ClienteId) References Cliente (ClienteId) ON DELETE CASCADE ON UPDATE CASCADE,

-> FOREIGN KEY (MensajeroId) References Mensajero (MensajeroId) ON DELETE CASCADE ON UPDATE CASCADE);

Ver tablas creadas:

-describe compania;

-describe cliente;

-describe mensajero;

-describe Tipotrayecto;

-describe AplicacionMovil

-describe ContactoDireccion;

Insertar Registros a las tablas:

insert into compania(MensajeriaId, Nit, Nombre, Direccion, Telefono) values (1001, '1028038096', 'TurboBoy', 'Cra 39a poblado', '3146395342');

insert into compania(MensajeriaId, Nit, Nombre, Direccion, Telefono) values (1002, '115234352', 'Rappi', 'Cra 40 VillaHermosa', '8285070');

insert into compania(MensajeriaId, Nit, Nombre, Direccion, Telefono) values (1003, '899577528', 'UberEats', 'Cra 41 itagui', '4445030');

insert into compania(MensajeriaId, Nit, Nombre, Direccion, Telefono) values (1004, '71934074', 'FatigaAple', 'Cra 42 envigado', '4421020');

insert into compania(MensajeriaId, Nit, Nombre, Direccion, Telefono) values (1005, '1025035095', 'Domicilios.com', 'Cra 42 sabaneta', '4531520');

insert into compania(MensajeriaId, Nit, Nombre, Direccion, Telefono) values (1006, '89632587', 'Envia', 'Cra 43 la estrella', '3136657726');

insert into compania(MensajeriaId, Nit, Nombre, Direccion, Telefono) values (1007, '74185296', 'Alejo-envia', 'Cra 44 calasanz', '3507398461');

insert into compania(MensajeriaId, Nit, Nombre, Direccion, Telefono) values (1008, '654789321', 'Envia-Colombia', 'Cra 45 Robledo', '317852369');

insert into compania(MensajeriaId, Nit, Nombre, Direccion, Telefono) values (1009, '96325874', 'Union-asocio', 'Cra 46 Laureles ', '4426050');

insert into compania(MensajeriaId, Nit, Nombre, Direccion, Telefono) values (1010, '39409859', 'El mundo de envia', 'Cra 47 Boston', '4445031');

Tabla cliente:

Insert into cliente(ClienteId, Documento, Nombre, Apellido, Sexo, Telefono, Estado) Values (001, '71934078', 'Armando', 'Casas', 1, '315852963', 1);

Insert into cliente(ClienteId, Documento, Nombre, Apellido, Sexo, Telefono, Estado) Values (002, '71935896', 'Armando', 'Puerta', 1, '313698521', 1);

Insert into cliente(ClienteId, Documento, Nombre, Apellido, Sexo, Telefono, Estado) Values (003, '39405826', 'Laura', 'Villegas', 0, '8254014', 1);

Insert into cliente(ClienteId, Documento, Nombre, Apellido, Sexo, Telefono, Estado) Values (004, '1028038096', 'Alejandro', 'Guevara', 1, '3146395342', 1);

Insert into cliente(ClienteId, Documento, Nombre, Apellido, Sexo, Telefono, Estado) Values (005, '102503596', 'Hector', 'Giraldo', 1, '3116181148', 1);

Insert into cliente(ClienteId, Documento, Nombre, Apellido, Sexo, Telefono, Estado) Values (006, '115289635', 'Leidy', 'Garcez', 0, '3116823164', 1);

Insert into cliente(ClienteId, Documento, Nombre, Apellido, Sexo, Telefono, Estado) Values (007, '102803456', 'Juan David', 'Perez', 1, '3188551624', 0);

Insert into cliente(ClienteId, Documento, Nombre, Apellido, Sexo, Telefono, Estado) Values (008, '102935645', 'Juan Pablo', 'Zuluaga', 1, '314785236', 1);

Insert into cliente(ClienteId, Documento, Nombre, Apellido, Sexo, Telefono, Estado) Values (009, '71358963', 'Juliana ', 'Montoya', 0, '31589634', 1);

Insert into cliente(ClienteId, Documento, Nombre, Apellido, Sexo, Telefono, Estado) Values (010, '102236548', 'Paula ', 'Garcia', 0, '32172589', 0);

Tabla Mensajero:

Insert into Mensajero(MensajeroId, Nombre, Direccion, Telefono) Values (101, 'Alejandro', 'Calle 1 Manrique', '3146395342');

Insert into Mensajero(MensajeroId, Nombre, Direccion, Telefono) Values (102, 'Luis Zuluaga', 'Calle 2 Robledo', '3507398491');

Insert into Mensajero(MensajeroId, Nombre, Direccion, Telefono) Values (103, 'Juan Pablo Bedoya', 'Calle 3 Palmas', '316963258');

Insert into Mensajero(MensajeroId, Nombre, Direccion, Telefono) Values (104, 'Pablo Andres Perez', 'Calle 4 San lucas', '320741356');

Insert into Mensajero(MensajeroId, Nombre, Direccion, Telefono) Values (105, 'Oliva Gonzalez', 'Calle 5 poblado', '317852369');

Insert into Mensajero(MensajeroId, Nombre, Direccion, Telefono) Values (106, 'Juliana Gutierrez', 'Calle 6 Santo Domingo', '3127148258');

Insert into Mensajero(MensajeroId, Nombre, Direccion, Telefono) Values (107, 'Juliana Gutierrez', 'Calle 7 Santo Lucia', '312745632');

Insert into Mensajero(MensajeroId, Nombre, Direccion, Telefono) Values (108, 'Mariana Escobar', 'Calle 8 Santo Javier', '311456987');

Insert into Mensajero(MensajeroId, Nombre, Direccion, Telefono) Values (109, 'Diana Mejia', 'Calle 9 Itagui', '3178965412');

Insert into Mensajero(MensajeroId, Nombre, Direccion, Telefono) Values (110, 'Pedro Giraldo', 'Calle 10 Envigado', '319632584');

Tabla tipotrayecto:

Nota: De la tabla tipotrayecto borre el campo valor a pagar porque lo había definido como entero

Nota: volvi agregar el campo valor a pagar como float

Insert into Tipotrayecto(TipoId, PuntoApunto, ValoraPagar) values (201, 'Medellin-Medellin', '7500');

Insert into Tipotrayecto(TipoId, PuntoApunto, ValoraPagar) values (202, 'Medellin-Niquia', '10000');

MariaDB [mensajeria]> Insert into Tipotrayecto(TipoId, PuntoApunto, ValoraPagar) values (203, 'Medellin-Bello', '10000');

Insert into Tipotrayecto(TipoId, PuntoApunto, ValoraPagar) values (204, 'Medellin-Itagui', '11000');

Insert into Tipotrayecto(TipoId, PuntoApunto, ValoraPagar) values (204, 'Medellin-Envigado', '11000');

Insert into Tipotrayecto(TipoId, PuntoApunto, ValoraPagar) values (205, 'Medellin-Envigado', '11000');

Insert into Tipotrayecto(TipoId, PuntoApunto, ValoraPagar) values (206, 'Medellin-Sabaneta', '13000');

Insert into Tipotrayecto(TipoId, PuntoApunto, ValoraPagar) values (207, 'Medellin-La Estrella', '15000');

Tabla contactodireccion:

insert into contactodireccion(ClienteId, TipoId, Telefono, Direccion, MunicipioId) values (4, '205', '3146395342', 'Medellin-Envigado', 'Envigado');

insert into contactodireccion(ClienteId, TipoId, Telefono, Direccion, MunicipioId) values (5, '206', '3116181148', 'Medellin-Estrella', 'Estrella');

insert into contactodireccion(ClienteId, TipoId, Telefono, Direccion, MunicipioId) values (6, '207', '3116823164', 'Medellin-Estrella', 'Estrella');

insert into contactodireccion(ClienteId, TipoId, Telefono, Direccion, MunicipioId) values (8, '202', '314785236', 'Medellin-Niquia', 'Niquia');

Tabla aplicacion Movil:

insert into AplicacionMovil(mensajeriaId, ClienteId, MensajeroId) values (1005, 5, 110);

insert into AplicacionMovil(mensajeriaId, ClienteId, MensajeroId) values (1007, 4, 108);

insert into AplicacionMovil(mensajeriaId, ClienteId, MensajeroId) values (1002, 2, 102);

insert into AplicacionMovil(mensajeriaId, ClienteId, MensajeroId) values (1003, 3, 107);

insert into AplicacionMovil(mensajeriaId, ClienteId, MensajeroId) values (1004, 9, 109);

insert into AplicacionMovil(mensajeriaId, ClienteId, MensajeroId) values (1008, 6, 110);

insert into AplicacionMovil(mensajeriaId, ClienteId, MensajeroId) values (1010, 8, 101);

Comando update:

actualizar la direccion dada por el cliente con el codigo=5

tablas a consultar: contactodireccion

campos a modificar: Direccion= Medellin-Estrella

condicion: clienteId=5

sintaxis:

update contactodireccion set direccion= 'Medellin-Sabaneta' where ClienteId=5;

Comando update:

actualizar el municipio dada por el cliente con el codigo=5

tablas a consultar: contactodireccion

campos a modificar: Municipio= sabaneta

condicion: clienteId=5

sintaxis:

update contactodireccion set MunicipioId= 'Sabaneta' where ClienteId=5;

Vista:

crear una vista que me muestre los clientes, sus direcciones y telefonos

sintaxis:

CREATE VIEW vContactoDir As SELECT c.clienteId,c.Nombre,c.Apellido,d.tipoId,d.telefono,d.direccion from cliente c left join contactodireccion d on d.clienteId=c.clienteId;

Borrar vista:

sintaxis:

DROP VIEW vContactoDir;

Procedimientos almacenados:

Crear procedimiento almacenado que retorne los clientes y sus direcciones y teléfonos.

sintaxis:

Delimiter //

Create procedure spClienteDir(in _clienteId int)

-> begin

-> select c.clienteId,c.nombre,c.apellido,d.tipoId,d.telefono,d.direccion from cliente c left join contactodirecciond on d.clienteId=c.clienteId where c.clienteId=_clienteId;

-> end

-> //

delimiter ;