Data: from Latin given; given facts, from which additional facts can be inferred (= response to a user query)
Given fact: true proposition in logic
Database: collection of true propositions
Relational approach:
Data is represented by rows in tables, which can be interpreted as true propositions
Operators are provided for operating on rows in tables, providing additional true propositions from the existing ones
over application-specific files such as spreadsheets:
Standards are important efforts, but
The entire information content of the database is represented in only one way, explicit values in column positions in table rows.
Relational Databases are based on the relational model. Objects and operations defined in this theoretical approach are found in a similar form in practical implementations i.e. working DB management systems.
relation = math. for table, NOT from ER-model
A relation has two parts:
denotes a predicate (truth-valued function)
each tuple denotes a true proposition, i.e. instantiates the predicate
The Structured Query Language is the standard for data query and manipulation in relational database systems. It implements many (but not all) concepts of the relational model.
The basic concept of the SQL implementation is the table. When going from the relation model to the SQL implementation relations become tables.
An SQL table is similar to a relation, with some important differences:
Doubles:
Order:
SQL92 standard numeric data types:
INT (precision)
NUMERIC (precision, scale)
DECIMAL (precision, scale)
REAL
DOUBLE PRECISION
FLOAT (precision)
Other Types:
CHAR (size)
VARCHAR (size)
DATE
BOOLEAN
These are standard definitions. Implementations vary considerably, e.g.
Time for studying the documentation of your DBMS is well spent. Otherwise, subtle errors will haunt you in years to come.
SQLite is a simple but powerful DBMS that uses a single file in your current directory to store all data.
We load the SQL extensions into the current notebook:
%load_ext sql
The sql extension is already loaded. To reload it, use: %reload_ext sql
This allows us to use SQL commands in code cells. We need to start the cell with
First we connect to a database. We choose SQLite for convenience.
%sql sqlite:///sp.db
'Connected: @sp.db'
We ensure that foreign keys are enabled. This is specific to SQLite.
%%sql
PRAGMA foreign_keys = ON;
* sqlite:///sp.db Done.
[]
Now we are ready to create tables. However, since we probably want to run this code multiple times, and a table cannot be created with the same name twice, we must first make sure that existing tables are removed:
You local birding club decides to finally put some structure into all those reports from members and enter them into a database. The following things are reported by the birders:
The birders are fairly learned in the avian ways, and the common names they use identify the species. Location and nickname also identify their entities perfectly (e.g., no two birders use the same nickname). Birders always report the same type of terrain for a given location.
Basic SQL is quite intuitive. By looking at the following statement we can easily see what it does:
The statement could be written on a singe line, but it is more readable this way. Layout matters not to the computer, but to the human who may be forced to read and understand our statements at some point in the future.
Why do we first drop the table, if it exists? Due to the nature of Jupyter notebooks it is much more convenient to ensure that all code in a notebook can run from start to end repeatedly. It is possible to hand-pick the order of execution by running cells individually, but this gets out of hand very quickly.
%%sql
drop table if exists BIRDING;
create table BIRDING (
BIRDNAME varchar,
OBSDATE date,
LOCATION varchar,
TERRAIN varchar,
NICKNAME varchar);
* sqlite:///sp.db Done. Done.
[]
Now that we have the table structure, we can fill it with some data:
%%sql
insert into BIRDING values ('House sparrow', '2018-06-12', 'Cold Striffen', 'Garden', 'jsmith');
insert into BIRDING values ('Tree sparrow', '2018-06-12', 'Cold Striffen', 'Garden', 'jsmith');
insert into BIRDING values ('Blackcap', '2018-06-14', 'Sherwood Forest', 'Woodland', 'mjones');
insert into BIRDING values ('European robin', '2018-07-04', 'Little Winging', 'Garden', 'gmorgan');
insert into BIRDING values ('Common gull', '2017-06-01', 'Cliffs of Moher', 'Beach', 'pmurphy');
insert into BIRDING values ('Green woodpecker', '2018-06-15', 'Sherwood Forest', 'Woodland', 'mjones');
* sqlite:///sp.db 1 rows affected. 1 rows affected. 1 rows affected. 1 rows affected. 1 rows affected. 1 rows affected.
[]
To check if everything worked the way we expected, we generate a listing:
%%sql
select * from BIRDING;
* sqlite:///sp.db Done.
| BIRDNAME | OBSDATE | LOCATION | TERRAIN | NICKNAME |
|---|---|---|---|---|
| House sparrow | 2018-06-12 | Cold Striffen | Garden | jsmith |
| Tree sparrow | 2018-06-12 | Cold Striffen | Garden | jsmith |
| Blackcap | 2018-06-14 | Sherwood Forest | Woodland | mjones |
| European robin | 2018-07-04 | Little Winging | Garden | gmorgan |
| Common gull | 2017-06-01 | Cliffs of Moher | Beach | pmurphy |
| Green woodpecker | 2018-06-15 | Sherwood Forest | Woodland | mjones |
Select statements can use conditions on records to be displayed, and state the columns, e.g.
%%sql
select location, nickname from birding where obsdate < '2018-01-01';
* sqlite:///sp.db Done.
| LOCATION | NICKNAME |
|---|---|
| Cliffs of Moher | pmurphy |
Get the SQL code for the BIRDING table to run in your own Jupyter notebook.
If you want a little more challenge, install Jupyter on your own computer. You will probably need the following packages:
If you are using Linux (and why would you not??) then enter the following on the command line:
EXERCISE:
Your local chess community is organized into several clubs; each club has a name and a home location where practise games are offered, usually once a week, but subject to change. One function of a database solution would be to simply announce the various training evenings for the clubs, and their location. Another very useful feature would be a list of participants for each event, so that furniture and equipment can be prepared in suitable number. For each evenings one of the clubs is hosting; but members from other clubs are also welcome.
Fill in information as you see fit to make this more interesting. Create the tables and use some select statements for useful listings.