SQL and You: A friendly introduction to databases

1
SQL and YouA friendly introduction to databases

• Kirk Annekma_at_geneseo.edu

2
What is a database?
3
Parts of a database

• Attributes (fields)
• An attribute/field describes something about an
  item.
• Records
• A record is the representation of an individual
  item.
• Table
• A collection of records
• Database
• A collection of tables and rules for accessing
  the tables

4
Parts of a database
Record
Tables
Attribute/Field

• Attributes/fields become columns
• Records become rows
• Rules determine the relationship between the
  tables and tie the data together to form a
  database

5
I need a new database!

• Many people ask for new databases when in fact
  they only need a new table within an existing
  database.
• The data within the tables should be all related
  somehow.
• By owner
• By project

6
Creating a database

• What information are we trying to store?
• How do we describe the information?
• Phone Book/Contact entries
• Name
• Address
• Company
• Phone Number
• URL/Web Page
• Age
• Height
• Birthday
• When we added the entry

7
Data Types

• Binary
• Database specific binary objects
• Pictures, digital signatures, etc.
• Boolean
• True/False values
• Character
• Fixed width or variable size
• Numeric
• Integer, Real (floating decimal point), Money
• Temporal
• Time, Date, Timestamp

8
Phone Book/Contact Record

• Name Character
• Address Character
• Company Character
• Phone Number Character
• URL/Web Page Character
• Age Integer
• Height Real (float)
• Birthday Date
• When we added the entry Timestamp

9
E. F. Codds Normal Forms

• Summarized from Barry Wises article on Database
  Normalization
• http//www.phpbuilder.com/columns/barry20000731.ph
  p3?page1

10
What are the normal forms?

• E. F. Codd in 1972 wrote a paper on Further
  Normalization of the Data Base Relational Model
• Normal forms reduce the amount of redundancy and
  inconsistent dependency within databases.
• Codd proposed three normal forms and through the
  years two more have been added.

11
The Zero Form

• No rules have been applied
• Where most people start (and stop)
• No room for growth
• Usually wastes space

12
First Normal Form

• Eliminate repeating columns in each table
• Create a separate table for each set of related
  data
• Identify each set of related data with a primary
  key

Benefits Now we can have infinite phone numbers
or company addresses for each contact. Drawback
Now we have to type in everything over and over
again. This leads to inconsistency, redundancy
and wasting space. Thus, the second normal form
13
Second Normal Form

• Create separate tables for sets of values that
  apply to multiple records
• Relate these tables with a foreign key.

14
Third Normal Form

• Eliminate fields that do not depend on the
  primary key.

Is this enough? Codd thought so What about
many to many?
15
Kinds of Relationships

• One to One
• One row of a table matches exactly to another
• One person, one id number, one address
• One to Many
• One row of a table matches many of another
• One person, many phone numbers
• Many to Many
• One row may match many of anotheror vice versa
• Artist to Album
• One album, many artists
• Many artists, one album
• Many artists, many album

16
Fourth Normal Form

• In a many to many relationship, independent
  entities cannot be stored in the same table.
• The same phone number 3211 shared by 1 and 3
  can now be changed in one spot.

17
Fifth Normal Form

• The very esoteric one that is probably not
  required to get the most out of your database.
• The original table must be reconstructed from
  the tables into which it has been broken down.
• The rule ensures that you have not created any
  extraneous columns and all the tables are only as
  large as they need to be.

18
The Normal Forms

• First Form
• Eliminate replicated data in tables
• Create separate tables for each set of related
  data
• Identify each set of related data with a primary
  key
• Second Form
• Create separate tables for sets of values that
  apply to multiple records
• Relate the tables with a foreign key
• Third Form
• Eliminate fields that do not depend on the
  primary key
• Fourth Form
• In many-to-many relationships, independent
  entities cannot be stored in the same table
• Fifth Form
• So rarely used but the purest form of separation
  of data

19
What do I need to remember?

• Keep normalization in mind.
• Dont replicate data in a table.
• If you break the rules, know why you are breaking
  the rules and do it for a good reason.
• Speed
• Organization
• Thats the way it comes to you

20
All you need to know about SQL in 30 minutes
21
Basic SQL Commands

• Creating tables with CREATE
• Adding data with INSERT
• Viewing data with SELECT
• Removing data with DELETE
• Modifying data with UPDATE
• Destroying tables with DROP

22
Creating tables with CREATE

• Generic form
• CREATE TABLE tablename (
• column_name data_type attributes,
• column_name data_type attributes,
• )
• Table and column names cant have spaces or be
  reserved words like TABLE, CREATE, etc.

23
Phone Book/Contact Record

• Name Character
• Address Character
• Company Character
• Phone Number Character
• URL/Web Page Character
• Age Integer
• Height Real (float)
• Birthday Date
• When we added the entry Timestamp

24
Phone Book/Contact Table

• CREATE TABLE contacts (
• Name VARCHAR(40),
• Address VARCHAR(60),
• Company VARCHAR(60),
• Phone VARCHAR(11),
• URL VARCHAR(80),
• Age INT,
• Height FLOAT,
• Birthday DATE,
• WhenEntered TIMESTAMP
• )
• Plan your tables very carefully!
• Once created, they are difficult to change!

25
Phone Book/Contact Table

• CREATE TABLE contacts (
• ContactID INT PRIMARY KEY,
• Name VARCHAR(40),
• Address VARCHAR(60),
• Company VARCHAR(60),
• Phone VARCHAR(11),
• URL VARCHAR(80),
• Age INT,
• Height FLOAT,
• Birthday DATE,
• WhenEntered TIMESTAMP
• )
• If you are going to use the relational nature of
  a database,dont forget you need to have a
  unique way to access records!
• There is a way to make the key automatically
  increment,so you dont have to worry about which
  one is next.

26
Data Types

• Binary
• Database specific binary objects (BLOB)
• Boolean
• True/False values (BOOLEAN)
• Character
• Fixed width (CHAR) or variable size (VARCHAR)
• Numeric
• Integer (INT), Real (FLOAT), Money (MONEY)
• Temporal
• Time (TIME), Date (DATE), Timestamp (TIMESTAMP)

27
Adding data with INSERT

• Generic Form
• INSERT INTO tablename (column_name,)
• VALUES (value,)

28
Inserting a record into contacts

• INSERT INTO contacts (contactid,name,address,compa
  ny,phone,url,age,height,birthday,whenentered)
• VALUES
• (1,Joe,123 Any St.,ABC,
• 800-555-1212,http//abc.com,30,1.9,6/14/197
  2,now())

29
Inserting a partial record

• INSERT INTO contacts (contactid,name,phone)
• VALUES (2,Jane,212-555-1212)

30
Automatic key generation

• CREATE SEQUENCE contactidseq
• Change the ContactID line in the CREATE TABLE
  to
• ContactID INT DEFAULT nextval(contactidseq)
  PRIMARY KEY
• Or when inserting into a table
• INSERT contacts (contactid,name,phone)
• VALUES (nextval(contactidseq),Jack, 716-5
  55-1212)

31
Viewing data with SELECT

• Generic Form
• SELECT column, FROM table, WHERE condition
  GROUP BY group_by_expression HAVING condition
  ORDER BY order_expression
• The most used command
• Probably the most complicated also
• If used improperly, can cause very long waits
  because complex combinations

32
A few simple SELECTs

• SELECT FROM contacts
• Display all records in the contacts table
• SELECT contactid,name FROM contacts
• Display only the record number and names
• SELECT DISTINCT url FROM contacts
• Display only one entry for every value of URL.

33
Refining selections with WHERE

• The WHERE subclause allows you to select
  records based on a condition.
• SELECT FROM contacts WHERE age
• Display records from contacts where age
• SELECT FROM contacts WHERE age BETWEEN 18 AND
  35
• Display records where age is 18-35

34
Additional selections

• The LIKE condition
• Allows you to look at strings that are alike
• SELECT FROM contacts WHERE name LIKE J
• Display records where the name starts with J
• SELECT FROM contacts WHERE url NOT LIKE
  .com
• Display where url does not end in .com

35
Removing data with DELETE

• Generic Form
• DELETE FROM table WHERE condition
• DELETE FROM contacts WHERE age

36
Modifying data with UPDATE

• Generic Form
• UPDATE table SET columnexpression
• WHERE condition
• UPDATE contacts SET companyAOL
• WHERE companyTime Warner

37
Destroying tables with DROP

• Generic Form
• DROP TABLE tablename
• DROP TABLE contacts

38
More about SELECT
39
Joining together tables

• SELECT name,phone,zip FROM people, phonenumbers,
  address WHERE people.addressidaddress.addressid
  AND people.idphonenumbers.id

40
Different types of JOINs

• Inner Join
• Unmatched rows in either table arent printed
• Left Outer Join
• All records from the left side are printed
• Right Outer Join
• All records from the right side are printed
• Full Outer Join
• All records are printed
• Multiple Table Join
• Join records from multiple tables

41
General form of SELECT/JOIN

• SELECT columns,
• FROM left_table
• join_type JOIN right_table ON condition
• SELECT name,phone FROM people
• JOIN phonenumbers ON people.idphonenumbers.id

42
Other versions

• SELECT name,phone FROM people
• LEFT JOIN phonenumbers ON people.idphonenumbers
  .id
• SELECT name,phone FROM people
• RIGHT JOIN phonenumbers ON people.idphonenumber
  s.id
• SELECT name,phone FROM people
• FULL JOIN phonenumbers ON people.idphonenumbers
  .id

43
Theta style vs. ANSI

• Theta Style (used in most SQL books)
• SELECT name,phone,zip FROM people, phonenumbers,
  address
• WHERE people.addressidaddress.addressid AND
  people.idphonenumbers.id
• ANSI Style uses JOIN
• SELECT name,phone,zip FROM people
• JOIN phonenumbers ON people.idphonenumbers.id
• JOIN address ON people.addressidaddress.addressi
  d

44
Other SELECT examples

• SELECT FROM contacts WHERE name is NULL
• SELECT FROM contacts WHERE zip IN
  (14454,12345)
• SELECT FROM contacts WHERE zip NOT IN
  ( SELECT zip FROM address WHERE
  stateNY )

45
GROUP BY/HAVING

• The GROUP BY clause allowsyou to group results
  together with aggregate functions
• AVG(), COUNT(), MAX(), MIN(), SUM()
• COUNT DISTINCT
• HAVING allows you to searchthe GROUP BY results

46
GROUP BY Examples

• SELECT FROM contactsGROUP BY company
• SELECT company,count(company)FROM contactsGROUP
  BY company
• SELECT company,count(company)FROM contactsGROUP
  BY companyHAVING count(company) 5

47
ORDER BY

• The ORDER BY clause allows you to sort the
  results returned by SELECT.
• SELECT FROM contacts
• ORDER BY company
• SELECT FROM contacts
• ORDER BY company, name

48
Views

• You can use CREATE VIEW to create a virtual
  table from a SELECT statement.
• CREATE VIEW contactview AS
• (SELECT name,phone,zip FROM
• people,phonenumbers,address
• WHERE people.idphonenumbers.id AND
  people.addressidaddress.addressid)

49
Working with SQL in PHP
50
Basic PHP/SQL interaction

• Open a connection to the database
• If ok, generate SQL command
• Execute SQL command
• Handle responses from the server
• If not done, go back to step 2
• If done, close connection to database

51
The actual PHP
.edu port5432 dbnamemgmt357 usermgmt357
passwordecommerce") if (!connection) print
pg_ErrorMessage() exit(-1) resultpg_exec(
connection,"select from customer") if
(result) r pg_numrows(result) row0 p
rint "\n" while
(datapg_fetch_object(result,row)) print
"" print "data-name" print
"\n" row print "\n"
else print pg_ErrorMessage() pg_close(conne
ction) ?
52
More sample code

• http//www.geneseo.edu/kma/PHP_Intro