· 6 years ago · Jan 03, 2020, 09:20 AM
1begin;
2
3CREATE TABLE IF NOT EXISTS providers (
4 id serial PRIMARY KEY,
5 username VARCHAR (50) UNIQUE NOT NULL,
6 password VARCHAR (50) NOT NULL,
7 email VARCHAR (355) UNIQUE NOT NULL,
8 is_active BOOLEAN NOT NULL DEFAULT FALSE,
9 created_on TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
10 last_login TIMESTAMP
11);
12
13CREATE TABLE IF NOT EXISTS pprofiles(
14 provider_id serial PRIMARY KEY,
15 bio VARCHAR (355),
16 phone VARCHAR (50) NOT NULL,
17 birth_date VARCHAR (20),
18 photo VARCHAR (50) ,
19 short_description text,
20 FOREIGN KEY (provider_id) REFERENCES providers (id) ON DELETE CASCADE
21);
22
23INSERT INTO providers(username, password, email) VALUES ('anhdt', '123456', 'duycuong87vn@gmail.com');
24INSERT INTO pProfiles(provider_id, bio, phone) values (1, 'I like football', '0969907605');
25
26INSERT INTO providers(username, password, email) VALUES ('freeman', '123456', 'anhdt@gmail.com');
27INSERT INTO pProfiles(provider_id, bio, phone) values (2, 'I like football', '0934591921');
28
29commit;
30
31select * from providers, pprofiles;
32select * from pprofiles;
33
34begin:
35
36CREATE TABLE IF NOT EXISTS trainers (
37 id serial PRIMARY KEY,
38 username VARCHAR (50) NOT NULL,
39 birth_date VARCHAR (50) NOT NULL,
40 email VARCHAR (355) UNIQUE NOT NULL,
41 phone VARCHAR (50) UNIQUE NOT NULL,
42 status BOOLEAN NOT NULL DEFAULT FALSE
43);
44
45CREATE TABLE IF NOT EXISTS service_address (
46 id serial PRIMARY KEY,
47 name VARCHAR not null,
48 city VARCHAR (50) NOT NULL,
49 district VARCHAR (50) NOT NULL,
50 postal_code VARCHAR (50) UNIQUE NOT NULL,
51 long float (50),
52 lat float (50)
53);
54
55commit;
56
57
58begin;
59
60CREATE TABLE IF NOT EXISTS service_group_types (
61 id serial PRIMARY KEY,
62 type_name VARCHAR (50) UNIQUE NOT NULL,
63 created_on TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
64);
65
66CREATE TABLE IF NOT EXISTS service_groups (
67 id serial PRIMARY KEY,
68 provider_id integer not null,
69 sgroup_type_id integer not null,
70 name VARCHAR (50) UNIQUE NOT NULL,
71 created_on TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
72 short_description text,
73 FOREIGN KEY (provider_id) REFERENCES providers(id) ON DELETE CASCADE,
74 FOREIGN KEY (sgroup_type_id) REFERENCES service_group_types(id) ON DELETE CASCADE
75);
76
77CREATE TABLE IF NOT EXISTS service_plans (
78 id serial PRIMARY KEY,
79 service_groups_id integer not null,
80 total_sessions integer not null,
81 sessions_per_week integer NOT NULL,
82 opening_day TIMESTAMP not null,
83 short_description text,
84 FOREIGN KEY (service_groups_id) REFERENCES service_groups(id) ON DELETE CASCADE
85);
86
87CREATE TABLE IF NOT EXISTS service_schedule (
88 id serial PRIMARY KEY,
89 service_plan_id integer not null,
90 days_of_week VARCHAR (150) not null,
91 limit_number_of_kid integer not null,
92 current_number integer not null,
93 total_price float,
94 status VARCHAR(50),
95 short_description text,
96 FOREIGN KEY (service_plan_id) REFERENCES service_plans(id) ON DELETE CASCADE
97);
98
99CREATE TABLE IF NOT EXISTS schedule_details (
100 id serial PRIMARY KEY,
101 service_schedule_id integer not null,
102 exact_day VarChar (50) not null,
103 exact_date date not null,
104 start_time time NOT NULL,
105 end_time time NOT NULL,
106 FOREIGN KEY (service_schedule_id) REFERENCES service_schedule(id) ON DELETE CASCADE
107);
108
109CREATE TABLE IF NOT EXISTS service_details (
110 time_detail_id serial PRIMARY KEY,
111 trainer_id integer not null,
112 service_address_id integer not null,
113 price float NOT NULL,
114 status VARCHAR(50) NOT NULL,
115 FOREIGN KEY (time_detail_id) REFERENCES schedule_details(id),
116 FOREIGN KEY (trainer_id) REFERENCES trainers(id),
117 FOREIGN KEY (service_address_id) REFERENCES service_address(id)
118);
119
120
121
122
123commit;
124
125INSERT INTO trainers(username, birth_date, email, phone) values ('Jack', '1990-11-11 08:00:00', 'jack@gmail.com', '+840923481828');
126INSERT INTO trainers(username, birth_date, email, phone) values ('Mike', '1991-12-12 08:00:00', 'mike@gmail.com', '+840923481829');
127select * from trainers;
128
129INSERT INTO service_address(name, city, district, postal_code, long, lat) values ('110-TDH', 'Porland', 'Roseway', '191919', 10.3331, 30.1111);
130INSERT INTO service_address(name, city, district, postal_code, long, lat) values ('95-KM', 'Porland' , 'Buckman', '202020', 20.3331, 50.1111);
131select * from service_address;
132
133INSERT INTO service_group_types(type_name) VALUES ('one-time');
134INSERT INTO service_group_types(type_name) VALUES ('recurrent');
135select * from service_group_types;
136
137INSERT INTO service_groups(provider_id, sgroup_type_id, name) values (1, 2, 'English Class for kids - 2 to 3 years old ');
138INSERT INTO service_groups(provider_id, sgroup_type_id, name) values (1, 2, 'English Class for kids - 4 to 6 years old ');
139INSERT INTO service_groups(provider_id, sgroup_type_id, name) values (1, 1, 'Football Class for kids - 5 to 7 years old ');
140select * from service_groups;
141
142INSERT INTO service_plans(service_groups_id, total_sessions, sessions_per_week, opening_day) values (1, 20, 2, '2019-12-27 08:00:00');
143INSERT INTO service_plans(service_groups_id, total_sessions, sessions_per_week, opening_day) values (1, 20, 3, '2019-12-27 08:00:00');
144INSERT INTO service_plans(service_groups_id, total_sessions, sessions_per_week, opening_day) values (2, 30, 3, '2019-12-28 08:00:00');
145select * from service_plans;
146
147
148INSERT INTO service_schedule(service_plan_id, days_of_week, limit_number_of_kid, current_number, total_price, status) values (1, 'Monday,Tuesday', 20, 10, 400, 'Reservating');
149INSERT INTO service_schedule(service_plan_id, days_of_week, limit_number_of_kid, current_number, total_price, status) values (1, 'Friday-Sunday', 20, 09, 300, 'Reservating');
150INSERT INTO service_schedule(service_plan_id, days_of_week, limit_number_of_kid, current_number, total_price, status) values (2, 'Monday-Tuesday-Friday', 40, 20, 200, 'Reservating');
151select * from service_schedule;
152
153select sp.service_groups_id as ServGID, sp.opening_day as opening, sp.sessions_per_week as sessions, sers.service_plan_id as PlanID, sers.days_of_week, sers.total_price,
154scd.exact_day, scd.start_time, scd.end_time, sdetail.price, tr.username as trainer, sa.city as sersadd
155from service_schedule as sers
156inner join schedule_details as scd on sers.id = scd.service_schedule_id
157inner join service_details as sdetail on scd.id = sdetail.time_detail_id
158inner join trainers as tr on sdetail.trainer_id = tr.id
159inner join service_address as sa on sdetail.service_address_id = sa.id
160inner join service_plans as sp on sers.service_plan_id = sp.id
161where sp.id = 1
162
163
164INSERT INTO schedule_details(service_schedule_id, exact_day, start_time, end_time, exact_date) values (1, 'Monday', '18:00:00', '20:00:00', '2019-12-30');
165INSERT INTO schedule_details(service_schedule_id, exact_day, start_time, end_time, exact_date) values (1, 'Tuesday', '08:00:00', '10:00:00', '2019-12-31');
166
167INSERT INTO schedule_details(service_schedule_id, exact_day, start_time, end_time, exact_date) values (2, 'Friday', '18:00:00', '20:00:00', '2019-01-03');
168INSERT INTO schedule_details(service_schedule_id, exact_day, start_time, end_time, exact_date) values (2, 'Sunday', '08:00:00', '10:00:00', '2019-01-05');
169
170INSERT INTO schedule_details(service_schedule_id, exact_day, start_time, end_time, exact_date) values (3, 'Monday', '19:00:00', '21:00:00', '2019-12-30');
171INSERT INTO schedule_details(service_schedule_id, exact_day, start_time, end_time, exact_date) values (3, 'Tuesday', '08:00:00', '10:00:00', '2019-12-31');
172INSERT INTO schedule_details(service_schedule_id, exact_day, start_time, end_time, exact_date) values (3, 'Friday', '18:00:00', '20:00:00', '2019-01-03');
173
174select * from schedule_details;
175
176
177
178INSERT INTO service_details(time_detail_id, trainer_id, service_address_id, price, status) values (1, 1, 1, 50, 'Reservating');
179INSERT INTO service_details(time_detail_id, trainer_id, service_address_id, price, status) values (2, 1, 1, 50, 'Reservating');
180
181INSERT INTO service_details(time_detail_id, trainer_id, service_address_id, price, status) values (3, 2, 2, 100, 'Reservating');
182INSERT INTO service_details(time_detail_id, trainer_id, service_address_id, price, status) values (4, 2, 2, 150, 'Reservating');
183
184INSERT INTO service_details(time_detail_id, trainer_id, service_address_id, price, status) values (5, 1, 2, 100, 'Reservating');
185INSERT INTO service_details(time_detail_id, trainer_id, service_address_id, price, status) values (6, 2, 1, 150, 'Reservating');
186INSERT INTO service_details(time_detail_id, trainer_id, service_address_id, price, status) values (7, 2, 2, 100, 'Reservating');
187
188
189select * from service_details as sd
190inner join schedule_details as scd on sd.time_detail_id = scd.id
191
192
193select pv.username as provider, sg.name as group_name, sp.opening_day, sp.sessions_per_week as sess_week, sd.price, sd.limit_number_of_kid as limit_num,
194td.expect_date, td.start_time, td.end_time, tr.username as trainer, sa.name as address
195from service_details as sd
196inner join time_details as td on sd.time_detail_id = td.id
197inner join service_plans as sp on td.service_plans_id = sp.id
198inner join service_groups as sg on sp.service_groups_id = sg.id
199inner join providers as pv on sg.provider_id = pv.id
200inner join trainers as tr on sd.trainer_id = tr.id
201inner join service_address as sa on sd.service_address_id = sa.id
202where sd.id = 4