Cincinnati Linux Users Group

Document Sample
Cincinnati Linux Users Group Powered By Docstoc
					Database Fundamentals
Cincinnati Linux Users Group
October 25, 2008

Database Fundamentals
Cincinnati Linux Users Group – 25Oct08

Introduction

Database Fundamentals
Cincinnati Linux Users Group – 25Oct08

An Eye Opener

Database Fundamentals
Cincinnati Linux Users Group – 25Oct08

Database Fundamentals
Cincinnati Linux Users Group – 25Oct08

Database Fundamentals
Cincinnati Linux Users Group – 25Oct08

Database Fundamentals
Cincinnati Linux Users Group – 25Oct08

Database Fundamentals
Cincinnati Linux Users Group – 25Oct08

Database Fundamentals
Cincinnati Linux Users Group – 25Oct08

Intro to RDBMS

Database Fundamentals
Cincinnati Linux Users Group – 25Oct08

Organizing Your Data
Invest some time into thinking about your data How it will be used? Who will use it? What Data Types will you need to use?

Database Fundamentals
Cincinnati Linux Users Group – 25Oct08

Data Hierarchy
Databases have Tables Tables have Rows Each Row represents a unique entity Rows have Fields Each Field is an “Attribute” of the entity

Database Fundamentals
Cincinnati Linux Users Group – 25Oct08

Tables
Primary Key Unique value ensures accurate ID Foreign Key References a Primary Key in another table

Database Fundamentals
Cincinnati Linux Users Group – 25Oct08

Fields
Make them easy to read! COB Customer_Open_Balance Avoid Spaces! Customer Account Num Customer_Account_Num

Database Fundamentals
Cincinnati Linux Users Group – 25Oct08

Normalization
Goal: Avoid anomalies and inconsistencies "The goal of normalization in simple terms is just this: to store each fact in exactly one place. When you put each fact in only one place, you always know where to go to read it or write it. When facts are repeated in the database, the application programmer has an increased burden to make sure they are all consistent. If he fails to shoulder this burden completely, the database will have inconsistent values for the same facts, leading to emergency phone calls and emails requesting help."

Database Fundamentals
Cincinnati Linux Users Group – 25Oct08

Denormalization
Convenience Calculated values accessed more easily Stability As new features requested, derived values stored and accessed more easily Simplify Avoid “Join Jungles” Performance Joins are expensive (in time and resources)

Database Fundamentals
Cincinnati Linux Users Group – 25Oct08

An Example

Database Fundamentals
Cincinnati Linux Users Group – 25Oct08

Employees
employee_id first_name last_name department_id

Database Fundamentals
Cincinnati Linux Users Group – 25Oct08

Vehicles
vehicle_id employee_id make model color license

Database Fundamentals
Cincinnati Linux Users Group – 25Oct08

Departments
department_id name

Database Fundamentals
Cincinnati Linux Users Group – 25Oct08

Employees employee_id first_name last_name department_id

Vehicles vehicle_id employee_id make model color license

Departments department_id name

Database Fundamentals
Cincinnati Linux Users Group – 25Oct08

The Danger of Non-Normalization
What if an employee has more than one car?

What if a department changes its name?

Database Fundamentals
Cincinnati Linux Users Group – 25Oct08

Database T ools

Database Fundamentals
Cincinnati Linux Users Group – 25Oct08

Structured Query Language (SQL)
How we get data out of (and into) the database Select Insert select * from tablename; insert into tablename values (v1, v2, v3); Update update tablename set field = v1; Delete delete from tablename where Joins

Database Fundamentals
Cincinnati Linux Users Group – 25Oct08

Command Line

Database Fundamentals
Cincinnati Linux Users Group – 25Oct08

PHPMyAdmin

Database Fundamentals
Cincinnati Linux Users Group – 25Oct08

MySQL Query Browser

Database Fundamentals
Cincinnati Linux Users Group – 25Oct08

Applications

Database Fundamentals
Cincinnati Linux Users Group – 25Oct08

Web-Based Applications
Using MySQL and PHP in these examples “Your mileage may vary.”

Database Fundamentals
Cincinnati Linux Users Group – 25Oct08

Data Warehouses
Making data available for reporting and supporting business decisions OLAP vs OLTP On Line Analytical Processing On Line Transactional Processing

Database Fundamentals
Cincinnati Linux Users Group – 25Oct08

Business Intelligence
Support business decisions Identify trends, markets, opportunities, etc. Average customer purchases per year Average price sold per transaction Customer business size

Database Fundamentals
Cincinnati Linux Users Group – 25Oct08

Resources

Database Fundamentals
Cincinnati Linux Users Group – 25Oct08 http://www.wampserver.com/ WAMPServer http://www.mysql.com/ MySQL http://www.postgresql.org/ PostgreSQL http://www.firebirdsql.org/ Firebird http://www.sqlite.org/ SQLite http://www.openoffice.org/product/base.html OpenOffice Base http://dev.mysql.com/downloads/gui-tools/5.0.html MySQL GUI Tools Downloads http://dev.mysql.com/doc/administrator/en/index.html MySQL Administrator 1.2 http://dev.mysql.com/doc/query-browser/en/ MySQL Query Browser 1.2

Database Fundamentals
Cincinnati Linux Users Group – 25Oct08 http://www.mysql.com/products/workbench/ MySQL Workbench http://fabforce.net/dbdesigner4/ FabForce DBDesigner4 http://www.vfront.org/en/ VFront http://www.databaseanswers.org/ DatabaseAnswers.org http://www.databaseanswers.org/data_models/ DatabaseAnswers.org Data Models http://www.w3schools.com/sql/default.asp W3Schools SQL Tutorials http://www.tizag.com/sqlTutorial/ Tizag.com SQL Tutorials http://database-programmer.blogspot.com/2008/10/argument-for-normalization.html The Database Programmer: The Argument for Normalization http://database-programmer.blogspot.com/2008/10/argument-for-denormalization.html The Database Programmer: The Argument For Denormalization

Database Fundamentals
Cincinnati Linux Users Group – 25Oct08 http://www.sqlcommands.net/ SQL Commands http://lampcomputing.com/setting-mysql-database-users-and-sample-table LAMPComputing.com: Setting Up MySQL - Database, Users And Sample Table http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/the-manga-guide-to-databases The Manga Guide to Databases

Database Fundamentals
Cincinnati Linux Users Group – 25Oct08

Questions?