IMPORTANCIA DEL USO DE SQL PARA LA CREACIÓN Y MANIPULACIÓN DE DATOS
BigData,
además de ser el centro de la informática actual, es también el eje de las
demás actividades del ser humano en el presente, pues de los datos y del manejo
que se le dé a los mismos dependen la toma de decisiones y el cambio del mundo.
SQL es una herramienta
que si bien permite el manejo sistemático de los datos, es capaz de almacenar y
manipular grandes cantidades de información, a la vez que puede soportar varios
lenguajes de programación, por lo cual es una excelente opción para la POO.
Además de
ser accesible en cuanto a temas presupuestales, su interfaz gráfica está
evolucionando para que más usuarios puedan hacer uso de esta herramienta.
PLANTEAMIENTO DEL PROBLEMA
Situación de una Agencia de Arrendamientos
Se requiere crear un modelo entidad relación que
cuenta con las siguientes características:
a. Se tiene un conjunto de sedes que pertenecen
a la agencia de arrendamiento en cada una hay un administrador responsable.
b. Cuenta con 5000 propiedades para cada
propiedad que se arrienda se debe conocer su ubicación y sus características
principales.
c. Cada propiedad tiene un dueño.
d. Para los que toman el arriendo de la
propiedad que serían los clientes deben presentar certificados de trabajo.
e. Para que un cliente pueda tomar en arriendo
una propiedad necesita proporciona mínimo un fiador que cuente con uno o más
bienes para que pueda respaldar el contrato de alquiler o arriendo.
f. Una propiedad varía su precio en cada nuevo
contrato que realiza.
g.También se manejan los pagos que se han
generado por cada contrato de arrendamiento de una determinada propiedad.
1. La creación de la base de datos, el script
con comandos DDL del diseño entregado en la unidad 1, este ya debe estar
corregido en la realimentación que le realizó el docente.
Crear database arrendamientosVEN
USE arrendamientosVyN
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
Crear Tabla [dbo].[TAdministradores](
[idAdministrador] [int] NOT NULL,
[nombreAdmin] [char](30) NOT NULL,
[apellidoAdmin] [char](30) NOT NULL,
[telAdmin] [char](10) NULL,
CONSTRAINT [PK_TAdministradores]
PRIMARY KEY CLUSTERED
(
[idAdministrador] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
Crear Tabla [dbo].[TBienesFiador](
[idBien] [int] NOT NULL,
[nombreBien] [char](30) NOT NULL,
[tipoBien] [char](30) NOT NULL,
[direcBien] [char](30) NOT NULL,
[idFiador] [int] NOT NULL,
CONSTRAINT [PK_TBienesFiador]
PRIMARY KEY CLUSTERED
(
[idBien] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
Crear Tabla [dbo].[TClientes](
[idCliente] [int] NOT NULL,
[nombreCliente] [char](30) NOT NULL,
[apellidoCliente] [char](30)
NOT NULL,
[ocupacionCliente] [char](30) NULL,
[telCliente]
[int] NOT NULL,
CONSTRAINT [PK_TClientes] PRIMARY
KEY CLUSTERED
(
[idCliente] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
Crear Tabla [dbo].[TContrato](
[idContrato] [int] NOT NULL,
[tipoContrato] [char](10) NOT NULL,
[idCliente] [int] NOT NULL,
[idPropiedad] [int] NOT NULL,
[certificadoLaboral] [char](8) NOT NULL,
[valorContrato] [float] NOT NULL,
[fechaInicio] [date] NOT NULL,
[fechaTerminacion] [date] NOT NULL,
[idFiador] [int] NOT NULL,
CONSTRAINT [PK_TContrato] PRIMARY
KEY CLUSTERED
(
[idContrato] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
Crear Tabla [dbo].[TFiadores](
[idFiador] [int] NOT NULL,
[nombreFiador] [char](30) NOT NULL,
[apellidoFiador] [char](30) NOT
NULL,
[ocupacFiador] [char](30) NULL,
[direccionFiador] [char](30)
NOT NULL,
[telFiador] [char](10) NULL,
CONSTRAINT [PK_TFiadores] PRIMARY
KEY CLUSTERED
(
[idFiador] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table
[dbo].[TPagos] Script Date: 20/11/2018
5:13:14 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Crear Tabla [dbo].[TPagos](
[idPago] [int] NOT NULL,
[idContrato]
[int] NOT NULL,
[fechaPago] [date] NOT NULL,
CONSTRAINT [PK_TPagos] PRIMARY
KEY CLUSTERED
(
[idPago] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
Crear Tabla [dbo].[TPropiedades](
[idPropiedad] [int] NOT NULL,
[nombrePropiedad] [char](30) NOT NULL,
[tipoPropiedad] [char](10) NOT
NULL,
[extensPropiedad] [int] NOT NULL,
[direcProp] [char](30) NOT NULL,
[idPropietario] [int] NOT NULL,
[idSede] [int] NOT NULL,
[cantidadHabitaciones] [int] NOT NULL,
CONSTRAINT [PK_TPropiedades]
PRIMARY KEY CLUSTERED
(
[idPropiedad] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
Crear Tabla [dbo].[TPropietarios](
[idPropietario] [int] NOT NULL,
[nombrePropietario] [char](30)
NOT NULL,
[apellidoPropietario]
[char](30) NOT NULL,
[direcPropietario] [char](30) NOT NULL,
[telPropietario] [char](10) NOT NULL,
CONSTRAINT [PK_TPropietarios]
PRIMARY KEY CLUSTERED
(
[idPropietario] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
Crear Tabla [dbo].[TSedes](
[idSede] [int] NOT NULL,
[nombreSede] [char](30) NOT NULL,
[direcSede] [char](30) NOT NULL,
[telSede] [char](10) NOT NULL,
[idAdministrador] [int] NOT NULL,
CONSTRAINT [PK_TSedes] PRIMARY
KEY CLUSTERED
(
[idSede] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[TBienesFiador]
WITH CHECK ADD CONSTRAINT
[FK_TBienesFiador_TFiadores] FOREIGN KEY([idFiador])
REFERENCES [dbo].[TFiadores] ([idFiador])
GO
ALTER TABLE [dbo].[TBienesFiador] CHECK CONSTRAINT
[FK_TBienesFiador_TFiadores]
GO
ALTER TABLE [dbo].[TContrato]
WITH CHECK ADD CONSTRAINT
[FK_TContrato_TClientes] FOREIGN KEY([idCliente])
REFERENCES [dbo].[TClientes] ([idCliente])
GO
ALTER TABLE [dbo].[TContrato] CHECK CONSTRAINT [FK_TContrato_TClientes]
GO
ALTER TABLE [dbo].[TContrato]
WITH CHECK ADD CONSTRAINT
[FK_TContrato_TFiadores] FOREIGN KEY([idFiador])
REFERENCES [dbo].[TFiadores] ([idFiador])
GO
ALTER TABLE [dbo].[TContrato] CHECK CONSTRAINT [FK_TContrato_TFiadores]
GO
ALTER TABLE [dbo].[TContrato]
WITH CHECK ADD CONSTRAINT
[FK_TContrato_TPropiedades] FOREIGN KEY([idPropiedad])
REFERENCES [dbo].[TPropiedades] ([idPropiedad])
GO
ALTER TABLE [dbo].[TContrato] CHECK CONSTRAINT
[FK_TContrato_TPropiedades]
GO
ALTER TABLE [dbo].[TPagos] WITH
CHECK ADD CONSTRAINT
[FK_TPagos_TContrato] FOREIGN KEY([idContrato])
REFERENCES [dbo].[TContrato] ([idContrato])
GO
ALTER TABLE [dbo].[TPagos] CHECK CONSTRAINT [FK_TPagos_TContrato]
GO
ALTER TABLE [dbo].[TPropiedades]
WITH CHECK ADD CONSTRAINT
[FK_TPropiedades_TPropietarios1] FOREIGN KEY([idPropietario])
REFERENCES [dbo].[TPropietarios]
([idPropietario])
GO
ALTER TABLE [dbo].[TPropiedades] CHECK
CONSTRAINT [FK_TPropiedades_TPropietarios1]
GO
ALTER TABLE [dbo].[TPropiedades]
WITH CHECK ADD CONSTRAINT
[FK_TPropiedades_TSedes] FOREIGN KEY([idSede])
REFERENCES [dbo].[TSedes] ([idSede])
GO
ALTER TABLE [dbo].[TPropiedades] CHECK CONSTRAINT
[FK_TPropiedades_TSedes]
GO
ALTER TABLE [dbo].[TSedes] WITH
CHECK ADD CONSTRAINT
[FK_TSedes_TAdministradores1] FOREIGN KEY([idAdministrador])
REFERENCES [dbo].[TAdministradores]
([idAdministrador])
GO
ALTER TABLE [dbo].[TSedes] CHECK CONSTRAINT
[FK_TSedes_TAdministradores1]
GO
2. El diagrama generado por el sistema gestor de
base de datos SQL Server.
3. Los comandos DDL utilizados para insertar
mínimo tres registros por tabla.
Tabla
Administradores
INSERT INTO
[dbo].[TAdministradores]([idAdministrador],[nombreAdmin],[apellidoAdmin],[telAdmin])
VALUES(3333,'Vanessa','Betancur',5555555)
GO
INSERT INTO
[dbo].[TAdministradores]([idAdministrador],[nombreAdmin],[apellidoAdmin],[telAdmin])
VALUES(111111,'Camilo','Velez',4444444)
GO
INSERT INTO
[dbo].[TAdministradores]([idAdministrador],[nombreAdmin],[apellidoAdmin],[telAdmin])
VALUES(555555,'Octavio','Duque',4444444)
GO
INSERT INTO [dbo].[TAdministradores]([idAdministrador],[nombreAdmin],[apellidoAdmin],[telAdmin])
VALUES(1015276265,'Carlos','Suarez',5555555)
Tabla
sedes
INSERT INTO
[dbo].[TSedes]([idSede],[nombreSede],[direcSede],[telSede],[idAdministrador])
VALUES(1234,'extremo','cra 44',6666666,1015276265)
GO
INSERT INTO
[dbo].[TSedes]([idSede],[nombreSede],[direcSede],[telSede],[idAdministrador])
VALUES(2345,'poblado','cra 44',6666666,1015276265)
GO
INSERT INTO [dbo].[TSedes]([idSede],[nombreSede],[direcSede],[telSede],[idAdministrador])
VALUES(4324,'centro','cra 45',5555555,3333)
GO
Tabla Propiedades
INSERT INTO
[dbo].[TPropiedades]([idPropiedad],[nombrePropiedad],[tipoPropiedad],[extensPropiedad],[direcProp],[idPropietario],[idSede],[cantidadHabitaciones])
VALUES(1,'casalinda','casa',56,'cra 5',23456,1234,2)
GO
INSERT INTO
[dbo].[TPropiedades]([idPropiedad],[nombrePropiedad],[tipoPropiedad],[extensPropiedad],[direcProp],[idPropietario],[idSede],[cantidadHabitaciones])
VALUES(2,'pradoverde','casa',68,'
calle 25
#12-12’,34567,1234,4)
GO
INSERT INTO
[dbo].[TPropiedades]([idPropiedad],[nombrePropiedad],[tipoPropiedad],[extensPropiedad],[direcProp],[idPropietario],[idSede],[cantidadHabitaciones])
VALUES(3,'casabela','apto',67,'cra 56',34567,1234,1)
GO
Tabla propietarios
INSERT INTO
[dbo].[TPropietarios]([idPropietario],[nombrePropietario],[apellidoPropietario],[direcPropietario],[telPropietario])
VALUES (12345,'Cesar','Diaz','cra 56',3333333)
GO
INSERT INTO
[dbo].[TPropietarios]([idPropietario],[nombrePropietario],[apellidoPropietario],[direcPropietario],[telPropietario])
VALUES (23456,'Anibal','Ruiz','cra 67',4981293)
GO
INSERT INTO [dbo].[TPropietarios]([idPropietario],[nombrePropietario],[apellidoPropietario],[direcPropietario],[telPropietario])
VALUES (34567,'Jorge','Perez','cra 10',8657432)
GO
Tabla Fiadores
INSERT INTO
[dbo].[TFiadores]([idFiador],[nombreFiador],[apellidoFiador],[ocupacFiador],[direccionFiador],[telFiador])
VALUES (9876,'Camilo','Restrepo','arriero','cra 45',7896543)
GO
INSERT INTO
[dbo].[TFiadores]([idFiador],[nombreFiador],[apellidoFiador],[ocupacFiador],[direccionFiador],[telFiador])
VALUES (8765,'Pepita','Perez','ama de casa','cra 43',4567899)
GO
INSERT INTO
[dbo].[TFiadores]([idFiador],[nombreFiador],[apellidoFiador],[ocupacFiador],[direccionFiador],[telFiador])
VALUES (5678,'Luis','Restrepo','agente','cra 46',3456789)
GO
INSERT INTO [dbo].[TFiadores]([idFiador],[nombreFiador],[apellidoFiador],[ocupacFiador],[direccionFiador],[telFiador])
VALUES (5673,'Evelio','Salas','Policia','cra 55',2543789)
GO
Tabla Bienes Fiador
INSERT INTO [dbo].[TBienesFiador]([idBien],[nombreBien],[tipoBien],[direcBien],[idFiador])
VALUES(1,'acuarela','casa','cra 56',5673)
GO
INSERT INTO
[dbo].[TBienesFiador]([idBien],[nombreBien],[tipoBien],[direcBien],[idFiador])
VALUES(2,'Polaris','apartamento','cra 65',5678)
GO
INSERT INTO
[dbo].[TBienesFiador]([idBien],[nombreBien],[tipoBien],[direcBien],[idFiador])
VALUES(4,'Acapela','mansión','cra 45',8765)
GO
Tabla Clientes
INSERT INTO
[dbo].[TClientes]([idCliente],[nombreCliente],[apellidoCliente],[ocupacionCliente],[telCliente])
VALUES (880402,'Tulio','Zuluaga','Empresario',5555555)
GO
INSERT INTO
[dbo].[TClientes]([idCliente],[nombreCliente],[apellidoCliente],[ocupacionCliente],[telCliente])
VALUES (57025,'Adela','Cortina','Piloto',6789032)
GO
INSERT INTO
[dbo].[TClientes]([idCliente],[nombreCliente],[apellidoCliente],[ocupacionCliente],[telCliente])
VALUES (1015276,'Emilio','Zuleta','Docente',5432145)
GO
Tabla Contratos
INSERT INTO
[dbo].[TContrato]([idContrato],[tipoContrato],[idCliente],[idPropiedad],[certificadoLaboral],[valorContrato],[fechaInicio],[fechaTerminacion],[idFiador])
VALUES (1,'fijo',57025,2,'si',237879,'1/1/2017','2/2/2019',5678)
GO
INSERT INTO [dbo].[TContrato]([idContrato],[tipoContrato],[idCliente],[idPropiedad],[certificadoLaboral],[valorContrato],[fechaInicio],[fechaTerminacion],[idFiador])
VALUES (2,'indefinido',880402,3,'si',578908,'1/3/2015','2/2/2019',9876)
GO
INSERT INTO [dbo].[TContrato]([idContrato],[tipoContrato],[idCliente],[idPropiedad],[certificadoLaboral],[valorContrato],[fechaInicio],[fechaTerminacion],[idFiador])
VALUES (3,'indefinido',1015276,1,'si',879032,'1/4/2013','2/2/2019',8765)
GO
Tabla
Pagos
INSERT INTO [dbo].[TPagos]([idPago],[idContrato],[fechaPago])
VALUES (12,1,'6/7/2018')
GO
INSERT INTO
[dbo].[TPagos]([idPago],[idContrato],[fechaPago])
VALUES (13,2,'8/7/2018')
GO
INSERT INTO
[dbo].[TPagos]([idPago],[idContrato],[fechaPago])
VALUES (14,3,'9/7/2018')
GO
4. Los comandos DML dar solución a las
siguientes consultas
4.1. Mostrar los datos de las propiedades con
número de habitaciones mayor que 3 y tiene un área mayor que 68 mts2 o son tipo
casa.
SELECT
TOP (200) idPropiedad, nombrePropiedad, tipoPropiedad, extensPropiedad,
direcProp, idPropietario, idSede, cantidadHabitaciones
FROM
TPropiedades
WHERE
(extensPropiedad > 68) AND (tipoPropiedad = 'casa') AND
(cantidadHabitaciones > 3)
4.2. Mostrar los datos de los pagos generados
por la propiedad con dirección calle 25 #12-12.
SELECT
TOP (200) TPagos.idPago, TPagos.idContrato, TPagos.fechaPago,
TContrato.valorContrato, TClientes.nombreCliente, TClientes.apellidoCliente,
TPropiedades.nombrePropiedad, TPropiedades.direcProp
FROM TPagos
INNER JOIN
TContrato ON TPagos.idContrato = TContrato.idContrato INNER
JOIN
TClientes ON TContrato.idCliente =
TClientes.idCliente INNER JOIN
TPropiedades ON TContrato.idPropiedad =
TPropiedades.idPropiedad
WHERE
(TPropiedades.direcProp = 'calle 25 #12-12')
4.3. Mostrar los datos de las propiedades
arrendadas en fechas menores a 01/01/2016
SELECT
TOP (200) TContrato.valorContrato, TContrato.fechaInicio,
TPropiedades.nombrePropiedad, TPropiedades.tipoPropiedad,
TPropiedades.cantidadHabitaciones
FROM
TContrato INNER JOIN
TPropiedades ON TContrato.idPropiedad =
TPropiedades.idPropiedad
WHERE (TContrato.fechaInicio <
CONVERT(DATETIME, '2016-01-01 00:00:00', 102))
4.4. Mostrar los datos de clientes que han
arrendado casas en el poblado y sus dueños son Jorge Pérez o Aníbal Ruiz
SELECT
TOP (200) TClientes.idCliente, TClientes.nombreCliente, TClientes.apellidoCliente,
TClientes.ocupacionCliente, TContrato.tipoContrato, TPropiedades.direcProp,
TPropietarios.nombrePropietario,
TPropietarios.apellidoPropietario
FROM
TClientes INNER JOIN
TContrato ON TClientes.idCliente = TContrato.idCliente INNER JOIN
TPropiedades ON TContrato.idPropiedad =
TPropiedades.idPropiedad INNER JOIN
TPropietarios ON TPropiedades.idPropietario =
TPropietarios.idPropietario
WHERE
(TPropiedades.direcProp = 'poblado') AND
(TPropietarios.nombrePropietario = 'jorge') AND
(TPropietarios.apellidoPropietario = 'perez') OR
(TPropietarios.nombrePropietario = 'anibal') AND
(TPropietarios.apellidoPropietario = 'ruiz')