{"product_id":"new-full-course-guide-dbm438-database-administration-with-lab-devry-university_id-hex8bshk6dwoa5x","title":"(New Full Course Guide) DBM438 Database Administration with Lab DeVry University","description":"\u003ch1\u003e\n\u003cb\u003e\u003cspan\u003eDBM438-Database Administration with Lab DeVry University \u003c\/span\u003e\u003c\/b\u003e\u003cb\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/b\u003e\n\u003c\/h1\u003e\n\u003ch2\u003e\u003cb\u003e\u003cspan\u003eDBM438 Full Course Study Guide Solution Download\u003c\/span\u003e\u003c\/b\u003e\u003c\/h2\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDBM438 Lab 1 Comparison of MySQL Oracle and SQL Server,\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDBM438 Lab 2 Create a new MySQL Database.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDBM438 Lab 3 General Analysis and Administration of a MySQL Server and Database,\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDBM438 Lab 4 Taking a look at Different Data Storage Issues and Basic Table Management,\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDBM438 Lab 5 Optimize Queries in MySQL and Analyzing the Database,\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDBM438 Lab 6 Creating and Managing Users in the Database,\u003c\/span\u003e\u003c\/p\u003e\n\u003cp\u003eDBM438 Lab 7 Creating and Managing Database Backups\u003c\/p\u003e\n\u003ch2\u003e\n\u003cb\u003e\u003cspan\u003eDBM438 Lab 7 Creating and Managing Database Backups\u003c\/span\u003e\u003c\/b\u003e\u003cb\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/b\u003e\n\u003c\/h2\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eI. OBJECTIVES\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eTake a look at the process of creating and managing database backups and how these backups are used in recovery of the database when it becomes corrupted.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eII. ASSUMPTIONS\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eAt the end of this lab, you should have a better understanding of how both a binary backup of a MySQL database and a text backup of the database works.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eIII. PROCEDURE\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eFor each lab it will be necessary for you to create an output file that will capture all of your commands and work within your MySQL session. The steps for doing this are as follows.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eCreate a folder on the S: drive of your Windows VM named DBM438. This will be where you will save all of your session output files.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eLog into a MySQL session at the ‘root’ level. This will be the default beginning level for all of your iLab sessions.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eAt the mysql\u0026gt; prompt, type the following: tee s:\\dbm438\\labxoutputfile.log, where the ‘x’ is the lab number.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003ePress enter. You should receive a ‘Logging to ‘s:\\dbm438\\labxoutputfile.log’ message.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eTo stop writing to the log you simply type note at the prompt.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eUnfortunately, you cannot copy this file from your VM to your host computer so you will need to open the file, copy the contents, and paste them into the Lab Report document for this lab under the Lab Results section. If you use Courier New 9pt. font, formatting will look just like in the session.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNOTE: If you have to stop your lab session and go back later you will need to create a new file with a different name, otherwise if you use the same file name you will overwrite what you previously recorded. You can later copy and paste the contents of the second file into the first and thus just have one file to turn in.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eReview the lecture in Week 7 for additional information that can be used for this lab.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eThe MySQL architecture supports two primary ways to backup a database; binary and text. To start with we are going to make binary backup of the devrydbm438 database.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eFrom the Windows Command prompt, log in to a MySQL session as the root user. Once logged in, you will need to start an output file.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eBefore you start making copies of the database files for the devrydbm438 database, you need to find out what the most current log file used is so that this can be captured as well. Use the SHOW MASTER STATUS command to find the name of this file and make note of it.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eWe now need to make sure that no changes are made to any of the tables during the backup process. For each of the four tables in the database, you need to lock the table in read-only status and then flush the table. Be sure that you have set the session to use the devrydbm438 database.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNow, you are going to physically copy the files associated with the devrydbm438 database, but first we need a place to put them under your DBM438 folder on the S: drive of your VM create a new folder named backup. Once this is done, exit out of the MySQL session.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eUsing Windows Explorer in your VM, find the data folder under the MySQL folder found in the Program Data folder on your C: drive. This is where all of the files you need to copy can be found. First, find the log file that you made note of using the show master status command and copy that file to your backup folder on the S: drive. Next, copy all of the files in the devrydbm438 folder to your backup folder on the S: drive.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNow you need to start a new MySQL session from the C:\\\u0026gt; prompt in the Windows Command window. Once logged in, start a new output file. After setting the session to use the devrydbm438 database, write a query to select all of the contents from the EMP table. What was the result? Add content to the Conclusions section of the iLab Report stating the results and reason.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eTo complete this part of the lab you need to unlock each table. Once you have done this, issue another select statement on the EMP to display all of the contents and verify that the table is unlocked. You can now exit out of the MySQL session but do not exit out of the Windows Command window.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNow that you have created a binary backup of the database, we are going to use a MySQL utility to make a text backup of the database. This utility generates a SQL text file containing SQL commands which can be used to recover and restore the database. Since this utility works from outside a MySQL session, you will issue the commands for this from the Windows Command C:\\\u0026gt; prompt.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eYou will be using the MYSQLDUMP utility as the root user to make your backup. Although this utility can drill down to an individual table level within a database, we are going to make a backup of the entire devrydbm438 database. From the C:\\\u0026gt; prompt, issue the command to use mysqldump to backup the devrydbm438 database and put the backup file in the backup folder under your S drive. Name the output file dbm438_yourlast name_dump.sql.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003ei. NOTE: Your text shows an example using the % character to indicate the prompt. Also, the text does not show the full syntax for the mysqldump utility which is:\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eC:\\\u0026gt; mysqldump –u user_name –p database_name \u0026gt; “full_path\\filname.sql”\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eUsing this example you will be prompted for the password for the root user.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eFor a large database, this process could take a while to run but for what we are working with it will not. Once the process finishes, go to your folder on the S: drive and verify that the file is there and that the contents look similar to the example in the textbook. You will submit a copy of this file with your Lab Report.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNow we are going to try and recover the devrydbm438 database using the text backup that we just created. Since this not actually a working business database and the data in the tables has not been constantly changing it is a little easier to do, as we will not have to work with the binary log files.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eFirst, start a MySQL session logged in as the root user. Once your session is running start a new output file.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eSet the database for the session to the devrydbm438 database and query the EMP table to show all of the contents.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNow we are going to cause some trouble. First, exit out of your current MySQL session back to the Command Window C:\\\u0026gt; prompt. Now issue the command to shut down the mysql56 server process (NET STOP MYSQL56). Now go into the ProgramData folder in Windows Explorer and find the Data folder under MySQL and the folder for the devrydbm438 database. Find and open the emp.frm folder (you will need to select notepad to open it with), place your curser anywhere in the first two lines of binary data and hit the enter key twice. Now save the file.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eBack at the Windows Command prompt, start the mysql56 service back up (NET START MYSQL56). Once started, start a new MySQL session as the root user and start a new output file.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eSet the session to use the devrydbm438 database and issue a SELECT statement to show all of the data in the table—and now we have a problem, but we can fix it.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eUsing the SOURCE command like you did in Lab 6, run the SQL file that you created earlier using the mysqldump utility. Once it has finished and you are back at the mysql\u0026gt; prompt, issue a SELECT statement to show all the contents in the EMP table. You should now have a recovered and restored database and can stop the current output file and exit the session.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eYou will need to open the output file for this lab on the S: drive of your VM, copy the contents of the file, and paste them into the Lab Report document for this lab under the Lab Results section. If you use Courier New 9pt. font, formatting will look just like in the session. You also need to remember to include either the actual backup file created with mysqldump with your lab report or copy the contents into the lab report. This completes this lab.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eGrading of this lab assignment will be based on the following:\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e[table]\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDescription, Points\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eObjectives section is sufficiently filled out., 5\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eSteps 3 through 6 were satisfactorily completed. ,15\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eContents of output file showing session input and results were included in the iLab Report., 15\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eConclusions section is sufficiently filled out., 5\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eTotal Lab Points:, 50\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e[\/table]\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eYou May Also Like:\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDBM438 Lab 1 Comparison of MySQL Oracle and SQL Server\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDBM438 Lab 2 Create a new MySQL Database\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDBM438 Lab 3 General Analysis and Administration of a MySQL Server and Database\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDBM438 Lab 4 Taking a look at Different Data Storage Issues and Basic Table Management\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDBM438 Lab 5 Optimize Queries in MySQL and Analyzing the Database\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDBM438 Lab 6 Creating and Managing Users in the Database\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003ch2\u003e\n\u003cb\u003e\u003cspan\u003eDBM438 Lab 6 Creating and Managing Users in the Database\u003c\/span\u003e\u003c\/b\u003e\u003cb\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/b\u003e\n\u003c\/h2\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eI. OBJECTIVES\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eTake a look at the process of creating and managing users in the database, as well as how user management helps support security.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eII. ASSUMPTIONS\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eAs a result of this lab, you will have gained a working knowledge of how to create users, grant privileges, and use those privileges to access data in the database.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eIII. PROCEDURE\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eFor each lab it will be necessary for you to create an output file that will capture all of your commands and work within your MySQL session. The steps for doing this are as follows.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eCreate a folder on the S: drive of your Windows VM named DBM438. This will be where you will save all of your session output files.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eLog into a MySQL session at the ‘root’ level. This will be the default beginning level for all of your iLab sessions.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eAt the mysql\u0026gt; prompt, type the following: tee s:\\dbm438\\labxoutputfile.log, where the ‘x’ is the lab number.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003ePress enter. You should receive a ‘Logging to ‘s:\\dbm438\\labxoutputfile.log’ message.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eTo stop writing to the log you simply type note at the prompt.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eUnfortunately, you cannot copy this file from your VM to your host computer, so you will need to open the file, copy the contents, and then paste them into the Lab Report document for this lab under the Lab Results section. If you use Courier New 9pt. font, formatting will look just like in the session.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNOTE: If you have to stop your lab session and then go back later you need to be sure that you are using the same file name for your output file. Doing so will allow MySQL to append the new work to the end of the file thus allowing you to save all of your lab output into one file. If you do end up using two or more file names then you will need to copy and paste the contents of the second file into the first and thus just have one file to turn in.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eReview the lecture in Week 6 for additional information that can be used for this lab.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eBefore starting this lab, you will need to download a file from Doc Sharing that will be run later during the lab. To download this file, do the following:\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eLog into eCollege from your VM and download the DBM438_Lab6.SQL file from Doc Sharing to your DBM438 directory on the S: drive in your VM. You will run this script in a later step.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eStartup your MySQL session from the Windows Command Line window using localhost as the host, the root user and “devrydevry” password. Once you have the MySQL\u0026gt; prompt, be sure to start your output file printing as described at the beginning of this lab. Now you want to set the database to devrydbm438, which is the database that was created in Lab 2.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNow you are going to go through the process of creating several users, granting them certain privileges, and using those users and privileges. During this process it will be necessary to exit your session as one user and start a new one as another user, so be sure to keep track of your output files each time you have to start a new session. Let’s get started.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eAs the root user (you are currently logged in as root), create a user name BOB with a password of ALONG and associate the user with LOCALHOST.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eGrant privileges to Bob that will allow the user to create tables and insert data, drop tables, and select and update data in the devrydbm438 database.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNow write the statement command that will show the grants for user BOB.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eExit out of this session and start a new one as the user BOB (remember to start a new output file). Once you are logged in and have started your new output file, set the database for the session to devrydbm438.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eOnce again, write the statement command that will show the privileges for the user, but remember that you are doing it as the user this time and not as root.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eExit out of this session and start a new one as the root user (remember to start a new output file).\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eBob has forgotten his password, so assign Bob a new password of OLINK.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eGrant user Bob the ability to create a temporary table, also limit the maximum number of connections the user can have per hour to 5.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eOnce again, write the statement command that will show the privileges for user BOB and also write the statement that will show the user resources for this user.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eAs the final step for this part of the lab, you need to run the Lab6_support.sql file that you downloaded. You can do this by using the word SOURCE followed by the path to the file (S:\\DBM438\\DBM438_lab6.sql) and then hitting the ENTER key.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eWe are now going to create a second user, so you will need to exit out of your current session (exit out only to the C:\\ prompt) and reconnect as the root user. Be sure to start your output file once logged in to MySQL).\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eAs the root user (you are currently logged in as root), create a user name JACK with a password of HORNOR and associate the user with LOCALHOST.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eGrant privileges to Jack that will allow the user to create tables and insert data, drop tables, select data, update data, and create a temporary table in the devrydbm438 database.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNow write the statement command that will show the grants for user Jack.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eWrite and execute the query using the mysql.user table to show the host and user that currently exist.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNow exit out of your current session and log back in to MySQL as user Jack. Be sure to start your output file once you are logged in.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eAs user Jack, write a SELECT statement to show all of the data in STUDENT table in the SAMPDB database. What was the result? Add content to the Conclusions section of your Lab Report stating your findings and why this happened.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eAs user Jack, write a SELECT statement to show all of the data in the EMP table. Now write the CREATE statement that will create a temporary table named EmpTemp, based on the EMP table. Now write a SELECT statement to show all of the contents of the EmpTemp table you just created.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eThis concludes your lab. You will need to open the output file(s) for this lab on the S: drive of your VM, copy the contents of the file, and paste them into the Lab Report document for this lab under the Lab Results section. If you use Courier New 9pt. font, formatting will look just like in the session. This completes this lab.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eGrading of this lab assignment will be based on the following:\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e[table]\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDescription, Points\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eObjectives section is sufficiently filled out., 5\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eSteps 3 through 7 were satisfactorily completed., 15\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eContents of output file showing session input and results were included in the iLab Report., 15\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eConclusions section is sufficiently filled out., 5\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eTotal Lab Points:, 50\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e[\/table]\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eYou May Also Like:\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDBM438 Lab 1 Comparison of MySQL Oracle and SQL Server\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDBM438 Lab 2 Create a new MySQL Database\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDBM438 Lab 3 General Analysis and Administration of a MySQL Server and Database\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDBM438 Lab 4 Taking a look at Different Data Storage Issues and Basic Table Management\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDBM438 Lab 5 Optimize Queries in MySQL and Analyzing the Database\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDBM438 Lab 7 Creating and Managing Database Backups\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003ch2\u003e\n\u003cb\u003e\u003cspan\u003eDBM438 Lab 5 Optimize Queries in MySQL and Analyzing the Database\u003c\/span\u003e\u003c\/b\u003e\u003cb\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/b\u003e\n\u003c\/h2\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eI. OBJECTIVES\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eTake a look at various ways to optimize queries in MySQL and various ways that the Data Directory can help in analyzing the database.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eII. ASSUMPTIONS\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eAfter completing this lab, you will have a general understanding of how MySQL handles queries of different types, how these queries can be analyzed, and how, based on this analysis, the query can be optimized.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eIII. PROCEDURE\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eFor each lab, it will be necessary for you to create an output file that will capture all of your commands and work within your MySQL session. The steps for doing this are as follows.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eCreate a folder on the S: drive of your Windows VM named DBM438. This will be where you will save all of your session output files.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eLog into a MySQL session at the ‘root’ level. This will be the default beginning level for all of your iLab sessions.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eAt the mysql\u0026gt; prompt, type the following: tee s:\\dbm438\\labxoutputfile.log, where the ‘x’ is the lab number.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003ePress enter. You should receive a ‘Logging to ‘s:\\dbm438\\labxoutputfile.log’ message.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eTo stop writing to the log you simply type note at the prompt.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eUnfortunately you cannot copy this file from your VM to your host computer, so you will need to open the file, copy the contents, and then paste them into the Lab Report document for this lab under the Lab Results section. If you will use Courier New 9pt. font, formatting will look just like in the session.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNOTE: If you have to stop your lab session and go back later, you need to be sure that you are using the same file name for your output file. Doing so will allow MySQL to append the new work to the end of the file, thus allowing you to save all of your lab output into one file. If you do end up using two or more file names, you will need to copy and paste the contents of the second file into the first, and thus just have one file to turn in.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eReview the lecture in Week 5 for additional information that can be used for this lab. To begin this lab, open up the Windows Command Prompt and change the prompt to C:\\\u0026gt;. Now, start a new MySQL session as the root user on localhost. Once you are logged in, start the output file for this lab. You now need to set up the environment for this lab.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eLog into eCollege and download the DBM438_leebooks.sql file from Doc Sharing.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eSet the Mysql to use the devrydbm438 database.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDrop any and all tables from Lab 4. You will not need to drop the G_DEPT temporary table, as it was dropped automatically when you exited the session for that lab.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eOnce the database is clean, use the SOURCE command to run the DBM438_leebooks.sql script to load the tables and data for this lab.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNow use the SHOW TABLES command to verify that the tables are there (you should have eight tables listed).\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eAll major database architectures employ some form of query optimizer to help make sure that queries are running as efficiently as possible. With the limited database schema that we are working with, some of the support the optimizer offers will be a little difficult to show, but we can still get a good idea.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eTo start, we are going to look at how the EXPLAIN EXTENDED command works with a query. This is how we can get the query to take advantage of the information that the optimizer has already. Write and execute, using EXPLAIN EXTENDED, a simple query that will return data from the book_customer table showing the first name, last name, and city for customers in zip code 32106.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNotice the output (I realize it is a little hard to read and follow)—one thing to note is the number of rows compared to return the one row of data. Also, notice the time it took to run the query. Now, execute the query again by itself and compare the time taken. The optimizer is now using information gained from the first run of the query to better execute the query (your second run of the query should have shown 1 row in set (0.00)).\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNow we are going to create a scenario where we can compare what happens when we can use the ANALYZE process to give the optimizer valuable information before we run a more complicated query, but we need to get a baseline first.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eFirst, you need to construct a join query using the books, book_author, and author that will display the book title and first and last name of the author. Notice the amount of time that it takes to execute this query the first time it successfully runs. Now, execute the query again, and a third time if necessary until you receive a 0.00 sec return. Why do you think the amount of time to execute the query took less time each subsequent time you executed it? Add content to you iLab Report Conclusions section to address your answer.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNow, we need to reset the environment so that we can replicate this process with a slightly different approach. Be sure that you have saved the successful version of your query before you proceed, as you will need it again.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNow we are going to repeat the process in step 5 but use the ANALYZE utility on the tables involved first and then compare the outcome from the first time the query is run.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eWe need to reset the environment, so exit out of your MySQL session back to the C:\\\u0026gt; prompt. Now issue the commands to stop the mysql56 service and then start it back up again. Once it is started, log back into a MySQL session as the root user. Once logged in, start your output file, being sure to use the same file name.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNow, use the ANALYZE utility on the three tables that are being used in the join query you wrote in step 5.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNow execute the actual query and compare the amount of time it took to execute this time compared to what was done in step 5. What do you think caused the difference? Add content to you iLab Report Conclusions section to address your answer.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNow, run the same query and use the EXPLAIN EXTENDED option.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eWe have already looked at some ways that we can affect change on the name of certain objects in the Data Directory of the MySQL architecture. Now we are going to look at how we can actually relocate things related to the directory.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eFor this part of the lab, we are going to make a change to the my.ini file and then show the outcomes of that change. To start, you need to exit out of your current session and then once at the C:\\\u0026gt;, execute the command to stop the mysql56 service.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNow we are going to change the location of the error log for this MySQL server. First, go to the ProgramData folder on your C drive and create a new folder named data2 under the ‘MySQL Server 5.6’ folder. Next, go to the my.ini file that you have used before (should be under C:\\ProgramData\\MySQL\\MySQL Server 5.6). Open the file and find the log-error parameter in the [mysqld] section of the file. You want to change the file name to DBM438.err and include the full file path to the new data2 folder starting from the C drive (hint: use the file path under the parameter datadir= as a guide). Enclose the full path in double quotes and then save the file.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNow go back to the Windows Command window C:\\\u0026gt; prompt and issue the command to start the mysql56 service. Once started, then start a new MySQL session as the root user on localhost. Once logged in, start your output file, being sure to use the same file name.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNow, using the SHOW VARIABLES command, find any variables that reference log_error. This should show the new path to your error log file.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eThis concludes the lab for Week 5. You will need to open the output file for this lab on the S: drive of your VM, copy the contents of the file and then paste them into the Lab Report document for this lab under the Lab Results section. If you use Courier New 9pt. font, formatting will look just like in the session. This completes this lab.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eGrading of this lab assignment will be based on the following:\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e[table]\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDescription, Points\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eObjectives section is sufficiently filled out., 5\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eSteps 3 through 8 were satisfactorily completed., 15\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eContents of output file showing session input and results were included in the iLab Report., 15\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eConclusions section is sufficiently filled out. ,5\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eTotal Lab Points:, 50\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e[\/table]\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eYou May Also Like:\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDBM438 Lab 1 Comparison of MySQL Oracle and SQL Server\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDBM438 Lab 2 Create a new MySQL Database\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDBM438 Lab 3 General Analysis and Administration of a MySQL Server and Database\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDBM438 Lab 4 Taking a look at Different Data Storage Issues and Basic Table Management\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDBM438 Lab 6 Creating and Managing Users in the Database\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDBM438 Lab 7 Creating and Managing Database Backups\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003ch2\u003e\n\u003cb\u003e\u003cspan\u003eDBM438 Lab 4 Taking a look at Different Data Storage Issues and Basic Table Management\u003c\/span\u003e\u003c\/b\u003e\u003cb\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/b\u003e\n\u003c\/h2\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eI. OBJECTIVES\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eTaking a look at different data storage issues and basic table management.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eII. ASSUMPTIONS\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eIn this lab, you will look at how the database stores tables for both the InnoDB and MyISAM storage engines. This information will be used in later labs. You will also look at various options available when creating tables and several different types of tables that MySQL supports.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eIII. PROCEDURE\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eFor each lab it will be necessary for you to create an output file that will capture all of your commands and work within your MySQL session. The steps for doing this are as follows.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eCreate a folder on the S: drive of your Windows VM named DBM438. This will be where you will save all of your session output files.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eLog into a MySQL session at the ‘root’ level from the Windows Command Prompt window. This will be the default beginning level for all of your iLab sessions.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eAt the mysql\u0026gt; prompt type the following: tee s:\\dbm438\\labxoutputfile.log where the ‘x’ is the lab number.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003ePress enter. You should receive a ‘Logging to ‘s:\\dbm438\\labxoutputfile.log’ message.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eTo stop writing to the log, simply type note at the prompt.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eUnfortunately, you cannot copy this file from your VM to your host computer, so you will need to open the file, copy the contents and then paste them into the Lab Report document for this lab under the Lab Results section. If you use Courier New 9pt. font formatting it will look just like in the session.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNOTE: If you have to stop your lab session and go back later, you need to be sure that you are using the same file name for your output file. Doing so will allow MySQL to append the new work to the end of the file, thus allowing you to save all of your lab output into one file. If you do end up using two or more file names, you will need to copy and paste the contents of the second file into the first and thus just have one file to turn in.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eReview the lecture in Week 4 for additional information that can be used for this lab.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eTo begin this lab we want to reset our MySQL server back to the original state that we started with. At the Windows Command C:\\\u0026gt; prompt, execute the command to shut down the MySQL56 service. Now under the C:\\ProgramData folder, find the my.ini initialization file and change the default-storage-engine variable in the file to reflect an INNODB storage engine. Save the file and go back to the Windows Command prompt and issue the command to start the MySQL56 service back up.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNow startup your MySQL session from the Windows Command Line window using localhost as the host, the root user and “devrydevry” password. You also need to start your output file.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eThe two storage engines that we are dealing with in this class are the Innodb and MyISAM storage engine. It is important to understand how each handles table and data storage, so in this section we are going to first look at how this is done.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eWe will start with the INNODB storage engine. Using the SHOW VARIABLES command, find variables that contain INNODB_DATA. You need to use the LIKE delimiter to return just those variables.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNow we need to look at log files. Using the SHOW VARIABLES command, find variables that contain INNODB_LOG. Again, be sure to use the LIKE delimiter. Notice that the innodb_log_group_home_dir has a setting of .\/. Include an entry in your iLab Report Conclusions section explaining what this means (you will probably need to do some research on the internet for this).\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNow, let’s look at the MYISAM engine and see what the differences are. Using the SHOW VARIABLES command again, find variables that contain MYISAM. Add an entry in your iLab Report Conclusions section addressing why the MYISAM engine does not have variables for data or log entries.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNow we are going to look at some basic table management in the MySQL database environment.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eTo begin, set your session to use the devrydbm438 database. Now, create two tables, one named S_EMP and the other named S_DEPT, based on the following specifications and have them associated with the MyISAM storage engine. Remember that your MySQL session by default now uses the InnoDB storage engine, so you will need to add the necessary definition to your CREATE statements to have the two tables use the MyISAM storage engine.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e1\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e2\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e3\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e4\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e5\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e6\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e7\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e8\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e9\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e10\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e11\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e12\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e \u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eTable Name Column Name Data Type Constraint\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eS_DEPT DEPTNO INT(2) PK\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDNAME VARCHAR(14)\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eLOC CHAR(13)\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eS_EMP EMPNO INT(4) PK\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eENAME VARCHAR(10)\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eJOB VARCHAR(9)\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eMGR INT(4) FK References EMPNO of S_EMP\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eHIREDATE DATE\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eSAL NUMBER(7,2)\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eCOMM NUMBER(7,2)\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDEPTNO NUMBER(2) FK References S_DEPT\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eOnce you have successfully created the two tables, use the SHOW TABLES command to list the tables currently in the devrydbm438 database.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eThe INFORMATION_SCHEMA.COLUMNS table contains columns that will allow you to show the table name, column name, data type, and columns that have constraints associated with them. Write and execute the query on this table that will show this data for each of the two tables.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNow we need to look at more specific information about the tables that were created. Using the TABLES table in INFORMATION_SCHEMA, write and execute the query that will show the table name, the engine, the data length, the maximum data length and the auto-incrimination setting for each table in the devrydbm43 database.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eSometimes it is necessary to create a WORK table based on the definition of an existing table. This is the same thing as creating an actual TEMPORARY TABLE, but in this case, it is a temporary table based on the definition of an existing permanent table. Obviously, you could use the CREATE TABLE script from the existing table, but this is not always available. To help overcome this problem, you can create a new table using a SELECT statement on the existing table as the subquery to the CREATE TABLE statement. If the existing table has data in it, you can either allow the data to transfer or you can exempt the data by adding a WHERE clause, like WHERE 1 = 2. The table gets created, only without the data.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eFor this part of the lab, you are going to create a new table based on the definition of the S_DEPT table. Before you do this, though, we want to put some data in the table. Download the DBM438_LOAD_DEPT.SQL file from Doc Sharing and run the script using the SOURCE command to load your S_DEPT table with four records.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNow, create a new table named G_DEPT based on the S_DEPT table. Make sure that it will preserve the data on commit, and make sure that the data in S_DEPT gets transferred to the new table. After the table is created, query the new G_DEPT table to verify that the data is there.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eRemember that this is only a work table, good for a single session. To compare this new type of table to the two that you have already created, query the INFORMATION_SCHEMA.COLUMNS table to show the table name, column name, and data type for the G_DEPT table. What was in the result set of the query? Add an entry in your iLab Report Conclusions section addressing why this query returned the results it did.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNow we are going to go through the process of making changes to the two tables that have been created. Often time business requirements change and thus changes to the database follow. For this case, the changes will be easy as there is no data in the tables, as if there were, the changes would be a little more involved. Let’s get started.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eThe S_DEPT table needs to be able to accommodate a picture of each location. The pictures are .gif format and will require the correct type of column data type to store the photo internally. Add a column to the S_DEPT table named LOCATION_PIC that will satisfy this requirement. Keep in mind that picture files are binary so you need to choose a data type that will accept a binary file.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eThe column name LOC is not very descriptive of the column contents and could cause some confusion. It has been decided to use the full description LOCATION for the column name. Write and execute the statement that will alter the DEPT table and rename the column LOC to LOCATION.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eAdditionally, some of the table’s locations are turning out to be longer names than the column will allow, and it was also decided that the data type needed to be a VARCHAR in place of a CHAR data type. Write and execute the required statement to change the column data type to a VARCHAR with a length of 30.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eThe department name column of the DEPT table contains the names of the departments per location. Most all locations have the same departments, with very few exceptions. This column seems well suited for a Bit Map index. Write and execute the statement that would create a new index for the LOCATION column. Name the index LOC_IDX.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNow use the INFORMATION_SCHEMA.COLUMNS table to show the table name, column name, and data type for the S_DEPT table.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNow use the SHOW INDEX command to show the indexes in the DEPT table.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eThis concludes your lab for this week. You will need to open the output file(s) for this lab on the S drive of your VM, copy the contents of the file and then paste them into the Lab Report document for this lab under the Lab Results section. If you use Courier New 9pt. font, the formatting will look just like in the session. This completes this lab.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eGrading of this lab assignment will be based on the following:\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e[table]\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDescription, Points\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eObjectives is sufficiently filled out., 5\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eSteps 3 through 9 were satisfactorily completed., 15\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eContents of output file showing session input and results was included in the iLab Report., 15\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eConclusions section is sufficiently filled out., 5\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eTotal Lab Points:, 50\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e[\/table]\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eYou May Also Like:\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDBM438 Lab 1 Comparison of MySQL Oracle and SQL Server\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDBM438 Lab 2 Create a new MySQL Database\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDBM438 Lab 3 General Analysis and Administration of a MySQL Server and Database\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDBM438 Lab 5 Optimize Queries in MySQL and Analyzing the Database\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDBM438 Lab 6 Creating and Managing Users in the Database\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDBM438 Lab 7 Creating and Managing Database Backups\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003ch2\u003e\n\u003cb\u003e\u003cspan\u003eDBM438 Lab 3 General Analysis and Administration of a MySQL Server and Database\u003c\/span\u003e\u003c\/b\u003e\u003cb\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/b\u003e\n\u003c\/h2\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eI. OBJECTIVES\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eGeneral analysis and administration of a MySQL server and database.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eII. ASSUMPTIONS\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eIn this lab, you will look at various ways to look at how the server is set up to act and react to various situations. You will then demonstrate how to change those reactions. You will also look at ways to enhance the database architecture.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eIII. PROCEDURE\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eFor each lab it will be necessary for you to create an output file that will capture all of your commands and work within your MySQL session. The steps for doing this are as follows.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eCreate a folder on the S: drive of your Windows VM named DBM438. This will be where you will save all of your session output files.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eLog into a MySQL session at the root level. This will be the default beginning level for all of your iLab sessions.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eAt the mysql\u0026gt; prompt type the following: tee s:\\dbm438\\labxoutputfile..log where the x is the lab number.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003ePress enter. You should receive a Logging to ‘s:\\dbm438\\labxoutputfile.log message.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eTo stop writing to the log you simply type notee at the prompt.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eUnfortunately you cannot copy this file from your VM to your host computer so you will need to open the file, copy the contents and then paste them into the lab report document for this lab under the Lab Results section. If you will use Courier New 9-point font formatting it will look just like in the session.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNOTE: THIS INSTRUCTION IS DIFFERENT FROM YOUR PREVIOUS LAB. This lab is going to require you to shut down and then restart your MySQL server service several times which will require you to start a new output file each time you start a new MySQL session. At the end of the lab you can take the contents of each output file and merge them into one file and copy\/paste the contents of that file into your iLab report document.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eReview the lecture in Week 3 for aspects of the MySQL architecture and how it is set up and how it can be changed.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eMySQL like other database architectures uses variables (Oracle refers to them as parameters) to control how the RDBMS reacts to various things that happen during the course of operations within the database. Now let’s look at some of these variables and how we can change the values that control database operations. First, we are going to revisit the INFORMATION_SCHEMA and look at some very specific things within this schema. NOTE: As we do this you should not ever use a SELECT * query for any of these steps as the results could be totally useless due to the amount of data return. Always be specific as to the columns or specific tables you are looking for.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eExecute a SHOW command on the INFORMATION_SCHEMA looking for variables that have the word buffer in the name. Find the sort_buffer_size and read_buffer_size. The size for both of these is being displayed in bytes.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eExecute a set command to change both of these two variables from 256K to 128K. You will have to use the byte value in your set command to make this change work.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNow close your MySQL session, start a new one along with a new output file, and execute your original SHOW command to verify that your changes took effect. These changes will only be good until the server is rebooted (you will see the effect of this during the next part of the lab).\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNow that we have seen how we can change a single variable we are going to look at how we can change numerous variables at startup of the server. MySQL uses an options file to set variable settings at startup of the server much like Oracle uses an initialization parameter file. The file that MySQL uses is in respect to this course is the my.ini file and can be found in the file path of C:\\ProgramData\\MySQL\\MySQL Server 5.6. Open up the file and then save it as MY.INI.COPY. We are now going to promote some changes to MySQL from the server startup.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eFirst, exit out of your current MySQL session.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eYour MySQL server is controlled by a Windows Service so we first need to shut it down. Open up a Windows command line window by going to lower left windows icon and tying CMD in the Search Programs and Files entry box.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNow issue a CD C:\\ command to change the prompt to the root.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNow issue the command to shut down your MySQL56 server service.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNext, go into your Windows Explorer and find your my.ini file under the path previously mentioned and set up the server to use the MYISAM storage engine. Open the file and find and change the following variables to the values shown below.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003edefault-storage-engine=MYISAM\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003emyisam_max_sort_file_size=50G\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003emyisam_sort_buffer_size=12M\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003ekey_buffer_size=6M\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eread_buffer_size=32K\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eread_rnd_buffer_size=128K\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eSave your ini file once your changes have been made.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNext, go back to your Windows command prompt window and issue the command to start up the MySQL56 service.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNow start up a new MySQL session and a new output file and execute the SHOW commands for the variables that you made the changes to and a select statement on the INFORMATION_SCHEMA.ENGINES table to verify that your changes have taken place.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eAs a final step in looking at how the MySQL server architecture is set up we will look at the different logs used and how we can rename them.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eTo begin, you want to repeat steps 4.a, 4.b, 4.c and 4.d that you just completed.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eAgain, you need to open your MY.INI file and find the section where it lists the various logs. You should see a listing for a General log, the Slow log, the Binary log and the Error log. The naming convention for logs by default uses the computer name as one of the nodes, so for example DLS-NB-MOB075.log would be one a computer named MOB075. For each of the four logs change the computer name node to DBM438. Save and close the file.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNow go back to your Windows command line session and issue the statement to start the MySQL56 service.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNow start up a new MySQL session and a new output file. To verify that your changes took affect issue a SHOW GLOBAL VARIABLES statement looking for variables with log_file in the name.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eThis concludes your lab.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eYou will need to open the output file for this lab on the S drive of your VM, copy the contents of the file, and then paste them into the lab report document for this lab under the Lab Results section. If you will use Courier New 9-point font formatting it will look just like in the session. This completes this lab.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eGrading of this lab assignment will be based on the following.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e[table]\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDescription, Points\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eObjectives is sufficiently filled out., 5\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eSteps 3 through 5 were satisfactorily completed., 15\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eContents of output file showing session input and results was included in the lab report., 15\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eConclusions is sufficiently filled out., 5\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eTotal Lab Points, 50\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e[\/table]\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eYou May Also Like:\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDBM438 Lab 1 Comparison of MySQL Oracle and SQL Server\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDBM438 Lab 2 Create a new MySQL Database\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDBM438 Lab 4 Taking a look at Different Data Storage Issues and Basic Table Management\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDBM438 Lab 5 Optimize Queries in MySQL and Analyzing the Database\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDBM438 Lab 6 Creating and Managing Users in the Database\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDBM438 Lab 7 Creating and Managing Database Backups\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003ch2\u003e\n\u003cb\u003e\u003cspan\u003eDBM438 Lab 2 Create a new MySQL Database\u003c\/span\u003e\u003c\/b\u003e\u003cb\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/b\u003e\n\u003c\/h2\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eI. OBJECTIVES\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eCreate a new MySQL database.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eII. ASSUMPTIONS\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eIn this lab, you will perform some standard commands to find out about the server environment and then create a new database.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eIII. PROCEDURE\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eFor each lab it will be necessary for you to create an output file that will capture all of your commands and work within your MySQL session. The steps for doing this are as follows.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eCreate a folder on the S: drive of your Windows VM named DBM438. This will be where you will save all of your session output files.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eLog into a MySQL session at the root level. This will be the default beginning level for all of your iLab sessions.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eAt the mysql\u0026gt; prompt type the following: tee s:\\dbm438\\labxoutputfile..log where the x is the lab number.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003ePress enter. You should receive a Logging to ‘s:\\dbm438\\labxoutputfile.log message.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eTo stop writing to the log you simply type notee at the prompt.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eUnfortunately you cannot copy this file from your VM to your host computer so you will need to open the file, copy the contents and then paste them into the Lab Report document for this lab under the Lab Results section. If you use Courier New 9-point font formatting it will look just like in the session.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNOTE: If you have to stop your lab session and then go back later you will need to create a new file with a different name, otherwise if you use the same file name you will overwrite what you previously recorded. You can later copy and paste the contents of the second file into the first and thus just have one file to turn in.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNow let’s take a few minutes to explore the MySQL server by entering some queries and commands to see what is there.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eFirst, type the word help to look at the help menu for MySQL. This shows you some useful shortcuts.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNext, execute a query to show the current date and time.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNext, execute a query to show the current date and time, the user, and version of MySQL.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNext, execute both a command and a query to show which databases are currently available. This will be two separate steps.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNow we need to create a new database that we will be able to use for some of the other labs. Just as with many other RDBMS you do not want to be putting a lot of stuff in the root schema (MySQL) or in the SYSTEM Tablespace (Oracle) so we need a new place to work.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eCreating a new database is a simple process. Execute the statement that will create a new database named devrydbm438.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNow execute the command and query that you previously used to show which databases are available.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNotice that although the new database is listed in the list of databases it still does not show up as the default database.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eExecute the command that will switch our new database to the default database.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNow, execute a query to show what the default database is.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNow we have a new default database to work with so let’s do some more exploring to see what else we have.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eMySQL supports many different character sets. Issue the commands that will show the available character sets and their collations.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eMySQL uses a variety of storage engines, each having pros and cons over the other. Now we are going to see what we are working with.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eIssue the command that will show which storage engine is the default and which ones are available. We will come back to things more specific to storage engines in a later lab.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eMySQL has several schemas that can be used to gain general knowledge about the database. We have already used some of the tables from the INFORMATION_SCHEMA like character_set and collation, but now let’s look at just what is in this schema.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eWrite and execute the command that will show all of the tables in the INFORMATION_SCHEMA.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eNext execute the command that will describe the table in the INFORMATION_SCHEMA schema that is used to store information about the files used to store tablespace data.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eThis concludes our initial exploration of the MySQL VM you will be using for these labs. You can now enter NOTEE at the command line to stop writing to your output file and EXIT to exit your MySQL session.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eYou will need to open the output file for this lab on the S drive of your VM, copy the contents of the file and then paste them into the lab report document for this lab under the Lab Results section. If you use Courier New 9-point font formatting it will look just like in the session. This completes this lab.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eGrading of this lab assignment will be based on the following.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e[table]\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDescription, Points\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eObjectives is sufficiently filled out., 5\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eSteps 2 through 8 were satisfactorily completed., 15\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eContents of output file showing session input and results was included in the iLab Report., 15\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eConclusions is sufficiently filled out., 5\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eTotal Lab Points, 50\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e[\/table]\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eYou May Also Like:\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDBM438 Lab 1 Comparison of MySQL Oracle and SQL Server\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDBM438 Lab 3 General Analysis and Administration of a MySQL Server and Database\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDBM438 Lab 4 Taking a look at Different Data Storage Issues and Basic Table Management\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDBM438 Lab 5 Optimize Queries in MySQL and Analyzing the Database\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDBM438 Lab 6 Creating and Managing Users in the Database\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDBM438 Lab 7 Creating and Managing Database Backups\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003ch2\u003e\n\u003cb\u003e\u003cspan\u003eDBM438 Lab 1 Comparison of MySQL Oracle and SQL Server\u003c\/span\u003e\u003c\/b\u003e\u003cb\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/b\u003e\n\u003c\/h2\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eI. OBJECTIVES\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eCompare MySQL, Oracle, and SQL Server database architectures.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eII. ASSUMPTIONS\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eIn this lab you will create a comparison document listing elements of the three database architectures and how they differ.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eIII. PROCEDURE\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDownload the DBM438_W1_iLab_Report document from Doc Sharing which has the initial template for your comparison. You will expand this template as you go based on the number of areas you find to compare and the data that you enter.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eReview the lecture in Week 1 for details on some of the differences between the three databases to get you started.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eUse the resources in the Webliography in the course shell along with any other resources you can find to gather your information.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eTo achieve a maximum grade for this lab you must have a minimum of 15 areas of comparison. The template has five to get you started.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eGrading of this lab assignment will be based on the following.\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e[table]\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDescription, Points\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eObjectives is sufficiently filled out., 5\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDocument contained a viable comparison (at least 15)., 25\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eConclusions is sufficiently filled out., 5\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDocument was free of spelling and grammar issues., 5\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eOverall quality of the document was present., 10\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eTotal Lab Points, 50\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e[\/table]\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003e \u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eYou May Also Like:\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDBM438 Lab 2 Create a new MySQL Database\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDBM438 Lab 3 General Analysis and Administration of a MySQL Server and Database\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDBM438 Lab 4 Taking a look at Different Data Storage Issues and Basic Table Management\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDBM438 Lab 5 Optimize Queries in MySQL and Analyzing the Database\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDBM438 Lab 6 Creating and Managing Users in the Database\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e\n\u003cp class=\"MsoNormal\"\u003e\u003cspan\u003eDBM438 Lab 7 Creating and Managing Database Backups\u003c\/span\u003e\u003cspan\u003e\u003c\/span\u003e\u003c\/p\u003e","brand":"Take My Online Class","offers":[{"title":"Default Title","offer_id":53394388222227,"sku":null,"price":69.99,"currency_code":"USD","in_stock":true}],"thumbnail_url":"\/\/cdn.shopify.com\/s\/files\/1\/0966\/3794\/4083\/files\/DBM438FullCourseSolutionsCapture4.png?v=1769725257","url":"https:\/\/takemyonlineclass.store\/products\/new-full-course-guide-dbm438-database-administration-with-lab-devry-university_id-hex8bshk6dwoa5x","provider":"Take My Online Class","version":"1.0","type":"link"}