Informe sistema de gestión Juguetería RayoMac

Proyecto bases de datos 2

Jhon Sebastian Aparicio Mesa

John fredy Paipa Lara

Diego Armando Higuera Blanco

Universidad Pedagogica y tecnologica de colombia

Seccional Sogamoso

2020

INFORME SISTEMA DE GESTIÓN JUGUETERÍA ‘RAYO MAC’

Caso de negocio:

La tienda de juguetes  RayoMac desea  implementar un sistema de almacenamiento y venta de  juguetes de colección , para ello es importante clasificar los juguetes por secciones esto con el fin de ubicarlos, dichas secciones  estarán identificadas por nombres como:  juguetes cientificos, juguetes de construcción, juguetes eléctricos, juguetes electrónicos, figurillas, muñecas, peluches, vehículos, entre otros.

Lo ideal es hacer uno de un sistema de colecciones, donde una colección está compuesta por uno o varios juguetes con características como nombre No. de piezas (el cual pueden ser juguetes dentro de la colección o accesorios que para el caso de negocio son irrelevantes y se tienen en cuenta en número pero no en características), precio, marca, para su correcta ubicación en las secciones especificadas anteriormente, estas secciones que se pueden sobreentender más como una ubicación dada por una especie de categoría, las cuales estarán identificadas por un ID y un nombre como los que vimos previamente.

para su comercialización  se debe contar con la factura del producto, dicha factura debe estar asociada a un detalle con el cliente con el fin de desglosar los productos adquiridos por el cliente, teniendo en cuenta características del cliente como su nombre, apellido, cedula, No. telefonico solo si el cliente lo desea proporcionar y un identificador que lo caracteriza como usuario dentro de la tienda.

Problema:

Con el conocimiento del caso de negocio se desea tener una base de datos, que permita tener registros en los cuales pueda facilitar la venta de los juguetes de colección, hacer registros de ventas, esto por medio de facturas y la busqueda de algun juguete dentro de las tienda, lo que nos lleva a ¿cómo podríamos realizar esta tarea de manera más fácil y efectiva?

Reglas de negocio:

• Una colección está compuesta por uno o más juguetes.
• Un juguete tiene que formar parte de una colección.
• Una marca puede tener una o muchas colecciones.
• Una colección corresponde a una sola marca.
• Una sección contiene una o muchas colecciones.
• Una colección se encuentra en una sección.
• Un detalle puede tener una o muchas colecciones.
• Una colección pertenece a un detalle.
• Una factura contiene uno o muchos detalles.
• Un detalle pertenece a una sola factura.
• Un cliente puede tener una o muchas facturas.
• Una factura debe pertenecer a un cliente.

Modelo Lógico:

[pic 1]

Modelo Relacional:

[pic 2]

Consultas:

• Realizar una consulta que nos permita conocer las facturas con su id, el id de los detalles de cada venta de cada colección dentro de cada factura donde el número de piezas que componen estas colecciones sean inferiores o iguales a dos.

• select factura_id, id, valor from Detalle where  coleccion_id < any (select id from coleccion where noPiezas<=2); Esta consulta nos permite conocer las facturas con su id, el id de los detalles de cada venta de cada colección dentro de cada factura donde el número de piezas que componen estas colecciones sean inferiores o iguales a dos.

• Realizar una consulta en la cual se obtengan todos los datos de un juguete que pertenecen al  id de la marca No. 4.

• select * from juguete where coleccion_id = any (select id from coleccion where marca_id=04); En esta consulta se nos relaciona la información acerca de los juguetes que se encuentren  bajo la colección identificada por el id de la marca No. 4.

• Realizar una consulta que muestre la identificación del cliente, la fecha, el costo total e identificador de una factura, en la cual el cliente tiene una factura.

• select c.id as id_cliente, f.id, f.costoTotal , f.fecha from factura f cross join cliente c where c.id=f.cliente_id; En esta consulta mostramos de manera resumida los valores que componen una factura tales como ID del cliente, ID de la factura, el costo total de la factura y la fecha en la que se genera la misma.

• Realizar una consulta que permita obtener todos los datos de la marca  como son el id, el nombre, y el año de fundación, además del id del cliente, el nombre del cliente donde el id de la marca y del cliente sean iguales.

• select m.id, m.nombre, extract(year from m.fechafundacion), c.id,c.nombre from marca m cross join coleccion c where m.id=c.marca_id; Esta consulta muestra la unión entre las colecciones que componen una marca y la marca, donde se nos retorna el ID, nombre, el año extraído de la fundación de la marca, el ID y nombre de la colección.

• Realizar una consulta que muestre el ID y el nombre tanto de la colección como del juguete para la cual un juguete se encuentre dentro de una colección.

• select c.id, c.nombre, j.id, j.nombre from coleccion c cross join juguete j where c.id=j.coleccion_id; Esta consulta muestra la unión entre juguetes y marcas teniendo en cuenta que un juguete se compone por una colección, retornando valores para la ID y nombre para la colección y el ID y nombre para el juguete.

• Realizar una consulta que integre los valores de id , nombre de la colección además de el id de la factura donde el id de la factura que esta en detalle sea igual al id en la factura.

• select c.id, c.nombre, d.factura_id from coleccion c cross join detalle d where c.id=d.coleccion_id and d.factura_id = any (select id from factura f); Esta consulta relaciona un ID, nombre de una colección, Id de factura, esto relacionado con el ID de la colección dentro del talle y también el ID de las facturas dentro de los detalles, siendo esta una relación donde los detalles estarán relacionados a una factura.

• realizar una consulta muestra de forma más explícita los datos de una factura donde inicialmente tendremos un nombre del cliente, un ID de factura, un ID de detalle, una cantidad comprada para cada detalle, un ID de la colección y el nombre de la colección haciendo un cross join entre el ID de la colección y el ID de las colecciones compradas en cada detalle.

• select v.nombre, v.id, v.id_detalle, v.cantidad, c.id, c.nombre from v_c_f_d v cross join coleccion c where v.coleccion_id=c.id; Consulta que nos muestra de forma más explícita los datos de una factura donde inicialmente tendremos un nombre del cliente, un ID de factura, un ID de detalle, una cantidad comprada para cada detalle, un ID de la colección y el nombre de la colección haciendo un cross join entre el ID de la colección y el ID de las colecciones compradas en cada detalle.

• Realizar una consulta  que  devuelva la suma total del costo de las ventas realizadas entre dos fechas, haciendo uso del disparador controlcambiosfactura.

• select sum(datonewc) as Total from controlcambiosfactura where fecha between '20-09-2020' and '02-10-2020'; Esta es una consulta que realiza la suma total del costo de las ventas realizadas entre dos fechas, esto haciendo uso de la tabla controlcambiosfactura la cual es una tabla de un disparador, simplificando así la consulta. select sum(datonewc) as Total from controlcambiosfactura where fecha between sysdate-10 and sysdate; en los últimos 10 dias

• Realizar una consulta que muestre el promedio de los costos de las ventas realizadas después de una fecha definida haciendo uso de la tabla controlcambiosfactura en la cual se hacen inserciones a través de un disparador.

• select fecha, avg(datonewc) as Promedio from controlcambiosfactura where fecha > '28-09-2020'; Esta consulta realiza el promedio de los costos de las ventas realizadas después de una fecha definida haciendo uso de la tabla controlcambiosfactura en la cual se hacen inserciones a través de un disparador. select fecha, avg(datonewc) as Promedio from controlcambiosfactura where fecha > '28-09-2020'; ultimo mes

• Realizar una consulta haciendo uso de la tabla controlcambiosfactura para mostrar la sumatoria de las ventas realizadas por día en los cuales se realizaron ventas.

• select fecha, sum(datonewc) as Total from controlcambiosfactura group by fecha; Consulta que por medio de la tabla controlcambiosfactura realiza la suma por la fecha en la que se realizaron las ventas, permitiendo así tener un control por días del costo total de ventas.

• Realizar una consulta permita saber el número de clientes creados dentro de una misma fecha por medio de la tabla controlcambioscliente, que se genera a través de un disparador.

• select fecha, count(*) as No_Clientes from controlcambioscliente group by fecha; Consulta que nos permite saber el número de clientes creados dentro de una misma fecha por medio de la tabla controlcambioscliente, que se genera a través de un disparador.

• Realizar una consulta que permita onocer las colecciones que se han sacado del mercado dentro de la tienda entre determinadas fechas haciendo uso de la tabla controlcambioscoleccion siendo esta una tabla de un disparador.

• select datoold, fecha as Nombre from controlcambioscoleccion where fecha between '20-09-2020' and '01-10-2020'; Esta consulta nos permite conocer las colecciones que se han sacado del mercado dentro de la tienda entre determinadas fechas haciendo uso de la tabla controlcambioscoleccion siendo esta una tabla de un disparador.

Vistas:

• Hacer una vista que permita almacenar u obtener los datos principales como nombre y apellido de cada cliente que tenga facturas y de cada una de estas mostrar ID, fecha y total de cada factura que tenga el cliente.

• CREATE or REPLACE VIEW v_Venta_Factura_Cliente as select c.nombre,c.apellido,f.fecha,f.costoTotal, f.id from cliente c cross join factura f where f.cliente_id=c.id; Esta es una vista que nos permite hacer inmediata la una unión entre el cliente con las diferentes facturas que puede llegar a tener registradas dentro del sistema guardando valores como su nombre, apellido, fecha de compra, costo total y finalmente el id de la factura.

• Realizar una vista en la cual se puedan visualizar todas las secciones que tienen colecciones con sus datos principales.

• CREATE or REPLACE VIEW v_Ubicacion_Coleccion as select c.id as id_Coleccion,c.nombre as nombre_Coleccion, s.id as id_seccion, s.nombre as nombre_Seccion from coleccion c cross join seccion s where s.id=c.seccion_id; Esta vista nos permite visualizar de manera más fácil la unión que se da dentro de la colección, teniendo en cuenta que una colección la podemos encontrar dentro de una sección, donde guardamos datos tales como el ID y nombre de la sección, también el ID y nombre de la colección.

• Realizar una vista en la que se puedan ver el ID y el número de piezas que tiene una colección, para las colecciones que cuentan con una disponibilidad o una cantidad de piezas entre 2 y 5 piezas.

• CREATE or REPLACE VIEW view v_Coleccion as select id, noPiezas from coleccion where noPiezas between 2 and 5; Esta vista facilita de manera más fácil ver las colecciones que están compuestas por 2 a 5 piezas, está con el conocimiento de que los clientes se interesan por colecciones que contengan más de una pieza pero menos de seis ya que pueden llegar a ser demasiadas.

• CREATE or REPLACE VIEW v_Coleccion_Fact_Clien as select c.id, c.nombre, d.factura_id from coleccion c cross join detalle d where c.id=d.coleccion_id and d.factura_id = any (select id from factura f); Esta es una vista que relaciona un ID, nombre de una colección, Id de factura, esto relacionado con el ID de la colección dentro del talle y también el ID de las facturas dentro de los detalles, siendo esta una relación donde los detalles estarán relacionados a una factura.

• CREATE or REPLACE VIEW v_Venta_Factura_Cliente_2 as select c.nombre,c.apellido,f.fecha,f.costoTotal, f.id from cliente c cross join factura f where f.cliente_id=c.id; Esta vista muestra el nombre, apellido del cliente con la fecha en que se genera la factura, con su costo total y su ID, en la cual coincide el ID del cliente que está dentro de cada factura.

• CREATE or REPLACE VIEW v_c_f_d as select v.nombre||' '||v.apellido as Nombre, v.id, d.id as id_detalle, d.cantidad, d.coleccion_id from v_venta_factura_cliente v cross join detalle d where  v.id=d.factura_id; Esta vista haciendo uso de la vista creada anteriormente (v_Venta_Factura_Cliente) para poder relacionar el nombre del cliente con el ID de la factura y el ID, cantidad de compras dentro del Detalle y el ID de la colección que fue comprada para cada detalle, haciendo una unión entre la vista v_Venta_Factura_Cliente en su parte ID de la factura y los detalles de cada factura.

Triggers:

• CREATE OR REPLACE TRIGGER tr_eliminar_colecciones

    before DELETE on coleccion

    for each row

    begin

      INSERT INTO controlCambiosColeccion VALUES(

      user,

      sysdate,

      :old.nombre,

      null

      );

end tr_eliminar_colecciones;

/

Descripción: Bajo el uso de una tabla de control de cambios en la tabla colección llamada controlCambiosColección para almacenar los datos que sean eliminados dentro de la tabla colección, guardando el usuario quien realiza esta eliminación, la fecha en la que esta fue realizada y el nombre de la colección que se ha eliminado. Esto con el fin de saber que juguetes han sido sacados de la base de datos y en sí de la tienda para así no ofertar elementos no existentes.