Computer level three database design and application problem

A national transport company established a large-scale OLTP system and built a data warehouse on top of the system. The following data tables are available in OLTP systems and data warehouses:
Shipping Schedule (Shipping order ID, send station ID, final arrival ID, cargo ID, cargo weight, shipping price, date of shipment)
Summary table 1 (Send station ID, final arrival ID, cargo ID, Shipping date, gross weight, total tariff)
Summary table 2 (Send station ID, end to Region ID, cargo ID, Shipping date, total weight, total tariff)
Summary table 3 (Send station ID, final arrival ID, cargo ID, Shipping month, total weight, total tariff)
Summary table 4 (send region ID, end to Region ID, cargo category ID, shipping date, total weight, total tariff)
The business management of the cargo terminal about 100, the goods about 500 total 10 categories, the summary table has a master code, and the table has a reasonable maintenance strategy, the data can be consistent after each maintenance. With view V, the view has a high frequency of access, and its query result mode is (send region ID, final arrival ID, Shipping month, total weight, total freight), the view is now summarized in table 1 as the data source. The monitoring results show that the frequency of the aggregated table 1 is too high, and the overall performance of the system decreases, while the other summary tables are accessed less frequently. If you do not increase the summary tables and indexes, give an optimization scheme to improve the performance of the system service, and briefly explain the reasons.

Reference Answer:
Because summary table 1 and views have a high frequency of mode access, and the view's data source is from summary table 1, and because other summary tables are accessed less frequently, you only need to bind the view's data source to summary table 3, because summary table 3 also satisfies the output mode of the view. This not only improves the data access rate of the summary table 3, but also reduces the data access rate of the summary table 1, and the system performance and serviceability are greatly optimized. And because there are about 500 kinds of goods, a total of 10 categories, you can build a view binding data source for summary table 4, so that you can make full use of the data information of summary table 4, so as to further improve the performance of the system optimization.

There is a commodity table (product number, product name, unit price) and sales table (sales document number, product number, sales time, sales volume, sales unit price). Among them, the product number represents a class of goods, commodity number, unit price, sales volume and sales unit price are integral type.
Write a stored procedure that queries the gross gross margin of a product for a year, gross margin = sales quantity x (Sales unit price-unit price). The product number and year are required as input parameters, and gross hairs are returned using the output parameters.

Reference Answer:
CREATE PROCEDURE Product @ Product number int,@ year int,@ gross margin int output
As
Delcare
@ A product sales int,@ a commodity price int,@ a commodity sales unit price int
BEGIN
Select @ price of a product = Unit price from commodity table [email protected] Product Number = Product number
Select @ Commodity sales Unit Price = Sales price, @ a product sales =count ()
From sales form [email protected] Product Number = Product number and time of sale [email protected] Year
IF @ The price of an item is NULL then
ROLLBACK;
RETURN;
END IF;
IF @ price of a commodity isNULL then
ROLLBACK;
RETURN;
Rndif;
SET @ Gross margin = (@ price of a commodity [email protected]price of a commodity) @ Sales of a product
GO

Computer level three database design and application problem