You are a new employee at a startup company Van Whinkle. The company just purchased several...

     

Transcribed Image Text:

You are a new employee at a startup company Van Whinkle. The company just purchased several mom and pop hotels that they want to incorporate under their Van Whinkle brand name. Because the company is new and the hotels were previously owned by small business owners, they do not have any electronic databases. Most have their data on paper. Only a few were a little bit more organized and had spreadsheets and QuickBooks. This Assignment has asked you to create three new databases, one for each of the database technologies you've covered so far in this course: 1. Microsoft SQL Server 2. Oracle; and 3. MYSQL 1 You were also handed over the following data scribbles on 8 x 11 pages, napkins, and the backs of customer receipts. They are for one hotel based in London Ontario. Notice that some data is missing. How are you going to handle it? Defaults? Manually entering data? You will need to update your approach & assumptions accordingly. o Bob Smith is the manager of the hotel with 20 years' experience. He makes $80,000 per year and lives in Windsor Ontario. His manager is Deena Donor. o Bob Smith is the desk clerk with only 5 years of experience at the hotel. He makes $15 per hour and lives in Toronto Ontario. His manager is the other Bob Smith. o Tanya Duncan does maintenance and has been with the hotel for 10 years. She makes $5,000 per month and lives in London Ontario. Her manager is Bob Smith. o Kristoff Kurn was a customer that reserved a room Jan 3rd and schecked in on Feb 3rd. She checked out on Feb 17th. She paid a deposit of $50 and her room rate per day was $50. She was a smoker so she was charged $25 for smoke damage and $30 for the carpet burns. She lives in Vancouver, British Columbia o Billy Elliot was a customer that reserved a room online the same day he checked in. He checked in on Jan 20th at 4pm. He is a student at Queens University and paid the student discount rate of $30 per day. Because he is a loyal customer he didn't need to pay a deposit. He lives in Toronto. He used to live in Winnipeg Manitoba. o Justin Hackman is in sales and travels back and forth from Detroit and Toronto. Because of his frequent stays at the hotel he doesn't need a reservation and pays a discount rate of $35 per day and a deposit of $20 because he is a smoker. He lives in Woodstock and stayed at the hotel overnight on Feb 2nd, 8th, 17th, and 28th. o Deena Donor is the operation manager of the hotel. She oversees the hotel by coming, in person on the 1st of every month. She has been doing this from January through to April. Because of her position, she is not charged a deposit or room rate. When she does checkout the desk clerk sends the invoice directly to Van Whinkle for reimbursement. Here are the queries you are asked to create using SQL. You do not need to include the results of those queries. Any Blanks. For each row retrieved, show all customers that have a blank in at least one of its columns. Sort from Z to A on last name then A to Z on first name. Number of Stays. For each row retrieved, show the customer name and number of days stayed at the hotel where guests stayed at most 2 days, stayed exactly 4 days, and stayed more than 6 days. The number of stays MUST be in one column and calculated using the query. If a customer stays at a hotel for 3 days, leaves and comes back for 2 days, the customer DID NOT stay for 5 days. The customer stayed for 2 days and for 3 days. Expenses including Room Rate. For each row retrieved, show the customer name, each expense, and their total expenses when total expenses (including room rate) are larger than $50. Sort by highest expenses to lowest. All totals must be calculated using the query. The query MUST NOT change when new types of expenses are added to the database. Expenses excluding Room Rate. For each row retrieved, show the customer name, each expense, and their total expenses when total expenses (excluding room rate) are larger than $50. Sort by highest total expenses to lowest. All totals must be calculated using the query. The query MUST NOT change when new types of expenses are added to the database. Employees. For each row retrieved, show the employee's name, their city, their manager, and their manager's city. From London and Winnipeg. For each row retrieved, show which employees or customers are from London and Winnipeg using using the words London and Winnipeg in the SELECT statement. From Cities. For each row retrieved, show which employees and customers are from specific cities. Do this by using a CITYLIST table to filter the query's results. Employees are Customers. For each row retrieved, show which employees are also customers Employee Customer M C. For each row retrieved, show all employees data and their corresponding customer data for employees that have last names starting with M or have a C in it. Take note that you have also heard the owner plans on purchasing golf courses, casinos, and day spas in the near future, all of which will carry the Van Whinkle brand name. Hints Think about what you've seen on invoices, your bank statement, transcripts, and how you enter data online. How do you think that data is stored. Here's an example of a bank statement. Date Jan 10 Feb 12 Mar 11 Mar 24 Description Here's a subset of a telecom bill Cheque Deposit Online Transfer Online Payment Mobile Deposit Type of Service Amount Internet Cable Mobile Phone 2 Year Promotion Item GROCERY Diet C-UP MEAT Pepsi Bottle $25.00 $30.00 $50.00 - $10.00 Amounts Debited From Account ($) Price 0.99 2.29 $700.00 $400.00 And here's an example of a sales receipt from the grocery story. Date Jun 30 Jun 30 Jun 30 Jun 30 Amounts credited to your account ($) $1,000.00 $500.00 Balance ($) 2,000.00 1,300.00 900.00 1,400.00 Chicken Steak PRODUCE Gold Apples Cucumbers DISCOUNT 5.56 13.78 4.98 4.88 - 2.00 What table structures do you think were used? You are a new employee at a startup company Van Whinkle. The company just purchased several mom and pop hotels that they want to incorporate under their Van Whinkle brand name. Because the company is new and the hotels were previously owned by small business owners, they do not have any electronic databases. Most have their data on paper. Only a few were a little bit more organized and had spreadsheets and QuickBooks. This Assignment has asked you to create three new databases, one for each of the database technologies you've covered so far in this course: 1. Microsoft SQL Server 2. Oracle; and 3. MYSQL 1 You were also handed over the following data scribbles on 8 x 11 pages, napkins, and the backs of customer receipts. They are for one hotel based in London Ontario. Notice that some data is missing. How are you going to handle it? Defaults? Manually entering data? You will need to update your approach & assumptions accordingly. o Bob Smith is the manager of the hotel with 20 years' experience. He makes $80,000 per year and lives in Windsor Ontario. His manager is Deena Donor. o Bob Smith is the desk clerk with only 5 years of experience at the hotel. He makes $15 per hour and lives in Toronto Ontario. His manager is the other Bob Smith. o Tanya Duncan does maintenance and has been with the hotel for 10 years. She makes $5,000 per month and lives in London Ontario. Her manager is Bob Smith. o Kristoff Kurn was a customer that reserved a room Jan 3rd and schecked in on Feb 3rd. She checked out on Feb 17th. She paid a deposit of $50 and her room rate per day was $50. She was a smoker so she was charged $25 for smoke damage and $30 for the carpet burns. She lives in Vancouver, British Columbia o Billy Elliot was a customer that reserved a room online the same day he checked in. He checked in on Jan 20th at 4pm. He is a student at Queens University and paid the student discount rate of $30 per day. Because he is a loyal customer he didn't need to pay a deposit. He lives in Toronto. He used to live in Winnipeg Manitoba. o Justin Hackman is in sales and travels back and forth from Detroit and Toronto. Because of his frequent stays at the hotel he doesn't need a reservation and pays a discount rate of $35 per day and a deposit of $20 because he is a smoker. He lives in Woodstock and stayed at the hotel overnight on Feb 2nd, 8th, 17th, and 28th. o Deena Donor is the operation manager of the hotel. She oversees the hotel by coming, in person on the 1st of every month. She has been doing this from January through to April. Because of her position, she is not charged a deposit or room rate. When she does checkout the desk clerk sends the invoice directly to Van Whinkle for reimbursement. Here are the queries you are asked to create using SQL. You do not need to include the results of those queries. Any Blanks. For each row retrieved, show all customers that have a blank in at least one of its columns. Sort from Z to A on last name then A to Z on first name. Number of Stays. For each row retrieved, show the customer name and number of days stayed at the hotel where guests stayed at most 2 days, stayed exactly 4 days, and stayed more than 6 days. The number of stays MUST be in one column and calculated using the query. If a customer stays at a hotel for 3 days, leaves and comes back for 2 days, the customer DID NOT stay for 5 days. The customer stayed for 2 days and for 3 days. Expenses including Room Rate. For each row retrieved, show the customer name, each expense, and their total expenses when total expenses (including room rate) are larger than $50. Sort by highest expenses to lowest. All totals must be calculated using the query. The query MUST NOT change when new types of expenses are added to the database. Expenses excluding Room Rate. For each row retrieved, show the customer name, each expense, and their total expenses when total expenses (excluding room rate) are larger than $50. Sort by highest total expenses to lowest. All totals must be calculated using the query. The query MUST NOT change when new types of expenses are added to the database. Employees. For each row retrieved, show the employee's name, their city, their manager, and their manager's city. From London and Winnipeg. For each row retrieved, show which employees or customers are from London and Winnipeg using using the words London and Winnipeg in the SELECT statement. From Cities. For each row retrieved, show which employees and customers are from specific cities. Do this by using a CITYLIST table to filter the query's results. Employees are Customers. For each row retrieved, show which employees are also customers Employee Customer M C. For each row retrieved, show all employees data and their corresponding customer data for employees that have last names starting with M or have a C in it. Take note that you have also heard the owner plans on purchasing golf courses, casinos, and day spas in the near future, all of which will carry the Van Whinkle brand name. Hints Think about what you've seen on invoices, your bank statement, transcripts, and how you enter data online. How do you think that data is stored. Here's an example of a bank statement. Date Jan 10 Feb 12 Mar 11 Mar 24 Description Here's a subset of a telecom bill Cheque Deposit Online Transfer Online Payment Mobile Deposit Type of Service Amount Internet Cable Mobile Phone 2 Year Promotion Item GROCERY Diet C-UP MEAT Pepsi Bottle $25.00 $30.00 $50.00 - $10.00 Amounts Debited From Account ($) Price 0.99 2.29 $700.00 $400.00 And here's an example of a sales receipt from the grocery story. Date Jun 30 Jun 30 Jun 30 Jun 30 Amounts credited to your account ($) $1,000.00 $500.00 Balance ($) 2,000.00 1,300.00 900.00 1,400.00 Chicken Steak PRODUCE Gold Apples Cucumbers DISCOUNT 5.56 13.78 4.98 4.88 - 2.00 What table structures do you think were used?

Answer rating: 100% (QA)

It appears youre being asked to create a new database for a startup company that involves three database technologies Microsoft SQL Server Oracle and ... View the full answer