# SQL Database

An introduction to SQL database queries and concepts.

## 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

## 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

## 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]";

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

## Cartesian product (×)

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