A complex query has to be used repeatedly, there are at least two ways to avoid

Question

A complex query has to be used repeatedly, there are at least two ways to avoid retyping that query: creating a view or saving the query inside a procedure. Each method has some performance tradeoffs.

Suppose you are designing a database for a bookstore.The owner wants to retrieve information about books that cost more than $50.

Describe one situation in which you would prefer to create a view and one in which you would prefer a stored procedure.

For each situation, outline the strengths and weaknesses of the two methods with specific examples. Consider the effects of potential future changes to the business needs.

Explanation / Answer

View is nothing but a virtulaization used to retrieve required data from the database and it provides security since it is virtual

In real time scenario, views can be used in bikes manfacturing company where at a particular instant huge number of bikes are to be deployed or few at the different stages of manufacturing. Since to find data about the bikes which are manufactured completely are to be retreived then we will create view on that table and retrei.ve the required data..

Strengths of view::: since it creates a virtual concept it provides security and also make easier the process of retreiving the required data

Weakness ::: since it copies the data from the table any other users make changes to the table will not affect view hence there will be no updated data.

Procedure:

Set of statements used to accomplish the given task like a function but no return type in this procedure...

In the scenario of finding purchases done on the given day or greater than the given amount, procedures play an important role. Here all the time the work to be done is same but based on the given amount above which transactions are to be identified hence procedure can be used...

Strength::: Reuse of code is the main strength of the procedure it can be executed as many times as required.

Weakness::: since set of statements are cannot be changed, we cannot modify the type of input to be given or work to be done by procedure...

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.