Karim A. Angueira Del Valle
598-12-4650
Cois 5110 – Sec. Miércoles 6:00PM – 9:00PM
Profesor Carlos M. Figueroa
Descripción
El sistema hace referencia a la problemática que existe en los
cierre de control de acceso de varias urbanizaciones del área metro.Para
controlar el acceso se tienen unas listas de direcciones residentes y números
de teléfono.Cada vez que un visitante desea entrar a estas urbanizaciones
el guardia de seguridad tiene que buscar en las listas el nombre de la
familia, dirección y numero para llamar y solicitar el acceso del
visitante.En momentos de tráfico se congestionan las entradas a
estas urbanizaciones debido al tiempo que consume buscar la residencia
en el listado, por lo que muchas veces se opta por registrar el nombre
del visitante y luego se le permite el acceso sin anunciar su entrada.
Cuando se opera con estas deficiencias la efectividad del control de acceso de ve afectado.Estas deficiencias en el proceso manual de verificación de acceso dan lugar a que con frecuencia a que visitantes den información incorrecta la cual no puede ser verificada.También surgen quejas de los residentes por los inconvenientes que ocasionan las visitas no anunciadas o autorizadas al igual que la congestión para entrar.
Propuesta
Se propone montar una base de datos donde se encuentren las listas
de tal manera que el guardia de control de acceso pueda acceder la información
con más rapidez y precisión agilizando así la entrada
de vehículos por el control de acceso. Esta aplicación deberá
tener como mínimo la funcionalidad de: (1) añadir, modificar
y borrar direcciones; (2) producir reportes de las visitas y residentes
de la urbanización.
Anejo A: Listener.ora
# LISTENER.ORA Network Configuration File: c:\Oracle\ORAPO\network\admin\listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = educom)(PORT = 1521))
)
)
(DESCRIPTION =
(PROTOCOL_STACK =
(PRESENTATION = GIOP)
(SESSION = RAW)
)
(ADDRESS = (PROTOCOL = TCP)(HOST = educom)(PORT = 2481))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = c:\Oracle\ORAPO)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = ORCL.LOCALHOST)
(ORACLE_HOME = c:\Oracle\ORAPO)
(SID_NAME = ORCL)
)
)
Anejo B: TNSNames.ora
# TNSNAMES.ORA Network Configuration File: c:\Oracle\ORAPO\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
ORCL.LOCALHOST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = educom)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL.LOCALHOST)
)
)
Anejo C: Diagrama Entidad – Relación (E-R)
Anejo D: Creación de Tablas
Tabla: ADDRESS
CREATE TABLE ADDRESS(
street VARCHAR2(25) NOT NULL,
block VARCHAR2(5) NOT NULL,
num VARCHAR2(5) NOT NULL,
restriction VARCHAR2(50) DEFAULT 'No Restriction',
comments VARCHAR2(50));
COMMENT ON TABLE ADDRESS IS 'Table of locations or addresses';
COMMENT ON COLUMN ADDRESS.street IS 'Street Name';
COMMENT ON COLUMN ADDRESS.block IS 'Block Number';
COMMENT ON COLUMN ADDRESS.num IS 'Alfanumeric block number or description';
ALTER TABLE ADDRESS
ADD CONSTRAINT PK_ADDRESS1
PRIMARY KEY (street,block,num);
Tabla: RES_TYPE
CREATE TABLE RES_TYPE(
res_type_id INTEGER NOT NULL,
res_type_desc VARCHAR2(50) NOT NULL);
COMMENT ON TABLE RES_TYPE IS 'Resident Type entity';
COMMENT ON COLUMN RES_TYPE.res_type_id IS 'Resident type id';
COMMENT ON COLUMN RES_TYPE.res_type_desc IS 'Resident type description';
ALTER TABLE RES_TYPE
ADD CONSTRAINT PK_RES_TYPE
PRIMARY KEY (res_type_id);
Tabla: RESIDENT
CREATE TABLE RESIDENT(
firstname VARCHAR2(20) NOT NULL,
lastname VARCHAR2(20) NOT NULL,
FK_res_type_id INTEGER NOT NULL,
FK_num VARCHAR2(5) NOT NULL,
FK_block VARCHAR2(5) NOT NULL,
FK_street VARCHAR2(25) NOT NULL);
COMMENT ON TABLE RESIDENT IS 'Household members, includingowners and any other family member living at home.';
COMMENT ON COLUMN RESIDENT.lastname IS 'Resident last name';
ALTER TABLE RESIDENT
ADD CONSTRAINT FK_ADDRESS_RESIDENT_1
FOREIGN KEY (FK_num,FK_block,FK_street) REFERENCES ADDRESS (num,block,street);
ALTER TABLE RESIDENT
ADD CONSTRAINT FK_RES_TYPE_RESIDENT_2
FOREIGN KEY (FK_res_type_id) REFERENCES RES_TYPE (res_type_id);
ALTER TABLE RESIDENT
ADD CONSTRAINT PK_RESIDENT1
PRIMARY KEY (firstname,lastname);
Tabla: VISITOR
CREATE TABLE VISITOR(
vis_lastname VARCHAR2(20) NOT NULL,
vis_firstname VARCHAR2(20) NOT NULL);
COMMENT ON TABLE VISITOR IS 'People pre-authorized to visit related address without any previous notification.';
COMMENT ON COLUMN VISITOR.vis_lastname IS 'Visitor last name';
COMMENT ON COLUMN VISITOR.vis_firstname IS 'Visitor firstname';
ALTER TABLE VISITOR
ADD CONSTRAINT PK_VISITOR1
PRIMARY KEY (vis_lastname,vis_firstname);
Tabla: VISTYPE
CREATE TABLE VISTYPE(
vis_type_id INTEGER NOT NULL,
vis_type_desc VARCHAR2(25) NOT NULL);
COMMENT ON TABLE VISTYPE IS 'Visitor type entity';
COMMENT ON COLUMN VISTYPE.vis_type_id IS 'Visitor id';
COMMENT ON COLUMN VISTYPE.vis_type_desc IS 'Visitor type description';
ALTER TABLE VISTYPE
ADD CONSTRAINT PK_VISTYPE1
PRIMARY KEY (vis_type_id);
ALTER TABLE VISTYPE
ADD CONSTRAINT CK_VISTYPE1
CHECK (vis_type_id>0);
Tabla: ADDRESS_VISITOR
CREATE TABLE ADDRESS_VISITOR(
FK_vis_lastname VARCHAR2(20) NOT NULL,
FK_vis_firstname VARCHAR2(20) NOT NULL,
FK_street VARCHAR2(25),
FK_block VARCHAR2(5),
FK_number VARCHAR2(5));
ALTER TABLE ADDRESS_VISITOR
ADD CONSTRAINT FK_ADDRESS_VISITOR_1
FOREIGN KEY (FK_street,FK_block,FK_number) REFERENCES ADDRESS (street,block,num);
ALTER TABLE ADDRESS_VISITOR
ADD CONSTRAINT FK_ADDRESS_VISITOR_2
FOREIGN KEY (FK_vis_lastname,FK_vis_firstname) REFERENCES VISITOR (vis_lastname,vis_firstname);
ALTER TABLE ADDRESS_VISITOR
ADD CONSTRAINT PK_ADDRESS_VISITOR1
PRIMARY KEY (FK_vis_lastname,FK_vis_firstname,FK_street,FK_block,FK_number);
Tabla: PHONE_TYPE
CREATE TABLE PHONE_TYPE(
phone_type_id INTEGER NOT NULL,
phone_type_desc VARCHAR2(30) NOT NULL);
COMMENT ON TABLE PHONE_TYPE IS 'Table of telephone number types';
COMMENT ON COLUMN PHONE_TYPE.phone_type_id IS 'Phone type id';
COMMENT ON COLUMN PHONE_TYPE.phone_type_desc IS 'Phone type descriptiion';
ALTER TABLE PHONE_TYPE
ADD CONSTRAINT PK_PHONE_TYPE1
PRIMARY KEY (phone_type_id);
ALTER TABLE PHONE_TYPE
ADD CONSTRAINT CK_PHONE_TYPE1
CHECK (phone_type_id>0);
Tabla: PHONE
CREATE TABLE PHONE(
phone CHAR(10) NOT NULL,
FK_phone_type_id INTEGER NOT NULL,
FK_num VARCHAR2(5) NOT NULL,
FK_block VARCHAR2(5) NOT NULL,
FK_street VARCHAR2(25) NOT NULL);
COMMENT ON TABLE PHONE IS 'Phone numbers available for each address.';
COMMENT ON COLUMN PHONE.phone IS 'Telephone number';
ALTER TABLE PHONE
ADD CONSTRAINT FK_ADDRESS_PHONE_1
FOREIGN KEY (FK_num,FK_block,FK_street) REFERENCES ADDRESS (num,block,street);
ALTER TABLE PHONE
ADD CONSTRAINT FK_PHONE_PHONE_TYPE_2
FOREIGN KEY (FK_phone_type_id) REFERENCES PHONE_TYPE (phone_type_id);
ALTER TABLE PHONE
ADD CONSTRAINT PK_PHONE1
PRIMARY KEY (phone);
Tabla: VISITOR_VISTYPE
CREATE TABLE VISITOR_VISTYPE(
FK_vis_type_id INTEGER,
FK_vis_lastname VARCHAR2(20) NOT NULL,
FK_vis_firstname VARCHAR2(20) NOT NULL);
ALTER TABLE VISITOR_VISTYPE
ADD CONSTRAINT FK_VISITOR_VISTYPE_1
FOREIGN KEY (FK_vis_lastname,FK_vis_firstname) REFERENCES VISITOR (vis_lastname,vis_firstname);
ALTER TABLE VISITOR_VISTYPE
ADD CONSTRAINT FK_VISITOR_VISTYPE_2
FOREIGN KEY (FK_vis_type_id) REFERENCES VISTYPE (vis_type_id);
ALTER TABLE VISITOR_VISTYPE
ADD CONSTRAINT PK_VISITOR_VISTYPE1
PRIMARY KEY (FK_vis_type_id,FK_vis_lastname,FK_vis_firstname);