Develop 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-

Develop 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-

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: Please 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.

You should create your DDL file using Notepad++, which will help you to check syntax and aids our grading process. When completed, please upload your Word document and DDL text file under the “Assignments” tab. Please use a document naming convention that allows us to determine your name. EXAMPLE: “LastName_FirstName_Assn1.docx or .txt .sql”.

Submission Requirements

· Briefly describe the data for the Hilton Hotel, 1 page. (5 points)

· Using UML Class Diagramming notation, create a complete model for all of the data needed for this business situation. Use MS Visio or other tool to draw your diagram. Do not add attributes to the diagram. Record your attributes as a relational model, i.e., class name and list of attribute names, e.g., Patient(C_Patient_PA, T_PatientFirstName_PA, d_BirthDate_PA). Use the following naming convention for your attributes: c_ indicates a key or foreign key attribute; d_ is a date variable; i_ indicates an integer, n_ indicates some numeric value with decimal places, and t_ indicates an attribute contains a text or string value. Also, add a two character class abbreviation, e.g., _pa, indicating the class the attribute is in the Patient table. Note any assumptions. (20 points)

· Evaluate your model to determine its normal form. Briefly, 1 paragraph, explain what normal form your model is in and why this is the appropriate normal form. Note any assumptions. (5 points)

· Write the create table SQL statements needed to implement the ten most important tables in your relational model. Be sure to include appropriate constraints for keys and foreign keys. Use Notepad++ to create a file with .sql extension for this part of the assignment and also submit that file to the assignment 1 folder. (20 points)

· 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.) (10 points)

· Write the SQL to find the hotel that generates the most revenue. (10 points)

· Write the SQL to find the service that generates the most revenue for each hotel. (10 points)

· Write another interesting question your design can answer. (10 points)

· Write the SQL to answer your question from #8. (10 points)

Assessment

Your ER diagram, normal form explanation, and DDL will be evaluated for correctness and completeness. Correctness is how well your model represents the problem, e.g., the model includes the correct entities and relationships among them, along with appropriate attributes. Completeness is how well your model satisfies all of the requirements of the problem situation.

The post Develop 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- appeared first on graduatepaperhelp.