SQL Database

An introduction to SQL database queries and concepts.

A Brief History of Databases

The first database

1960

Computers become cost-effective enough for private businesses to use them for data management

1970

E.F. Codd, working for IBM, publishes a paper describing the relational database model.

1980

SQL becomes a technological standard for query languages.

1990

After a period of heavy market saturation, remaining database companies continue to sell their products at high prices.

2000

Interactive applications are created to interact with databases.

What is SQL?

  • Structured Query Language
  • Used to manipulate data in a relational database

What is a Relational Database?

Relations

  • A tuple data structure
  • Order of tuples in database does not matter
  • Order of elements in tuple does not matter

Schema

A representation of how information is organized in a database.

Entity-Relationships

Database design focus on the data application, instead of logical table structure.

Relational databases

A database schema, or logical organization, is disconnected from how the information is physicaly stored on the computer.

Database Mathematics

  1. Set theory
  2. Relational algebra
  3. Relational calculus (skipping)

Set theory

A set is a collection of things
where each thing in the collection is unique.

Set notation

We write sets as a comma seperated list
inside a pair of curly braces.

{ }

Relational Algebra

The main application of relational algebra is providing a theoretical foundation for relational databases, particularly query languages for such databases, chief among which is SQL. (wikipedia.org/relational_algebra)

Five primitive relational operators

Union
Intersection
× Cartesian product
π Projection
σ Selection

Union (∪)

The union of two sets A and B
is the set of elements which are
in A, in B, or in both A and B.

Example
A = {2, 4, 6, 8}
B = {1, 3, 5, 7}
A ∪ B = {1, 2, 3, 4, 5, 6, 7, 8}

Intersection (∩)

The intersection of two sets A and B
is the set that contains all elements of A that also belong to B.

Example
A = {1, 2, 3, 4}
B = {2, 4, 6, 8}
A ∩ B = {2, 4}

Cartesian product (×)

The Cartesian product of sets A and B is the set of all ordered pairs (a, b) where a ∈ A and b ∈ B.

Example
A = {1, 2}
B = {3, 4}
A × B = {(1,3), (1,4), (2,3), (2,4)}
B × A = {(3,1), (3,2), (4,1), (4,2)}

Projection (Π)

A projection is the subset of available columns based on a set of attribute names where attributes not listed in the set have been discarded.

Account
Name Email Age
Gunther gunther@salvius.org 22
Mark mark@aol.com 65
Abby abby42@gmail.com 21
Π Email, Name(Account)
Email Name
gunther@salvius.org Gunther
mark@aol.com Mark
abby42@gmail.com Abby

Selection (σ)

A selection is an operation that returns a subset of the rows in a database.

Account
Name Email Age
Gunther gunther@salvius.org 22
Mark mark@aol.com 65
Abby abby42@gmail.com 21
σ Email="gunther@salvius.org" (Account)
Name Email Age
Gunther gunther@salvius.org 22
σ Age<50 (Account)
Name Email Age
Gunther gunther@salvius.org 22
Abby abby42@gmail.com 21

Converting relational algebra to SQL

SQL query syntax

SELECT column_name, column_name
FROM table_name
WHERE column_name operator value;

Union (∪)

SELECT *
FROM Account
WHERE Name="Gunther"
OR Email="gunther@salvius.org";

Intersection (∩)

SELECT *
FROM Account
WHERE Age > 20
AND Age < 30;

Cartesian product (×)

SELECT Name, Address
FROM Account, Location;
Location
Email Street City State
gunther@salvius.org 24 Main Street Springfiled MA
mark@aol.com 208 Innovation Drive Silicon Valley CA
abby42@gmail.com 4 Pine Grove Lane Ducktrap ME

Since we have 3 Accounts and 3 Locations, the cardinality of the cartesian product will be 108.

This is because each tuple in the Account table contains 3 elements and we have 3 Accounts. There is 4 elements in row of the Location table and there is 3 rows. (3 * 3) * (3 * 4) = 108

Projection (π)

SELECT Email, Name
FROM Account
Email Name
gunther@salvius.org Gunther
mark@aol.com Mark
abby42@gmail.com Abby

Selection (σ)

SELECT *
FROM Account
WHERE Age < 50;
Name Email Age
Gunther gunther@salvius.org 22
Abby abby42@gmail.com 21

Links

What I did not cover

  • Entity-Relationship diagrams
  • Additional SQL syntax (ORDER BY, GROUP BY, etc.)
  • Types of join operations (inner join, outer join, etc.)
  • Schema requirements (Not NULL, etc.)
  • Primary keys, candidate keys and foreign keys