Jugando con tablas normalizadas y SQL

El otro día estábamos jugando a crear tablas normalizadas e instrucciones SQL y lo que resultó fue un ejemplo de diseñar tablas normalizadas (escribí algo acerca de la normalización de bases de datos que puedes leer aquí) y el uso de instrucciones SQL para usarlas. Por esta razón, publico aquí el ejercicio y la mejor solución que surgió esperando que te sea útil.

Antes que nada, este es el problema planteado:

Crear las tablas necesarias para almacenar los datos que necesita una tienda de autoservicio. El programa es de punto de venta, que controlará el inventario de producto, clientes que han solicitado facturas, recibos (ventas) y facturas.

Los datos (mínimos) que se tienen que almacenar son:

• IdProducto – Número que identifica de manera única cada producto
• Descripcion – Descripción del producto
• PrecioCompra – El precio al que te venden el producto.
• Min – Cantidad mínima que necesitas tener en el almacén. Si llegas a este valor, es necesario hacer otro pedido.
• Max – Cantidad máxima que puedes almacenar de un producto.
• Cantidad – Cantidad de producto que tienes actualmente.
• PrecioVenta – Precio al que vendes el producto.
• Proveedor – Nombre del proveedor que te surte un producto.
• Tel – Teléfono del proveedor.
• EMail – I-Meil del proveedor.
• Cliente – Nombre del cliente.
• RFC – del cliente.
• Domicilio – del cliente.
• CP – Código postal del cliente.
• FechaVenta – Fecha en que se hizo la venta.
• NoRecibo – El número que identifica un recibo.
• NoFactura – Número de factura que se hizo de un recibo (si es que se hizo una fectura de ese recibo).

Ten en cuenta algunas de las funciones que tendrá el programa:

• Emite recibos: almacena el número de recibo, la fecha en que se hizo la venta, la lista de productos que se vendieron (producto, cantidad, precio del producto ) y el número de factura, si es que el usuario solicitó una factura de este recibo.
• Control de inventario: conforme se vayan vendiendo productos en la tienda, se va a ir ajustando la cantidad que se tiene en el almacén. Cuando llegue al tope mínimo, deberá mandar un aviso para que hagan un pedido de ese material y que el pedido (más lo que se tiene) no supere el límite máximo.
• Facturación: que lleve un registro de las facturas que se emitieron. Se tiene que registrar que factura corresponde a qué recibo y a qué cliente.
• Reportes: se necesitarán reportes de existencias en inventario, de lo que se ha vendido (reportes financieros) y uno que otro más.

Además de esto, debes guardar instrucciones SQL que hagan lo siguiente:

1. Los productos que se tienen en inventario (descripción, cantidad en inventario, precio compra y precio de venta, ordenado por la descripción).

2. La lista de los clientes (RFC, Nombre, Apellidos y Teléfono, ordenado por apellido paterno, luego por apellido materno y luego por nombre).

3. Nombres de todos los clientes cuyo apellido paterno comience con “Men” o su RFC contenga la cadena “2011”.

4. Listado de todos los recibos que contengan el producto 2 o el 9 y que se hayan comprado en el mes de septiembre de cualquier año.

5. Listado de todos los productos que contengan la cadena “coca”, “sprite” o “fanta” en la descripción. Ordénalos por descripción de forma descendente y luego por precio de venta.

6. Dar de alta un recibo de compra: número de recibo, fecha y 10 artículos que compró.

Este es el problema. Ahora la onda es ver cómo solucionarlo. Comencemos por el diseño de las tablas. No voy a hacer un repaso de las formas normales (que, como dije antes, puedes ver aquí), pero solo escribir como quedarían. Pongo un signo # antes del campo que sería la llave primaria de la tabla.

Tabla: Productos
Aquí se guardan los datos de los productos

#IdProducto
Descripcion
Min
Max
Cant
PrecioCompra
PrecioVenta
IdProveedor (esto lo pongo solamente si cada producto tiene un solo proveedor. si no hay que hacer otra tabla que ilustro más adelante en la tabla ProveedorProducto)

Tabla: Proveedores
Aquí se guardan los datos de los proveedores

#IdProveedor
Nombre
Tel
EMail

Tabla: Clientes
Aquí se guardan los datos de los clientes

#RFC
Nombre
ApellidoP
ApellidoM
Domicilio
CP

Tabla: Recibos
Aquí se guardan los datos generales de los recibos y facturas, si la han hecho de este recibo

#NoRecibo
Fecha
NoFactura (en caso que no haya factura de este recibo, este campo tiene 0 y el que sigue no le pongo valor, es nulo) RFCCliente

Tabla: ReciboProducto
Aquí se guardan los productos que se compraron en cada recibo

#NoRecibo
#NoProducto
Cant
Precio

Tabla: ProveedorProducto
En caso de que más de un proveedor puede proveer un producto, aquí se guarda quien provee qué

#IdProveedor
#IdProducto

Ahora van las instrucciones SQL. Como las consultas van enumeradas, voy a poner primero el numero y en el siguiente renglón la instrucción SQL correspondiente:

1.
SELECT Descripcion,Cant,PrecioCompra,PrecioVenta
FROM Productos
ORDER BY Descripcion

2.
SELECT RFC,Nombre,ApP,ApM,Tel
FROM Clientes
ORDER BY ApP,ApM,Nombre

3.
SELECT RFC,Nombre,ApP,ApM
FROM Clientes
WHERE ApP LIKE "Men*" OR RFC LIKE "*2011*"

4.
SELECT DISTINCT NoRec
FROM ReciboProducto
WHERE NoProd=2 OR NoProd=9

5.
SELECT Descripcion
FROM Productos WHERE Descripcion LIKE "*coca*" OR Descripcion LIKE "*sprite*" OR Descripcion LIKE "*fanta*" ORDER BY Descripcion DESC,PrecioVenta

6.
En primer lugar, inserto los datos generales a la tabla Recibos. Como puedes ver, estoy omitiendo la información de la factura:
INSERT INTO Recibos (NoRec,Fecha) VALUES (1,"28/Feb/08")

Ahora todos estos dan de alta los 10 productos que ampara este recibo (en nuestor caos, el recibo número 1):
INSERT INTO ReciboProducto (NoRec,NoProd,Cant,Precio) VALUES (1,1,5,3.5)
INSERT INTO ReciboProducto (NoRec,NoProd,Cant,Precio) VALUES (1,3,1,13)
INSERT INTO ReciboProducto (NoRec,NoProd,Cant,Precio) VALUES (1,9,1,25.95)
INSERT INTO ReciboProducto (NoRec,NoProd,Cant,Precio) VALUES (1,25,1,14.5)
INSERT INTO ReciboProducto (NoRec,NoProd,Cant,Precio) VALUES (1,7,2,3.75)
INSERT INTO ReciboProducto (NoRec,NoProd,Cant,Precio) VALUES (1,2,1,5)
INSERT INTO ReciboProducto (NoRec,NoProd,Cant,Precio) VALUES (1,10,1,4)
INSERT INTO ReciboProducto (NoRec,NoProd,Cant,Precio) VALUES (1,13,2,8.5)
INSERT INTO ReciboProducto (NoRec,NoProd,Cant,Precio) VALUES (1,7,1,50)
INSERT INTO ReciboProducto (NoRec,NoProd,Cant,Precio) VALUES (1,24,3,30)

Espero que esto te ayude. Si no te queda claro, no dudes en dejar un mensaje. ¡Hasta la próxima!