POS 410 Week 5 Learning Team: SQL Account Database Project

0 items
POS 410 Week 5 Learning Team: SQL Account Database Project
POS 410 Week 5 Learning Team: SQL Account Database Project
$9.99
Go to the store page
  • Description

POS 410 Week 5 Learning Team: SQL Account Database Project

Complete and submit work on Service Request SR-kf-009, “SQL Accounting Database.”

Click the Assignment Files tab to submit your assignment.

Below is a quick summary of the items that should be included in your learning team assignment according to the syllabus.  I’ve also included the rubric I use to grade the project and a sample of what the report might look like.  I’ve put a few additional notes on items 3 thru 5 in blue below, as those relate to queries that need to be provided.

  1. Summary of your team’s observationsof the “Charts of Accounts” data including recommendations on normalizing the data as well as rationale for any keys and indexes chosen. Breaking the data into more than one table is not required for this report, however your submission should include recommendations about how the data could be stored more effectively for queries outside of the report defined in SR-kf-009. Pay close attention to the data itself and consider how it should be stored versus how it’s stored in the spreadsheet today.

  2. Database diagram

  3. SQL statements used for creating database table(s) and any related keys and/or indexes used. These will be your create table statements for the tables you have chosen.

  4. SQL statements used to load test dataThis could be a bulk insert statement, a series of manual INSERT statements, or some screenshots of how you used SQL Server Integration Services (SSIS) to import the data.  The method you use is up to your team.

  5. SQL statement(s) used to query data for reportYour team has some flexibility in this area.  In some cases, I have teams that use SQL Server Reporting Services to produce the report, others use a single query, and still others use multiple queries.  I would estimate that roughly half of the teams I have taught use more than one query.  In all cases, I’ve seen a lot of variances in how teams choose to implement the report.  Let me provide some suggestions, particularly if you choose to accomplish with one query, which is definitely possible.  First, research the substring or left functions.  Either of these can help you pull the first two digits of the account number as described in the requirement.  If you GROUP BY the first two digits, that is one way of handling the “break based on the first two digits” requirement mentioned.  Secondly, research the COMPUTE function.  You can use this in conjunction with SUM to create your subtotals and total.

  6. Report results

Example of using the ROLLUP and SUM functions for subtotals and a grand total:

SELECT Country, State, SUM(order total)

FROM Sales.SalesOrderHeader

GROUP BY Country, State

WITH ROLLUP