In completing this assignment you MAY use/access the following resources:
- Examples and sample code found here:
https://josephus.hsutx.edu/classes/db/source/
- The SQL handout provided in class and also available here:
https://josephus.hsutx.edu/classes/db/source/sql.pdf
- A text editor (or VSCode) that provides syntax highlighting. You MAY
NOT USE an extensions to such editors/IDEs that produce code.
- The phpPgAdmin front end to your personal database (login is here:
https://csci.hsutx.edu/phpPgAdmin/
- Video instructions provided in Canvas as part of this course. You MAY
NOT USE any other video resources.
- Any handouts provided by the instructor as part of this course.
- Your own course notes
- Your instructor
- Discussions about the assignment with other students as long as you
never look at the code produced by another student and you never receive
instructions about solving the homework. That is, discussions need to
be about concepts and understanding the technologies and not about
how to solve the particular problem posed in this assignment.
You may NOT use/access:
- Resources not expressly listed above, including, but not limited to,
the following ...
- Source code not provided as part of this assignment. (Obviously, this
includes, but is not limited to, source code written by other students
whether current or in the past).
- Code-generating tools (of which ChatGPT is one example).
Failure to abide by these guidelines will result in a zero for the assignment
and the incident will be reported to the university provost as a violation of
the university academic integrity policy. A second incident of academic
dishonesty (whether from this course or another computer science course) will
result in an F in the course.
IMPORTANT: It is likely that your current database design will not accommodate
all of the requirements described below. If that is the case then you will need
to modify your design. This assignment will be pretty time-consuming if you do it
correctly.
Create a text file (with a .sql extension) containing SQL commands that will:
- (2 pts) Establish a schema called hoteldb and set the search path to
it.
- (8 pts) Create tables to implement the hotel database you've been working in
for the last two assignments.
- (8 pts) Populate the tables with at least 7 reservations with additional
corresponding information as follows:
- one customer should have three separate reservations with
two payment methods (one payment method for one stay and another
payment method for the other two stays)
- the data should include stays with children and without children
- should have multiple customers staying on same day as well as
days with only one customer
- should have rooms that have not been used and rooms that have
been used on multiple nights
- should have a single customer with multiple reservations on
the same day
- Write the following select queries:
- (2 pts) a query that will display (in order of date and then
room number) customer name and amount paid for room.
- (2 pts) a query that will display the total amount a customer has
paid across all reservations
- (2 pts) list of rooms that haven't been reserved on a given date
- (2 pts) Demonstrate a delete query (of your choice).
- (2 pts) Demonstrate an update query (of your choice).
Once you have completed the document upload your hoteldb.sql file into canvas.