I. OBJECTIVES
1. Taking a look at different data storage issues and basic table management.

II. ASSUMPTIONS
In 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.

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 from the Windows Command Prompt window. 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, simply type note 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 9pt. font formatting it will look just like in the session.

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

1. Review the lecture in Week 4 for additional information that can be used for this lab.

2. To begin this lab we want to reset our MySQL server back to the original state that we started with. At the Windows Command C:\> 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.

3. Now 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.

4. The 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.
a. We 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.
b. Now 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).
c. Now, 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.

5. Now we are going to look at some basic table management in the MySQL database environment.
a. To 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.

Table Name Column Name Data Type Constraint
S_DEPT DEPTNO INT(2) PK
DNAME VARCHAR(14)
LOC CHAR(13)

S_EMP EMPNO INT(4) PK
ENAME VARCHAR(10)
JOB VARCHAR(9)
MGR INT(4) FK References EMPNO of S_EMP
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2) FK References S_DEPT

b. Once you have successfully created the two tables, use the SHOW TABLES command to list the tables currently in the devrydbm438 database.
c. The 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.
d. Now 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.

6. Sometimes 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.
a. For 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.
b. Now, 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.
c. Remember 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.

7. Now 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.
a. The 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.
b. The 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.
c. Additionally, 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.
d. The 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.
e. Now use the INFORMATION_SCHEMA.COLUMNS table to show the table name, column name, and data type for the S_DEPT table.
f. Now use the SHOW INDEX command to show the indexes in the DEPT table.

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

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

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