Create a secondary index

Assignment Help Database Management System
Reference no: EM131426768

Advanced Database Topics Assignment

This will give you a chance to apply everything you learned in the class. We will use two different databases (Postgres and Oracle are easiest because you have access to them already and you even have the data loaded). You can use MySQL, but that would require additional guess regarding the estimated cost or using real runtime. Of course, you are welcome to install or use your own (relational) database engine if you want.

In the interest of finishing quickly, we will not run "real" runtimes for the queries and focus on explain costs.

Baseline results -

Start this part by collecting the baseline performance (estimated query runtimes with no additional structures in the database) for all 13 queries in the SSBM workload - we will use these numbers as are reference point to estimate improvement. If you have any indexes from previous assignments, drop them first.

You should have estimated numbers (for MySQL you can use real runtime or your own estimate) for all 13 queries for two different databases. You need to submit all 26 estimated cost numbers - but only have to submit one query screenshot from each database showing the EXPLAIN plan output.

Please be sure to specify which database / version / computer hardware you are using.

You should provide an answer for each database you chose. That answer can be the same for both databases, but only if you verified that it works in the second database. Your index/MV should improve the query cost (that's the point, after all). However, if your suggested index/MV does not get picked up, do not worry about it. If that happens, be sure to describe what you have attempted and why you think your index was appropriate.

Please do not forget to include the SQL code for index creation (and query rewrite for MVs in Postgres) for any of your answers.

1: Indexes

A. Create a secondary index for Q1.2

B. Create a secondary index for Q2.2

C. Create a secondary index for Q3.2

D. Create a secondary index for Q4.3 (Include a screenshot of EXPLAIN here for both databases in addition to the estimate number)

E. Create a secondary index for Flight3 (Q3.1, Q3.2, Q3.3, Q3.4). Report the explain costs for all 3 queries.

F. Create a clustered index for Q3.2. Remember that in Oracle you have to create an IOT (Index Organized Table) and cannot re-cluster an existing table like you can in Postgres. Include a screenshot here in addition to reporting the estimate EXPLAIN cost.

G. Create a clustered index for Flight2 (Q2.1, Q2.2, Q2.3).

2: Materialized Views

A. Create a materialized view (no pre-filtering, that is only GROUP BY and JOIN is allowed, but no predicates in the MV) for Q1.3

B. Create a materialized view (no pre-filtering, that is only GROUP BY and JOIN is allowed, but no filter predicates may be used) for Q3.4

C. Create a materialized view for Flight4 (Q4.1, Q4.2, Q4.3)

D. Add an index to your MV answer in 2-C and re-evaluate all three queries (submit a screenshot of explain for one of the three queries for each database you are using)

3: Database Physical Design

Using the structures you have already created, put together a physical design, specifying the size of the combined structures and the estimated cost for all 13 queries. For example, if you choose an index from 1-A and an MV from 2-C, you would report the size of these structures and the (estimated EXPLAIN) runtimes that can be achieved if both of them were used. Do this for each database -- you do not need to re-run anything for this.

Which DBMS achieved a better (size-to-improvement) ratio?

4: Further database optimization

For one database, evaluate the benefits of compression for any one previous structure. I.e., compress a structure and check for cost difference that results. In Oracle, it is as simple as rightclick on the structure (table/mv/etc), then choose "Storage -> Compress..."

5:

Use your code from previous homework assignments, create an automatic index builder. That is, given a query such as what you parsed in HW1, generate the CREATE INDEX AutoIndex ON TABLE ... SQL code that can be pasted into a database. I suggest assuming simplified queries (simple equality or range predicates only) but using your query parser and statistics to estimate selectivity. You can assume that all selectivities are independent, so you would create an index for columns as long as product of their selectivies is low enough (e.g., lo_discount 0.1, lo_quantity 0.05, is 0.005 which is low enough and would produce CREATE INDEX AutoIndex on Lineorder(lo_quantity, lo_discount);).

Attachment:- Assignment File.rar

Reference no: EM131426768

Questions Cloud

Entrepreneurial strategies apply : When we think of entrepreneurship, we often think of small, start-up firms. Describe how a large corporation in an established industry can be "entrepreneurial" and discuss how Peng's entrepreneurial strategies apply in this situation.
What is probability that the dentist will see the patient : Find the probability that the patient will have to wait at least 30 minutes past the appointment time.- Find the probability that the dentist will see the patient between 10 and 20 minutes after the appointment time.
Assess segways chosen distribution channels and methods : Identify and assess Segway's chosen distribution channels and methods. Is it the best choice, or what would you recommend? How would you organize and manage the sales staff at Segway to best reach its target markets?
Understanding and analyzing business communication situation : Create a communication strategy that fosters change and innovation. What am I trying to achieve and how will my audience react to what I am trying to achieve and will my message be resisted?
Create a secondary index : CSC 553: Advanced Database Topics Assignment. This will give you a chance to apply everything you learned in the class. We will use two different databases (Postgres and Oracle are easiest because you have access to them already and you even have t..
Calculating the true gdp deflator : 1. Explain how uncertainty in calculating the true GDP deflator makes it uncertain how much the production of goods and services has increased.
What additional services do my target customers want : Who are my Target Customers? How can I best reach them? What additional services do my Target Customers want? What quality of service of product does my customer desire?
Probability that the next call will occur after given time : If a 911 call is taken at 10:07 p.m., what is the probability that the next call will occur before 10:30 p.m.?- If a 911 call is taken at 11:30 p.m., what is the probability that the next call will occur after 11:45 p.m.?
Bang-for-the-buck effect on the economy : 1. Give 2 reasons why increasing government spending could have a stronger bang-for-the-buck effect on the economy than an equivalent-sized tax cut. (2-3 sentences)

Reviews

len1426768

3/14/2017 5:09:18 AM

In the interest of finishing quickly, we will not run "real" runtimes for the queries and focus on explain costs. Submit a single document containing your project solutions. Be sure that this document contains your name and “CSC 553 Take-home final” at the top. You should provide an answer for each database you chose. That answer can be the same for both databases, but only if you verified that it works in the second database.

Write a Review

 

Database Management System Questions & Answers

  Develop data visualization that displays several dimensions

Use your imagination to develop your own data visualization that displays several dimensions with a single plot. For example, color of data points can be used to categorize one dimension.

  Implementation of information gathering component

Based on the pseudocode developed in Subtask 1.1, you are to implement the Information Gathering Component in this task.

  Create trend-moving averages with the given values form

Create trend-moving averages with the following values form: 10, 100, and 200. Graph the data with Excel. Create centered-moving averages with the following values form: 10, 100, and 200. Graph the data with Excel.

  Compare database backup and disaster planning

This research paper will describe, compare and contrast Database Backup & Disaster Planning. This research paper must be submitted using SafeAssign.

  Determine an effective sampling online database

Select a company from one of the Rasmussen online databases, such as Hoover's, Mergent Online, or the Harvard Business School library. Build a set of at least 40 effective measurement questions keeping in mind the content, participants and the out..

  Database system development

The database application project (DAP) for this course consists of the creation of a fully functional Microsoft Access database application for a real client.

  What advice would you give the managers of this company

Advice given to managersThe storage system that best fits the needs of the companyReasons why this is the best storage system.

  Display structure of the i_model table

Display structure of the i_model table. Display all the information stored in the i_model table belonging to car group ‘A4'.

  What are steps you take to complete the development process

What are the steps you are going to take to complete the development process? Include a detail requirement (5 pages max) with suggest design and ER diagram.

  Types of keys used in relational database

Explain with examples different types of keys used in relational database, how they help relating different tables.

  List the name of employee and total hours

1 List the name of employee and total hours he works on the project (use outer join so that employees who don't work on project will be also listed withzero project count ) 2 List the name of project that ‘chen’ does not work on (request: must use NO..

  What is the difference between a database and a table

What is the difference between a database and a table? What two conditions must be met before an entity can be classified as a weak entity? Give an example of a weak entity.

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd