3cP60UGZ

1-- object: public.hires | type: TABLE --
2-- DROP TABLE IF EXISTS public.hires CASCADE;
3CREATE TABLE public.hires(
4        id smallint NOT NULL,
5        name varchar,
6        CONSTRAINT hires_pk PRIMARY KEY (id)
7
8);
9ALTER TABLE public.hires OWNER TO postgres;
10
11INSERT INTO public.hires (id, name) VALUES (E'1', E'8 hours');
12INSERT INTO public.hires (id, name) VALUES (E'2', E'whole day');
13INSERT INTO public.hires (id, name) VALUES (E'3', E'6 hours (suites)');
14
15-- object: public.rooms | type: TABLE --
16-- DROP TABLE IF EXISTS public.rooms CASCADE;
17CREATE TABLE public.rooms(
18        id smallint NOT NULL,
19        type char(1),
20        name varchar,
21        addhr_price money,
22        corkage_price money,
23        CONSTRAINT ballrooms_pk PRIMARY KEY (id)
24
25);
26COMMENT ON COLUMN public.rooms.type IS 'Ballroom or Suite';
27ALTER TABLE public.rooms OWNER TO postgres;
28
29INSERT INTO public.rooms (id, type, name, addhr_price, corkage_price) VALUES (E'1', E'B', E'Whole venue', E'1500', E'1000');
30INSERT INTO public.rooms (id, type, name, addhr_price, corkage_price) VALUES (E'2', E'B', E'Grand Ballroom Exclusive', E'1000', E'750');
31INSERT INTO public.rooms (id, type, name, addhr_price, corkage_price) VALUES (E'3', E'B', E'Grand Ballroom Non-Exclusive', E'1000', E'750');
32INSERT INTO public.rooms (id, type, name, addhr_price, corkage_price) VALUES (E'4', E'B', E'Meridian Ballroom Exclusive', E'750', E'500');
33INSERT INTO public.rooms (id, type, name, addhr_price, corkage_price) VALUES (E'5', E'B', E'Meridian Ballroom Non-Exclusive', E'750', E'500');
34INSERT INTO public.rooms (id, type, name, addhr_price, corkage_price) VALUES (E'6', E'S', E'Eternity & Infinity', E'350', DEFAULT);
35INSERT INTO public.rooms (id, type, name, addhr_price, corkage_price) VALUES (E'7', E'S', E'Eternity & Infinity  (MG catering)', E'350', DEFAULT);
36INSERT INTO public.rooms (id, type, name, addhr_price, corkage_price) VALUES (E'8', E'S', E'Eternity', E'250', DEFAULT);
37INSERT INTO public.rooms (id, type, name, addhr_price, corkage_price) VALUES (E'9', E'S', E'Eternity (MG catering)', E'250', DEFAULT);
38INSERT INTO public.rooms (id, type, name, addhr_price, corkage_price) VALUES (E'10', E'S', E'Infinity', E'150', DEFAULT);
39INSERT INTO public.rooms (id, type, name, addhr_price, corkage_price) VALUES (E'11', E'S', E'Infinity (MG catering)', E'150', DEFAULT);
40
41-- object: public.days | type: TABLE --
42-- DROP TABLE IF EXISTS public.days CASCADE;
43CREATE TABLE public.days(
44        id smallint NOT NULL,
45        name varchar,
46        CONSTRAINT days_pk PRIMARY KEY (id)
47
48);
49ALTER TABLE public.days OWNER TO postgres;
50
51INSERT INTO public.days (id, name) VALUES (E'1', E'sunday');
52INSERT INTO public.days (id, name) VALUES (E'2', E'saturday');
53INSERT INTO public.days (id, name) VALUES (E'3', E'friday');
54INSERT INTO public.days (id, name) VALUES (E'4', E'monday-thursday');
55
56-- object: public.periods | type: TABLE --
57-- DROP TABLE IF EXISTS public.periods CASCADE;
58CREATE TABLE public.periods(
59        id smallint NOT NULL,
60        description varchar,
61        name varchar,
62        CONSTRAINT periods_pk PRIMARY KEY (id)
63
64);
65ALTER TABLE public.periods OWNER TO postgres;
66
67INSERT INTO public.periods (id, description, name) VALUES (E'1', E'Summer holidays', E'super peak');
68INSERT INTO public.periods (id, description, name) VALUES (E'2', E'Easter & Christmas, Feb., May & Oct. Half Term', E'peak');
69INSERT INTO public.periods (id, description, name) VALUES (E'3', E'Non-holiday', E'off-peak');
70
71-- object: public.prices | type: TABLE --
72-- DROP TABLE IF EXISTS public.prices CASCADE;
73CREATE TABLE public.prices(
74        id smallint NOT NULL,
75        amount money,
76        restrictions varchar,
77        id_hires smallint NOT NULL,
78        id_rooms smallint NOT NULL,
79        id_days smallint NOT NULL,
80        id_periods smallint NOT NULL,
81        CONSTRAINT prices_pk PRIMARY KEY (id)
82
83);
84ALTER TABLE public.prices OWNER TO postgres;
85
86-- object: hires_fk | type: CONSTRAINT --
87-- ALTER TABLE public.prices DROP CONSTRAINT IF EXISTS hires_fk CASCADE;
88ALTER TABLE public.prices ADD CONSTRAINT hires_fk FOREIGN KEY (id_hires)
89REFERENCES public.hires (id) MATCH FULL
90ON DELETE RESTRICT ON UPDATE CASCADE;
91
92-- object: rooms_fk | type: CONSTRAINT --
93-- ALTER TABLE public.prices DROP CONSTRAINT IF EXISTS rooms_fk CASCADE;
94ALTER TABLE public.prices ADD CONSTRAINT rooms_fk FOREIGN KEY (id_rooms)
95REFERENCES public.rooms (id) MATCH FULL
96ON DELETE RESTRICT ON UPDATE CASCADE;
97
98-- object: days_fk | type: CONSTRAINT --
99-- ALTER TABLE public.prices DROP CONSTRAINT IF EXISTS days_fk CASCADE;
100ALTER TABLE public.prices ADD CONSTRAINT days_fk FOREIGN KEY (id_days)
101REFERENCES public.days (id) MATCH FULL
102ON DELETE RESTRICT ON UPDATE CASCADE;
103
104-- object: periods_fk | type: CONSTRAINT --
105-- ALTER TABLE public.prices DROP CONSTRAINT IF EXISTS periods_fk CASCADE;
106ALTER TABLE public.prices ADD CONSTRAINT periods_fk FOREIGN KEY (id_periods)
107REFERENCES public.periods (id) MATCH FULL
108ON DELETE RESTRICT ON UPDATE CASCADE;