CPSC 310 Database Systems CPSC 603 Database Systems

CPSC 310 Database Systems CPSC 603 Database Systems and Applications Fall 2005 Prof. Jennifer Welch CPSC 310/603 Fall 2005 1

References u. Database Systems, The Complete Book, Hector Garcia-Molina, Jeffrey D. Ullman, and Jennifer Widom, Prentice Hall 2002. u. Slides by Jeffrey Ullman: http: //www-db. stanford. edu/~ullman/dscb/pslides. html u. Slides by Hector Garcia-Molina: http: //www-db. stanford. edu/~hector/cs 245/notes. htm u. Slides by Arthur Keller: http: //www. soe. ucsc. edu/classes/cmps 180/Winter 02 u. Slides by Rada Chirkova: http: //courses. ncsu. edu/csc 540/lec/001/ CPSC 310/603 Fall 2005 2

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

Relational Model u u u 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: account. No balance type 12345 1000. 0 savings 67890 2846. 92 checking CPSC 310/603 Fall 2005 4

Structured Query Language (SQL) Preview Accounts account. No balance type 12345 67890 1000. 00 2846. 92 savings checking SELECT balance FROM Accounts WHERE account. No = 67890; CPSC 310/603 Fall 2005 SELECT account. No FROM Accounts WHERE type = 'savings' AND balance < 0; 5

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

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

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

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

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

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