bdii-2013 parte iii

SQL Server y Visual Baisc - Lic. Victor Pozo Díaz

1

2013Victor Pozo Díaz

PRACTICA EN LABORATORIO

BASE DE DATOS II

SISTEMA DE SEGUIMIENTO Y CONTROL COMISARIA II

Enunciado

Se desea recoger en una base de datos información acerca del funcionamiento interno de unacomisaria de policía. Se consideren los siguientes supuestos:

En la comisaria trabajan una serie de policías (cod_pol, paterno, materno, nombres, foto,fecha_nac, función), que pueden desempeñar funciones distintas: administrativos, agentes, etc.

En la comisaria existe un arsenal de armas. Cada arma esta identificada por un código único,pertenece a una clase y tiene un nombre determinado.

Cada policía puede utilizar una o varias armas en un momento determinado. Es importanteconocer el grado de habilidad (puntuación de 1 a 10) de cada policía con cada una de las armas queutiliza.

Un delincuente (cod_del, paterno, materno, nombres, sexo, obs, foto) es arrestado por uno ovarios policías. A cada delincuente que permanezca en la comisaria se le encierra en un calabozo(cod_cal, ubicación, observaciones). En el calabozo pueden estar encerrados varios delincuentes.

Los delincuentes están involucrados en casos (cod_caso, juzgado, tipo, descripción); interesa sabercuál es el principal cargo (robo, homicidio, etc.), que se le imputa a un delincuente en cada delitoen que esta involucrado. Uno o varios policías investigan cada uno de los casos.

MODELO ENTIDAD RELACION.

POLICIA

ARMACASO

DELINCUENTE

CALABOZOINVOLUCRADO

INVESTIGA

ARRESTAN N

N

N

N

N

1

1


2


GRAFO RELACIONAL

POLICIA (COD_POL, PATERNO, MATERNO, NOMBRES, FOTO, FECHA_NAC, FUNCION)

ARMA (COD_ARMA,CLASE,NOM_ARMA,NOTA,COD_POL)

INVESTIGA (COD_POL.COD_CASO)

CASO (COD_CASO, JUZGADO, TIPO, DESCRIPCION)

INVOLUCRADO (COD_DEL,COD_CASO,CARGO)

DELINCUENTE (COD_DEL,PATERNO,MATERNO,NOMBRES,SEXO,OBS,FOTO,COD_CAL)

CALABOZO (COD_CAL,UBICACIÓN,OBSERVACIONES)

ARRESTA (COD_POL,COD_DEL,FECHA)

CREACION DE LA BASE DE DATOS Y LAS TABLAS EN SQL SERVER

§ Crear la base de datos COMISARIA(SQL-SERVER)§ Crear el origen de datos ORIGENCOM§ Crear las siguientes estructura de tipos de objetos(Tablas):


3


Diseño de la base de datos


4


POLICIA

ARMA

CASO

CALABOZO


5


DIAGRAMA DE BASE DE DATOS

Los diagramas de base de datos representan gráficamente las tablas en la base de datos,muestran las columnas que las tablas contienen, las relaciones entre las tablas, y los índices yrestricciones de las tablas.

§ Expandir base de datos COMISARIA§ Botón derecho en Diagramas§ Clic en Nuevo diagrama de Base de Datos§ Seguir los pasos del Asistente

Nota.- Para establecer las relaciones arrastrar el Mouse sobre la llave primaria hasta lallave foránea de la segunda tabla. Para establecer más de una llave primaria marcar loscampos designados a ser llaves principales y hacer clic en el icono


6


Otra forma de crear la estructura de la BD (COMISARIA), es ejecutar el siguiente Script.

CREATE DATABASE COMISARIAGOUSE COMISARIAGOCREATE TABLE POLICIA(COD_POL CHAR(10) NOT NULL,PATERNO CHAR(20),MATERNO CHAR(20),NOMBRES CHAR(30) NOT NULL,FOTO IMAGE,FECHA_NAC CHAR(10),FUNCION CHAR(20));GO

CREATE TABLE ARMA(COD_ARMA INT IDENTITY(100,1) NOT NULL,CLASE CHAR(20) NOT NULL,NOM_ARMA CHAR(30),NOTA INT CHECK(NOTA>0 AND NOTA<11),COD_POL CHAR(10));GO

CREATE TABLE CASO(COD_CASO INT IDENTITY(500,1) NOT NULL,JUSGADO CHAR(20) NOT NULL,TIPO CHAR(20),DESCRIPCION CHAR(50));GO

CREATE TABLE INVESTIGA(COD_POL CHAR(10) NOT NULL,COD_CASO INT NOT NULL);GO

CREATE TABLE CALABOZO(COD_CAL INT IDENTITY(1000,1) NOT NULL,UBICACION CHAR(30) NOT NULL,OBSERVACIONES CHAR(100));GO

CREATE TABLE DELINCUENTE(COD_DEL INT IDENTITY(2000,1) NOT NULL,PATERNO CHAR(20),MATERNO CHAR(20),NOMBRES CHAR(30) NOT NULL,SEXO CHAR(1),OBSERVACIONES CHAR(100),FOTO IMAGE,COD_CAL INT);GO


7


CREATE TABLE INVOLUCRADO(COD_DEL INT NOT NULL,COD_CASO INT NOT NULL,CARGO CHAR(50) NOT NULL);GO

CREATE TABLE ARRESTA(COD_POL CHAR(10) NOT NULL,COD_DEL INT NOT NULL,FECHA DATETIME NOT NULL);GO

/*LLAVES PRIMARIAS Y FORANEAS*/

ALTER TABLE POLICIAADD CONSTRAINT PK_POLICIA_COD_POL PRIMARY KEY(COD_POL)GO

ALTER TABLE ARMAADD

CONSTRAINT PK_ARMA_COD_ARMA PRIMARY KEY(COD_ARMA),CONSTRAINT FK_ARMA_COD_POL FOREIGN KEY(COD_POL) REFERENCES POLICIA(COD_POL)

GO

ALTER TABLE CASOADD CONSTRAINT PK_CASO_COD_CASO PRIMARY KEY(COD_CASO)

GO

ALTER TABLE CALABOZOADD

CONSTRAINT PK_CALABOZO_COD_CAL PRIMARY KEY(COD_CAL)GO

ALTER TABLE DELINCUENTEADD CONSTRAINT PK_DELINCUENTE_COD_DEL PRIMARY KEY(COD_DEL), CONSTRAINT FK_DELINCUENTE_COD_CAL FOREIGN KEY(COD_CAL) REFERENCES

CALABOZO(COD_CAL)GO

ALTER TABLE INVOLUCRADOADD CONSTRAINT PK_INVOLUCRADO_COD_DEL_COD_CASO PRIMARY KEY(COD_DEL,COD_CASO), CONSTRAINT FK_INVOLUCRADO_COD_DEL FOREIGN KEY(COD_DEL) REFERENCES

DELINCUENTE(COD_DEL), CONSTRAINT FK_INVOLUCRADO_COD_CASO FOREIGN KEY(COD_CASO) REFERENCES CASO(COD_CASO)GO

ALTER TABLE ARRESTAADD CONSTRAINT PK_ARRESTA_COD_DEL_COD_POL PRIMARY KEY(COD_DEL,COD_POL), CONSTRAINT FK_ARRESTA_COD_DEL FOREIGN KEY(COD_DEL) REFERENCES

DELINCUENTE(COD_DEL), CONSTRAINT FK_ARRESTA_COD_POL FOREIGN KEY(COD_POL) REFERENCES

POLICIA(COD_POL)GO


8


ALTER TABLE INVESTIGAADD CONSTRAINT PK_INVESTIGA_COD_POL_COD_CASO PRIMARY KEY(COD_POL,COD_CASO),

CONSTRAINT FK_INVESTIGA_COD_POL FOREIGN KEY(COD_POL) REFERENCES POLICIA(COD_POL), CONSTRAINT FK_INVESTIGA_COD_CASO FOREIGN KEY(COD_CASO) REFERENCES CASO(COD_CASO)GO

--CREANDO LOS INDICES--SOBRE LAS LLAVES FORANEAS O SECUNDARIAS PORQUE LAS LLAVES--PRIMARIAS ESTAN POR DEFECTO INDEXADAS

CREATE INDEX IND_ARMA_COD_POL ON ARMA(COD_POL)GOCREATE INDEX IND_DELINCUENTE_COD_CAL ON DELINCUENTE(COD_CAL)GOCREATE INDEX IND_INVOLUCRADO_COD_DEL_COD_CASO ON INVOLUCRADO(COD_DEL,COD_CASO)GOCREATE INDEX IND_ARRESTA_COD_DEL_COD_POL ON ARRESTA(COD_DEL,COD_POL)GOCREATE INDEX IND_INVESTIGA_COD_POL_COD_CASO ON INVESTIGA(COD_POL,COD_CASO)GO

--INSERTANDO DATOS A LAS TABLASINSERT INTO POLICIA(COD_POL,PATERNO,MATERNO,NOMBRES,FECHA_NAC,FUNCION)VALUES('PDV-120978','PEREZ','DURAN','VIDAL','12/09/1978','Administrativo')GO

INSERT INTO ARMA(CLASE,NOM_ARMA,NOTA,COD_POL)VALUES('FUEGO','AMETRALLADORA',9,'PDV-120978')GOINSERT INTO ARMA(CLASE,NOM_ARMA,NOTA,COD_POL)VALUES('PUNSOCORTANTE','CUCHILLO',10,'PDV-120978')GO

INSERT INTO CASO(JUSGADO,TIPO,DESCRIPCION)VALUES('Jusgado 1','PILDORITAS','ROBO EN SILENCIO')GOINSERT INTO CASO(JUSGADO,TIPO,DESCRIPCION)VALUES('Jusgado 2','NARCOTRAFICO','ALTAMENTE PELIGROSO')GOINSERT INTO CASO(JUSGADO,TIPO,DESCRIPCION)VALUES('Jusgado 5','AUTEROS','ROBO DE AUTOS')GO

INSERT INTO CALABOZO(UBICACION,OBSERVACIONES)VALUES('POSTA','DELINCUENTES ALTAMENTE PELIGROSOS')GOINSERT INTO CALABOZO(UBICACION,OBSERVACIONES)VALUES('COMUN','DELINCUENTES COMUNES')GO

bdii-2013 parte iii

Documents