Este proyecto es una aplicación para trabajar con un base de datos Postgresql por el ramo de base de datos. https://cromer.cl/sernatur
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

1606 lines
39 KiB

--
-- PostgreSQL database dump
--
-- Dumped from database version 11.1 (Ubuntu 11.1-3.pgdg18.04+1)
-- Dumped by pg_dump version 11.1 (Ubuntu 11.1-3.pgdg18.04+1)
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: arrienda; Type: TABLE; Schema: public; Owner: cromer
--
CREATE TABLE public.arrienda (
patente character varying(6) NOT NULL,
rut_empresa character varying(9) NOT NULL,
precio integer NOT NULL,
fecha_devolucion date NOT NULL,
CONSTRAINT arrienda_precio_check CHECK ((precio > 0))
);
ALTER TABLE public.arrienda OWNER TO cromer;
--
-- Name: asociado; Type: TABLE; Schema: public; Owner: cromer
--
CREATE TABLE public.asociado (
id_tour integer NOT NULL,
id_lugar smallint NOT NULL,
fecha_llegada date,
hora_llegada time without time zone,
fecha_salida date,
hora_salida time without time zone
);
ALTER TABLE public.asociado OWNER TO cromer;
--
-- Name: categoria; Type: TABLE; Schema: public; Owner: cromer
--
CREATE TABLE public.categoria (
id_categoria smallint NOT NULL,
descripcion_categoria text NOT NULL
);
ALTER TABLE public.categoria OWNER TO cromer;
--
-- Name: categoria_id_categoria_seq; Type: SEQUENCE; Schema: public; Owner: cromer
--
CREATE SEQUENCE public.categoria_id_categoria_seq
AS smallint
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE public.categoria_id_categoria_seq OWNER TO cromer;
--
-- Name: categoria_id_categoria_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: cromer
--
ALTER SEQUENCE public.categoria_id_categoria_seq OWNED BY public.categoria.id_categoria;
--
-- Name: ciudad; Type: TABLE; Schema: public; Owner: cromer
--
CREATE TABLE public.ciudad (
id_ciudad smallint NOT NULL,
nombre_ciudad character varying(20) NOT NULL,
id_region smallint
);
ALTER TABLE public.ciudad OWNER TO cromer;
--
-- Name: ciudad_id_ciudad_seq; Type: SEQUENCE; Schema: public; Owner: cromer
--
CREATE SEQUENCE public.ciudad_id_ciudad_seq
AS smallint
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE public.ciudad_id_ciudad_seq OWNER TO cromer;
--
-- Name: ciudad_id_ciudad_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: cromer
--
ALTER SEQUENCE public.ciudad_id_ciudad_seq OWNED BY public.ciudad.id_ciudad;
--
-- Name: contacto_emergencia; Type: TABLE; Schema: public; Owner: cromer
--
CREATE TABLE public.contacto_emergencia (
id_contacto integer NOT NULL,
telefono_emergencia bigint NOT NULL,
nombre_emergencia character varying(50) NOT NULL,
CONSTRAINT contacto_emergencia_telefono_emergencia_check CHECK (((telefono_emergencia > 99999999) AND (telefono_emergencia < '100000000000'::bigint)))
);
ALTER TABLE public.contacto_emergencia OWNER TO cromer;
--
-- Name: contacto_emergencia_id_contacto_seq; Type: SEQUENCE; Schema: public; Owner: cromer
--
CREATE SEQUENCE public.contacto_emergencia_id_contacto_seq
AS integer
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE public.contacto_emergencia_id_contacto_seq OWNER TO cromer;
--
-- Name: contacto_emergencia_id_contacto_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: cromer
--
ALTER SEQUENCE public.contacto_emergencia_id_contacto_seq OWNED BY public.contacto_emergencia.id_contacto;
--
-- Name: descuento; Type: TABLE; Schema: public; Owner: cromer
--
CREATE TABLE public.descuento (
id_descuento smallint NOT NULL,
descripcion_descuento text NOT NULL,
porcentaje numeric(3,2) NOT NULL,
CONSTRAINT descuento_porcentaje_check CHECK (((porcentaje >= (0)::numeric) AND (porcentaje <= (1)::numeric)))
);
ALTER TABLE public.descuento OWNER TO cromer;
--
-- Name: descuento_id_descuento_seq; Type: SEQUENCE; Schema: public; Owner: cromer
--
CREATE SEQUENCE public.descuento_id_descuento_seq
AS smallint
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE public.descuento_id_descuento_seq OWNER TO cromer;
--
-- Name: descuento_id_descuento_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: cromer
--
ALTER SEQUENCE public.descuento_id_descuento_seq OWNED BY public.descuento.id_descuento;
--
-- Name: empresa; Type: TABLE; Schema: public; Owner: cromer
--
CREATE TABLE public.empresa (
rut_empresa character varying(9) NOT NULL,
nombre_empresa character varying(50) NOT NULL,
contacto character varying(50) NOT NULL,
telefono bigint NOT NULL,
CONSTRAINT empresa_telefono_check CHECK (((telefono > 99999999) AND (telefono < '100000000000'::bigint)))
);
ALTER TABLE public.empresa OWNER TO cromer;
--
-- Name: enfermedad; Type: TABLE; Schema: public; Owner: cromer
--
CREATE TABLE public.enfermedad (
id_enfermedad smallint NOT NULL,
descripcion_enfermedad text NOT NULL
);
ALTER TABLE public.enfermedad OWNER TO cromer;
--
-- Name: enfermedad_id_enfermedad_seq; Type: SEQUENCE; Schema: public; Owner: cromer
--
CREATE SEQUENCE public.enfermedad_id_enfermedad_seq
AS smallint
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE public.enfermedad_id_enfermedad_seq OWNER TO cromer;
--
-- Name: enfermedad_id_enfermedad_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: cromer
--
ALTER SEQUENCE public.enfermedad_id_enfermedad_seq OWNED BY public.enfermedad.id_enfermedad;
--
-- Name: especialidad; Type: TABLE; Schema: public; Owner: cromer
--
CREATE TABLE public.especialidad (
id_especialidad smallint NOT NULL,
descripcion_especialidad text NOT NULL
);
ALTER TABLE public.especialidad OWNER TO cromer;
--
-- Name: especialidad_id_especialidad_seq; Type: SEQUENCE; Schema: public; Owner: cromer
--
CREATE SEQUENCE public.especialidad_id_especialidad_seq
AS smallint
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE public.especialidad_id_especialidad_seq OWNER TO cromer;
--
-- Name: especialidad_id_especialidad_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: cromer
--
ALTER SEQUENCE public.especialidad_id_especialidad_seq OWNED BY public.especialidad.id_especialidad;
--
-- Name: guia; Type: TABLE; Schema: public; Owner: cromer
--
CREATE TABLE public.guia (
rut_guia character varying(9) NOT NULL,
nombre_guia character varying(50) NOT NULL,
calle character varying(20) NOT NULL,
numero smallint NOT NULL,
id_ciudad smallint
);
ALTER TABLE public.guia OWNER TO cromer;
--
-- Name: lugar; Type: TABLE; Schema: public; Owner: cromer
--
CREATE TABLE public.lugar (
id_lugar smallint NOT NULL,
nombre_lugar character varying(50) NOT NULL,
valor_entrada integer NOT NULL,
nivel smallint NOT NULL,
id_ciudad smallint,
CONSTRAINT lugar_nivel_check CHECK ((nivel >= 0)),
CONSTRAINT lugar_valor_entrada_check CHECK ((valor_entrada >= 0))
);
ALTER TABLE public.lugar OWNER TO cromer;
--
-- Name: lugar_id_lugar_seq; Type: SEQUENCE; Schema: public; Owner: cromer
--
CREATE SEQUENCE public.lugar_id_lugar_seq
AS smallint
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE public.lugar_id_lugar_seq OWNER TO cromer;
--
-- Name: lugar_id_lugar_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: cromer
--
ALTER SEQUENCE public.lugar_id_lugar_seq OWNED BY public.lugar.id_lugar;
--
-- Name: participa; Type: TABLE; Schema: public; Owner: cromer
--
CREATE TABLE public.participa (
id_tour integer NOT NULL,
rut_guia character varying(9) NOT NULL,
id_categoria smallint NOT NULL
);
ALTER TABLE public.participa OWNER TO cromer;
--
-- Name: posee; Type: TABLE; Schema: public; Owner: cromer
--
CREATE TABLE public.posee (
rut_guia character varying(9) NOT NULL,
id_especialidad smallint NOT NULL,
nivel_especialidad smallint NOT NULL,
CONSTRAINT posee_nivel_especialidad_check CHECK ((nivel_especialidad >= 0))
);
ALTER TABLE public.posee OWNER TO cromer;
--
-- Name: realiza; Type: TABLE; Schema: public; Owner: cromer
--
CREATE TABLE public.realiza (
id_tour integer NOT NULL,
rut_turista character varying(9) NOT NULL,
id_descuento smallint
);
ALTER TABLE public.realiza OWNER TO cromer;
--
-- Name: region; Type: TABLE; Schema: public; Owner: cromer
--
CREATE TABLE public.region (
id_region smallint NOT NULL,
nombre_region character varying(20) NOT NULL
);
ALTER TABLE public.region OWNER TO cromer;
--
-- Name: region_id_region_seq; Type: SEQUENCE; Schema: public; Owner: cromer
--
CREATE SEQUENCE public.region_id_region_seq
AS smallint
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE public.region_id_region_seq OWNER TO cromer;
--
-- Name: region_id_region_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: cromer
--
ALTER SEQUENCE public.region_id_region_seq OWNED BY public.region.id_region;
--
-- Name: tour; Type: TABLE; Schema: public; Owner: cromer
--
CREATE TABLE public.tour (
id_tour integer NOT NULL,
nombre_tour character varying(50) NOT NULL,
costo_indiv integer NOT NULL,
costo_grupal integer NOT NULL,
minima_personas integer NOT NULL,
id_ciudad smallint,
CONSTRAINT tour_check CHECK ((costo_indiv > costo_grupal)),
CONSTRAINT tour_costo_grupal_check CHECK ((costo_grupal > 0)),
CONSTRAINT tour_minima_personas_check CHECK ((minima_personas > 0))
);
ALTER TABLE public.tour OWNER TO cromer;
--
-- Name: regiones_sindescuento; Type: VIEW; Schema: public; Owner: cromer
--
CREATE VIEW public.regiones_sindescuento AS
SELECT r.nombre_region AS nombreregion,
count(r2.rut_turista) AS cantidad
FROM (((public.region r
JOIN public.ciudad c ON ((c.id_region = r.id_region)))
JOIN public.tour t ON ((t.id_ciudad = c.id_ciudad)))
JOIN public.realiza r2 ON ((r2.id_tour = t.id_tour)))
WHERE ((EXISTS ( SELECT asociado.fecha_llegada
FROM public.asociado
WHERE ((asociado.id_tour = t.id_tour) AND ((asociado.fecha_llegada >= '2018-11-01'::date) AND (asociado.fecha_llegada <= '2018-11-30'::date))))) AND (r2.id_descuento = 2))
GROUP BY r.nombre_region;
ALTER TABLE public.regiones_sindescuento OWNER TO cromer;
--
-- Name: requerir_auto; Type: TABLE; Schema: public; Owner: cromer
--
CREATE TABLE public.requerir_auto (
id_tour integer NOT NULL,
patente character varying(6) NOT NULL,
chofer character varying(50) NOT NULL
);
ALTER TABLE public.requerir_auto OWNER TO cromer;
--
-- Name: tiene_enfermedad; Type: TABLE; Schema: public; Owner: cromer
--
CREATE TABLE public.tiene_enfermedad (
rut_turista character varying(9) NOT NULL,
id_enfermedad smallint NOT NULL
);
ALTER TABLE public.tiene_enfermedad OWNER TO cromer;
--
-- Name: total_arriendos; Type: VIEW; Schema: public; Owner: cromer
--
CREATE VIEW public.total_arriendos AS
SELECT count(a.patente) AS totalarriendo
FROM (((public.arrienda a
JOIN public.requerir_auto r ON (((r.patente)::text = (a.patente)::text)))
JOIN public.tour t ON ((t.id_tour = r.id_tour)))
JOIN public.asociado a2 ON ((t.id_tour = a2.id_tour)))
WHERE ((a2.fecha_llegada >= '2018-01-01'::date) AND (a2.fecha_llegada <= '2018-02-28'::date));
ALTER TABLE public.total_arriendos OWNER TO cromer;
--
-- Name: total_coordinadores; Type: VIEW; Schema: public; Owner: cromer
--
CREATE VIEW public.total_coordinadores AS
SELECT
NULL::integer AS idt,
NULL::character varying(50) AS nombret,
NULL::bigint AS totalcoordinadores;
ALTER TABLE public.total_coordinadores OWNER TO cromer;
--
-- Name: total_turistas; Type: VIEW; Schema: public; Owner: cromer
--
CREATE VIEW public.total_turistas AS
SELECT
NULL::integer AS idt,
NULL::character varying(50) AS nombret,
NULL::bigint AS totalturistas;
ALTER TABLE public.total_turistas OWNER TO cromer;
--
-- Name: vehiculo; Type: TABLE; Schema: public; Owner: cromer
--
CREATE TABLE public.vehiculo (
patente character varying(6) NOT NULL,
ano_vehiculo smallint NOT NULL,
marca character varying(20) NOT NULL,
capacidad smallint NOT NULL
);
ALTER TABLE public.vehiculo OWNER TO cromer;
--
-- Name: total_vehiculos; Type: VIEW; Schema: public; Owner: cromer
--
CREATE VIEW public.total_vehiculos AS
SELECT count(v.patente) AS totalveh
FROM (((public.vehiculo v
JOIN public.requerir_auto r ON (((r.patente)::text = (v.patente)::text)))
JOIN public.tour t ON ((t.id_tour = r.id_tour)))
JOIN public.asociado a ON ((t.id_tour = a.id_tour)))
WHERE ((a.fecha_llegada >= '2018-01-01'::date) AND (a.fecha_llegada <= '2018-02-28'::date));
ALTER TABLE public.total_vehiculos OWNER TO cromer;
--
-- Name: tour_descuentos; Type: VIEW; Schema: public; Owner: cromer
--
CREATE VIEW public.tour_descuentos AS
SELECT
NULL::integer AS idt,
NULL::character varying(50) AS nombret,
NULL::numeric AS totaldescuentos;
ALTER TABLE public.tour_descuentos OWNER TO cromer;
--
-- Name: tour_id_tour_seq; Type: SEQUENCE; Schema: public; Owner: cromer
--
CREATE SEQUENCE public.tour_id_tour_seq
AS integer
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE public.tour_id_tour_seq OWNER TO cromer;
--
-- Name: tour_id_tour_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: cromer
--
ALTER SEQUENCE public.tour_id_tour_seq OWNED BY public.tour.id_tour;
--
-- Name: turista; Type: TABLE; Schema: public; Owner: cromer
--
CREATE TABLE public.turista (
rut_turista character varying(9) NOT NULL,
nombre_turista character varying(50) NOT NULL,
fecha_nacimento date NOT NULL,
id_contacto integer
);
ALTER TABLE public.turista OWNER TO cromer;
--
-- Name: valores_tours; Type: VIEW; Schema: public; Owner: cromer
--
CREATE VIEW public.valores_tours AS
SELECT
NULL::integer AS idt,
NULL::character varying(50) AS nombret,
NULL::bigint AS totalventas;
ALTER TABLE public.valores_tours OWNER TO cromer;
--
-- Name: categoria id_categoria; Type: DEFAULT; Schema: public; Owner: cromer
--
ALTER TABLE ONLY public.categoria ALTER COLUMN id_categoria SET DEFAULT nextval('public.categoria_id_categoria_seq'::regclass);
--
-- Name: ciudad id_ciudad; Type: DEFAULT; Schema: public; Owner: cromer
--
ALTER TABLE ONLY public.ciudad ALTER COLUMN id_ciudad SET DEFAULT nextval('public.ciudad_id_ciudad_seq'::regclass);
--
-- Name: contacto_emergencia id_contacto; Type: DEFAULT; Schema: public; Owner: cromer
--
ALTER TABLE ONLY public.contacto_emergencia ALTER COLUMN id_contacto SET DEFAULT nextval('public.contacto_emergencia_id_contacto_seq'::regclass);
--
-- Name: descuento id_descuento; Type: DEFAULT; Schema: public; Owner: cromer
--
ALTER TABLE ONLY public.descuento ALTER COLUMN id_descuento SET DEFAULT nextval('public.descuento_id_descuento_seq'::regclass);
--
-- Name: enfermedad id_enfermedad; Type: DEFAULT; Schema: public; Owner: cromer
--
ALTER TABLE ONLY public.enfermedad ALTER COLUMN id_enfermedad SET DEFAULT nextval('public.enfermedad_id_enfermedad_seq'::regclass);
--
-- Name: especialidad id_especialidad; Type: DEFAULT; Schema: public; Owner: cromer
--
ALTER TABLE ONLY public.especialidad ALTER COLUMN id_especialidad SET DEFAULT nextval('public.especialidad_id_especialidad_seq'::regclass);
--
-- Name: lugar id_lugar; Type: DEFAULT; Schema: public; Owner: cromer
--
ALTER TABLE ONLY public.lugar ALTER COLUMN id_lugar SET DEFAULT nextval('public.lugar_id_lugar_seq'::regclass);
--
-- Name: region id_region; Type: DEFAULT; Schema: public; Owner: cromer
--
ALTER TABLE ONLY public.region ALTER COLUMN id_region SET DEFAULT nextval('public.region_id_region_seq'::regclass);
--
-- Name: tour id_tour; Type: DEFAULT; Schema: public; Owner: cromer
--
ALTER TABLE ONLY public.tour ALTER COLUMN id_tour SET DEFAULT nextval('public.tour_id_tour_seq'::regclass);
--
-- Data for Name: arrienda; Type: TABLE DATA; Schema: public; Owner: cromer
--
COPY public.arrienda (patente, rut_empresa, precio, fecha_devolucion) FROM stdin;
LJ58BV 56404537k 100000 2018-11-29
AS45FE 566034578 120000 2018-11-29
49FD38 566003458 150000 2018-11-29
\.
--
-- Data for Name: asociado; Type: TABLE DATA; Schema: public; Owner: cromer
--
COPY public.asociado (id_tour, id_lugar, fecha_llegada, hora_llegada, fecha_salida, hora_salida) FROM stdin;
1 1 2018-01-10 04:15:00 2018-01-10 05:15:00
2 2 2018-02-22 09:35:00 2018-02-22 10:35:00
3 3 2018-03-30 16:40:00 2018-03-30 19:10:00
3 4 2018-10-15 12:40:00 2018-10-15 13:10:00
1 1 2018-10-15 12:40:00 2018-10-15 13:10:00
3 4 2018-11-21 09:22:44 2018-11-21 10:30:22
1 1 2018-11-19 12:45:33 2018-11-19 15:33:11
38 7 2019-01-01 12:30:00 2019-01-03 14:40:00
\.
--
-- Data for Name: categoria; Type: TABLE DATA; Schema: public; Owner: cromer
--
COPY public.categoria (id_categoria, descripcion_categoria) FROM stdin;
1 Coordinador
2 Speaker
3 Expert
\.
--
-- Data for Name: ciudad; Type: TABLE DATA; Schema: public; Owner: cromer
--
COPY public.ciudad (id_ciudad, nombre_ciudad, id_region) FROM stdin;
1 Pensacola 1
2 Montgomery 2
3 Pheonix 3
4 Destin 1
5 Los Angeles 4
10 Cairo 9
\.
--
-- Data for Name: contacto_emergencia; Type: TABLE DATA; Schema: public; Owner: cromer
--
COPY public.contacto_emergencia (id_contacto, telefono_emergencia, nombre_emergencia) FROM stdin;
1 56912345678 Chris Cromer
2 56913334578 John Cromer
3 56913984347 Tammy Cromer
\.
--
-- Data for Name: descuento; Type: TABLE DATA; Schema: public; Owner: cromer
--
COPY public.descuento (id_descuento, descripcion_descuento, porcentaje) FROM stdin;
1 0-15 años 0.20
2 16-50 años 0.00
3 51 0.10
4 66 en adelante 0.30
\.
--
-- Data for Name: empresa; Type: TABLE DATA; Schema: public; Owner: cromer
--
COPY public.empresa (rut_empresa, nombre_empresa, contacto, telefono) FROM stdin;
566034578 Hurtz John Jacob 56948904446
56404537k Rent-a-Car Jim Smith 56948904446
566003458 Car Rentals Dot Com Tony Stark 56948904446
\.
--
-- Data for Name: enfermedad; Type: TABLE DATA; Schema: public; Owner: cromer
--
COPY public.enfermedad (id_enfermedad, descripcion_enfermedad) FROM stdin;
1 Heart Disease
2 Diabetes
3 Lung Cancer
\.
--
-- Data for Name: especialidad; Type: TABLE DATA; Schema: public; Owner: cromer
--
COPY public.especialidad (id_especialidad, descripcion_especialidad) FROM stdin;
1 Climbing
2 History
3 Astronomy
4 Public Speaking
\.
--
-- Data for Name: guia; Type: TABLE DATA; Schema: public; Owner: cromer
--
COPY public.guia (rut_guia, nombre_guia, calle, numero, id_ciudad) FROM stdin;
266304579 John Smith Edison Dr. 214 1
266404573 Jack Offer Jackson Ave. 1023 2
266444575 Mike Toga Madison Way 342 3
\.
--
-- Data for Name: lugar; Type: TABLE DATA; Schema: public; Owner: cromer
--
COPY public.lugar (id_lugar, nombre_lugar, valor_entrada, nivel, id_ciudad) FROM stdin;
1 El Bio Bio 0 3 1
2 Lago San Pedro 10000 3 2
3 Atacama 30000 0 3
4 Kilamanjaro 30000 5 3
5 Pensacola Beach 10000 0 1
7 Pyramids 20000 1 10
\.
--
-- Data for Name: participa; Type: TABLE DATA; Schema: public; Owner: cromer
--
COPY public.participa (id_tour, rut_guia, id_categoria) FROM stdin;
2 266304579 1
1 266404573 1
2 266404573 1
3 266444575 1
\.
--
-- Data for Name: posee; Type: TABLE DATA; Schema: public; Owner: cromer
--
COPY public.posee (rut_guia, id_especialidad, nivel_especialidad) FROM stdin;
266304579 1 0
266304579 2 5
266304579 3 5
266304579 4 5
266404573 2 3
266404573 1 5
266444575 3 5
\.
--
-- Data for Name: realiza; Type: TABLE DATA; Schema: public; Owner: cromer
--
COPY public.realiza (id_tour, rut_turista, id_descuento) FROM stdin;
3 173983808 2
4 173983808 1
3 194333579 2
2 194333579 2
4 194333579 1
2 232322225 2
1 226634576 2
4 226634576 3
\.
--
-- Data for Name: region; Type: TABLE DATA; Schema: public; Owner: cromer
--
COPY public.region (id_region, nombre_region) FROM stdin;
1 Florida
2 Alabama
3 Arizona
4 California
9 Egypt
\.
--
-- Data for Name: requerir_auto; Type: TABLE DATA; Schema: public; Owner: cromer
--
COPY public.requerir_auto (id_tour, patente, chofer) FROM stdin;
1 LJ58BV Dominic Toretto
1 AS45FE Luke Hobbs
1 49FD38 Roman Pearce
4 AS45FE Dale Earnheart Jr.
38 LJ58BV Alejandra Chacano
\.
--
-- Data for Name: tiene_enfermedad; Type: TABLE DATA; Schema: public; Owner: cromer
--
COPY public.tiene_enfermedad (rut_turista, id_enfermedad) FROM stdin;
173983808 3
194333579 2
226634576 1
\.
--
-- Data for Name: tour; Type: TABLE DATA; Schema: public; Owner: cromer
--
COPY public.tour (id_tour, nombre_tour, costo_indiv, costo_grupal, minima_personas, id_ciudad) FROM stdin;
1 Los Rios 500000 350000 2 1
3 Peru 400000 110000 2 3
4 San Pedro 400000 110000 2 3
38 Egypt 500000 400000 10 10
2 Valle de la Luna 300000 120000 2 2
\.
--
-- Data for Name: turista; Type: TABLE DATA; Schema: public; Owner: cromer
--
COPY public.turista (rut_turista, nombre_turista, fecha_nacimento, id_contacto) FROM stdin;
173983808 Jimmy Johnson 1955-01-01 3
194333579 Tommy Gunner 1990-06-02 2
232322225 Jimmy Olson 1985-04-03 1
226634576 Jack Asserton 1985-02-23 1
\.
--
-- Data for Name: vehiculo; Type: TABLE DATA; Schema: public; Owner: cromer
--
COPY public.vehiculo (patente, ano_vehiculo, marca, capacidad) FROM stdin;
TASDF8 2001 Toyota 10
LJ58BV 2002 Mazda 20
AS45FE 2017 Chevy 4
49FD38 2001 Subaru 10
\.
--
-- Name: categoria_id_categoria_seq; Type: SEQUENCE SET; Schema: public; Owner: cromer
--
SELECT pg_catalog.setval('public.categoria_id_categoria_seq', 3, true);
--
-- Name: ciudad_id_ciudad_seq; Type: SEQUENCE SET; Schema: public; Owner: cromer
--
SELECT pg_catalog.setval('public.ciudad_id_ciudad_seq', 11, true);
--
-- Name: contacto_emergencia_id_contacto_seq; Type: SEQUENCE SET; Schema: public; Owner: cromer
--
SELECT pg_catalog.setval('public.contacto_emergencia_id_contacto_seq', 3, true);
--
-- Name: descuento_id_descuento_seq; Type: SEQUENCE SET; Schema: public; Owner: cromer
--
SELECT pg_catalog.setval('public.descuento_id_descuento_seq', 4, true);
--
-- Name: enfermedad_id_enfermedad_seq; Type: SEQUENCE SET; Schema: public; Owner: cromer
--
SELECT pg_catalog.setval('public.enfermedad_id_enfermedad_seq', 3, true);
--
-- Name: especialidad_id_especialidad_seq; Type: SEQUENCE SET; Schema: public; Owner: cromer
--
SELECT pg_catalog.setval('public.especialidad_id_especialidad_seq', 4, true);
--
-- Name: lugar_id_lugar_seq; Type: SEQUENCE SET; Schema: public; Owner: cromer
--
SELECT pg_catalog.setval('public.lugar_id_lugar_seq', 7, true);
--
-- Name: region_id_region_seq; Type: SEQUENCE SET; Schema: public; Owner: cromer
--
SELECT pg_catalog.setval('public.region_id_region_seq', 10, true);
--
-- Name: tour_id_tour_seq; Type: SEQUENCE SET; Schema: public; Owner: cromer
--
SELECT pg_catalog.setval('public.tour_id_tour_seq', 45, true);
--
-- Name: categoria categoria_pkey; Type: CONSTRAINT; Schema: public; Owner: cromer
--
ALTER TABLE ONLY public.categoria
ADD CONSTRAINT categoria_pkey PRIMARY KEY (id_categoria);
--
-- Name: ciudad ciudad_pkey; Type: CONSTRAINT; Schema: public; Owner: cromer
--
ALTER TABLE ONLY public.ciudad
ADD CONSTRAINT ciudad_pkey PRIMARY KEY (id_ciudad);
--
-- Name: contacto_emergencia contacto_emergencia_pkey; Type: CONSTRAINT; Schema: public; Owner: cromer
--
ALTER TABLE ONLY public.contacto_emergencia
ADD CONSTRAINT contacto_emergencia_pkey PRIMARY KEY (id_contacto);
--
-- Name: descuento descuento_pkey; Type: CONSTRAINT; Schema: public; Owner: cromer
--
ALTER TABLE ONLY public.descuento
ADD CONSTRAINT descuento_pkey PRIMARY KEY (id_descuento);
--
-- Name: empresa empresa_pkey; Type: CONSTRAINT; Schema: public; Owner: cromer
--
ALTER TABLE ONLY public.empresa
ADD CONSTRAINT empresa_pkey PRIMARY KEY (rut_empresa);
--
-- Name: enfermedad enfermedad_pkey; Type: CONSTRAINT; Schema: public; Owner: cromer
--
ALTER TABLE ONLY public.enfermedad
ADD CONSTRAINT enfermedad_pkey PRIMARY KEY (id_enfermedad);
--
-- Name: especialidad especialidad_pkey; Type: CONSTRAINT; Schema: public; Owner: cromer
--
ALTER TABLE ONLY public.especialidad
ADD CONSTRAINT especialidad_pkey PRIMARY KEY (id_especialidad);
--
-- Name: guia guia_pkey; Type: CONSTRAINT; Schema: public; Owner: cromer
--
ALTER TABLE ONLY public.guia
ADD CONSTRAINT guia_pkey PRIMARY KEY (rut_guia);
--
-- Name: lugar lugar_pkey; Type: CONSTRAINT; Schema: public; Owner: cromer
--
ALTER TABLE ONLY public.lugar
ADD CONSTRAINT lugar_pkey PRIMARY KEY (id_lugar);
--
-- Name: region region_pkey; Type: CONSTRAINT; Schema: public; Owner: cromer
--
ALTER TABLE ONLY public.region
ADD CONSTRAINT region_pkey PRIMARY KEY (id_region);
--
-- Name: tour tour_pkey; Type: CONSTRAINT; Schema: public; Owner: cromer
--
ALTER TABLE ONLY public.tour
ADD CONSTRAINT tour_pkey PRIMARY KEY (id_tour);
--
-- Name: turista turista_pkey; Type: CONSTRAINT; Schema: public; Owner: cromer
--
ALTER TABLE ONLY public.turista
ADD CONSTRAINT turista_pkey PRIMARY KEY (rut_turista);
--
-- Name: vehiculo vehiculo_pkey; Type: CONSTRAINT; Schema: public; Owner: cromer
--
ALTER TABLE ONLY public.vehiculo
ADD CONSTRAINT vehiculo_pkey PRIMARY KEY (patente);
--
-- Name: total_coordinadores _RETURN; Type: RULE; Schema: public; Owner: cromer
--
CREATE OR REPLACE VIEW public.total_coordinadores AS
SELECT t.id_tour AS idt,
t.nombre_tour AS nombret,
count(DISTINCT p.rut_guia) AS totalcoordinadores
FROM ((((((public.participa p
JOIN public.tour t ON ((t.id_tour = p.id_tour)))
JOIN public.guia g ON (((g.rut_guia)::text = (p.rut_guia)::text)))
JOIN public.categoria c ON ((c.id_categoria = p.id_categoria)))
JOIN public.posee p2 ON (((g.rut_guia)::text = (p2.rut_guia)::text)))
JOIN public.asociado a ON ((a.id_tour = t.id_tour)))
JOIN public.lugar l ON ((l.id_lugar = a.id_lugar)))
WHERE ((c.descripcion_categoria = 'Coordinador'::text) AND (EXISTS ( SELECT
FROM public.lugar
WHERE ((lugar.id_lugar = l.id_lugar) AND (l.nivel = 3)))) AND (( SELECT count(posee.rut_guia) AS count
FROM public.posee
WHERE ((posee.rut_guia)::text = (g.rut_guia)::text)) > 3))
GROUP BY t.id_tour;
--
-- Name: total_turistas _RETURN; Type: RULE; Schema: public; Owner: cromer
--
CREATE OR REPLACE VIEW public.total_turistas AS
SELECT t.id_tour AS idt,
t.nombre_tour AS nombret,
count(r.rut_turista) AS totalturistas
FROM (((public.tour t
JOIN public.realiza r ON ((t.id_tour = r.id_tour)))
JOIN public.asociado a ON ((t.id_tour = a.id_tour)))
JOIN public.lugar l ON ((a.id_lugar = l.id_lugar)))
WHERE ((EXISTS ( SELECT lugar.id_lugar
FROM public.lugar
WHERE ((lugar.id_lugar = l.id_lugar) AND (lugar.nivel >= 5)))) AND ((a.fecha_llegada >= '2018-10-01'::date) AND (a.fecha_llegada <= '2018-10-31'::date)) AND (NOT (EXISTS ( SELECT tiene_enfermedad.id_enfermedad
FROM public.tiene_enfermedad
WHERE (((tiene_enfermedad.rut_turista)::text = (r.rut_turista)::text) AND ((tiene_enfermedad.id_enfermedad = 1) OR (tiene_enfermedad.id_enfermedad = 3)))))))
GROUP BY t.id_tour;
--
-- Name: tour_descuentos _RETURN; Type: RULE; Schema: public; Owner: cromer
--
CREATE OR REPLACE VIEW public.tour_descuentos AS
SELECT t.id_tour AS idt,
t.nombre_tour AS nombret,
sum(DISTINCT ((( SELECT descuento.porcentaje
FROM public.descuento
WHERE (descuento.id_descuento = r.id_descuento)) * (( SELECT count(realiza.rut_turista) AS count
FROM public.realiza
WHERE ((realiza.id_tour = r.id_tour) AND (realiza.id_descuento = r.id_descuento))))::numeric) * (
CASE
WHEN (t.minima_personas <= ( SELECT count(realiza.rut_turista) AS count
FROM public.realiza
WHERE (realiza.id_tour = t.id_tour))) THEN ( SELECT tour.costo_grupal
FROM public.tour
WHERE (tour.id_tour = t.id_tour))
ELSE ( SELECT tour.costo_indiv
FROM public.tour
WHERE (tour.id_tour = t.id_tour))
END)::numeric)) AS totaldescuentos
FROM (public.tour t
JOIN public.realiza r ON ((t.id_tour = r.id_tour)))
WHERE (r.id_descuento <> 2)
GROUP BY t.id_tour;
--
-- Name: valores_tours _RETURN; Type: RULE; Schema: public; Owner: cromer
--
CREATE OR REPLACE VIEW public.valores_tours AS
SELECT t.id_tour AS idt,
t.nombre_tour AS nombret,
(
CASE
WHEN (t.minima_personas <= ( SELECT count(realiza.rut_turista) AS count
FROM public.realiza
WHERE (realiza.id_tour = t.id_tour))) THEN ( SELECT tour.costo_grupal
FROM public.tour
</