Ask Question, Ask an Expert

+61-413 786 465

info@mywordsolution.com

Ask DBMS Expert


Home >> DBMS

Description

This group assignment provides an opportunity to gain some practical experience with indexing. The "experiment of the week"   assignments are intended to be more open, with your group picking the specific topics to investigate. Basically, this gives you a chance  to be a bit more creative and follow directions of specific interest to you (and more relevant to your career). Besides creativity, you should try to pick experiments that are interesting and carry them out with good technical skill. That does not mean the results  have to be in line with your intuition, counter-intuitive yet interesting results are great. Besides the topic of the week, such asindexing,  ou are free to bring in any past topics to complement your experiments. For instance, you might develop some new queries that fit better with the current experiments. The deliverable is an "experiment of the week" write-up that includes an  explanation of each experiment, along with screenshots, figures, and/or tables that highlight key steps or results. Along with some   ideas below, there are "hall of fame" examples that show fragments of past student projects. These fragments and hints are selected because of interesting features, but are not guaranteed to be completely correct. So, use them for inspiration and develop your own  informed results (i.e., "trust but verify"). Together these hints and ideas should help you complete your assignments and learn along the way.Project Ideas

Idea 1: Investigating Selectivity

Look up selectivity in your database textbook. Essentially, the optimizer makes a decision regarding index use based on the fraction of results returned. Indexes are most useful when selecting a small fraction of the available records. You can conduct a simple experiment to find out where the cutoff percentage lies by developing a simple single table query and graduallyshrinking (or growing) the query range.

Idea 2: Start Simple and Show that Indexing Works
You can start with a simple experiment that builds on your query writing. Take a simple query and improve the performance by adding indexes (and/or using any subsequent techniques).The idea is to take a query scenario based approached to performance tuning.  hen expand to more complex queries.

Idea 3: Primary Keys and Indexes

Why index a primary key? Primary key constraints can be expensive to enforce, since any new values have to be unique (and compared with all existing values). So, a fast lookup based on the primary key value is very useful (based on an index). You could  explore this by creating a table with no primary key constraint, but with a unique constraint on the candidate keycolumn. Do an INSERT  nd look at the execution plan and cost. Now put a primary keyconstraint in place, which automatically creates an index. How  does the performance differ? I have not tried this, but it sounds interesting.

Idea 4: Indexing for Different Query Types There are many  types of queries. Some queries are highly focused and return a single row or small set (a "point queries"). Other queries return larger  sets based on ranges of specific attributes (range queries). Report-like queries typically scan large amounts of data and often form   aggregates for results (scan queries). You could explore the importance of index structures under these different scenarios.

Idea 5:  Function-Based Indexes Database systems often provide methods for improving performance for computed columns. It
is often good practice to derive data from several existing columns. However, these multicolumn computations can be expensive to  calculate at query time. Several techniques such asmaterialized views or function-based indexes can improve performance in these  ituations. In particular, function-based index structures store the calculated values for efficient retrieval. As  part of an experiment, you  an create a computed attribute and try queries with and without function-based indexes

DBMS, Programming

  • Category:- DBMS
  • Reference No.:- M91421902
  • Price:- $20

Priced at Now at $20, Verified Solution

Have any Question?


Related Questions in DBMS

You are in a real estate business renting apartments to

You are in a real estate business renting apartments to customers. Your job is to define an appropriate schema using SQL DDL in MySQL. The relations are Property(Id, Address, NumberOfUnits), Unit(ApartmentNumber, Propert ...

Assignmenta restaurant is designing a database to keep

Assignment A restaurant is designing a database to keep track of customer services. A customer is defined as a customer ID, name, address and a telephone number. Customers are served by employees. Each employee is define ...

The relation memberstudentid organizationid roleid stores

The relation Member(StudentId, OrganizationId, RoleId) stores the membership information of student joining organization. For example, ('S1', 'O2', 'R3') indicates that student with Id 'S1' joined the organization with i ...

In sql database questions phase-1 in 100 words what steps

In SQL Database Questions: Phase-1 In 100 words, what steps can one take to avoid losing work? Which command is used to save changes to the database? What is the syntax for this command? Phase-2 In 100 words, explain the ...

Sql assignmentin these exercises youll enter and run your

SQL Assignment In these exercises, you'll enter and run your own SELECT statements. You will use the MyGuitarShop database for these queries. If you do not already have the MyGuitarShop database, the SQL script and the i ...

Systems analysis project scenic routes operates a bus

Systems analysis project Scenic Routes operates a bus company that specializes in travelling on secondary roads, rather than Interstate highways. Their slogan is: "It Takes a Little Longer, But It's Scenic." The firm nee ...

Assignment -scenario setup a mock phase 3 clinical trial

Assignment - Scenario: Setup a Mock Phase 3 Clinical Trial for evaluating the efficacy of a Blood Pressure/Weight Loss/ or Muscle Strength Enhancement supplement. Assume that the testing takes place at a physician's offi ...

Question we can sort a given set of n numbers by first

Question : We can sort a given set of n numbers by first building a binary search tree containing these numbers (using TREE-INSERT repeatedly to insert the numbers one by one) and then printing the numbers by an inorder ...

This assignment is a continuation of this solution the case

This assignment is a continuation of this solution The case study company has received the first report from its enterprise content management (ECM) consultant and now has a documented list of major content requirements ...

Question a suppose you are a marathon runner that can run a

Question : a) Suppose you are a marathon runner that can run a maximum of n miles on a single bottle of water. You are given a map of your marathon route with all the water stations marked. Design an efficient algorithm ...

  • 4,153,160 Questions Asked
  • 13,132 Experts
  • 2,558,936 Questions Answered

Ask Experts for help!!

Looking for Assignment Help?

Start excelling in your Courses, Get help with Assignment

Write us your full requirement for evaluation and you will receive response within 20 minutes turnaround time.

Ask Now Help with Problems, Get a Best Answer

Why might a bank avoid the use of interest rate swaps even

Why might a bank avoid the use of interest rate swaps, even when the institution is exposed to significant interest rate

Describe the difference between zero coupon bonds and

Describe the difference between zero coupon bonds and coupon bonds. Under what conditions will a coupon bond sell at a p

Compute the present value of an annuity of 880 per year

Compute the present value of an annuity of $ 880 per year for 16 years, given a discount rate of 6 percent per annum. As

Compute the present value of an 1150 payment made in ten

Compute the present value of an $1,150 payment made in ten years when the discount rate is 12 percent. (Do not round int

Compute the present value of an annuity of 699 per year

Compute the present value of an annuity of $ 699 per year for 19 years, given a discount rate of 6 percent per annum. As