Seminar 2 database handling
This presentation is the property of its rightful owner.
Sponsored Links
1 / 29

Informatics tools in network science PowerPoint PPT Presentation


  • 101 Views
  • Uploaded on
  • Presentation posted in: General

seminar 2 Database handling. Informatics tools in network science. storing information. memory. (local) files. database. speed. capacity. technical features. in-memory database. real-time database (transactions and concurrency). pure database concept. data warehouse system

Download Presentation

Informatics tools in network science

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Seminar 2 database handling

seminar 2

Database handling

Informatics tools in network science


Storing information

storing information

memory

(local) files

database

speed

capacity


Technical features

technical features

in-memory database

real-time database

(transactions and concurrency)

pure databaseconcept

data warehouse system

(big, reporting and analysis)

distributed database


Off topic raid technologies

off-topic: raid technologies

“striping”

“mirroring”

“byte level parity”

“block level parity”

against SPOF (Single Point Of Failure)


Relational database

relational database

  • storing data in tables

terminology:


Relational database1

relational database

  • storing data in tables

  • data types


Relational database2

relational database

  • storing data in tables

  • data types

  • table relations

ARTICLES

  • title

  • keyword

  • author

storing articles


Relational database3

relational database

  • storing data in tables

  • data types

  • table relations

ARTICLES

  • title

  • keyword1

  • keyword2

  • keyword3

  • author

more keyword? no problem…


Relational database4

relational database

  • storing data in tables

  • data types

  • table relations

ARTICLES

  • title

  • keyword1

  • keyword2

  • keyword3

  • author1

  • author2

  • author3

more author? why not…


Relational database5

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?


Relational database6

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!


Relational database7

relational database

  • storing data in tables

  • data types

  • table relations

KEYWORDS

  • keyword

ARTICLES

  • title

AUTHORS

  • name

  • contact


Relational database8

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


Relational database9

relational database

  • storing data in tables

  • data types

  • table relations

ARTICLES_KEYWORDS

KEYWORDS

ARTICLES

AUTHORS

ARTICLES_AUTHORS


Informatics tools in network science

could be worse… 


Database servers

Database servers

some example:

MS SQL Server

(professional)

sqLite

(easy)

MySQL

(free / professional)

postgre SQL

(object oriented)

MS Access

(MS Office)

Oracle

(professional)


Install mysql

install MySQL

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


Install mysql1

install MySQL

  • Download MySQL GUI tools from: http://dev.mysql.com/downloads


Informatics tools in network science

SQL

  • SQL = Structured Query Language

Selecting all attributes of given data rows…

SELECT * FROM BookWHERE price > 10.00 ORDERBY title;

…of table called “Book”,

…where the price is higher than 100.

Give back this list ordered by the title of the title.


Informatics tools in network science

SQL

  • SQL = Structured Query Language

UPDATE My_tableSET field1 = 'updated value‘WHERE field2 = 'N';

SELECTisbn, title, price, price * 0.06 AS sales_taxFROM Book WHERE price > 100.00 ORDERBY title;

DELETEFROM My_tableWHERE field2 = 'N';

INSERTINTO My_table (field1, field2, field3)VALUES ('test', 'N', NULL);


Mysql query browser

MySQL Query Browser


Sql join tables

SQL – join tables

animals

foods

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;


Sql join tables1

SQL – join tables

sellings

people

Let us ask MySQL to list data from both table!

mysql> 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)mysql>

INNER JOIN

SELECT name, phone, selling

FROM people join sellingson people.pid = selling.pid;


Sql join tables2

SQL – join tables

sellings

people

Let us ask MySQL to list data from both table!

INNER JOIN = cross join + WHERE but faster!!

SELECT name, phone, selling

FROM people join sellingson people.pid = selling.pid;

mysql> 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)mysql>

SELECT name, phone, selling

FROM people, sellings

WHERE people.pid = selling.pid;


Sql join tables3

SQL – join tables

sellings

people

Let us ask MySQL to list data from both table!

mysql> 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)mysql>

LEFT JOIN

SELECT name, phone, selling

FROM people left join sellingson people.pid = selling.pid;


Sql join tables4

SQL – join tables

sellings

people

Let us ask MySQL to list data from both table!

mysql> 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)mysql>

RIGHT JOIN

SELECT name, phone, selling

FROM people right join sellingson people.pid = selling.pid;


Sql join tables5

SQL – join tables

sellings

people

Use aggregated function and join:

mysql> 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)mysql>

SELECT name, COUNT(sid) as selling_num

FROM people join sellingson people.pid = selling.pid

GROUP BY selling.pid;

+-----------+--------------+| name | selling_num |+-----------+--------------+| Mr Brown | 1 || Mr Pullen | 3 |+-----------+--------------+


Sql join tables6

SQL – join tables

sellings

people

Use aggregated function and (left) join:

mysql> 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)mysql>

SELECT name, COUNT(sid) as selling_num

FROM people left join sellingson people.pid = selling.pid

GROUP BY selling.pid;

+------------+--------------+| name | selling_num |+------------+--------------+| Mr Brown | 1 || Miss Smith | 0 || Mr Pullen | 3 |+------------+--------------+


Sql more info

SQL – more info

  • http://www.w3schools.com/SQl/default.asp

  • MySQL 5.1 Reference Manual: http://dev.mysql.com/doc/refman/5.1/en/index.html

  • http://en.wikipedia.org/wiki/SQL


  • Login