Required Textbook:
| Title | Fundamentals of Database Systems |
| Author | Ramez A. Elmasri and Shamkant B. Navathe |
| Publisher | Addison-Wesley Publishing |
| ISBN | 0805317554 |
| Title | Practical PostgreSQL |
| Author | John C. Worsley and Joshua D. Drake |
| Publisher | O'Reilly |
| ISBN | 1-56592-846-6 |
From the book: Chapter 1, most of 1.1 should be familiar, 1.4, 1.7, and 1.8 What are the advantages and disadvantages of a database compared to another storage paradigm? Identify two examples where a database is (or might be) employed. Going through the list of advantages from using a DBMS that the book provides consider which ones are the most important for this use of a database. If you ignore that factor would it still make sense to use a database? Chapter 2, most of 2.1 should be familiar, 2.3, 2.4, 2.5, 2.11 (on a small scale.) Try to recall (and draw) the basic structure of a database from memory. What does each part do? What implication does having a three schema architecture have on the task of integrating database schemas developed by separate groups? We haven't finished Chapter 3 yet, so many of these will be for later Chapter 3, most of 3.3 should be familiar, 3.4, 3.5, 3.9, 3.12, 3.13, 3.18, 3.19, consider the other exercises and try some out.
From the book: 4.1, 4.3, 4.7, 4.9, 4.17 What is an ontology? What are the main trade-offs between databases and knowledge representation systems? From the book 5.1, 5.2, 5.3, 5.7, 5.9, 5.16, 5.20, 6.1, 6.2, 6.3, 6.4, 6.6, 6.12, 6.14 a-g
From Chapter 7:
Be familiar with terms in 7.1, 7.4, 7.6, 7.8, 7.9.
What is the difference between Natural Join, Cross Product, and Division.
Describe the return type from each of the algebraic operations. What property
holds for the set of all of the return types, and is it important?
The following are operations on the cs687 database on skyhawk:
- create a table, with 4 attributes
- drop the table
- insert a value into each of the existing tables (keep in mind that there
are some foreign key constraints)
- perform a query that:
- lists all of the elements in one of the tables
- does a join on the user and empire tables
- finds the highest score in all of the games
- finds the highest score in just one of the games
- create a view that duplicates your earlier join of user and empire tables
From Chapter 8:
8.1
From Chapter 9:
9.2, 9.4, 9.5, 9.7
Choose one of the (small) ER diagrams in the book and practice mapping it to the
relational model.
Restate some of the operations that you performed in PostgreSQL as calculus formulas.
Chapter 11 in the book: 11.2, 11.3, 11.5, 11.6, 11.11, and 11.14. Chapter 12 in the book: 12.1, 12.7, 12.8, 12.9, and 12.11. How are relationships represented in an OODB? How does this compare to the relational model? Compare how you would write a simple query in the Object Query Language as compared to SQL. What is the Object Relational model? Describe the following, having clause, limit and offset, and case when statements. How can a subselect be used in a query? Describe a sequence and what it is used for. Describe triggers.
From the book, chapter 14: 14.3, 14.4, 14.6, 14.7, 14.8, 14.10, 14.11, 14.12, 14.14, 14.15, 14.19, 14.21 chapter 15: 15.1 - 15.3, 15.6 - 15.9, 15.13, 15.14 Give examples of relations in each of the normal forms. chapter 19: 19.1, 19.5 - 19.9, 19.17, 19.22 chapter 20: 20.1, 20.3 (and livelock), 20.5, 20.8, 20.10 Sketch the different two phase locking protocols. Choose a protocol described and prove it provides concurrency control.
from Chapter 21 in the book: 21.2, 21.6, 21.9, 21.10, 21.11, 21.18, 21.19, 21.35, 21.36, 21.37 from Chapter 18 in the book: 18.1, one reasonably efficient algorithm each for 18.2, 18.3, 18.5-8, 18.12 from Chapter 26 in the book: 26.1, 26.3-5, 26.7, 26.13-26.15