Modern Database Management (12th Edition)
12th Edition
ISBN: 9780133544619
Author: Jeffrey A. Hoffer, Ramesh Venkataraman, Heikki Topi
Publisher: PEARSON
expand_more
expand_more
format_list_bulleted
Concept explainers
Videos
Textbook Question
Chapter 5, Problem 5.51PAE
Refer to Figure 4-5 0. For each of the following reports (with sample data), indicate any indexes that you feel would help the report run faster as well as the type of index:
a. State, by products (user-specified period)
State, by Products Report, January 1, 2018, to March 31, 2018
| State |
Product Description |
Total Quantity Ordered |
| CO |
8-Drawer Dresser |
1 |
| CO |
Entertainment Center |
0 |
| co |
Oak Computer Desk |
1 |
| co |
Writer’s Desk |
2 |
| NY |
Writer’s Desk |
1 |
| VA |
Writer’s Desk |
5 |
b. Most frequently sold product finish in a user-specified month
Most Frequently Sold Product Finish Report, March 1, 2018, to March 31, 2018
| Product Finish |
Units Sold |
| Cherry |
13 |
c. All orders placed last month
Monthly Order Report, March 1, 2018, to March 31, 2018
| Order Id |
Order Date |
Customer Id |
Customer Name |
| 19 |
3/5/18 |
4 |
Eastern Furniture |
Associated Order Details
| Product Description | Quantity Ordered | Price | Extended Price |
| Cherry End Table |
10 |
$75.00 |
$750.00 |
| High Back Leather Chair |
5 |
$362.00 |
$1,810.00 |
| Order Id | Order Date | Customer Ids | Customer Name |
| 24 |
3/10/18 |
1 |
Contemporary Casuals |
Associated Order Details
| Product Description | Quantity Ordered | Price | Extended Price |
| Bookcase |
4 |
$69.00 |
$276.00 |
d. Total products sold, by product line (user-specified period)
Products Sold by Product Line, March 1, 2018, to March 31, 2018
| Product Line | Quantity Sold |
| Basic |
200 |
| Antique |
15 |
| Modern |
10 |
| Classical |
75 |
Expert Solution & Answer
Want to see the full answer?
Check out a sample textbook solution
Students have asked these similar questions
1. Use the following table to answer the questions below:
Table name: Products
Column
Role
Data Type Length
Constraint
Name
Stores the ID of a product
Stores the name of a
product
P_entryDate Stores the entry date of a
| product
Stores the expiry date of a
product
P_ID
P_Name
Number
Varchar2
|Primary key
25
Date
Cannot be null
P_Expiry
Date
P_Expiry date
should be greater
than P_entryDate
|Cannot be null
P_Price
| Stores the price of a product | Number
3,2
2.
a. Write in SQL a command that creates the table Products according to the above description
b. Write in SQL a statement that adds to the table Products a new column named supplierlD of type Number(5).
This column should be defined as a foreign key that relates the table Products to the table Supplier
c. Write in SQL a query that lists all the details of products arranged in descending order according to the entry
date
a. Write a SQL query that displays the number of products that will expire in the year 2021|
e. Write in SQL a…
1. Use the following table to answer the questions below:
Table name: Products
Column
Data Type Length
Constraint
Role
Name
Stores the ID of a product
P ID
P_Name
Primary key
Number
Stores the name of a
Varchar2
25
product
P_entryDate Stores the entry date of a
product
Date
Cannot be null
P_Expiry
Stores the expiry date of a
product
Date
P_Еxpiry date
should be greater
than P_entryDate
P_Price
Stores the price of a product Number
3,2
Cannot be null
a. Write a SQL query that displays the mumber of products that will expire in the year 2021
b. Write in SQL a query that displays the ids of the most expensive products
c. Create a view named ProductDetails' to hold all the details of products whose names start with 'DELL' and
their prices are between 200 and 500 Omani Rials
Data normalisation
Chapter 5 Solutions
Modern Database Management (12th Edition)
Ch. 5 - Prob. 5.1RQCh. 5 - Prob. 5.2RQCh. 5 - Prob. 5.3RQCh. 5 - Prob. 5.4RQCh. 5 - Prob. 5.5RQCh. 5 - Prob. 5.6RQCh. 5 - Prob. 5.7RQCh. 5 - Prob. 5.8RQCh. 5 - Explain why you sometimes have to reserve much...Ch. 5 - Why are field values sometimes coded?
Ch. 5 - Prob. 5.11RQCh. 5 - Prob. 5.12RQCh. 5 - Explain why normalized relations may not comprise...Ch. 5 - Prob. 5.14RQCh. 5 - List three common situations that suggest that...Ch. 5 - Explain the reasons why some experts are against...Ch. 5 - Prob. 5.17RQCh. 5 - Prob. 5.18RQCh. 5 - Prob. 5.19RQCh. 5 - Prob. 5.20RQCh. 5 - Prob. 5.21RQCh. 5 - State nine rules of thumb for choosing indexes.Ch. 5 - One of the strongest recommendations regarding...Ch. 5 - Explain why an index is useful only if there is...Ch. 5 - Indexing can clearly be very beneficial. Why...Ch. 5 - Consider the following two relations for...Ch. 5 - Prob. 5.28PAECh. 5 - Prob. 5.29PAECh. 5 - Prob. 5.30PAECh. 5 - Prob. 5.31PAECh. 5 - Suppose you are designing a default value for the...Ch. 5 - When a student has not chosen a major at a...Ch. 5 - Prob. 5.34PAECh. 5 - Prob. 5.35PAECh. 5 - Consider the relations in Problem and Exercise...Ch. 5 - Prob. 5.37PAECh. 5 - Prob. 5.38PAECh. 5 - Prob. 5.39PAECh. 5 - Prob. 5.40PAECh. 5 - Prob. 5.41PAECh. 5 - Consider the relations specified in Problem and...Ch. 5 - Prob. 5.43PAECh. 5 - Prob. 5.44PAECh. 5 - Prob. 5.45PAECh. 5 - Prob. 5.46PAECh. 5 - Prob. 5.47PAECh. 5 - Problems and Exercises 8-65 through 8-68 refer to...Ch. 5 - Refer to the large Pine Valley Furniture Company...Ch. 5 - Problems and Exercises 8-65i5 through 8-68 refer...Ch. 5 - Refer to Figure 4-5 0. For each of the following...
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.Similar questions
- Modify the CHARTER table to add the attributes shown in the following table. TABLE P8.37 ATTRIBUTES AND VALUES FOR PROBLEM 37 ATTRIBUTE NAME ATTRIBUTE DESCRIPTION ATTRIBUTE TYPE CHAR_WAIT_CHG Waiting charge for each model (copied from the MODEL table) Numeric CHAR_FLT_CHG_HR Flight charge per mile for each model (copied from the MODEL table using the MOD_CHG_MILE attribute) Numeric CHAR_FLT_CHG Flight charge (calculated by CHAR_HOURS_FLOWN CHAR_FLT_CHG_HR) Numeric CHAR_TAX_CHG CHAR_FLT_CHG tax rate (8%) Numeric CHAR_TOT_CHG CHAR_FLT_CHG + CHAR_TAX_CHG Numeric CHAR_PYMT Amount paid by customer Numeric CHAR_BALANCE Balance remaining after payment Numericarrow_forwardEnsure that the only legal values for the BDRMS column in the CONDO_UNIT table are 1, 2, or 3.arrow_forwardCreate a table named REP_CONTRACTS containing the columns listed in the following chart A composite PRIMARY KEY constraint including the Rep_ID, Store_ID, and Quarter columns should be assigned. In addition, FOREIGN KEY constraints should be assigned to both the Rep_ID and Store_ID columns.arrow_forward
- An EMPLOYEES table was added to the JustLee Books database to track employee information. Display a list of each employee’s name, job title, and manager’s name. Use column aliases to clearly identify employee and manager name values. Include all employees in the list and sort by manager name.arrow_forwardDefine second normal form. What types of problems might you encounter using tables that are not in second normal form?arrow_forwardUpdate the DETAILRENTAL table to set the values in DETAIL_RETURNDATE to include a time component. Make each entry match the values shown in the following table. TABLE P8.53 UPDATES FOR THE DETAILRENTAL TABLE RENT_NUM VID_NUM DETAIL_RETURNDATE 1001 34342 02-MAR-18 10:00am 1001 61353 03-MAR-18 11:30am 1002 59237 04-MAR-18 03:30pm 1003 54325 09-MAR-18 04:00pm 1003 61369 09-MAR-18 04:00pm 1003 61388 09-MAR-18 04:00pm 1004 44392 07-MAR-18 09:00am 1004 34367 07-MAR-18 09:00am 1004 34341 07-MAR-18 09:00am 1005 34342 05-MAR-18 12:30pm 1005 44397 05-MAR-18 12:30pm 1006 34366 04-MAR-18 10:15pm 1006 61367 1007 34368 1008 34369 05-MAR-18 09:30pm 1009 54324 1001 34366 02-MAR-18 10:00amarrow_forward
- Product_id Product_name Category Mfg_date Exp_Dte Product_price 01 Dairy Milk Choclate Junk 2,Aug, 2019 2aug, 2020 80 02 Lipton Tea Bags Not junk 2,jan 2019 2, jan 2020 160 03 Kurkure junk 2, april 2919 2, april, 2021 30 04 Shezan Juice Junk 3,Aug, 2019 3,aug, 2020 30 06 Olpers Milk Not junk 3,april, 2018 3,april, 2020 350 Canteen_table 1: Write query to create canteen_table. Put check on category values as only “junk” and “Not junk”. Change colum name of product_price to unit_price. Display products manufactured between 1, jan 2019 to 1, May 2019. Display count of only junk food items in canteen_table with alias name as “junk Food Count”. Display Count Of both Junk food and not junk food products whose count is greater then 1.arrow_forward5. In the OrderItems table, display a count of the number of products where item_price is greater than or equal to $6, grouped by prod_id, but only for those groups having 3 or more of a given prod_id. Display the count with the alias shown. Sort by prod_id in ascending order. BRO3 1 rows returned in 0.03 seconds PROD ID Download 4 NUM PROD IDarrow_forwardUse the query box below to answer the questions, saving your query for each question. When you are done, submit your exam for grading. establishment license_no dba_name aka_name facility_type risk_level address city state zip latitude longitude employee employee_id- first_name last_name address city state zip phone title salary supervisor violation inspection_id point_id fine inspector_comment inspection inspection_id inspection_date inspection_type results employee_id license_no followup_to inspection_point point_id Description category code fine point_level List the inspections (inspection_id) with their date and type for all inspections that had a violation in the category of personnel' for January 2016. Also print the description of the inspection_point that was violated.arrow_forward
- Based on the ERD below, display all columns in the JOB table for those job descriptions that have both analysts and engineers within the description name. (Hint: You will use a “Like” phrase.)arrow_forwardHow is the order of characteristics in a result table altered?arrow_forwardPlease answer with detailed explanationarrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
Np Ms Office 365/Excel 2016 I NtermedComputer ScienceISBN:9781337508841Author:CareyPublisher:Cengage
Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781305627482Author:Carlos Coronel, Steven MorrisPublisher:Cengage Learning
Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781285196145Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos CoronelPublisher:Cengage Learning
A Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology Ptr
Np Ms Office 365/Excel 2016 I Ntermed
Computer Science
ISBN:9781337508841
Author:Carey
Publisher:Cengage
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
SQL Basics for Beginners | Learn SQL | SQL Tutorial for Beginners | Edureka; Author: edureka;https://www.youtube.com/watch?v=zbMHLJ0dY4w;License: Standard YouTube License, CC-BY