OSI AND TCP/IP Models Assessment Questions

Start the VM for assignment four. Inside the VM, click Start, type SSMS (to open SQL Server Management Studio), and connect to the instance. Select Databases, then select the AdventureWorksDW2017 database for this assignment.

In this assignment, students will use the Adventure Works CTP3 database and practice indexing appropriately.

The ability to evaluate and implement various storage structures, index selections, and views is an important aspect of database management.

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

Create a Microsoft Word document that includes the SQL query code used to explore the database tables and complete the following criteria. Make sure to provide a screenshot for each.

  1. You notice when running queries that the database is returning results very slowly. Make sure to address the following:
    1. Discuss likely causes for the slow returns.
    1. Propose solutions for speeding up the process.
    1. Explain why implementing the index will speed queries.
  2. Discuss two indexing issues that can negatively affect databases. Provide specific examples in your discussion.
  3. The sales manager has received a customer request that all products be searchable according to colour specifications. In this particular case, the customer is seeking red products. Build a view consisting of Productid, Product, and Colour, and query the new view.
  4. The sales manager presented the customer with the view you created in question three. The customer is still unhappy with the product list because many of the items were not available for bulk purchase or were not yet in storage, which resulted in delays. To address the problem, rebuild the view with multiple joins and filter your query to only look up items from the “Finished Goods Storage” located within the “Name” column from the Location table utilizing Location (primary key). Bring in the “Quantity” field from the Inventory table. Rerun the query in the view.

Submit the Word document (with screenshots).

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.

? Refer to the “Contemporary Issues in Science and Technology Research Guide,” located in the Course Materials, for complete instructions.

Section 1: Topic Selection, Problem Statement, and Metrics

Research and find a topic related to current scientific, human, social, business, or economic problems. Refer to the “Contemporary Issues in Science and Technology Research Guide” for possible topics. In a Microsoft Word document a maximum of 750 words, include the following:

  1. Research the historical, social, professional, ethical, and legal aspects of your discipline. Identify a problem facing the world today and create a problem statement. The problem statement should be objective, backed by statistics, clear, and concise. The problem scope and affected population(s) should be inherent to the problem statement.
  2. Rationale: Analyse the social and professional context of your discipline. Provide a one-paragraph explanation of why the topic is important, both to you personally and to the profession, as well as its effect(s) on the professional and ethical environments.
  3. Vision Statement: Include a version of the world where the problem remains unsolved, as well as a version of the world if the problem were to be solved.
  4. Define the factors that affect the problem, and clearly identify metrics that show the severity of the problem.
  5. Once the measurable factors have been identified, determine how this data can be gathered for future analysis.

The assignment requires a minimum of 3 peer reviewed journal articles from the last 5 years. Articles with no designated author are no allowed. Citations must be made following APA.

Prepare this assignment according to the guidelines found in the APA Style Guide, located in the Student Success Centre. An abstract is not required.

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

  1. An ISP is granted a block of addresses starting with 190.100.0.0/16. The ISP needs to allocate these addresses to three groups of customers as follows (assume no reserved subnets):
    1. The first group has 64 customers; each need 128 addresses.
    1. The second group has 128 customers; each need 256 addresses.

The third group has 128 customers; each need 64 addresses.

Start the VM for assignment three. Inside the VM, click Start, type SSMS (to open SQL Server Management Studio), and connect to the instance. Select Databases, then select the AdventureWorksDW2017 database for this assignment.

In this assignment, students will create database tables and implement keys and restraints.

The human resources department has requested that you create a table that can be used to store birthdays in the database. This table will be used to support a newsletter that is distributed internally to report on office events.

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

Create a Microsoft Word document that includes the SQL query code used to explore the database tables and complete the following criteria. Make sure to provide a screenshot for each.

  1. Create BusinessEntityID column as an integer. Include a DoB (Date of Birth) column and a date format.
  2. Add a constraint to make BusinessEntityID a primary key.
  3. Insert one record for BusinessEntityID 1 and include 1990/01/01 as the birthday.
  4. Once added, query your new table showing the new record.

Submit the Word document (with screenshots).

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.

1. If a logical address is 32 bits (4 bytes), what is the minimum header size at network layer of the TCP/IP protocol suite?

Start the VM for assignment two. Inside the VM, click Start, type SSMS (to open SQL Server Management Studio), and connect to the instance. Select Databases, then select the AdventureWorksDW2017 database for this assignment.

In this assignment, students will build refined queries and utilize transactional statements.

The human resources manager has asked you to obtain some specific information from the database. Use what you have learned in Topics 1 and 2 to build a refined query that answers each of the questions below using the WHERE statement and the specified fields so you can report the requested information back to the human resources manager.

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

Create a Microsoft Word document that includes the SQL query code used to explore the database tables and complete the following criteria. Make sure to provide a screenshot for each.

  1. Provide a list of all of the records pertaining to the FirstName “John” in the Person. Person table. Include the full name of all the records and state the total number of records.
  2. The human resources manager indicated that John T. Moore was an erroneous entry and has requested that record be removed from the database. Without running a query, provide the full SQL transaction (write-out/type the query) that you would use to delete that specific record using the BusinessEntityID.
  3. In order to clear old contact lists from the Outlook address books, the human resources manager has requested a full list of the e-mail address for each employee. Using the “Adventure Works CTP3 Database,” find the related table and write a join query to include the full name and the e-mail address for each employee.
  4. Using the query written for question three, identify the full name for the following e-mail address, [email protected]. Add to the question three query with a WHERE clause to display this record.

Submit the Word document (including screenshots).

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 In this assignment, students will become familiar with the SQL Server Database environment and the SQL Server Management Studio (SSMS) to run simple SQL queries. Start the VM for assignment one. Inside the VM, click Start, type SSMS (to open SQL Server Management Studio), and connect to the instance. Select Databases, then right-click on the AdventureWorksDW2017 database, and select new Query. Run the following queries using Adventure Works CTP3. Please note that when SQL queries are run, results are generated in the form of data. This data should be exported and saved to a Microsoft Excel file for a visual check of accuracy. Select * fromSales.Store; Select * fromSales.CreditCard; Create a Microsoft 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–2 above. Make sure to provide a screenshot for each answer to every question. How many records are in theSales.Store table? What kind of information is contained in theSales.Store table (i.e. employee demographics, customer information, facility information, etc.)? What columns are contained on theSales.Store table? What is the Expiration year of Credit Card ID #13? In the Credit Card table, how would you expand the second query to determine all the cards of type “Superior Card”? Submit the Word document (with screenshots). 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