Diseño y Consultas SQL Esenciales para Gestión de Ferreterías

Clasificado en Informática

Escrito el en español con un tamaño de 15,24 KB

Introducción al Esquema de Base de Datos para Ferreterías

Este documento presenta el diseño de una base de datos relacional para la gestión de una ferretería, incluyendo la definición de su esquema (DDL) y una serie de consultas SQL (DML) para la manipulación y recuperación de datos. El objetivo es proporcionar una estructura robusta para el almacenamiento de información sobre clientes, productos, proveedores, empleados y transacciones.

Configuración Inicial de la Base de Datos

Para comenzar, se crea la base de datos dbferreteria y se selecciona para su uso.

CREATE DATABASE dbferreteria;
GO
USE dbferreteria;
GO

Definición de Tipos de Datos Personalizados

Se define un tipo de dato personalizado llamado codigo, que se utilizará para los identificadores principales y foráneos en las tablas, asegurando consistencia y no nulabilidad.

EXEC sp_addtype 'codigo', 'bigint', 'not null';
GO

Creación de Tablas del Esquema

A continuación, se detallan las sentencias DDL para la creación de cada tabla, incluyendo sus columnas, tipos de datos, claves primarias y relaciones con otras tablas (claves foráneas).

Tabla: Zonas

Almacena información sobre las diferentes zonas geográficas.

CREATE TABLE zona(
idzona codigo PRIMARY KEY,
nombre_zona VARCHAR(50) NOT NULL
);
GO

Tabla: Barrios

Contiene los barrios, asociados a una zona específica.

CREATE TABLE barrio(
idbarrio codigo PRIMARY KEY,
nombre_barrio VARCHAR(70) NOT NULL,
idzona codigo REFERENCES zona(idzona)
);
GO

Tabla: Clientes

Tabla base para todos los clientes, sean naturales o empresas.

CREATE TABLE cliente(
id_cliente codigo PRIMARY KEY,
nit codigo,
telefono1 codigo,
telefono2 bigint,
telefono3 bigint,
correo VARCHAR(70) NOT NULL,
idbarrio codigo REFERENCES barrio(idbarrio)
);
GO

Tabla: Clientes Naturales

Detalles específicos para clientes que son personas naturales.

CREATE TABLE natural(
id_natural codigo PRIMARY KEY REFERENCES cliente(id_cliente),
nombre_cliente VARCHAR(70) NOT NULL,
app VARCHAR(70) NOT NULL,
apm VARCHAR(70) NOT NULL,
f_nac datetime NOT NULL,
f_def datetime,
sexo VARCHAR(1) NOT NULL
);
GO

Tabla: Clientes Empresa

Detalles específicos para clientes que son empresas.

CREATE TABLE empresa(
id_empresa codigo PRIMARY KEY REFERENCES cliente(id_cliente),
representante_legal VARCHAR(90)
);
GO

Tabla: Categorías de Productos

Clasificación principal de los productos.

CREATE TABLE categoria(
id_categoria codigo PRIMARY KEY,
nombre_categoria VARCHAR(70)
);
GO

Tabla: Subcategorías de Productos

Clasificación secundaria de los productos, dependiente de una categoría.

CREATE TABLE sub_categoria(
id_sub_categoria codigo PRIMARY KEY,
nombre_sub_categoria VARCHAR(70),
id_categoria codigo REFERENCES categoria(id_categoria)
);
GO

Tabla: Cargos de Empleados

Define los diferentes puestos de trabajo dentro de la ferretería.

CREATE TABLE cargo(
id_cargo codigo PRIMARY KEY,
nombre_cargo VARCHAR(70)
);
GO

Tabla: Empleados

Información detallada de los empleados.

CREATE TABLE empleado(
id_empleado codigo PRIMARY KEY,
nombre_empleado VARCHAR(70) NOT NULL,
app VARCHAR(70) NOT NULL,
apm VARCHAR(70) NOT NULL,
telefono1 bigint NOT NULL,
direccion VARCHAR(70) NOT NULL,
correo VARCHAR(70) NOT NULL,
id_cargo codigo REFERENCES cargo(id_cargo)
);
GO

Tabla: Pedidos de Clientes

Registra los pedidos realizados por los clientes.

CREATE TABLE pedido_cliente(
id_pedido codigo PRIMARY KEY,
fecha_pedido datetime NOT NULL,
id_cliente codigo REFERENCES cliente(id_cliente),
id_empleado codigo REFERENCES empleado(id_empleado)
);
GO

Tabla: Marcas de Productos

Información sobre las marcas de los productos.

CREATE TABLE marca(
id_marca codigo PRIMARY KEY,
nombre_marca VARCHAR(70) NOT NULL
);
GO

Tabla: Proveedores

Tabla base para todos los proveedores, sean naturales o empresas.

CREATE TABLE proveedor(
id_proveedor codigo PRIMARY KEY,
nit codigo,
direccion VARCHAR(70) NOT NULL,
telefono1 codigo,
telefono2 bigint,
correo VARCHAR(70) NOT NULL
);
GO

Tabla: Proveedores Naturales

Detalles específicos para proveedores que son personas naturales.

CREATE TABLE natural_proveedor(
id_proveedor codigo PRIMARY KEY REFERENCES proveedor(id_proveedor),
nombre_proveedor VARCHAR(70) NOT NULL,
app VARCHAR(70) NOT NULL,
apm VARCHAR(70) NOT NULL,
CI bigint NOT NULL
);
GO

Tabla: Proveedores Empresa

Detalles específicos para proveedores que son empresas.

CREATE TABLE empresa_proveedor(
id_proveedor codigo PRIMARY KEY REFERENCES proveedor(id_proveedor),
representante_legal VARCHAR(100) NOT NULL
);
GO

Tabla: Pedidos a Proveedores

Registra los pedidos realizados a los proveedores.

CREATE TABLE pedido_proveedor(
id_pedidoprov codigo PRIMARY KEY,
fecha_pedido datetime NOT NULL,
id_proveedor codigo REFERENCES proveedor(id_proveedor)
);
GO

Tabla: Productos

Información detallada de los productos disponibles en la ferretería.

CREATE TABLE producto(
id_producto codigo PRIMARY KEY,
nombre_producto VARCHAR(70) NOT NULL,
procedencia VARCHAR(70) NOT NULL,
peso decimal(18,2) NOT NULL,
stock bigint NOT NULL,
caracteristica VARCHAR(500),
id_marca codigo REFERENCES marca(id_marca),
id_subcategoria codigo REFERENCES sub_categoria(id_sub_categoria)
);
GO

Tabla: Detalle de Pedidos de Clientes

Contiene los productos y cantidades de cada pedido de cliente.

CREATE TABLE detalle_pedido(
id_pedido codigo REFERENCES pedido_cliente(id_pedido),
id_producto codigo REFERENCES producto(id_producto),
CONSTRAINT pk_detalle_pedido PRIMARY KEY(id_pedido,id_producto),
cantidad bigint NOT NULL,
precio_unitario decimal(18,2) NOT NULL
);
GO

Tabla: Detalle de Pedidos a Proveedores

Contiene los productos y cantidades de cada pedido a proveedor.

CREATE TABLE detalle_pedido_prove(
id_pedidoprov codigo REFERENCES pedido_proveedor(id_pedidoprov),
id_producto codigo REFERENCES producto(id_producto),
CONSTRAINT pk_detalle_pedido_prove PRIMARY KEY(id_pedidoprov,id_producto),
cantidad bigint NOT NULL,
precio_unitario decimal(18,2) NOT NULL
);
GO

Consultas SQL para la Gestión de Datos

Esta sección presenta una serie de consultas SQL (DML) para extraer información relevante de la base de datos de la ferretería. Cada consulta está diseñada para responder a necesidades específicas de negocio.

  1. Listado de Todas las Zonas

    Obtiene todos los registros de la tabla zona. Se muestran dos formas equivalentes de realizar esta consulta.

    SELECT * FROM zona;
SELECT z.* FROM zona z;

  2. Listado de Clientes Naturales con Edad y NIT

    Muestra el ID, nombre completo, NIT y la edad aproximada de cada cliente natural.

    SELECT c.id_cliente, (n.nombre_cliente + ' ' + n.app + ' ' + n.apm) AS nombre_cliente, c.nit, DATEDIFF(d, n.f_nac, GETDATE())/365 AS edad
FROM cliente c
INNER JOIN natural n ON c.id_cliente = n.id_natural;

  3. Listado de Clientes Empresa con Representante Legal, NIT y Dirección

    Obtiene el ID, representante legal, NIT, barrio y zona de los clientes que son empresas.

    SELECT c.id_cliente, e.representante_legal AS Representante_Legal, c.nit AS NIT, b.nombre_barrio AS Barrio, z.nombre_zona AS Zona
FROM cliente c
INNER JOIN empresa e ON c.id_cliente = e.id_empresa
INNER JOIN barrio b ON c.idbarrio = b.idbarrio
INNER JOIN zona z ON b.idzona = z.idzona;

  4. Listado de Clientes Naturales Fallecidos

    Identifica a los clientes naturales que tienen registrada una fecha de defunción.

    SELECT c.id_cliente, (n.nombre_cliente + ' ' + n.app + ' ' + n.apm) AS nombre_cliente
FROM cliente c
INNER JOIN natural n ON c.id_cliente = n.id_natural
WHERE n.f_def IS NOT NULL;

  5. Listado de Clientes Naturales Vivos

    Muestra los clientes naturales que no tienen registrada una fecha de defunción.

    SELECT c.id_cliente, (n.nombre_cliente + ' ' + n.app + ' ' + n.apm) AS nombre_cliente
FROM cliente c
INNER JOIN natural n ON c.id_cliente = n.id_natural
WHERE n.f_def IS NULL;

  6. Clientes Naturales Mayores de 18 Años

    Filtra los clientes naturales cuya edad calculada es igual o superior a 18 años.

    SELECT c.id_cliente, (n.nombre_cliente + ' ' + n.app + ' ' + n.apm) AS Nombre_Cliente, DATEDIFF(d, n.f_nac, GETDATE())/365 AS Edad
FROM cliente c
INNER JOIN natural n ON c.id_cliente = n.id_natural
WHERE DATEDIFF(d, n.f_nac, GETDATE())/365 >= 18;

  7. Productos Cuyo Nombre Empieza con 'c' y Termina con 'a'

    Busca productos cuyos nombres cumplen con un patrón específico.

    SELECT p.* FROM producto p WHERE p.nombre_producto LIKE 'c%a';

  8. Proveedores Naturales Llamados 'Joaquin Perez'

    Encuentra proveedores naturales con un nombre y apellido específicos.

    SELECT p.id_proveedor, (n.nombre_proveedor + ' ' + n.app + ' ' + n.apm) AS Nombre_Proveedor, n.CI
FROM proveedor p
INNER JOIN natural_proveedor n ON p.id_proveedor = n.id_proveedor
WHERE n.nombre_proveedor = 'Joaquin'
AND n.app = 'Perez';

  9. Marcas Cuyo Nombre Empieza con 'tr' y Termina con 'a'

    Filtra marcas que siguen un patrón de nombre determinado.

    SELECT m.* FROM marca m WHERE m.nombre_marca LIKE 'tr%a';

  10. Empleados con Cargo entre ID 1 y 2

    Selecciona empleados cuyo ID de cargo se encuentra en un rango específico.

    SELECT e.* FROM empleado e
WHERE e.id_cargo BETWEEN 1 AND 2;

  11. Productos con Nombres Específicos

    Obtiene productos que coinciden con una lista predefinida de nombres.

    SELECT p.* FROM producto p WHERE p.nombre_producto IN('cuchillo de mesa', 'pala honda', 'fierro de 2 pulgadas');

  12. Cantidad Total de Productos

    Calcula el número total de productos registrados en la base de datos.

    SELECT COUNT(p.id_producto) AS cantidad FROM producto p;

  13. Cliente Natural Más Adulto

    Identifica al cliente natural con la mayor edad registrada.

    SELECT TOP 1 c.id_cliente, (n.nombre_cliente + ' ' + n.app + ' ' + n.apm) AS Nombre_Cliente, DATEDIFF(d, n.f_nac, GETDATE())/365 AS Edad
FROM cliente c
INNER JOIN natural n ON c.id_cliente = n.id_natural
ORDER BY Edad DESC;

  14. Cliente Natural Más Joven

    Identifica al cliente natural con la menor edad registrada.

    SELECT TOP 1 c.id_cliente, (n.nombre_cliente + ' ' + n.app + ' ' + n.apm) AS Nombre_Cliente, DATEDIFF(d, n.f_nac, GETDATE())/365 AS Edad
FROM cliente c
INNER JOIN natural n ON c.id_cliente = n.id_natural
ORDER BY Edad ASC;

  15. Clientes Naturales que Compraron en Febrero (Año 2011)

    Lista los clientes naturales que realizaron pedidos en el mes de febrero de 2011.

    SELECT c.id_cliente, (n.nombre_cliente + ' ' + n.app + ' ' + n.apm) AS nombre_cliente_natural, p.id_pedido
FROM cliente c
INNER JOIN natural n ON c.id_cliente = n.id_natural
INNER JOIN pedido_cliente p ON c.id_cliente = p.id_cliente
WHERE p.fecha_pedido BETWEEN '2011-02-01' AND '2011-02-28';

  16. Clientes Empresa que Compraron en Mayo (Año 2011)

    Lista los clientes empresa que realizaron pedidos en el mes de mayo de 2011.

    SELECT c.id_cliente, e.representante_legal AS nombre_cliente_empresa, p.id_pedido
FROM cliente c
INNER JOIN empresa e ON c.id_cliente = e.id_empresa
INNER JOIN pedido_cliente p ON c.id_cliente = p.id_cliente
WHERE p.fecha_pedido BETWEEN '2011-05-01' AND '2011-05-31';

  17. Clientes Naturales Sin Pedidos (Uso de LEFT JOIN)

    Encuentra clientes naturales que no han realizado ningún pedido.

    SELECT c.id_cliente, (n.nombre_cliente + ' ' + n.app + ' ' + n.apm) AS nombre_cliente, p.id_pedido
FROM cliente c
INNER JOIN natural n ON c.id_cliente = n.id_natural
LEFT JOIN pedido_cliente p ON c.id_cliente = p.id_cliente
WHERE p.id_pedido IS NULL;

  18. Clientes Empresa Sin Pedidos (Uso de LEFT JOIN)

    Encuentra clientes empresa que no han realizado ningún pedido.

    SELECT c.id_cliente, e.representante_legal, c.nit, c.telefono1, p.id_pedido
FROM cliente c
INNER JOIN empresa e ON c.id_cliente = e.id_empresa
LEFT JOIN pedido_cliente p ON c.id_cliente = p.id_cliente
WHERE p.id_pedido IS NULL;

  19. Pedidos Sin Cliente Natural Asociado (Uso de RIGHT JOIN)

    Identifica pedidos que no tienen un cliente natural asociado, posiblemente debido a datos inconsistentes o un tipo de cliente diferente.

    SELECT c.id_cliente, (n.nombre_cliente + ' ' + n.app + ' ' + n.apm) AS nombre_cliente, p.id_pedido
FROM cliente c
INNER JOIN natural n ON c.id_cliente = n.id_natural
RIGHT JOIN pedido_cliente p ON c.id_cliente = p.id_cliente
WHERE c.id_cliente IS NULL;

  20. Código de Cliente, Nombre, Cantidad de Pedidos y Monto Total

    Calcula el número de pedidos y el monto total gastado por cada cliente natural.

    SELECT c.id_cliente, (n.nombre_cliente + ' ' + n.app + ' ' + n.apm) AS nombre_cliente, COUNT(p.id_pedido) AS cantidad_pedido,
SUM(d.cantidad * d.precio_unitario) AS precio_total
FROM cliente c
INNER JOIN natural n ON c.id_cliente = n.id_natural
INNER JOIN pedido_cliente p ON c.id_cliente = p.id_cliente
INNER JOIN detalle_pedido d ON d.id_pedido = p.id_pedido
GROUP BY c.id_cliente, n.nombre_cliente, n.app, n.apm;

  21. Empleados con Cargo 'Cajero'

    Lista los empleados cuyo cargo es 'cajero'.

    SELECT e.id_empleado, (e.nombre_empleado + ' ' + e.app + ' ' + e.apm) AS Nombre_Empleado, c.nombre_cargo
FROM empleado e
INNER JOIN cargo c ON e.id_cargo = c.id_cargo
WHERE c.nombre_cargo IN('cajero');

  22. Proveedores Naturales Ordenados por CI (Ascendente)

    Muestra los proveedores naturales ordenados por su número de identificación (CI) de menor a mayor.

    SELECT p.id_proveedor, (n.nombre_proveedor + ' ' + n.app + ' ' + n.apm) AS Nombre_proveedor_natural, n.CI
FROM proveedor p
INNER JOIN natural_proveedor n ON p.id_proveedor = n.id_proveedor
ORDER BY n.CI ASC;

  23. Proveedores Naturales Ordenados por CI (Descendente)

    Muestra los proveedores naturales ordenados por su número de identificación (CI) de mayor a menor.

    SELECT p.id_proveedor, (n.nombre_proveedor + ' ' + n.app + ' ' + n.apm) AS Nombre_proveedor_natural, n.CI
FROM proveedor p
INNER JOIN natural_proveedor n ON p.id_proveedor = n.id_proveedor
ORDER BY n.CI DESC;

  24. Cantidad Total de Barrios

    Calcula el número total de barrios registrados en la base de datos.

    SELECT COUNT(b.idbarrio) AS Nro_de_Barrio FROM barrio b;
Karma: -8%
Visitas: 0

Entradas relacionadas:

Etiquetas:
uso del exec sp_addtype codigo en mysql select c.nombre,e.nombre from cargo as c left join empleado as e on c.id_cargo = e.id_cargo; exec sp_addtype codigo,'bigint','not null' create table producto proovedor marca sp_addtype mysql