-
Notifications
You must be signed in to change notification settings - Fork 5
Weekly Update Blog
###HW SQL Fiddles Practice Problems:
a) http://sqlfiddle.com/#!2/3b40f/1
b) http://sqlfiddle.com/#!2/3b40f/2
c) http://sqlfiddle.com/#!2/a0ddd/1
d) http://sqlfiddle.com/#!2/58760/1
e) http://sqlfiddle.com/#!2/ac982/1
f) http://sqlfiddle.com/#!2/ac982/2
g) http://sqlfiddle.com/#!2/ac982/3
h) http://sqlfiddle.com/#!2/9ee62a/17
i) http://sqlfiddle.com/#!2/9ee62a/16
j) http://sqlfiddle.com/#!2/ac982/4
###Class Notes: uncle bob video:
most difficult part of db: transactions
when memory is unlimited: won't need to save state- just save transactions no CRUD applications - just CR
database is a detail- not the center of the app should be treated as a plugin made for the app not the app made to work around the database
object is a bunch of methods- not neccessarily data
application should not have outgoing dependencies
innner join vs outer
join be default is inner
inner: http://sqlfiddle.com/#!2/0aa74/1 inner join keeps only rows where things match
outer: http://sqlfiddle.com/#!2/0aa74/5 makes sure everything in 1st table appears in query, if theres no match, put it a null (for left outer join, otherwise 2nd table)
###Work:
Did week 4 of database class. Learned about indexes and how they can make the database find things much faster. We learned about multikey indexing and how the database uses indexes for finding and sorting. We used this to make the blog app run faster so that the app only the documents that are scanned are returned.
###Class Notes:
cartesian product: creating an mxn table combining all the attributes combinations can use indexes to shorten time (use a hash for constant time- or btree for log(n) time)
Outer join creations: http://sqlfiddle.com/#!2/1e02d/11 http://sqlfiddle.com/#!2/1e02d/15
different syntaxes for joins- ex: From a left join using (b, c)
relational algebra: http://en.wikipedia.org/wiki/Relational_algebra
algebra is a collection of operands and elements where each operands has associated properties with it
relational: all values are tables or relations- operands are performed on tables to make new tables operands:
times-crossing a and b union- combine all rows(not counting dupes) subtraction- all rows in a not also in b intersection- common rows
projection- filters columns out selection- filters out rows based on a condition
natural join- combinations of products where values of one collumn matches up semi joins outer joins division
TODO: express antijoin in sql
###Work:
HW from last class: anti joins: An antijoin is applied on 2 relation sets A and B and produces a set of relations consisting of only the documents d where dEA but d!EB Expressed in sql as(from problem i from last week):
select p.name from person p left outer join works w on p.id = w.personid where w.personid is null
MONGO WORK: Did week 5 of database class. Worked on queries involving aggregation and learned about the step that can be taken in aggregation project, unwind, match, group, limit, sort, and skip. Queries using these processes and aggregation function like avg, sum, addToSet, push, first, last, max and min can be used to find out general information about large datasets. Solutions to mongo HW problems can be found in the repo under hw5
Also read through chapter 9 of sql book.
###Work:
MONGO WORK: Did week 6 of mongo class. Week 6 was all about replica sets. Replica sets are used to run multiple instances of a mongo database so that when one goes down, another one can become the primary. The class went over failovers, elections (selecting a new primary) and the different kinds of voting and non voting nodes. The class also went over sharding, and how a collection can be broken up or sharded on a certain element. This is done to meet the demands of data growth. If a collection gets too big, it must be split up to be stored. The class went over how to make shards, what elements should be used as a shard key and how it have to be indexed. For the homework, there were a number of multiple choice questions and a problem where replica sets had to be set up for a colleciton
Also read ch 10 of sql book talking about practical database implementations and how to make uml diagrams to represent them
###Class Talking to a database via an application node- running javascript in command line
npm search: finding packages using node
connection pooling for high volume of connections
###Work:
MONGO WORK: FINAL EXAM
Other work: Started coding project in meteor: http://whatwouldwarrenbuffettdo.meteor.com/
###Class Databases in the real world Bobby Tables: http://xkcd.com/327/ Sql injection
SQL alchemy python way to use sql :name --named parameters ? --positioned parameters
jooq: sql with java most java db programs: JDBC myBatis hibernate jooq
TODO: http://programmerinterview.com/index.php/database-sql/practice-interview-question-1/
###Work http://programmerinterview.com/index.php/database-sql/practice-interview-question-1/ Schema: http://sqlfiddle.com/#!2/ddee6 a. The names of all salespeople that have an order with Samsonic. http://sqlfiddle.com/#!2/ddee6/8
b. The names of all salespeople that do not have any order with Samsonic. http://sqlfiddle.com/#!2/e12ff3/1
c. The names of salespeople that have 2 or more orders. http://sqlfiddle.com/#!2/e12ff3/7
And added updates to website at: http://whatwouldwarrenbuffettdo.meteor.com/
###Class created testdb express app see repo
client to server- http requests-(get, post, put, delete) server talks to mongo
include password as environment variable
env MY_PASSWORD=XXXXXX node process.env.MY_PASSWORD
###Class KTAH framework -network tab in dev tools -200: ok -3xx: redirect -304: already in cache -302: moved temporarily -ex: not logged in in k-tah redirects to login page -check if user is logged in-> go to lobby or redirect to home login information and sessions stored in cookies -cookies called cookies from magic cookie, fortune cookie? who knows?
###Work Read article on why not to use mongodb: -author had a problem with mongo not supporting joins -this caused either duplications of data or requiring to do joins manually -does not support many kinds of data structures in this way -if the data implies a schema, then a relational db should be used
Started textbook problems- committed in repo
###Class Sarah Mei article
Initially says mongo would be good for tv shows stroing -with relational, it requires a several table join of show, season, reviews, episode, cast member... -some people say gets in the way of scaling -others say only poor modeling can make this take a long time -with mongo, all of this can be embedded in the mongo document -very fast
social data isn't the same -document becomes cyclic fast -embeds users inside users- duplicated data -with relational use user id an can do a join- many to many relationship
normalized data: no duplicated data in database (ex:show name will never appear in season table- only show id- name will be looked up in show table using joins)
n+1 selects problem: find all x's for y http://stackoverflow.com/questions/97197/what-is-the-n1-selects-issue
relational: can have trouble scaling vertically -cant split things up with sharding document key-value: for looking something up graph database- very young (article says its too niche)- but developing fast, very useful for social data
postgreSQL supporting JSON stores- relational with adopt some mongo with become better at relations -manual joins can allow for programmatically de normalizing just enough that is needed
Joins in bash: awk ex: awk -F',' '{print $2 " has director " $3}' movie join -13 -21 -t ',' -o '1.2 2.1' movie director //requires sorted by the field to be joined on
###Work: Textbook problems More work on project
###Class redis: (redis.io) a key value store think of as a big hash
redis known for being all in memory -stores counts and such that allows for response times in the milliseconds unlike long aggregation commands
tutorial: try.redis.io commands: incr- atomic opperation- is done in mutual exclusion with other operations preserves concurrency SET can set resource: and EXPIRE it GET resource: LRANGE for lists (lpush, rpush, lpop, rpop)
integers and strings sort of interchangeable (but need quotes for spaces) -weakly typed
SADD- set add (set doesn't have order) SUNION- join sets SISMEMBER listname element SMEMBERS
ZADD- add to sorted set
see redis.io/commands
terminal: redis-cli