CPSC 310 Database Systems CPSC 603 Database Systems and Applications

1. CPSC 310 Database SystemsCPSC 603 Database Systemsand Applications Fall 2005 Prof. Jennifer Welch

2. References • Database Systems, The Complete Book, Hector Garcia-Molina, Jeffrey D. Ullman, and Jennifer Widom, Prentice Hall 2002. • Slides by Jeffrey Ullman: http://www-db.stanford.edu/~ullman/dscb/pslides/pslides.html • Slides by Hector Garcia-Molina: http://www-db.stanford.edu/~hector/cs245/notes.htm • Slides by Arthur Keller: http://www.soe.ucsc.edu/classes/cmps180/Winter02 • Slides by Rada Chirkova: http://courses.ncsu.edu/csc540/lec/001/

3. Database Management System (DBMS) • Specialized software that manages access to very large amounts of data: • data persists over a long time • efficient access • concurrent access • reliable and predictable access • convenient interface language • Examples: airline reservation systems, banking, corporate records

4. Relational Model • Store information in tables • Each table is a relation • Each column is named with an attribute • Each row is a tuple • Example relation named Accounts:

5. Structured Query Language (SQL) Preview Accounts SELECT accountNo FROM Accounts WHERE type = 'savings' AND balance < 0; SELECT balance FROM Accounts WHERE accountNo = 67890;

6. Setting up a Database • Database schema specifies what relations are in the database • Schema is altered using DDL (data definition language) commands • Only database administrator should be able to do this • Schema affects how the data is stored

7. Querying the Database • A query is expressed in a DML (data manipulation language), e.g. SQL • The query compiler translates query into a query plan (sequence of operations to be performed). • Query compilation includes query optimization, to choose best sequence of operations • Execution engine performs the operations, interacting with…

8. Storage and Buffer Management • Data usually is stored on disk, in units called blocks • Storage manager controls movement of data between disk and main memory • Buffer manager controls partitioning of main memory into block-sized regions • Relies on info about data, schema, statistics, and indexes (special data structures for efficient access)

9. Transactions • Usually database queries are grouped into transactions • A transaction must satisfy ACID properties: • Atomicity: either all the changes or none of them are made to the database • Consistency: transaction must preserve consistency constraints of the database • Isolation: the result must be "as if" the transaction ran alone • Durability: once transaction has completed, changes must not be lost

10. Transaction Processing • Every change is logged separately on disk by log manager • After a system failure, recovery manager uses log on disk to reconstruct a consistent state • Scheduler ensures that concurrently executing transactions do not interfere with each other

11. Outline of Course • Database Design: • entity-relationship model: notation to express relationships among the data (Ch 2) • relational model: easier to implement than E-R (Ch 3) • Database Programming: • relational algebra: how to operate on relations (Ch 5) • programming with SQL (Chs 6-8) • Database Implementation: • storage management (Chs 11-13) • query processing (Chs 15-16) • transaction processing (Chs 17-19)