Databases / Chapter 1: What is a Database? / Introduction To Databases

Welcome to the databases course! A database is fundamentally a collection of data. Thus, databases share many characteristics with collections that we studied in the Data Structures course, such as lists and maps. Databases are also similar to files and folders since they both store data in the hard drive. Finally, databases are like spreadsheets since the data is stored in rows and columns.

In this course, we are going to study relational databases. We can think of a relational database as a set of tables that are linked to each other in some way. For example, the following table contains names and addresses of people in the US:

Given Name	Family Name	Street	City	State
Curtis	Conway	123 Easy Street	Portland	Oregon
Billy	Homes	910 Starry Road	Cleveland	Ohio
Amy	Milton	8324 Main Avenue	Sonoma	California
Matt	Lions	2370 Park Boulevard	Miami	Florida

Database tables have names. The table above is called addresses.

Here is another table called profiles that contains profile information:

Given Name	Family Name	Birthdate	Weight (in kg)	Height (in cm)
Amy	Milton	1955/01/21	60	165
Billy	Homes	2003/07/04	51	169
Curtis	Conway	1993/10/19	82	177

addresses and profiles are part of the database called people. The two tables are associated by the given and family names. For example, we can get Curtis Conway's address from the addresses table and birthday from the profiles table.

We can retrieve information from the database by issuing queries. In this course, we are going to use SQL, which stands for Structured Query Language. SQL is by far the most popular database query language in the world.

A single database server manages many databases, so we first need to choose the database we want to query. For example, if we want to query the people database, we issue the following command:

USE people

We can now start querying the tables in the people database. For example, if we want to know Billy Homes's birthday, we can issue the following query:

SELECT birthdate FROM profiles WHERE given_name="Billy" AND family_name="Homes"

The database server looks for Billy Homes in the profiles table of the people database and prints the following output:

2003/07/04

We can also insert a new address. Here is an example:

INSERT "Chris", "Kane", "1 Third Avenue", "Detroit", "Michigan" TO addresses

After the databases processes this statement, the addresses table is updated as follows:

Given Name	Family Name	Street	City	State
Curtis	Conway	123 Easy Street	Portland	Oregon
Billy	Homes	910 Starry Road	Cleveland	Ohio
Amy	Milton	8324 Main Avenue	Sonoma	California
Matt	Lions	2370 Park Boulevard	Miami	Florida
Chris	Kane	1 Third Avenue	Detroit	Michigan

We can also update 1 or more rows in the table. For example, if we want to update Amy's weight to 64, we can issue the following query:

UPDATE profiles SET weight=64 WHERE family_name=Milton

After the databases processes this statement, the addresses table is updated as follows:

Given Name	Family Name	Birthdate	Weight (in kg)	Height (in cm)
Amy	Milton	1955/01/21	64	165
Billy	Homes	2003/07/04	51	169
Curtis	Conway	1993/10/19	82	177

The database updates the weight column of every row with the family name "Milton." This means that if the table contains 1000 rows with the family name "Milton," the database updates all 1000 rows.

Finally, we can delete 1 or more rows from the table. For example, if we want to delete Billy Homes from the addresses table, then we can issue the following query:

DELETE FROM addresses WHERE given_name="Billy" AND family_name="Homes"

Whenever we modify the database, the database server writes the changes to the hard drive. Thus, powering down or rebooting the database computer do not result in data loss.

How Users Interact with Databases

The database server is a process that serves client requests. As with any server, the database server waits for network messages to arrive on a designated port (the port number depends on the database).

Clients use client shell programs to type and send queries to the database server. The client program sends the query over the network and waits for the server to respond.

When the database server receives the query, the server processes the request and sends the output back to the client.

Finally, when the client program receives the server output, the client prints the output to the client's shell.

Database Simulator

Let's execute some database queries in the simulator below. Run the following command in TERMINAL_B to start the database server:

> serve_db /sql_db

Then run the following command in TERMINAL_A to start the client shell:

> sql b.com

The client prints the SQL shell as follows:

sql>

First, we choose the database:

sql> USE baseball

We can now start issuing queries. Here is an example:

sql> SELECT given_name FROM players

Next Lesson