Since the database is not available the following is provided to gain a better understandingSales Table contains the following fields- Sales ID, Invoice No., TransDate, Description, Colour, Size, Qty, Price , Store ID, Year ID, Brand, Name (of customers), Address, Currency Id, YearvalLocation Table contains the following fields - Store ID, Store Name, Store Type (which details what is Duty paid(DP) or Duty free(DF)), Hotel, Home Currency, and Can sell?Currency Table fields are - Currency ID, Rate (rates- USD: 2.0675 and BDS: 1)Relationships between tables have been formed and referencial intergrity enforced. QUESTIONSThe company has been informed that the tax has been increased from 15% to 17.5%. Calculatehow much additional tax (i.e., the difference) the store would have collected by year.Name this query Q3 – ADDED-TAX.Then, Create a report showing the following for Jan, Feb, Mar in 2009. Duty-free sales, duty-paidsales, and taxes (original) are collected monthly by location. The report should be grouped bymonth, sorted by location, and include detail (one per month) and summary (the quarter)numbers. For printing purposes, ensure the report prints each month on a new page with portraitorientation. This report will be distributed at a meeting. Name the report ‘MGMT BoutiqueINC, Monthly Sales Q1, 2009 ’ and put the title on each page. Please explain in detail, the SQL view can be provided or used if it makes explaining easier. These step have been completed but I am not getting the exact results needed and not sure where I am going wrong, hence the request for explaining in great deatail. CASE BACKGROUNDMGMT Boutique Inc. is a high-end retailer that operates a chain of boutique stores in major hotelproperties along the west coast of Barbados, including the famous five-diamonds Sandy Lane Hotel.Operating in eleven (11) locations, the company offers chic brands like Roberto Cavalli, Pain-de-Sucre,Versace, and Etro Milano, to name a few, and is well known for an eclectic collection of designerswimwear, fine linens, cotton and silk apparel for warm days and sultry evenings. A fine complementof shoes, hats and handbags with hand-picked custom jewellery are included to embrace the veryessence of luxurious fashion.The locations connect over a MetroE-wide area network (WAN) that FLOW provides. Each store hasa local Point-of-Sale (POS), replicated to a central hub in the main office. Replication occurs once every15 minutes, so the head office is virtually up-to-date, in real-time, and can see, at a glance, transactionsin each store.Customers primarily shop duty-free, paying in either Barbados (BDS) or USA dollars. Duty-free salesdo not attract a 15.0% VAT tax. The list price, seen on the shelf, is the price paid. For customerconvenience, the company offers a limited range of duty-paid items.Again, customers can pay in either currency or the price listed on the self is still the price paid. However,this price now includes 15.0% VAT. Customers pay by cash or card, charge the expense to their room,and settle the bill later.As a reporting requirement for a duty-free establishment, POS details for customers are routinelycaptured. This information has proven helpful for analysing behaviour, and the company also tries tocapture the exact details of duty-paid transactions. As a rule, duty-free transactions will be done in USdollars, while duty-paid transactions will appear in BDS dollars.●As a new boutique employee, your manager wants you to produce some reports to reviewperformance in preparation for an upcoming trip to pu ase new merchandise.All queries and reports should appear in Barbados dollars, and so do the conversions.Format all money as currency to improve the display.Please don't have weird values with seven (7) decimal places.Use the currency table provided to convert US dollars to Barbados dollars and pay attention to thecurrencies in the sales table. First, you need to peruse the database. It comprises the following tables:● CURRENCY:A list of currencies and conversion rates to the Barbados dollar.SALES: The POS transactions, both duty-free and duty-paid.LOCATION: Both stores that sell and head office stock storage locations.You are asked to prepare several queries, as well as some reports.

To calculate the additional tax collected due to the increase from 15% to 17.5%, we need to identify…

Since the database is not available the following is provided to gain a better understanding Sales Table contains the following fields- Sales ID, Invoice No., TransDate, Description, Colour, Size, Qty, Price , Store ID, Year ID, Brand, Name (of customers), Address, Currency Id, Yearval Location Table contains the following fields - Store ID, Store Name, Store Type (which details what is Duty paid(DP) or Duty free(DF)), Hotel, Home Currency, and Can sell? Currency Table fields are - Currency ID, Rate (rates- USD: 2.0675 and BDS: 1) Relationships between tables have been formed and referencial intergrity enforced. QUESTIONS The company has been informed that the tax has been increased from 15% to 17.5%. Calculate how much additional tax (i.e., the difference) the store would have collected by year. Name this query Q3 – ADDED-TAX. Then, Create a report showing the following for Jan, Feb, Mar in 2009. Duty-free sales, duty-paid sales, and taxes (original) are collected monthly by location. The report should be grouped by month, sorted by location, and include detail (one per month) and summary (the quarter) numbers. For printing purposes, ensure the report prints each month on a new page with portrait orientation. This report will be distributed at a meeting. Name the report ‘MGMT Boutique INC, Monthly Sales Q1, 2009 ’ and put the title on each page. Please explain in detail, the SQL view can be provided or used if it makes explaining easier. These step have been completed but I am not getting the exact results needed and not sure where I am going wrong, hence the request for explaining in great deatail.

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Concept explainers

Question

Since the database is not available the following is provided to gain a better understanding

Sales Table contains the following fields- Sales ID, Invoice No., TransDate, Description, Colour, Size, Qty, Price , Store ID, Year ID, Brand, Name (of customers), Address, Currency Id, Yearval

Location Table contains the following fields - Store ID, Store Name, Store Type (which details what is Duty paid(DP) or Duty free(DF)), Hotel, Home Currency, and Can sell?

Currency Table fields are - Currency ID, Rate (rates- USD: 2.0675 and BDS: 1)

Relationships between tables have been formed and referencial intergrity enforced.

QUESTIONS

The company has been informed that the tax has been increased from 15% to 17.5%. Calculate

how much additional tax (i.e., the difference) the store would have collected by year.

Name this query Q3 – ADDED-TAX.

Then, Create a report showing the following for Jan, Feb, Mar in 2009. Duty-free sales, duty-paid

sales, and taxes (original) are collected monthly by location. The report should be grouped by

month, sorted by location, and include detail (one per month) and summary (the quarter)

numbers. For printing purposes, ensure the report prints each month on a new page with portrait

orientation. This report will be distributed at a meeting. Name the report ‘MGMT Boutique

INC, Monthly Sales Q1, 2009 ’ and put the title on each page.

Please explain in detail, the SQL view can be provided or used if it makes explaining easier. These step have been completed but I am not getting the exact results needed and not sure where I am going wrong, hence the request for explaining in great deatail.

CASE BACKGROUND
MGMT Boutique Inc. is a high-end retailer that operates a chain of boutique stores in major hotel
properties along the west coast of Barbados, including the famous five-diamonds Sandy Lane Hotel.
Operating in eleven (11) locations, the company offers chic brands like Roberto Cavalli, Pain-de-Sucre,
Versace, and Etro Milano, to name a few, and is well known for an eclectic collection of designer
swimwear, fine linens, cotton and silk apparel for warm days and sultry evenings. A fine complement
of shoes, hats and handbags with hand-picked custom jewellery are included to embrace the very
essence of luxurious fashion.
The locations connect over a MetroE-wide area network (WAN) that FLOW provides. Each store has
a local Point-of-Sale (POS), replicated to a central hub in the main office. Replication occurs once every
15 minutes, so the head office is virtually up-to-date, in real-time, and can see, at a glance, transactions
in each store.
Customers primarily shop duty-free, paying in either Barbados (BDS) or USA dollars. Duty-free sales
do not attract a 15.0% VAT tax. The list price, seen on the shelf, is the price paid. For customer
convenience, the company offers a limited range of duty-paid items.
Again, customers can pay in either currency or the price listed on the self is still the price paid. However,
this price now includes 15.0% VAT. Customers pay by cash or card, charge the expense to their room,
and settle the bill later.
As a reporting requirement for a duty-free establishment, POS details for customers are routinely
captured. This information has proven helpful for analysing behaviour, and the company also tries to
capture the exact details of duty-paid transactions. As a rule, duty-free transactions will be done in US
dollars, while duty-paid transactions will appear in BDS dollars.
●
As a new boutique employee, your manager wants you to produce some reports to review
performance in preparation for an upcoming trip to pu ase new merchandise.
All queries and reports should appear in Barbados dollars, and so do the conversions.
Format all money as currency to improve the display.
Please don't have weird values with seven (7) decimal places.
Use the currency table provided to convert US dollars to Barbados dollars and pay attention to the
currencies in the sales table.

First, you need to peruse the database. It comprises the following tables:
● CURRENCY:A list of currencies and conversion rates to the Barbados dollar.
SALES: The POS transactions, both duty-free and duty-paid.
LOCATION: Both stores that sell and head office stock storage locations.
You are asked to prepare several queries, as well as some reports.

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 7 steps with 3 images

SEE SOLUTION Check out a sample Q&A here

Follow-up Questions

Read through expert solutions to related follow-up questions below.

Follow-up Question

For the first part of the question that deals with the additional tax, Kindly note that based on the case background it also stated that All Currency is to be converted to BDS (there are sales recorded in USD) and DF (duty free) sales do not attract any tax (there are df sales among the transactions) so therefore apart from calculating the additional tax, the currency needs to be coverted (the rates are USD:2.0675 & BDS:1) and only DP sales should be displayed.
Kindly modify please to reflect this.

Lastly the report did not call for a creation of a table but rather creating a report via report wizard. Is it possible you can redo.

Solution

by Bartleby Expert

SEE SOLUTION

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.