Actividad integradora
Competencia de la credencial:
Usar el gestor de base de datos, sus principales comandos y sentencias de T-SQL basado en conceptos sólidos de diseño y programación, necesarios para implementar y gestionar una plataforma de business intelligence.
Instrucciones:
Te contrataron como programador T-SQL para una empresa extranjera llamada Adventure Works y te asignaron directamente a un proyecto relacionado con proveedores, donde, mediante un sitio web, el proveedor podrá validar sus datos, órdenes de compra con su detalle, estado de sus entregas, órdenes pendientes de pago, entre más opciones.
En el levantamiento de requerimientos del proyecto se han definido puntos a desarrollar a cargo del departamento de back end (base de datos), pero antes de comenzar con las actividades del proyecto, se deberá acondicionar el entorno con los siguientes pasos:
Configuración del entorno:
- Instalar Microsoft SQL Server Management Studio 2018. Enlace de descarga:
El siguiente enlace es externo a la Universidad Tecmilenio,
al acceder a éste considera que debes apegarte a sus términos y condiciones.
- Entrar al siguiente enlace:
El siguiente enlace es externo a la Universidad Tecmilenio,
al acceder a éste considera que debes apegarte a sus términos y condiciones.
y descargar el archivo .bak. En la siguiente imagen se muestra específicamente el archivo a descargar.
Pantalla 1. Página de descarga.
Esta pantalla se obtuvo directamente del software que se está explicando en la computadora, para fines educativos
El uso y descarga del software deberá apegarse a los términos y condiciones del sitio oficial del fabricante y su uso será responsabilidad de quien lo descargue. Tecmilenio no tiene licencia ni posee los derechos sobre dicho software.
- Restaurar la base de datos: se deberá seguir el procedimiento que se encuentra debajo de “Download backup files” dentro del mismo enlace del paso 2, donde se descargó el archivo con extensión .bak.
- Una vez restaurada la base de datos, se debe visualizar de la siguiente manera en el explorador de objetos:
Pantalla 2. Object Explorer.
Esta pantalla se obtuvo directamente del software que se está explicando en la computadora, para fines educativos
Ahora que se tiene todo correcto, se enlistan las actividades a realizar de manera ordenada, ya que llevan secuencia, pues algunas dependen de otras, por lo que se recomienda que se completen en el orden mostrado para evitar contratiempos.
Requerimientos:
- Crear una vista (Purchasing.vPurchaseOrderDetail) y mostrar las siguientes columnas conservando el mismo nombre, es decir, no se deberá usar alias:
- PurchaseOrderID.
- Status.
- VendorID.
- ShipMethodID.
- OrderDate.
- ShipDate.
- SubTotal.
- TaxAmt.
- TotalDue.
- ProductID.
- OrderQty.
- ReceivedQty.
- UnitPrice.
- LineTotal.
- Crear un procedimiento almacenado (usp_GetPurchaseOrderDetail”). Este objeto hará uso de la vista creada en el requerimiento 1 y será capaz de recibir un parámetro de entrada para filtrar una orden de compra (PurchaseOrderID) y deberá retornar las siguientes columnas respetando el orden con el que se enlistan:
- PurchaseOrderID.
- OrderDate.
- VendorID.
- ProductID.
- OrderQty.
- ReceivedQty.
- UnitPrice.
- LineTotal.
- ExtractedDate (campo calculado para saber la fecha en la que se extrajo la información).
- ExtractedTime (campo calculado para saber la hora, minutos y segundos en los que se extrajo la información).
- Crear una función con valores de tabla (ufnGetPurchaseOrderCountByVendor) que deberá retornar únicamente tres columnas:
- VendorID.
- PurchaseOrderCount (campo calculado para conocer el número de órdenes de compra que tiene el proveedor).
- LastPurchaseOrderDate (campo calculado para conocer la fecha de la última compra del proveedor. Si aún no se tiene ninguna, se deberá colocar la fecha que se tiene en ([Purchasing].[Vendor].[ModifiedDate].
- Crear un procedimiento almacenado para extraer a todos los proveedores de acuerdo con su estatus de activación (ActiveFlag) y estatus de crédito (CreditRating). Las columnas que el objeto deberá retornar al ser ejecutado serán las siguientes:
- BusinessEntityID.
- AccountNumber.
- Name.
- CreditRating.
- ActiveFlag.
- PurchaseOrderCount (obtenerlo mediante el ufnGetPurchaseOrderCountByVendor, que se creó previamente).
- Se requiere desarrollar un script que retorne información tipo resumen para un informe gerencial, con referencia a las compras que se han hecho por empleado, fecha y estatus. Se deberán implementar comandos como GROUP BY, SUM, COUNT, ORDER BY, entre otros.
Nota: Declarar variables para que los campos funcionen como filtros y la información que retorne sea dinámica.
- PurchaseOrderHeader.EmployeeID.
- PurchaseOrderHeader.OrderDate.
- PurchaseOrderHeader.Status.
Columnas que se esperan obtener:
- EmployeeID.
- EmployeeLoginID.
- PurchaseOrderCount (Usar COUNT).
- PurchaseOrderTotalDue (Usar SUM).
- PurchaseOrderNewTax (obtener el cálculo del 5% del total de las compras).
Nota: Hay que recordar que el script debe ser dinámico, es decir, si se colocan diferentes fechas, se recalcularán los valores, pues se deberá filtrar la información; lo mismo sucede con EmployeeID y Status.
- Diseñar una función escalar que retorne un 0 o 1 para indicar si una orden de compra tiene cantidad pendiente por recibir (ReceivedQty).
Se deberá comparar dicho campo contra OrderQty, obtener la diferencia y sumarla a nivel PurchaseOrderID. Para valorar si el resultado es mayor a 0, retornar 1, de lo contrario, 0.
- Diseñar un procedimiento almacenado para insertar información en la tabla Production.Product (uspInsertProduct). Si falla este proceso, se deberá hacer uso de uspLogError para insertar el detalle de por qué ha fallado.
El procedimiento almacenado debe contar con parámetros de entrada, validaciones e implementación del procedimiento almacenado para guardar mensajes de error en el log.
- Realizar un script que filtre todos los productos que en su nombre contengan la palabra “Classic”, para que, una vez identificados los registros, se modifique la palabra “Classic” por “Classic Premium”.
En resumen, es hacer uso del UPDATE para afectar la tabla [dbo].[Production.Product] para validar si la información se actualizó correctamente. Puedes usar la vista [Production].[vProductAndDescription].
- Diseñar una vista que regrese información de órdenes de compra que cumplan con tres condiciones: el total de la orden debe ser mayor o igual a 10,000 USD, que el estatus de la orden sea igual a 4 y que el estatus del proveedor esté activo. El nombre que tendrá el objeto será Purchasing.vPurchaseOrderMoreThan10000.
Columnas por mostrar:
- VendorID.
- VendorName.
- PurchaseOrderID.
- TotalDue.
- Diseñar un proceso de backup para las tablas [PurchaseOrderHeader] y [PurchaseOrderDetail]. Para eso, se deberán crear tablas espejo (misma definición) [PurchaseOrderHeaderHistory] y [PurchaseOrderDetailHistory], y crear un script que ejecute una secuencia de comandos, como se describe a continuación:
- Insertar registros a las tablas de History, siempre y cuando en la tabla origen (PurchaseOrderHeader) se superen los 365 días en referencia a ModifiedDate.
- Eliminar registros de las tablas PurchaseOrderHeader y PurchaseOrderDetail una vez que se haya hecho el respaldo correspondiente.
- Hacer uso de transacciones para asegurar la integridad de los datos. Si falla, aplicar ROLLBACK, si no, COMMIT.
Tecmilenio no guarda relación alguna con las marcas mencionadas como ejemplo. Las marcas son propiedad de sus titulares conforme a la legislación aplicable, se utilizan con fines académicos y didácticos, por lo que no existen fines de lucro, relación publicitaria o de patrocinio.
Criterios de evaluación:
- Usar correctamente la combinación de tablas mediante JOINS.
- Crear e implementar vistas.
- Implementar procedimientos almacenados, parámetros, validaciones y flujo de programa.
- Manejar resultados con agrupación.
- Integrar objetos de funciones con valores de tabla y funciones escalares.
- Reutilizar e incorporar los objetos creados en una tabla dentro de una nueva creación.
- Manipular transacciones.
- Mantener la armonía con la nomenclatura de los nombres de los objetos en la base de datos.
- Optimizar recursos con el manejo ideal de comandos de T-SQL.
- Proponer soluciones en beneficio de los requerimientos solicitados.
- Realizar pruebas de los objetos y scripts creados para asegurar el resultado con exactitud.
- Cumplir con las reglas de negocio descritas por el solicitante.
