–You don’t need to upload anything for this assignment.
• What to include:
–Your ER model.
–Typeset relational model and constraints.
1. Problem Description
You work for the software company Database Solutions. A hotel chain, Five Star Hotels, has hired your company to develop its new hotel management software. You’ve been tasked with developing a database schema for the database that will drive the software’s backend. As an experienced developer you know a good ER diagram is helpful when making a schema. So you set about drawing up a diagram based on your client’s specifications.
• Each guest has a name, address, zip code, phone number, and ID number.
1
2. Task I (50%)
Draw an ER diagram that meets your client’s specifications. You can draw the diagram on paper or computer. Make sure you capture all relevant constraints.
3. Task II (50%)
(40%) Convert your ER diagram to an equivalent instance of the relational model. The correctness of this part will be based on its faithfulness to your original ER diagram. Simply list all of the resulting relations one at a time. Each relation should be of the form:
RELATION NAME (att1, att2, att3 (fk: references REL2.att6), att4, att5)
Be sure to combine relations from ONE-MANY and ONE-ONE relationships with an appropriate entity. Indicate the conversion method used for any IS-A entities shown in the ER diagram. Be sure to underline key attributes and clearly indicate all foreign keys.
(10%) Include a list of 3 semantic integrity constraints, specified in English. You only need to list the semantic integrity constraints that are directly specified by the ER model, but are not enforced by the keys and foreign keys given along with the relations.
2
