Relational Database tutorial

The experiments database

 1. data - 2. ERD - 3. creation - 4. queries - 5. C - 6. data analysis

Datatabase are used to store data in a structured way. Let's explain the basics with a small example.

1. The Data

Assume I want to store the results of experiments performed at the lab. I group the data in 4 tables. Each row contains the information about 1 specific item or entity,  the columns represent the information elements.

experiments
ID  name creation who  comment
1 my first parallel program 22/2/2002  jan 4 just a test
2 parallel genetic algorithm 22/3/2002  jan 8
3 parallel curve measurements 25/3/2002  erik 8 speedup curve 
p is the number of processors of the parallel experiment. The who column is a person selected from the users table.

users
name  function 
jan assistant
johan assistant
erik professor
andy student

variables
name isParallel  explanation
Ts FALSE sequential processing time
Tp TRUE parallel processing time
Tpart TRUE partitioning time
Tsync TRUE synchronisation time
Tcomm  TRUE communication time
these are all variables that are measured in an experiment.

measurements
experimentID variable value
1 Ts 101.5
1 Tp 51.2
1 Tpart 5.3
1 Tsync 10.2
1 Tcomm 12.2
2 Ts 12356.5
2 Tp 8562.6
2 Tpart 256
2 Tsync 5023.3
2 Tcomm 2036.6
3 Ts 2305.6
3 Tp 805.6
3 Tpart 56.2
3 Tsync 12.6
3 Tcomm 98.5
finally, for each experiment, all variables are measured and stored in the database.
 
 

2. Entity-Relationship Diagram

The definition of the database is represented by the ERD:
For each table we list the columns or attributes. The attributes annotated with a * represent the primary key of the table, meaning that the value of the key is unique for each row (no doubles), it identifies an entity. Attributes with a - are optional, meaning that they don't need to be filled in, the others attributes are mandatory.
Another important feature in relational databases is the relationship between the tables. In the experiments table, the who attribute is a member of the users table. This connects both tables, what is indicated with . Moreover, we denote the cardinality of the relationship. Here, one user will have 0, 1 or more experiments, indicated at the left side of the connection. On the other hand, 1 experiment is performed by exactly 1 user, this is indicated at the right side of the connection. The experiments table is connected with the measurements table with a one-to-many relationship, but one experiment must have at least 1 measurement. So the connection symbol is .
 
 
 

3. Database creation

The Database Definition file ddt.txt  with CREATE SQL commands.
Upload this file with the mysql monitor (in a console or terminal):

    mysql -h info9 -u guest ExperimentsExample < ddt.txt

 You don't have to redo this, I already created the database. Note that for each primary key, we created a unique index. Check the definition of the database with mysqlshow:

    mysqlshow -h info9 -u guest ExperimentsExample

=> you get all the tables

    mysqlshow -h info9 -u guest ExperimentsExample experiments

=> the definition of the table experiments
 

4. Database access

Add the data with the INSERT statements. First start the mysql monitor

    mysql -h info9 -u guest ExperimentsExample

mySQL> INSERT INTO users VALUES ('john', 'student');

Remark that the ; is to end the query and send it to the database.
All INSERT commands to fill the database.
Also try the SELECT statements:

mySQL> SELECT * FROM experiments\g
 
 

5. Database access in C

mySQL provides a mySQL API library to access the database.
Let's illustrate this with an example program, download the following files: Create the Makefile with qmake:
qmake experiments.pro
Make the executable:
make all
Run it:
experiments
Study the code of experiments.cc,  change the query, etc.
 

6. Data analysis

Once you have added data to the database, it is ready to be analysed for reporting, Microsoft Excel is a good tool herefore.
But, how get the data in Excel?
  1. Put your query in a file.
  2. Start msql with the query file as input and an empty result file as output:
  3. Open the result file in Excel, it will use a Wizard to convert the text file into rows and columns




Last Updated on 04/9/2003
By Jan Lemeire