Escuela de Informática y Telecomunicaciones elecomunicaciones
GUÍA DE PL/SQL N! USAND" #U$S"$ES E%PLÍ#IT"S El &roceso automático 'ue Ud( construy) &ara el cálculo de &romedios del cole*io SAN +UAN ya fue e,ecutado &or cada docente y se o-tu.ieron las notas &romedios &ara cada una de las asi*naturas de los alumnos( Almacenada esta informaci)n en la ase de Datos y -asados en el 0odelo 'ue se muestra a continuaci)n1 de-erá satisfacer los re'uerimientos de informaci)n &lanteados en cada caso y 'ue son &arte del acuerdo contractual entre Ud( y la direcci)n del cole*io2
Para crear y poblar las tablas del Modelo efectúe lo siguiente: Conéctese a la base de datos como usuario S3STE0 y ejecute el archivo • scri&t4creaci)n4usuario4*u5a4PLSQL4N6! que creará el usuario &4&ls'! password &ractica( Conéctese posteriormente como usuario &4&ls'! y ejecute el archivo scri&t4creaci)n4ta-las4*u5a4PLSQL4N6! • para crear y poblar las tablas tablas del Modelo como se muestra muestra en el ejemplo:
TALA #U$S"
TALA ASIGNATU$A
TALA ALU0N"
Escuela de Informática y Telecomunicaciones
TALA P$"7ES"$
TALAS P$"0EDI"4ASIG4ALU0N"
TALA 8"$A$I"4P$"7ES"$
Escuela de Informática y Telecomunicaciones
Escuela de Informática y Telecomunicaciones
9(: a direcci!n del colegio desea contar en forma urgente con un listado de la situaci!n de cada asignatura de los alumnos" Por esta ra#!n y dada la urgencia de contar esta informaci!n$ por ahora s!lo es necesario que sea visuali#ada a través de un bloque P%&' (n!nimo ya que a través de &')eveloper el listado también podrá ser guardado en archivo (t;t" *l informe requiere el apellido paterno y primer nombre +concatenado, del alumno$ nombre de la asignatura$ promedio y situaci!n" &i la situaci!n de la asignatura es ( se debe mostrar (probado y si es - se debe mostrar -eprobado" a informaci!n se debe mostrar ordenada por apellido de alumno$ nombre del alumno y nombre de la asignatura en forma ascendente y al ejecutar el bloque se deber.a visuali#ar en el for mato que se muestra en el ejemplo" &e solicita además que sea guardado en un archivo con el nombre listado4situacion4asi*(t;t"
)*C(-* C/-&0- cur1emp 2& select a"appat1alumno 33 4 4 33 a"pnombre1alumno as alumno$ asig"nombre as asignatura$ case p"situacion1asig when 4(4 then 4(probado4 when 4-4 then 4-eprobado4 end as situacion from promedio1asig1alumno p join alumno a on a"cod1alumno 5 p"cod1alumno join asignatura asig on asig"cod1asignatura 5 p"cod1asignatura order by a"appat1alumno$ a"pnombre1alumno$ asig"nombre6 0-)*7 7/M8*-:596 8*27 )8M&10/;P/;"P/;127*+42&;()0 &2;/(C207 )* (&27(;/-(& )* 0& (/M70&4,6 )8M&10/;P/;"P/;127*+4555555555555555555555555555555555555555555555554,6 )8M&10/;P/;"P/;127*+4 4,6 )8M&10/;P/;"P/;127*+40-)*7 (/M70 (&27(;/-( &2;/(C2074,6 )8M&10/;P/;"P/;127*+455555555555555555555555555555555555555555555555555555555555554,6 <0- reg1emp 27 cur1emp 00P )8M&10/;P/;"P/;127*+ ;01C=(-+0-)*7, 33 4 4 33 reg1emp"(/M70 33 4 4 33 reg1emp"(&27(;/-( 33 4 4 33 reg1emp"&2;/(C207 ,6 0-)*7:50-)*7>96 *7) 00P6 *7)6 <(: )ebido a un incendio en la de profesores del colegio$ los libros de clases se debieron volver a confeccionar pero la imprenta los entregará en una semana más" )ebido a esto$ se requiere poder entregar a cada profesor un listado para registrar la asistencia a clases de los alumnos$ requerimiento que le han solicitado a /d" poder resolver"
Escuela de Informática y Telecomunicaciones *l listado debe mostrar el rut y nombre completo del alumno además de los d.as de la semana de lunes a viernes para registrar la asistencia por cada curso" a informaci!n se requiere en orden alfabético y al ejecutar el bloque P%&' el listado debe mostrar la informaci!n para todos los cursos en el formato del ejemplo y se requiere en un archivo con el nombre asistencia4alumnos4&or4curso(t;t"
Escuela de Informática y Telecomunicaciones
)*C(-* C/-&0- cursos 2& &**C; cod1curso$ descripcion <-0M curso6 C/-&0- alumnos+p1curso 7/M8*-, 2& &**C; to1char+numrut1alumno$ 4???$???$???4, 33 4@4 33 dvrut1alumno as rut$ trim+pnombre1alumno, 33 4 4 33 trim+snombre1alumno, 33 4 4 33 trim+appat1alumno, 33 4 4 33 trim+apmat1alumno, as nombre <-0M alumno A=*-* cod1curso 5 p1curso6 v1nro1alumno 7/M8*-+B,6 8*27 <0- reg1curso 27 cursos 00P )8M&10/;P/;"P/;127*+4(sistencia 433 reg1curso"descripcion,6 )8M&10/;P/;"P/;127*+455555555555555555555555555555555555554,6 )8M&10/;P/;"P/;127*+47 -/; (/M70 70M8-* (/M70 /7*& M(-;*& M2*-C0*& D/*E*& E2*-7*&4,6 )8M&10/;P/;"P/;127*+455555555555555555555555555555555555555555555555555555555555555 555555555555555555555555555555554,6 v1nro1alumno :5 F6 <0- reg1alumno 27 alumnos+reg1curso"cod1curso, 00P v1nro1alumno :5 v1nro1alumno > 96 )8M&10/;P/;"P/;127*+rpad+to1char+v1nro1alumno,$G$4 4, 33 rpad+reg1alumno"rut$BF$4 4, 33 rpad+reg1alumno"nombre$ HF$4 4 , 33 4 0 0 0 0 04,6 *7) 00P6 )8M&10/;P/;"P/;127*+4555555555555555555555555555555555555555555555555555555555555 555555555555555555555555555555555555554,6 )8M&10/;P/;"P/;127*+4;otal (lumnos del curso: 4 33 v1nro1alumno,6 )8M&10/;P/;"7*A127*+,6 )8M&10/;P/;"7*A127*+,6 *7) 00P6 *7)6
=(: a direcci!n desea que automatice el proceso de cálculo de promedios finales de los alumnos el que además deberá permitir actuali#ar la informaci!n de los alumnos que mantendrán su beca de estudio en los casos que correspondan" &e
Escuela de Informática y Telecomunicaciones deben procesar todos los alumnos$ por cada uno de ellos$ y de acuerdo a las pol.ticas del colegio$ obtener la informaci!n de acuerdo a las siguientes especificaciones: 9" os valores se redondean a un decimal" B" &i el alumno aprob! todas las asignaturas$ el alumno aprueba el aIo académico + situaci)n final A, y si el promedio de notas es mayor a J$K mantiene la beca de estudio si es que la posee + -ecado de-e 'uedar >S?," G" &i el alumno reprob! todas sus asignaturas$ el alumno reprueba el aIo académico + situaci)n final $ , y pierde su beca de estudio si es que la posee + -ecado de-e 'uedar NUL"," H" &i el alumno reprob! ambas asignaturas cuyo c!digo finali#a en 9 ! B el alumno reprueba el aIo académico +situaci)n final $ , y pierde su beca de estudio si es que la posee + -ecado de-e 'uedar NUL"," J" &i el alumno reprob! una de las asignaturas cuyo c!digo finali#a en 9 ! B$ pero aprob! todas las otras asignaturas y el promedio de todas las notas es superior a J$F$ el alumno aprueba el aIo académico + situaci)n final A," &i el promedio de notas es igual o inferior a J$F el alumno reprueba el aIo académico + situaci)n final $ ," *n ambos casos el alumno pierde la beca de estudio si es que la posee + -ecado de-e 'uedar NUL"," L" &i el alumno reprob! una de las asignaturas cuyo c!digo finali#a en 9 ! B$ además reprob! una de las otras asignaturas y el promedio de todas las notas es superior a H$K$ el alumno aprueba el aIo académico + situaci)n final A," &i el promedio de notas es igual o inferior a H$K el alumno reprueba el aIo académico + situaci)n final $ ," *n ambos casos el alumno pierde la beca de estudio si es que la posee + -ecado de-e 'uedar NUL"," " &i el alumno reprob! una de las asignaturas cuyo c!digo finali#a en 9 ! B$ además reprob! más de una de las otras asignaturas y el promedio de todas las notas es superior a J$F$ el alumno aprueba el aIo académico + situaci)n final A," &i el promedio de notas es igual o inferior a J$F el alumno reprueba el aIo académico + situaci)n final $ ," *n ambos casos el alumno pierde la beca de estudio si es que la posee + -ecado de-e 'uedar NUL"," K" &i el alumno aprob! las dos asignaturas cuyo c!digo finali#a en 9 ! B$ pero reprob! más de una o todas las otras asignaturas y el promedio de todas las notas es superior a J$F$ el alumno aprueba el aIo académico + situaci)n final A, y mantiene la beca de estudio si es que la posee + -ecado de-e 'uedar >S?," &i el promedio de notas es igual o inferior a J$F el alumno reprueba el aIo académico + situaci)n final $ , y pierde la beca de estudio si es que la posee + -ecado de-e 'uedar NUL", ?" &i el alumno aprob! las dos asignaturas cuyo c!digo finali#a en 9 ! B$ además aprob! dos de las otras asignaturas y el promedio de todas las notas es superior a J$J$ el alumno aprueba el aIo académico + situaci)n final A, y mantiene la beca de estudio si es que la posee + -ecado de-e 'uedar >S?," &i el promedio es igual o inferior a J$J el alumno reprueba el aIo académico + situaci)n final $ , y pierde su beca de estudio si es que la posee + -ecado de-e 'uedar NUL"," (l finali#ar la ejecuci!n del bloque$ el resultado deber.a ser el que se muestra en el ejemplo:
TALA P$"0EDI"47INAL4ALU0N"
TALA ALU0N"
DECLARE CURSOR ALUMNO IS SELECT A.COD_ALUMNO, A.COD_CURSO FROM ALUMNO A; CURSOR ASIGNATURA(P_COD_ALUMNO NUMBER) IS SELECT COD_ALUMNO, COD_ASIGNATURA, PROMEDIO_ASIG, SITUACION_ASIG FROM PROMEDIO_ASIG_ALUMNO WHERE COD_ALUMNO=P_COD_ALUMNO; V_PROMEDIO_FINAL NUMBER; V_SITUACION VARCHAR(01); V_BECA VARCHAR2(01); V_NRO_ASIG_R NUMBER(02); V_NRO_ASIG_A NUMBER(02); V_NRO_ASIG_OTRA NUMBER(02); V_NRO_ASIG_OTRA_A NUMBER(02); V_ASIG_1 VARCHAR(01); V_ASIG_2 VARCHAR(01); V_SITUACION_CONDICION_ VARCHAR(01);
Escuela de Informática y Telecomunicaciones BEGIN FOR R_ALUMNO IN ALUMNO LOOP !!PUNTO 1 " 2 SELECT ROUND(AVG(PROMEDIO_ASIG),1) INTO V_PROMEDIO_FINAL FROM PROMEDIO_ASIG_ALUMNO WHERE COD_ALUMNO = R_ALUMNO.COD_ALUMNO; IF V_PROMEDIO_FINAL # $.% THEN V_SITUACION &= 'A'; V_BECA &= 'S'; ELSE V_NRO_ASIG_R &= 0; V_NRO_ASIG_A &= 0; V_NRO_ASIG_OTRA &= 0; V_NRO_ASIG_OTRA_A &= 0; FOR R_ASIG_ALUMNO IN ASIGNATURA(R_ALUMNO.COD_ALUMNO) LOOP !!! PUNTO IF R_ASIG_ALUMNO.SITUACION_ASIG = 'R' THEN V_NRO_ASIG_R &= V_NRO_ASIG_R 1; ELSE V_NRO_ASIG_A &= V_NRO_ASIG_A 1; END IF; !!! PUNTO PARTE IF SUBSTR(R_ASIG_ALUMNO.COD_ASIGNATURA,*,1) NOT IN ('1','2') AND R_ASIG_ALUMNO.SITUACION_ASIG = 'R' THEN V_SITUACION_CONDICION_ &= 'R'; !!! PUNTO + PARTE V_NRO_ASIG_OTRA &= V_NRO_ASIG_OTRA 1; ELSE !!!PUNTO V_NRO_ASIG_OTRA_A &= V_NRO_ASIG_OTRA_A 1; END IF; END LOOP; !!! LOOP DE ASIGNATURAS POR ALUMNO END IF; !! PRINCIPAL
!!! PUNTO IF V_NRO_ASIG_R -# 0 AND V_NRO_ASIG_A = 0 THEN V_SITUACION &= 'R'; V_BECA &= NULL; END IF; !!! PUNTO * SELECT SITUACION_ASIG INTO V_ASIG_1 FROM PROMEDIO_ASIG_ALUMNO WHERE SUBSTR(COD_ASIGNATURA,*,1) = 1 AND COD_ALUMNO = R_ALUMNO.COD_ALUMNO; SELECT SITUACION_ASIG INTO V_ASIG_2 FROM PROMEDIO_ASIG_ALUMNO WHERE SUBSTR(COD_ASIGNATURA,*,1) = 2 AND COD_ALUMNO = R_ALUMNO.COD_ALUMNO; IF V_ASIG_2 = 'R' AND V_ASIG_1 = 'R' THEN V_SITUACION &= 'R'; V_BECA &= NULL; ELSIF (V_ASIG_2 = 'R' OR V_ASIG_1 = 'R') AND V_PROMEDIO_FINAL # $.0 THEN V_SITUACION &= 'A'; V_BECA &= NULL; ELSIF (V_ASIG_2 = 'R' OR V_ASIG_1 = 'R') AND V_PROMEDIO_FINAL -= $.0 THEN V_SITUACION &= 'R'; V_BECA &= NULL; !!!! PUNTO ELSIF (V_ASIG_2 = 'R' OR V_ASIG_1 = 'R') AND V_SITUACION_CONDICION_ = 'R' AND V_PROMEDIO_FINAL # *.% THEN V_SITUACION &= 'A'; V_BECA &= NULL; ELSIF (V_ASIG_2 = 'R' OR V_ASIG_1 = 'R') AND V_SITUACION_CONDICION_ = 'R' AND V_PROMEDIO_FINAL -= *.% THEN V_SITUACION &= 'R'; V_BECA &= NULL; !!!! PUNTO + ELSIF (V_ASIG_2 = 'R' OR V_ASIG_1 = 'R') AND V_NRO_ASIG_OTRA # 1 AND V_PROMEDIO_FINAL # $.0 THEN V_SITUACION &= 'A'; V_BECA &= NULL; ELSIF (V_ASIG_2 = 'R' OR V_ASIG_1 = 'R') AND V_NRO_ASIG_OTRA # 1 AND V_PROMEDIO_FINAL -= $.0 THEN V_SITUACION &= 'R'; V_BECA &= NULL; !!!! PUNTO % ELSIF (V_ASIG_2 = 'A' / V_ASIG_1 = 'A') AND V_NRO_ASIG_OTRA # 1 AND V_PROMEDIO_FINAL # $.0 THEN V_SITUACION &= 'A'; V_BECA &= 'S'; ELSIF (V_ASIG_2 = 'A' / V_ASIG_1 = 'A') AND V_NRO_ASIG_OTRA # 1 AND V_PROMEDIO_FINAL -= $.0 THEN V_SITUACION &= 'R'; V_BECA &= NULL; !!!! PUNTO ELSIF (V_ASIG_2 = 'A' / V_ASIG_1 = 'A') AND V_NRO_ASIG_OTRA_A #= 2 AND V_PROMEDIO_FINAL # $.$ THEN V_SITUACION &= 'A'; V_BECA &= 'S'; ELSIF (V_ASIG_2 = 'A' / V_ASIG_1 = 'A') AND V_NRO_ASIG_OTRA_A #= 2 AND V_PROMEDIO_FINAL -= $.$ THEN V_SITUACION &= 'R'; V_BECA &= NULL; END IF; !!! ACTUALIA EN LAS TABLAS DE PROMEDIO FINAL ALUMNO " ALUMNO
UPDATE PROMEDIO_FINAL_ALUMNO SET PROMEDIO_FINAL= V_PROMEDIO_FINAL,
Escuela de Informática y Telecomunicaciones SITUACION_FINAL= V_SITUACION WHERE COD_ALUMNO = R_ALUMNO.COD_ALUMNO AND COD_CURSO = R_ALUMNO.COD_CURSO; IF SL3NOTFOUND THEN INSERT INTO PROMEDIO_FINAL_ALUMNO VALUES(R_ALUMNO.COD_ALUMNO,R_ALUMNO.COD_CURSO,V_PROMEDIO_FINAL,V_SITUACION); END IF; UPDATE ALUMNO SET BECADO = V_BECA WHERE COD_ALUMNO = R_ALUMNO.COD_ALUMNO; END LOOP; COMMIT; END;
@(: Como una forma de incentivar el buen trabajo que han desempeIado los profesores durante el aIo académico$ la direcci!n del colegio incluirá en el salario del mes de diciembre el pago de un bono especial de acuerdo a la asignatura que dictaron y al total de horas semanales asignadas" Por lo tanto$ por cada profesor procesado$ se debe calcular su bono especial de acuerdo a las asignaturas y al total de horas semanales correspondiente a cada una de las asignaturas que dicta" Para desarrollar este proceso$ las consideraciones son las siguientes: *l cálculo de cada bono por profesor debe ser almacenado en la tabla 80701P-0<*&0- +redondeado, que debe • tener la siguiente estructura:
N"0$E DE #"LU0NA TIP" DE DAT" C0)1P-0<*&0-
• •
•
•
•
•
=0-(&1(&219B
7umérico de largo +obligatorio, 7umérico de largo B
=0-(&10;-(&1(&2
7umérico de largo B
M07;018070
7umérico de +obligatorio,
largo
IN7"$0A#IN QUE AL0A#ENA$B J
L
Clave primaria de la tabla que contiene el c!digo del profesor ;otal de horas semanales de las asignaturas con c!digo que finali#a en 9 ! B que el docente dicta" ;otal de horas semanales de las otras G asignaturas que el docente dicta" Monto del bono especial calculado para el docente"
os docentes que dictan asignaturas con c!digo que finali#a en 9 ! B no dictan las otras asignaturas y viceversa" &i el docente dicta asignaturas cuyo c!digo finali#a con 9 y%o B y su total de horas semanales de clases es ma yor o igual a BF el monto del bono será el JFN de su sueldo base" &i el docente dicta asignaturas cuyo c!digo finali#a con 9 y%o B y su total de horas semanales de clases es menor a BF el monto del bono será el GFN de su sueldo base" &i el docente no dicta asignaturas cuyo c!digo finali#a con 9 ! B y su total de horas semanales de clases es mayor o igual a 9J el monto del bono será el BFN de su sueldo base" &i el docente no dicta asignaturas cuyo c!digo finali#a con 9 ! B y su total de horas semanales de clases es menor a 9J y mayor o igual a K el monto del bono será el 9JN de su sueldo base" &i el docente no dicta asignaturas cuyo c!digo finali#a con 9 ! B y su total de horas semanales de clases es menor a K el monto del bono será el 9FN de su sueldo base"
(l finali#ar el proceso$ el resultado deber.a ser como se muestra en el ejemplo:
TALA "N"4P$"7ES"$
#$EATE TALE "N"4P$"7ES"$ Ccod4&rofesor NU0E$C! #"NST$AINT P4"N"4P$"7ES"$ P$I0A$3 E31 Foras4asi*49< NU0E$C<1 Foras4otras4asi* NU0E$C<1 monto4-ono NU0E$C! N"T NULL
Escuela de Informática y Telecomunicaciones !(: Para efectos administrativos$ la direcci!n debe contar con informaci!n estad.stica de porcentaje de efectividad de cada docente en cada una de las asignaturas que dicta" *l proceso que /d" desarrolle debe generar en forma simultánea informaci!n resumida por cada profesor e informaci!n detallada por cada curso en que el docente dict! la asignatura de acuerdo a las siguientes especificaciones: a informaci!n resumida por docente debe quedar almacenada en la tabla -*&/M*71(&21P-0<*&0- que • debe tener la siguiente estructura:
N"0$E DE #"LU0NA TIP" DE DAT" C0)1P-0<*&0- C0)1(&27(;/-(
;0;(1(/M70& ;0;(1(P-08()0& ;0;(1-*P-08()0& P0-C1(P-08()0&
P0-C1-*P-08()0&
•
7umérico de +obligatorio, 7umérico de +obligatorio,
IN7"$0A#IN QUE AL0A#ENA$B
largo
J
largo
H
7umérico de largo G +obligatorio, 7umérico de largo G +obligatorio, 7umérico de largo G +obligatorio, 7umérico de largo H con 9 decimal +obligatorio, 7umérico de largo H con 9 decimal +obligatorio,
Clave primaria de la tabla que contiene el c!digo del profesor" Clave primaria de la tabla que contiene el c!digo de la asignatura que el profesor dict!" ;otal de alumnos a los que el profesor dict! la asignatura" ;otal de alumnos que aprobaron la asignatura que dict! el profesor" ;otal de alumnos que reprobaron la asignatura que dict! el profesor" Porcentaje +redondeado a un decimal, de alumnos que aprobaron la asignatura que el profesor dict!" Porcentaje +redondeado a un decimal, de alumnos que reprobaron la asignatura que el profesor dict!"
a informaci!n detallada por cada curso en que el profesor dict! la asignatura debe quedar almacenada en la tabla )*;(*1(&21P-0<*&0- que debe tener la siguiente estructura:
N"0$E DE #"LU0NA TIP" DE DAT" C0)1P-0<*&0-
IN7"$0A#IN QUE AL0A#ENA$B
7umérico de +obligatorio, 7umérico de +obligatorio,
largo
J
largo
H
C0)1C/-&0
7umérico de +obligatorio,
largo
J
;0;1(/M70&1C/-&0
7umérico de largo G +obligatorio, 7umérico de largo G +obligatorio, 7umérico de largo G +obligatorio, 7umérico de largo H con 9 decimal +obligatorio,
C0)1(&27(;/-(
;0;1(P-081C/-&0 ;0;(1-*P-081C/-&0 P0-C1(P-081C/-&0
P0-C1-*P-081C/-&0
7umérico de largo H con9 decimal +obligatorio,
Clave primaria de la tabla que contiene el c!digo del profesor" Clave primaria de la tabla que contiene el c!digo de la asignatura que el profesor dict!" Clave primaria de la tabla que contiene el c!digo del curso en que el profesor dict! la asignatura ;otal de alumnos del curso" ;otal de alumnos del curso que aprobaron la asignatura que dict! el profesor" ;otal de alumnos del curso que reprobaron la asignatura que dict! el profesor" Porcentaje +redondeado a un decimal, de alumnos que aprobaron la asignatura en el curso" Porcentaje +redondeado a un decimal, de alumnos que reprobaron la asignatura en el curso"
(l finali#ar el proceso$ los resultados deber.an ser como se muestran en el ejemplo:
Escuela de Informática y Telecomunicaciones TALA $ESU0EN4ASIG4P$"7ES"$
TALA DETALLE4ASIG4P$"7ES"$
