Loading presentation...

Present Remotely

Send the link below via email or IM

Copy

Present to your audience

Start 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

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.

DeleteCancel

Make your likes visible on Facebook?

Connect your Facebook account to Prezi and let your likes appear on your timeline.
You can change this under Settings & Account at any time.

No, thanks

Introduction to database management with MySQL

No description
by

Guillaume Larocque

on 5 March 2014

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of Introduction to database management with MySQL

Introduction to database
management with MySQL

Guillaume Larocque,
research professional,
Quebec center for biodiversity Science

Getting started...
Database design
principles

Basics of the
MySQL language

Basics
Importing data...
From Libreoffice/Access (csv,xls,ods)
From the MySQL command line (CREATE TABLE, LOAD DATA INFILE)
What is a database?
An organized collection of data.
What is a DBMS?
Database Management System.
Software.
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.
RDBMS
- Relational.
Benefits of open-source
software
Free today, free tomorrow.
Collaborative development.
Collaborative help system.
Links more easily with other open source software.

Disadvantages: not always good with user interface. No support from company.
Basic principles
Standardization
Coherence
Expandability
Flexibility
Steps of database design
Determine entities and tables
Relationships/cardinality
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.
Objectives
Understand what databases do
Concepts of database management
Intro to the MySQL language
Brief intro to LibreOffice Base
Connecting MySQL 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
Microsoft Excel?
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.
Microsoft Access?
MS Access is a front-end to a database 'server' called Jet/ACE. It can be used with other database servers such as MySQL.
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.
MySQL and PostgreSQL
Open source.
Extremely powerful.
Fast.
Can handle very large datasets.
Good development.
Used by major companies (Facebook, Twitter, Google, etc.).
SQL standard.
MySQL - developed mostly by Oracle.
Postgres - community development.
Database servers with no backends.
What is SQL?
Structured query language.
Often pronounced 'sequel'
Programming language designed for RDBMS.
Standards-based.
Used by most RDBMS systems.
Variations between implementations.
Full-fledged DBMS
Open source
MySQL
PostgreSQL

Proprietary
Oracle
Microsoft SQL Server
Determine entities
and tables
Establish relationships/
cardinality
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
Short but meaningful table and column names. No spaces (use _ ), no strange characters (accented, ?&%$@'(",)
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

CHAR
( ) A fixed section from 0 to 255 characters long.
VARCHAR
( ) A variable section from 0 to 255 characters long.
TINYTEXT
A string with a maximum length of 255 characters.
TEXT
A string with a maximum length of 65535 characters.
BLOB
A string with a maximum length of 65535 characters.
MEDIUMTEXT
A string with a maximum length of 16777215 characters.
MEDIUMBLOB
A string with a maximum length of 16777215 characters.
LONGTEXT
A string with a maximum length of 4294967295 characters.
LONGBLOB
A string with a maximum length of 4294967295 characters.
String types

TINYINT
( ) -128 to 127 normal 0 to 255 UNSIGNED.
SMALLINT
( ) -32768 to 32767 normal 0 to 65535 UNSIGNED.
MEDIUMINT
( ) -8388608 to 8388607 normal 0 to 16777215 UNSIGNED.
INT
( ) -2147483648 to 2147483647 normal 0 to 4294967295 UNSIGNED.
BIGINT
( ) -9223372036854775808 to 9223372036854775807 normal
0 to 18446744073709551615 UNSIGNED.
FLOAT
A small number with a floating decimal point.
DOUBLE
( , ) A large number with a floating decimal point.
DECIMAL
( , ) A DOUBLE stored as a string , allowing for a fixed decimal point.
Numeric types
Date-time

DATE
YYYY-MM-DD.
DATETIME
YYYY-MM-DD HH:MM:SS.
TIMESTAMP
YYYYMMDDHHMMSS.
TIME
HH:MM:SS.
Primary
keys
No Key
NULL
Used for unknown attributes
Entity-relationship diagram
Table, column names separated by commas, not commands
Strings are separated by ''
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)
INSERT command
Entering data...
USer interface
Command line client
LibreOffice BASE/ MS Access
MySQL Workbench
Phpmyadmin
Navicat, HeidiSQL
Examples of DBMS use
QCBS student/prof database and website.
The internet (e.g. http://quebio.ca/bam)
Collaborative databases (GBIF, GENBANK, etc.)
Basic commands
INSERT, CREATE, SELECT, UPDATE, DELETE, APPEND
FROM, WHERE, JOIN, GROUP BY, ORDER BY, etc.
Operators
http://qcbs.ca/wiki/mysql
QCBS_students
Universities
Supervisors
Full transcript