Assignment
Overview
The concepts of primary key, foreign key, and table representations are the foundation for understanding the newer data management techniques you will be learning in this course.
The best way to review is to create an entity-relationship model for a sample problem, then implement the resulting model in data-definition language of SQL. Thus, a sample problem is provided and you should review entity-relationship modeling techniques, listen to the recorded review of ER modeling, carefully analyze the Hilton hotel problem description, and begin to develop your ER model.
You should complete the following activities:
1) Describe the key requirements and data required to support them for the Hilton Hotel presented in the situation described in the attached file: DBProblemDescriptionHiltonResorts-1.docx
2) Create an entity-relationship model for the Hilton Hotel business situation using UML class notation. This Visio file contains a template that you can use: Assignment1-Template.vsdx
3) Explain why your entity-relationship model is in third-normal-form (or justify if not 3NF).
4) Create the SQL DDL that creates the 10 most important tables represented in your UML Class diagram.
5) Write the SQL Procedure that demonstrates that your design is capable of reporting all of the charges a guest has generated during a stay, i.e., the total amount and list of charges provided at checkout. (Note you do not have to implement your design in a DBMS to show that your SQL satisfies this requirement.)
6) Write the SQL to find the hotel that generates the most revenue.
7) Write the SQL to find the service that generates the most revenue for each hotel.
8) Write another interesting question your design can answer.
9) Write the SQL to answer your question from #8.
Directions: Begin this assignment soon, as it will take longer than you expect to work carefully through the problem description and create your initial ER model. You are to create a MS Word document that contains your description of the data needed, ER model (which should be created with Visio or other drawing tool with UML class diagram template), relational model associating attributes with classes, and your explanation of your database normal form.
Format your assignment according to the following formatting requirements:
1. The answer should be typed, double spaced, using Times New Roman font (size 12), with one-inch margins on all sides.
2. The response also include a cover page containing the title of the assignment, the student's name, the course title, and the date. The cover page is not included in the required page length.
3. Also Include a reference page. The Citations and references should follow APA format. The reference page is not included in the required page length.
Attachment:- Assignment-Template.rar