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.

CURSORES EN SQL SERVER

Los cursores permiten tener el control fila por fila de un conjunto de registros.
¿en que caso usamos cursores?, puesto que si ya tenemos acceso alas filas de una tabla uno a uno entonces podremos obtener dichos valores y controlarlos desde variables locales y así poder acumular valores, contarlos, aplicar quiebres, etc.

ejemplo en sql server(triggers, cursores)

 1) modelado de base de datos




2) script: crear las tablas

create database BDMatricula;
use BDMatricula;
go

create table alumno(
idAlumno int primary key identity(1,1),
nombres varchar(50) not null,
apellidos varchar(50) not null
)

create table curso(
idCurso int primary key identity(1,1),
nombre varchar(50) not null,
creditos smallint not null default(3),
)

create table preRequisito(
idCurso int references curso(idCurso),
curso int references curso(idCurso)
)
create table seccion(
idSeccion int primary key identity(1,1),
nombre varchar(50) not null,
)
create table horario(
idSeccion int references seccion(idSeccion),
idCurso int references curso(idCurso),
vacantes int not null default(0),
dia varchar(10) not null,
horaInico int not null,
horaFinal int not null
)
create table nota(
idCurso int references curso(idCurso),
idAlumno int references alumno(idAlumno),
nota1 int  default(0),
nota2 int  default(0),
nota3 int  default(0),
nota4 int  default(0)
)

create table cronograma(
idCronograma int primary key identity(1,1),
inicioMatricula date not null default getdate(),
finMatricula date not null
)

create table matricula(
idMatricula int primary key identity(1,1),
idAlumno int references alumno(idAlumno),
fecha date default getdate()
)
create table detalleMatricula(
idMatricula int references matricula(idMatricula),
idCurso int references curso(idCurso),
idSeccion int references seccion(idSeccion)
)
create table registrarMatricula(
idMatricula int references matricula(idMatricula),
idCronograma int references cronograma(idCronograma),
recibo varchar(50) not null,
totalCreditos int not null
constraint creditos check( totalCreditos>=12 and totalCreditos<=22)
)

3)trigger para detalle matrícula

create trigger tgDetalleMatricula on detalleMatricula for insert, update
as
begin
 declare @idAlumno int
 declare @idSeccion int
 declare @idMatricula int
 declare @idCurso int
 select
  @idMatricula=i.idMatricula,
  @idCurso=i.idCurso,
  @idSeccion=i.idSeccion,
  @idAlumno=m.idAlumno
 from inserted i
 inner join matricula m on i.idMatricula=m.idMatricula
 -- b.4 Debe haber vacante en la sección donde desea matricularse
 declare @vacantes int
 select
  @vacantes=vacantes
 from horario
 where idSeccion=@idSeccion and idCurso=@idCurso
 if @vacantes=0
 begin
  raiserror ('no hay vacantes disponibles', 16, 2)
     rollback transaction 
 end
end
go

4) trigger para registrar matrícula

create trigger tgRegistraMatricula on registrarMatricula
for insert, update
as
begin
 declare @idMatricula int
 declare @idAlumno int
 declare @idCronograma int

 select
  @idMatricula=idMatricula,
  @idCronograma=idCronograma
 from inserted
 select @idAlumno=idAlumno
 from matricula
 -- a.1El registro de la matrícula solamente se puede efectuar
 -- en las fechas establecidas en el cronograma;
 declare @inicioMatricula date
 declare @finMatricula date
 declare @fechaMatricula date
 select
  @inicioMatricula=inicioMatricula,
  @finMatricula=finMatricula
 from cronograma
 where idCronograma=@idCronograma;
 select
  @fechaMatricula=fecha
 from matricula
 where idMatricula=@idMatricula;
 
 if @fechaMatricula<@inicioMatricula or @fechaMatricula>@finMatricula
 begin
  raiserror ('las matrícula se realizó fuera de fecha', 16, 2)
     rollback transaction
 end
 -- a.2 se debe verificar el pago de matrícula registrando
 -- el número de recibo respectivo
 -- solución: registrarMatricula.recibo es not null por lo tanto
 -- si o si tiene que ingresarse el campo recibo

 -- b.1 La nota del alumno, en el curso pre requisito
 --  del Plan de estudios actual, debe estar aprobado
 declare @nota1 int,@nota2 int,@nota3 int, @nota4 int
 declare @desaprobado int
 set @desaprobado=0
 declare infoNotasPreRequisitos cursor read_only for
 select
  n.nota1,
  n.nota2,
  n.nota3,
  n.nota4
 from matricula m
 join alumno a on m.idAlumno=m.idAlumno
 join detalleMatricula dm on dm.idMatricula=m.idMatricula
 join curso c on dm.idCurso=c.idCurso
 join preRequisito pr on pr.idCurso=c.idCurso
 join nota n on n.idCurso=pr.curso
 where m.idMatricula=@idMatricula;
 open infoNotas
 FETCH NEXT FROM infoNotasPreRequisitos INTO @nota1,@nota2,@nota3,@nota4
 WHILE @@fetch_status = 0
 begin
  if @nota1<=10 and @nota2<=10 and @nota3<=10 and @nota4<=10
   set @desaprobado=1
  FETCH NEXT FROM infoNotasPreRequisitos INTO @nota1,@nota2,@nota3,@nota4
 end
 close infoNotasPreRequisitos
 if @desaprobado=1
 begin
  raiserror ('cursos pre requisitos no aprobados', 16, 2)
     rollback transaction  
 end

 --  El total de créditos no debe ser menor de 12, ni mayor a 22 créditos.
 declare @totalCreditos int
 declare @creditos int
 set @totalCreditos=0
 declare cTotalCreditos cursor read_only for
 select c.creditos
 from matricula m
 inner join detalleMatricula dm on dm.idMatricula=m.idMatricula
 inner join curso c on dm.idCurso=c.idCurso
 where m.idMatricula=1
 open cTotalCreditos
 FETCH NEXT FROM cTotalCreditos  INTO @creditos
 WHILE @@fetch_status = 0
 BEGIN
  set @totalCreditos=@totalCreditos+@creditos
  FETCH NEXT FROM cTotalCreditos  INTO @creditos
 END
 close cTotalCreditos
 if @totalCreditos<12 or @totalCreditos>22
 begin
  raiserror ('total de créditos no válidos', 16, 2)
     rollback transaction 
 end
-- b.3 El alumno no puede tener una asignatura con más de 3 notas desaprobadas
 set @desaprobado=0
 declare notasAlumno cursor read_only for
 select
 nota1,nota2,nota3,nota4
 from nota
 where idAlumno=@idAlumno
 open notasAlumno
 FETCH NEXT FROM notasAlumno  INTO @nota1,@nota2,@nota3,@nota4
 WHILE @@fetch_status = 0
 BEGIN
  if @nota1<=10 and @nota2<=10 and @nota3<=10 and @nota4<=10
   set @desaprobado=1
  FETCH NEXT FROM notasAlumno  INTO @nota1,@nota2,@nota3,@nota4
 END
 close notasAlumno
 if @desaprobado=1
 begin
  raiserror ('Más de tres notas desaprobatorias', 16, 2)
     rollback transaction 
 end 

 --c1. Considerar que al confirmarse el registro de la matricula del alumno;
 --se actualizará la vacante del curso
 
 
 declare cambiarVacantes cursor read_only for
 select
  idCurso,
  idSeccion
 from detalleMatricula dm
 where dm.idMatricula=@idMatricula
 open cambairVacantes
  FETCH NEXT FROM cambairVacantes  INTO @idCurso,@idSeccion
 WHILE @@fetch_status = 0
 BEGIN
  update horario
  set vacantes=vacantes-1
  where idCurso=@idCurso and idSeccion=@idSeccion
  FETCH NEXT FROM cambairVacantes  INTO @idCurso,@idSeccion
 END
 close cambairVacantes

end

MANEJO DE ESQUEMAS EN SQL SERVER

El esquema de una base de dato describe la estructura de la misma ya que todos los objetos que se puede crear o administra en SQL server se encuentra dentro de un esquema estándar .
El manejo de esquemas responde a temas como agrupación de objetos y seguridad de las mismas. Cuando se crea un objeto de base de datos o se especifica una entidad de seguridad de dominio válida(usuario o grupo) como la propietaria del objeto, o la entidad de seguridad de dominio se agregará a la base de datos como esquema. Esa entidad  de seguridad  de dominio será la propietaria del nuevo esquema

sintaxis de creación:

CREATE SCHEMA nombreEsquema AUTHORIZATION nombrePropietario

sintaxis de eliminación de esquemas:
para eliminar un esquema debe tener en cuenta que no tenga objetos asociado a él. Por lo tanto, la sitaxis reflejará los pasos a seguir:
DROP TABLE
GO
DROP nombreEsquema SCHEMA AUTHORIZATION nombrePropietario
GO

ejemplo:

-- implemente los esquemas PAGO dentro de la base de datos BDMatricula

use BDMatricula
go
create schema pago authorization dbo
go

select * from sys.schemas where principal_id=1

Tomar en cuenta que hay varios esquemas dentro de una base de datos, pero la identificación asociada al propietario DBO es uno por ese motivo la consulta se condiciona a ese valor por la columna PRINCIPAL_ID.