2.1 Introduction

2.1 Introduction#

You already know how to delete in SQL. Just use a DELETE FROM statement, and the rows you select are gone:

csc343h-dianeh=> select * from course;
 cnum |           name            | dept | breadth 
------+---------------------------+------+---------
| Intro to Databases        | CSC  | f
| Software Design           | CSC  | f
| Intro to Comp Sci         | CSC  | f
| Data Struct & Anal        | CSC  | f
| Intro to Visual Computing | CSC  | f
| Intro Archaeology         | ANT  | t
| Human Biol & Evol         | ANT  | f
| Organisms in Environ      | EEB  | f
| Marine Mammal Bio         | EEB  | f
| Compar Vert Anatomy       | EEB  | f
| Narrative                 | ENG  | t
| Rhetoric                  | ENG  | t
| The Graphic Novel         | ENG  | t
| Environmental Change      | ENV  | f
| Natl & Intl Env Policy    | ENV  | f
| Mediaeval Society         | HIS  | t
| Black Freedom             | HIS  | t
| COBOL programming         | CSC  | f
(18 rows)

csc343h-dianeh=> delete from course where name like '%COBOL%';
DELETE 1
csc343h-dianeh=> select * from course;
 cnum |           name            | dept | breadth 
------+---------------------------+------+---------
| Intro to Databases        | CSC  | f
| Software Design           | CSC  | f
| Intro to Comp Sci         | CSC  | f
| Data Struct & Anal        | CSC  | f
| Intro to Visual Computing | CSC  | f
| Intro Archaeology         | ANT  | t
| Human Biol & Evol         | ANT  | f
| Organisms in Environ      | EEB  | f
| Marine Mammal Bio         | EEB  | f
| Compar Vert Anatomy       | EEB  | f
| Narrative                 | ENG  | t
| Rhetoric                  | ENG  | t
| The Graphic Novel         | ENG  | t
| Environmental Change      | ENV  | f
| Natl & Intl Env Policy    | ENV  | f
| Mediaeval Society         | HIS  | t
| Black Freedom             | HIS  | t
(17 rows)

Let’s go under the hood to see what has to happen to make this occur. It’s a little more complicated than one might think.

Some background on storage#

First we need to think about how data is stored on a computer.

In order for a program to use data — to examine its value or perform operations on it — that data must be in memory. The variables used in a program, whether ints that take only a few bytes, or large data structures consuming a lot of space, are all stored in memory. However, a relational database is designed to be capable of handling data that is far too large to fit in memory all at once. What won’t fit in memory must be kept in secondary storage and brought into memory when it is needed.

Unfortunately, working in secondary storage (for example reading data from there or writing data to there) is orders of magnitude slower than working in memory.

Putting DBMSs aside for a moment, the slowness of file operations comes up even with very simple code such as this:

with open('data.txt', 'r') as f:
    one_line = f.readline()
    print(one_line)

When we ask to read a single line from the file and put it into the variable one_line, the operating system decides instead to read a big chunk of data (called called a “block” or “page”) and store it in a buffer in memory. In the long run, retrieving and storing a large block is very likely to save time. The next time the program asks for something from the file, there is a good chance it is already in the buffer and can be retrieved from there. Thus, a very slow file read is avoided in favour of a very fast buffer read.

The OS manages multiple buffers and hides all these details from you; you can write a Python program that does file input and output without even knowing that buffering is going on.

A block is quite large. On current systems, it is typcially 4K, 8K, or 16K bytes.

2.1 Introduction

Contents

2.1 Introduction#

Some background on storage#