I need you to develop a simple database in PostgreSQL 9.3 for a massage reservation system. The result of this project should ONLY be a PostgreSQL database SCHEMA with at least 20 rows for main tables and a couple of SQL QUERIES as outlined below. No additional software is required
Database is required for the software system which allows user to select a date, a massage type, a masseur (from the ones who are available on that date) and pick a 20-minutes long time-slot (not reserved by another user)
Specific tasks:
1. Database schema should contain some or all of the following tables: user, massage type, masseur, reservations, time-slots(predefined, ranging from 9:00 to 17:00 with 20 minutes interval)
2. I need the following SQL queries for this database:
- mark masseur available/unavailable on a specific day
- for the purpose of making a reservation: find a matrix of available masseurs (marked as available), massage types they can perform, and their open timeslots (20 minutes each) on specific date between start and end time.
Considering the following input: (date, massage-type, startTime, endTime) I need the output to look like this: (John, 'chair, swedish', (9:00, 9:20, 9:40, 10:00)), (Barabara: 'shiatsu, chair', (9:00, 10:20)) etc. The output should only list time slots not already taken by other user. If there are no open time slots for some masseur, don't return it in the result
- for a specific date and a masseur, find all his appointments. I.e., provided the following input: (masseur-name, date) get the following output: (9:00, 13:00)
- mark reservation as cancelled
- get all reservations starting in the next 30 minutes. The result should contain: masseur-name, massage-type, user-name and start time
- Find all past reservation of a user
- find all future reservation of a user
- find all past reservations of a masseur
- final all future reservations of a masseur
This task is time sensitive, I expect it to require not more that 3-4 days to complete. If you can't complete it in that timeframe, please don't apply. To make sure, you read all of the above, please put a word GONDOLA at the beginning of your message. Let me know if something is unclear
Thank you