Capitulo III BASES DE DATOS ACTIVAS SIS 306 BASE DE DATOS III Ing. Edgar Espinoza R.
En la USFX se desea llevar un control sobre los proyectos de investigación que se desarrollan. Para ello se decide emplear una Base de Datos que contenga toda la información sobre los proyectos, departamentos, grupos de investigación y profesores. Esta información se detalla a continuación: Un departamento se identifica por su nombre (informática, Ingenieria, etc.). Tiene una sede situada en un determinado campus, un teléfono de contacto y un director, que ha de ser un profesor de esa Universidad. Dentron de un departamento se crean grupos de investigación. Cada grupo tiene un nombre único dentro del departamento (pero que puede ser el mismo en distintos departamentos) y esta asociado a un área de conocimiento (bases de datos, inteligencia artificial, sistemas y comunicaciones, etc.). Cada grupo tiene un líder que ha de ser profesor de la Universidad. Un profesor esta identificado por su CI. De el se desea saber el nombre, titulación, anos de experiencia en investigación, grupo de investigación en el que desarrolla su labor y proyectos en los que trabaja. Cada proyecto de investigación tiene un nombre, un código único, un presupuesto, fechas de inicio y terminación y un grupo que lo desarrolla. Por otro lado, puede estar financiado por varios programas. Dentro de cada programa, cada proyecto tiene un numero asociado y una cantidad de dinero fianciado (por ejemplo, el proyecto “BDC-Bases de Datos en Colores” tiene el numero 3113 dentro del programa “Ayudas al Pobre investigado” que le financia con Bs. 30,000). Un profesor puede participar en varios proyectos. En cada proyecto se incorpora en una determinada fecha y cesa en otra, teniendo una determinada dedicación (en horas a la semana) durante ese periodo.
Diseñe el esquema relacional .Indique en el las claves primarias , claves alternativas y las claves ajenas, nulos indicando las opciones de borrado y modificación, etc.
ANALISIS DE LOS SUPUESTOS SEMANTICOS “Un departamento se identifica por su nombre (Informática, Ingeniería, etc.). Tiene una sede situada en un determinado campus……”
Opciones de Borrado y Modificación: Las opciones de integridad referencial de ambas claves ajenas pueden ser en cascada. REGLAS ECA: Si consideramos que todo departamento tiene que tener al menos una sede y que en cualquier sede al menos hay un departamento, debemos asegurar estas cardinalidades mediante disparadores.
“…todo departamento tiene un director que ha de ser profesor de la universidad.”
Opciones de borrado y de modificación: Si se elimina un profesor director de algún departamento, se debe controlar que no se elimine el departamento, por tanto el borrado debe ser con puesta a nulos o restringido, sin embargo la modificación puede hacerse en cascada.
“Dentro de cada departamento se crean grupos de investigación…..., además cada grupo tiene un líder que es un profesor… Se desea conocer el grupo al que pertenece cada profesor.”
Opciones de borrado y modificación: Si se elimina un departamento no tiene sentido dejar los grupos pertenecientes al mismo. Por tanto el borrado se hace en cascada, al igual que la modificación. Ademas. Si se elimina un grupo se debe poner a nulos la clave ajena “Grupo” de la tabla profesor, por que de no hacerlo eliminaríamos a todos los porfesores, pertenecientes al grupo y esto no se puede admitir. En cuanto a la modificación, debe hacerse en cascada. En cuanto a la clave ajena de GRUPO, “Lider”, su eliminación seria con puesta a nulos, por que en caso contrario, si cambia el líder de un grupo lo eliminaríamos de la relación PROFESOR, caso que no esta admitido
Reglas ECA: Deberíamos controlar mediante un disparador que, cuando se inserte un nuevo grupo, exista el departamento correspondiente y, en caso contrario insertar primero el departamento.
“Cada proyecto de investigación tiene un nombre…. Y un grupo que desarrolla. Por otro lado, puede estar financiado por varios programas”
Opciones de borrado y modificación:
Un borrado y la modificación dela clave ajena grupo puede ser en cascada, pues si se elimina o se modifica un grupo, es lógico que se eliminen o modifiquen todos los proyectos que llevaba dicho grupo. Lo mismo sucede con las claves ajenas “CodigoP”y ”NombreProg” de la relación FINANCIA
Restricciones de rechazo: Deberíamos comprobar mediante un CHECK que la fecha de inicio del proyecto es anterior a la fecha de finalización. Se debería controlar mediante una sercion que el presupuesto de cada proyecto sea siempre mayor o igual que la suma de las financiaciones otorgadas por cada programa a cada uno de los proyectos.
“Un profesor puede participar en varios proyectos. En cada proyecto se incorpora en una determinada fecha y cesa en otra, teniendo una determinada dedicación (en horas a la semana) durante ese periodo”
Opciones de borrado y modificación: Los borrados de las dos claves ajenas de la relación PARTICIPA deben ser restringidos para poder guardar al información acerca de las fechas y la dedicación a los proyectos, de cada profesor. La modificación en cualquiera de los dos casos es en cascada.
Reglas ECA: Cuando se introduzca un nuevo proyecto, en el deberá participar al menos un profesor, lo que podríamos controlar con un disparador que, por ejemplo, cada vez que insertemos un nuevo proyecto, se le asigne al menos un profesor de los existentes y en caso contrario rechazar la inserción.
Restricciones de rechazo: Se debería controlar con un CHECK que las fechas de inicio de participación en cada proyecto sean anteriores a las fechas de cese.
SCRIPT SQL 92
CREATE DOMAIN NombreValido CHAR(30); CREATE DOMAIN TipoCodigo CHAR(5); CREATE DOMAIN TipoCodigo CHAR(5); CREATE DOMAIN TipoCI CHAR(9);
CREATE TABLE Sede( NombreSede NombreValido, Campus NombreValido, PRIMARY KEY (NombreSede) ); CREATE TABLE Departamento( CodigoD NombreValido, Sede NombreValido, Telefono CHAR(9) NOT NULL, Director NombreValido, PRIMARY KEY (NombreP, Sede) );
CREATE TABLE Ubicacion( CodigoD NombreValido, NombreSede DireccionValida, PRIMARY KEY (NombreP, Sede), FOREING KEY (codigoD) REFERENCES Departamento ON UPDATE CASCADE, FOREING KEY (NombreSede) REFERENCES Sede ON UPDATE CASCADE ); CREATE TABLE Grupo( NombreG NombreValido, CodigoD NombreValido, Area NombreValido, Lider NombreValido, PRIMARY KEY (NombreG, CodigoD) ); CREATE TABLE Profesor( CI TipoCI, NombreProf NombreValido, Titulacion CHAR(20 NOT NULL), Experiencia INTEGER, Grupo NombreValido, PRIMARY KEY (CI) );
ALTER TABLE Departamento ADD FOREING KEY (Director) REFERENCES Profesor ON DELETE SET NULL ON UPDATE CASCADE; ALTER TABLE Grupo ADD FOREING KEY (CodigoD) REFERENCES Departamento ON DELETE CASCADE ON UPDATE CASCADE ADD FOREING KEY (Lider) REFERENCES Profesor ON DELETE SET NULL ON UPDATE CASCADE; ALTER TABLE Profesor ADD FOREING KEY (Grupo) REFERENCES Grupo ON DELETE SET NULL ON UPDATE CASCADE;
CREATE TABLE Proyecto( CodigoP TipoCodigo, NombreP NombreValido, Presupuesto INTEGER NOT NULL, Fini DATE NOT NULL, Ffin DATE, Grupo NombreValido, PRIMARY KEY (CodigoP), UNIQUE (NombreP), CHECK (Fini
CREATE TABLE Participa( CI TipoCI, CodigoP TipoCodigo, Finicio INTEGER NOT NULL, FCese DATE Dedicacion INTEGER NOT NULL, PRIMARY KEY (CI, CodigoP), CHECK(FInicio
CREATE TABLE Programa( NombreProg NombreValido, PRIMARY KEY (NombreProg)); CREATE TABLE Proyecto( NombreProg NombreValido, CodigoP TipoCodigo, NumeroP TipoCodigo, Financiacion INTEGER, PRIMARY KEY (NombreProg, CodigoP), FOREING KEY (CodigoP) REFERENCES Proyecto ON UPDATE CASCADE ON DELETE CASCADE FOREING KEY (NombreProg) REFERENCES Programa ON UPDATE CASCADE ON DELETE CASCADE );
CREATE ASSERTION Subvencion CHECK (( SELECT Presupuesto FROM Proyecto NATURAL JOIN FINANCIA< (SELECT SUM(Financiacion) FROM Financia NATURAL JOIN Programa));
EXAMEN LA PRACTICA DEBERA CONTEMPLAR LOS SIGUIENTES ASPECTOS: Diagrama E-R Diseñe el esquema relacional Indique en el las claves primarias , claves alternativas y las claves ajenas indicando las opciones de borrado y modificación. Marque con un asterisco (*) los campos que acepten valores nulos.
TODO EN DOCUMENTO WORD Script de creación de la BD Scripts de los Triggers y Procedimientos almacenados
TODO ESTO EN BLOCK DE NOTAS LA PRESENTACION DEBERA HACERSE EN CDROM Backups de la BD
