ITS410: DATABASE MANAGEMENT

ITS410: DATABASE MANAGEMENT

Credit Hours: 3

Contact Hours: This is a 3-credit course, offered in accelerated format. This means that 16 weeks of material iscovered in 8 weeks. The exact number of hours per week that you can expect to spend on each course will vary based upon the weekly coursework, as well as your study style and preferences. You should plan to spend 14-20 hours per week in each course reading material, interacting on the discussion boards, writing papers, completing projects, and doing research.

COURSE DESCRIPTION AND OUTCOMES

COURSE DESCRIPTION:

This course teaches students to design, implement, and use database management systems. Students gain a working knowledge of available software packages, concepts of query languages, software integration services, and security considerations. Students will also learn fundamentals of structured query language (SQ ) in developing common queries and reports.

Note: Access to a Windows-based operating system is required for this class. Recommended Prior Course:

ITS310 or ITS315; ITS320.

COURSE OVERVIEW:

Relational database management allows organizations to manipulate large amounts of data in order to make strategic decisions. This course provides database administrators, programmers, and other newcomers to MySQL with the knowledge and skills needed to install, configure, and program a MySQL database. This course also provides a basic understanding of Structured Query Language, or SQL, programming

COURSE LEARNING OUTCO	ES:
1.	Install and configure	ySQL.
2.	Store data in	ySQL.
3.	Select and modify data stored in   ySQL tables.
4.	Connect to	ySQL from a Python script.
5.	Improve MySQL database query performance.

P RTICIP  TION & TTEND  NCE

Prompt and consistent attendance in your online courses is essential for your success at CSU-Global Campus. Failure to verify your attendance within the first 7 days of this course may result in your withdrawal. If for some reason you would like to drop a course, please contact your advisor.

Online classes have deadlines, assignments, and participation requirements just like on-campus classes. Budget your time carefully and keep an open line of communication with your instructor. If you are having technical problems, problems with your assignments, or other problems that are impeding your progress, let your instructor know as soon as possible.

COURSE MATERIALS

Textbook Information is located in the CSU-Global Booklist on the Student Portal.

COURSE SCHEDULE

Due Dates

The Academic Week at CSU-Global begins on Monday and ends the following Sunday.

• Discussion Boards: The original post must be completed by Thursday at 11:59 p.m. MT and peerresponses posted by Sunday 11:59 p.m. MT. Late posts may not be awarded points.

• Opening Exercises: Take the Opening Exercise before reading each week’s content to see which areasyou will need to focus on. You may take these exercises as many times as you need. The Opening Exercises will not affect your final grade.

• Mastery Exercises: Students may access and retake Mastery Exercises through the last day of class untilthey achieve the scores they desire.

• Critical Thinking: Assignments are due Sunday at 11:59 p.m. MT.

WEEKLY READING AND ASSIGNMENT DETAILS

MODULE 1

Readings

• Read the following sections in Jump Start MySQL:o Getting Started with MySQL

o  Storing Data

Opening Exercise (0 points)

Discussion (25 points)

Mastery Exercise (10 points)

Critical Thinking (60 points)

Choose one of the following two assignments to complete this week. Do not do both assignments. Identify your assignment choice in the title of your submission.

Option #1: Configure a ySQL Database

Download the world-x database from the following MySQL website: http://dev.mysql.com/doc/index-other.html

1. Create a database user named jumpwith password
2. Load the world-xdatabase into your MySQL database instance.
3. Grant create, drop, alter, insert, update, select, delete, and indexprivileges for user jump on the world-x
4. ubmit a 2- to 3-page Microsoft Word document containing a screen shot of the tables in the world-x
5. Provide brief explanation of the MySQL grant command. How is this command used to implement database security?

Your paper should be 2-3 pages in length and conform to CSU-Global Guide to Writing and APA. Include at least two scholarly references in addition to the course textbook. The CSU-Global Library is a good place to find these references.

Option #2: Configure a MySQL Database

Download the classicmodels database from the following MySQL website: http://www.mysqltutorial.org/mysql-sample-database.aspx

1. Create a database user named jumpwith password
2. Load the classicmodelsdatabase into your MySQL database instance.
3. Grant create, drop, alter, insert, update, select, delete, and indexprivileges for user jump on the classicmodels
4. Submit a 2- to 3-page Microsoft Word document containing a screen shot of the tables in the world-x
5. Provide brief explanation of the MySQL grant command. How is this command used to implement database security?

Your paper should be 2-3 pages in length and conform to CSU-Global Guide to Writing and APA. Include at least two scholarly references in addition to the course textbook. The CSU-Global ibrary is a good place to find these references.

MODULE 2

Readings

• Read the following sections in Jump Start MySQL:o Storing Data

• Creating Tables

• Data Types and Storage Requirements

• MySQL/Language/Data Types. Retrieved fromhttps://en.wikibooks.org/wiki/MySQL/Language/Data Types.

Opening Exercise (0 points)

Discussion (25 points)

Mastery Exercise (10 points)

Critical Thinking (60 points)

Choose one of the following two assignments to complete this week. Do not do both assignments. Identify your assignment choice in the title of your submission.

Option #1: Changing a ySQL Table Structure

Using the world x database you installed in odule 1 and your ySQL Workbench, create a new table named “independence” with the following attributes (columns):

• field named “id” which has data type auto increment,

• field named “country name” which has data type varchar(50), and

• field named “independence date” which has type “date.”

fter you create the table, run the following SQL command:

IN ERT INTO independence(contry name, independence date)

VALUE (‘United  tates’,’1776-07-04’)

ubmit a 1-page Microsoft Word document that shows the following:

• The QL command you used to create the “independence” table and a list of the rows in the “independence” table.

• Explain the purpose of the field named “id.” Why do you not have to include it in an insert statements? How would change the incrementing value to a number greater than one?

Option #2: Changing a MySQL Table Structure

Using the classicmodels database you installed in Module 1 and your MySQL Workbench, add a JSON field named “customers_contacts” to the customers table with the following keys:

• customerNumber,
• contactLastName, and

Write a SQL statement to insert at least three values for the “customers contacts” field. Use data from the customers table. If you need a hint on how to write the SQL insert statement for a JSON data type, refer to theexamples in http://www.mysqltutorial.org/mysql-json/.

Submit a 1-page Microsoft Word document that shows the following:

• The SQL command you used to insert data into the “customers contacts” field and a listing of rows from the customertable where the “customers contacts” column has data.
• What is JSON? What are the advantages of using JSON as a field type? What are the disadvantages of using JSON as a field type?

MODULE 3

Readings

• Read the following section in Jump Start MySQL:o Retrieving and Updating Data
• MySQL/Language/Queries/Select. Retrieved fromhttps://en.wikibooks.org/wiki/MySQL/Language/Queries#SELECT

Opening Exercise (0 points)

Discussion (25 points)

Mastery Exercise (10 points)

Critical Thinking (60 points)

Choose one of the following two assignments to complete this week. Do not do both assignments. Identify your assignment choice in the title of your submission.

Option #1: Performing an Advanced ySQL Query

Using the world x database you installed in odule1, list the codes for countries where English is spoken by more than 30% of the population. Submit your query and query results in a Word file.

In your document, suggest another query where you list the country codes for countries where English is spoken by less than 30% of the population. Be sure to identify the literary sources you used to look up any SQL syntax you used to formulate your query.

Option #2: Performing an dvanced MySQL Query

Using the classicmodels database you installed in Module 1, list the office codes, phone numbers, and addresses located in an Francisco, Paris, Sydney, and London. Sort your results by office code.

ubmit your query and query results in a text file.

In your document, suggest another query where you list the same information cities other than San Francisco, Paris, ydney, and London. Be sure to identify the literary sources you used to look up any SQL syntax you used to formulate your query.

MODULE 4

Readings

• Read the following section in Jump Start MySQL:o Working with Multiple Tables
• MySQL/Language/Queries/Joins. Retrieved fromhttps://en.wikibooks.org/wiki/MySQL/Language/Queries#Joins

Opening Exercise (0 points)

Discussion (25 points)

Mastery Exercise (10 points)

Critical Thinking (60 points)

Choose one of the following two assignments to complete this week. Do not do both assignments. Identify your assignment choice in the title of your submission.

Option #1: Working with MySQL Table Joins

Using the world x database you installed in Module 1, list the counties and the capitals of each country. Modify your query to limit the list to those countries where less than 30% of the population speaks nglish. Be sure to identify the literary sources you used to look up any SQL syntax you used to formulate your query.

Submit your query and query results Word file.

Option #2: Working with MySQL Table Joins

Using the classicmodels database you installed in Module 1, list the customers who have not placed any orders.

Sort your results by customer number.

• Modify your query to list customers who have placed fewer than five orders.

• Submit your query and query results in a Word file.

MODULE 5

Readings

• Read the following section in Jump Start ySQL:

• Retrieving and Updating Data
  • ggregate Functions and Grouping

• MySQL/Language/Queries/Subqueries. Retrieved fromhttps://en.wikibooks.org/wiki/ ySQL/Language/Queries#Subqueries

Opening Exercise (0 points)

Discussion (25 points)

Mastery Exercise (10 points)

Critical Thinking (65 points)

Choose one of the following two assignments to complete this week. Do not do both assignments. Identify your assignment choice in the title of your submission.

Option #1: Aggregating MySQL Table Data

Using the world x database you installed in Module 1, list the languages spoken in each country. Order your results by the most popular language. Submit your query and query results in a Word file. Cite any sources you used to research the syntax your needed to formulate your query.

Option #2: Aggregating MySQL Table Data

Using the classicmodels database you installed in Module 1, list the total sales for years after 2003. Submit your query and query results in a Word file. Cite any sources you used to research the syntax your needed to formulate your query.

Portfolio Milestone (25 points)

Choose one of the following two assignments to complete this week. Do not do both assignments. Identify your assignment choice in the title of your submission.

Portfolio Milestone Option #1

Make appropriate corrections to the assignments you submitted in Modules 3 and 4 (Critical Thinking Assignments). Resubmit your rework from Modules 3 and 4 with all outlined corrections.

Portfolio Milestone Option #2

Make appropriate corrections to the assignments you submitted in Modules 3 and 4 (Critical Thinking Assignments). Resubmit your rework from Modules 3 and 4 with all outlined corrections.

MODULE 6

Readings

• Read the following section in Jump Start MySQL:o Connecting from Code

• Connecting from  ython with Connector/ ython

• 5.1 Connecting to MySQL using Connector/ ython. Retrieved fromhttps://dev.mysql.com/doc/connector-python/en/connector-python-example-connecting.html

Opening Exercise (0 points)

Discussion (25 points)

Mastery Exercise (10 points)

Critical Thinking (65 points)

Choose one of the following two assignments to complete this week. Do not do both assignments. Identify your assignment choice in the title of your submission.

Option #1: Integrating ySQL Table Data with a Python Script

In this assignment, you will write a Python script to increase the population of each city in the world x database by 10% (rounded).

First, add a new column to the world x city table using the following MySQL command:

• LTER T BLE `world x`.`city` DD COLU N `Population` DOUBLE NULL DEFAULT NULL AFTER `Info`;

The existing population data are stored as JSON datatype in the city table in a field named Info. You learned about JSON data types in Module 2. To obtain the population data from the Info column in the city table, run the following My QL command:

• select info->’$.Population’ from city

Your Python script should do the following:

• Copy the population data from the Infocolumn into the Population column,

• Increase the population by 10% in the Populationcolumn, and

• List the population before the increase and the population after it was increased.

Submit your Python script and Python script output as two separate text files included in a zip file. Submit your zip file to the Module 6 folder. Provide a detailed technical report on the steps needed to integrate Python and MySQL. In addition, provide details on how you iterate through a JSON field in a Python script.