The Worlds of Database Systems

Why DBMS?

• Suppose that you want to build an university database. It must store the following information:

1. Entities: Students, Professors, Classes, Classrooms
2. Relationships: Who teaches what? Who teaches where? Who teaches whom

What can DBMS do for applications?

• Store huge amount of data (e.g., TB+) over a long period of time
• Allow apps to query and update data

1. Query: what is Mary’s grade in the “Operating System” course?
2. Update: enroll Mary in the “Database” course

• Protect from unauthorized access

1. Students cannot change their course grades.

• Protect from system crashes

1. When some system components fail (hard drive, network, etc.), database can be restored to a good state

• Protect from incorrect inputs

Mary has registered for 100 courses

• Support concurrent access from multiple users

1000 students using the registration system at the same time

• Allow administrators to easily change data schema

At a later time, add TA info to courses.

• Efficient database operations

Search for students with 5 highest GPAs

Alternative to Using a DBMS

• Store data as files in operating systems.
• Applications have to deal with the following issues:

1. 32-bit addressing (4GB) is insufficient to address 100GB+ data file
2. Write special code to support different queries
3. Write special code to protect data from concurrent access
4. Write special code to protect against system crashes
5. Optimize applications for efficient access and query
6. May often rewrite applications

• Easier to buy a DBMS to handle these issues

这样说明，数据库管理系统是能够给我们提供大量方便的。

Data Models

• A data model is a collection of concepts for describing data

1. Entity-relation (ER) model
2. Relational model (main focus of this course)

• A schema is a description of data

1. The relational model is the most widely used data model.

• A relation is basically a table with rows and columns of records.

Every relation has a schema, which describes the columns, or fields（字段）

Relational Model

The entire table shows an instance of the Students relation.

• The Students schema is the column heads

Students(Sid: String, Name: String, Login: String, age: Integer,… )

Levels of Abstractions in DBMS

Many views, one conceptual（概念模式）schema and one physical 、schema.

• Conceptual schema defines logical structure（概念模式定义的是逻辑结构）
• Physical schema describes the file and indexing used（物理模式定义的是文件和索引方式）
• Views describe how applications (users) see the data（储存的是一种数据之间的关系，并不 真实存在）

Example: University Database

• Conceptual schema:

1. Students (sid: string, name: string, login: string, age: integer, gpa:real)
2. Courses (cid: string, cname:string, credits:integer)
3. Enrolled (sid:string, cid:string, grade:string) --选课表

• Physical schema:

Relations stored as unordered files. Index on first column of Students.

• View (External Schema):

Course_info(cid:string, enrollment:integer)

Data Independence

Three levels of abstraction provides data independence.

• Changes in one layer only affect one upper layer.
• E.g., applications are not affected by changes in conceptual & physical schema.

Queries in DBMS

• Sample queries on university database:

What is the name of the student with student ID 123456?

• The key benefits of using a relational database are Easy to specify queries using a query language: Structured Query Language (SQL)

SELECT S.name FROM Students S WHERE S.sid = 123456

• Efficient query processor to get answer

Transaction Management

• A transaction is an execution of a user program in a DBMS.

• Transaction management deals with two things:

1. Concurrent execution of transactions（并发事务处理）
2. Incomplete transactions and system crashes（事务回滚机制）

Concurrency Control

• Example

two travel agents (A, B) are trying to book one remaining airline seat (two transactions), only one transaction can succeed in booking.

• How to solve this?

Solution: use locking protocol（锁机制）

Transaction A: get exclusive lock on num_seats Transaction B: wait until A releases lock on num_seats Transaction A: if num_seats > 0, book & num_seat--; // book the seat, num_seat is set to 0 Transaction A: release exclusive lock on num_seats Transaction B: num_seats = 0, no booking; // does not book the seat

Crash Recovery

• Example:

a bank transaction transfers $100 from account A to account B A = A - $100 <system crashes> // good for the bank! B = B + $100

• How to solve this?

Solution:use logging, meaning that all write operations are recorded in a log on a stable storage

A = A - $100 // recorded A value (checkpoint) in a log <system crashes> // start recovery: read the log from disk //analyze, undo, & redo

Overview of DBMS

Layered Architecture

• 应用层
• 查询优化和执行层
• 关系操作符层
• 文件和访问方法层
• 缓冲管理层
• 磁盘空间管理层

要注意的是，这些分层架构都应该考虑到并发管理的问题

ApplicationArchitectures

• 两层架构：客户端和数据库系统（客户端程序使用数据库互接或者Java连接与数据库进行通信）
• 三层架构：客户端、应用服务器和数据库系统（给予Web的程序、使用中间件构建的应用程序）