Part 1. 

Read “Appendix A: MS Project 2016”.

Part 2.

Research 3 other project management software tools (include at least 1 smartphone or tablet app).  You should find information about the pricing, features, and usage of each.

Part 3.

Answer the following questions in a short paper:

What functions and capabilities does project management software provide that you cannot do easily using other tools such as spreadsheets or databases? 
Do a comparative analysis of MS Project and the three other software tools you researched.  This can be in the form of an MS Word table, showing a comparison of each in terms of specific features, pricing, etc.  Or you can write a brief synopsis of each. 
How can organizations justify investing in enterprise or portfolio project management software?

 Your paper should be in APA format and 3-4 pages, not counting the title page and reference pages. Make sure you have in-text citations and a reference page.  

Building on the ERD developed in deliverable 1. The group will continue to work on the project and turn in the following files (with new requirements highlighted in bold text):

a) A MS Word document that contains the same five sections of the group proposal (no individual proposal needed). Include my feedback for deliverable 1 and highlight any changes made in response to the feedback. In Particular, section 5 should be expanded to include a list of 10 specific functionalities that you plan to implement using queries in Access (to be done in deliverable 3). Build your queries with the following specific requirements (Note that you may have the one query that satisfies multiple requirements below):

1) The 10 functionalities should cover all entities in your ERD (with the exception that only one of the subtypes needs to be covered). In other words, there should be no entities that are not involved in any proposed functionality.

2) At least 5 functionalities should involve more than one entity (Example: show the total number of the products supplied by customer X) – this functionality involves at least the Product and Customer entities).

3) At least 2 functionalities should involve more than two entities (Example: Show the most profitable product purchased by customers from Florida – this functionality involves at least the Orderline, Order, and Customer entities).

4) At least 5 functionalities should involve the use of mathematical functions such as Sum, AVG, Min, Max.  

5) At least 2 functionalities should involve the “Group By” feature (Example: Display the average prices of the products supplied by each vendor).

6) At least 1 functionality should involve the use of the Count feature (Example: Count the number of vendors who have supplied more than one product).

7) At least 1 functionality should involve the entity that exhibits the unary relationship (i.e. a self-join is needed). 

8) At least 1 functionality should involve both the supertype and one of the subtypes. 

After you list the 10 functionalities, indicate which of the above requirements each functionalitiy satisfies, e.g.: Find out the average number of products purchased by customers from Florida (#2, #4, #5). .

2)  An Access file that contains all the tables outlined in the ERD with both entity integrity (through PK) and referential entity (through FK) properly enforced in the relationship diagram. Each table should have at least 1 test record. When you insert these test records, make sure they are consistent with the business rules and constraints you specified in the proposal. For example, if you specified the “Price” attribute to be numeric and not null, then the column should not contain either a string value or a null value. Similarly, if you specified that “Each vendor must supply at least one product”, then you need to make sure that every vendor that appears in the Vendor table must show up at least once in the Product table. 

3) The Visio file that contains the updated ERD (include the Visio file even if no changes are made).

Final submission for this assignment will include a Word Doc (.docx), a Access file (.aacdb), and a Visio diagram (.vsdx).

Tasks to complete

Goal: This project will be used to integrate concepts developed from all the assignments in the second half of this class, specifically. You will identify a data driven business problem that requires preparation of the data. This preparation involves Extracting data (from 3 or more sources), Transforming (or cleaning) the data before Loading it into a database for analysis. In other words, you will experience, first-hand, the ETL process of Data management.

Options: You can take this project in one of two directions: (1) Identify a large file, clean the data and normalize it into three or more tables OR (2) Identify three or more large data sources, clean the data and merge them into a denormalized table for analysis. In both cases, you will need to identify what you plan to learn from the cleaned and loaded data.

Resource: This articleLinks to an external site.

In preparation for your project this term, I need you to do some digging to identify sources and ideas for a decent project.

There are a couple of decisions that have to be made. And so, I am making part of the project a “deliverable” so you can begin mulling over it. Most ETL tasks involve cleaning and integration. For integration, it is vital that you have an attribute that is common across all three data sets

Cleaning

Cleaning is one of the most important steps as it ensures the quality of the data in the data warehouse. Cleaning should perform basic data unification rules, such as:

• Making identifiers unique (sex categories Male/Female/Unknown, M/F/null, Man/Woman/Not Available are translated to standard Male/Female/Unknown)
• Convert null values into standardized Not Available/Not Provided value
• Convert phone numbers, ZIP codes to a standardized form
• Validate address fields, convert them into proper naming, e.g. Street/St/St./Str./Str
• Validate address fields against each other (State/Country, City/State, City/ZIP code, City/Street).

Transform

The transform step applies a set of rules to transform the data from the source to the target. This includes

• converting any measured data to the same dimension (i.e. conformed dimension) using the same units so that they can later be joined.
• generating surrogate keys or FKs so that you can join data from several sources,
• generating aggregates
• deriving new calculated values,
• Adding columns to create PKs and/or FKs

Data Integration

It is at this stage that you get the most value for the project. This typically means you are adding some attribute from a related set that adds ‘Color’ to the data. Perhaps Census data to labor data or other demographic data. The challenge is to locate data that are relatable.

Project direction: You will need to complete a datamart with significant pre-processing (ETL) activities. 

Requirements:

1. Problem being solved:  What do you propose to learn from this data? List several of these business questions and show how your project solution (data set) could answer them.
2. Tools: You must complete the entire project using Visual Studio. OR you can do this with some other tool of your choice (ETL) like Power BI or tableauLinks to an external site..
3. Volume: Total result data set must add up to at least 5k records, but not more than 100k.
4. Destination: SQL server table(s). Depending on the direction you are taking, you can move all the data to a single CSV file and dump it into SQL server at the end or direct the final destination tables to SQL server.
5. Transformation – it must include TWO new columns (for each final destination) that is populated by (a) the current date and time so you know when that data was brought into the final dataset and (b) a second one to know where the data came from (source file name). This may be done through SSIS or in SQL server.
   Note: Filename capturing works only when the source is a flat file.  So, if your source is NOT a flat file,  you may want to make a CSV file an intermediate destination and then use this file as the source (Hint: Use derived column transformation to add a column)
   In addition it must include at least 3 of the following transformations: data conversion, derived column, data split, lookup, merge, merge join, multicast, union all, fuzzy lookup or any of the transforms not covered in class. 

Data sources: You are welcome to use datasets from work that has been sufficiently “anonymizedLinks to an external site.“. In fact this itself is a valuable transformation task that you can then use to protect your data and make it available for additional analysis/exploration. There are many public data sets that can be used (see “data sources” tab)

Submit: Use the text area to submit a project “proposal” that addresses the following points; I am not looking for an elaborate write up, but use these 4 prompts to develop 4 well-written paragraphs free from language/grammar errors. Please do not write it in Q&A format!

1. Be sure to give a meaningful title. 
2. Motivation for the project: what insights do you anticipate getting from this ETL project? 
3. what problems do you anticipate during the ETL process?  Cleaning? Transforming? 
4. What data will you be using? where will you be getting these? How many rows would you be processing in all? What are the keys (Pk/fk)

   And finally, include

5. What type of decision support do you expect this project to provide? Would this have been possible with Excel? Why is this approach an improvement?
6. An ERD showing how the data sets are related to each other (either source or destination tables – see “options” above)
7. A subset of data from EACH file (5-10 rows and 5-10 columns) that shows the kind of data you are dealing with. For each file, be sure to identify what you would consider as a primary key. These can be included as a screenshot with column headers.
8. The datasets, properly named. Its best that you create a folder called {myGateID}_Project and save your datafiles there. ZIP this FOLDER and attach it

 Instructions

In order to complete Assignment #5 you will need to answer the below questions. Please complete the questions in a Word document and then upload the assignment for grading. When assigning a name to your document please use the following format (last name_Assignment #5). Use examples from the readings, lecture notes and outside research to support your answers. The assignment must be a minimum of 1-full page in length with a minimum of 2 outside sources. Please be sure to follow APA guidelines for citing and referencing sources. Assignments are due by 11:59 pm Eastern Time on Sunday.

1. Identify and explain the factors that have limited local law enforcement efforts against digital crime.

2. Explain and describe the best practices for collection, preservation, transportation, and storage of electronic evidence.

3. What is the importance of chain of custody as it relates to computer crime?