Managing Data with SQL

& Relational Databases

Michael Shensky

Head of Research Data Services

m.shensky@austin.utexas.edu

Welcome

• This is the first of five Data & Donuts workshops in the Fall 2022 term
• Click the links below to register for additional workshops
• September 2: Data Management with SQL and Relational Databases
• September 16: Introduction to Python for Data Management
• September 30: Introduction to R and R Studio
• October 14: Python for Data Retrieval and Visualization
• October 28: Data Preservation and Sharing
• Recorded workshops and lecture slides will be posted online at:
• https://guides.lib.utexas.edu/data-and-donuts

Workshop Logistics

• All workshops this fall will be conducted as Zoom webinars (workshop on 9/16 is also in-person)
• You can register for upcoming workshops by visiting https://guides.lib.utexas.edu/data-and-donuts
• Feel free to ask questions and add comments in the chat
• Workshop instruction will run from 12pm to 1pm and there will be time for questions until 1:15pm
• Workshops this fall will be recorded and links to view the recordings will be posted at https://guides.lib.utexas.edu/data-and-donuts/information-about-past-workshops

Other Fall 2022 Research Data Services Announcements

UTL Map & Geospatial Collections Explorer Fellowship

• This Fellowship will be awarded for a second consecutive year in 2022
• The Fellowship is designed to incentivize and reward use of UT Libraries geospatial collections
• The 2022 call for proposals is now posted and proposals are now due by 10/10/2022 at 11:59pm
• Currently enrolled UT students at all levels, faculty, & post-docs are eligible for the $1500 fellowship award
• Winners will be announced at an event on UT GIS Day (11/16/2022)
• Visit https://guides.lib.utexas.edu/gis/utl-map-and-geospatial-collection-explorer-fellowship for the latest updates

Goals for This Workshop

• Provide a foundation in the basics of using SQL
• Understand why SQL is useful and develop familiarity with common use cases
• Cover the most common relational database options
• Practice using DB Browser for SQLite to manage data using SQL
• Gain hands on experience using SQL and Python to interact with a relational database

​

​

What is a Database?

• A database is an organized collection of related datasets with defined data structures
• Conforms to a specific database model (relational, document, graph, etc.)
• In relational databases, stored information can be queried using SQL

A grey cylinder symbol is commonly used to represent databases in system diagrams

What is a Relational Database?

• In a relational database all data is stored in tables
• Each row contains information about a particular record
• Each column is dedicated to storing information about a specific attribute
• Rows in one table can be joined to related rows in another table based on a common key value
• Data can be normalized so that no redundant information needs to be stored

​

​

What is SQL?

• SQL = Structured Query Language
• SQL is a standardized syntax for interacting with relational databases
• SQL can be used within database management software or can be used within scripts written in other languages (e.g. Python)
• Used for:
• Creating new databases and tables
• Querying data
• Updating data
• Joining data
• Setting constraints
• Defining triggers and views
• Creating indices
• Managing primary and foreign keys

​

​

Database Implementations

File database (single user architecture)

• Functionality for accessing and modifying a database is built directly into an application/script and the database is stored locally

Application/script >>> Locally stored file database

​

Client-Server (multi-user architecture)

• The application/script sends a request to a relational database management system (RDBMS) software on a server that controls the database

Application/script >>>

Database management system on a server >>>

Database within DBMS

Why Use Databases & SQL for Data Management

• Easier to manage multiple tables in a database than manage multiple spreadsheets
• Ability to join related data records
• SQL commands are easy to share and results are easy to reproduce
• SQL commands can be integrated into scripted processes (Python, PHP, Java, etc.)
• SQL has many applications - data driven web design, GIS data management, etc.
• RDBMS instances can store massive amounts of data and allow for granular control over data access

​

​

​

​

Relational Database Use Case Examples

• Managing experimental research data
• Managing business records pertaining to sales, marketing, HR, etc.
• Building data driven web sites
• Embedding data within mobile applications

RDBMS Options

• SQLite
• MySQL* / MariaDB
• PostgreSQL
• Microsoft SQL Server*
• Oracle*

*Offered as a free service for faculty & staff by UT ITS

​

UT ITS Data Storage Services: https://it.utexas.edu/services/servers-storage-data

RDBMS Popularity Rankings

Rankings of Database Popularity: https://db-engines.com/en/ranking

DB Browser for SQLite

• Cross platform compatible (Windows, MacOS, Linux)
• Easy to use, good for practicing database management with SQL
• Good DB Browser tutorial at http://datacarpentry.org/sql-ecology-lesson/
• Demonstration of common SQL operations
• Select, distinct, where, count, sum, avg, order by

​

​

Download installation files for DB Browser for SQLite at https://sqlitebrowser.org/dl/

DB Browser Demo

• Install DB Browser
• Download tree inventory data from the City of Austin
• Open DB Browser
• Import CSV as table
• Demonstration of common SQL table structure operations
• Change table name, change field name, add field, drop field
• Demonstration of common SQL operations
• Select, distinct, where, count, min, max, sum, avg, order by, insert into

Export Austin tree inventory data as CSV from: https://data.austintexas.gov/Locations-and-Maps/Tree-Inventory/wrik-xasw

Google Colab SQL Example

• Colab is a free Google service that allows you to create and run Jupyter Notebooks
• Allows you to write Python code and text notes in compartmentalized cells within a notebook
• Code cells can be run individually to allow for previewing outputs and troubleshooting issues
• Notebooks are stored in Google Drive and can access other files in Google Drive

https://research.google.com/colaboratory/faq.html

Colab FAQs at https://research.google.com/colaboratory/faq.html

What is Python?

• Open source, interpreted programming language
• Cross platform
• Current version is 3.10.4 (as of 4/22)
• Extensive use in a variety of fields
• Large ecosystem of open source packages
• Can be used for file management, analyzing data, editing data, visualizing data, and more!

​

​

Python Basics

• Packages and modules
• Variables and object types
• Operators
• Methods and functions
• Syntax
• Comments
• Conditional statements
• Loops

​

​

​

Link to publicly shared Google Colab Notebook #1:

https://colab.research.google.com/drive/1BWlHkt4zGroPHlUJmfqc3tsh1sfL1tTA?usp=sharing

Python and SQL Demo

• Importing packages and modules
• Creating a new SQLite database
• Creating new tables
• Populating tables with records
• Querying records
• Joining tables

Google Colab notebook file for this demo can be accessed at:

https://colab.research.google.com/drive/1XBuPpFq7kAyjLwNleJNx-RqCx7r9PQGS?usp=sharing

​

SQL Resources, Documentation, and Help

• SQL Documentation (https://www.postgresql.org/docs/current/sql.html)
• SQL Tutorials (https://www.w3schools.com/sql/)
• Data Carpentry SQL Lesson (http://datacarpentry.org/sql-ecology-lesson/)
• Stackoverflow (https://stackoverflow.com/)
• PostgreSQL Documentation https://www.postgresql.org/docs/14/index.html
• MariaDB Documentation https://mariadb.com/kb/en/documentation/
• SQLITE Documentation https://github.com/sqlitebrowser/sqlitebrowser/wiki
• LinkedIn Learning for SQL (https://www.linkedin.com/learning/topics/sql)

Wrap Up

Michael Shensky

Head of Research Data Services

m.shensky@austin.utexas.edu

​

Questions? Comments?

Next Workshop

• September 16: Introduction to Python for Data Management
• Hybrid Format (Zoom Webinar and in-person in PCL Learning Lab 1)
• Donuts!