Transcript Blue Collar Database by David Twamley (2/5)

Blue Collar Database
A Workingman's Review
(heavily skewed by a career of web development)
http://tinyurl.com/bluecollardb
Copyright (c) 2009 David Twamley, Prepared for SJSU CS 157B, Feb. 5, 2009
"Fair Use" references to original works in accordance with US Copyright Law §107
Overview
• Essential Job Skill
o Interview Success
o Market Leaders
o Market Trends
• Administration
o Be your own DBA
• Design
o 3NF + Tips
• Good Security Practices
o Protect Data + SQL Injection
Interview Prep
Every Interview for the rest of your life will ask...
•
•
•
•
•
What experience do you have with SQL Server?
What experience do you have with Oracle?
What experience do you have with MySQL?
What experience do you have with PostgreSQL?
What experience do you have with __DB__?
Bad Answers: None. No. Yes.
Interview Success
Absolutely, I've used databases extensively. Let me
tell you about this one project where I...
•
•
•
•
•
•
designed...
and developed...
and tested...
and deployed...
and administered...
and tuned...
State how your project added $value.
I Dated Her Sister
But we're looking for experience with __XYZ DB__!
Yes sir, __XYZ DB__ is definitely one of the top
databases in the market. As I mentioned, most of my
professional experience is with __ABC DB__ and that
experience carries over well since they are all in the
same family. And, of course I've tinkered with __XYZ
DB__ as well on smaller projects like...
TOO MANY WORDS, HOW 'BOUT A PICTURE...
SQL Server and
Oracle are
practically twins!
A Codd Family Reunion
Market Leaders According to Craig
Let's ask Craigslist about jobs in the San Francisco Bay Area.
[ JAN 29, 2009. http://sfbay.craigslist.org/jjj/ ]
Market Trends According to Google
Based on Search History.
[ JAN 29, 2009. http://www.google.com/trends ]
Latest Version Trends
Interest in recent versions.
[ JAN 29, 2009. http://sfbay.craigslist.org/jjj/ ]
Losing the Race
Here's Google's regional statistics for those search trends.
Notice anything missing?
Where is America!?
There we are
We're not even #1 in this category.
Step It Up
Obama wants you to help keep tech jobs
in America. So...
Administration
I've never met a DBA!!
You're on your own.
Your DBA Responsibilities
Maintenance Tasks
• Create
• Tune
• Debug
• Security
• Manage Production, Stage, Dev databases
• Script and test your schema upgrades
• Disaster Recovery (Backup Plan)
Learn the admin tools!
Learn SQL!
Structured Query Language (SQL)
Even if Object Relation Mapping (ORM) or LINQ
(Language INtegrated Query) saves you day-to-day pain
you will fall back on this essential skill.
Only small differences between T-SQL, SQL/PL, etc.
Data Manipulation Language (DML)
SELECT, INSERT, UPDATE
Data Definition Language (DDL)
CREATE, ALTER, DROP
Data Control Language (DCL)
GRANT, REVOKE, DENY
Creating Databases
• Requires good design
skills.
• Similar to object analysis
in OOD.
• Establish naming
conventsions.
• Make ER Diagrams.
• Learn 3NF. Seriously.
• Study good designs.
[ http://www.phpwebcommerce.com/shopping-cart-database-design.php ]
Third Normal Form (3NF)
I've never needed to go beyond 3NF professionally and the
math notation is only useful for proofs. A lot of people much
smarter than you and I have already proved it so lets move on.
We need to focus on applying it!
• Identify Entities (often nouns that match your classes)
• Identify a Key for each Entity
• Identify Type of Relationship between Entities
o many-to-many (requires a third "intermediate" table)
o many-to-one (FK->PK)
o one-to-one (rare)
• Eliminate Redundancy
3NF should become automatic for you.
3NF Step-by-Step
[ http://dotnet.org.za/willy/archive/2008/04/10/taking-a-step-back-database-normalisation-1nf-2nf-3nf-bcnf-and-4nf-part-1.aspx ]
The 3NF Oath
"Each attribute represents a
fact about the key, the whole
key, and nothing but the key
so help me Codd."
[ http://en.wikipedia.org/wiki/Third_normal_form ]
Database Design Tips
• Email addresses make for a bad key.
o What if the user changes their email address?
• Use an Auto Incrementing ID for a Primary Key
o Do you usually give your object instances names?
o Just give them a number and move on.
o Many popular ORMs like ActiveRecord do this
automatically.
• Choose Indexes Wisely
o If your email address isn't the primary key, index it!
o Profile your database and let real usage patterns dictate
your indexes
Good Security Practices
Protect data from hackers.
Protect data even from your own employees.
For instance, don't store plain text passwords for your user
accounts in the database.
Salt + Hash Password fields
Encrypt Credit Card fields
Have more than one account for accessing data and give each
appropriately limited permissions.
Guard Against SQL Injection
SQL Injection is a very serious concern and its your job to
guard against it.
[ http://www.unixwiz.net/techtips/sql-injection.html ]
What is SQL Injection
Bottom line: Don't trust user input. Ever.
Sanitize it.
Escape it.
Pass it as a typed parameter.
[ http://www.ybo-interactive.com/blog/2008/01/03/sql-injection/ ]