Table of Contents
Databases on 1 Foot
Overview for the day
What we’re skipping
More Background
Database Management Systems
Describing Data: Data Models
Levels of Abstraction
Example: University Database
Data Independence
Structure of a DBMS
Advantages of a DBMS
Relational Algebra
Relational Query Languages
Formal Relational Query Languages
Preliminaries
Example Instances
Relational Algebra
Projection
Selection
Union, Intersection, Set-Difference
Cross-Product
Joins
Joins
Basic SQL Query
Conceptual Evaluation Strategy
Example of Conceptual Evaluation
A Note on Range Variables
Find sailors who’ve reserved at least one boat
Expressions and Strings
Aggregate Operators
Find name and age of the oldest sailor(s)
GROUP BY and HAVING
Queries With GROUP BY and HAVING
Conceptual Evaluation
Find the age of the youngest sailor with age ? 18, for each rating with at least 2 such sailors
For each red boat, find the number of reservations for this boat
Find the age of the youngest sailor with age > 18, for each rating with at least 2 sailors (of any age)
Find those ratings for which the average age is the minimum over all ratings
Null Values
Embedded SQL
Cursors
Cursor that gets names of sailors who’ve reserved a red boat, in alphabetical order
Embedding SQL in C: An Example
Database APIs: alternative to embedding
SQL API in Java (JDBC)
Concurrency Control & Recovery. Why Have Concurrent Processes?
Query Optimization & Processing
Transactions
The ACID Properties
Passing the ACID Test
Schedules
Acceptable Schedules
Serializability Violations
More Conflicts
Locking: A Technique for C. C.
Two-Phase Locking (2PL)
Strict 2PL
Precedence Graph
Conflict Serializability
Conflict Serializability & Graphs
Lock Manager Implementation
Handling a Lock Request
More Lock Manager Logic
Lock Upgrades
Deadlock Detection
Prevention vs. Detection
Multiple-Granularity Locks
Solution: New Lock Modes, Protocol
Examples
Logging and Recovery: Motivation
Assumptions
Handling the Buffer Pool
More on Steal and Force
Basic Idea: Logging
Write-Ahead Logging (WAL)
|
Author: Joe Hellerstein
Email: cs294@db.cs.berkeley.edu
Home Page: http://www.cs.berkeley.edu/~jmh
Download presentation source
|