When would building an index be considered useless? What are integrity constrain

Question

When would building an index be considered useless?

What are integrity constraints and what is their purpose?

What is a Cartesian product?

What are database triggers?

What is uncorrelated subquery?

Given the salary column defined as VARCHAR(5), what, if anything, is wrong with the following SQL statement? SELECT ename, salary * 1.05
FROM emp;

Explain how to use COMMIT within the MySQL transaction.

Which columns in a table should you consider indexing? What are other considerations that should be considered before creating the index?

The lead developer is insisting that you create a bitmapped index on the primary key of a table. Write a paragraph explaining why you would or would not grant his or her request. Provide a rationale for whatever position you choose.

Review this query.
SELECT CONCAT(FirstName, ' ', LastName) AS CustomerName,
Street, City, State, ZipPostalCode
FROM CUSTOMER
ORDER BY CustomerName;

Create a stored function named FirstNameFirst() so that the following query will generate the same output as the above query.

SELECT FirstNameFirst(FirstName, LastName) AS CustomerName,
Street, City, State, ZipPostalCode
FROM CUSTOMER
ORDER BY CustomerName;

You are the DBA of a development project. Your developers are arguing about whether to write a query using a join, a subquery, a correlated subquery, or a set operator. Each faction claims that one method performs better than the others. Write a brief synopsis that tells the developer how to prove whether one query performs better than another. Include the different factors that affect performance for queries.

What are some significant issues when using triggers? Be sure to explain your answers clearly, accurately, and concisely.

What are the regular preventive maintenance programs for MySQL databases?

Explanation / Answer

When would building an index be considered useless? Indexes seems usesless, when you update the data so frequently, when you're generating sequences that are not of any particular order(i.e., unsorted), when you're inserting rows into a table more often.

What are integrity constraints and what is their purpose? Integrity constraints provide a mechanism for ensuring that data conforms to some standard guidelines specified by the orgnization personal requirements, and their database administrator specifications. They are used to set some standards as per the requirements of the organization.

What is a Cartesian product? A cartesian product which is also called cross product or cross join is the concept of pairing each row in the first table with every row in the other table. Therefore, if there are m rows in table1, and n rows in table 2, then the cartesian product will return a table of m x n rows.

What are database triggers? Triggers are the procedures which will be automatically executed when an event occurs. For example, if you have a table containing the total of all employee salaries which is maintained in the employees table, if an employee is added, or deleted, or his salary is modified in the employees table, obviously the total salary should be updated accordingly. This can be done easily using the triggers.

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

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