(TCO 1) Ideally, the DBMS test environment will: (Points : 10)

Be a newer, more powerful, and better equipped computer than the production system, to ensure testing goes smoothly and as quickly as possible. Testing is so criticalit is important that the test system is even more capable than the production system, if possible. Mirror the production system as closely as possible, so that tests conducted in the test environment will accurately reflect actual performance of the production environment. Consist of older, less capable equipment. This offers the organization better economy, and if tests run adequately on this lesser platform, they are certain to run even better in the production environment. Not be needed, if the organization is properly employing modern Object Oriented Programming and modeling and using an interactive, incremental agile software development framework, such as SCRUM.

Question 2.2. (TCO 1) The General Query Log: (Points : 10)

Tracks information about all queries that have been submitted on the MySQL server. Captures information about queries that take longer than a pre-defined time interval to complete. Assists in restoration and replication, as well as providing a record of all changes that were made to the database. Keeps a record of major events, such as a server start/stop.

Question 3.3. (TCO 1) The Slow Query Log: (Points : 10)

Tracks information about all queries that have been submitted on the MySQL server. Captures information about queries that take longer than a pre-defined time interval to complete. Assists in restoration and replication, as well as providing a record of all changes that were made to the database. Keeps a record of major events such as a server start/stop.

Question 4.4. (TCO 2 and 9) Which of the following is NOT considered by the Query Optimizer: (Points : 10)

Table size Available Indexes Query Columns Available Views

Question 5.5. (TCO 2 and 9) Which of the following statements are NOT TRUE about the MySQL Query Optimizer: (Points : 10)

The query optimizer takes into account available indexes when determining how to optimize query execution The query optimizer takes into account key length of indexes when determining how to optimize query execution. It is possible to override the optimizer, forcing it to use specific indexes The query optimizer guarantees that the optimally performing execution path is automatically selected.

Question 6.6. (TCO 8)Which of the following is NOT a wise justification for MySQL Clustering? (Points : 10)

A need for data to be available 24 x 7. The organization has an abundance of low-performance computers. Data reliability is criticalthe loss of a single record or lost transaction is a significant concern. Users are disbursed over a number of different locations.

Question 7.7. (TCO 8) Which of the following is NOT a wise justification for MySQL Replication? (Points : 10)

Data needs to be available 24 x 7. Frequent backups are required to protect against data loss. Widespread use of LAMP (LINUX, Apache, MySQL, PHP) throughout the organization. The system needs to serve both OLTP and DSS needs. Plentiful availability of low performance computers. Users are disbursed over a number of different locations. The organizations MySQL applications can readily be modified to write data to a master MySQL instance, while performing reads against replicas.

Question 8.8. (TCO 6) A DBA is designing a spatially-enabled database to determine the distance from a customers location at point 1 (x1, y1), to the companys distribution center at point 2 (x2, y2), in order to automatically provide the customer with a delivery cost estimate for the companys products, which are delivered by company truck, based on a fixed cost-per-mile. Using the Pythagorean Theorem, the DBA creates a shipDistance(x1, y1, x2, y2) function that uses the following formula as the basis for its calculation: shipDistance = ((x2-x1)^2 + (y2-y1)^2)^0.5 What, if any, problems will be revealed by the DBAs careful testing of this function using known points and distances to validate the softwares correct functioning? (Points : 10)

The DBA finds that long distances generate values equal to or insignificantly varying from the expected results, while short distances generate significant errors, which are increasingly inaccurate as points that lie closer together are tested. This is due to the Law of Averages, in which uncertainty tends to cancel out for large measurements, but which can be significant and skew results when measuring very small quantities. The DBA finds that short distances generate values equal to or insignificantly varying from expected results, while long distances generate significant errors, which increase with the distance. This is because the distances calculated reside on a curved surface, the earths roughly spheroidal surface, rather than points on a planar surface. Since the amount of curvature is smaller over short distances, the errors for short distances are often insignificant. The DBA finds that the distance calculation for points with large latitudinal displacement (y-variance, or North to South differences) produce significant errors, without respect to the longitudinal difference (x-variance, or East to West differences), while points with large longitudinal displacement produce no significant errors. This is because the distance between longitudinal lines becomes closer together as you move away from the equator and close to the poles. The DBA finds that testing using a result set of coordinates of varying known short and long distances is invariant from expected results. This is because the Pythagorean Theorem is universally applicable across planar and non-planar surfaces.

Question 9.9. (TCO 5 and 7) Which of the following defensive steps for securing a MySQL system is not correctly paired with the threat which it mitigates: (Points : 10)

Require SSL encryption for remote access; Prevents unauthorized parties from obtaining system access via social engineering. Require all accounts to have a password; Prevents unauthorized use of an account by other than the account holder. Avoid running the MySQL server process as the UNIX/LINUX root/superuser;Prevents someone that successfully gains access to the MySQL server also gaining the ability to create and execute arbitrary files in the host computers filesystem. Avoid granting PROCESS or SUPER privilege to a non-administrative user; Prevents an unauthorized user modifying any of the data, tables, and even MySQL processes and log files without authorization or detection. Avoid the use of symlinks to tables; Prevents anyone with write access to the servers data directory also having the ability to delete any file in the system.

Question 10.10. (TCO 3 and 4) Comparing Text vs. Binary export of MySQL data, and related import/export utilities, which of the following is NOT true: (Points : 10)

Binary format offers advantages in speed. Text format offers better compatibility when data is to be imported into other, non-MySQL systems. mysqlhotcopy is the best choice of universal tool, as it is available on all platforms which support MySQL. MySQL Administrator offers ease-of-use, due to its graphical user interface, as well as the benefit of schedule control.

Question 11.11. (TCO 3 and 4) In the case study, Practice Makes Perfect, the scenario reflects a situation of dynamic business requirements which have evolved to require nightly importation of large data files from external business partners. Performance is poor, and optimization of processes, practices, architecture, and database configuration are required. Select all of the recommended optimizations that pertain to the following investigational observation to be addressed. Observation: The database server is dedicated to running the import job, and no other applications are permitted to run on this host concurrently. No server parameter configuration settings have been altered since the server was first put into service, and no alternation in database settings is made between daytime (when the database server is used as the general purpose DBMS), and nighttime (when the database server is solely devoted to running the import job). (Points : 10)

Generate the import file in the order of the target tables primary key. Generate the data file to be imported to perform multiple row inserts for each batch. Perform imports from a machine in the local network, rather than over the wide area network. When possible, perform imports directly upon the database server host. Eliminate unnecessary security/encryption layers when performing bulk imports. Temporarily alter the database storage engine settings, such as increasing system memory buffers and log buffers and increasing log file sizes, so as to perform more operations in-memory, and to reduce disk I/O. Eliminate indexes used by the source database which are not required by the receiving database. Drop foreign key constraints, which are important for preserving referential integrity during individual record input, but which are generally redundant when batch importing data already validated.