IT2230 Full Course (August 2018)

Unit 1 discussion

DQ1 First Attempts at Working With the Client and MariaDB

Work through at least some of the SQL commands in Chapter 3 of Dyer’s Learning MySQL and MariaDB (you will need to run all of them for part 1 of the assignment). Discuss your initial experience with working with MariaDB in the command line environment. Summarize what you have tried and describe your experience. Be sure to use terminology from the study assignment correctly. What issues did you run into and what questions do you have at this point?

DQ2 Database System Life Cycle

The Database System Life Cycle, otherwise known as the System Development Life Cycle (SDLC), consists of five important steps:

Investigation.

System Design.

Development.

Execution.

Maintenance.

Using the library or the Internet, research the Database System Life Cycle. Find at least two references to it. Note that the names and wording in the steps of the life cycle may superficially vary between different sources. Focus on understanding the concepts presented, rather than the language.

Summarize each step and describe how they work together.

Explain how you have used, or can use, the cycle in another context. For example, how can these steps be used when buying a car or planning a trip?

Use a table to show parallels between developing a database and performing the task you chose.

Submit your response in the discussion area by Thursday evening. For the table, you may either submit a Word document or paste an HTML table into the discussion. Refer to the Discussion Participation Scoring Guide for posting expectations. Use APA format for all references.

Response Guidelines

Were your peers’ explanations of the life cycle clear?

Were there areas you found difficult to understand?

What suggestions do you have for your peers in their explanations?

What are your thoughts about their comparisons? Were their comparisons clear?

 

Unit 2 Discussion

Data Gathering and Normalization

Assume that you have been hired to develop a database to track volunteers who work and raise funds for a local community organization. Discuss data gathering process for the volunteer organization. Discuss how to approach the design of the tables and normalize the data acquired.

 

Unit 3 Discussion

Database Backup Policy and Process

Discuss backup policy and backup process. Develop a backup policy for the community organization’s volunteer database and describe the backup process.

 

Unit 4 Discussion

Using JOIN Queries to Combine Data From DB Tables

Discuss the use of joins and subqueries to produce more complex pictures of the data. Describe at least three JOIN queries that will provide useful reports from the data in the volunteer database.

 

Unit 5 Discussion

DQ1 Working With SQL Functions

Discuss how you will use some of the SQL functions you have read about in queries to report on the data in the volunteer database.

DQ2 Reflection: Building the Volunteer Database

Reflect on the process of building the volunteer database that you have worked through over the last few weeks. Relate this development to the database life cycle that you investigated for the discussion in Unit 1.

 

 

 

Unit 1 Assignment 1

Connecting to the Database and First Steps With SQL

For this assignment, you connect to MariaDB and get hands-on experience with the command line client and some common SQL commands.

Part 1

At the prompt, enter Password as the password to log in as the database server’s root user. Run the SQL commands presented in Chapter 3 of Dyer’s Learning MySQL and MariaDB. To document your progress, take at least 5 screen shots showing your progress and submit them in a Word document.

Do not worry if you do not understand the SQL statements completely. The important thing is to start getting perspective on what the language is like and the different kinds of common commands.

Part 2

Do exercises 1–5 at the end of Chapter 3 of Dyer’s Learning MySQL and MariaDB. Please refer to the assignment resources for material to help you with this portion of the assignment. Save the SQL commands and results to a text file for submission. See the resource document on using the mysql command line client’s tee function to record commands and results. Submit this SQL and the results as part of the assignment.

Resources

Connecting to the Database and First Steps With SQL Scoring Guide.

 

Unit 2 Assignment 1

Creating the Initial Table for the Volunteer Database

Part 1

Refer to the document with sample data for normalization in the resources. Clean up the sample data provided and write SQL INSERT statements to insert the information into the table described in that document. Create the table in your test database and test your normalization and SQL by running the INSERT statements. Submit a text file with the SQL INSERT statements for the cleaned up data. Use screen shots to document the successful insertion of the data.

Part 2

Through the remaining assignments in the course, you will be creating a simple database for tracking information about volunteers working and raising money for a community organization. This assignment requires you to create the initial table, called Person, to hold basic information about individual volunteers. You will be refining the design and building the database in the assignments for upcoming units.

Think about the design of the table to hold a volunteer’s ID number, name, mailing address, phone number, email address, and three more fields of your choice. In a very brief paper (2–3 pages) describe your design for this table. Explain your decisions about the structure of the table and data types used. Discuss how you would go about gathering the information that you need for putting the volunteer database together and how that fits into the database life cycle.

Part 3

Write a SQL table creation statement and run it to create the Person table in your database (as discussed in Part 2 above). After creating the table, write and run SQL statements to insert rows in the table for 10 volunteers. Save your SQL to a text file and document the process with screen shots.

Part 4

Write and run three different queries that pull data from the Person table and use ORDER BY to sort the rows in three different ways. Include the queries and the corresponding results in a text file. Use screen shots to document this part of the assignment.

Resources

Creating the Initial Table for the Volunteer Database Scoring Guide.

Normalizing Data for Data Entry

 

 

Unit 3 Assignment 1

Database Backup and Modification

Part 1

Use the mysqldump command line tool to backup the data in your database.

To access the mysqldump tool, start the mysql client command line tool.

Use the quit command to exit to the system command prompt.

Run mysqldump and direct the output of the backup to an appropriately named file.

Create a new database and restore the database from the backup to the new database. Submit a screen shot to document the restoration of the data to a new database.

Part 2

Create 3 new tables for your volunteer database to hold the street address, phone number, and email address for each volunteer. Insert the relevant data for the volunteers in these tables. Modify the Person table to remove the columns for the data moved to separate tables.

Part 3

In a brief (2–3-page) paper, discuss why the tables created above for addresses, phone numbers, and email addresses are a better way to organize the data than having them in the Person database. Describe the structure of the tables that you added and how these tables are related to the Person table.

Resources

Database Backup and Modification Scoring Guide.

 

Unit 4 Assignment 1

Querying a Database Using Joins and Subqueries

Part 1

Perform a backup of the database using mysqldump before continuing. Do not overwrite the previous backup. There is no need to restore from the backup unless you run into problems in the following parts of the assignment.

Part 2

Add additional tables to the volunteer database to track hours worked per week by the volunteers and amounts of funds raised. Remember to design the tables with appropriate primary and foreign keys so you can relate these records to volunteers in the Person table. Each table should include a column to hold a date to identify the start of the week. Insert data into these tables (at least 10 rows). Submit SQL statements with this assignment. Include a screen shot showing the successful insertion of the rows into the tables.

Part 3

Create 3 queries using SQL JOINs involving two or more tables to display information for all volunteers. Submit the text of the queries and results, in addition to screen shots when you turn in the assignment.

Part 4

Write a brief paper describing the tables added in Part 2. Describe the structure of the tables including the column types and the keys (primary and foreign). Discuss the role of the keys in a SQL JOIN like the ones you have written for Part 3 of this assignment.

Resources

Querying a Database Using Joins and Subqueries Scoring Guide.

 

 

Unit 5 Assignment 1

SQL Functions

Part 1

As part of developing a Web application to track volunteer activities, create a table for the volunteer database to store user names for the volunteers to log in to the system. Use an INSERT INTO statement with a subquery containing a SQL string function to create user names for the volunteers based on their legal names and insert them into the new table. Submit the SQL to create the new table, the SELECT INTO query, and the result of a SELECT query to show the contents as part of this assignment.

Part 2

Using SQL aggregate functions such as COUNT(), MIN(), and MAX(), write three queries to report on various aspects of the organization’s volunteers. Submit the text of the queries and the results as part of this assignment.

Part 3

Write a final report on the volunteer database. Identify the key components of the database and discuss how the database life cycle has progressed through the phases of investigation, system design, development, execution, and maintenance. Focus on the correct use of terminology and writing clearly.

Resources

SQL Functions Scoring Guide.