CIS 331: Database Management Using SQL

Review for Second Test

 

1. What is a multiple table query? Why does the text sometimes refer to it as a JOIN operation?
   
   
2. When performing a multiple table query, what part of the command relates the tables?
   
   
3. Why do you qualify some field names in a query? How do you do so?
   
   
4. What clauses in a query are most likely to use qualified field names?
   
   
5. What is a subquery? What are some operators that can be used between inner and outer queries?
   
   
6. When a subquery makes reference to the table that is only listed in the FROM clause of the outer query, what do you call the subquery?
   
   
7. What do you call a subquery inside another subquery?
   
   
8. When queries are nested, in what order are they evaluated? What math convention is used that makes this more understandable?
   
   
9. What is an alias for a table? Why might you use an alias?
   
   
10. What is a self join? The text shows an example of one. What was the reason given for performing this kind of join?
    
    
11. What are set operations? How many tables do you need to perform a set operation?
    
    
12. What is the difference set operation? What would it result in?
    
    
13. What is an intersection operation?
    
    
14. What is a union operation? What must be true of tables to make them union compatible?
    
    
15. When do you use the ANY operator?
    
    
16. When do you use the ALL operator?
    
    
17. What is the difference between an inner join and an outer join?
    
    
18. What are the three kinds of outer join? What do their names refer to?
    
    
19. How can you accidentally create a PRODUCT query? Why may you want to avoid this?
    
    
20. What is normalization? What are the names of the standard normal forms? What update anomalies can occur if tables are not normalized?
    
    
21. How do you tell if a table is in first normal form?
    
    
22. How do you tell if a table is in second normal form?
    
    
23. How do you tell if a table is in third normal form?
    
    
24. Give an example that explains the phrase "functionally dependent".
    
    
25. Give an example that explains the phrase "functionally determines".
    
    
26. What is a determinant?
    
    
27. What is a key? What are the kinds of keys discussed in your texts? How do keys relate to normalization?
    
    
28. How can we have update problems as a result of lack of normalization?
    
    
29. How can we have inconsistent data as a result of not having normalized our tables?
    
    
30. What is a partial dependency?
    
    
31. What is multivalued dependence? How might it occur in a database?
    
    
32. What are the functions of a DBMS discussed in the text?
    
    
33. How might a DBMS support concurrent update from multiple users?
    
    
34. Why is disaster recovery a necessary feature of a DBMS?
    
    
35. What is the purpose of data replication? What other feature in the chapter would this support?
    
    
36. What is meant by catalog services in a DBMS? Who should have access to the catalog of a DBMS?
    
    
37. What are two versions of locking that might be used by a DBMS? What problem is locking meant to avoid?
    
    
38. When a user sends an update to the DBMS, what is the general term for that action?
    
    
39. What is a deadly embrace? What term is used in your text for this kind of problem?
    
    
40. How is a deadlock typically resolved? Who is the victim in the resolution? What should happen to the victim's data?
    
    
41. What is timestamping transactions? Why would we do that?
    
    
42. How are encryption and decryption used in database systems?
    
    
43. How is authentication used in a DBMS? What two pieces of information must a user typically have?
    
    
44. How does biometric authentication work?
    
    
45. How is authorization different from authentication?
    
    
46. What is an integrity constraint? What kind of errors will it avoid?