Skip to content

Instantly share code, notes, and snippets.

@antoc0d3

antoc0d3/db PI-1-2018

Last active September 10, 2018 06:29
Show Gist options
  • Save antoc0d3/71c0ea606453d117c49214a3ae2a0ae0 to your computer and use it in GitHub Desktop.
Save antoc0d3/71c0ea606453d117c49214a3ae2a0ae0 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
db PI-1-2018
CREATE DATABASE INBOMILIARIA_DATABASE_2018;
USE INBOMILIARIA_DATABASE_2018;
/* GEOLOCALIZACION */
CREATE TABLE DEPARTAMENTOS (
ID INT NOT NULL,
NAME VARCHAR(100) NOT NULL,
);
INSERT INTO DEPARTAMENTOS VALUES('AMAZONAS'),
('ANCASH'),
('APURIMAC'),
('AREQUIPA'),
('AYACUCHO'),
('CAJAMARCA'),
('ICA'),
('LIMA'),
('CALLAO'),
('CUSCO'),
('HUANCAVELICA'),
('HUANUCO'),
('JUNIN'),
('LA LIBERTAD'),
('LAMBAYEQUE'),
('LORETO'),
('MADRE DE DIOS'),
('MOQUEGUA'),
('PASCO'),
('PIURA'),
('PUNO'),
('SAN MARTIN'),
('TACNA'),
('TUMBES'),
('UCAYALI');
/* GEOLOCALIZACION */
CREATE TABLE PROVINCIAS (
ID INT NOT NULL,
ID_DEP INT NOT NULL,
NAME VARCHAR(100) NOT NULL,
);
INSERT INTO PROVINCIAS VALUES
('BAGUA', 1),
('BONGARA',1),
('CHACHAPOYAS',1),
('CONDORCANQUI',1),
('LUYA',1),
('RODRIGUEZ DE MENDOZA',1),
('UTCUBAMBA',1),
('AIJA',2),
('ANTONIO RAYMONDI',2),
('ASUNCION',2),
('BOLOGNESI',2),
('CARHUAZ',2),
('CARLOS FERMIN FITZCARRALD',2),
('CASMA',2),
('CORONGO',2),
('HUARAZ',2),
('HUARI',2),
('HUARMEY',2),
('HUAYLAS',2),
('MARISCAL LUZURIAGA',2),
('OCROS',2),
('PALLASCA',2),
('POMABAMBA',2),
('RECUAY',2),
('SANTA',2),
('SIHUAS',2),
('YUNGAY',2),
('ABANCAY',3),
('ANDAHUAYLAS',3),
('ANTABAMBA',3),
('AYMARAES',3),
('CHINCHEROS',3),
('COTABAMBAS',3),
('GRAU',3),
('AREQUIPA',4),
('CAMANA',4),
('CARAVELI',4),
('CASTILLA',4),
('CAYLLOMA',4),
('CONDESUYOS',4),
('ISLAY',4),
('LA UNION',4),
('CANGALLO',5),
('HUAMANGA',5),
('HUANCA SANCOS',5),
('HUANTA',5),
('LA MAR',5),
('LUCANAS',5),
('PARINACOCHAS', 5),
('PAUCAR DEL SARA SARA',5),
('SUCRE',5),
('VICTOR FAJARDO',5),
('VILCAS HUAMAN',5),
('CAJABAMBA', 6),
('CAJAMARCA',6),
('CELENDIN',6),
('CHOTA',6),
('CONTUMAZA',6),
('CUTERVO',6),
('HUALGAYOC',6),
('JAEN',6),
('SAN IGNACIO',6),
('SAN MARCOS',6),
('SAN MIGUEL',6),
('SAN PABLO',6),
('SANTA CRUZ',6),
('CALLAO',7),
('ACOMAYO',8),
('ANTA',8),
('CALCA',8),
('CANAS',8),
('CANCHIS',8),
('CHUMBIVILCAS',8),
('CUSCO',8),
('ESPINAR',8),
('LA CONVENCION',8),
('PARURO',8),
('PAUCARTAMBO',8),
('QUISPICANCHI',8),
('URUBAMBA',8),
('ACOBAMBA',9),
('ANGARAES',9),
('CASTROVIRREYNA',9),
('CHURCAMPA',9),
('HUANCAVELICA',9),
('HUAYTARA',9),
('TAYACAJA',9),
('AMBO',10),
('DOS DE MAYO',10),
('HUACAYBAMBA',10),
('HUAMALIES',10),
('HUANUCO',10),
('LAURICOCHA',10),
('LEONCIO PRADO',10),
('MARAÑON',10),
('PACHITEA',10),
('PUERTO INCA',10),
('YAROWILCA',10),
('CHINCHA',11),
('ICA',11),
('ICA',11),
('NASCA',11),
('PALPA',11),
('PISCO',11),
('CHANCHAMAYO',12),
('CHUPACA',12),
('CONCEPCION',12),
('HUANCAYO',12),
('JAUJA',12),
('JUNIN',12),
('SATIPO',12),
('TARMA',12),
('YAULI',12),
('ASCOPE',13),
('BOLIVAR',13),
('CHEPEN',13),
('GRAN CHIMU',13),
('JULCAN',13),
('OTUZCO',13),
('PACASMAYO',13),
('PATAZ',13),
('SANCHEZ CARRION',13),
('SANTIAGO DE CHUCO',13),
('TRUJILLO',13),
('VIRU',13),
('CHICLAYO',14),
('FERREÑAFE',14),
('LAMBAYEQUE',14),
('BARRANCA',15),
('CAJATAMBO',15),
('CAÑETE',15),
('CANTA',15),
('HUARAL',15),
('HUAROCHIRI',15),
('HUAURA',15),
('LIMA',15),
('OYON',15),
('YAUYOS',15),
('ALTO AMAZONAS',16),
('DATEM DEL MARAÑON',16),
('LORETO',16),
('MARISCAL RAMON CASTILLA',16),
('MAYNAS',16),
('PUTUMAYO',16),
('REQUENA',16),
('UCAYALI',16),
('MANU',17),
('TAHUAMANU',17),
('TAMBOPATA',17),
('GENERAL SANCHEZ CERRO',18),
('ILO',18),
('MARISCAL NIETO',18),
('DANIEL ALCIDES CARRION',19),
('OXAPAMPA',19),
('PASCO',19),
('AYABACA',20),
('HUANCABAMBA',20),
('MORROPON',20),
('PAITA',20),
('PIURA',20),
('SECHURA',20),
('SULLANA',20),
('TALARA',20),
('AZANGARO',21),
('CARABAYA',21),
('CHUCUITO',21),
('EL COLLAO',21),
('HUANCANE',21),
('LAMPA',21),
('MELGAR',21),
('MOHO',21),
('PUNO',21),
('SAN ANTONIO DE PUTINA',21),
('SAN ROMAN',21),
('SANDIA',21),
('YUNGUYO',21),
('BELLAVISTA',22),
('EL DORADO',22),
('HUALLAGA',22),
('LAMAS',22),
('MARISCAL CACERES',22),
('MOYOBAMBA',22),
('PICOTA',22),
('RIOJA',22),
('SAN MARTIN',22),
('TOCACHE',22),
('CANDARAVE',23),
('JORGE BASADRE',23),
('TACNA',23),
('TARATA',23),
('CONTRALMIRANTE VILLAR',24),
('TUMBES',24),
('ZARUMILLA', 24),
('ATALAYA',25),
('CORONEL PORTILLO',25),
('PADRE ABAD',25),
('PURUS',25);
CREATE TABLE DISTRITOS (
ID_DIST INT NOT NULL,
ID_PROV INT NOT NULL
);
INSERT INTO DISTRITOS VALUES (1, 'ARAMANGO'),
(1, 'BAGUA'),
(1, 'COPALLIN'),
(1, 'EL PARCO'),
(1, 'IMAZA'),
(1, 'LA PECA'),
(2, 'CHISQUILLA'),
(2, 'CHURUJA'),
(2, 'COROSHA'),
(2, 'CUISPES'),
(2, 'FLORIDA'),
(2, 'JAZAN'),
(2, 'JUMBILLA'),
(2, 'RECTA'),
(2, 'SAN CARLOS'),
(2, 'SHIPASBAMBA'),
(2, 'VALERA'),
(2, 'YAMBRASBAMBA'),
(3, 'ASUNCION'),
(3, 'BALSAS'),
(3, 'CHACHAPOYAS'),
(3, 'CHETO'),
(3, 'CHILIQUIN'),
(3, 'CHUQUIBAMBA'),
(3, 'GRANADA'),
(3, 'HUANCAS'),
(3, 'LA JALCA'),
(3, 'LEIMEBAMBA'),
(3, 'LEVANTO'),
(3, 'MAGDALENA'),
(3, 'MARISCAL CASTILLA'),
(3, 'MOLINOPAMPA'),
(3, 'MONTEVIDEO'),
(3, 'OLLEROS'),
(3, 'QUINJALCA'),
(3, 'SAN FRANCISCO DE DAGUAS'),
(3, 'SAN ISIDRO DE MAINO'),
(3, 'SOLOCO'),
(3, 'SONCHE'),
(4, 'EL CENEPA'),
(4, 'NIEVA'),
(4, 'RIO SANTIAGO'),
(5, 'CAMPORREDONDO'),
(5, 'COCABAMBA'),
(5, 'COLCAMAR'),
(5, 'CONILA'),
(5, 'INGUILPATA'),
(5, 'LAMUD'),
(5, 'LONGUITA'),
(5, 'LONYA CHICO'),
(5, 'LUYA'),
(5, 'LUYA VIEJO'),
(5, 'MARIA'),
(5, 'OCALLI'),
(5, 'OCUMAL'),
(5, 'PISUQUIA'),
(5, 'PROVIDENCIA'),
(5, 'SAN CRISTOBAL'),
(5, 'SAN FRANCISCO DE YESO'),
(5, 'SAN JERONIMO'),
(5, 'SAN JUAN DE LOPECANCHA'),
(5, 'SANTA CATALINA'),
(5, 'SANTO TOMAS'),
(5, 'TINGO'),
(5, 'TRITA'),
(6, 'CHIRIMOTO'),
(6, 'COCHAMAL'),
(6, 'HUAMBO'),
(6, 'LIMABAMBA'),
(6, 'LONGAR'),
(6, 'MARISCAL BENAVIDES'),
(6, 'MILPUC'),
(6, 'OMIA'),
(6, 'SAN NICOLAS'),
(6, 'SANTA ROSA'),
(6, 'TOTORA'),
(6, 'VISTA ALEGRE'),
(7, 'BAGUA GRANDE'),
(7, 'CAJARURO'),
(7, 'CUMBA'),
(7, 'EL MILAGRO'),
(7, 'JAMALCA'),
(7, 'LONYA GRANDE'),
(7, 'YAMON'),
(8, 'AIJA'),
(8, 'CORIS'),
(8, 'HUACLLAN'),
(8, 'LA MERCED'),
(8, 'SUCCHA'),
/* GEOLOCALIZACION */
CREATE TABLE ADDRESSES (
ADDRESS_ID INT NOT NULL,
LINE_1 VARCHAR(255) NOT NULL,
LINE_2 VARCHAR(255) NULL,
CITY VARCHAR(255) NOT NULL,
ZIP_CODE VARCHAR(255) NOT NULL,
STATE VARCHAR(255) NOT NULL,
DETAILS VARCHAR(255) NOT NULL
);
/* USUARIOS QUE PUBLICAN PROPIEDADES */
CREATE TABLE ["USERS"] (
USER_ID INT NOT NULL,
CAT_ID INT NOT NULL,
ADDRESS_ID INT NOT NULL,
NAMES VARCHAR(255) NOT NULL,
LAST_NAMES VARCHAR(255) NOT NULL,
GENDER CHAR(1) NOT NULL,
EMAIL VARCHAR(255) NOT NULL,
EMAIL_ALTERNATIVE VARCHAR(255) NULL,
PHONE_NUMBER VARCHAR(11) NOT NULL,
MOBILE_NUMBER VARCHAR(11) NOT NULL,
DNI CHAR(8) NOT NULL,
PASSWORD VARCHAR(255) NOT NULL,
DATE_ON_REGISTER DATE NOT NULL,
);
/* CATEGORIAS DE USUARIOS */
CREATE TABLE REF_USER_CATEGORIES (
CAT_ID INT NOT NULL,
DESCRIPTION VARCHAR(250) NOT NULL
)
/* PROPIEDADES EN VENTA */
CREATE TABLE PROPERTIES_CATEGORIES (
CAT_ID INT NOT NULL,
DESCRIPTION VARCHAR(255) NOT NULL
)
INSERT INTO PROPERTIES_CATEGORIES VALUES ('Departamentos'),
('Casas'),
('Habitacion'),
('Terrenos'),
('Hoteles'),
('Locales Comerciales'),
('Locales Industriales'),
('Oficinas'),
('Cocheras'),
('Otros');
/* ESTADO DE PROPIEDADES */
CREATE TABLE REF_PROPERTY_STATUS (
STATUS_ID INT NOT NULL,
DESCRIPTION VARCHAR(255) NOT NULL
)
INSERT INTO REF_PROPERTY_STATUS VALUES ('ACTIVO'),
('DESACTIVADO');
/* TIPO DE PROPIEDADES */
CREATE TABLE REF_PROPERTIES_TYPES (
TYPE_ID INT NOT NULL,
DESCRIPTION VARCHAR(255) NOT NULL
)
CREATE TABLE REF_PROPERTIES_SUB_TYPES (
SUB_TYPE_ID INT NOT NULL,
TYPE_ID INT NOT NULL,
DESCRIPTION VARCHAR(255) NOT NULL
)
INSERT INTO REF_PROPERTIES_TYPES VALUES (1,'Departamento'),
(1,'Departamento Dúplex'),
(1,'Departamento Tríplex'),
(1,'Departamento de Playa'),
(1,'Departamento Loft'),
(1,'Departamento PentHouse'),
(1,'Minidepartamento');
INSERT INTO REF_PROPERTIES_TYPES VALUES (2,'Casa'),
(2,'Casa de Playa'),
(2,'Casa de Playa en condominio'),
(2,'Casa de campo'),
(2,'Casa en condominio'),
(2,'Casa en quinta');
INSERT INTO REF_PROPERTIES_TYPES VALUES (4,'Terreno'),
(4,'Terreno de playa'),
(4,'Terreno agrícola'),
(4,'Terreno industrial'),
(4,'Terreno comercial'),
(4,'Terreno eriazo'),
(4,'Terreno residencial');
INSERT INTO REF_PROPERTIES_TYPES VALUES (5,'Hotel de Playa'),
(5,'Hotel de Pasajeros'),
(5,'Hotel Familiar'),
(5,'Hotel de Alojamiento'),
(5,'Hotel Todo Incluido'),
(5,'Posada'),
(5,'Hostel');
/* PROPIEDADES EN VENTA */
CREATE TABLE PROPERTIES (
PROPERTY_ID INT NOT NULL,
USER_ID INT NOT NULL,
TYPE_ID INT NOT NULL,
ADDRESS_ID INT NOT NULL,
STATUS_ID INT NOT NULL,
NAME VARCHAR(255) NOT NULL,
DESCRIPTION VARCHAR(255) NOT NULL,
DATE_ON_MARKET DATE NOT NULL,
DATE_OFF_MARKET DATE NOT NULL,
)
/* BUSQUEDAS DE USUARIO */
CREATE TABLE USERS_SEARCHES (
SEARCH_ID INT NOT NULL,
USER_ID INT NOT NULL,
DESCRIPTION VARCHAR(255) NOT NULL
)
/* TIPO DE ARCHIVOS MULTIMEDIA ACEPTADOS EN PROPIEDADES */
CREATE TABLE REF_PROPERTIES_MEDIA_TYPES (
TYPE_MEDIA VARCHAR(255) NOT NULL,
DESCRIPTION VARCHAR(255) NOT NULL
)
/* GALERIA MULTIMEDIA DE PROPIEDADES */
CREATE TABLE PROPERTIES_MEDIA_ITEMS (
MEDIA_ID INT NOT NULL,
PROPERTY_ID INT NOT NULL,
TYPE_MEDIA VARCHAR(255) NOT NULL,
RESOURCE VARCHAR(255) NOT NULL
)
/* QUE TIENE CADA PROPIEDADES (SERVICIOS) */
CREATE TABLE PROPERTIES_FEATURES (
FEATURED_ID INT NOT NULL,
FEATURED_NAME VARCHAR(255) NOT NULL,
FEATURED_DESCRIPTION VARCHAR(255) NOT NULL
)
/* RELACION ENTRE PROPIEDADES Y FEATURES */
CREATE TABLE HAS_PROPERTIES_FEATURES (
PROPERTY_ID INT NOT NULL,
FEATURED_ID INT NOT NULL
)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment