relational database cisc/qcse 810 some materials from software carpentry
TRANSCRIPT
![Page 1: Relational Database CISC/QCSE 810 some materials from Software Carpentry](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649ebf5503460f94bca004/html5/thumbnails/1.jpg)
Relational Database
CISC/QCSE 810some materials from Software Carpentry
![Page 2: Relational Database CISC/QCSE 810 some materials from Software Carpentry](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649ebf5503460f94bca004/html5/thumbnails/2.jpg)
Introduction
Text files have a long and useful history for storing files they are human-readable they can always be imported into new
formats simple to write reading routines in
any language
![Page 3: Relational Database CISC/QCSE 810 some materials from Software Carpentry](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649ebf5503460f94bca004/html5/thumbnails/3.jpg)
Limitations of Text Files 1
No meta-data experimental/simulation data has
conditions/parameters attached If your file names are like
"results_s10_n5_p43.txt", you should consider recording results in a database
![Page 4: Relational Database CISC/QCSE 810 some materials from Software Carpentry](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649ebf5503460f94bca004/html5/thumbnails/4.jpg)
Limitations of Text Files 2
Redundancy (and error) One way to include meta-data is to add extra
columns to your text files
"Any duplication will eventually lead to errors"
Extra columns of constants are extremely expensive, largely wasted space, and don't scale well as you add new meta-data
![Page 5: Relational Database CISC/QCSE 810 some materials from Software Carpentry](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649ebf5503460f94bca004/html5/thumbnails/5.jpg)
Limitations of Text Files 3
Searching and subsetting searching for matching entries in a
text file is cumbersome grep is fine for some easy matches,
but not for searches like "“Find all experiments done with the Mark VII that had yields greater than 30%, that didn't use cadmium disulfide as a reagant”
![Page 6: Relational Database CISC/QCSE 810 some materials from Software Carpentry](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649ebf5503460f94bca004/html5/thumbnails/6.jpg)
Non-text files
Collections of documents need be both stored and searched graphic images: index by date,
source, resolution, format, processing done, comments
video clips: same
![Page 7: Relational Database CISC/QCSE 810 some materials from Software Carpentry](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649ebf5503460f94bca004/html5/thumbnails/7.jpg)
Relational Databases
Relational Databases are data repositories that can help store relations between different types of dataAllows you to focus on one aspect of data modeling at a time reduce redundancy improve search
![Page 8: Relational Database CISC/QCSE 810 some materials from Software Carpentry](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649ebf5503460f94bca004/html5/thumbnails/8.jpg)
Getting Started
A database is a collection of zero or more tables, each of which: Has a name Stores a single relation (i.e., a set of information
of a particular kind)
Each table has a fixed set of named columns All the values in a column have the same type
Each table has zero or more rows Also called records
![Page 9: Relational Database CISC/QCSE 810 some materials from Software Carpentry](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649ebf5503460f94bca004/html5/thumbnails/9.jpg)
![Page 10: Relational Database CISC/QCSE 810 some materials from Software Carpentry](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649ebf5503460f94bca004/html5/thumbnails/10.jpg)
SQL Interface, DBMS
Interact with database management system (DBMS) using a specialized language called SQL Every vendor implements its own
extensions to the standard Table, database names are not case
sensitive: gravity, Gravity and GRAVITY are considered the same
Data can be case sensitive
![Page 11: Relational Database CISC/QCSE 810 some materials from Software Carpentry](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649ebf5503460f94bca004/html5/thumbnails/11.jpg)
![Page 12: Relational Database CISC/QCSE 810 some materials from Software Carpentry](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649ebf5503460f94bca004/html5/thumbnails/12.jpg)
Building vs Querying
Most time with database is spent querying generating graphs, tables from subsets of
data
Still need to build it in the first place! record-by-record table-by-table Perl/Python program to do fancy file-to-
database upload
![Page 13: Relational Database CISC/QCSE 810 some materials from Software Carpentry](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649ebf5503460f94bca004/html5/thumbnails/13.jpg)
Create a Database
Database is a grouped collection of tables CREATE DATABASE <database
name>
![Page 14: Relational Database CISC/QCSE 810 some materials from Software Carpentry](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649ebf5503460f94bca004/html5/thumbnails/14.jpg)
Creating a Table
To create a table, specify its name, and the names and types of its columns
CREATE TABLE Person(Login TEXT NOT NULL,LastName TEXT NOT NULL,FirstName TEXT NOT NULL
); The expression NOT NULL means that the value
must be present Data types are similar to C, Java types
To erase a table, use DROP TABLE name Very handy when you're first starting…
![Page 15: Relational Database CISC/QCSE 810 some materials from Software Carpentry](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649ebf5503460f94bca004/html5/thumbnails/15.jpg)
Filling in a Table
Adding records manually INSERT INTO <table> VALUES (….)INSERT INTO Person VALUES("skol", "Kovalevskaya", "Sofia");
Adding from a file LOAD DATA LOCAL INFILE
![Page 16: Relational Database CISC/QCSE 810 some materials from Software Carpentry](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649ebf5503460f94bca004/html5/thumbnails/16.jpg)
Filling in a Table 2
For anything more complicated, you'll want to write a script in some other language that does the uploading for you, through tailored "insert" commandse.g. experiment just finished check for existing experiment w/same
conditions create new record for experiment if necessary get new experiment ID (or old if it exists) upload results, with DB experimentID attached
![Page 17: Relational Database CISC/QCSE 810 some materials from Software Carpentry](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649ebf5503460f94bca004/html5/thumbnails/17.jpg)
Querying
Suppose we want to get everyone's name and login IDWrite a query that specifies what we want, and where to find it
SELECT Person.FirstName, Person.LastName, Person.Login
FROM Person;
![Page 18: Relational Database CISC/QCSE 810 some materials from Software Carpentry](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649ebf5503460f94bca004/html5/thumbnails/18.jpg)
Querying and Sorting
SELECT Person.FirstName, Person.LastName, Person.Login
FROM Person ORDER BY Person.Login;
![Page 19: Relational Database CISC/QCSE 810 some materials from Software Carpentry](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649ebf5503460f94bca004/html5/thumbnails/19.jpg)
Selection
Frequently only want a subset of data
SELECT FirstNameFROM Person WHERE Login = "skol"
![Page 20: Relational Database CISC/QCSE 810 some materials from Software Carpentry](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649ebf5503460f94bca004/html5/thumbnails/20.jpg)
Simple Joins
Getting project IDs from namesSELECT ProjectID, LastNameFROM Person, InvolvedWHERE Person.login = Involved.login
![Page 21: Relational Database CISC/QCSE 810 some materials from Software Carpentry](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649ebf5503460f94bca004/html5/thumbnails/21.jpg)
Query Joins w/3 Tables
More interesting query: what are the names of the projects is
Ivan involved in?
![Page 22: Relational Database CISC/QCSE 810 some materials from Software Carpentry](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649ebf5503460f94bca004/html5/thumbnails/22.jpg)
Far more to see!
Design of databases "normal forms" knowing the relationships between
your data tables
Efficient access of databases optimization of queries
![Page 23: Relational Database CISC/QCSE 810 some materials from Software Carpentry](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649ebf5503460f94bca004/html5/thumbnails/23.jpg)
Accessing an existing database
Download a MySQL clientTry to connect to 130.15.100.140 username aableson password
![Page 24: Relational Database CISC/QCSE 810 some materials from Software Carpentry](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649ebf5503460f94bca004/html5/thumbnails/24.jpg)
First steps
Create a database named for your Queen's NetIDTry some of the operations outlined in the MySQL tutorial skip the logging in with command
line, go straight to building tables, queries
![Page 25: Relational Database CISC/QCSE 810 some materials from Software Carpentry](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649ebf5503460f94bca004/html5/thumbnails/25.jpg)
Resources
MySQL Query Browser download http://dev.mysql.com/downloads/gui-tools/5.0.html
MySQL tutorial http://dev.mysql.com/doc/refman/5.0/en/tutorial.html tailored for command-line usage; can skip some if you're
using a GUI
MySQL reference http://dev.mysql.com/doc/refman/5.1/en/index.html