In this assignment, you will design the tables to hold data in the CSVs, import the CSVs into a SQL database, and answer questions about the data. In other words, you will perform:

1. Data Engineering
2. Data Analysis

Note: You may hear the term “Data Modeling” in place of “Data Engineering,” but they are the same terms. Data Engineering is the more modern wording instead of Data Modeling.

Data Modeling

Inspect the CSVs and sketch out an ERD of the tables. Feel free to use a tool like http://www.quickdatabasediagrams.com.

Data Engineering

• Use the information you have to create a table schema for each of the six CSV files. Remember to specify data types, primary keys, foreign keys, and other constraints.
  • For the primary keys check to see if the column is unique, otherwise create a composite key. Which takes to primary keys in order to uniquely identify a row.
  • Be sure to create tables in the correct order to handle foreign keys.
• Import each CSV file into the corresponding SQL table. Note be sure to import the data in the same order that the tables were created and account for the headers when importing to avoid errors.

Data Analysis

Once you have a complete database, do the following:

1. List the following details of each employee: employee number, last name, first name, sex, and salary.
2. List first name, last name, and hire date for employees who were hired in 1986.
3. List the manager of each department with the following information: department number, department name, the manager’s employee number, last name, first name.
4. List the department of each employee with the following information: employee number, last name, first name, and department name.
5. List first name, last name, and sex for employees whose first name is “Hercules” and last names begin with “B.”
6. List all employees in the Sales department, including their employee number, last name, first name, and department name.
7. List all employees in the Sales and Development departments, including their employee number, last name, first name, and department name.
8. In descending order, list the frequency count of employee last names, i.e., how many employees share each last name.

Bonus (Optional)

As you examine the data, you are overcome with a creeping suspicion that the dataset is fake. You surmise that your boss handed you spurious data in order to test the data engineering skills of a new employee. To confirm your hunch, you decide to take the following steps to generate a visualization of the data, with which you will confront your boss:

1. Import the SQL database into Pandas. (Yes, you could read the CSVs directly in Pandas, but you are, after all, trying to prove your technical mettle.) This step may require some research. Feel free to use the code below to get started. Be sure to make any necessary modifications for your username, password, host, port, and database name:
   from sqlalchemy import create_engine engine = create_engine(‘postgresql://localhost:5432/<your_db_name>’) connection = engine.connect()

• Consult SQLAlchemy documentation for more information.
• If using a password, do not upload your password to your GitHub repository. See https://www.youtube.com/watch?v=2uaTPmNvH0I and https://help.github.com/en/github/using-git/ignoring-files for more information.

1. Create a histogram to visualize the most common salary ranges for employees.
2. Create a bar chart of average salary by title.

Epilogue

Evidence in hand, you march into your boss’s office and present the visualization. With a sly grin, your boss thanks you for your work. On your way out of the office, you hear the words, “Search your ID number.” You look down at your badge to see that your employee ID number is 499942.

Submission

• Create an image file of your ERD.
• Create a .sql file of your table schemata.
• Create a .sql file of your queries.
• (Optional) Create a Jupyter Notebook of the bonus analysis.

  Using the Microsoft Visio schema and excel files attached. Answer the following questions:

• Which schools generate the highest amount of debt for the average student who attends?
• What is the employment rate for the students who graduate from this school?

Revise the Database Schema in the attached Visio File based on the excel files attached and the questions above if any data was missed, add it to the schema.

Test your schema by completing the following tasks:

• Define the database tables you will need and group the tables by function. Document your tables in an annotated Microsoft® Visio® diagram.
• Develop the SQL queries needed and populate the tables from the attached excel files.
• Define an appropriate strategy to optimize and incorporate best practices in to the SQL that you created.
• Document your SQL and metadata in Microsoft® Visio® 
• Capture screenshots of each query result.

Document your results as a 1-page Microsoft®Word document that:

• Describes your SQL, populated tables, and the best practices you used to develop your SQL queries
• Includes the SQL statements and corresponding screenshots of each query result

 Compress your revised diagram(s), testing results documentation and SQL files into a ZIP file. 

 Answer all the following Questions in one MS word document:  

 Robotics, Social Networks, AI and IoT

1. Some say that analytics in general dehumanize managerial activities, and others say they do not. Discuss arguments for both points of view. 

3. What are some of the major privacy concerns in employing intelligent systems on mobile data? 

4. Identify some cases of violations of user privacy from current literature and their impact on data science as a profession.

5. Search the Internet to find examples of how intelligent systems can facilitate activities such as empowerment, mass customization, and teamwork. 

Must add APA formatted references.  Do not use direct quotes, rather rephrase the author’s words and continue to use in-text citations.  

After completing the reading this week, we reflect on a few key concepts this week:

1. Discuss what performance management is and how it influences effective teams.
2. Review table 11.1, define leadership behaviors (in your own words) and note which behaviors are beneficial at specific organizational activities (example: project planning, leading coworkers, etc…).  Please note at least five organizational activities and be specific when responding.
3. Note at least two organizational capabilities and compare and contrast each.

Text Book : 

https://books.google.com/books?id=Pkm3DwAAQBAJ&pg=PT105&source=gbs_selected_pages&cad=2#v=onepage&q&f=true 

Please be sure to answer all the questions above in the initial post.

Please ensure the initial post and two response posts are substantive.  Substantive posts will do at least TWO of the following:

• Ask an interesting, thoughtful question pertaining to the topic
• Expand on the topic, by adding additional thoughtful information
• Answer a question posted by another student in detail
• Share an applicable personal experience
• Provide an outside source
• Make an argument

At least one scholarly (peer-reviewed) resource should be used in the initial discussion thread.  Please ensure to use information from your readings and other sources from the UC Library.  Use APA references and in-text citations.

Complete the following assignment in one MS word document:

Chapter 8 –discussion question #1-4 & exercise 4

Chapter 9- discussion question #1-5 & exercise 3

When submitting work, be sure to include an APA cover page and include at least two APA formatted references (and APA in-text citations) to support the work this week.

All work must be original (not copied from any source)

Chapter 8: Discussion question 1-4

1. How does prescriptive analytics relate to descriptive and predictive analytics?

2. Explain the differences between static and dynamic models. How can one evolve into the other?

3. What is the difference between an optimistic approach and a pessimistic approach to decision making under assumed uncertainty?

4. Explain why solving problems under uncertainty sometimes involves assuming that the problem is to be solved under conditions of risk.

Exercise 4

Investigate via a Web search how models and their solutions are used by the U.S. Department of Homeland Security in the “war against terrorism.” Also investigate how other governments or government agencies are using models in their missions.

Chapter 9: Discussion question 1-5

1. What is Big Data? Why is it important? Where does Big Data come from?

2. What do you think the future of Big Data will be? Will it lose its popularity to something else? If so, what will it be?

3. What is Big Data analytics? How does it differ from regular analytics?

4. What are the critical success factors for Big Data analytics?

5. What are the big challenges that one should be mindful of when considering implementation of Big Data analytics?

Exercise 3

At teradatauniversitynetwork.com, go to the Sports Analytics page. Find applications of Big Data in sports. Summarize your findings.