Caso práctico. Diplomado

Se desea realizar un Sistema Informático para que los alumnos de un Tecnológico Virtual puedan consultar sus calificaciones a través de la Web. La especificación proporcionada es la siguiente:

A continuación, se muestra el esquema relacional de la base de datos:

Se pide

1. Implementación del esquema para Oracle

1.1. Definir las correspondientes tablas recogiendo las restricciones del esquema relacional anterior: integridad de entidad, integridad referencial, opciones de borrado y actualización, obligatoriedad…

DROP TABLE imparte        CASCADE CONSTRAINTS;
DROP TABLE calificacion   CASCADE CONSTRAINTS;
DROP TABLE usuario        CASCADE CONSTRAINTS;
DROP TABLE alumno         CASCADE CONSTRAINTS;
DROP TABLE diplomado      CASCADE CONSTRAINTS;
DROP TABLE asignatura     CASCADE CONSTRAINTS;

CREATE TABLE alumno ( 
  nControl        CHAR(9)  NOT NULL,
  nombre          CHAR(20) NOT NULL,
  apellido1       CHAR(12) NOT NULL,
  apellido2       CHAR(12) NOT NULL,
  fechaNacimiento DATE,
  direccion       CHAR(35),
  CONSTRAINT PK_alumno PRIMARY KEY (nControl)
);


CREATE TABLE diplomado (
  idDiplomado        CHAR(3)   NOT NULL,
  nombrediplomado    CHAR(30)  NOT NULL,
  precio             NUMBER(4) NOT NULL,  
  CONSTRAINT DOM_diplomado_Precio CHECK ( precio > 0),
  CONSTRAINT PK_diplomado         PRIMARY KEY ( idDiplomado )
);

CREATE TABLE asignatura (
  idAsignatura       CHAR(4)   NOT NULL,
  nombreAasignatura  CHAR(50)  NOT NULL,  
  creditos           NUMBER(2) NOT NULL,
  CONSTRAINT DOM_asignatura_Creditos CHECK ( creditos > 0 ), 
  CONSTRAINT PK_asignatura           PRIMARY KEY ( idAsignatura )
);

CREATE TABLE usuario (
  nControl  CHAR(9)  NOT NULL,
  login     CHAR(12) NOT NULL,
  password  CHAR(6)   NOT NULL,
  CONSTRAINT PK_usuario PRIMARY KEY ( nControl ),
  CONSTRAINT FK_alumno  FOREIGN KEY ( nControl ) 
                         REFERENCES  alumno ON DELETE CASCADE
);

CREATE TABLE imparte (
  idDiplomado  CHAR(3) NOT NULL, 
  idAsignatura CHAR(4) NOT NULL, 
  CONSTRAINT FK_ASIGNATURA FOREIGN KEY ( idAsignatura )
                            REFERENCES  asignatura ( idAsignatura ) 
                           ON DELETE CASCADE, 
  CONSTRAINT FK_diplomado FOREIGN KEY (idDiplomado)
                           REFERENCES  diplomado ( idDiplomado )
                           ON DELETE CASCADE
);

1.2. Simule mediante CHECKS las restricciones que no se pueden modelar mediante el grafo relacional, o que no pueden ser implementadas directamente en Oracle. Por ejemplo calificación entre 0 y 100.

CREATE TABLE calificacion (
  idAsignatura  CHAR(4)  NOT NULL,
  nControl      CHAR(9)  NOT NULL,
  semestre      CHAR(10) NOT NULL,
  calificacion  NUMBER(3)  DEFAULT 0 NOT NULL,
  CONSTRAINT   DOM_Calificacion 
            CHECK (( calificacion >= 0) AND ( calificacion <= 100)), 
  CONSTRAINT   FK_alumnos   FOREIGN KEY (nControl)
                             REFERENCES alumno
                             ON DELETE CASCADE,
  CONSTRAINT  FK_Asignatura FOREIGN KEY ( idAsignatura )
                             REFERENCES asignatura
                             ON DELETE CASCADE
);

2. Inserciones

2.1. Realizar las siguientes inserciones en la tabla de alumnos

Número de Control Apellido 1 Apellido 2 Nombre
E06020024 ACOSTA AMADOR ARTURO
E06021506 AGUILERA PEREZ LILIANA LIZBET
E05020337 COBOS ORTEGA ANGEL RAUL
E06230874 ANGHEVEN AGUIRRE BERNARDO DAGOBERTO
E06021504 BARRADAS GARCIA EDNA ASTRID
E06020038 CASTILLO ALARCON JORGE ABRAHAM
E06020043 CISNEROS ROMO CLAUDIA ISABEL
E06021495 ESCALANTE ESPEJO WILLIAM
E07020091 FERMAN GONZáLEZ YAZMíN GUADALUPE
E06020070 FIGUEROA FIGUEROA ROSA ISELA
E05020054 FLORES CHáVEZ URIEL
E06021450 HERNANDEZ PONTIGO ERIK SAMUEL
E05020376 MEDINA CORDOBA KARLA NAYELI
E05020364 LARA HORN MARCO ANTONIO
E06021530 MAYA PEREZ INDA ASTRID
E06021442 MENDEZ FIERRO SEBASTIAN
E06020129 MONTENEGRO FIERRO SANDRA GLENDA
E07020166 MORALES NAVARRO RODRIGO EUGENIO
E06021482 PARTIDA VAZQUEZ JOSE DE JESUS
E06021500 RAMIREZ REYES RAQUEL
E06021440 RENTERAL OLIVOS BRENDA GUADALUPE
E06020090 HERNANDEZ LOYO ROBERTO
E06022607 RODRIGUEZ RONQUILLO ALDO DE JESUS
E06021432 ROJAS GARCIA ANGEL ARTURO
E07020095 ROSAS HERNáNDEZ PEDRO ANTONIO
E06020177 RUIZ MARRON DIANA
E06020181 SANCHEZ VILLARAUS URI
E06020185 SANTOS MASSON FRANCISCO JAVIER
E07020098 VALERIO ORTíZ SILVIA GUADALUPE
E07020096 ZáRATE AGUILAR JULISSA

2.2. Para insertar en la tabla usuario el login será la inicial del nombre concatenado a los 7 primeros carácteres del apellido1. Si el apellido1 es menor de 7 carácteres, habrá que rellenar con ceros hasta completar la longitud total. La password será un número entero positivo de 6 dígitos que se deberá generar de forma aleatoria. Antes de insertar la tupla, habrá que comprobar que no hay ningún login repetido, en cuyo caso se deberá utilizar un secuencial en la octava posición del mismo

Ejemplos:

Para el alumno Juan Rodríguez, el login sería jrodrigu.

Para la alumna Ana Pazo, el login sería apazo000.

Para el alumno José Rodríguez, el login sería jrodrig1, para diferenciarlo del login de Juan Rodríguez.

INSERT INTO usuario (nControl,login,password) 
   SELECT nControl, 
   RPAD(SUBSTR(nombre,1,1) || SUBSTR(apellido1,1,7),8,'X'),
   ROUND(DBMS_RANDOM.VALUE(100000,999999))
   FROM alumno;

2.3. Ejecutar una consulta sobre la tabla de usuario para comprobar que los valores se han insertado correctamente

SELECT * FROM usuario;

3. Inserciones en la tablas ASIGNATURA, DIPLOMADO e IMPARTE

3.1. Teniendo en cuenta el plan mostrado, insertar los valores correspondientes en las tablas diplomado, asignatura e imparte.

Diplomado Asignatura Créditos
Administración de Empresas (001) Contabilidad analítica (0001) 10
Planificación y Gestión de Activos (0002) 25
Microeconomía (0003) 15
Programación (002) Fundamentos de programación (0004) 5
Estructuras de Datos (0005) 5
Compiladores e Intérpretes (0006) 10
Diseño de BD (0007) 10
Dirección de proyectos (003) Metodologías de análisis (0008) 5
Planificación de proyectos (0009) 5
Ingeniería del Software (0010) 10
Seguimiento de proyectos (0011) 20
Aseguramiento calidad y control de cambios (0012) 10
Sistemas de Información (004) Programación (0013) 5
Análisis y diseño de SI (0014) 5
Evolución de las TIC (0015) 15
Aplicaciones informáticas (0016) 5
Ingenieria para WEB (0017) 10

INSERT INTO diplomado  VALUES ('001', 'Administracion de Empresas',200);
INSERT INTO diplomado  VALUES ('002', 'Programacion', 230);
INSERT INTO diplomado   VALUES ('003', 'Direccion de proyectos', 210);
INSERT INTO diplomado  VALUES ('004', 'Sistemas de  Informacion', 250);

INSERT INTO asignatura VALUES ('0001','Contabilidad analitica', 10);
INSERT INTO asignatura VALUES ('0002','Planificacion y Gestion de  activos', 25);

INSERT INTO imparte VALUES ('001','0001');
INSERT INTO imparte VALUES ('001','0002');

4. Inscripción a diplomados

4.1. Asigne las asignaturas a los alumnos y note que la calificación inicial es cero

INSERT INTO calificacion (idasignatura,nControl,semestre) 
  VALUES ('0001','E05020337','ENEJUN2009');
INSERT INTO calificacion (idasignatura,nControl,semestre) 
  VALUES ('0001','E06020024','ENEJUN2009');
INSERT INTO calificacion (idasignatura,nControl,semestre) 
  VALUES ('0002','E06020024','ENEJUN2009');

4.2. Use la sentecia UPDATE para modificar aleatoriamente el campo calificación de la tabla calificiaciones.

UPDATE calificaciones 
SET  calificacion = ROUND(DBMS_RANDOM.VALUE(0,100));
UPDATE calificaciones SET calificacion = 0 WHERE calificacion <70;

4.3. Obtener el alumno con mejor promedio por diplomado

DROP TABLE re;

CREATE TABLE re AS
SELECT d.nombrediplomado,
	 al.nombre,
	 TO_CHAR(AVG(c.calificacion),'900.99') AS promedio
FROM imparte       i,
	   calificacion  c,
	   asignatura    a,
	   diplomado     d,
	   alumno        al
WHERE  a.idasignatura = c.idasignatura
AND	 c.nControl      = al.nControl
AND	 d.iddiplomado   = i.iddiplomado
AND	 i.idasignatura  = a.idasignatura
GROUP BY d.nombrediplomado, al.nombre
ORDER BY d.nombrediplomado, al.nombre;

SELECT d.nombreDiplomado, d.nombre, sc.maximoPromedio
FROM re d, 
       (SELECT nombreDiplomado, MAX(promedio) maximoPromedio
        FROM re
        GROUP BY nombreDiplomado) sc
WHERE (d.nombreDiplomado = sc.nombreDiplomado) 
   AND (d.promedio >= sc.maximoPromedio)
ORDER BY  d.nombreDiplomado, sc.maximoPromedio DESC;

4.4. Obtener el costo por diplomado

SELECT d.nombreDiplomado AS nombreDiplomado,
	 TO_CHAR(SUM(a.creditos*d.precio),'99,999') AS costo
FROM asignatura a,
	   imparte    i,
	   diplomado  d 
WHERE  d.idDiplomado = i.idDiplomado
   AND i.idAsignatura = a.idAsignatura
GROUP BY d.nombreDiplomado
ORDER BY costo;

5. Agregar un diplomado con sus asignaturas y alumnos.

6. Traducir el ejemplo a MySQL.



Anterior
Valid XHTML
home

Siguiente