DATA MANAGEMENT

Theme

MLB expansion, looking for four youngest rookies to go to MLB

https://www.baseball-reference.com/teams/LAA/2021-pitching.shtml

Sample business report. A sample business report is provided at the end of the document. The business report should include the following:

a. Sample business report. The report body is the result of your SQL query. Format results in a professional business report format. Add a title, column headings, and some key selection criteria to the report.

b. A short narrative explaining how this report can be used to facilitate organizational operations or decision-making.

C. The SQL query that will return the information displayed in the report. You can use multiple SQL queries to generate information in one report.

d. Describes sample data for tables generated in logging reports.

e. Description of Data Source - Briefly explain the operational activity or external provider that generated the recorded data.

An example business report

Sample Business Report

Table 1. Example of Monthly Vendor Activity Summary Report

Vendor Activity Summary
For 1/1/2011 thru 1/31/2011
Vendor	# of Invoices	Total Amount	Average Invoice Amount	Unpaid Amount
American Standard Plumbing	3	$168,650.00	$56,216.67	$19,850.00
El's Pipe Supply	1	$16,000.00	$16,000.00	$16,000.00
Grainger Welding Equipment	1	$1,000.00	$1,000.00	$1,000.00
Horizon Tape	1	$2,582.50	$2,582.50	$0.00
Kitchen Sink Co.	2	$35,000.00	$17,500.00	$13,000.00
Office Max	2	$980.00	$490.00	$0.00
Skil Tools	2	$7,000.00	$3,500.00	$0.00
Totals	12	$231,212.50	$19,267.71	$49,850.00

A short narrative

The Vendor Activity summary is run at least monthly as part of the purchasing manager’s oversight activities. This manager is charged with ensuring that payments are appropriate and with optimizing purchasing efficiency by monitoring vendor activity. This oversight process begins by understanding the flow of purchases.

The report identifies total purchases (to identify key suppliers), number and average amount of invoices, and unpaid amounts to assess accounts payable. This information can be important for a variety of activities including:

· looking for price negotiation opportunities to reduce cost, e.g., identify the vendor from whom many purchases are made.

· identifying new or unusual patterns of activity that might reflect fraud or a need for oversight. For example, the purchasing manager can identify vendors with uncommon average invoice amounts and investigate those invoices.

SQL queries (You may need one or multiple queries to generate a business report)

The heading for this report includes a title and a date range. The regular monthly version uses the first and last day of the month, but reports can also be generated for other time frames. These dates are then embedded in the queries that produce the report.

The rows shown in the body of the report are extracted using this query:

SELECT AP_Vendors.Vendor_Name, Count(Invoices.Invoice_ID) AS CountOfInvoice_ID, Sum(Invoices.Inv_Ttl_Amt) AS SumOfInv_Ttl_Amt, Avg(Invoices.Inv_Ttl_Amt) AS AvgOfInv_Ttl_Amt, Sum([Inv_Ttl_Amt]-[Invoices].[Paid_Amt]) AS Amt_Unpaid

FROM AP_Vendors, Invoices

WHERE AP_Vendors.Vendor_ID = Invoices.Vendor_ID And Invoices.Entered_Date >=#1/1/2011# And Invoices.Entered_Date <=#1/31/2011#

GROUP BY AP_Vendors.Vendor_Name;

ORDER BY AP_Vendors.Vendor_Name;

The totals can be generated using this query:

SELECT Count(Invoices.Invoice_ID) AS CountOfInvoice_ID, Sum(Invoices.Inv_Ttl_Amt) AS SumOfInv_Ttl_Amt, Avg(Invoices.Inv_Ttl_Amt) AS AvgOfInv_Ttl_Amt, Sum([Inv_Ttl_Amt]-[Invoices].[Paid_Amt]) AS Amt_Unpaid

FROM Invoices

WHERE (((Invoices.Entered_Date)>=#1/1/2011# And (Invoices.Entered_Date)<=#1/31/2011#));

Example data for the tables used to generate the documented report

This report uses data from two tables. The AP_Vendors Table and the Invoices table.

Table 2. Example data from the AP_Vendors Table

AP_Vendors
Vendor_ID	Vendor_Name	Annual_Limit	Open_Payables	YTD_Total	Contact_Name
10001	SilphCo. Paper	$2,800	$250	$1,300	Steve Wilson
10002	El's Pipe Supply	$920,000	$16,000	$112,000	Rainn Novak
10003	Home Depot	$20,000	$800	$62,550	Angela Nunez
10013	Kinkos	$4,000	$23	$980	Creed Robinson
10014	Ricks Truck Rental	$22,000	$0	$18,000	Craig Liberstein
10015	Mill Supply	$500,000	$0	$0	John Calven

The AP_Vendors table lists vendors who are paid using the AP system. Its primary key is Vendor_ID. It is connected to a number of tables that have foreign key relationships that point to the Vendor_ID, as shown in Figure 1.

Table 3. Example data from the Invoices Table

Invoices
Invoice_ID	Vendor_ID	Inv_Ttl_Amt	Paid_Amt	Vend_Inv_No	Terms_ Code_ID	Invoice_Date	Received_Date	Entered_Date	Approved_Date	Paid_Date	Check_Num	Batch_ID
641	10007	$2,000.00	$2,000.00	35	3	12/1/2010	10/29/2010	10/30/2010	10/31/2010	11/2/2010	223871	1554
642	10014	$9,000.00	$9,000.00	2248	3	12/1/2010	10/31/2010	10/31/2010	10/31/2010	11/6/2010	223872	1554
643	10011	$15,450.00	$0.00	141	2	3/1/2011	11/5/2010	11/5/2010	11/8/2010		0	1555
644	10011	$40,000.00	$40,000.00	144	2	12/1/2010	11/5/2010	11/6/2010	11/8/2010	11/10/2010	223873	1555
645	10001	$300.00	$300.00	415	3	1/1/2011	11/5/2010	11/7/2010	11/9/2010	11/10/2010	223874	1555
665	10010	$3,900.00	$3,900.00	27	4	1/1/2011	12/15/2010	12/18/2010	12/20/2010	12/23/2010	223886	1562
667	10010	$250.00	$250.00	123	2	1/1/2011	12/12/2010	12/16/2010	12/20/2010	12/23/2010	223886	1562

The Invoices table (a transaction table) lists all invoices recorded in the AP system. Invoices are used to generate payments. Note that while each invoice has a unique Invoice_ID, each vendor may appear multiple times in the list. Invoices are paid by checks, posted to the general ledger in batches signaled through the Batch_ID field, and dates are recorded as the invoice moves through the invoicing process. Multiple invoices for the same vendor may be paid on a single check, as shown below for invoices 665 and 667.

A description of the data sources

The data from this report is generated by accounts payable activities.

· The Vendor table contains a list of all vendors to whom payments are made. Because it is important to control this list, organizational policy calls for AP manager approval of any new vendors before payments are made.

· The Invoices table lists the transactions summarized on the report. Invoices are entered into the system by AP clerks as bills are received. Invoices are approved by a second clerk after the request is reviewed. Several times a week, invoices are selected for payment and checks are run.

Notes for students:

· Note that key issues are briefly mentioned such as how the data are maintained (e.g., the policy for adding vendors), primary keys, and important cardinalities.

· Not all the rows in the tables are shown. But enough data is shown to demonstrate the cardinalities. For example, Vendor number 10011 appears multiple times in the list of invoices and several invoices share the same Terms_Code_ID.

· Other illustrative details can be observed in the provided data. For example, Invoice number 643 is not yet paid although it was approved for payment. And note that check number 223886 pays two invoices.

· Figures and tables are numbered and have captions. Make your reports look professional.

The database life cycle includes six phrases: database initial study, database design, implementation and loading, testing and evaluation, operation, maintenance and evolution. The first phrase of the initial study of the database is to understand the purpose of the entire database and all the information. By analyzing the company's situation, finding the problems and constraints, defining scope and boundaries to make an overview for the next phrase. In order to develop the solution, it is necessary to find the root cause of the problem in the initial phase and to have a strong logical understanding of the relationship between data. The second phase, database design, is undoubtedly one of the most important aspects that will support the company's operations and objects. Depending on the company's needs and data, different models will be selected for design. The third phrase is implementation and loading. After designing the database, it is natural to implement and input data. It is necessary to install DBMS to run and use virtualization to present the data in various forms, which is one of the most popular data presentations nowadays. Next is the testing and evaluation phase, where the designed database is used to check whether it needs to be implemented or added/reduced. This phrase also checks the security of the database, such as passwords, physical security, and evaluation of the backup model of the data. After that, it is operation, it means that the database, administration, users, and its application program are combined into a complete system, and this means that Many problems that did not occur in the testing stage start to occur, and this stage also requires constant checking. The final phrase is maintenance and evolution, where the system needs to be maintained for a long time, including its back up, recovery, security, and enhancing performance.

We have completed the first four phrases of our term project, and initially we selected the business problem to help the coach identify the best starting player for the Los Angeles baseball team for the upcoming season. Based on the project, we obtained the past data of the players of the team, including the number of games played, winning percentage, losing percentage, and batting average, etc. We designed the performance table with this data. In addition, there are players' rank, age and other information that can help us make more accurate conclusions when conducting business reports. Then we start to design the database, we first filter out the required data, then classify the data, use ERD to connect the correlation between entities, and finally create SQL to show the complete information system. After this phrase, we started implementation and loading, entering some of the data into the database, and then we performed testing and evaluation, running through the input data, checking the database design for logical errors, or for additions/deletions and making changes. and made changes. We have not started implementing the final operation, maintenance and evolution phases, which require time for operation and observation. In order to complete these two phases, we need to regularly upload new data to observe the operation of the database, and perform regular testing and maintenance of the database, such as backup and recovery functions.

In fact, the database life cycle is the management of database improvement. For example, in the testing and evaluation phase, it can be improved by finding a tune for performance and security constraints. If a test failure occurs, there should be a corresponding solution, so that once the same or similar problem occurs in the future life cycle, it can be solved quickly with the same solution. Moreover, both the physical design and the logical design need to be modified according to the update of data. In our project, identifying the best player for the next season is a matter of considering multiple dimensions to make the most equitable decision. In our database for the Los Angeles baseball team, the MLB Team table has foreign key relationships with Outfield player, Catcher, Pitcher, Infield player, and Potential Prospect tables. When we add new player IDs, coach IDs, etc. to the MLB Team table, it can be updated to the other tables simultaneously and give us the most up-to-date player projection data. The data of a team is very large and updated frequently according to the number of games, so it is important for us to ensure the life cycle by adding data smoothly and securely. By updating the data, coaches can continuously use the database to see the latest situation of the players, as well as to forecast the best players, the youngest rookie, etc. to be an aid to the team's victory.