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.
In this assignment we will continue with the hotel reservations theme. However,
we will focus on only a subset of problem and you will use a pre-existing
database rather than your own design. In particular, use the tables found in
https://josephus.hsutx.edu/classes/db/source/hoteldb.sql. Start by running
the provided script and verifying that it works correctly. The provided script
will create and populate tables. However, the tables have no specified integrity
constraints. You will systematically provide the constraints listed below and then
fix data to follow the specified constraints.
Modify your copy of hoteldb.sql as follows:
- (2 pts) Add primary keys to every table. The primary key for the
room_reservation
table should include both fields. Once the primary
keys have been added. Make appropriate changes to the insert commands
to support those changes.
- (4 pts) In the
reservation
table identify customer_id
as
a foreign key. If a customer record is deleted or updated it should also
delete/update the reservation accordingly. Fix inserts as needed. Verify
that deletes/updates are behaving properly by adding a queries at the end
of the document to delete customer whose id is 3 and to update customer whose
id is 2 to have an id of 3.
- (2 pts) In the
payment_method
table create an entry that has
a payment_method_id
of zero that has a card type call “cash” with
a blank card number, code, an expiration date. Then make this payment method
the default method for a reservation.
- (3 pts) In the
reservation
table identify payment_method_id
as
a foreign key. If a payment method is deleted it should be set to its default
value. Updates should cascade. Fix inserts as needed. Verify that deletes
are behaving properly by adding a query at the end of the document to delete
payment method 1.
- (1 pts) Restrict customer name (first and last) to disallow null values.
Fix inserts as needed.
- (2 pts) Require the
card_number
and expiration_date
fields in the payment_method
table to be unique.
By that I mean, the table should not allow those two values to be the
same for separate rows. It would be okay if the same card number appears
in a separate row as long as the expiration date is different.
Fix inserts as needed.
- (3 pts) Require
max_occupancy
to be in the range 0 to 6. Fix
inserts as needed.
- (3 pts) Require
card_code
to be a 3 digit number (and change its
type to CHAR(3)
. Fix inserts as needed.
- (3 pts) Add a default value for the
rdate
field in the
reservation
table to be set to the current date. Add a query at the
bottom of the document that inserts a new reservation but does not specify
a value for the date to verify it is working.
- (2 pts) Add foreign key constraints to both of the fields in
the
room_reservation
table.
- (1 pts) Require values for the fields
max_occupancy
and cost
in the room
table.
- (2 pts) In a SQL comment section describe one possible more sophisticated
constraint on data that could be useful if this database were to actually
be used by a hotel for managing reservations. As one example, you might
want to flag if a the same credit card was being used by two different
people. Come up with one more example.
Once you have completed the document upload your hoteldb.sql file into canvas.
Point values for each step are identified above.