Structured Query Language Skills, Essay Example

Our development team is sharpening their SQL skills. We need to create the SQL select statements that would produce running summary files for reports on customer volume by month the details of which include customer ID, month, total shipments, and total weight. Furthermore, we need to generate a report to compile driver performance by month the details of which will include driver ID, month, and number of manifests on time, and number of manifests delivered late.

As such, we are faced with certain questions. From where should we source the information for the design? Our transaction processing database will serve as the repository of information we will use to collate the necessary information for each report. Nevertheless, some information will best be generated “on-the-fly” – as the report is run (Date, 1999).  For example

What difficulties will we encounter writing the SQL statements for each of these tables? One of the primary challenges the team faces is constructing the SQL statements in appropriate syntax. It is one thing to develop a psuedocode algorithm of the necessary logic (Freetutes.com, 2011). It is quite another to translate the algorithm in a way such that the SQL will report what we wish it to. One case-in-point is how we will instruct the SQL to generate a count of certain fields such as number of manifests or total weight.

Should any of these tables be created on an as-needed basis? It seems both tables should be created on an as-needed basis because many of the outputs will change chronologically. For example, the total shipments for a given customer will probably from June 1st to June 30^th and from June 15^th to July 15^th. So, to make sure the calculations are run each time the report is run the tables will be created as-needed.

Do these summary tables violate the concept of table normalization? They must violate certain rules, because each table is generated with only the data necessary to complete the report. That means each table will be unique and contain information that is only applicable to one record (Kent, 1983). These were some questions we faced as a result of executing the requirments of the design.

The resultant tables and SQL statements follow:

• Select Orders.Customerid, Sum(Orders.Weight) As Sumofweight, Orders.Date, Count(Shipments.Shipmentid) As Countofshipmentid
• From Orders, Shipments
• Group By Orders.Customerid, Orders.Date;
• Select Count(Shipments.Shipmentid) As Countofshipmentid, Shipments.Driver, Shipments.Ata, Sum(Shipments.Weight) As Sumofweight
• From Orders, Shipments
• Group By Shipments.Driver, Shipments.Ata
• Having (((Shipments.Ata)<[Shipments]![Eta]));

Our development team has been sharpening their SQL skills with the following task. We needed to create the SQL select statements that would produce running summary files for reports on customer volume by month the details of which include the customer ID, the month, the total shipments, and the total weight. Furthermore, we need to generate a report to compile driver performance by month the details of which will include the driver ID, the month, and the number of manifests on time, and the number of manifests delivered. As a result of executing the requirements of the design, we answered a number of critical questions about the source of the data, problems that may be encountered, whether the tables would be created as-needed, and whether they violate the rules of normalization. Furthermore, examples, sample tables and SQL logic were provided in the explanations.

References

Date, C. J. (1999). An Introduction to Database Systems (8th ed.). Addison-Wesley Longman.

Freetutes.com (2011). Systems analysis and design: Complete introductory tutorial for software engineering. Freetutes.com. Retrieved from http://www.freetutes.com/systemanalysis/

Kent, W. (1983). A Simple Guide to Five Normal Forms in Relational Database Theory, Communications of the ACM, 26: 120–125.