Last Updated: Feb 21, 2022, 4:14 PM
Introduction to Databases
MIS-605 4 Credits Feb 24 – Apr 20, 2022
Course Description
This course introduces the basic role and uses of databases within business enterprises. Students examine
database design types, development, staging, production environments, and maintenance of a database
structure. Emphasis is on appropriate application and implementation of database functions in relation to
performing data analytics.
Instructor Contact Information
Kenneth Ferrell
[email protected]
Class Resources
MIS-605 Discussion Questions
Instructor Only
Databases Illuminated
Ricardo, C. M., & Urban, S. D. (2017). Databases illuminated (3rd ed.). Jones and Bartlett Learning. ISBN-13:
9781284056945
SQL Tutorial
Explore the SQL Tutorial website. You will use this resource throughout the course to assist with assignment
completion.
http://www.w3schools.com/sql/
MIS-605 Instructor Solutions
Page 1 Grand Canyon University 2022 © Prepared on: Feb 21, 2022For instructor use only. Do not post.
LopesCloud
This course requires the use of LopesCloud. Virtual machines are located in LopesCloud and will be utilized for
assignments throughout this course.
MIS-605 Course Revision History
This document provides a history of changes to the course.
AdventureWorks Sample Databases
This course requires the use of the AdventureWorks OLTP downloads.
Use this link to download the resource versions as directed by your instructor.
https://www.gcumedia.com/digital-resources/microsoft/2020/adventureworks_1e.php
AdventureWorks 2014 Sample OLTP Database
Use the “AdventureWorks 2014 Sample OLTP Database” dictionary to complete the assignments for this
course. Download and save a copy of the file using the link provided.
AssignmentTutorOnline
Click to access AdventureWorks2014.pdf
Draw.io
This resource can be used throughout the course to assist with assignment completion.
https://app.diagrams.net/
Topic 1: Database Methodology
This topic provides an introduction to database management system concepts and components, describes
user roles within a database environment, and shows how databases are used to facilitate solutions to realworld business problems. Students will become familiar with basic SQL commands as they explore database
schemas and write queries to retrieve data from various tables.
Objectives:
Page 2 Grand Canyon University 2022 © Prepared on: Feb 21, 20221. Define basic components of databases: structures, tables, and metadata.
2. Identify database field types.
3. Identify database software tools.
4. Evaluate the pros and cons of using various database software programs to address business problems.
5. Examine the process of database management within a typical IT department.
Feb 24, 2022 – Mar 2, 2022 Max Points: 100
Resources
Microsoft SQL Server vs. Oracle: The Same, But Different?
Read “Microsoft SQL Server vs. Oracle: The Same, But Different?” by Stansfield, located on the Segue
Technologies website (2014).
http://www.seguetech.com/blog/2014/03/13/Microsoft-SQL-Server-versus-oracle
Databases Illuminated
Read Chapter 1 in Databases Illuminated.
Schemas in AdventureWorks
Read “Schemas in AdventureWorks,” from Microsoft (2010).
https://technet.microsoft.com/en-us/en_us/library/ms124894(v=sql.100).aspx
Assessments
Class Introductions
Start Date & Time Due Date & Time Points
Feb 24, 2022, 12:00 AM Feb 26, 2022, 11:59 PM 0
Take a moment to explore your new classroom and introduce yourself to your fellow classmates. What are you
excited about learning? What do you think will be most challenging?
Locating Database Content
Start Date & Time Due Date & Time Points
Feb 24, 2022, 12:00 AM Mar 2, 2022, 11:59 PM 35
The purpose of this assignment is to practice navigating and identifying database tables and related fields.
Page 3 Grand Canyon University 2022 © Prepared on: Feb 21, 2022This assignment uses a GCU-provided virtual machine that will be specified by your instructor.
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 data dictionary titled “AdventureWorks 2014 Sample OLTP Database,” located in Class Resources,
and use SELECT and FROM statements to find the answer to each of the questions below. 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–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 LopesWrite.
Proposal for Process Improvement
Start Date & Time Due Date & Time Points
Feb 24, 2022, 12:00 AM Mar 2, 2022, 11:59 PM 35
The purpose of this assignment is to practice making proposals to communicate database needs to relevant
stakeholders.
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.
Using the “Proposal for Process Improvement” template, construct a Microsoft Word document that outlines
the requester, business problem, proposed solution, resources, implementation steps, benefits, and potential
obstacles of the request.
APA style is not required, but solid academic writing is expected.
Page 4 Grand Canyon University 2022 © Prepared on: Feb 21, 2022This 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 LopesWrite.
Topic 1 DQ 1
Start Date & Time Due Date & Time Points
Feb 24, 2022, 12:00 AM Feb 26, 2022, 11:59 PM 5
In the topic reading “Microsoft SQL Server vs. Oracle: The Same, But Different?” two relational database
management systems were compared. Conduct research and evaluate the pros and cons of two additional
systems. Some to consider could include MYSQL, PostgreSQL, and IBM DB2. Include relevant links to related
articles and content when appropriate.
Topic 1 DQ 2
Start Date & Time Due Date & Time Points
Feb 24, 2022, 12:00 AM Feb 28, 2022, 11:59 PM 5
Explain metadata, why metadata is important in a database design, and metadata’s connection to the schema.
Include specific examples to support your ideas.
Week 1 Participation
Start Date & Time Due Date & Time Points
Feb 24, 2022, 12:00 AM Mar 2, 2022, 11:59 PM 20
Topic 2: Relational Databases
In this topic, students become acquainted with the relational data model and its characteristics. Relational
database management systems (RDBMS) store data in a structured format that allows data to be easily
retrieved and aggregated based on specific business rules. Data is stored in multiple tables with defined
constraints (primary and foreign key designations) which are used to preserve data integrity. The importance
of properly planning RDBMS is also covered, along with the database design and modeling concepts that are
used to create the entity-relationship diagrams (ERD) that are used to diagram and create databases.
Objectives:
1. Analyze the structure of relational databases using diagrams.
2. Utilize various types of database constraints, object relational mapping, and entity relationship diagrams
(ERDS) to diagram and construct databases.
Mar 3, 2022 – Mar 9, 2022 Max Points: 105
Resources
Codd’s 12 Rules
Page 5 Grand Canyon University 2022 © Prepared on: Feb 21, 2022Read “Codd’s 12 Rules,” located on the W3rewsources website.
http://www.w3resource.com/sql/sql-basic/codd-12-rule-relation.php
Databases Illuminated
Read Chapter 2 and the following sections from Chapter 3 in Databases Illuminated:
3.1 Purpose of the E-R Model3.2 Entities3.3 Attributes3.3.1 Domains3.3.2 Null Values3.3.3 Multivalued
Attributes3.3.4 Composite Attributes3.3.5 Derived Attributes3.4 Keys3.2.1 Superkeys3.4.2 Candidate Keys3.4.3
Primary Keys
A Relational Model of Data for Large Shared Data Banks
Read “A Relational Model of Data for Large Shared Data Banks,” by Baxendale and Codd, from Communications
of the ACM (1970).
https://lopes.idm.oclc.org/login?url=http://search.ebscohost.com/login.aspx?
direct=true&db=aci&AN=5221525&site=ehost-live&scope=site
Assessments
Interpreting and Building Entity-Relationship Diagram (ERD)
Start Date & Time Due Date & Time Points
Mar 3, 2022, 12:00 AM Mar 9, 2022, 11:59 PM 75
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.
This assignment uses a GCU-provided virtual machine that will be specified by your instructor.
Part 1:
For this part of the assignment, you will use the “AdventureWorks 2014 OLTP Database,” located in the Class
Resources, and use the schema. Follow the tree/chain to answer the questions below in a Microsoft 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:
Page 6 Grand Canyon University 2022 © Prepared on: Feb 21, 2022For 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 Entity-Relationship Diagram ERD (using Draw.io or another similar application as approved by
instructor) demonstrating how your new table will connect to the “HumanResources.Employee” 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 to two paragraph Word document, explain how these fields relate to the rest of the database content.
Compile the ERD 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 LopesWrite.
Topic 2 DQ 1
Start Date & Time Due Date & Time Points
Mar 3, 2022, 12:00 AM Mar 5, 2022, 11:59 PM 5
Explain why database designers use relationships to construct a database instead of listing all the records in
one location. Some think it would seem easier to have all the information in one place, so explain why using
relationships is the standard.
Topic 2 DQ 2
Start Date & Time Due Date & Time Points
Mar 3, 2022, 12:00 AM Mar 7, 2022, 11:59 PM 5
In the topic Resources, you read “Codd’s 12 Rules” and learned about how in 1985, Dr. E. F. Codd developed
the rules for an ideal relational database. Although all of the rules were not actually used, explain why they
have served as a guideline for developers over the last few decades. Provide specific examples to support your
ideas.
Week 2 Participation
Start Date & Time Due Date & Time Points
Mar 3, 2022, 12:00 AM Mar 9, 2022, 11:59 PM 20
Topic 3: Interpreting Business Needs and Introduction to SQL Server
Organizations use data collected and stored in database management systems (DBMS) to support their day-today business processes, and to analyze trends and behavior patterns using historical data so they can make
informed decisions based on the health and growth potential of the business. The information presented in
this topic shows how these processes are accomplished using back-end DBMS and Structured Query Language
Page 7 Grand Canyon University 2022 © Prepared on: Feb 21, 2022(SQL).
Objectives:
1. Identify business problems that can be addressed through analyzing database content.
2. Construct a basic database query and manage data by using common table expressions (CTEs) and SQL.
Mar 10, 2022 – Mar 16, 2022 Max Points: 70
Resources
10 Database Design Best Practices
Read “10 Database Design Best Practices,” by All, located on the Enterprise Apps Today website (2015).
10 Database Design Best Practices
Databases Illuminated
Read Chapter 15 and the following sections from Chapters 5 and 6 in Databases Illuminated:
5.1 Brief History of SQL in Relational Database Systems6.7 The Normalization Process6.7.1 Analysis6.7.2
Synthesis6.7.3 Normalization from an Entity-Relationship Diagram6.8 When to Stop Normalizing6.9 Nonnormalized Databases
ACM Code of Ethics and Professional Conduct
Read “ACM Code of Ethics and Professional Conduct,” located on the Association for Computing Machinery
website (1992).
https://www.acm.org/about-acm/acm-code-of-ethics-and-professional-conduct
IEEE Code of Ethics
Read “IEEE Code of Ethics,” located on the IEEE website.
http://www.ieee.org/about/corporate/governance/p7-8.html
Do We Still Need Database Design in the Era of Big Data?
Read “Do We Still Need Database Design in the Era of Big Data?” by Lyon, located on the Database Journal
website (2014).
http://www.databasejournal.com/features/db2/do-we-still-need-database-design-in-the-era-of-big-data.html
Page 8 Grand Canyon University 2022 © Prepared on: Feb 21, 2022Assessments
Visualizing Business Problems
Start Date & Time Due Date & Time Points
Mar 10, 2022, 12:00 AM Mar 16, 2022, 11:59 PM 40
The purpose of this assignment is to identify business problems that can be addressed through analyzing
database content.
This assignment uses a GCU-provided virtual machine that will be specified by your instructor.
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 FrameBlack 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 data dictionary titled
“AdventureWorks 2014 OLTP Schema,” located in the Class Resources,
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 LopesWrite.
Topic 3 DQ 1
Start Date & Time Due Date & Time Points
Mar 10, 2022, 12:00 AM Mar 12, 2022, 11:59 PM 5
Assume you have a position as database administrator in a large corporation. The company has been
collecting data about employees, including monitoring their working habits by recording their keystrokes,
timing their telephone interactions with clients, and scanning their e-mail for personal correspondence. As
database administrator (DBA), you are asked to help establish a record-keeping system to store such data.
Does the company have a legal right to perform this kind of monitoring? Explain your professional
Page 9 Grand Canyon University 2022 © Prepared on: Feb 21, 2022responsibility in this situation. Discuss provisions within the ACM and IEEE Codes of Ethics that can be used to
guide you.
Topic 3 DQ 2
Start Date & Time Due Date & Time Points
Mar 10, 2022, 12:00 AM Mar 14, 2022, 11:59 PM 5
Your manager has informed you that another manager’s team sales are going to be included as part of your
team’s sales report this month. Discuss whether you would report this. If so, to whom? Explain why.
Week 3 Participation
Start Date & Time Due Date & Time Points
Mar 10, 2022, 12:00 AM Mar 16, 2022, 11:59 PM 20
Topic 4: Construction of Database Queries
Practical use of Structured Query Language (SQL) is covered in this Topic. The syntax of the SELECT statement
is explained as students learn how to interpret business problems and find solutions using SQL.
Objectives:
1. Interpret business problems to identify appropriate database query design solutions.
2. Create simple queries to retrieve data from tables.
3. Execute a database query using a select statement.
4. Demonstrate the use of indexes, including Creating, Altering, and Dropping Tables, in database
management.
Mar 17, 2022 – Mar 23, 2022 Max Points: 105
Resources
Writing SQL Queries: Let’s Start With the Basics
Read “Writing SQL Queries: Let’s Start with the Basics,” by Goldstein, located on the TechNet website (2005).
https://technet.microsoft.com/en-us/library/bb264565(v=sql.90).aspx
Databases Illuminated
Read the following sections from Chapter 5 in Databases Illuminated:
5.3.3 CREATE INDEX5.4 Manipulating the Database: SQL DML5.4.1 Introduction to the SELECT Statement5.4.2
SELECT Using Multiple Tables
Page 10 Grand Canyon University 2022 © Prepared on: Feb 21, 2022Assessments
Writing Queries to Get Data
Start Date & Time Due Date & Time Points
Mar 17, 2022, 12:00 AM Mar 23, 2022, 11:59 PM 75
The purpose of this assignment is to design and implement queries that will assist in developing answers to
business problems.
This assignment uses a GCU-provided virtual machine that will be specified by your instructor.
For this assignment, continue to operate in the role of data analyst at Adventure Works Cycles company.
Based on the “LL Road Frame-Black 60” scenario and questions formulated for the Topic 3 assignment, write
queries for the AdventureWorks database using Microsoft SQL Server.
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 Microsoft 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 prices for the LL Road Frame Black 60. Include the ProductID, EndDate, and ListPrice.
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 LopesWrite.
Topic 4 DQ 1
Start Date & Time Due Date & Time Points
Mar 17, 2022, 12:00 AM Mar 19, 2022, 11:59 PM 5
In the Topic 4 assignment, a key query has been left out. You were not asked to query the date of the sales
orders. Discuss how you think you would complete that query. Explain why this query is important as you
attempt to address the business problem that has been presented to you.
Topic 4 DQ 2
Start Date & Time Due Date & Time Points
Page 11 Grand Canyon University 2022 © Prepared on: Feb 21, 2022Mar 17, 2022, 12:00 AM Mar 21, 2022, 11:59 PM 5
Your manager has difficulty understanding how SQL works and what insights the queries you have completed
reveal. Discuss how you can communicate this information to the manager to get your point across. Provide
specific ideas for ways to share the information in easy to understand terms.
Week 4 Participation
Start Date & Time Due Date & Time Points
Mar 17, 2022, 12:00 AM Mar 23, 2022, 11:59 PM 20
Topic 5: Foundational Database Statements and Queries
The Data Definition Language (DDL) commands and usage are explained in this topic. Students will write
queries using CREATE, ALTER, UPDATE, and DROP statements to define, change, and delete database objects.
Students will also learn how temporary tables are used in SQL programming to store and process
intermediate data.
Objectives:
1. Retrieve data from multiple tables.
2. Construct a basic database query to address a problem.
3. Utilize and execute select statements to construct, manage, and organize data within a database query.
4. Utilize CREATE, ALTER, UPDATE and DROP statements in query construction to interact within a database
management system.
Mar 24, 2022 – Mar 30, 2022 Max Points: 155
Resources
Database Design: More Than Just an ERD
Read “Database Design: More Than Just an ERD,” by Edison, located on the Verbabelo website (2015).
http://www.vertabelo.com/blog/notes-from-the-lab/database-design-more-than-just-an-erd
Entity Relationship Diagram (ERD) Training Video
View “Entitle Relationship Diagram (ERD) Training Video,” by Baldazzi, from YouTube (2013).
Databases Illuminated
Read the following sections from Chapters 3 and 5 in Databases Illuminated:
Page 12 Grand Canyon University 2022 © Prepared on: Feb 21, 20223.5 Relationships3.5.1 Degree of Relationships3.5.2 Attributes of Relationship Sets3.5.3 Cardinality of a Binary
Relationship3.5.4 Showing Cardinalities on an E-R Diagram3.5.5 Participation Constraints3.5.6 Using (min,max)
Notation for Cardinality and Participation3.6 Roles3.7 Existence Dependency and Weak Entities3.8 Sample E-R
Diagram5.1 Brief History of SQL in Relational Database Systems5.2 Architecture of a Relational Database
Management System5.3 Defining the Database: SQL DDL5.3.1 CREATE DATABASE, CREATE SCHEMA5.3.2
CREATE TABLE5.3.4 ALTER TABLE, RENAME TABLE5.3.5 DROP Statements5.3.6 Additional SQL DDL
Example5.4.3 SELECT with Other Operators
SELECT (Transact-SQL)
Read ” SELECT (Transact-SQL),” from Microsoft (2017).
https://docs.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql?view=sql-server-ver15
Assessments
Advanced Queries
Start Date & Time Due Date & Time Points
Mar 24, 2022, 12:00 AM Mar 30, 2022, 11:59 PM 75
The purpose of this assignment is to conduct advanced queries for the creation of separate reports.
This assignment uses a GCU-provided virtual machine that will be specified by your instructor.
For this assignment, continue to play the role of the data analyst for Adventure Works Cycling Company. Your
manager has asked you to localize query results into a temporary table to avoid running the same queries
repeatedly and to better track the data. This will also free up network resources for the company. Saving
queries is of the utmost importance since temporary tables are lost when you disconnect from the server.
Failing to save a query will result in the need to rebuild the code for the temporary table.
Using the queries completed in the Topic 4 assignment, complete the steps below and address the questions.
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 Microsoft Word document that includes the SQL query code used to explore the database tables and
answer the following questions.
1. Create a basic temporary table from work done in Topic 4, question 5 and call it “MyTempTable.” Write a
SELECT statement to query the new data.
2. Order the results on MyTempTable by SalesOrderID.
3. Add a timestamp field called “DateRan” and update it to the current date.
4. Explain how to drop the table and start over.
5. Explain how the table can be re-added?
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.
Page 13 Grand Canyon University 2022 © Prepared on: Feb 21, 2022APA 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 LopesWrite.
Creating an Entity Relationship Diagram (ERD)
Start Date & Time Due Date & Time Points
Mar 24, 2022, 12:00 AM Mar 30, 2022, 11:59 PM 50
The purpose of this assignment is to create temporary tables and document them using an Entity Relationship
Diagram (ERD).
This assignment uses a GCU-provided virtual machine that will be specified by your instructor.
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 Microsoft 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, as described in the table
below.
Temporary Table Name
Topic 4 Assignment Query
#MyProduct
Find the product ID for the LL Road Frame – Black 60
#MyPriceHistory
Find the listing price of the LL Road Frame – Black 60. Display ProductID, EndDate, and ListPrice.
#MySalesOrderDetail
How many orders have been placed for LL Road Frame – Black 60?
1. Use the skills you have learned and practice writing a query to determine the dates of each sales orders
from a table you have not looked at yet. Create a temporary table called #AllSalesOrderDates for this
information.
2. Use Draw.io 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 Draw.io, Excel data file, and Word document containing the SQL queries and answers to the
questions into a .zip file and submit to your instructor.
Page 14 Grand Canyon University 2022 © Prepared on: Feb 21, 2022APA 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 LopesWrite.
Topic 5 DQ 1
Start Date & Time Due Date & Time Points
Mar 24, 2022, 12:00 AM Mar 26, 2022, 11:59 PM 5
Explain how temporary tables can be used and why this is beneficial to the company. Provide specific
examples to support your ideas. Explain why it is important to clear temporary tables, and discuss what could
happen if this is not done.
Topic 5 DQ 2
Start Date & Time Due Date & Time Points
Mar 24, 2022, 12:00 AM Mar 28, 2022, 11:59 PM 5
There is a division of labor from the business, to the analyst, to the IT department. Explain how having each of
these roles ensures the proper requests are submitted. Would it be easier to have one person handle it all?
Defend your answer using specific examples to justify your reasoning.
Week 5 Participation
Start Date & Time Due Date & Time Points
Mar 24, 2022, 12:00 AM Mar 30, 2022, 11:59 PM 20
Topic 6: Database Joins and Identifying Data Issues
In this topic, students learn the importance of maintaining data integrity when using SQL JOINs to merge data
from multiple tables within SELECT statements.
Objectives:
1. Utilize and execute various statements and joins to construct, manage, and stack data within a database.
2. Analyze join results and data integrity to determine if proper data has been displayed.
Mar 31, 2022 – Apr 6, 2022 Max Points: 130
Resources
Databases Illuminated
Review the following sections from Chapters 3 and 5 in Databases Illuminated:
Page 15 Grand Canyon University 2022 © Prepared on: Feb 21, 20223.5 Relationships3.5.1 Degree of Relationships3.5.2 Attributes of Relationship Sets3.5.3 Cardinality of a Binary
Relationship3.5.4 Showing Cardinalities on an E-R Diagram3.5.5 Participation Constraints3.5.6 Using (min,max)
Notation for Cardinality and Participation3.6 Roles3.7 Existence Dependency and Weak Entities3.8 Sample E-R
Diagram5.4.2 SELECT Using Multiple Tables
SQL Server Join Example
Read “SQL Server Join Example,” by Kadlec, located on the MSSQL Tips website (2016).
https://www.mssqltips.com/sqlservertip/1667/sql-server-join-example/
Assessments
Benchmark – Joins
Start Date & Time Due Date & Time Points
Mar 31, 2022, 12:00 AM Apr 6, 2022, 11:59 PM 100
The purpose of this assignment is to complete two complex queries, join them, and validate the expected
results of the join.
This assignment uses a GCU-provided virtual machine that will be specified by your instructor.
For this assignment, continue to assume the role of 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 Microsoft 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 MySalesOrderDetailbyDate. 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.
Page 16 Grand Canyon University 2022 © Prepared on: Feb 21, 20222. 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 LopesWrite.
Benchmark InformationThis benchmark assignment assesses the following programmatic
competencies:MS Business Analytics1.1: Organize, query, and report data using databases.
Topic 6 DQ 1
Start Date & Time Due Date & Time Points
Mar 31, 2022, 12:00 AM Apr 2, 2022, 11:59 PM 5
Explain the difference between an INNER and OUTER join. Discuss why you would select one over the other
using a specific example.
Topic 6 DQ 2
Start Date & Time Due Date & Time Points
Mar 31, 2022, 12:00 AM Apr 4, 2022, 11:59 PM 5
In the topic assignment, the two temporary tables (#MyPriceHistory and #MySalesOrderDetailbyDate) cannot
be joined in their current state. Explain why this join is not possible. Discuss what you think is missing in order
to make a join work and propose the solution to your peers.
Provide two peers with feedback about the potential answers to this question, and justify why you think one
answer might be more correct than others that are proposed.
Week 6 Participation
Start Date & Time Due Date & Time Points
Mar 31, 2022, 12:00 AM Apr 6, 2022, 11:59 PM 20
Topic 7: Query Optimization and Error Repair
In this topic, students are introduced to several query optimization techniques that can be used to improve
query processing performance.
Objectives:
Page 17 Grand Canyon University 2022 © Prepared on: Feb 21, 20221. Debug and fine tune queries to present specified data.
2. Apply query optimization strategies.
Apr 7, 2022 – Apr 13, 2022 Max Points: 130
Resources
The Baker’s Dozen: 26 Productivity Tips for Optimizing SQL Server Queries (Part 1 of 2)
Read “The Baker’s Dozen: 26 Productivity Tips for Optimizing SQL Server Queries (Part 1 of 2),” by Goff, located
on the Code Magazine website.
http://www.codemag.com/article/1208111
Basic SQL DeBugging
Read “Basic SQL DeBugging,” located on the MariaDB website.
https://mariadb.com/kb/en/mariadb/basic-sql-debugging/
The Baker’s Dozen: 26 Productivity Tips for Optimizing SQL Server Queries (Part 2 of 2)
Read “The Baker’s Dozen: 26 Productivity Tips for Optimizing SQL Server Queries (Part 2 of 2),” by Goff, located
on the Code Magazine website.
http://www.codemag.com/article/1211061
Assessments
Query Optimization and Errors
Start Date & Time Due Date & Time Points
Apr 7, 2022, 12:00 AM Apr 13, 2022, 11:59 PM 100
The purpose of this assignment is to update a previous query and present it in an easily readable format.
This assignment uses a GCU-provided virtual machine that will be specified by your instructor.
For this assignment, continue to assume the role of 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 Microsoft Word document that includes the SQL query code used to explore the database tables and
answer the following questions.
Page 18 Grand Canyon University 2022 © Prepared on: Feb 21, 2022Part 1:
1. Using what you have learned, replicate the data found in #MySalesOrderDetailbyDate and
#MyProductPriceHistory using a single SELECT statement for each query. Refer to the Topic 5 assignment,
“Creating an Entity Relationship Diagram (ERD)” for this information.
2. Modify the queries completed in the Topic 6
assignment and present it in an easy-to-read format. To do this, write the query in a single SELECT
statement 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 LopesWrite.
Topic 7 DQ 1
Start Date & Time Due Date & Time Points
Apr 7, 2022, 12:00 AM Apr 9, 2022, 11:59 PM 5
Describe how complex a query can get by locating an example of a complex AdventureWorks query online.
Post a link to the example in the Main Forum. Include an explanation of what the data tells you.
Comment on the accuracy and interpretation of the posts and data listed by two of your peers.
Topic 7 DQ 2
Start Date & Time Due Date & Time Points
Apr 7, 2022, 12:00 AM Apr 11, 2022, 11:59 PM 5
Using the AdventureWorks database, create your own unique query using at least three tables. Post the query
in the Main Forum. Explain the query and what the data tells you.
Comment on the queries, accuracy, and interpretation of the data listed in posts from two of your peers.
Page 19 Grand Canyon University 2022 © Prepared on: Feb 21, 2022Week 7 Participation
Start Date & Time Due Date & Time Points
Apr 7, 2022, 12:00 AM Apr 13, 2022, 11:59 PM 20
Topic 8: Stored Procedures and Exporting Data
In this topic, students learn how to create and execute SQL Server-stored procedures and Views, which are
used extensively in SQL development to automate specific business logic.
Objectives:
1. Define various types of stored procedures and identify when and how they are used to maximize database
functionality.
2. Create, alter, and drop a stored procedure to capitalize on database efficiencies.
3. Export data from various sources and utilize SQL Server storage structures efficiently.
Apr 14, 2022 – Apr 20, 2022 Max Points: 205
Resources
Databases Illuminated
Read the following sections from Chapter 5 in Databases Illuminated:
5.4.4 Operators for Updating: UPDATE, INSERT, DELETE5.4.5 Creating and Using Views
SQL Server: VIEW
Read ” SQL Server: VIEW,” located on the Tech on the Net website.
https://www.techonthenet.com/sql_server/views.php
Assessments
Stored Procedures
Start Date & Time Due Date & Time Points
Apr 14, 2022, 12:00 AM Apr 20, 2022, 11:59 PM 75
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.
Page 20 Grand Canyon University 2022 © Prepared on: Feb 21, 2022This assignment uses a GCU-provided virtual machine that will be specified by your instructor.
For this assignment, continue to assume the role of 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 Microsoft 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 LopesWrite.
Final Project
Start Date & Time Due Date & Time Points
Apr 14, 2022, 12:00 AM Apr 20, 2022, 11:59 PM 100
The purpose of this assignment is to analyze data and use it to provide stakeholders with potential answers to
a previously identified business problem.
This assignment uses a GCU-provided virtual machine that will be specified by your instructor.
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 a Microsoft document of 250–500 words, share these recommendations and address the following:
1. Summary of the business problem, including the requester who initially brought the problem to you.
2. Summary of the data that was requested and how it 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.
Page 21 Grand Canyon University 2022 © Prepared on: Feb 21, 2022Compile 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 LopesWrite.
Topic 8 DQ 1
Start Date & Time Due Date & Time Points
Apr 14, 2022, 12:00 AM Apr 16, 2022, 11:59 PM 5
In theory, how complex could a View statement get? Discuss whether there would be an advantage to chain
multiple views under another view. Where does it stop? Explain the possibilities, and support your ideas with
specific examples.
Topic 8 DQ 2
Start Date & Time Due Date & Time Points
Apr 14, 2022, 12:00 AM Apr 18, 2022, 11:59 PM 5
Explain how keeping an up-to-date ERD ensures data integrity. If someone looks over the ERD and finds an
error, explain how the error could be fixed and the update/change communicated to other stakeholders.
Discuss the potential backlash that could result from a misreported dataset.
Week 8 Participation
Start Date & Time Due Date & Time Points
Apr 14, 2022, 12:00 AM Apr 20, 2022, 11:59 PM 20
Page 22 Grand Canyon University 2022 © Prepared on: Feb 21, 2022
- Assignment status: Already Solved By Our Experts
- (USA, AUS, UK & CA PhD. Writers)
- CLICK HERE TO GET A PROFESSIONAL WRITER TO WORK ON THIS PAPER AND OTHER SIMILAR PAPERS, GET A NON PLAGIARIZED PAPER FROM OUR EXPERTS
