CASO PRACTICO 3
DICCIONARIO DE DATOS
CREATE TABLE LINEA(
IDLINEA CHAR(20) NOT NULL,
NOMBRELINEA CHAR(100) NOT NULL,
FINANCIADOPOR CHAR(30) NOT NULL,
CONSTRAINT PF_LINEA PRIMARY KEY (IDLINEA)
);
CREATE TABLE PROYECTO(
IDPROYECTO CHAR(20) NOT NULL,
IDLINEA CHAR(20) NOT NULL,
NOMBREPROYECTO CHAR(100) NOT NULL,
F_INICIO DATE,
F_TERMINACION DATE,
PRESUPUESTO NUMBER(10) NOT NULL,
CONSTRAINT PK_PROYECTO PRIMARY KEY (IDPROYECTO),
CONSTRAINT FK_LINEA FOREIGN KEY (IDLINEA)
REFERENCES LINEA
);
CREATE TABLE DEPACADEMICO(
IDDEPARTAMENTO NUMBER(10) NOT NULL,
NOMBREDEPARTAMENTO CHAR(30) NOT NULL,
CONSTRAINT PK_DEPACADEMICO PRIMARY KEY (IDDEPARTAMENTO)
);
CREATE TABLE INVESTIGADOR(
IFE NUMBER(13) NOT NULL,
NOMBREINVESTIGADOR CHAR(50) NOT NULL,
IDDEPARTAMENTO NUMBER(10) NOT NULL,
TELEFONO CHAR(10) NOT NULL,
EMAIL CHAR(50) NOT NULL,
CONSTRAINT PK_INVESTIGADOR PRIMARY KEY (IFE),
CONSTRAINT FK_DEPACADEMICO FOREIGN KEY (IDDEPARTAMENTO)
REFERENCES DEPACADEMICO
);
CREATE TABLE ASIGNADO (
IDASIGNADO INTEGER NOT NULL,
IDPROYECTO CHAR(20) NOT NULL,
IFE NUMBER(13) NOT NULL,
TIPOPARTICIPACION VARCHAR2(20) NOT NULL,
F_INICIO DATE NOT NULL,
F_TERMINACION DATE NOT NULL,
CONSTRAINT "PK_ASIGNADO" PRIMARY KEY (IDASIGNADO),
CONSTRAINT "FK_INVESTIGADOR" FOREIGN KEY (IFE) REFERENCES
"INVESTIGADOR",
CONSTRAINT "FK_PROYECTO" FOREIGN KEY (IDPROYECTO)
REFERENCES "PROYECTO"
);
*INVESTIGADOR
INSERT INTO INVESTIGADOR VALUES(2214650803279,'ABELARDO
RODRIGUEZ LEON',1001,2291521230,'ABE@[Link]');
INSERT INTO INVESTIGADOR VALUES(1398523080246,'ENRIQUE DEL
MORAL PEREZ',1001,2291125458,'EMORAL@[Link]');
INSERT INTO INVESTIGADOR VALUES(1285109008749,'RAFAEL RIVERA
LOPEZ',1001,2299189623,'RIVERA@[Link]');
INSERT INTO INVESTIGADOR VALUES(3954122391398,'JULIO MORALES
VILLANUEVA',1002,2292549358,'JVILLA@[Link]');
INSERT INTO INVESTIGADOR VALUES(5879634568091,'ELSY MAGALLANES
GARCIA',1002,2299326120,'ELSY@[Link]');
INSERT INTO INVESTIGADOR VALUES(3254189074252,'ROGELIO PARAMO
PEREZ',1002,2291598870,'ROGELIO@[Link]');
INSERT INTO INVESTIGADOR VALUES(1128567903429,'ANTONIO REYES
MARTINEZ',1003,2292138450,'REYESM@[Link]');
INSERT INTO INVESTIGADOR VALUES(1895623031707,'JORGE DE LEON
MARTINEZ',1003,2291932753,'LEON@[Link]');
INSERT INTO INVESTIGADOR VALUES(9327566810743,'JUAN PEREZ
GARCIA',1003,2292128552,'JPEREZ@[Link]');
INSERT INTO INVESTIGADOR VALUES(3984921817543,'KARLA ZARAZA
VARGAS',1004,2291985220,'KALY@[Link]');
INSERT INTO INVESTIGADOR VALUES(1736498724507,'FIDEL CASTRO
GARCIA',1004,2299872345,'FCASTRO@[Link]');
INSERT INTO INVESTIGADOR VALUES(0883764520912,'NICOLAS ALVAREZ
NAVARRETE',1004,2299837652,'NAVA@[Link]');
INSERT INTO INVESTIGADOR VALUES(7663892735450,'ZAIRA GRANADOS
GARCIA',1004,2291287345,'ZAIRA@[Link]');
INSERT INTO INVESTIGADOR VALUES(1234567809874,'MARIBEL HUERTA
SALAS',1004,2291287645,'HUERTA@[Link]');
INSERT INTO INVESTIGADOR VALUES(1877463028563,'NOEMI GOMEZ
CRUZ',1005,2299826734,'GOMEZ@[Link]');
INSERT INTO INVESTIGADOR VALUES(7638928375475,'RAFAEL RIVERA
LOPEZ',1005,2291521230,'ABE@[Link]');
*DEPARTAMENTO
INSERT INTO DEPACADEMICO VALUES(1001,'SISTEMAS');
INSERT INTO DEPACADEMICO VALUES(1002,'PROGRAMACION');
INSERT INTO DEPACADEMICO VALUES(1003,'ADMINISTRACION');
INSERT INTO DEPACADEMICO VALUES(1004,'BASE DE DATOS');
INSERT INTO DEPACADEMICO VALUES(1005,'ALGORITMO');
*LINEAS
INSERT INTO LINEA VALUES(1,'DESARROLLO DE TECNOLOGIA DE
INFORMACION','MONTE DE PIEDAD');
INSERT INTO LINEA VALUES(2,'COMPUTO DISTRIBUIDO','BANAMEX');
INSERT INTO LINEA VALUES(3,'ELECTRONICA DE
POTENCIA','SANTANDER');
INSERT INTO LINEA VALUES(4,'ROBOTICA','CONACYT');
*PROYECTO
INSERT INTO PROYECTO
VALUES(1,1,'MULTIPROCESAMIENTO','05/06/2010','10/06/2010','111991');
INSERT INTO PROYECTO
VALUES(2,2,'ALGORITMOS','05/06/2010','10/06/2010','333333');
INSERT INTO PROYECTO VALUES(3,3,'INGENIERIA DE SOFTWARE
WEB','05/06/2010','10/06/2010','423355');
INSERT INTO PROYECTO VALUES(5,1,'METODOLOGIAS DE DE INGENIERIA
DE SOFTWARE ORIENTADA A OBJETOS','05/06/2010','10/06/2010','145691');
INSERT INTO PROYECTO
VALUES(6,2,'MECATRONICA','05/06/2010','10/06/2010','776767');
INSERT INTO PROYECTO VALUES(7,3,'WEB
2.0','05/06/2010','10/06/2010',989989');
*ASIGNADO
INSERT INTO ASIGNADO
VALUES(1,1,'1398523080246','LIDER','05/06/2010','06/05/2010');
INSERT INTO ASIGNADO
VALUES(2,2,'2214650803279','LIDER','05/06/2010','06/05/2011');
INSERT INTO ASIGNADO
VALUES(3,3,'1285109008749','COLABORADOR','05/06/2010','06/05/2011');
INSERT INTO ASIGNADO
VALUES(4,5,'3954122391398','COLABORADOR','05/06/2010','06/05/2011');
INSERT INTO ASIGNADO
VALUES(5,6,'5879634568091','BECARIO','05/06/2010','06/05/2011');
INSERT INTO ASIGNADO
VALUES(6,7,'3254189074252','BECARIO','05/06/2010','06/05/2011');
INSERT INTO ASIGNADO
VALUES(7,1,'1128567903429','COLABORADOR','05/06/2010','06/05/2011');
INSERT INTO ASIGNADO
VALUES(8,2,'1895623031707','COLABORADOR','05/06/2010','06/05/2011');
INSERT INTO ASIGNADO
VALUES(9,2,'9327566810743','BECARIO','05/06/2010','06/05/2011');
INSERT INTO ASIGNADO
VALUES(10,3,'3984921817543','BECARIO','05/06/2010','06/05/2011');
INSERT INTO ASIGNADO
VALUES(11,5,'1736498724507','LIDER','05/06/2010','06/05/2011');
INSERT INTO ASIGNADO
VALUES(12,5,'0883764520912','BECARIO','05/06/2010','06/05/2011');
INSERT INTO ASIGNADO
VALUES(13,6,'7663892735450','LIDER','05/06/2010','06/05/2011');
INSERT INTO ASIGNADO
VALUES(14,6,'1234567809874','COLABORADOR','05/06/2010','06/05/2011');
INSERT INTO ASIGNADO
VALUES(15,7,'1877463028563','LIDER','05/06/2010','06/05/2011');
INSERT INTO ASIGNADO
VALUES(16,7,'7638928375475','BECARIO','05/06/2010','06/05/2011');
*CONSULTAS
SELECT [Link] AS NINVESTIGADOR,
[Link] AS "NOMBRE PROYECTO",
[Link] AS DEPARTAMENTO
FROM PROYECTO P, ASIGNADO A, INVESTIGADOR I, DEPACADEMICO D
WHERE [Link] = 'SISTEMAS'
AND [Link] = [Link] AND [Link] = [Link] AND
[Link] = [Link] ORDER BY NINVESTIGADOR;
SELECT [Link], [Link],
[Link], TO_CHAR (P.F_INICIO, 'YYYY/MM/DD') AS
FECHA
FROM PROYECTO P, ASIGNADO A, DEPACADEMICO D, INVESTIGADOR I
WHERE [Link] ='LIDER'
AND P.F_TERMINACION IS NULL AND [Link] =
[Link]
AND [Link] = [Link] AND [Link] = [Link];
SELECT [Link],
[Link],
[Link],
P.F_INICIO, P.F_TERMINACION
FROM PROYECTO P, ASIGNADO A, DEPACADEMICO D,
INVESTIGADOR I
WHERE [Link] = 'ABELLARDO RODRIGUEZ LEON'
AND [Link] = [Link] AND [Link] = [Link] AND
[Link] = [Link];
SELECT [Link],
[Link],
[Link]
FROM ASIGNADO A, PROYECTO P, INVESTIGADOR I
WHERE [Link] = 0
AND [Link]='LIDER'
AND [Link]=[Link] AND [Link] = [Link];
SELECT NOMBREPROYECTO "NOMBRE PROYECTO",
TO_CHAR(F_INICIO,'YYYY/MM/DD') "FECHA INICIO"
FROM PROYECTO
WHERE TO_CHAR(F_INICIO,'YYYY')= TO_CHAR(SYSDATE,'YYYY');
SELECT [Link], [Link]
FROM ASIGNADO A,
PROYECTO P,
INVESTIGADOR I,
LINEA L
WHERE [Link] > 0
AND [Link] = 'CONACYT'
AND [Link] = [Link]
AND [Link] = 'LIDER'
AND [Link] = [Link]
AND [Link] = [Link];
SELECT [Link],
TO_CHAR ([Link], '999,999,999') PRESUPUESTO
FROM LINEA L, (SELECT IDLINEA,
SUM (PRESUPUESTO) SPRESUPUESTO
FROM PROYECTO
GROUP BY IDLINEA) P
WHERE ([Link] > 100000) AND ([Link] = [Link])
ORDER BY PRESUPUESTO;
SELECT [Link], [Link] "NOMBRE
PROYECTO",
P.F_INICIO, P.F_TERMINACION "CULMINO"
FROM INVESTIGADOR I, (
SELECT [Link], [Link]. P.F_INICIO, P.F_TERMINACION
FROM PROYECTO P, ASIGNADO A, INVESTIGADOR I
WHERE NOT (P.F_TERMINACION IS NULL)
AND NOT ([Link] = 'BECARIO')
AND [Link] = [Link]
AND [Link] = [Link]) P
WHERE [Link] = [Link];
SELECT NOMBREPROYECTO "NOMBRE PROYECTO", F_INICIO "FECHA
INICIO",
F_TERMINACION "CULMINO"
FROM PROYECTO
WHERE NOT (F_TERMINACION IS NULL);
SELECT NOMBREPROYECTO "NOMBRE PROYECTO", F_INICIO "FECHA
INICIO",
F_TERMINACION "CULMINO"
FROM PROYECTO
WHERE F_TERMINACION IS NULL;
SELECT NOMBREPROYECTO "NOMBRE PROYECTO",
TO_CHAR(PRESUPUESTO,'9,999,999') PRESUPUESTO FROM PROYECTO
WHERE PRESUPUESTO >= (SELECT AVG (PRESUPUESTO)
FROM PROYECTO WHERE PRESUPUESTO > 0);
SELECT NOMBREINVESIGADOR "NOMBRE INVESTIGADOR"
FROM INVESTIGADOR I
WHERE ([Link] NOT IN (SELECT DISTINCT ([Link])
FROM PROYECTO P, ASIGNADO A,
INVESTIGADOR I
WHERE PRESUPUESTO = 0
AND [Link] = [Link]
AND [Link] = [Link]))
AND ([Link] IN (SELECT DISTINCT ([Link])
FROM PROYECTO P, ASIGNADO A, INVESTIGADOR I
WHERE [Link] = [Link]
AND [Link] = [Link]))
ORDER BY NOMBREINVESIGADOR;
