Database I, année 2022

Lab work is assumed to be done with Linux using most of the time the command-line or Python. If you are lost for technical reasons please ask for help. The goal of the lectures is not to train you with command line scripting.

Databases interactions

Most of databases have support to be integrated into many environement:

In most of the case, the interaction itself is done purely in SQL and the interface is mostly a way to simplify some of the work.

A database are often remote and we connect to them as they have a client/server layout. It is designed to be shared among many processes. A notable exception is SQLite which is a uniq file-based database.

SQLite

To create a Database we can simply execute query and provides it to the SQLite3 programs.

echo "SELECT 1" | sqlite3
1

Or we can precise a file to store data more permanently:

echo "
    CREATE TABLE Edges(x,y); --- It is untyped in SQLite
    INSERT INTO Edges(x,y) VALUES (0,1), (1, 2), (2,3);
" | sqlite3 /tmp/ex.db

Then you can execute queries on this file:

echo "
    SELECT E1.x, E2.y FROM Edges as E1, Edges as E2 WHERE E1.y = E2.x;
" | sqlite3 /tmp/ex.db
0|2
1|3

You can also execute it in Python if you want:

import sqlite3
db = sqlite3.connect("/tmp/ex.db")
print(list(db.execute("SELECT E1.x, E2.y FROM Edges as E1, Edges as E2 WHERE E1.y = E2.x")))
[(0, 2), (1, 3)]

You can also use sqlite3 as in interactive command line interface (very much like psql) just by typing sqlite3 ex.db or use a graphic inteface as dbbrowser (if installed).

Exercice

Download the database here which is stolen from Sławek Staworko You can explore the table definitions with the SQLite specifics command .schema:

echo '.schema' | sqlite3 disques.db
CREATE TABLE Artiste(
   ID INTEGER PRIMARY KEY,
   Nom TEXT
);
CREATE TABLE Disque (
  CDDB         TEXT PRIMARY KEY, 
  ID_Artiste   INT, 
  Titre        TEXT, 
  Annee_Sortie INTEGER,
  Prix         FLOAT,
  Etat         TEXT
);
CREATE TABLE Genre (
  ID   INTEGER PRIMARY KEY,
  Nom  TEXT,
  Description TEXT
);
CREATE TABLE Chanson (
   ID          INTEGER,
   Titre       TEXT,
   Texte_Libre BOOL,
   ID_Genre    INTEGER REFERENCES Genre(ID)
);
CREATE TABLE Ecrit (
  ID_Artiste INTEGER REFERENCES ARTISTE(ID),
  ID_Chanson INTEGER REFERENCES CHANSON(ID)
);
CREATE TABLE Contient (
  CDDB_Disque TEXT REFERENCES Disque(CDDB),
  Numero INTEGER,
  ID_Chanson INTEGER REFERENCES Chanson(ID),
  ID_Artiste INTEGER REFERENCES Artiste(ID)
);

Gives the queries for the following tasks:

Reads about Group By operations on the documentation of SQLite webpage and display the number of disque by Artiste.

PostgreSQL/psql presentations

To connect to PostgreSQL

psql -h postgresql-edu.priv.centralelille.fr -U first_name

To not remember the URL you can use:

export PGHOST=postgresql-edu.priv.centralelille.fr
export PGUSER=YOUR_FIRST_NAME

and then, simply type

psql 

Exercice

In Python:

In ipython, you can use the %time command and in psql you can use the \timing command to have the timing information in both world.

Try to improve your implementation if you can.


Mastodon