Transcript CS 122: Last Lecture

CS 122: Last
Lecture
Tarik Booker
California State University,
Los Angeles
Last Lecture!!!
 Final

Exam
Tuesday, March 17 1:30PM – 4:30PM ETA220
 Today:



Goodies
Fun
Things to keep you busy until CS 422
Final Exam
 Everything

until last lecture
E-R Diagrams MAYBE Included on Final
 Look
at Final Review for More Examples
 GOOD
LUCK!!!
What we will cover today…




Fancy Stuff
LAMP / WAMP
Bioinformatics
Fun with databases



Public Databases
Applications for databases
Query Languages
Yahoo’s YQL
 Facebook FQL
 Google’s Freebase


Query Languages
Fancy Stuff
 What’s

the rule for fancy stuff?
Just checking…
 MySQL
Workbench
 Command-line
 Output
execution
formatting (HTML)
MySQL WorkBench
A
Graphical User Interface (GUI) for
MySQL

http://www.mysql.com/products/workbenc
h/
 Contains
interactive windows for MySQL
server, E-R Diagrams, and SQL command
interface.

Fun; easy; buggy.
 Use
at your own risk!
Command-Line Execution

You don’t need to open a direct client
connection to access the database

Try this at the shell command line, not the mysql
client:

>mysql –u root –e “show databases;” –D lyric

E stands for “execute”, D stands for database

Make sure the –D is capitalized.
>mysql –u root –e “select * from members;” –D
lyric
 >mysql –u root –e “Any SQL query” –D
database_name

Output Formatting
 Now

try this:
>mysql –u root –e “show databases;” –D lyric –H



Again, CAPITAL “H”
You can specify mysql to output
information as a table in a website!!
Our tables are already formatted!

>mysql –u root –e “select * from artists join
xrefartistsmembers using (artistid) join members
using(memberid);” -D lyric -H
Output Formatting (2)
 The
> operator in the windows shell will put
the output of the command into a
filename (of your choosing)


Called output redirection
Don’t confuse this with my prompt
indicator(>)
 Ex:

>dir > dir_out.txt
 Let’s
use these with our command-line mysql
queries
Output Formatting (3)



>mysql –u root –e “select * from artists join
xrefartistsmembers using(artistid) join members
using(memberid);” -D lyric –H > ArtistMembersout.html
Now open this in a web browser!
Note: This file is in the directory mysql is in (unless you
change the path)


>mysql –u root –e “select * from artists join xrefartistsmembers
using(artistid) join members using(memberid);” -D lyric –H >
../../ArtistMembersout.html
>mysql –u root –e “select * from artists join xrefartistsmembers
using(artistid) join members using(memberid);” -D lyric –H |
../../ArtistMembersout.html
LAMP/WAMP
 Linux,







Apache, MySQL, PHP
Typical model of web solutions
Completely free (buy your own computer,
though)
Linux – open source operating system
Apache – open source server
MySQL – this class
PHP – web programming language
WAMP = Windows, Apache, MySQL, PHP
LAMP/WAMP (2)
 Most
(if not all) professional (and data
intensive) web pages use databases


Wikipedia, IMDB, Google, PayPal, Ebay,
etc.
LAMP makes creating your own site easy
(and free)
Public Databases
 Public
MySQL Databases
 Public
Databases
Public MySQL Databases
 If
you are interesting in seeing the type of
information used in databases, search for
public mysql databases
 Many are also PostgreSQL DBs (look up)
 Many are for public analysis of interesting
data

Bioinformatics – the study of processing
biological data
Bioinformatics
 The
interdisciplinary study of developing
methods and software tools for processing
biological data.

Why? There’s a LOT of biological data
 Viruses,
Genome, etc.
 Human Genome


Over 3 billion base pairs
Data must be structured, accessible, and
computable
Bioinformatics (2)
Only One Human
X Chromosome:
Bioinformatics (3)
 Different

schools have bioinformatics
Cal State LA has a minor:
 http://web.calstatela.edu/centers/cinqa/binf
/minorinfo.php

And courses:
 http://web.calstatela.edu/faculty/jmomand/
Bioinformaticscourse.html

Other schools have more resources…
UCSC Genome Bioinformatics

UCSC Genome
 https://genome.ucsc.edu/index.html
 Public MySQL Database
 https://genome.ucsc.edu/goldenPath/help/mysql.html


Note “Conditions of Use:”
“Avoid excessive or heavy queries that may impact
the server performance. Inappropriate query use
will result in a restriction of access. If you plan to
execute a query that you think may be excessive,
contact UCSC first to avoid the possibility of having
your access blocked.”

What SQL techniques should you NOT use?
UCSC Genome Bioinformatics
(2)
 Contains
a portal (site) of information on
Ebola and other diseases

Can search database with biological
information
UCSC Bioinformatics
 To
access the public database:

mysql --user=genome --host=genome-mysql.cse.ucsc.edu –A
 Don’t
do this in class!
 Make sure you follow the rules of the
database before you access!!!
 Grants
(public) access to many different
biological data

Ebola is “eboVir3” database
UCSC Bioinformatics
 Each
database has large amounts of
biological data


Can be downloaded separately
Up to you to determine what’s necessary
Other Bioinformatics
Databases
 There
are also other databases out there
for bioinformatics
 ENSEMBL Database

http://uswest.ensembl.org/info/data/mysql.
html
 GO

Database
http://geneontology.org/page/leaddatabase-guide#mirrors
Human Genome
 Genome

http://users.soe.ucsc.edu/~kent/gbd.html
 UCSC’s

Browser Database
Genome Browser
http://hgdownload.cse.ucsc.edu/downloa
ds.html
Drosophila Database
 ftp://ftp.flybase.net/releases/FB2014_06/p
sql/README

Who knows what Drosophila are?
 High
school biology…
Public Data Sources
 Try

to find other SQL files out there!
Amazon Public Datasets
 https://aws.amazon.com/datasets

Images of the Night Sky
 http://classic.sdss.org/dr1/access/index.html

Influenza Database
 http://www.ncbi.nlm.nih.gov/genomes/FLU/FL
U.html

Don’t always have to be completely scientific…
Marvel Teamup Database
 http://bioinfo.uib.es/~joemiro/marvel.html
 Used
to study social characteristics of the
Marvel Universe

Example of a “Social Collaboration
Network”
 Mirrors
real-world collaboration
 More of a directed graph


You will study later
Vertices and branches
Baseball Reference
 http://www.baseball-reference.com/
 All
statistics from Baseball History
 They

are hiring!
http://www.sportsreference.com/jobs_2014_fall.shtml
Fashion Database
 https://www.myfdb.com/
Many Other Databases
 Many
other databases out there
 Look on Google
 Many use PostgreSQL



Another type of Free, Open source
Database
Practice this
Code a little different, logic always same
Query Languages
 SQL
not the only query language

Many are proprietary

FQL
 Facebook

YQL
 Yahoo

Query Language
Query Language
Freebase
 Google
Product
Facebook Query Language
(FQL)
 https://developers.facebook.com/docs/t
echnical-guides/fql
 Used to search Facebook information like
a database

Information organized in tables
 Like
a relational database
 We covered this earlier…
Facebook Query Language
(3)

Deprecated (no longer used), but interesting

Look at query guidelines:

https://developers.facebook.com/docs/techni
cal-guides/fql

What technique can’t you use for queries?


Notice a pattern?
What programming language does this use?
Yahoo Query Language (YQL)


https://developer.yahoo.com/yql/
Different than FQL

Designed to manipulate data across the
internet

Including other sites and treating their info like
databases

Flickr, Yelp, etc.
Queries web services (you will learn later)
 Let’s look at the tutorial:


https://developer.yahoo.com/yql/guide/twominute-tutorial.html
Yahoo Query Language (2)

Two minute tutorial:


https://developer.yahoo.com/yql/guide/twominute-tutorial.html
Let’s get the Weather Forecast for Los
Angeles:

First get LA weather id.
select woeid from geo.places where text="los
angeles, ca“
 Then get forecast... (Subquery)


Lots of things to query here… results in XML…
Google Freebase
 https://developers.google.com/freebase/
 An
“open, shared database of the world’s
knowledge”



User collected, “curated” content
Uses Metaweb Query Language (MQL)
Returns XML or JSON info (objects)
 Just
organized data (like generalized HTML)
Database Jobs
 Everyone

needs a database
SQL DBA / SQL Database Administrator View similar jobs Job type: Full-Time | Pay:
$85k - $95k/year
Database Administrator and the salary is in
the $85K-$95K range plus benefits. Only
candidates authorized ...
Food For Thought…
 For
 15

your own information…
Coveted Programming Skills
http://readwrite.com/2013/04/09/15-programming-skillsmost-coveted-by-employers
 Why

technology will always change:
http://readwrite.com/2013/05/01/10-technology-skills-nolonger-in-demand

Remember, code changes, but logic always stays the same!!!
Go See “The Imitation Game!”
The type of crazy that
produced modern
Computer Science!!!
Alan Turing