What is a lock, and how, in general, does it work?
Respond to the following chapter 10 review questions (you may have discussed some of these questions in class so be sure to include ideas and information you gained from the class discussion).
(2 pts) List and discuss the five transaction properties.
What is a lock, and how, in general, does it work?
What are the different levels of lock granularity?
What is a deadlock? Discuss three specific strategies for dealing/avoiding deadlocks.
Chapter 10 PROBLEMS (25 points)
See the text for details for each question.
(5 pts) Suppose you are a manufacturer of product ABC, which is composed of parts A, B, and C. Each time a new product is created, it must be added to the product inventory, using the PROD_QOH in a table named PRODUCT. And each time the product ABC is created, the parts inventory, using PART_QOH in a table named PART, must be reduced by one each of parts A, B, and C.
The sample database contents are shown in Table P10.1
You may wish to create a simple database with the two tables in this problem and actually write and test the SQL statements in part b and c. to ensure they are correct.
a. How many database requests can you identify for an inventory update that involves both PRODUCT and PART tables?
b. Using SQL, write each database request you identified in step a.
c. Using what you created in step b above, add the SQL statements to make them a single and complete transaction(s).
d. Write the transaction log, using Table 10.1 as your template.
e. Using the transaction log created in Step d, trace its use in database recovery. Assume the database experienced a crash just before the final commit was executed. What are the TRL ID (from step d) that must be executed and what specific action must be done to reverse the transaction and return the database to a stable state before the transaction started. The truncations should be reverse order and the action should be specific.
(2 pts) Describe the three most common problems with concurrent transaction execution. Explain how concurrency control can be used to avoid those problems.
(2 pts) What DBMS component is responsible for concurrency control? How is this feature used to resolve conflicts?
(3 pts) Suppose that your database system has failed. Describe the database recovery process and the use of deferred-write and write-through techniques.
Create a ch10_abc_markets database and use the Ch10_ABC_Markets_MySQL file to create and load the tables for use in problems 5-10. Be sure to set ch10_abc_markets as the default schema.
(2 pts) ABC Markets sell products to customers. The relational diagram shown in Figure P10.6 represents the main entities for ABC’s database. Note the following important characteristics: (See the textbook for important characteristics).
Using this database, write the SQL code to represent each one of the following transactions. Make sure to use BEGIN TRANSACTION and COMMIT to group the SQL statements in logical transactions.
On April 15, 2018, customer ‘10011’ makes a credit purchase (30 days) of two unit of product ’14-Q1/L3’ with a unit price of $17.49; the tax rate is 10 percent. The invoice number is 1010, and this invoice has only one product line. (NOTE: Be sure to calculate the full invoice value (rounded up) with quantity and tax, but add the extended unit price for the line item).
On May 10, 2018, customer ‘10011’ makes a payment of $38.48 in cash. The payment ID is 1012. (Note: Be sure to update the Invoice status to PAID.)
(3 pts) Create a simple transaction log (using the format shown in Table 10.16) to represent the actions of the two previous transactions. (NOTE: There will be two truncations, so be sure to use different TRX NUM values)
(2 pts) Assuming that pessimistic locking is being used, but the two-phase locking protocol is not, create a chronological list of the locking, unlocking, and data manipulation activities that would occur during the complete processing of the transaction described in Problem 5a.
(2 pts) Assuming that pessimistic locking with the two-phase locking protocol is being used, create a chronological list of the locking, unlocking, and data manipulation activities that would occur during the complete processing of the transaction described in Problem 5a.
(2 pts) Assuming that pessimistic locking is being used, but the two-phase locking protocol is not, create a chronological list of the locking, unlocking, and data manipulation activities that would occur during the complete processing of the transaction described in Problem 5b.
(2 pts) Assuming that pessimistic locking with the two-phase locking protocol is being used, create a chronological list of the locking, unlocking, and data manipulation activities that would occur during the complete processing of the transaction described in Problem 5b.
Chapter 13 REVIEW (5 points)
(2 pts) List the seven things that a BI framework provides.
According to the textbook what are the three most relevant differences between operational and decision support data? Identify each one and explain each one in your own words.
What is a data warehouse, and what are its main characteristics? How does it differ from a data mart?
Explain the use of facts, dimensions, and attributes in the star schema.
Chapter 13 PROBLEMS (25 points)
Create a ch13_saleco_dw database and use the CH13_SaleCo-DW_MySQL file to create and load the tables for use in the following problems. Be sure to set ch13_saleco_dw as the default schema. Copy & paste the image from your MySQL window for each problem.
After developing the correct SQL statement to properly solve the problem statement, get a screenshot that contains ALL of the following:
(3 pts) Using Group By with Rollup, write & run the SQL command to list the total sales by customer and by product, with subtotals by customer and a grand total for all product sales. Scroll the results to the bottom to show the GRAND TOTAL and the PREVIOUS SUB-TOTAL. Use the image below as a reference for a correct solution.
(3 pts) Using Group By with Rollup, write & run the SQL command to list the total sales by customer, month and product, with subtotals by customer and by month and a grand total for all product sales. Scroll the results to the bottom to show the GRAND TOTAL and the PREVIOUS SUB-TOTAL. Use the image below as a reference for a correct solution.
(3 pts) Using Group By with Rollup, write & run the SQL command to list the total sales by region and customer, with subtotals by region and a grand total for all sales. Scroll the results to the bottom to show the GRAND TOTAL and the PREVIOUS SUB-TOTAL. Use the image below as a reference for a correct solution.
(2 pts) Using Group By with Rollup, write & run the SQL command to list the total sales by month and product category, with subtotals by month and a grand total for all sales. Scroll the results to the bottom to show the GRAND TOTAL and the PREVIOUS SUB-TOTAL. Use the image below as a reference for a correct solution.
(2 pts) Using Group By with Rollup, write & run the SQL command to list the number of product sales (number of rows) and total sales by month, with subtotals by month and a grand total for all sales. Scroll the results to the bottom to show the GRAND TOTAL and the PREVIOUS SUB-TOTAL. Use the image below as a reference for a correct solution.
(2 pts) Using Group By with Rollup, write & run the SQL command to list the number of product sales (number of rows) and total sales by month and product category with subtotals by month and product category and a grand total for all sales. Scroll the results to the bottom to show the GRAND TOTAL and the PREVIOUS SUB-TOTAL. Use the image below as a reference for a correct solution.
(2 pts) Using Group By with Rollup, write & run the SQL command to list the number of product sales (number of rows) and total sales by month, product category and product, with subtotals by month and product category and a grand total for all sales. Scroll the results to the bottom to show the GRAND TOTAL and the PREVIOUS SUB-TOTAL. Use the image below as a reference for a correct solution.
Using the following ETL process, denormalize and extract the data from the Ch13_Saleco_DW database and place the data into an Excel spreadsheet.
Run the following query in MySQL Workbench to denormalize that data into a single result set.
SELECT P_CODE, P_DESCRIPT, P_CATEGORY, V_NAME, V_AREACODE, V_STATE, CUS_CODE, CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_STATE, REG_NAME, TM_YEAR, TM_MONTH, TM_DAY, TM_QTR, SALE_UNITS, SALE_PRICE, (SALE_UNITS * SALE_PRICE) AS SALE_TOTAL
FROM DWDAYSALESFACT JOIN DWTIME USING (TM_ID)
JOIN DWCUSTOMER USING (CUS_CODE)
JOIN DWREGION USING (REG_ID)
JOIN DWPRODUCT USING (P_CODE)
JOIN DWVENDOR USING (V_CODE);
Click on the “Export” icon above the result set and save the data as an Excel Spreadsheet XML file.
Start Excel and open the XML file exported in step 2.
From the Home ribbon, click on the “Format as Table” icon. Select an appropriate table design, ensure the entire dataset is selected and the “My table has headers” is checked and click OK.
(2 pts) Create a pivot table that has the same data analysis information as the data result set from Problem 1.
From the excel table, go to the Insert ribbon and click on Pivot Table.
Select the table (likely Table1) and select “New Worksheet” and click OK.
Move P_CODE to the Columns section and move CUS_CODE to the Rows section.
Move SALE_TOTAL to the Values section.
Ensure the Grand Total along right side (the total purchase by customer) matches the rollup results from problem 1.
Copy and Paste an image of the pivot table results below.
(2 pts) Create a pivot table that has the same data analysis information as Problem 2, but substitute the customer’s last name for the customer code. (HINT: add both the CUS_LNAME and TM_MONTH to the Rows section). Copy and Paste an image of the pivot table results below.
(2 pts) Create a pivot table that has the same data analysis information as Problem 6. (HINT: To display the number of sales, add the SALES_TOTAL twice but change the Value Field Settings from Sum to Count) Rename the column headers to “# of Sales” and “Total Sales” (this can be done the Value Field Settings). Copy and Paste an image of the pivot table results below.
(2 pts) Assume you are the sales manager from SaleCo and you are doing your own sales analysis. What information do you feel would be important to view and analyze? Create a pivot table (different from any of the previous pivot tables) that will allow you to analyze that important information. 1) Copy and Paste an image of the pivot table results below and 2) write a two or three sentence explanation of the type of information this pivot table could be used to analyze and why that information is important.
Collepals.com Plagiarism Free Papers
Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.
Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS
Why Hire Collepals.com writers to do your paper?
Quality- We are experienced and have access to ample research materials.
We write plagiarism Free Content
Confidential- We never share or sell your personal information to third parties.
Support-Chat with us today! We are always waiting to answer all your questions.