This website uses cookies to ensure you have the best experience. Learn more

Assignment Tulsa

2232 words - 9 pages

This assignment helps you understand what table columns deserve an index through a series of exercises on a database schema. You do not type SQL in this assignment, but rather identify which columns deserve indexes, the type of index it deserves, and why. Correct placement of indexes is absolutely critical to database performance. All but the smallest of databases would have terrible performance if no indexes were assigned to any of its table columns.

An index is a physical construct that serves two purposes – speeding up data retrieval and enforcing uniqueness. Indexes are not modeled in logical entity-relationship diagrams, because indexes do not operate at the logical level of ...view middle of the document...

Let us begin exploring index placement by first examining the album schema below.

[pic]

1. The first item deserving our attention is that many modern relational DBMS, including Oracle and SQL Server, automatically add unique indexes to table columns covered by a primary key constraint.

Identify the primary key columns in the album schema, using the standardized dot notation, that is, TableName.ColumnName. We do not need to add indexes to these since the DBMS will create them automatically for us.

2. The next thing we want to do is to add indexes to all foreign key columns. We do this without concerning ourselves with any SQL queries (as we do in #3 below). Some DBMS, including Oracle, will sometimes escalate a row-level lock to a page-level lock when a SQL join is performed using a foreign key that has no index. The focus of this assignment is not locking, so I will not get into fine details, but suffice it to say that page-level locks are always bad for transactions because they result in deadlocks over which the database developer has no control. Another reason we index all foreign key columns is because the vast majority of the time, the foreign key will be used in the WHERE clause of SQL queries that perform a join on that table.

Identify the foreign key columns in the album schema, using the standardized TableName.ColumnName notation. For each foreign key, indicate whether you would create a non-unique index, or a unique index. A non-unique index speeds up data retrieval, but does not enforce a uniqueness on the covered column, so that values in the column can repeat. A unique index speeds up data retrieval and also enforces uniqueness on the covered column. Unique indexes obtain better performance than non-unique indexes for some queries, because the DBMS query optimizer knows that each key requested from a unique index will at most have one value, while each key requested from a non-unique index may have many values. Therefore if it is guaranteed that values will not repeat, it is better to use unique indexes. However, adding a unique index on a column that has values that can repeat will cause erroneous transaction abortions every time a repeated value is added to the column, so it is important to correctly discern which type of index is needed.

3. This step concerns itself with adding indexes needed by specific queries. We have already identified the primary key columns for which the DBMS creates indexes automatically, and have identified the foreign key indexes. The final step in the process of adding indexes to a schema is to index all columns referenced in the WHERE clause and join conditions of all pertinent SQL queries. The WHERE clause and join conditions in a SQL query contains conditions that specify what rows from the tables will be present in the result set. The query optimizer makes heavy use of these conditions to ensure that the results are retrieved in a timely fashion. For example, if the...

Other Essays Like Assignment Tulsa

The Versatility And Flexibility Of OLED's

1014 words - 5 pages In April 1, 2002, organic light emitting diodes gain rise in the scientific community with their published, more practical form at Ames Laboratory. “Scientists at the U.S. Department of Energy's Ames Laboratory, in collaboration with scientists at the University of Michigan, Ann Arbor, have developed and demonstrated a novel, fluorescence-based chemical sensor that is more compact, versatile and less expensive than existing technology of its

Comparing The Moral Virtues Of Antony And Julian The Apostate

1103 words - 5 pages Roman emperor Julian the Apostate and Christian leader Antony both exhibited many qualities of character during their existence. Both of them led very distinctive lives although shared several ethical values. Book 25 of “The Later Roman Empire” and the book “Early Christian Lives” show concrete evidence of this. In the following essay, I will argue how both leaders’ lives were devoted to their religious beliefs and their mutual cardinal virtues

Living In A Cashless Society

1637 words - 7 pages Money in a traditional sense no longer exists. Money is becoming much of a concept than a physical material, and most ordinary bitter have not see the reality of the switch. People today are using credit and debit cards on a regular basis and in everyday situations such as meal purchased at fast food, highway tolls, clothing, groceries, gas stations, etc. all of these means of systems could be regarded as a cashless society or world. The question

The French And Indian War: The "Real" First World War

1955 words - 8 pages The Seven Years War, or more commonly referred to as “The French and Indian War”, has been called the true First World War. In this book The French and Indian War: Deciding the Fate of North America, the author and historian Walter R. Borneman paints a detailed and elaborate picture that justifies the claim of it being the first true war of global proportions. If ever there truly was a climax to the never ending feud of the European powers

Is The Use Of Animals In Medical Research A Necessary Measure?

1513 words - 7 pages Throughout history, animals have been used in experiments to test product safety and obtain medical knowledge that benefits both humans and animals alike. Every year there are numerous medical breakthroughs, such as medications and surgical instruments, which are tested on animals to insure their safety before they are deemed acceptable for human use. Even though the results of the experiments saved millions of human lives, they are also

Education And The Evolving Job Market

2363 words - 10 pages The lives of students today are changing. They are preparing for lives and jobs that have never before existed. If teachers hope to have a significant and worthwhile impact on these quickly changing lives, they must change the way they think, prepare, and instruct our future generations. Children cannot afford to have teachers who remain stagnant in their methods and ideals. Students crave instructors that are willing to allow them to tap

Young And Relentless

1737 words - 7 pages There are numerous influences that can be responsible of teenager’s behaviors and attitude as they develop. One factor that is important to these behaviors is parental figures being over involved or uninvolved in their children’s lives. Many of these effects include illegal substance abuse, rising sexual activity, underage alcohol consumption, and tobacco use. Studies show parental participation plays a key role in the characteristics developed

The Natural Law Theory

1231 words - 5 pages Obeying by the natural law theory is the only true and moral way to live life; especially a life lived in God’s image. God’s presence is a guiding factor to obtaining a moral and virtuous life, which can only be obtained by following the natural law theory. God created a set of laws as a supreme guide for humans to live life, like any law these laws were created to ensure wellbeing for everyone. The laws he created are the civil law, the natural

Resolved: Presidential Signing Statements Threaten To Undermine The Rule Of Law And The Separation Of Powers

1811 words - 8 pages The subject of signing statements has created much debate among the houses of Congress, government officials, and the public alike. These signing statements fall under the categories of constitutional and legislative history signing statements. Constitutional signing statements are those in which the president deems certain provisions of the legislation as unconstitutional, therefore they should not be enforced (Bradley & Posner, 2006

Oppressive Systems Of Government In Egypt And Animal Farm

1529 words - 7 pages As in Egypt, Orwell demonstrates through his allegorical novel “Animal Farm” that leaders are able to establish and maintain power over a people, and in turn create an oppressive and corrupt government system. Orwell shows the significant difference in the education and levels of knowledge in the animals, and how the government takes advantage of this difference. The split between the levels of intelligence is portrayed in the first chapter when

The Pathway To Psychosis

1415 words - 6 pages “How all occasions do inform against me” is a line from act IIII, scene IIII of William Shakespeare’s Hamlet. This line, spoken by Hamlet, expresses his emotional state as he is currently overwhelmed by the death of his father, the king of Denmark, and the situation surrounding it. After Hamlet learns of his father’s death he finds out that his mother has married Claudius, Hamlet’s uncle. On top of all of that, Hamlet soon after

Related Papers

Kathy's Chat & Chew Essay

1015 words - 5 pages Kathy's Chat & Chew Nikhol L. Robinson ECO/561 September 27, 2012 Robert Bailey Kathy's Chat & Chew The purpose of this assignment is to describe a service business proposal. A description regarding how the soul food restaurant that will be known as Kathy’s Chat & Chew can open in north Tulsa sustain and make a profit. This paper includes assumptions about the elasticity of demand and the market structure of the good and service

Qnt 561 Week 4 Weekly Learning Assessments Assignment

1964 words - 8 pages QNT 561 Week 4 Weekly Learning Assessments - Assignment Chapter 10 Exercise 2 [The following information applies to the questions displayed below.] A sample of 36 observations is selected from a normal population. The sample mean is 12, and the population standard deviation is 3. Conduct the following test of hypothesis using the 0.01 significance level H0: μ ≤ 10 H1: μ > 10 1. Award: 10 out of 10.00 points a. Is this a one- or two

Qnt 561 Week 4 Weekly Learning Assessments Assignment

1964 words - 8 pages QNT 561 Week 4 Weekly Learning Assessments - Assignment Chapter 10 Exercise 2 [The following information applies to the questions displayed below.] A sample of 36 observations is selected from a normal population. The sample mean is 12, and the population standard deviation is 3. Conduct the following test of hypothesis using the 0.01 significance level H0: μ ≤ 10 H1: μ > 10 1. Award: 10 out of 10.00 points a. Is this a one- or two

The Separation Of Capital Ownership And Control

1577 words - 7 pages The argument of whether the separation of capital ownership and control is an efficient form of organization has constantly been a controversial issue. The criticism whether the controllers’ act is in the best interest of the owners’ wills never end as long as hired managers operate management. As the number of public companies has been increasing over the course of this century, meanwhile the American style of contact based corporation has