Blue Collar Database by David Twamley (2/5

arghtalentΔιαχείριση Δεδομένων

31 Ιαν 2013 (πριν από 4 χρόνια και 6 μήνες)

226 εμφανίσεις

Blue Collar Database

A Workingman's Review

(heavily skewed by a career of web development)

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

http://tinyurl.com/bluecollardb

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/ ]