1 / 29

Informatics tools in network science

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

berget
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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. seminar 2 Database handling Informatics tools in network science

  2. storing information memory (local) files 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 ARTICLES_KEYWORDS KEYWORDS ARTICLES AUTHORS ARTICLES_AUTHORS

  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.com/downloads

  19. 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.

  20. SQL • SQL = Structured Query Language UPDATE My_tableSET field1 = 'updated value‘WHERE field2 = 'N'; SELECT isbn, 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);

  21. MySQL Query Browser

  22. 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;

  23. 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 sellings on people.pid = selling.pid;

  24. 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 sellings on 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;

  25. 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 sellings on people.pid = selling.pid;

  26. 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 sellings on people.pid = selling.pid;

  27. 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 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 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 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/doc/refman/5.1/en/index.html • http://en.wikipedia.org/wiki/SQL

More Related