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/p
  slides.html
• Slides by Hector Garcia-Molina
• http//www-db.stanford.edu/hector/cs245/notes.ht
  m
• Slides by Arthur Keller http//www.soe.ucsc.edu/c
  lasses/cmps180/Winter02
• Slides by Rada Chirkova http//courses.ncsu.edu/c
  sc540/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 lt 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)