EXAMEN DE BASE DE DATOS

BASE DE DATOS AVANZADAS
EXAMEN DE UNIDAD I
DOCENTE: DR. LUIS BOY CHAVIL
CONSIDERACIONES GENERALES
- El alumno está autorizado a utilizar su material de consulta.
- No se permite intercambiar el material del curso entre los alumnos.

CONTENIDO

Elabore el diagrama de la base de datos considerando las tablas necesarias para el registro de la matrícula
de los estudiantes de la UNT. Ud. es libre de utilizar funciones, cursores, triggers, vistas y/o procedimientos
almacenados; para atender las siguientes preguntas:

a) El registro de la matrícula solamente se puede efectuar en las fechas establecidas en el cronograma;
asimismo se debe verificar el pago de matrícula registrando el número de recibo respectivo; en los
demás casos se rechazará el registro de matrícula ….. 4 puntos.
b) Al efectuar el registro de una matrícula se deberá llevar a cabo los siguientes controles: …… 10 puntos
o La nota del alumno, en el curso pre requisito del Plan de estudios actual, debe estar aprobado
o El total de créditos no debe ser menor de 12, ni mayor a 22 créditos.
o El alumno no puede tener una asignatura con más de 3 notas desaprobadas
o Debe haber vacante en la sección donde desea matricularse
o En los casos en que el alumno haya solicitado reserva de matrícula, debe acreditar el registro de su
matrícula, con la Resolución de reingreso respectiva.
o Opcionalmente, se verificará que el horario de un curso no esté cruzado con el horario de otro
curso.
c) Programe el caso de una matrícula en un curso, en forma co curricular .……  4 puntos
d) Considerar que al confirmarse el registro de la matricula del alumno; se actualizará la vacante del curso
y de su correspondiente Laboratorio (si hubiera)…2 puntos

PRACTICA DE MODELADO UML

PRIMERA PRÁCTICA CALIFICADA LABORATORIO

El presente caso de estudio trata de la Biblioteca Atenea la cual tiene como misión prestar libros de diversas especialidades al público en general. Su visión es ser la biblioteca más importante del medio brindando el mejor servicio de atención y ofreciendo gran variedad de material bibliográfico actualizado. Y como parte de su estrategia de negocio, la Biblioteca Atenea ha planteado los siguientes objetivos:

a)    Adquirir nuevos libros cada año en función a la demanda de préstamos;

b)    Atender en promedio cien préstamos mensuales e ir incrementando gradualmente; y

 c) Controlar eficientemente las devoluciones de libros prestados. Así mismo, para el cumplimiento de los objetivos descritos anteriormente, la Biblioteca Atenea gestiona tres procesos de negocio que se describen a continuación:

1.     Gestionar adquisición de libros. Este proceso apunta al cumplimiento del primer objetivo y consta del siguiente procedimiento:

El proceso inicia cuando el Jefe de Compras solicita al Bibliotecario al inicio del año un reporte de libros que se necesita adquirir. El Bibliotecario elabora el reporte en función de los más prestados y envía el reporte al Jefe de Compras. Luego, el Jefe de Compras gestiona la compra de los libros, y después de efectuarse la compra, envía un informe al Bibliotecario de todo los libros adquiridos. El Bibliotecario clasifica los libros adquiridos, les asigna su código correspondiente y los registra en el catálogo. Finalmente, el Bibliotecario ubica los libros en los estantes, y el proceso termina.

2.       Gestionar préstamo de libros. Este proceso apunta al cumplimiento del segundo objetivo y consta del siguiente procedimiento:

El proceso inicia cuando el Lector llega a la biblioteca en busca del libro de su interés y le solicita al Bibliotecario, el Bibliotecario le entrega el catálogo para que realice la búsqueda y un formato de solicitud de préstamo. El Lector hace la búsqueda en el catálogo y si encuentra el libro de su interés registra los datos en el formato de solicitud de préstamo y lo entrega al Bibliotecario, caso contrario se retira de la biblioteca y el proceso termina. Luego que el Bibliotecario recibe el formato de solicitud de préstamo, el Bibliotecario verifica si el libro está disponible para préstamo. Si el material bibliográfico solicitado no estuviera disponible, el Bibliotecario informa al Lector de la situación y el Lector si desea puede volver a realizar la búsqueda de otro libro repitiéndose los pasos anteriores, caso contrario el Lector se retira de la biblioteca y el proceso termina. Por otra parte, si el libro solicitado estuviera disponible, entonces el Bibliotecario primero solicita los datos del Lector y verifica si ya está registrado o es nuevo. En el caso que sea un Lector nuevo, el Bibliotecario registra los datos del Lector el cual debe ser mayor de edad, esta condición es necesario cumplir para que se le pueda hacer el préstamo. Luego, el Bibliotecario registra el préstamo pero sólo un libro a la vez y si es a domicilio por un periodo de tres días como máximo.   

El lector puede hacer hasta dos préstamos, pero no puede hacer un nuevo préstamo si tiene un préstamo pendiente de devolución (o que haya superado su fecha límite de devolución) y tampoco si aún se encuentra sancionado por moroso. Si las condiciones se cumplen y se registra el préstamo, el Bibliotecario entrega el libro al Lector. Posteriormente, luego que el Lector ha usado el libro lo entrega al Bibliotecario, el Bibliotecario verifica el préstamo y actualiza su estado ha devuelto. Si el préstamo ha sido a domicilio, el Bibliotecario verifica si ha superado la fecha límite de devolución, si este fuera el caso, el Bibliotecario le aplica al Lector una sanción de prohibición de préstamo por un mes. Por último, el Bibliotecario ubica el libro en el estante correspondiente, y el proceso terminar.

3.     Gestionar devoluciones de libros. Este proceso apunta al cumplimiento del tercer objetivo y consta del siguiente procedimiento:

El proceso inicia cada día cuando el Bibliotecario decide revisar los préstamos a domicilio y verificar qué Lectores se encuentran morosos, es decir aquellos Lectores cuyo préstamo ha superado la fecha límite de devolución. En caso que no existiera Lectores morosos, el proceso termina. De lo contrario, si existiera Lectores morosos, el Bibliotecario elabora una lista de ellos, luego va seleccionando uno por uno y para el que haya superado un día la fecha límite de devolución le envía una notificación por correo electrónico recordándole que debe hacer la devolución del libro prestado, o en caso que haya superado cuatro días la fecha límite de devolución hace una llamada telefónica al Lector para solicitarle la devolución del libro y en caso que el Lector responda la llamada, el Lector confirma el retorno del libro. Pero, en caso que el Lector moroso haya superado treinta días la fecha límite de devolución, el Bibliotecario actualiza el estado del libro prestado como perdido. Por último, el Bibliotecario elabora un cuadro estadístico del estado de préstamos pendientes de devolución, y el proceso termina.

Además, como parte de la estrategia de negocio apuntando al logro de la visión, la Biblioteca Atenea necesita automatizar los procesos de negocio descritos anteriormente a través de un software. Esto permitirá gestionar eficientemente los procesos relacionados a la gestión de la biblioteca y ofrecer un servicio de mayor calidad a los estudiantes y público en general.

El software a desarrollar llevará el nombre de ATENEA-SOFT y se ha planificado cumplir los siguientes objetivos durante el desarrollo del proyecto:

1.            Modelar el caso de uso del negocio.

2.            Modelo de análisis de negocio.

3.            Especificar los requisitos del software.

4.            Diagrama de caso de uso del sistema.

PRACTICA (EJEMPLO) DE BASE DE DATOS

BASE DE DATOS AVANZADAS

PRÁCTICA CALIFICADA I

DOCENTE: DR. LUIS BOY CHAVIL

CONSIDERACIONES GENERALES

-          El alumno está autorizado a utilizar su material de consulta.

-          No se permite intercambiar el material del curso entre los alumnos.

CONTENIDO

                Una Universidad privada requiere desarrollar el Módulo de pago de pensiones de sus estudiantes, para ello se solicita elaborar la base de datos normalizada, con las siguientes consideraciones:

-          Los estudiantes programan el pago de sus pensiones en 1, 2, 3, 4, o hasta en 5 cuotas.

-          Para tener derecho a la matrícula, el alumno debe haber cancelado el monto de la matrícula y la primera cuota.

-          Hay un cronograma de pagos; para cada caso: Si es en dos cuotas, en tres, o cuatro, o cinco cuotas.

-          Si el alumno se retraza en la fecha de vencimiento de la cuota, deberá pagar los intereses respectivos.

-          Los alumnos becados con beca cero, solo pagan el monto de la matrícula.

-          Hay cuatro categorías de pago: 1. Beca cero (solo paga matrícula), 2. Beca 100 (paga 5 cuotas de 100 soles), 3. Media beca (paga el 50% del monto total de la categoría Normal; en 1, 2, 3, 4, o 5 cuotas), y 4. Categoría Normal

-          Los alumnos que se encuentran en alguna categoría, reciben una Resolución, cuyo número deberá ingresarse al sistema para saber el monto a pagar en el semestre. Las resoluciones solamente tienen vigencia durante un semestre.

-          No se acepta pagos parciales de una cuota; es decir, si la cuota es de S/ 450.00; no se aceptará el pago de un monto menor.

-          Si un alumno está al día en sus pagos, se habilitará el acceso a su campus virtual.

-          Programe los dominios y valores por defecto que el sistema requiera.

-          Implementar un Trigger para que al ingresar los datos de un nuevo estudiante se pueda crear el Login y la Contraseña, cuya característica es:

o   Login à 1° letra del ApellidoPaterno + ApellidoMaterno al reves + 1° letra del Nombre + Correlativo autogenerado (si el dato Existe) + @upk.edu.pe

o   Contraseña à # vocales ApPaterno (de 02 dígitos) + ‘0’ + Vocales ApMaterno (máx 6 letras, sino completar con z, por la izquierda) + 4 dígitos extremos del DNI

Ejemplo:

Estudiante(IdEstudiante, Nombre, ApPaterno, ApMaterno, DNI, FchIngreso, Otros (completar), Login, Contraseña) 

Datos:

(‘1023’, ‘Juan Pedro’, ‘Peralta’, ‘Fernandez’, ‘71065149’, ‘10/07/2015’, ‘. . . .’, Null, Null)

Login à PzednanreFJ01@upk.edu.pe

Contraseña à ‘030zzzeae7149’

-          Elabore la base de datos.

-          Archivo de entrega: <<ApellidoNombre.sql>>

PROBLEMA DESARROLLADO EN SQL SERVER

UNIVERSIDAD NACIONAL DE TRUJILLO
ESCUELA DE INGENIERÍA DE SISTEMAS

PRÁCTICA CALIFICADA N° 01

BASE DE DATOS AVANZADAS

DOCENTE: Dr. Luis Boy ChavilEN UN SISTEMA DE VENTAS DE PRODUCTOS FARMACÉUTICOS SE
REQUIERE:AL REGISTRAR UNA VENTA DE PRODUCTOS FARMACÉUTICOS; SE ACTUALIZARÁ EL STOCK DEL PRODUCTO; ASI COMO SU RESPECTIVO KÁRDEX DONDE SE REGISTRARÁ EL TIPO DE DOCUMENTO DE LA VENTA (FACTURA O BOLETA), EL N° DE DICHO DOCUMENTO, EL TIPO DE OPERACIÓN (EN ESTE CASO ES DE SALIDA), EL STOCK ANTERIOR,
LA CANTIDAD VENDIDA Y EL STOCK ACTUAL. TAMBIEN SE
ACTUALIZARÁ LA COMISIÓN DEL VENDEDOR QUE SERÁ ACUMULADO EN BASE AL 1% DEL IMPORTE DE LA VENTA TOTAL; ASIMISMO, SE HARÁ LAS SIGUIENTES VERIFICACIONES: EL ESTADO DEL CLIENTE DEBE ESTAR ACTIVO Y SU SALDO DE CRÉDITO DEBE SER MAYOR AL IMPORTE TOTAL DE VENTA, EN CASO CONTRARIO SE RECHAZA LA OPERACIÓN; SI NO HAY STOCK SUFICIENTE, SE MOSTRARÁ UN MENSAJE DE RECHAZO Y SE ANULA LA OPERACIÓN. IMPLEMENTE EL SCRIPT EN T-SQL PARA RESOLVER EL PROBLEMA PLANTEADO.
NOTA: PUEDE UTILIZAR: TRIGGER’S, FUNCIONES, CURSORES,
PROCEDIMIENTOS ALMACENADOS, ETC.  

SOLUCIÓN:

1. MODELADO

2. CREAR TABLAS

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45

create database BDVentas use BDVentas; go create table CLIENTE( CLI_codigo int primary key identity(1,1), CLI_nombres varchar(50), CLI_apellidos varchar(50), ClI_saldo money, ClI_estado char(1) not null ) create table VENDEDOR( VEN_codigo int primary key identity(1,1), VEN_nombres varchar(50) not null, VEN_apellidos varchar(50) not null, VEN_comision money ) create table PEDIDO( PED_codigo int primary key identity(1,1), VEN_codigo int references VENDEDOR(VEN_codigo), CLI_codigo int references CLIENTE(CLI_codigo), PED_fecha date ) create table FACTURA( FAC_codigo int primary key identity(1,1), PED_codigo int references PEDIDO(PED_codigo), FAC_fecha date, FAC_total money not null ) create table PRODUCTO( PRO_codigo int primary key identity(1,1), PRO_precio money ) create table STOCK( STO_codigo int primary key identity(1,1), PRO_codigo int references PRODUCTO(PRO_codigo), STO_actual int not null, STO_anterior int not null ) create table DETALLE_FACTURA( FAC_codigo int references FACTURA(FAC_codigo), PRO_codigo int references PRODUCTO(PRO_codigo), cantidad int not null )

3. CREAR TRIGGER

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71

create trigger tgdetalleFactura on DETALLE_FACTURA for insert as begin declare @CLI_estado char(1), @CLI_codigo int, @CLI_saldo money, @VEN_codigo int, @FAC_codigo int, @PRO_codigo int, @cantidadProducto int, @STO_actual int, @PRO_precio money select @FAC_codigo=i.FAC_codigo, @PRO_codigo=i.PRO_codigo, @cantidadProducto=i.cantidad, @STO_actual=s.STO_actual, @PRO_precio=p.PRO_precio, @CLI_codigo=c.ClI_codigo, @CLI_estado=c.ClI_estado, @CLI_saldo=c.ClI_saldo, @VEN_codigo=v.VEN_codigo from inserted i inner join FACTURA f on f.FAC_codigo=i.FAC_codigo inner join PEDIDO pe on pe.PED_codigo=f.PED_codigo inner join VENDEDOR v on v.VEN_codigo=pe.VEN_codigo inner join CLIENTE c on c.CLI_codigo=pe.CLI_codigo inner join PRODUCTO p on p.PRO_codigo=i.PRO_codigo inner join STOCK s on s.PRO_codigo=p.PRO_codigo if @CLI_estado='v' and @CLI_saldo>=(@PRO_precio*@cantidadProducto) begin -- el stock del producto tiene que ser mayor a la cantidad del pedido if @cantidadProducto<=@STO_actual begin --insertar un nuevo stock insert into STOCK values(@PRO_codigo,(@STO_actual-@cantidadProducto),@STO_actual) -- actualizar el total de la factura update factura set FAC_total=FAC_total+(@PRO_precio*@cantidadProducto) where FAC_codigo=@FAC_codigo -- actualizar el saldo del cliente update CLIENTE set ClI_saldo=ClI_saldo-(@PRO_precio*@cantidadProducto) where CLI_codigo=@CLI_codigo -- actualizar la comision de vendedor update VENDEDOR set VEN_comision=VEN_comision+0.01*(@PRO_precio*@cantidadProducto) where VEN_codigo=@VEN_codigo end else begin raiserror ('stock insuficiente', 16, 2) rollback transaction end end else begin raiserror ('el cliente no tiene saldo o su estado es f', 16, 2) rollback transaction end end

4. PRUEBA

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

use BDVentas go insert into CLIENTE values ('Elson Roque','Caldas Dominguez',5000,'v'), ('Hector','Caldas Dominguez',0,'v'), ('Emerson','Caldas Lozano',2000,'f') insert into VENDEDOR values('Emerita','Caldas Pinedo',0) insert into PRODUCTO values(5),(10) insert into STOCK values(1,15,0),(2,0,0) insert into pedido values (1,1,GETDATE()),-- primer pedido (1,2,GETDATE()),-- segundo pedido (1,3,GETDATE())-- tercer pedido insert into factura values(1,GETDATE(),0), (2,GETDATE(),0), (3,GETDATE(),0) insert into detalle_factura values (2,1,1)

EJERCICIO RESUELTO EN SQL SERVER

1. modelado

2. crear tablas

create database planillas
use planillas
go
-- tabla TRABAJADORES
create table TRABAJADORES(
TRA_codigo int primary key identity(1,1),
TRA_nombres varchar(50) not null,
TRA_apellidoPaterno varchar(50) not null,
TRA_apellidoMaterno varchar(50) not null,
TRA_dni char(8) not null,
TRA_fechaContrato date default(getdate()),
TRA_cargo varchar(50) not null check(TRA_cargo='Obrero' or TRA_cargo='Operador' or TRA_cargo='Ejecutivo') default('Operador'),
TRA_sueldoMensual money not null,
TRA_descuentoAFP float check(TRA_descuentoAFP<1 and TRA_descuentoAFP>0 )
)



--tabla USUARIOS
create table USUARIOS(
USU_codigo varchar(200) primary key,
TRA_codigo int references TRABAJADORES(TRA_codigo),
TRA_contraseña varchar(200) not null
)
-- tabla REGISTROS
create table REGISTROS(
REG_codigo int primary key identity(1,1),
TRA_codigo int references TRABAJADORES(TRA_codigo),
REG_horaEntrada tinyint not null check(REG_horaEntrada<24),
REG_horaSalida tinyint not null check(REG_horaSalida<24),
REG_fecha date not null default(getdate())
)
-- tabla HORARIO
create table HORARIOS(
HOR_codigo int primary key identity(1,1),
TRA_codigo int references TRABAJADORES(TRA_codigo)
)
-- tabla TURNOS
create table TURNOS(
TUR_codigo int primary key identity(1,1),
TUR_turno varchar(50) not null check(TUR_turno='Mañana' or TUR_turno='Tarde' or TUR_turno='Noche') default('Mañana'),
TUR_horaInicio tinyint not null check(TUR_horaInicio<=24 and(TUR_horaInicio=8 or TUR_horaInicio=16 or TUR_horaInicio=24)),
TUR_horaFinal tinyint not null check(TUR_horaFinal<=24 and(TUR_horaFinal=16 or TUR_horaFinal=24 or TUR_horaFinal=8))

)

-- tabla DETALLE_HORARIOS
create table DETALLE_HORARIOS(
HOR_codigo int references HORARIOS(HOR_codigo),
TUR_codigo int references TURNOS(TUR_codigo),
DHO_dia varchar(50) not null
)
--tabla HORAS_EXTRAS
create table HORAS_EXTRAS(
HEX_codigo int primary key identity(1,1),
TRA_codigo int references TRABAJADORES(TRA_codigo),
HEX_fecha date not null default(getdate()),
HEX_horaEntrada tinyint not null,
HEX_horaSalida tinyint not null,
HEX_totalHoras tinyint not null
)
--tabla TAREAS
create table TAREAS(
TAR_codigo int primary key identity(1,1),
TAR_nombre varchar(200) not null,
TAR_descripcion text
)

-- tabla RESOLUCIONES
create table RESOLUCIONES(
RES_codigo int primary key identity(1,1),
HEX_codigo int references HORAS_EXTRAS(HEX_codigo),
TAR_codigo int references TAREAS(TAR_codigo)
)

3. trigger

use planillas
go
create trigger tgUsuarios on TRABAJADORES 
for  insert
as
begin
 declare @apellidoPaterno varchar(50),
 @apellidoMaterno varchar(50),
 @nombres varchar(50),
 @dni char(8),
 @codigoTrabajador int 
select
 @codigoTrabajador=i.TRA_codigo,
 @apellidoPaterno=i.TRA_apellidoPaterno,
 @apellidoMaterno=i.TRA_apellidoMaterno,
 @nombres=i.TRA_nombres,
 @dni=i.TRA_dni

from inserted i

 -- establecer codigo
  declare @codigoUsuario varchar(200)
  --apellido al revez
   declare @apellidoMaternoRevez varchar(200)
   set @apellidoMaternoRevez=reverse(@apellidoMaterno)


  --correlativo autogenerado
   declare @apMaterno varchar(200)
   declare @correlativo int
   set @correlativo=0
   declare recorrerTrabajadore cursor read_only for
   select
    t.TRA_apellidoMaterno
   from TRABAJADORES t
   open recorrerTrabajadore
   FETCH NEXT FROM recorrerTrabajadore  INTO  @apMaterno
   WHILE @@fetch_status = 0 
   begin
    if @apMaterno=@apellidoMaterno
     set @correlativo=@correlativo+1
    FETCH NEXT FROM recorrerTrabajadore  INTO @apMaterno
   end
   
   close recorrerTrabajadore
   deallocate recorrerTrabajadore
   if @correlativo<10
    set @codigoUsuario=@apellidoMaternoRevez+'0'+cast(@correlativo as varchar(10))+'@unitru.edu.pe'
   else
    set @codigoUsuario=@apellidoMaternoRevez+cast(@correlativo as varchar(10))+'@unitru.edu.pe'
 -- establecer contraseña
 declare @contraseñaUsuario varchar(200)
  -- # letras ApMaterno Excepto Vocales (de 2 dígitos)
  declare @cantidadLetras int
  declare @tamañoApellidoMaterno int
  declare @letra char(1)
  set @tamañoApellidoMaterno=len(@apellidoMaterno)
  set @cantidadLetras=0
  while @tamañoApellidoMaterno>0
  begin
   set @letra=LOWER(SUBSTRING(@apellidoMaterno,@tamañoApellidoMaterno,1))
   if @letra!='a' and @letra!='e' and @letra!='i' and @letra!='o' and @letra!='u'
       set @cantidadLetras=@cantidadLetras+1
   set @tamañoApellidoMaterno=@tamañoApellidoMaterno-1
  end
  -- # Vocales ApPaterno
  declare @tamañoApellidoPaterno int
  declare @cantidadVocales int
  set @cantidadVocales=0
  declare @vocal char(1)
  set @tamañoApellidoPaterno=len(@apellidoPaterno)
  while @tamañoApellidoPaterno>0
  begin
   set @vocal=LOWER(SUBSTRING(@apellidoPaterno,@tamañoApellidoPaterno,1))
   if @vocal='a' or @vocal='e' or @vocal='i' or @vocal='o' or @vocal='u' 
    set @cantidadVocales=@cantidadVocales+1
   set @tamañoApellidoPaterno=@tamañoApellidoPaterno-1
  end
 if @cantidadLetras<10
  set @contraseñaUsuario='0'+cast(@cantidadLetras as varchar(20))+'&'+cast(@cantidadVocales as varchar(20))+SUBSTRING(@dni,1,2)+SUBSTRING(@dni,7,2)
 else
  set @contraseñaUsuario=cast(@cantidadLetras as varchar(20))+'&'+cast(@cantidadVocales as varchar(20))+SUBSTRING(@dni,1,2)+SUBSTRING(@dni,7,2)

 insert into USUARIOS VALUES(@codigoUsuario,@codigoTrabajador,@contraseñaUsuario)
 

end

4. prueba

insert into TRABAJADORES values('Juan','Santos','Fernandez','27965149',getdate(),'Ejecutivo',3000,0.5)
select * from USUARIOS

CREAR UN TIPO DE DATO EN SQL SERVER

En vista que SQL Server nos proporciona una serie de tipos de dato a veces necesitaremos algún tipo especial. Como el caso del DNI que cuenta con 8 caracteres de tipo CHAR o el número telefónico de 15 caracteres.