Send the link below via email or IMCopy
Present to your audienceStart remote presentation
- Invited audience members will follow you as you navigate and present
- People invited to a presentation do not need a Prezi account
- This link expires 10 minutes after you close the presentation
- A maximum of 30 users can follow your presentation
- Learn more about this feature in our knowledge base article
Introduction to database management with open source tools
Transcript of Introduction to database management with open source tools
management with open source tools
Quebec Center for Biodiversity Science
Basics of the
From Libreoffice/Access (csv,xls,ods)
From the PostgreSQL command line (CREATE TABLE, COPY...)
What is a database?
An organized collection of data.
What is a DBMS?
Database Management System.
Controls the creation, maintenance, and use of a database.
Allows concurrent access by several users and applications.
Does not necessarily come with a user interface.
Benefits of open-source
Free today, free tomorrow.
Collaborative help system.
Links more easily with other open source software.
Disadvantages: not always good with user interface. No support from company.
Steps of database design
Determine entities and tables
Establish primary keys
Establish data types
Rules of thumb
One attribute per column. Cannot be subdivided.
Unique way to identify each row.
No repeating info.
Simple but meaningful table and column names. Avoid words such as 'name', 'text','count','long', etc.
Understand what databases do
Concepts of database management
Intro to the SQL language
Brief intro to LibreOffice Base
Connecting PostgreSQL with other tools
Where to find more info?
What is a relational database?
Data is stored in formatted tables.
Tables are linked together with "keys".
Options for database management
Poor at working with multiple linked tables.
Poor at working with very large datasets.
No multi-user access.
No strict formatting.
No server access.
Performing complex queries can be difficult.
Data and 'reports' are not separated.
MS Access is a front-end to a database 'server' called Jet/ACE. It can be used with other database servers such as PostgreSQL.
Not an open platform.
Multiple versions and document formats.
Not frequently used on servers.
Limits to the file sizes and concurrent users.
Can make a good front-end for designing forms or generating reports.
Can handle very large datasets.
Used by major companies (Facebook, Twitter, Google, etc.).
MySQL - developed mostly by Oracle.
Postgres and MariaDB - community development.
Database servers with no front-ends.
What is SQL?
Structured query language.
Often pronounced 'sequel'
Programming language designed for RDBMS.
Used by most RDBMS systems.
Variations between implementations.
Microsoft SQL Server
One to one
One to many
Many to many
Establish primary keys
Mechanism used to relate tables together
One entity = one unique key
Unique columns, combination of columns or logical primary key
Establish data types
Has to allow all possible current and future values.
One table per type of entity: person, type of person, address, sample, quadrat, ...
When should I use a DBMS?
Standardization of data is important
Very large datasets
Multiple users, multiple platforms
Access on a server
Interaction with other software or computer tools
Small datasets that don't need standardization
Single users, simple needs
Lack of technical expertise/time
When I should not use a DBMS?
Storing one to many relationships
Option 1 - Multiple columns
Option 2 - Lists
Option 3 - Lookup table
( ) A fixed section from 0 to 255 characters long.
( ) A variable section from 0 to 255 characters long.
A string with a maximum length of 65535 characters.
( ) -128 to 127 normal 0 to 255 UNSIGNED.
( ) -32768 to 32767 normal 0 to 65535 UNSIGNED.
( ) -8388608 to 8388607 normal 0 to 16777215 UNSIGNED.
( ) -2147483648 to 2147483647 normal 0 to 4294967295 UNSIGNED.
( ) -9223372036854775808 to 9223372036854775807 normal
0 to 18446744073709551615 UNSIGNED.
A small number with a floating decimal point.
( , ) A large number with a floating decimal point.
( , ) A DOUBLE stored as a string , allowing for a fixed decimal point.
Used for unknown attributes
Table, column names separated by commas, not commands
Strings are separated by ''
No spaces, capital letters or special characters $&?%!...
Names can be enclosed with "" when spaces or special characters are present or for reserved words.
Not case-sensitive. But always use good case.
Create forms (Access, LibreOffice, PHP)
Edit directly in user interface (e.g. Libreoffice Base)
Command line client
LibreOffice BASE/ MS Access
Examples of DBMS use
QCBS student/prof database and website.
Des nids chez vous website
The internet (e.g. http://quebio.ca/bam)
Collaborative databases (GBIF, GENBANK, etc.)
JOIN ... GROUP BY
list tables in database
describe table and columns
create database or table
insert line(s) into table
add columns or modify format
delete table or database