The Worlds of Database Systems

32-bit addressing (4GB) is insufficient to address 100GB+ data file
Write special code to support different queries
Write special code to protect data from concurrent access
Write special code to protect against system crashes
Optimize applications for efficient access and query
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

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

A schema is a description of data

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:

Students (sid: string, name: string, login: string, age: integer, gpa:real)
Courses (cid: string, cname:string, credits:integer)
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:

Concurrent execution of transactions（并发事务处理）
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