Skip to content

Latest commit

 

History

History
269 lines (184 loc) · 10.4 KB

Database_Applications.md

File metadata and controls

269 lines (184 loc) · 10.4 KB

Data Management

For Scientific Research

stanza body copyPhoto: © Stanza. Used with permission.

Session 6: Database Applications and SQL

Today we will be discussing two types of database applications: servers and clients.

We will also introduce a smattering of SQL in our examples.

groupImage: Seahen / Ch.Andrew / David Vignoni / Wikimedia

Database Servers

Three popular types of database servers are:

  • Relational (usually SQL)
  • NoSQL ("Not only SQL")
  • Object-Relational

SQL = "Structured Query Language"

SQL Servers

SQL servers are the most common type of database server.

Some popular SQL server products are:

  • MySQL
  • PostgreSQL
  • Microsoft SQL
  • Oracle

Database Clients

We consider any end-user application that can connect to a database server as a database "client".

Examples include:

  • Various web applications and frameworks
  • Desktop Applications and Mobile Apps
  • Data analysis tools and statistics packages
  • Scripts and programs and their database libraries

Database Connections

There are three main ways to connect to a database:

  • File-based access (SQLite, MDB, CSV)
  • Connection to SQL service using "native" client
  • ODBC (Open Database Connectivity)

ODBC (Open Database Connectivity)

  • Standard interface to DBs.
  • Connect to DB with a DSN.
  • DSN = "Data Source Name"
  • Requires driver (connector)
  • Driver is specific to:
    • 32 bit vs. 64 bit
    • DB server product

ms dsn

Hands-on Group Exercise

Create a DSN for accessing your SQL database from a Windows PC.

For our exercise, we will connect from the student server.

Create a "user DSN" using the MySQL Unicode Driver.

Go to "Administrative Tools" -> Data Sources (ODBC).

dsn

Under the "User DSN" tab click "Add".

Using MS-Access with a SQL Database

From MS-Access, you can link to tables and views from a SQL server.

  • Connect DB with a DSN
  • Link to DB tables
  • Recreate relationships
  • Create views, forms, and reports

ms access relationships

Using SQL in R

We can use the same DSN to connect with R.

R Windows

Using SQL in Stata

And we can use the same DSN to connect with Stata.

Stata Windows

Using SQL in MySQL Workbench

MySQL WB can connect to the server without needing a DSN.

MySQL WB Example

SQLite Example: Firefox History

File-based access does not need a DSN or connection string.

firefox history query

SQL Queries in a Nutshell

SQL (Structured Query Language) is a language for working with relational databases.

The most common SQL command is a SELECT statement.

Some example queries using SELECT:

  • SELECT * FROM table1;
  • SELECT column1, column2 FROM table1;
  • SELECT * FROM table1 WHERE column1 = "Joe";
  • SELECT * FROM table1 ORDER BY column2, column1 ASC;

You can combine WHERE, ORDER BY, and other clauses.

Hands-on Group Exercise

Take a look at your own Firefox history database.

  • .header on
  • .mode column
  • .width 50
  • .help
  • .tables
  • SELECT host, frecency FROM moz_hosts ORDER BY frecency DESC LIMIT 5;
  • .quit

relational databaseGraphic: Mozilla/dietrich

Discussion

We will discuss your SQL queries.

discussionGraphic: Jagbirlehl / Wikimedia

In the Coming Sessions...

  • Web Applications and Frameworks
  • Importing and Exporting SQL Tables
  • Using SQL from Another Language
  • Project Management and Version Control

Action Items (videos, readings, and tasks)

watching
readings
tasks
  • Add some test data to your tables.
  • Connect to your database from another program.
  • Try all query types you know about so far.
  • If you have some external data, try importing it.

See Also

Questions and Comments

questionImage: © Nevit Dilmen / Wikimedia

Some Parting Words

how to write a cvImage: Geek and Poke. Used with permission.