Re: Q1.composite key and Q2.correlated subquery

> 1. Explain, in simple terms, the concept of composite keys and why we 
use them.

A composite key is simply a key that consists of more than one column, 
e.g. the primary key for table ORDER_LINE could be (ORDER_NO, LINE_NO).  
We use them when they are a correct and sensible key for the table.  If 
there are too many columns in a composite key, making it unwieldy, we may 
prefer to introduce a "surrogate" (invented) one-column key (e.g. 
ORDER_LINE_ID).  

2. What is a correlated subquery and why it is "dangerous"?

A correlated subquery is a subquery that is run for each row in the main 
query, because part of its definition depends on data from the main 
query.  Example:

SELECT * FROM dept WHERE EXISTS
( SELECT 1 FROM EMP WHERE emp.deptno = dept.deptno );

This is not "dangerous" in the sense of being a bad idea - correlated 
queries are very useful.  However, there is a danger to be wary of: since 
the correlated subquery will be executed once per row returned by the 
outer query, it must be an efficient query otherwise performance wiil be 
slow.  In the above example, if there were 5000 departments, and each 
subquery execution took 0.01 seconds, then the total execution time would 
be 5000*0.01 = 50 seconds.  In this example, you would probably want an 
index on emp.deptno.
---
Change your mail options at http://p2p.wrox.com/manager.asp or 
to unsubscribe send a blank email to [email protected].