Adventure Works Cycles Company Story

Business

Adventure Works Cycles, the fictitious company on which the AdventureWorks sample databases are based, is a large, multinational manufacturing company. The company manufactures and sells metal and composite bicycles to North American, European, and Asian commercial markets. While its base operation is located in Bothell, Washington, with 290 employees, several regional sales teams are located throughout its market base.

In 2000, Adventure Works Cycles bought a small manufacturing plant, Importadores Neptuno, located in Mexico. Importadores Neptuno manufactures several critical subcomponents for the Adventure Works Cycles product line. These subcomponents are shipped to the Bothell location for final product assembly. In 2001, Importadores Neptuno, became the sole manufacturer and distributor of the touring bicycle product group.

Coming off a successful fiscal year, Adventure Works Cycles is looking to broaden its market share by targeting its sales to its best customers, extending its product availability through an external website, and reducing cost of sales through lower production costs.

Product Overview

As a bicycle manufacturing company, Adventure Works Cycles has four product lines:

· Bicycles that are manufactured at the Adventure Works Cycles company.

· Bicycle components that are replacement parts, such as wheels, pedals, or brake assemblies.

· Bicycle apparel that is purchased from vendors for resale to Adventure Works Cycles customers.

· Bicycle accessories that are purchased from vendors for resale to Adventure Works Cycles customers.

Manufacturing Overview

· Bill of materials: List of the products used or contained in another product.

· Work orders: Manufacturing orders by work center.

· Locations: Major manufacturing and inventory areas, such as frame forming, paint, subassembly, and so on.

· Manufacturing and product assembly instructions by work center.

· Product inventory: The physical location of a product in the warehouse or manufacturing area, and the quantity available in that area.

· Engineering documentation: Technical specifications and maintenance documentation for bicycles or bicycle components.

Assignment 1

The purpose of this assignment is to practice navigating and identifying database tables and related fields.

For this assignment, assume you have just been hired by Adventure Works Cycles company. As part of the orientation process, you have been asked to learn a little about the company by reading the “Adventure Works Cycles Company Story.”

Your supervisor also wants you to gain an understanding of the content included in the company database. To do this, you must utilize SELECT and FROM statements like the example provided below.

EXAMPLE: SELECT * FROM [Database].[Schema_Name].[Table_Name]

Access the “AdventureWorks 2016 and Scripts for SQL Server 2016 CTP3” database and use SELECT and FROM statements to find the answer to each of the questions below. Create a Word document that includes the SQL queries used to explore the database tables and answer the following questions using the queries completed in steps 1-5.

Please note that when SQL queries are run, results are generated in the form of data. This data should be exported and saved to an Excel file for a visual check of accuracy.

1. Locate the “Person” table and run a basic SELECT query as listed in the example. List all the available fields and how many records exist in that table.

2. On what table and schema would you locate an applicant’s resume data?

3. When is the CEO’s Birthday?

4. What is the list “ListPrice” of the product “HL Touring Seat Assembly?”

5. Is “Holiday Skate & Cycle” a preferred vendor?

Compile the Excel data file and Word document containing the SQL queries and answers to the questions into a .zip file and submit to your instructor.

APA style is not required, but solid academic writing is expected.

This assignment uses a rubric. Please review the rubric prior to beginning the assignment to become familiar with the expectations for successful completion.

You are not required to submit this assignment to Turnitin.

For part 2 of assignment 1

Proposal for Process Improvement

Requestor:

Business Problem:

Proposed Solution:

Required Resources:

1.

1.

Implementation Steps:

1.

1.

Benefits:

1.

1.

Potential Obstacles:

1.

1.

1. The purpose of this assignment is to practice making proposals to communicate database needs to relevant stakeholders.

1. For this assignment, assume you work at Adventure Works Cycles. Your manager recently informed you that by law, all employees must have an emergency phone number on file. This information is not currently included in the company database, so you must submit a proposal to the IT Department detailing your request to have it added.

1. Using the “Proposal for Process Improvement” template, construct a Word document that outlines the requester, business problem, proposed solution, resources, implementation steps, benefits, and potential obstacles of the request.

1. APA style is not required, but solid academic writing is expected.

1. This assignment uses a rubric. Please review the rubric prior to beginning the assignment to become familiar with the expectations for successful completion.

1. You are not required to submit this assignment to Turnitin.

Assignment 2

The purpose of this assignment is to analyze the structure of a relational database and demonstrate the ability to correctly document and explain additions to the structure of a relational database.

Part 1:

For this part of the assignment, you will use the “AdventureWorks 2014 OLTP Schema.” Open the schema and follow the tree/chain to answer the questions below in a Word document.

1. How does the Sales.SalesTerritory table relate to the Person.StateProvince table? Which table holds the Primary and the Foreign Key? On what field do they join together?

2. If you wanted to know the Planned Cost of a Product, what route would you take to the data? Identify the tables and fields used to join on each.

3. Imagine a bonus was paid to an employee using a different currency. Identify the fields you would need so you could access the information. List all the tables involved and the fields used to join.

Part 2:

For this part of the assignment, you will continue to assume you work at Adventure Works Cycles company. In the Topic 1 assignment, you made a request to add a field to the company database. That request has been approved, and now the database diagram needs to be accurately updated.

Create a simple OpenOffice Draw document demonstrating how your new table will connect to the Human Resources.Person table on the diagram. Since this is a new table, add new fields to create the proper relationships as you see fit.

In a one or two paragraph Word document, explain how these fields relate to the rest of the database content.

Compile the OpenOffice Draw file and Word document into a .zip file and submit to your instructor.

APA style is not required, but solid academic writing is expected.

This assignment uses a rubric. Please review the rubric prior to beginning the assignment to become familiar with the expectations for successful completion.

You are not required to submit this assignment to Turnitin.

Assignment 3

The purpose of this assignment is to identify business problems that can be addressed through analyzing database content.

For this assignment, assume the role of a data analyst at Adventure Works Cycles company. Your manager recently approached you with a problem. The company is losing money on its popular model “LL Road Frame-Black 60” but cannot determine why sales are down. You have been tasked to research potential reasons why this product, which your manager believes is solid, is not selling.

In order to address this business problem, you must deconstruct it. Study the entity relationship diagram (ERD) titled “AdventureWorks 2014 OLTP Schema,” and use it determine the specific questions that must be asked and answered to address the problem.

In a 250-word document, address the following. Include basic information related to the problem along with specific information about the tables that should be researched.

1. Explain what methods will be used to set the parameters for the query.

2. Define the specific questions that need to be asked and answered in order to address the specified business problem.

3. Explain how the entity relationship diagram (ERD) was used in addressing questions 1-2 above.

4. Describe ethical dilemmas that could be encountered as a result of the research being done to address the business problem.

5. Explain what resources you will use to assist with addressing ethical dilemmas.

APA style is not required, but solid academic writing is expected.

This assignment uses a rubric. Please review the rubric prior to beginning the assignment to become familiar with the expectations for successful completion.

You are not required to submit this assignment to Turnitin.

Assignment 4

The purpose of this assignment is to design and implement queries that will assist in developing answers to business problems.

For this assignment, continue to operate in the role of a data analyst at Adventure Works Cycles company. Based upon “LL Road Frame-Black 60” scenario and questions formulated for the Topic 3 assignment, write queries for the “AdventureWorks 2016” database using SQL Server 2016 Developer Edition.

Please note that when SQL queries are run, results are generated in the form of data. This data should be exported and saved to Excel for a visual check of accuracy.

Create a Word document that includes the SQL queries used to explore the database tables, and answer the following questions.

1. Find the product ID for the LL Road Frame – Black 60.

2. Find the listing price of the LL Road Frame – Black 60.

3. How would you rewrite the query used in question 2 to exclude NULL values?

4. How many orders have been placed for LL Road Frame – Black 60?

5. Rename the OrderQty to Quantity in your results.

Compile the Excel data file and Word document containing the SQL queries and answers to the questions into a .zip file and submit to your instructor.

APA style is not required, but solid academic writing is expected.

This assignment uses a rubric. Please review the rubric prior to beginning the assignment to become familiar with the expectations for successful completion.

You are not required to submit this assignment to Turnitin.

Assignment 5

The purpose of this assignment is to design and implement queries that will assist in developing answers to business problems.

For this assignment, continue to operate in the role of a data analyst at Adventure Works Cycles company. Based upon “LL Road Frame-Black 60” scenario and questions formulated for the Topic 3 assignment, write queries for the “AdventureWorks 2016” database using SQL Server 2016 Developer Edition.

Please note that when SQL queries are run, results are generated in the form of data. This data should be exported and saved to Excel for a visual check of accuracy.

Create a Word document that includes the SQL queries used to explore the database tables, and answer the following questions.

1. Find the product ID for the LL Road Frame – Black 60.

2. Find the listing price of the LL Road Frame – Black 60.

3. How would you rewrite the query used in question 2 to exclude NULL values?

4. How many orders have been placed for LL Road Frame – Black 60?

5. Rename the OrderQty to Quantity in your results.

Compile the Excel data file and Word document containing the SQL queries and answers to the questions into a .zip file and submit to your instructor.

APA style is not required, but solid academic writing is expected.

This assignment uses a rubric. Please review the rubric prior to beginning the assignment to become familiar with the expectations for successful completion.

You are not required to submit this assignment to Turnitin.

Part 2

The purpose of this assignment is to create temporary tables and document them using an entity relationship diagram (ERD).

For this assignment, continue to play the role of the data analyst for Adventure Works Cycling Company.

Please note that when SQL queries are run, results are generated in the form of data. This data should be exported and saved to Excel for a visual check of accuracy.

Create a Word document that includes the SQL query code for the temporary table that includes the dates of each sales order.

1. Create a temporary table for each of the Topic 4 assignment data components.

2. Use the skills you have learned and practiced to write a query to determine the dates of each sales orders from a table you have not looked at yet. Create a temporary table for this information.

3. Use OpenOffice Draw to create a new entity relationship diagram (ERD) to document the addition of the temporary tables and the work stream. Show the relationship between the tables.

Compile the OpenOffice Draw, Excel data file, and Word document containing the SQL queries and answers to the questions into a .zip file and submit to your instructor.

APA format is not required, but solid academic writing is expected.

This assignment uses a rubric. Please review the rubric prior to beginning the assignment to become familiar with the expectations for successful completion.

You are not required to submit this assignment to Turnitin.

Assignment 6

The purpose of this assignment is to complete two complex queries, join them, and validate the expected results of the join.

For this assignment, continue to assume the role of a data analyst at Adventure Works Cycling Company. As you work to address the business problem surrounding sales of the “LL Road Frame-Black 60,” you must continue to refine the data by condensing them into two tables. This requires you to join the table data sets together. While this is an important process, you must be aware of the data integrity issues that can occur as a result of completing joins.

Please note that when SQL queries are run, results are generated in the form of data. This data should be exported and saved to Excel for a visual check of accuracy.

Create a Word document that includes the SQL query code used to explore the database tables and answer the following questions.

Complete the steps below to practice joins.

1. Combine MyProduct and MyPriceHistory displaying the fields into a new temporary table called MyProductPriceHistory. Display ProductID, Name, EndDate, and List Price.

2. Combine MyProduct and MySalesOrderDetail displaying the fields into a new temporary table called MySalesOrderDetailbyName. Display ProductID, Name, SalesOrderID, and Quantity.

3. Combine MySalesOrderDetailbyName and ALLSalesOrderDates displaying the fields into a new temporary table called MySalesHistory. Display ProductID, Name, OrderDate, and SumOfQuantity. This will require you using the SUM and GROUP BY statements.

Create a Word document that includes the SQL query code used for each of the joins listed. Additionally, identify and analyze the data integrity issues you encountered in SQL.

1. Discuss bad queries and bad table data in your analysis.

2. Explain the impact of the data integrity issues.

3. Indicate what needs to be revised in the code.

4. Discuss specifically what needs to be done to fix the data integrity issues created by the join. Please note that as part of your final project for the course you will be adding these new tables to the ERD you created in Topic 5.

Compile the Excel data file and Word document containing the SQL queries and answers to the questions into a .zip file and submit to your instructor.

APA style is not required, but solid academic writing is expected.

This assignment uses a rubric. Please review the rubric prior to beginning the assignment to become familiar with the expectations for successful completion.

You are not required to submit this assignment to Turnitin.

Assignment 7

he purpose of this assignment is to update a previous query and present it in an easily readable format.

For this assignment, continue to assume the role of a data analyst at Adventure Works Cycles company.

Please note that when SQL queries are run, results are generated in the form of data. This data should be exported and saved to Excel for a visual check of accuracy.

Create a Word document that includes the SQL query code used to explore the database tables and answer the following questions.

Part 1:

1. Using what you have learned, replicate the data found in #MySalesOrderDetailbyDate and #MyProductPriceHistory using a single line of SQL code. Refer back to the Topic 5 assignment for this information.

2. Modify the query completed in the Topic 6 assignment, and present it in an easy-to-read format. To do this, write the query in two single statements without temporary tables, using abbreviations and line spacing.

Part 2:

Karen Berge, a document control assistant at the company, comes to you with a request. Karen wants the titles and file names of all of the documents she has produced and has asked you to generate this information for her. Using what you have learned, produce a query in one statement to give her the information she needs. Note that constructing the query will require some thinking outside the box since the relationships are not well documented.

Please note that when SQL queries are run, results are generated in the form of data. This data should be exported and saved to Excel for a visual check of accuracy. This Excel file should include the names of all documents Karen has produced.

Add to the Word document you created in Part 1 and include the SQL query code associated with the query you wrote to locate Karen’s documents.

Compile the Excel data file and Word document containing the SQL queries and answers to the questions into a .zip file and submit to your instructor.

APA style is not required, but solid academic writing is expected.

This assignment uses a rubric. Please review the rubric prior to beginning the assignment to become familiar with the expectations for successful completion.

You are not required to submit this assignment to Turnitin.

Assignment 8

The purpose of this assignment is to analyze data and use it to provide stakeholders with potential answers to a previously identified business problem.

For this assignment, continue to assume the role of a data analyst at Adventure Works Cycling Company. Evaluate the data associated with the drop in sales for the popular model “LL Road Frame-Black 60.” Provide a hypothesis on what could be contributing to the falling sales identified in the initial business problem presented by your manager.

In 250-500 words, share these recommendations in a Word document that addresses the following.

1. Summary of the business problem including the requestor who initially brought the problem to you.

2. Summary of the data that were requested and how they was obtained.

3. Discussion of the limitations of the available data and ethical concerns related to those limitations.

4. Hypothesis of why sales of the popular model have dropped based upon data analysis. Reference the Excel file that summarizes the data findings that resulted from your queries.

5. Recommendations for addressing the business problem.

6. In addition to the report, the manager has requested that you submit the Excel files summarizing the data findings that resulted from your queries.

7. The manager has also requested that you update the ERD you created in the Topic 5 assignment to include the tables generated as a result of the joins completed in the Topic 6 assignment. The ERD should clearly document the work stream and relationships.

Compile the updated ERD, Excel data file, and Word document containing the SQL queries and answers to the questions into a .zip file and submit to your instructor.

APA style is not required, but solid academic writing is expected.

This assignment uses a rubric. Please review the rubric prior to beginning the assignment to become familiar with the expectations for successful completion.

You are not required to submit this assignment to Turnitin.

Part 2

The purpose of this assignment is to build a stored procedure that allows for the compression of data and the ability to run a process repeatedly over time.

For this assignment, continue to assume the role of a data analyst at Adventure Works Cycling Company. Your manager is waiting for you to deliver your analysis of why the company is losing money on its popular model “LL Road Frame-Black 60.” As you are preparing your final report, your manager asks you to save your final queries from Topic 6 (the two nontemporary queries) as a stored procedure so you can periodically check on them from time to time. To do this, you will need to complete the steps below.

Please note that when SQL queries are run, results are generated in the form of data. This data should be exported and saved to Excel for a visual check of accuracy.

Create a Word document that includes the SQL query code used to explore the database tables.

1. Drop all ORDER_BY statements from the query.

2. Label one as PriceHistory723.

3. Label the other as SalesOrderDetailbyDate723.

Compile the Excel data file and Word document containing the SQL queries into a .zip file and submit to your instructor.

APA style is not required, but solid academic writing is expected.

This assignment uses a rubric. Please review the rubric prior to beginning the assignment to become familiar with the expectations for successful completion.

You are not required to submit this assignment to Turnitin.

 

Do you need a similar assignment done for you from scratch? Order now!
Use Discount Code "Newclient" for a 15% Discount!

POM-QM for Windows software LINK(http://wps.prenhall.com/bp_heizer_opsmgmt_10/147/37741/9661929.cw/index.html )

For this part of this project, you will need to use the POM software:

1. Read Appendix IV of the Operations Management (Heizer and Render, 2014) textbook.

2. Review the linear programming section from the POM manual:  Weiss, H.J. (2013) POM-QM for Windows manual. Upper Saddle River, NJ: Prentice Hall. Available from the ‘Help’ menu in the POM-QM Windows software. (Accessed: 30 December 2014). You may also wish to research and review online tutorials regarding the linear programming module and/or view the following resource.

Valverde, R. (2014) QM for Windows linear programming [Online] YouTube. Available from:  https://www.youtube.com/watch?v=uHsQSG75sPk  (Accessed 23 April 2015).

3. Install and launch the POM-QM for Windows software. From the main menu, select Module and then Linear Programming.

4. Program the linear programming formulation for the problem below and solve it with the use of POM. Refer to Appendix IV from the Heizer and Render (2014) textbook.

Note: Do not program the non-negativity constraint, as this is already assumed by the software.

For additional support, please reference the POM-QM for Windows manual available from the ‘Help’ menu in the POM-QM Windows software.

Individual Project, part 1

A firm uses three machines in the manufacturing of three products:

· Each unit of product 1 requires three hours on machine 1, two hours on machine 2 and one hour on machine 3.

· Each unit of product 2 requires four hours on machine 1, one hour on machine 2 and three hours on machine 3.

· Each unit of product 3 requires two hours on machine 1, two hours on machine 2 and two hours on machine 3.

The contribution margin of the three products is £30, £40 and £35 per unit, respectively.

The following are available for scheduling:

· 90 hours of machine 1 time

· 54 hours of machine 2 time

· 93 hours of machine 3 time

The linear programming formulation of this problem is as follows:

Maximise Z = 30X1 + 40X2 + 35X3

3X1 + 4X2 + 2X3 <= 90

2X1 + 1X2 + 2X3 <=54

X1 + 3X2 + 2X3 <=93

With X1, X2, X3 >= 0

To answer this question:

Answer each question and explain your reasoning or show your calculations.

1. What is the optimal production schedule for this firm? What is the profit contribution of each of these products?

2. What is the marginal value of an additional hour of time on machine 1? Over what range of time is this marginal value valid?

3. What is the opportunity cost associated with product 1? What interpretation should be given to this opportunity cost?

4. How many hours are used for machine 3 with the optimal solution?

5. How much can the contribution margin for product 2 change before the current optimal solution is no longer optimal?

Individual Project, part 2

For this part of this project, you will need to use the POM software:

1. Review the linear programming section from the POM manual:  Weiss, H.J. (2013) POM-QM for Windows manual. Upper Saddle River, NJ: Prentice Hall. Available from the ‘Help’ menu in the POM-QM Windows software. You may also wish to research and review online tutorials regarding the linear programming module.

2. Program the project management problem below and solve it with the use of POM. Select Project Management(PERT/CPM) module, and then select the option File->New->Mean, Std dev given items.

Activity	Mean duration	Std. dev. (days)
A	11	0.9
B	13	1.1
C	7	0.2
D	9	0.8
E	6	1
F	7	1.2
G	10	0.7
H	9	0.6
I	8	0.8

Table 1: Activity, duration and standard deviation

To answer this question:

Answer each question and explain your reasoning or show your calculations.

1. Calculate the project completion time.

2. Indicate the critical path activities.

3. What is the probability of completing this project between 38 and 40 days?

4. What are the slack values for activities C and F? Interpret the meaning of their slack values.

 

Do you need a similar assignment done for you from scratch? Order now!
Use Discount Code "Newclient" for a 15% Discount!

1. Label the box below to create a two-pass schedule legend.

Ans:

ES		EF
Float	Activity Name
	Duration
LS		LF

 

3. In the example below, label which activities are predecessors and which activities are successors.

Ans:

Predecessor: An activity which comes before another activity. So, it is activity “A” and activity “B”.

Successor: An activity that must occur after another activity. So, it is activity “C”.

 

5. Calculate early start, early finish, late start, late finish, and float for each of the activities in the network below. The duration of each activity is given.

 

Based on the analysis for the two-pass schedule:

A B C D

Duration: 12 Duration: 4 Duration: 1 Duration: 7

Early start: 0 Early start: 12 Early start: 16 Early start: 12

Early finish: 12 Early finish: 16 Early finish: 17 Early finish:19

Late start: 0 Late start: 14 Late start: 18 Late start: 12

Late finish: 12 Late finish: 18 Late finish: 19 Late finish: 19

Float: 0 Float: 2 Float: 2 Float: 0

 

E

Duration: 3 Early finish: 22 Late finish: 22

Early start:19 Late start: 19 Float: 0

 

6. Identify the critical path for the network in Exercise 5. How long should the project take?

Ans: The critical path is:

A – D – E, which has the longest path of 22 days.

 

9. Given the information below, create the project schedule network. Then, using the enumeration method, calculate and show all of the paths through the network. Show how long each path will take. Identify the critical path. Show the schedule on a Gantt chart showing critical and noncritical activities and float.

The critical path is

B – A – C – F – H – I, which takes 25 days.

Other paths:

B – E – H – I = 20 days

B – A – D – G – I = 23 days

 

 

Part 2 Scheduling Problem (50 points)

Complete the following:

Your child is a member of the school theater group and have been asked to prepare the schedule, etc. for a skit to be performed during the 4th of July celebration in the local village square.  As a volunteer, you offer to serve as the project controller because of your expertise in schedule management.

1. Construct an AON diagram similar to the format used in the Youtube videos found in the commentary using an MS Excel spreadsheet.  Ensure that you include a Start activity and a Finish activity.  Include a legend (example is the one completed in Question 1, Part 1.

1. Calculate the estimated time for each activity using PERT techniques for the 3 estimates provided.  Include a sample calculation.

1. Prepare a table that identifies all paths and their lengths.  Which path is the critical path?

1. During the execution phase of the skit project, one of the activities on the critical path is delayed by one day.  Is this serious?

1. Using the activity estimates for optimistic, pessimistic and most likely time to complete activities on the critical path, qualitatively discuss the estimate uncertainty of the critical path.

 

ANS: 1

2.

Different Paths are:

 

1. : 1-5-10-13-15

1. : 1-4-9-13-15

1. : 1-4-6-8-12-15

1. : 2-6-8-12-15

1. : 2-3-8-12-15

1. : 2-3-7-11

1. : 2-3-7-14

#	Optimistic	Most Likely	Pessimistic
1	39	44	55
2	39	48	63
3	41	52	63
4	37	43	49
5	35	42	49
6	32	37	42
7	29	34	39

 

 

3.

Activity	Duration	Early Start	Early Finish	Late Start	Late Finish	Float
1	4	0	4	0	4	0
2	5	0	5	0	5	0
3	5	10	15	15	20	5
4	10	4	14	4	14	0
5	12	4	16	11	23	7
6	6	14	20	14	20	0
7	10	10	20	25	35	15
8	7	20	27	20	27	0
9	9	14	23	17	26	3
10	3	16	19	23	26	7
11	17	20	37	35	52	15
12	8	27	35	27	35	0
13	9	23	32	26	35	3
14	14	20	34	38	52	18
15	17	35	52	35	52	0

 

The critical path is 1-4-6-8-12-15 = 4+10+6+7+8+17 = 52

4.

If one activity in the critical path is delayed for one day, the critical path will delay one day and the whole project will delay for a day too.

5.

If we use optimistic time for the critical path, that is 41 days, the pessimistic path is 63 days, which means 22 days of uncertainty can be saved.

 

Do you need a similar assignment done for you from scratch? Order now!
Use Discount Code "Newclient" for a 15% Discount!

1-  collect data using an API for TMDb

2- construct a graph representation of this data that will show which actors have acted together in various movies

3- complete all tasks according to the instructions found in the file ‘submissions_og2.py’ to complete the Graph class, the TMDbAPIUtils class, and the two global functions. The Graph class will serve as a re-usable way to represent and write out your collected graph data. The TMDbAPIUtils class will be used to work with the TMDB API for data retrieval.

4- Create a TMDb account to obtain an authentication token.

5- Producing correct nodes.csv and edges.csv

ps. other files are examples found in the intenret (not sure if can help)

 

import http.client import json import csv ############################################################################################################################# # cse6242 # All instructions, code comments, etc. contained within this notebook are part of the assignment instructions. # Portions of this file will auto-graded in Gradescope using different sets of parameters / data to ensure that values are not # hard-coded. # # Instructions: Implement all methods in this file that have a return # value of ‘NotImplemented’. See the documentation within each method for specific details, including # the expected return value # # Helper Functions: # You are permitted to write additional helper functions/methods or use additional instance variables within # the `Graph` class or `TMDbAPIUtils` class so long as the originally included methods work as required. # # Use: # The `Graph` class is used to represent and store the data for the TMDb co-actor network graph. This class must # also provide some basic analytics, i.e., number of nodes, edges, and nodes with the highest degree. # # The `TMDbAPIUtils` class is used to retrieve Actor/Movie data using themoviedb.org API. We have provided a few necessary methods # to test your code w/ the API, e.g.: get_movie_cast(), get_movie_credits_for_person(). You may add additional # methods and instance variables as desired (see Helper Functions). # # The data that you retrieve from the TMDb API is used to build your graph using the Graph class. After you build your graph using the # TMDb API data, use the Graph class write_edges_file & write_nodes_file methods to produce the separate nodes and edges # .csv files for use with the Argo-Lite graph visualization tool. # # While building the co-actor graph, you will be required to write code to expand the graph by iterating # through a portion of the graph nodes and finding similar artists using the TMDb API. We will not grade this code directly # but will grade the resulting graph data in your Argo-Lite graph snapshot. # ############################################################################################################################# class Graph: # Do not modify def __init__(self, with_nodes_file=None, with_edges_file=None): “”” option 1: init as an empty graph and add nodes option 2: init by specifying a path to nodes & edges files “”” self.nodes = [] self.edges = [] if with_nodes_file and with_edges_file: nodes_CSV = csv.reader(open(with_nodes_file)) nodes_CSV = list(nodes_CSV)[1:] self.nodes = [(n[0], n[1]) for n in nodes_CSV] edges_CSV = csv.reader(open(with_edges_file)) edges_CSV = list(edges_CSV)[1:] self.edges = [(e[0], e[1]) for e in edges_CSV] def add_node(self, id: str, name: str) -> None: “”” add a tuple (id, name) representing a node to self.nodes if it does not already exist The graph should not contain any duplicate nodes “”” return NotImplemented def add_edge(self, source: str, target: str) -> None: “”” Add an edge between two nodes if it does not already exist. An edge is represented by a tuple containing two strings: e.g.: (‘source’, ‘target’). Where ‘source’ is the id of the source node and ‘target’ is the id of the target node e.g., for two nodes with ids ‘a’ and ‘b’ respectively, add the tuple (‘a’, ‘b’) to self.edges “”” return NotImplemented def total_nodes(self) -> int: “”” Returns an integer value for the total number of nodes in the graph “”” return NotImplemented def total_edges(self) -> int: “”” Returns an integer value for the total number of edges in the graph “”” return NotImplemented def max_degree_nodes(self) -> dict: “”” Return the node(s) with the highest degree Return multiple nodes in the event of a tie Format is a dict where the key is the node_id and the value is an integer for the node degree e.g. {‘a’: 8} or {‘a’: 22, ‘b’: 22} “”” return NotImplemented def print_nodes(self): “”” No further implementation required May be used for de-bugging if necessary “”” print(self.nodes) def print_edges(self): “”” No further implementation required May be used for de-bugging if necessary “”” print(self.edges) # Do not modify def write_edges_file(self, path=”edges.csv”)->None: “”” write all edges out as .csv :param path: string :return: None “”” edges_path = path edges_file = open(edges_path, ‘w’, encoding=’utf-8′) edges_file.write(“source” + “,” + “target” + “\n”) for e in self.edges: edges_file.write(e[0] + “,” + e[1] + “\n”) edges_file.close() print(“finished writing edges to csv”) # Do not modify def write_nodes_file(self, path=”nodes.csv”)->None: “”” write all nodes out as .csv :param path: string :return: None “”” nodes_path = path nodes_file = open(nodes_path, ‘w’, encoding=’utf-8′) nodes_file.write(“id,name” + “\n”) for n in self.nodes: nodes_file.write(n[0] + “,” + n[1] + “\n”) nodes_file.close() print(“finished writing nodes to csv”) class TMDBAPIUtils: # Do not modify def __init__(self, api_key:str): self.api_key=api_key def get_movie_cast(self, movie_id:str, limit:int=None, exclude_ids:list=None) -> list: “”” Get the movie cast for a given movie id, with optional parameters to exclude an cast member from being returned and/or to limit the number of returned cast members documentation url: https://developers.themoviedb.org/3/movies/get-movie-credits :param string movie_id: a numerical movie_id :param integer limit: maximum number of returned cast members by their ‘order’ attribute e.g., limit=5 will attempt to return the 5 cast members having ‘order’ attribute values between 0-4 If after excluding, there are fewer cast members than the specified limit, then return the remaining members (excluding the ones whose order values are outside the limit range). If cast members with ‘order’ attribute in the specified limit range have been excluded, do not include more cast members to reach the limit. If after excluding, the limit is not specified, then return all remaining cast members.” e.g., if limit=5 and the actor whose id corresponds to cast member with order=1 is to be excluded, return cast members with order values [0, 2, 3, 4], not [0, 2, 3, 4, 5] :param list exclude_ids: a list of ints containing ids (not cast_ids) of cast members that should be excluded from the returned result e.g., if exclude_ids are [353, 455] then exclude these from any result. :rtype: list return a list of dicts, one dict per cast member with the following structure: [{‘id’: 97909 # the id of the cast member ‘character’: ‘John Doe’ # the name of the character played ‘credit_id’: ’52fe4249c3a36847f8012927′ # id of the credit, …}, … ] Note that this is an example of the structure of the list and some of the fields returned by the API. The result of the API call will include many more fields for each cast member. Important: the exclude_ids processing should occur prior to limiting output. “”” return NotImplemented def get_movie_credits_for_person(self, person_id:str, vote_avg_threshold:float=None)->list: “”” Using the TMDb API, get the movie credits for a person serving in a cast role documentation url: https://developers.themoviedb.org/3/people/get-person-movie-credits :param string person_id: the id of a person :param vote_avg_threshold: optional parameter to return the movie credit if it is >= the specified threshold. e.g., if the vote_avg_threshold is 5.0, then only return credits with a vote_avg >= 5.0 :rtype: list return a list of dicts, one dict per movie credit with the following structure: [{‘id’: 97909 # the id of the movie credit ‘title’: ‘Long, Stock and Two Smoking Barrels’ # the title (not original title) of the credit ‘vote_avg’: 5.0 # the float value of the vote average value for the credit}, … ] “”” return NotImplemented ############################################################################################################################# # # BUILDING YOUR GRAPH # # Working with the API: See use of http.request: https://docs.python.org/3/library/http.client.html#examples # # Using TMDb’s API, build a co-actor network for the actor’s/actress’ highest rated movies # In this graph, each node represents an actor # An edge between any two nodes indicates that the two actors/actresses acted in a movie together # i.e., they share a movie credit. # e.g., An edge between Samuel L. Jackson and Robert Downey Jr. indicates that they have acted in one # or more movies together. # # For this assignment, we are interested in a co-actor network of highly rated movies; specifically, # we only want the top 3 co-actors in each movie credit of an actor having a vote average >= 8.0. # Build your co-actor graph on the actor ‘Laurence Fishburne’ w/ person_id 2975. # # You will need to add extra functions or code to accomplish this. We will not directly call or explicitly grade your # algorithm. We will instead measure the correctness of your output by evaluating the data in your argo-lite graph # snapshot. # # GRAPH SIZE # With each iteration of your graph build, the number of nodes and edges grows approximately at an exponential rate. # Our testing indicates growth approximately equal to e^2x. # Since the TMDB API is a live database, the number of nodes / edges in the final graph will vary slightly depending on when # you execute your graph building code. We take this into account by rebuilding the solution graph every few days and # updating the auto-grader. We establish a bound for lowest & highest encountered numbers of nodes and edges with a # margin of +/- 100 for nodes and +/- 150 for edges. e.g., The allowable range of nodes is set to: # # Min allowable nodes = min encountered nodes – 100 # Max allowable nodes = max allowable nodes + 100 # # e.g., if the minimum encountered nodes = 507 and the max encountered nodes = 526, then the min/max range is 407-626 # The same method is used to calculate the edges with the exception of using the aforementioned edge margin. # ———————————————————————————————————————- # BEGIN BUILD CO-ACTOR NETWORK # # INITIALIZE GRAPH # Initialize a Graph object with a single node representing Laurence Fishburne # # BEGIN BUILD BASE GRAPH: # Find all of Laurence Fishburne’s movie credits that have a vote average >= 8.0 # FOR each movie credit: # | get the movie cast members having an ‘order’ value between 0-2 (these are the co-actors) # | # | FOR each movie cast member: # | | using graph.add_node(), add the movie cast member as a node (keep track of all new nodes added to the graph) # | | using graph.add_edge(), add an edge between the Laurence Fishburne (actress) node # | | and each new node (co-actor/co-actress) # | END FOR # END FOR # END BUILD BASE GRAPH # # # BEGIN LOOP – DO 2 TIMES: # IF first iteration of loop: # | nodes = The nodes added in the BUILD BASE GRAPH (this excludes the original node of Laurence Fishburne!) # ELSE # | nodes = The nodes added in the previous iteration: # ENDIF # # FOR each node in nodes: # | get the movie credits for the actor that have a vote average >= 8.0 # | # | FOR each movie credit: # | | try to get the 3 movie cast members having an ‘order’ value between 0-2 # | | # | | FOR each movie cast member: # | | | IF the node doesn’t already exist: # | | | | add the node to the graph (track all new nodes added to the graph) # | | | ENDIF # | | | # | | | IF the edge does not exist: # | | | | add an edge between the node (actor) and the new node (co-actor/co-actress) # | | | ENDIF # | | END FOR # | END FOR # END FOR # END LOOP # # Your graph should not have any duplicate edges or nodes # Write out your finished graph as a nodes file and an edges file using: # graph.write_edges_file() # graph.write_nodes_file() # # END BUILD CO-ACTOR NETWORK # ———————————————————————————————————————- # Exception handling and best practices # – You should use the param ‘language=en-US’ in all API calls to avoid encoding issues when writing data to file. # – If the actor name has a comma char ‘,’ it should be removed to prevent extra columns from being inserted into the .csv file # – Some movie_credits may actually be collections and do not return cast data. Handle this situation by skipping these instances. # – While The TMDb API does not have a rate-limiting scheme in place, consider that making hundreds / thousands of calls # can occasionally result in timeout errors. If you continue to experience ‘ConnectionRefusedError : [Errno 61] Connection refused’, # – wait a while and then try again. It may be necessary to insert periodic sleeps when you are building your graph. def return_name()->str: “”” Return a string containing your GT Username e.g., gburdell3 Do not return your 9 digit GTId “”” return NotImplemented def return_argo_lite_snapshot()->str: “”” Return the shared URL of your published graph in Argo-Lite “”” return NotImplemented # You should modify __main__ as you see fit to build/test your graph using the TMDBAPIUtils & Graph classes. # Some boilerplate/sample code is provided for demonstration. We will not call __main__ during grading. if __name__ == “__main__”: graph = Graph() graph.add_node(id=’5064′, name=’Meryl Streep’) tmdb_api_utils = TMDBAPIUtils(api_key=’YOURKEY’) # call functions or place code here to build graph (graph building code not graded) # Suggestion: code should contain steps outlined above in BUILD CO-ACTOR NETWORK graph.write_edges_file() graph.write_nodes_file() # If you have already built & written out your graph, you could read in your nodes & edges files # to perform testing on your graph. # graph = Graph(with_edges_file=”edges.csv”, with_nodes_file=”nodes.csv”)

 

Do you need a similar assignment done for you from scratch? Order now!
Use Discount Code "Newclient" for a 15% Discount!

Project Scenario

 

Previously, the CEO told you about the breach at a rival company and his concerns for security at Mercury USA. In light of these developments, Mercury USA has decided to conduct vulnerability assessments. An outside firm has conducted penetration tests on Mercury’s systems and provided the reports. Now, it is up to you as the cyber threat analyst to look at those reports, apply them to the specific company setup and business needs, and recommend changes.

Mercury USA’s concerns:

· protecting customer information

· protecting proprietary business data

· protecting dangerous loads/hazardous materials during transportation

Before you came onboard, a third-party penetration tester was brought in to assess Mercury USA’s security posture and network. One of the findings from the pen test report was the realization that an important part of a VM process is a vulnerability scanner. The pen tester provided a scan report using the free tool Open Vulnerability Assessment Scanner (OpenVAS). Judy has provided you with the OpenVAS report for your initial analysis.

 

Do you need a similar assignment done for you from scratch? Order now!
Use Discount Code "Newclient" for a 15% Discount!