Modelo Entidad-Relación, Normalización 3FN y Consultas SQL para Gestión de Instalaciones de Gas
EJERCICIO 1
Modelo Entidad Relación completo, indicando entidades, relaciones, atributos y todos los componentes del modelo que correspondan al análisis brindado. (20 puntos)
ENTIDADES
- INSUMO (codIns [PK], dscIns, stkIns)
- Categorización (triángulo) → VÁLVULA (mt3)
- Categorización (triángulo) → CAÑO (largo, diametro)
- DOMICILIO (idDom [PK], nomDom, telDom, dirDom {calle, numero, piso, block, otrosDatos} (compuesto))
- TÉCNICO (codTec [PK], nomTec, apeTec, ciTec, mailTec, fchTec)
RELACIONES
- INSUMO N --< instalación (fecha, cantidad) >-- N DOMICILIO
- AGREGACIÓN [INSUMO - instalación - DOMICILIO] N ---< realiza >--- 1 TÉCNICO
RNE
- mailTec es único.
- Si el insumo es caño se conoce largo y diámetro; si es válvula, mt3.
EJERCICIO 2
Pasaje a tablas en 3FN del modelo, se deben indicar claramente las restricciones relacionales y las de negocio. (20 puntos)
Insumo(codIns, dscIns, stkIns, tipoIns, mt3, largo, diametro)
PK: codIns
RNE: Si tipoIns = 'V' ⇒ largo y diametro = NULL y mt3 <> NULL
Si tipoIns = 'C' ⇒ largo y diametro <> NULL y mt3 = NULL
Domicilio(idDom, nomDom, telDom)
PK: idDom
Direccion(idDir, dscDir)
PK: idDir
Dir_Dom(idDom, idDir)
PK: idDom, idDir
FK: idDir ⇒ Direccion
FK: idDom ⇒ Domicilio
Tecnico(codTec, nomTec, apeTec, ciTec, mailTec, fchTec)
PK: codTec
AK: ciTec
UK: mailTec
Instalacion(codIns, idDom, fecha, cantidad, codTec)
PK: codIns, idDom, fecha
FK: codIns ⇒ Insumo
FK: idDom ⇒ Domicilio
FK: codTec ⇒ Tecnico
EJERCICIO 3
Realizar en DDL el script de creación de las tablas indicando todas las restricciones, relacionales y de negocio, si alguna de las restricciones no se puede realizar con DDL se debe indicar cuál o cuáles. (20 puntos)
CREATE DATABASE GAS
GO
USE GAS
GO
CREATE TABLE Insumo(codIns char(10) NOT NULL,
dscIns varchar(30) NOT NULL,
stkIns int,
tipoIns char(1) NOT NULL,
mt3 numeric(10,2),
largo numeric(10,2),
diametro numeric(10,2),
CONSTRAINT pk_Insumo PRIMARY KEY(codIns),
CONSTRAINT ck_TipoIns CHECK(tipoIns IN ('V','C')))
GO
CREATE TABLE Domicilio(idDom int IDENTITY NOT NULL,
nomDom varchar(30) NOT NULL,
telDom varchar(30),
CONSTRAINT pk_Domicilio PRIMARY KEY(idDom))
GO
CREATE TABLE Direccion(idDir int IDENTITY NOT NULL,
dscDir varchar(50) NOT NULL,
CONSTRAINT pk_Direccion PRIMARY KEY(idDir))
GO
CREATE TABLE Dir_Dom(idDom int NOT NULL,
idDir int NOT NULL,
CONSTRAINT pk_DirDom PRIMARY KEY(idDom, idDir),
CONSTRAINT fk_Dom_Dir FOREIGN KEY(idDom) REFERENCES Domicilio(idDom),
CONSTRAINT fk_Dir_Dir FOREIGN KEY(idDir) REFERENCES Direccion(idDir))
GO
CREATE TABLE Tecnico(codTec numeric(5) NOT NULL,
nomTec varchar(30) NOT NULL,
apeTec varchar(30) NOT NULL,
ciTec numeric(10) NOT NULL,
mailTec varchar(30),
fchTec date,
CONSTRAINT pk_Tecnico PRIMARY KEY(codTec),
CONSTRAINT ak_CiTecnico UNIQUE(ciTec),
CONSTRAINT uk_MailTecnico UNIQUE(mailTec))
GO
CREATE TABLE Instalacion(codIns char(10) NOT NULL,
idDom int NOT NULL,
fecha datetime NOT NULL,
cantidad int,
codTec numeric(5) NOT NULL,
CONSTRAINT pk_Instala PRIMARY KEY(codIns, idDom, fecha),
CONSTRAINT fk_InsInstala FOREIGN KEY(codIns) REFERENCES Insumo(codIns),
CONSTRAINT fk_DomInstala FOREIGN KEY(idDom) REFERENCES Domicilio(idDom),
CONSTRAINT fk_TecInstala FOREIGN KEY(codTec) REFERENCES Tecnico(codTec))
GO-- No se pueden implementar con DDL: las RNE de caño / válvula (qué columnas quedan en NULL según tipoIns). Requieren un CHECK compuesto o un trigger.
EJERCICIO 4 - CONSULTAS SQL
Utilizando SQL:
4.a. Mostrá el código del técnico, su nombre completo y la cantidad total de instalaciones que realizó, ordená de mayor a menor por cantidad de instalaciones.
SELECT t.codTec, t.nomTec + ' ' + t.apeTec AS NombreCompleto, COUNT(*) AS TotalInstalaciones
FROM Instalacion i JOIN Tecnico t ON i.codTec = t.codTec
GROUP BY t.codTec, t.nomTec, t.apeTec
ORDER BY TotalInstalaciones DESC4.b. Obtené la descripción del insumo y el promedio de cantidad instalada, ordená por promedio de mayor a menor.
SELECT ins.dscIns, AVG(inst.cantidad) AS PromedioCantidad
FROM Instalacion inst JOIN Insumo ins ON inst.codIns = ins.codIns
GROUP BY ins.dscIns
ORDER BY PromedioCantidad DESC4.c. Listar insumos cuyo total de unidades instaladas (suma de cantidad) supere las 50 unidades, ordená por total de mayor a menor.
SELECT ins.dscIns, SUM(inst.cantidad) AS TotalUnidades
FROM Instalacion inst JOIN Insumo ins ON inst.codIns = ins.codIns
GROUP BY ins.dscIns
HAVING SUM(inst.cantidad) > 50
ORDER BY TotalUnidades DESC4.d. Mostrá por cada año la cantidad total de instalaciones realizadas y el número de técnicos distintos que participaron, ordená por año. (10 puntos c/u)
SELECT YEAR(fecha) AS Anio, COUNT(*) AS TotalInstalaciones,
COUNT(DISTINCT codTec) AS TecnicosDistintos
FROM Instalacion
GROUP BY YEAR(fecha)
ORDER BY AnioPARTE 3 - VARIANTES DE RESTRICCIONES (línea dentro del CREATE TABLE)
R1. El stock no puede ser negativo.
CONSTRAINT ck_Stock CHECK(stkIns >= 0)
R2. La cantidad debe estar entre 1 y 100.
CONSTRAINT ck_Cant CHECK(cantidad BETWEEN 1 AND 100)
R3. El tipo puede ser V o C.
CONSTRAINT ck_TipoIns CHECK(tipoIns IN ('V','C'))
R4. El mail debe contener @.
CONSTRAINT ck_Mail CHECK(mailTec LIKE '%@%')
R5. La fecha de fin no puede ser anterior a la de inicio.
CONSTRAINT ck_Fechas CHECK(fchFin >= fchIni)
R6. Si no se indica la fecha, se toma la de hoy.
fecha datetime DEFAULT GETDATE()
R7. El stock inicial es 0.
stkIns int DEFAULT 0
R8. El mail debe ser único.
CONSTRAINT uk_MailTecnico UNIQUE(mailTec)
R9. La descripción siempre debe existir.
dscIns varchar(30) NOT NULL
R10. Las lecturas de un medidor (débil) referencian al medidor.
CONSTRAINT fk_LecMed FOREIGN KEY(idDom, nroMed) REFERENCES Medidor(idDom, nroMed)
PARTE 4 - PARTES EXTRA (ALTER / INSERT / UPDATE / DELETE)
X1 - Agregar utilizando DDL una tabla Zona con código numérico y nombre de hasta 20 caracteres, y realizar los cambios necesarios para que los domicilios puedan pertenecer a una zona.
CREATE TABLE Zona(codZona int NOT NULL,
nomZona varchar(20),
CONSTRAINT pk_Zona PRIMARY KEY(codZona))
GO
ALTER TABLE Domicilio ADD codZona int
GO
ALTER TABLE Domicilio ADD CONSTRAINT fk_DomZona FOREIGN KEY(codZona) REFERENCES Zona(codZona)
GOX2.a. Agregar el teléfono del técnico.
ALTER TABLE Tecnico ADD telTec varchar(20)X2.b. Agregar la restricción de que el stock no puede ser negativo.
ALTER TABLE Insumo ADD CONSTRAINT ck_Stock CHECK(stkIns >= 0)X2.c. Cambiar la descripción del insumo a 50 caracteres.
ALTER TABLE Insumo ALTER COLUMN dscIns varchar(50) NOT NULLX2.d. Eliminar la restricción del punto b.
ALTER TABLE Insumo DROP CONSTRAINT ck_StockX2.e. Eliminar la columna del punto a.
ALTER TABLE Tecnico DROP COLUMN telTecX3 - Inserte por lo menos un registro en cada tabla, teniendo en cuenta el orden necesario para respetar las relaciones.
INSERT INTO Insumo VALUES('CAN0000001','Caño cobre',100,'C',NULL,6,12.7)
INSERT INTO Domicilio VALUES('Juan Pérez','099123456')
INSERT INTO Direccion VALUES('Av. Italia 1234 apto 5')
INSERT INTO Dir_Dom VALUES(1,1)
INSERT INTO Tecnico VALUES(12345,'Ana','Gómez',12345678,'[email protected]','1990-05-10')
INSERT INTO Instalacion VALUES('CAN0000001',1,GETDATE(),3,12345)X4.a. Aumentar en 10 unidades el stock de las válvulas.
UPDATE Insumo SET stkIns = stkIns + 10
WHERE tipoIns = 'V'X4.b. Poner en 0 el stock de los insumos que nunca se instalaron.
UPDATE Insumo SET stkIns = 0
WHERE codIns NOT IN (SELECT codIns FROM Instalacion)X4.c. Borrar las instalaciones anteriores a 2020.
DELETE FROM Instalacion
WHERE YEAR(fecha) < 2020X4.d. Borrar el técnico 12345.
DELETE FROM Instalacion WHERE codTec = 12345
DELETE FROM Tecnico WHERE codTec = 12345PARTE 5 - CONSULTAS EXTRA (sobre las tablas del examen)
e- Mostrar descripción y stock de los caños con stock entre 10 y 50 cuya descripción contenga «cobre».
SELECT dscIns, stkIns
FROM Insumo
WHERE tipoIns = 'C' AND
stkIns BETWEEN 10 AND 50 AND
dscIns LIKE '%cobre%'f- Mostrar nombre del propietario, descripción del insumo y fecha de las instalaciones de 2025.
SELECT d.nomDom, ins.dscIns, i.fecha
FROM Instalacion i JOIN Domicilio d ON i.idDom = d.idDom
JOIN Insumo ins ON i.codIns = ins.codIns
WHERE YEAR(i.fecha) = 2025g- Mostrar los técnicos que nunca realizaron una instalación.
SELECT codTec, nomTec, apeTec
FROM Tecnico
WHERE codTec NOT IN (SELECT codTec FROM Instalacion)h- Mostrar el técnico que realizó más instalaciones.
SELECT TOP 1 WITH TIES t.codTec, t.nomTec, COUNT(*) AS Cant
FROM Instalacion i JOIN Tecnico t ON i.codTec = t.codTec
GROUP BY t.codTec, t.nomTec
ORDER BY COUNT(*) DESCi- Mostrar los insumos cuyo stock es mayor al stock promedio.
SELECT codIns, dscIns, stkIns
FROM Insumo
WHERE stkIns > (SELECT AVG(stkIns) FROM Insumo)j- Mostrar los insumos instalados en más de 3 domicilios diferentes.
SELECT ins.codIns, ins.dscIns
FROM Instalacion i JOIN Insumo ins ON i.codIns = ins.codIns
GROUP BY ins.codIns, ins.dscIns
HAVING COUNT(DISTINCT i.idDom) > 3k- Por cada técnico, mostrar la cantidad de instalaciones del año 2025, solo los que hicieron más de 10, ordenado de mayor a menor.
SELECT t.codTec, t.nomTec, COUNT(*) AS Cant
FROM Instalacion i JOIN Tecnico t ON i.codTec = t.codTec
WHERE YEAR(i.fecha) = 2025
GROUP BY t.codTec, t.nomTec
HAVING COUNT(*) > 10
ORDER BY Cant DESCl- Por cada domicilio, mostrar la fecha de su primera y de su última instalación.
SELECT d.idDom, d.nomDom, MIN(i.fecha) AS Primera, MAX(i.fecha) AS Ultima
FROM Instalacion i JOIN Domicilio d ON i.idDom = d.idDom
GROUP BY d.idDom, d.nomDom