I. OBJECTIVES
1. Create a new MySQL database.

II. ASSUMPTIONS
In this lab, you will perform some standard commands to find out about the server environment and then create a new database.

III. PROCEDURE
1. For 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.
a. Create 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.
b. Log into a MySQL session at the root level. This will be the default beginning level for all of your iLab sessions.
c. At the mysql> prompt type the following: tee s:\dbm438\labxoutputfile..log where the x is the lab number.
d. Press enter. You should receive a Logging to ‘s:\dbm438\labxoutputfile.log message.
e. To stop writing to the log you simply type notee at the prompt.
f. Unfortunately 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.

NOTE: 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.

2. Now let’s take a few minutes to explore the MySQL server by entering some queries and commands to see what is there.
a. First, type the word help to look at the help menu for MySQL. This shows you some useful shortcuts.
b. Next, execute a query to show the current date and time.
c. Next, execute a query to show the current date and time, the user, and version of MySQL.
d. Next, execute both a command and a query to show which databases are currently available. This will be two separate steps.

3. Now 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.
a. Creating a new database is a simple process. Execute the statement that will create a new database named devrydbm438.
b. Now execute the command and query that you previously used to show which databases are available.

4. Notice that although the new database is listed in the list of databases it still does not show up as the default database.
a. Execute the command that will switch our new database to the default database.
b. Now, execute a query to show what the default database is.

5. Now we have a new default database to work with so let’s do some more exploring to see what else we have.
a. MySQL supports many different character sets. Issue the commands that will show the available character sets and their collations.

6. MySQL 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.
a. Issue 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.

7. MySQL 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.
a. Write and execute the command that will show all of the tables in the INFORMATION_SCHEMA.
b. Next 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.

8. This 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.

9. 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 9-point font formatting it will look just like in the session. This completes this lab.

10. Grading of this lab assignment will be based on the following.

Description Points
Objectives is sufficiently filled out. 5
Steps 2 through 8 were satisfactorily completed. 15
Contents of output file showing session input and results was included in the iLab Report. 15
Conclusions is sufficiently filled out. 5
Total Lab Points 50