Looking for help on MS access. part 3b - part2 only. Thanks!

 

 

Database Project
Deliverable
ER Diagram of business requirements
Transforming ERD into a relational
database schema
Implementation of database: table
creation, population, updates
Completion of database prototype:
import/enter external data, advanced
SQL queries to support the business Description
Part One
Part Two Due by 11:58 PM On
Tuesday, Feb. 14, 2017
Tuesday, Feb. 21, 2017 Points
25
15 Part Three Tuesday, Feb. 28, 2017 30 Part Four Tuesday, Mar. 7, 2017 30 This is the last part of the project that started with an idea for a database, design of a database using the ER
diagram, transforming the design to relational schema and finally implementing the design and writing advanced
SQL queries. Part Four:
Download the Access file LuxuryLodgingsDatabase.accdb and save as ‘YourName_PartFour’. Use this
database to complete this part of the project. You may need to click the ‘Enable Content’ button before
continuing. All the tables are created. All the tables are populated with data except for the RESERVATION
table.
Steps:
1. (5 points) The GUEST and GUEST_PHONE tables are populated with data. Write a select statement to
view the data in the GUEST and GUEST_PHONE table.
• Write the SQL statements to change the first and last names for guest G101 to your first and last
name. • Guest G101 does not have phone numbers in the GUEST_PHONE table. Write the SQL
statements to add a work phone number and a cell phone number to the GUEST_PHONE table
for Guest G101. Do not use your actual phone numbers; make up numbers. Save all your queries to the database file.
2. (5 points) Import data from an external data file.
Download the Excel file reservationData.xlsx . You do not need Excel on your computer to use this file.
Import the Excel data into the RESERVATION table in your database. The reservations are for the 20 guests
in the GUEST table.
• View the video on How to Import Excel data to ACCESS 3. For the following query requests:
1 • Save each of your SQL queries in the database file. Name each query appropriately.
When you join on a composite primary key and foreign key, you must use join criteria for each
attribute in the composite. a. (5 points) Write the SQL statement to retrieve first and last names of guests who have never
reserved a family (‘F’) room. You must use a subquery.
b. (5 point) The owners would like to know the revenue generated so far
i.e. where CheckOutDate < NOW( )
for each room type in each hotel. Write the SQL statement that will calculate the Revenue
generated for each room type in each hotel. The calculation must be done in the SQL
statement. To calculate the Revenue:
i. Determine the length of stay for each reservation (i.e. number of days) using the
DateDiff function
(datediff(‘d’, CheckInDate, CheckOutDate))
ii. Multiply this calculation by the room rate
iii. Embed the entire calculation in the SQL aggregate function SUM ( )
Your output should be formatted as shown below. The exact revenue totals are not given;
this is made up data.
Keep in mind, your Revenue totals may change on a daily basis, as we want to include only
those reservations that are completed, not ongoing or future reservations. Revenue By Room Type
Hotel RoomType Revenue A111 D 37500 A111 F 14700 A111 S 26700 B100 D 29500 B100 F 17500 B100 S 41700 c. (5 points) Write the SQL statement to determine the number (count) of rooms in each hotel that
have never been reserved. Must use a left or right join.
d. The owners would like to know which rooms were available (not reserved) last year, i.e. from Dec. 1,
2016 through Dec.31, 2016. They want to use this information to predict what the hotel occupancy
might be like for this year (2017). This needs to be done in two parts. Notice the second part is for
extra credit.
Part one: (5 points) Write a query that returns the hotel ID , room number, check in date and
check out date of the rooms that were reserved during the above specified time period. To
determine if a room was reserved (i.e. unavailable) you need to compare the check in and check
out dates in a reservation to the StartDate (Dec. 1, 2016) and the EndDate (Dec. 31, 2016).
2 Note: use date formatted as m/d/yyyy enclosed in tags, for example. #7/1/2016#.
Use the following WHERE clause (replace StartDate and EndDate with Dec. 1, 2016 and Dec. 31,
2016 respectively, in the proper format.
Where CheckInDate between StartDate and EndDate
or CheckOutDate between StartDate and EndDate
or (CheckInDate < StartDate and CheckOutDate > EndDate)
Part two: (5 points Extra Credit) To find the rooms that were available between Dec. 1, 2016
and Dec. 31, 2016 you first need to save the query from part one as ReservedRooms
Then, using ReservedRooms, and any other tables you think you need, write the SQL statement
to return all available rooms for the specified time period. Include the Hotel ID, Room number,
and room type for each available room. Submissions
Submit your ACCESS database file (“yourName_PartFour) via the D2L Dropbox for Database Project Part 4.
Database Project Part Four Grading Rubric
(Based on the criteria in the course rubric available in the Overview of this course in D2L)
Learning Outcomes:
•
• Use external data facility in ACCESS/MySQL to import excel/csv data into the database
Analyze request for information and write the SQL retrieval statement Problem
Number(s)
2 1, 3
(max 5
points each) Proficient
5
Shows a comprehensive
understanding of ACCESS
development facilities Nearing Proficiency
4-3
Shows an adequate
understanding of ACCESS
development facilities Needs Improvement
2-0
Shows minimal or no
understanding of ACCESS
development facilities • Uses ACCESS external data
facilities to get all data
from Excel file into
database; data is imported
to the correct table.
Shows a comprehensive
understanding of the concepts
of database retrieval • Uses ACCESS external data
facilities to get some data
from Excel file into
database; data is imported
to incorrect table.
Shows an adequate
understanding of the concepts
of database retrieval • • • • Completely and correctly
apply selection, projection
and join relational algebra
concepts Identifies all the important Adequately apply selection,
projection and join
relational algebra concepts Identifies most of the important
elements of the retrieval Did not import data from
Excel document into the
database. Shows minimal or no
understanding of the concepts
of database retrieval
Inadequately or did not
apply selection, projection
and join relational algebra
concepts Identifies a minimal number or 3 elements of the retrieval
request
• Completely and correctly
identifies the tables,
attributes, selection and
join criteria, grouping and
ordering of attributes • Completely and correctly
identifies the data subsets
on which aggregate
functions, date functions
and calculations are to be
applied. Uses correct SQL syntax and
notation to execute the
solution completely and
correctly
• Results are complete and
correct. request
• • • Adequate analysis of the
request to identify correct
tables, some of the
attributes and most of the
selection and join criteria,
grouping and ordering of
attributes
Incomplete or incorrectly
identification of the data
subsets on which aggregate
functions, date functions
and calculations are applied
Mostly correct calculations Uses correct SQL syntax and
notation to execute and
incomplete solution
• Results are incomplete
and/or incorrect. none of the important elements
of the retrieval request
• Incomplete or incorrect
analysis of the tables,
attributes, selection and
join criteria, grouping and
ordering of attributes • Minimal or no identification
of the data subsets on
which aggregate functions,
date functions and
calculations are applied • Incorrect or no calculations Uses incorrect SQL syntax and
notation to execute the solution
• Results are incomplete or
incorrect • No results produced due to
SQL syntax or notation
errors. 4

Attachments:

• 1493343042-ProjectPartFour.pdf