Hi,

 

Please help with Lab 2 for course DBM449.  I started the lab, but I'm stuck on Part A - Step 12. 

 

Can you help?  

 

Can you please complete the lab?  

 

All questions have to be answered and screen shots provided with a log file.  I was running the commands from the Command Prompt using MySQL. 

 

Thank you,

 

mysql> #3 check schema and database model from lab#1mysql> SHOW DATABASES;+--------------------+| Database|+--------------------+| information_schema || dbm449lab1|| devrydbm438|| devrystudent|| mysql|| newbaseball|| newtemp|| newtempstat|| performance_schema || sakila|| sampdb|| sys|| tempstatinc|| test|| world|+--------------------+15 rows in set (0.00 sec)mysql> use dbm449lab1;Database changedmysql> EXPLAIN-> SELECT * FROM COURSE;+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+| id | select_type | table| partitions | type | possible_keys | key| key_len| ref| rows | filtered | Extra |+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+|1 | SIMPLE| COURSE | NULL| ALL| NULL| NULL | NULL| NULL |5 |100.00 | NULL|+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+1 row in set, 1 warning (0.00 sec)mysql> #4.Execute the following SQL query.mysql> # we can see the result is the same as displayed in Figure 1.mysql> #6 Annotate the EXPLAIN output. I have defined what each of the fieldsmean as well as the implications of the results obtained. I will include theresults of this step in the lab report.mysql> #7 CHECK POINT QUESTION answeredmysql> #8 Execute the following SQL querymysql> EXPLAIN-> SELECT * FROM COURSE-> ORDER BY COURSE_CODE;+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------+| id | select_type | table| partitions | type| possible_keys | key|key_len | ref| rows | filtered | Extra |+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------+|1 | SIMPLE| COURSE | NULL| index | NULL| PRIMARY | 24| NULL |5 |100.00 | NULL|+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------+1 row in set, 1 warning (0.00 sec)mysql> #9 CHECK POINT QUESTIONmysql> # the check point question has been answered and will be included in thelab report

#DBM449#Week 1 Lab#Step 1 - Connect to MySQL and Create the SchemaCREATE SCHEMA IF NOT EXISTS DBM449LAB1;#Step 2 - Initialize Tablesuse DBM449LAB1;#running the scriptSET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';-- ------------------------------------------------------- Table `CLIENT`-- -----------------------------------------------------DROP TABLE IF EXISTS `CLIENT` ;CREATE TABLE IF NOT EXISTS `CLIENT` (`CLIENT_NO` CHAR(8) NOT NULL,`CLIENT_COMPANY` VARCHAR(35) NOT NULL,`CLIENT_NAME` VARCHAR(35) NOT NULL,`CLIENT_EMAIL` VARCHAR(35) NULL,`CLIENT_PROGRAM` CHAR(3) NOT NULL,`CLIENT_SCORE` DECIMAL(10,0) NOT NULL,PRIMARY KEY (`CLIENT_NO`))ENGINE = InnoDB;-- ------------------------------------------------------- Table `COURSE`-- -----------------------------------------------------DROP TABLE IF EXISTS `COURSE` ;CREATE TABLE IF NOT EXISTS `COURSE` (`COURSE_CODE` CHAR(8) NOT NULL,`COURSE_NAME` VARCHAR(35) NOT NULL,`COURSE_DATE` DATE NOT NULL,`COURSE_INSTRUCTOR` VARCHAR(35) NOT NULL,`COURSE_LOCATION` VARCHAR(20) NOT NULL,PRIMARY KEY (`COURSE_CODE`))ENGINE = InnoDB;-- ------------------------------------------------------- Table `COURSE_ACTIVITY`-- -----------------------------------------------------DROP TABLE IF EXISTS `COURSE_ACTIVITY` ;CREATE TABLE IF NOT EXISTS `COURSE_ACTIVITY` (`ACTIVITY_CODE` CHAR(8) NOT NULL,`CLIENT_NO` CHAR(8) NOT NULL,`COURSE_CODE` CHAR(8) NOT NULL,`GRADE` CHAR(1) NULL,`INSTR_NOTES` VARCHAR(50) NULL,PRIMARY KEY (`ACTIVITY_CODE`))ENGINE = InnoDB;-- ------------------------------------------------------- Table `CORP_EXTRACT1`-- -----------------------------------------------------

Laboratory ProceduresDeVry UniversityCollege of Engineering and Information SciencesI.OBJECTIVES1.Understand and become familiar with fundamentals of DBMS performance optimization,operation of the query optimizer, and analysis and tuning commands: EXPLAIN,ANALYZE, and OPTIMIZE.II.PARTS LIST1.EDUPE Omnymbus Environment (https://devry.edupe.net:8300) and/or2.MySQL (dev.mysql.com/downloads)III.PROCEDURE, Part A—The EXPLAIN CommandBy now you have set up either the Omnymbus environment or the MySQL Server Communityenvironment or both. You may do the labs in this class on your own computer equipped withMySQL or you may use the MySQL environment hosted by the vendor Omnymbus.You mayeven use both.Lab Procedure (common to both environments):1.CHECKPOINTQUESTION:An essential component of modern relational databasemanagement systems is thequery optimizer. Explain what the query optimizer is, anddescribe the decision making process the query optimizer uses in order to automaticallygenerate efficient query execution. Based on your explanation, it should be clear to youthat the query optimizer can only work with the tools that you, the database architect,provide. For example, the query optimizer cannot make use of an index that woulddramatically speed query results, if you have not created the index. Nor can the queryoptimizer automatically inform you of this serious omission. However, there are othertools, such as the EXPLAIN, ANALYZE, and OPTIMIZE commands which will providethe information you need in order to detect and address the need for indexes, objects, ormodifications in the data model which can dramatically improve DBMS performance.The query optimizer is responsible for taking the most efficient course to achieve your results. Ittakes into considerations such as: table size, indexes, query columns and others before it cangenerate an accurate query plan. The query optimizer uses all of the available information fromthe table or tables, picks an action plan that satisfies the user’s request in the most efficient way.The query optimizer will use indexes whenever possible and use the most restrictive indexes inorder to eliminate as many rows as possible as soon as possible. The optimizer tries to reject therows because the faster it eliminates rows from consideration, the more quickly the rows that

• 1495598748-Copy of the Lab 2 output DBM449.txt

ComputerScienceExpert

(11)

Status NEW Posted 24 May 2017 04:05 AM My Price 11.00

-----------

Attachments

file 1495598754-Solutions file 2.docx preview (51 words )

H-----------ell-----------o S-----------ir/-----------Mad-----------am ----------- Th-----------ank----------- yo-----------u f-----------or -----------you-----------r i-----------nte-----------res-----------t a-----------nd -----------buy-----------ing----------- my----------- po-----------ste-----------d s-----------olu-----------tio-----------n. -----------Ple-----------ase----------- pi-----------ng -----------me -----------on -----------cha-----------t I----------- am----------- on-----------lin-----------e o-----------r i-----------nbo-----------x m-----------e a----------- me-----------ssa-----------ge -----------I w-----------ill----------- be----------- qu-----------ick-----------ly -----------onl-----------ine----------- an-----------d g-----------ive----------- yo-----------u e-----------xac-----------t f-----------ile----------- an-----------d t-----------he -----------sam-----------e f-----------ile----------- is----------- al-----------so -----------sen-----------t t-----------o y-----------our----------- em-----------ail----------- th-----------at -----------is -----------reg-----------ist-----------ere-----------d o-----------n -----------THI-----------S W-----------EBS-----------ITE-----------. ----------- Th-----------ank----------- yo-----------u -----------

Not Rated(0)

• Buy Answer