Informatics tools in network science

1
Informatics tools in network science

• seminar 2
• Database handling

2
storing information
(local) files
memory
database
speed
capacity
3
technical features
in-memory database
real-time database (transactions and concurrency)
pure databaseconcept
data warehouse system (big, reporting and
analysis)
distributed database
4
off-topic raid technologies
striping
mirroring
byte level parity
block level parity
against SPOF (Single Point Of Failure)
5
relational database

• storing data in tables

terminology
6
relational database

• storing data in tables
• data types

7
relational database

• storing data in tables
• data types
• table relations

ARTICLES

• title
• keyword
• author

storing articles
8
relational database

• storing data in tables
• data types
• table relations

ARTICLES

• title
• keyword1
• keyword2
• keyword3
• author

more keyword? no problem
9
relational database

• storing data in tables
• data types
• table relations

ARTICLES

• title
• keyword1
• keyword2
• keyword3
• author1
• author2
• author3

more author? why not
10
relational database

• storing data in tables
• data types
• table relations

ARTICLES

• title
• keyword1
• keyword2
• keyword3
• author1
• author2
• author3
• author1_contact
• author2_contact
• author3_contact

and what about the contacts?
11
relational database

• storing data in tables
• data types
• table relations

ARTICLES

• title
• keyword1
• keyword2
• keyword3
• author1
• author2
• author3
• author1_contact
• author2_contact
• author3_contact

• there is some serious problem here
• what if there is a 4th author?
• what if usually there are 2 authors (wasting
  memory)
• contacts are redundant!

12
relational database

• storing data in tables
• data types
• table relations

KEYWORDS

• keyword

ARTICLES

• title

AUTHORS

• name
• contact

13
relational database

• storing data in tables
• data types
• table relations

KEYWORDS
ARTICLES_KEYWORDS

• keyword ID
• keyword

• article ID
• keyword ID

ARTICLES

• article ID
• title

AUTHORS
ARTICLES_AUTHORS

• author ID
• name
• contact

• article ID
• author ID

14
relational database

• storing data in tables
• data types
• table relations

KEYWORDS
ARTICLES_KEYWORDS
article ID keyword ID
1 1
1 2
2 2
keyword ID keyword
1 cell
2 neurology
ARTICLES
article ID title
1 first title
2 other title
AUTHORS
ARTICLES_AUTHORS
author ID name contact
1 Bill 555-2316
2 Joe joe_at_mit.edu
article ID author ID
1 2
2 1
2 2
15
could be worse ?
16
Database servers
some example
MS SQL Server (professional)
sqLite (easy)
MySQL (free / professional)
postgre SQL (object oriented)
MS Access (MS Office)
Oracle (professional)
17
install MySQL

• Download MySQL Community Server 5.1 from
  http//dev.mysql.com/downloads/mysql/5.1.html

18
install MySQL

• Download MySQL GUI tools from http//dev.mysql.co
  m/downloads

19
SQL

• SQL Structured Query Language

Selecting all attributes of given data rows
SELECT FROM BookWHERE price gt 10.00 ORDER BY
title
of table called Book,
where the price is higher than 100.
Give back this list ordered by the title of the
title.
Title Author Price
Linked A. L. Barabási 12.5
The Lord of the Links J. K. Lowling 40.0
Weak Links P. Csermely 15.0
20
SQL

• SQL Structured Query Language

UPDATE My_tableSET field1 'updated
valueWHERE field2 'N'
SELECT isbn, title, price, price 0.06 AS
sales_taxFROM Book WHERE price gt 100.00 ORDER
BY title
DELETE FROM My_tableWHERE field2 'N'
INSERT INTO My_table (field1, field2,
field3)VALUES ('test', 'N', NULL)
21
MySQL Query Browser
22
SQL join tables
animals
foods
id animal
1 cat
2 dog
3 cow
id food
1 milk
2 bone
3 grass
Let us ask MySQL to list data from both table!
-------------------------------------
animals.id animal foods.id food
------------------------------------- 1
cat 1 milk 1
cat 2 bone 1 cat
2 grass 2 dog 1
milk 2 dog 2
bone 2 dog 2
grass 3 cow 1 milk
3 cow 2 bone 3
cow 2 grass
-------------------------------------
CROSS JOIN
SELECT FROM animals, foods
23
SQL join tables
sellings
pid sid selling
1 1 Old House Farm
3 2 The Willows
3 3 Tall Trees
3 4 The Melksham Florist
4 5 Dun Roamin
people
pid name phone
1 Mr Brown 01225 708225
2 Miss Smith 01225 899360
3 Mr Pullen 01380 724040
Let us ask MySQL to list data from both table!
mysqlgt select name, phone, sellingfrom people
join sellings on people.pid sellings.pid----
-------------------------------------------
name phone selling
---------------------------------------------
-- Mr Brown 01225 708225 Old House Farm
Mr Pullen 01380 724040 The Willows
Mr Pullen 01380 724040 Tall Trees
Mr Pullen 01380 724040 The
Melksham Florist ----------------------------
-------------------4 rows in set (0.01
sec)mysqlgt
INNER JOIN
SELECT name, phone, selling FROM people join
sellings on people.pid selling.pid
24
SQL join tables
sellings
pid sid selling
1 1 Old House Farm
3 2 The Willows
3 3 Tall Trees
3 4 The Melksham Florist
4 5 Dun Roamin
people
pid name phone
1 Mr Brown 01225 708225
2 Miss Smith 01225 899360
3 Mr Pullen 01380 724040
Let us ask MySQL to list data from both table!
INNER JOIN cross join WHERE but faster!!
SELECT name, phone, selling FROM people join
sellings on people.pid selling.pid
mysqlgt select name, phone, sellingfrom people,
sellings where people.pid sellings.pid------
-----------------------------------------
name phone selling
---------------------------------------------
-- Mr Brown 01225 708225 Old House Farm
Mr Pullen 01380 724040 The Willows
Mr Pullen 01380 724040 Tall Trees
Mr Pullen 01380 724040 The
Melksham Florist ----------------------------
-------------------4 rows in set (0.01
sec)mysqlgt
SELECT name, phone, selling FROM people,
sellings WHERE people.pid selling.pid
25
SQL join tables
sellings
pid sid selling
1 1 Old House Farm
3 2 The Willows
3 3 Tall Trees
3 4 The Melksham Florist
4 5 Dun Roamin
people
pid name phone
1 Mr Brown 01225 708225
2 Miss Smith 01225 899360
3 Mr Pullen 01380 724040
Let us ask MySQL to list data from both table!
mysqlgt select name, phone, selling from people
left join sellings on people.pid sellings.pid
----------------------------------------------
-- name phone selling
----------------------------------------
-------- Mr Brown 01225 708225 Old House
Farm Miss Smith 01225 899360 NULL
Mr Pullen 01380 724040 The
Willows Mr Pullen 01380 724040
Tall Trees Mr Pullen 01380
724040 The Melksham Florist ----------------
--------------------------------5 rows in set
(0.00 sec)mysqlgt
LEFT JOIN
SELECT name, phone, selling FROM people left
join sellings on people.pid selling.pid
26
SQL join tables
sellings
pid sid selling
1 1 Old House Farm
3 2 The Willows
3 3 Tall Trees
3 4 The Melksham Florist
4 5 Dun Roamin
people
pid name phone
1 Mr Brown 01225 708225
2 Miss Smith 01225 899360
3 Mr Pullen 01380 724040
Let us ask MySQL to list data from both table!
mysqlgt select name, phone, selling from people
left join sellings on people.pid sellings.pid
----------------------------------------------
-- name phone selling
----------------------------------------
-------- Mr Brown 01225 708225 Old House
Farm Mr Pullen 01380 724040 The
Willows Mr Pullen 01380 724040
Tall Trees Mr Pullen 01380
724040 The Melksham Florist NULL
NULL Dun Romain
---------------------------------------------
---5 rows in set (0.00 sec)mysqlgt
RIGHT JOIN
SELECT name, phone, selling FROM people right
join sellings on people.pid selling.pid
27
SQL join tables
sellings
people
pid sid selling
1 1 Old House Farm
3 2 The Willows
3 3 Tall Trees
3 4 The Melksham Florist
4 5 Dun Roamin
pid name phone
1 Mr Brown 01225 708225
2 Miss Smith 01225 899360
3 Mr Pullen 01380 724040
Use aggregated function and join
mysqlgt select name, phone, sellingfrom people,
sellings where people.pid sellings.pid------
-----------------------------------------
name phone selling
---------------------------------------------
-- Mr Brown 01225 708225 Old House Farm
Mr Pullen 01380 724040 The Willows
Mr Pullen 01380 724040 Tall Trees
Mr Pullen 01380 724040 The
Melksham Florist ----------------------------
-------------------4 rows in set (0.01
sec)mysqlgt
SELECT name, COUNT(sid) as
selling_num FROM people join sellings on
people.pid selling.pid GROUP BY selling.pid
------------------------- name
selling_num ------------------------- Mr
Brown 1 Mr Pullen 3
-------------------------
28
SQL join tables
sellings
people
pid sid selling
1 1 Old House Farm
3 2 The Willows
3 3 Tall Trees
3 4 The Melksham Florist
4 5 Dun Roamin
pid name phone
1 Mr Brown 01225 708225
2 Miss Smith 01225 899360
3 Mr Pullen 01380 724040
Use aggregated function and (left) join
mysqlgt select name, phone, selling from people
left join sellings on people.pid sellings.pid
----------------------------------------------
-- name phone selling
----------------------------------------
-------- Mr Brown 01225 708225 Old House
Farm Miss Smith 01225 899360 NULL
Mr Pullen 01380 724040 The
Willows Mr Pullen 01380 724040
Tall Trees Mr Pullen 01380
724040 The Melksham Florist ----------------
--------------------------------5 rows in set
(0.00 sec)mysqlgt
SELECT name, COUNT(sid) as
selling_num FROM people left join sellings on
people.pid selling.pid GROUP BY selling.pid
-------------------------- name
selling_num -------------------------- Mr
Brown 1 Miss Smith 0
Mr Pullen 3
--------------------------
29
SQL more info

• http//www.w3schools.com/SQl/default.asp
• MySQL 5.1 Reference Manual http//dev.mysql.com/d
  oc/refman/5.1/en/index.html
• http//en.wikipedia.org/wiki/SQL