Managing Data includes classifying data, and creating a meaningful structure to store, manipulate and query it. In an earlier learning activity, your consulting group developed a cash flow projection for a snow removal company. In this Assignment, you will work with historical data about the clients, and the work done, and organize this data for future planning and projections.

You are being provided with 3 csv files:

• Customer information

• List of services available

• Work order

You are also being provided with example code. This example code has been edited to have key sections missing. These key sections have been replaced with a hint as to what is needed contained in square brackets [hint]. To use the example code, replace the hint (brackets and all) with the appropriate information.

1. Open and examine the provided csv files.

Add a column heading row with values.

Identify and/or create the Primary Key (pk) and Foreign Key (fk) fields for each of the three files.

Add a record/row to each file as follows:

• Customer Information: ([yourfirstname], [yourlastname], 237 Sait Road, 403-284-7248, aaastudent@csv.com)

• Service List: ([SID]Special, 2, $1000)

• Work order: (2020-12-31, [your service primary key], [your customer primary key]

2. Create the following directory structure using PowerShell

Company Name

Clients Folder

Individual client folders

Raw data

Work Order Folder

Monthly folders

Raw data

Example Code:

$path = [directory you wish to manipulate]

$csv = import-csv [path variable]\[name of file you wish to manipulate].csv

function createClientFolders{[csv variable] | foreach-object -process{[command for creating a new entity] -path [path variable] -name $_.[field you want to derive your folder names from] -itemtype [name of item type in ]} }

3. Move the 3 csv files to the appropriate locations (using PowerShell).

4. Produce the following reports from the data:

• Report 1: Work by date

Example Code:

$path = [directory you wish to manipulate]

$csv = import-csv [path variable]\[name of file you wish to manipulate].csv

[csv variable]|sort-object [name of field you wish to sort by]|[output parameters]

• Report 2: Work by service, grouped by Customer

Example Code:

$path = [directory you wish to manipulate]

$csv = import-csv [path variable]\[name of file you wish to manipulate].csv

[csv variable]|sort-object [name of field you wish to sort by]|group-object [name of field you wish to group by]|[output parameters]

• Report 3: Work by month for the top 10 clients

Page Break

Submission/Grading guide:

Personalize your PowerShell prompt using the prompt function.

If the application required to execute a task is not specified, you can choose any approach that works.

Screen snip showing the PowerShell command(s) used to create your directory structure.
Screen snip showing the PowerShell command(s) used to create the individual client folders
Screen snip showing the File Manager view of your directory structure
Screen snips showing how you added the header row to the csv files
What is the Primary Key field on your Clients Table?
What is the Primary Key field on your Services?
What is the Primary Key field on your Work Order Table?
Which table(s) has/have Foreign Keys?
Screen snip showing the PowerShell command(s) used to create Report 1.
Screen snip showing the PowerShell command(s) used to create Report 2.
Screen snip showing the PowerShell command(s) used to create Report 3.