proyectofinal bd equipo 9

BASES DE DATOS

PROFESIONISTAS

Proyecto BD

Equipo 9

20/05/2014

Integrantes: González Benítez David Natanael. Morales Olivares Cristian. Torres Olivera Karen Alejandra.

PROFESIONISTAS Equipo: 9

2

Requerimientos.

Se desea crear un sistema de Base de Datos que permita obtener información sobre la

situación actual de los profesionistas que trabajan en las empresas ubicadas dentro de la

Cuidad de México. El sistema debe contestar las siguientes preguntas:

i) Dado un grado académico (licenciatura, maestría o doctorado) y cierto

monto en pesos, conocer el nombre y la edad de aquellos profesionistas

que tengan este grado y que ganen un sueldo mayor al monto especificado.

ii) Dado el nombre de cierta Universidad y el nombre de cierta Empresa,

conocer el número de egresados de esta Universidad que trabajen en dicha

Empresa.

iii) Dada cierta área de las profesiones (por ejemplo: computación), conocer el

nombre, domicilio, puestos y sueldos de todos aquellos profesionistas

egresados de esta área.

iv) Conocer el número de profesionistas que existen dentro de cada área y

grado académico.

v) Conocer el domicilio de cada Universidad, así como el nombre de las

carreras que imparte cada una de estas Universidades.

vi) Conocer el domicilio de todas las Empresas ubicadas dentro de la Cuidad de

México.


3

Diagrama Entidad – Relación


4

Modelo Relacional


5

Creación de la Base de Datos: PROFESIONISTAS

/* CREAR BASE DE DATOS */

use master

go

create database PROFESIONISTAS on default="32M"

go

Creación de tablas, llaves foráneas y primarias según corresponda.

/* TABLA EMPRESA*/ create table EMPRESA ( ID_EMP char(5) not null, NOM_EMP char(25) null, DOMICILIO_E char(50) null, constraint PK_EMPRESA primary key nonclustered (ID_EMP) ) go


6

/*TABLA UNIVERSIDAD*/ create table UNIVERSIDAD ( ID_UNI char(5) not null, NOM_UNI char(30) null, DOMICILIO_U char(50) null, constraint PK_UNIVERSIDAD primary key nonclustered (ID_UNI) ) go


7

/*TABLA AREA_PROFESIONAL*/ create table AREA_PROFESIONAL ( ID_AREA char(5) not null, NOM_AREA char(20) null, CARRERA char(20) null, constraint PK_AREA_PROFESIONAL primary key nonclustered (ID_AREA) ) go


8

/*TABLA PROFESIONISTA*/ create table PROFESIONISTA ( ID_PROF char(4) not null, ID_UNI char(5) null, ID_EMP char(5) null, ID_AREA char(5) null, NOM_PROF char(40) null, EDAD numeric(2) null, DOMICILIO_P char(50) null, G_ACADEMICO char(15) null, PUESTO char(30) null, SUELDO numeric(5) null, foreign key (ID_UNI) references UNIVERSIDAD(ID_UNI), foreign key (ID_AREA) references AREA_PROFESIONAL(ID_AREA), foreign key (ID_EMP) references EMPRESA(ID_EMP), constraint PK_PROFESIONISTA primary key nonclustered (ID_PROF) ) go


9

/*TABLA IMPARTE*/ create table IMPARTE ( ID_AREA char(5) not null, ID_UNI char(5) not null, constraint PK_IMPARTE primary key (ID_AREA, ID_UNI) ) go


10

Creación de reglas, constraints o defaults que permitan la

integridad de dominio y/o reglas de negocio.

alter table IMPARTE

add constraint FK_IMPARTE_IMPARTE_AREA_PRO foreign key (ID_AREA)

references AREA_PROFESIONAL (ID_AREA)

go

alter table IMPARTE

add constraint FK_IMPARTE_IMPARTE2_UNIVERSI foreign key (ID_UNI)

references UNIVERSIDAD (ID_UNI)

go

alter table PROFESIONISTA

add constraint FK_PROFESIO_EGRESAN_UNIVERSI foreign key (ID_UNI)

references UNIVERSIDAD (ID_UNI)

go


11


add constraint FK_PROFESIO_ESTUDIAN_AREA_PRO foreign key (ID_AREA)


go


add constraint FK_PROFESIO_TRABAJAN_EMPRESA foreign key (ID_EMP)

references EMPRESA (ID_EMP)

go

alter table UNIVERSIDAD

add constraint FK_UNIVERSI_RELATIONS_AREA_PRO foreign key (ID_AREA)


go


12

Alta de 10 registros a las tablas catálogo de la Bases de Datos y los

registros correspondientes en las tablas relación.

/* Inserción de datos en table UNIVERSIDAD*/ insert into UNIVERSIDAD values ('UNI01','UNAM','Av. Universidad No. 3000') insert into UNIVERSIDAD values ('UNI02','IPN','Av. Instituto Politecnico Nacional No. 1936') insert into UNIVERSIDAD values ('UNI03','UAM','Prolongacion Canal de Miramontes 3855') insert into UNIVERSIDAD values ('UNI04','UNILA','Pedro Henriquez Urena 173') insert into UNIVERSIDAD values ('UNI05','ULA','Gabriel Mancera 1402') insert into UNIVERSIDAD values ('UNI06','UVM','Tlalpan 3058') insert into UNIVERSIDAD values ('UNI07','ICEL','Acueducto No. 25') insert into UNIVERSIDAD values ('UNI08','Insurgentes','Tlalpan 4051') insert into UNIVERSIDAD values ('UNI09','ITAM','Rio Hondo 1') insert into UNIVERSIDAD values ('UNI10','UNITEC','Ermita Iztapalapa 557')


13

/* Inserción de datos en table EMPRESA*/ insert into EMPRESA values ('EMP01','BANORTE','Churubusco 34') insert into EMPRESA values ('EMP02','ISSSTE','Av. Universidad 1321') insert into EMPRESA values ('EMP03','TELEVISA','Calzada de Tlalpan 3000') insert into EMPRESA values ('EMP04','TECSIR.SA','Ignacio Zaragoza 250') insert into EMPRESA values ('EMP05','TELMEX','Eje Central 4') insert into EMPRESA values ('EMP06','PEMEX','Marina Nacional 329') insert into EMPRESA values ('EMP07','Ciber Cafe','Romero Rubio 70') insert into EMPRESA values ('EMP08','Banco de Mexico','Av. 5 de Mayo 2') insert into EMPRESA values ('EMP09','Bueso y Bueso Abogados','Insurgentes Sur 1024') insert into EMPRESA values ('EMP10','Porrua','Ninos Heroes 132')


14

/* Inserción de datos en table AREA_PROFESIONAL */

insert into AREA_PROFESIONAL values ('ARE01','Computacion','Informatica') insert into AREA_PROFESIONAL values ('ARE02','Leyes','Derecho') insert into AREA_PROFESIONAL values ('ARE03','Humanidades','Escritor') insert into AREA_PROFESIONAL values ('ARE04','Comunicacion','Periodista') insert into AREA_PROFESIONAL values ('ARE05','Ciencias','Fisica') insert into AREA_PROFESIONAL values ('ARE06','Matematicas','Actuaria') insert into AREA_PROFESIONAL values ('ARE07','Artes','Canto') insert into AREA_PROFESIONAL values ('ARE08','Medicina','Odontologia') insert into AREA_PROFESIONAL values ('ARE09','Quimica','Farmaceutico') insert into AREA_PROFESIONAL values ('ARE10','Arte Grafica','Diseño grafico')


15

/* Inserción de datos en table AREA_PROFESIONAL */

insert into PROFESIONISTA values ('PR01','UNI05','EMP06','ARE01','Gabriela Aguileta',23,'Tlalpan 345','Licenciatura','Programador',30000) insert into PROFESIONISTA values ('PR02','UNI02','EMP04','ARE05','Ramon Bravo',23,'Churubusco 453','Maestria','fisico',20000) insert into PROFESIONISTA values ('PR03','UNI07','EMP07','ARE01','Lydia Cacho',27,'Miramontes 213','Maestria','Dueño',21000) insert into PROFESIONISTA values ('PR04','UNI10','EMP03','ARE04','Pablo Hiriart',25,'Xoco 452','Doctorado','Periodista',30000) insert into PROFESIONISTA values ('PR05','UNI01','EMP02','ARE09','Guadalupe Sandoval',30,'Universidad 3000','Doctorado','Quimico',34000) insert into PROFESIONISTA values ('PR06','UNI08','EMP06','ARE09','Margarita Peña',29,'Bugambilia 19','Maestria','Empleado',22000) insert into PROFESIONISTA values ('PR07','UNI06','EMP04','ARE05','Tomas Segovia',32,'Ermita Iztapalapa 234','Licenciatura','Supervisor',7100) insert into PROFESIONISTA values ('PR08','UNI03','EMP10','ARE03','Enrique Serna',28,'La tortuga 234','Licenciatura','Editor',9230) insert into PROFESIONISTA values ('PR09','UNI09','EMP10','ARE10','Fernando Vallejo',22,'Division del Norte 24','Doctorado','Diseñador',6400) insert into PROFESIONISTA values ('PR10','UNI04','EMP02','ARE08','Carlos Fuentes',24,'Insurgentes 123','Doctorado','Dentista',2340) insert into PROFESIONISTA values ('PR11','UNI03','EMP03','ARE10','Angeles Mastretta',31,'Tlalpan 745','Maestria','Periodista',9000) insert into PROFESIONISTA values ('PR12','UNI09','EMP09','ARE02','Laura Esquivel',30,'Camarones 234','Doctorado','Asesor Legal',8400)


16

/* Inserción de datos en table IMPARTE*/ insert into IMPARTE values ('ARE01','UNI10') insert into IMPARTE values ('ARE07','UNI08') insert into IMPARTE values ('ARE06','UNI03') insert into IMPARTE values ('ARE01','UNI09') insert into IMPARTE values ('ARE05','UNI01') insert into IMPARTE values ('ARE08','UNI04') insert into IMPARTE values ('ARE04','UNI02') insert into IMPARTE values ('ARE03','UNI05') insert into IMPARTE values ('ARE10','UNI06') insert into IMPARTE values ('ARE02','UNI07')


17

Creación de índices en campos líderes de consulta común.

create index índice_id_prof on PROFESIONISTAS(ID_PROF)

create index índice_id_uni on UNIVERSIDAD(ID_UNI)

create index índice_id_area on AREA_PROFESIONAL(ID_AREA)

Procedimientos almacenados

CREATE PROCEDURE sp_ingresarUNIVERSIDAD(@ID_UNI char(5), @NOM_UNI char(30), @DOMICILIO_U char(50)) as BEGIN INSERT INTO UNIVERSIDAD VALUES (@ID_UNI,@NOM_UNI,@DOMICILIO_U) END


18

CREATE PROCEDURE sp_actualizaEMPRESA (@ID_EMP char(5),@nuevo_NOMB_EMP char(25)) AS BEGIN UPDATE EMPRESA SET NOM_EMP=@nuevo_NOMB_EMP WHERE ID_EMP=@ID_EMP END

CREATE PROCEDURE sp_borraAREA_PROFESIONAL(@ID_AREA char(5)) AS BEGIN DELETE FROM AREA_PROFESIONAL WHERE ID_AREA=@ID_AREA END


19

Triggers necesarios para el control de la integridad de la

información.

create trigger borraUNIVERSIDAD on UNIVERSIDAD for delete as delete from PROFESIONISTA where PROFESIONISTA.ID_UNI in ( select UNIVERSIDAD.ID_UNI from UNIVERSIDAD, deleted where UNIVERSIDAD.ID_UNI = deleted.ID_UNI) go


20

create trigger borraAREA_PROFESIONAL on AREA_PROFESIONAL for delete as delete from PROFESIONISTA where PROFESIONISTA.ID_AREA in ( select AREA_PROFESIONAL.ID_AREA from AREA_PROFESIONAL,deleted where AREA_PROFESIONAL.ID_AREA = deleted.ID_AREA ) go

create trigger borraEMPRESA on EMPRESA for delete as delete from PROFESIONISTA where PROFESIONISTA.ID_EMP in ( select EMPRESA.ID_EMP from EMPRESA,deleted where EMPRESA.ID_EMP = deleted.ID_EMP ) go


21

Creación de consultas, que satisfagan la hoja de requerimientos

Dado un grado académico (licenciatura, maestría o doctorado) y cierto monto en pesos,

conocer el nombre y la edad de aquellos profesionistas que tengan este grado y que

ganen un sueldo mayor al monto especificado.

select NOM_PROF, EDAD from PROFESIONISTA where (G_ACADEMICO='Licenciatura' and SUELDO>9000)

Dado el nombre de cierta Universidad y el nombre de cierta Empresa, conocer el

número de egresados de esta Universidad que trabajen en dicha Empresa.

select count(*) from PROFESIONISTA where (ID_UNI in (select ID_UNI from UNIVERSIDAD where NOM_UNI='UNAM') and ID_EMP in (select ID_EMP from EMPRESA where NOM_EMP='TELEVISA'))


22

Dada cierta área de las profesiones (por ejemplo: computación), conocer el nombre,

domicilio, puestos y sueldos de todos aquellos profesionistas egresados de esta área.

select NOM_PROF, DOMICILIO_P, PUESTO, SUELDO from PROFESIONISTA where ID_AREA in (select ID_AREA from AREA_PROFESIONAL where NOM_AREA='Computacion')


23

Conocer el número de profesionistas que existen dentro de cada área y grado académico. select NOM_AREA, count(*) as PROF from AREA_PROFESIONAL, PROFESIONISTA where AREA_PROFESIONAL.ID_AREA=PROFESIONISTA.ID_AREA group by NOM_AREA

select G_ACADEMICO, count(*) as PROF from PROFESIONISTA group by G_ACADEMICO


24

Conocer el domicilio de cada Universidad, así como el nombre de las carreras que imparte cada una de estas Universidades. select DOMICILIO_U, CARRERA from UNIVERSIDAD, AREA_PROFESIONAL where AREA_PROFESIONAL.ID_AREA in (select ID_AREA from IMPARTE where ID_UNI in (select ID_UNI from UNIVERSIDAD))

Conocer el domicilio de todas las Empresas ubicadas dentro de la Cuidad de México.

select DOMICILIO_E from EMPRESA

proyectofinal bd equipo 9

Documents