Concept explainers
Since the
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.
Trending nowThis is a popular solution!
Step by stepSolved in 7 steps with 3 images
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.
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.
- Convert the below E/R diagram to relational database scheme form. Date Number Quantity Payment_info orders has N Orderitem Shipping_address discount Price Item_line_number make Contains number Price name Customer Product Type address numberarrow_forwardAssumptions: The database stores information about houses, each with a house ID, address, and zip code. 2. The database stores information about person, including person ID, house ID where the person lives, person name, phone number, and status where 1 means currently tested positive (i.e., the most recent test result is positive), 0 means currently tested negative (i.e., the most recent test result is negative), and null (no test so far so status is unknown). 3.The database stores information about a person's all past tests, including person ID, test date, and result (1 means positive and 0 negative). 4.The database stores information about events (could be gathering, party, etc.). Each event has an event ID, event name, date, and address. 5. The database stores information about a person participated in an event. 6. The database stores information about flights, including flight ID, flight date, flight number (a varchar type e.g., 'DL 345' is a delta 345 flight). 7. The database…arrow_forwardHow many patients in the database have been diagnosed with acute bronchitis (ICD9Code = 466) and have a total of 3 or more diagnoses? (diagnosis table; COUNT(*), WHERE, GROUP BY, & HAVING clauses) / Result = 20arrow_forward
- Development You will be writing the Python code to support the business requirements that were outlined in theWhatABook Rules/Requirements There is no deliverable for this assignment. Instead, use this time to work on the program, database queries, and tables. If you run into issues while developing the database queries to use in the program, refer to the course's GitHub repository, module_10/whatabook_program_queries.sql. Again, try to write the queries on your own and only use this resource as a last resort.arrow_forwardQ1:Draw ERD for the following database (Library) Customer Card number Password Name | Address ID Phone Username number Employee ID Phone number Password Username Name Branch number Card Number CW Branch Number Address | Phone number Bill Customer id Employee id Total price Order ID Day Time Book Isbh Name Availability Id Cost Address Cart Order number Book id Customer id Card number Customer Total price namearrow_forwardReducing the size of a table in a relational database may be done using a select operation by removing columns that aren't needed.Is that so, or not?arrow_forward
- The following tables form part of a database held in a relational DBMS:Hotel (hotelNo, hotelName, city)Room (roomNo, hotelNo, type, price)Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo)Guest (guestNo, guestName, guestAddress)• Hotel contains hotel details and hotelNo is the primary key;• Room contains room details for each hotel and (roomNo, hotelNo) forms the primarykey;• Booking contains details of the bookings and (hotelNo, guestNo, dateFrom) forms theprimary key;• Guest contains guest details and guestNo is the primary key.Run the SQL statements for the following queries and report the screenshotsof these SQL statements and the results generated from PostgreSQL.(a) List the roomNo, type, and price of the Biltmore Hotel (using subquery) (or a specifichotel name in your database).(b) What is the average price of a room?(c) List the guestNo and gusetName of guests who live in Chicago or Seattle, alphabeticallyordered by their names.(d) List the number of rooms in each hotel in…arrow_forward10A-arrow_forwardThe select operation in a relational database allows you to limit the number of columns in a table that satisfy your criteria.Is that the correct or incorrect answer?arrow_forward
- Topic: Database Design What are some concerns about using a date (or date time) as a primary key?arrow_forwardEvery data structure that we use in computer science has its weaknesses and strengthsHaving a full understanding of each will help make us better programmers!For this experiment, let's work with STL vectors and STL dequesFull requirements descriptions are found in the source code file Part 1Work with inserting elements at the front of a vector and a deque (30%) Part 2Work with inserting elements at the back of a vector and a deque (30%) Part 3Work with inserting elements in the middle, and removing elements from, a vector and a deque (40%) Please make sure to put your code specifically where it is asked for, and no where elseDo not modify any of the code you already see in the template file This C++ source code file is required to complete this problemarrow_forwardDesign your application's/organization's database. You need to create the 3 tables for each of the services offered. You must provide at least 5 examples in each of the table. There must be a table in which you have prices linked to the services you are providing. THIS IS THE NAME & DESCRIPTION OF THE ORGANIZATION FOR WHICH YOU NEED TO CREATE 3 TABLES FOR EACH SERVICE PROVIDED *Name of the Organization* :: ECOHUB *Description of the Organization* :: EcoHub is a sustainability-focused organisation that seeks to encourage environmentally friendly behaviours, increase public knowledge of environmental problems, and offer solutions for people, organisations, and communities to reduce their carbon footprint and help make the world a greener place. We think that many little individual actions can have a big influence on the environment when taken together. *Services Offered by the Organization are*1. EcoConsultancy : We provide individualised eco-consultancy services to people and…arrow_forward
- Database System ConceptsComputer ScienceISBN:9780078022159Author:Abraham Silberschatz Professor, Henry F. Korth, S. SudarshanPublisher:McGraw-Hill EducationStarting Out with Python (4th Edition)Computer ScienceISBN:9780134444321Author:Tony GaddisPublisher:PEARSONDigital Fundamentals (11th Edition)Computer ScienceISBN:9780132737968Author:Thomas L. FloydPublisher:PEARSON
- C How to Program (8th Edition)Computer ScienceISBN:9780133976892Author:Paul J. Deitel, Harvey DeitelPublisher:PEARSONDatabase Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781337627900Author:Carlos Coronel, Steven MorrisPublisher:Cengage LearningProgrammable Logic ControllersComputer ScienceISBN:9780073373843Author:Frank D. PetruzellaPublisher:McGraw-Hill Education