viernes, 20 de noviembre de 2020

TIA 2: Implementación y manipulación de bases de datos

 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')