Transcript Databases

Lecture 7: Introduction to SQL
Instructor: Dr. Mohammad Anwar Hossain
Review:
• How we got here?
• Why we got here?
• Where next?
Databases
Typical Web Database Topology
Database Basics
Relationships
among
database,
DBMS, and
programs
What is a database?
Parts of a database

Attributes (fields)
◦ An attribute or field is a component of a record
that describes something about an item.

Records (Tuples)
◦ A record is the representation of an individual
item.

Table (Relation)
◦ A collection of records

Database
◦ A collection of tables and rules for accessing the
tables
What is a relational database?
Originally developed by E.F. Codd in 1970
 Organizes data into tables where each item is
a row and the attributes of the item are in
columns.
 Different from “flat file” databases because
you can define “relationships” between items
in different tables.

Parts of a database
Record/Tuple
Tables
Attribute/Field
• Records become “rows”
• Attributes become “columns”
• Rules determine the relationship
between the tables and tie the data
together to form a database
The relational data model: concepts



Based on a simple data structure – relation (or table)
All entity and relationship sets are represented as tables
Each row in a relation (a tuple)
- represents an individual occurrence of that entity
- each tuple must be uniquely identified by one or more
attributes (the primary key)
- no part of the primary key may have a null value (entity
integrity rule)
- each attribute must be an atomic ie a single value drawn from
the domain of the attribute
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
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 (in metres)
Birthday
When we added the entry
Develop a model first, helpful for visualisation. See example.
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 another or many
rows match one row of another
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
Phone Book/Contact Record
Name
Address
Company
Phone Number
URL/Web Page
Age
Height
Birthday
When we added the entry
Character
Character
Character
Character
Character
Integer
Real (float)
Date
Timestamp
“Normal Forms”
What are the “normal forms”?
E. F. Codd in 1972 wrote a paper on “Further
Normalisation 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.

The Zero Form




No rules
have been
applied
Where most
people start
(and stop)
No room for
growth
Usually
wastes space
Contacts
Name
Company
Address
Phone1
Phone2
Phone3
ZipCode
Joe
ABC
123
5532
2234
3211
12345
Jane
XYZ
456
3421
Chris
PDQ
789
2341
14454
6655
14423
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
Contacts
Id
Name
Company
Address
Phone
ZipCode
1
Joe
ABC
123
5532
12345
1
Joe
ABC
123
2234
12345
1
Joe
ABC
123
3211
12345
2
Jane
XYZ
456
3421
14454
3
Chris
PDQ
789
2341
14423
3
Chris
PDQ
789
6655
14423
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…
Second Normal Form
People
Create separate
tables for sets of
values that apply to
multiple records
 Relate these tables
with a “foreign key”.

Id
Name
Company
Address
Zip
1
Joe
ABC
123
12345
2
Jane
XYZ
456
14454
3
Chris
PDQ
789
14423
PhoneNumbers
PhoneID
Id
Phone
1
1
5532
2
1
2234
3
1
3211
4
2
3421
5
3
2341
6
3
6655
Third Normal Form

Eliminate fields that
do not depend on
the primary key.
People
PhoneNumbers
PhoneID
Id
Phone
1
1
5532
2
1
2234
3
1
3211
4
2
5
6
Id
Name
AddressID
1
Joe
1
2
Jane
2
3
Chris
3
Address
AddressID
Company
Address
Zip
1
ABC
123
12345
3421
2
XYZ
456
14454
3
2341
3
PDQ
789
14423
3
6655
Is this enough? Codd thought so…
But other forms have been developed!
Why normalise?
Increases the integrity of the data
 Reduces redundancy
 Improves efficiency
 Although normalisation can be hard, it is
worth it in the long run.

What do I need to remember?
Keep normalisation in mind.
 Don’t replicate data in a table.
 If you break the rules, know why you are
breaking the rules and do it for a good
reason.

All you need to know about SQL
in 30 minutes (or less)
History of SQL
SQL: Structured Query Language
 SQL is based on the relational tuple calculus
 SEQUEL: Structured English QUEry Language;
part of SYSTEM R, 1974
 SQL/86: ANSI & ISO standard
 SQL/89: ANSI & ISO standard
 SQL/92 or SQL2: ANSI & ISO standard
 SQL3: in the works...
 SQL2 supported by ORACLE, SYBASE,
INFORMIX, IBM DB2, SQL SERVER,
OPENINGRES,...

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

Creating tables with CREATE

Generic form
CREATE TABLE tablename (
column_name data_type attributes…,
column_name data_type attributes…,
…
)

Table and column names can’t have spaces or
be “reserved words” like TABLE, CREATE,
etc.
Phone Book/Contact Record
Name
Address
Company
Phone Number
URL/Web Page
Age
Height
Birthday
When we added the entry
Character
Character
Character
Character
Character
Integer
Real (float)
Date
Timestamp
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!
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,
don’t forget you need to have a unique way to access records!
There is a way to make the key automatically increment,
so you don’t have to worry about which one is next.
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)
Adding data with INSERT

Generic Form
INSERT INTO tablename (column_name,…)
VALUES (value,…)
Inserting a record into ‘contacts’
INSERT INTO contacts
(contactid,name,address,company,phone,url,a
ge,height,birthday,whenentered)
VALUES
(1,‘Joe’,’123 Any St.’,’ABC’,
’800-555-1212’,‘http://abc.com’,30,1.9,
’6/14/1972’,
now());
Inserting a partial record
INSERT INTO contacts (contactid,name,phone)
VALUES (2,’Jane’,’212-555-1212’);
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-555-1212’);
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 computations

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.
Refining selections with WHERE
The WHERE “subclause” allows you to select
records based on a condition.
 SELECT * FROM contacts
WHERE age<10;

◦ Display records from contacts where age<10

SELECT * FROM contacts
WHERE age BETWEEN 18 AND 35;
◦ Display records where age is 18-35
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 LIKE ‘%.com’;
◦ Display records where url ends in “.com”
Removing data with DELETE

Generic Form
DELETE FROM table WHERE condition;
DELETE FROM contacts WHERE age<13;
Modifying data with UPDATE

Generic Form
UPDATE table SET column=expression
WHERE condition;
UPDATE contacts SET company=‘AOL’
WHERE company=‘Time Warner’;
Destroying tables with DROP

Generic Form
DROP TABLE tablename;
DROP TABLE contacts;
More about SELECT
“Normal Forms” and SELECT
Good database design using the normal forms
requires data to be separated into different
tables
 SELECT allows us to join the data back
together
 We can use “views” to create virtual tables

Joining together tables

SELECT name,phone,zip FROM people,
phonenumbers, address WHERE
people.addressid=address.addressid AND
people.id=phonenumbers.id;
People
PhoneNumbers
Id
Name
AddressID
1
Joe
1
2
Jane
2
3
Chris
3
PhoneID
Id
Phone
1
1
5532
2
1
2234
AddressID
Company
Address
Zip
3
1
3211
1
ABC
123
12345
4
2
3421
2
XYZ
456
14454
5
3
2341
3
PDQ
789
14423
6
3
6655
Address
Different types of JOINs

“Inner Join”

“Left Outer Join”

“Right Outer Join”

“Full Outer Join”

Multiple Table Join
◦ Unmatched rows in either table aren’t printed
◦ All records from the “left” side are printed
◦ All records from the “right” side are printed
◦ All records are printed
◦ Join records from multiple tables
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.id=phonenumbers.id;
Other versions
SELECT name,phone FROM people
LEFT JOIN phonenumbers ON
people.id=phonenumbers.id;
SELECT name,phone FROM people
RIGHT JOIN phonenumbers ON
people.id=phonenumbers.id;
SELECT name,phone FROM people
FULL JOIN phonenumbers ON
people.id=phonenumbers.id;
“Theta style” vs. ANSI

Theta Style (used in most SQL books)
SELECT name,phone,zip FROM people, phonenumbers, address
WHERE people.addressid=address.addressid AND
people.id=phonenumbers.id;

ANSI Style uses JOIN
SELECT name,phone,zip FROM people
JOIN phonenumbers ON people.id=phonenumbers.id
JOIN address ON people.addressid=address.addressid;
Other SELECT examples
SELECT * FROM contacts
WHERE name is NULL;
 SELECT * FROM contacts
WHERE zip IN (‘14454’,’12345’);
 SELECT * FROM contacts
WHERE zip IN (
SELECT zip FROM address
WHERE state=‘NY’
);

GROUP BY/HAVING

The “GROUP BY” clause allows you to group
results together with “aggregate functions”
◦ AVG(), COUNT(), MAX(), MIN(), SUM()
◦ COUNT DISTINCT

HAVING allows you to search the GROUP
BY results
GROUP BY Examples
SELECT company,count(company)
FROM contacts
GROUP BY company;
SELECT company,count(company)
FROM contacts
GROUP BY company
HAVING count(company) > 5;
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;
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.id=phonenumbers.id AND
people.addressid=address.addressid);
PHP style
Now… Let’s do it on the web
Basic PHP/SQL interaction
1.
2.
3.
4.
5.
6.
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
Creating a table with PHP
<?php
// Connection Parameters
//config.php
$db_host="127.0.0.1:3306";
$db_name="sweDB";
$username="root";
$password="";
// connect db
$db_con=mysql_connect($db_host,$username,$password);
if (!$db_con)
{
die('Could not connect: ' . mysql_error());
}
// select db
$connection_string=mysql_select_db($db_name,$db_con) or die("Could not select
examples");
?>
Creating a table with PHP
<html>
<body>
<?php include('config.php') ?>
<?php
//display all the records in department table
$sql = "SELECT * FROM department ORDER BY deptid ASC";
$result = mysql_query($sql) or die(mysql_error());
echo "<h2>Department Information<h2>";
echo "<table border='1‘><tr><th>deptID</th><th>Department Name</th></tr>";
while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['deptID'] . "</td>";
echo "<td>" . $row['deptName'] . "</td>";
echo "</tr>";
}
echo "</table>";
mysql_close($con);
?>
</body>
</html>
Summary:
When considering a developing a database:
• Consider physical topology/architecture
• Consider logical topology/architecture
• Design a model (E-R)
• Design data structure
• Normalise
• Implement
• Develop applications to query