As a dba it is vital that you understand in detail how a

Assignment Help Database Management System

Reference no: EM13380278

As a DBA it is vital that you understand in detail how a query is processed by the DBMS. Since Team Retail is using Oracle, you need to demonstrate your knowledge of Oracle query processing. You recognize that all relational DBMS must perform the same general steps when processing a query even though the steps may have different names and the details may be somewhat different.

1. Consider the processing of a (read-only) query in Oracle. The query will proceed through multiple steps to produce the output. Assume the query contains (or could contain) select, from, where, and order by clauses. Assume also the query may require the join of two tables. Create a flowchart or structured English "algorithm" to describe how a query is processed.

a. A description at the appropriate level of detail will include how and when syntax and semantic errors are identified through the return of a result set.

b. Include the processing that occurs when a syntax or semantic error occurs.

c. Include other errors as appropriate. Hint: remember the user has to be authorized.

d. Do not include details of the optimizer processing; identifying when the optimizer step occurs is sufficient.

e. Acceptable diagram formats include a flowchart, a UML activity diagram, or similar diagram style. The "algorithm" would be a numbered list of steps similar to those used in our assignment descriptions or a checklist.

f. This must be your own diagram or list of steps. Do not copy an existing diagram or algorithm. Such diagrams are usually too detailed or not detailed enough to answer this question.

2. Use the Global Engineering query from your Discussion 3 contribution that has two syntax errors. Describe in detail how this query is processed by Oracle. I recommend that you execute the query and save the results in a spool file; then refer to those results in your analysis. Consider the following in your answer.

a. What error does Oracle find first?

b. When does Oracle stop processing the query?

c. What error message does Oracle return?

d. Is that error message accurate?

e. Does Oracle identify both errors? Why or why not?

3. Use the Global Engineering query from your Discussion 3 contribution that has two semantic errors. Describe in detail how this query is processed by Oracle. I recommend that you execute the query and save the results in a spool file; then refer to those results in your analysis. Consider the following in your answer.

a. What error does Oracle find first?

b. When does Oracle stop processing the query?

c. What error message does Oracle return?

d. Is that error message accurate?

e. Does Oracle identify both errors? Why or why not?

4. Use the Global Engineering query from your Discussion 3 contribution that produces incorrect results. Execute the Explain Plan for this query. Describe in detail how this query is processed by Oracle. I recommend that you execute the query and save the results in a spool file; then refer to those results in your analysis. Consider the following in your answer.

a. How does Oracle process this query? That is, what does Explain Plan tell you about how the query is processed?

b. How would you recognize that the results were not correct?

c. What is the error that caused the query to produce incorrect results?

Reference no: EM13380278

Questions Cloud

Write a java application that calculates and displays the : write a java application that calculates and displays the property tax for n property owners. n should be declared

Information has many facets value confidentiality integrity : information has many facets value confidentiality integrity privacy legality and so on. all information is not the same

Imagine that you have been hired as the database : imagine that you have been hired as the database administrator for a local department store. the department store has

1write a program that uses joptionpanes to perform the : 1.write a program that uses joptionpanes to perform the following stepsmiddotfirst display a welcome message to your

As a dba it is vital that you understand in detail how a : as a dba it is vital that you understand in detail how a query is processed by the dbms. since team retail is using

Select a topic from the list and conduct research based on : select a topic from the list and conduct research based on literature at least three recent research papers and prepare

Based on the following background information and project : based on the following background information and project requirements complete a project making use of opnetbackground

You can are required to research into one of the : you can are required to research into one of the following topics on data security or computer-network security and

User Account

All Pages