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 [email protected] 22
Mark [email protected] 65
Abby [email protected] 21
Π Email, Name(Account)
Email Name
[email protected] Gunther
[email protected] Mark
[email protected] Abby

Selection (σ)

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

Account
Name Email Age
Gunther [email protected] 22
Mark [email protected] 65
Abby [email protected] 21
σ Email="[email protected]" (Account)
Name Email Age
Gunther [email protected] 22
σ Age<50 (Account)
Name Email Age
Gunther [email protected] 22
Abby [email protected] 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="[email protected]";

Intersection (∩)

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

Cartesian product (×)

SELECT Name, Address
FROM Account, Location;
Location
Email Street City State
[email protected] 24 Main Street Springfiled MA
[email protected] 208 Innovation Drive Silicon Valley CA
[email protected] 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
[email protected] Gunther
[email protected] Mark
[email protected] Abby

Selection (σ)

SELECT *
FROM Account
WHERE Age < 50;
Name Email Age
Gunther [email protected] 22
Abby [email protected] 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