HW04: Database Design due Wed 07 Feb 13:00
Allowed and Disallowed Resources
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.
Overview
You will be creating designs for three databases which will be provided in a text file (i.e., has a .txt extension). For each design you will do the following:- A sentence explaining the purpose of the database.
- List all fields the database will store along with a phrase or sentence that explains what it is.
- Identify and list all functional dependencies.
- Show the proposed BCNF design using list notation. Use an asterisk to identify primary keys.
If I were doing this assignment for the book database we've been using my solution would look something like this:
This database will hold information about books, authors, and publishers. We limit each book to have only one author. List of Fields isbn the standard book number title title of the book num_pages number of pages in the book year copyright year of the book auth_id author's id number auth_first book author's first name auth_last book author's last name pub_id publisher's id number pub_name name of the book's publisher pub_addr city/state of the publisher Functional Dependencies FD: pub_id --> pub_name, pub_addr FD: author_id --> author_first, author_last FD: isbn --> title,num_pages,year Design publisher=(pub_id*,pub_name,pub_addr) author=(author_id*,auth_first,auth_last) book=(isbn*,title,num_pages,year,auth_id,pub_id)
Three Designs
Two of the following designs are presented in UML notation here: https://josephus.hsutx.edu/classes/db/source/design_diagrams.pdf. The designs in this document were produced by beginning students who had not prior training in database design. For that reason, some of the designs probably don't make a lot of sense as presented. If that is the case then you should try to imagine what the design was intended to accomplish and then tweak the design as needed for your answers below.Produce a database design for each of these:
- The “candy” database given in the above design document. You may need
to modify/adjust fields in order for the design to make sense.
- The “four” database (dealing with recipes) given in the above
design document. You may need to modify/adjust fields in order for the
design to make sense.
- Imagine a major hotel is wanting to keep track of room reservations. To do so they need to store the following information: customer name, customer payment information, date of reservation, number of adults with the reservation, number of children with the reservation, room number, number of beds in the room, maximum occupancy for the room, smoking permitted in the room or not.
Grading and Submission
All three of your designs should be in a single text document and should follow the instructions provided. Upload the document into Canvas prior to the beginning of class on the due date.The assignment has 30 possible points with each design being designated 10 points as follows: 1 point for the descriptive sentence, 3 points for list of fields and descriptions, 2 points for identifying functional dependencies, and 4 points for the final design.