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 |
p |
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
- info9 is the network address of our database server
- you need to log in with a user, use guest (no password is
necessary, when logging in from our computers)
- ExperimentsExample
is the name of the database
-
the < indicates the file has used as input for
mysql
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?
-
Put your query in a file.
-
Start msql with the query file as input and an empty result file as
output:
-
mysql -h info9 -u guest ExperimentsExample <
example_query.txt > example_result.txt
-
The result file
-
Open the result file in Excel, it will use a Wizard to convert the text
file into rows and columns
-
Step 1:
-
Original data type: or you choose Delimited or Fixed width, both will
work
-
Start import at row 9
-
Step 2:
-
if you have chosen 'Delimited', you should choose space as delimiter
-
otherwise, indicate the columns (put the '|' in a seperated column)
-
Step 3: here you could for example skip the columns with the '|'
Last Updated on 04/9/2003
By Jan Lemeire