Data Management Class Notes 17/03/2014

Relational Database Management System Syntax

Different database management systems, like MySQL, PostgreSQL, SQLite, Access..., have different variations in their query and management languages.
For example:

To list the tables present in a database
- MySQL: SHOW TABLES;
- PostgreSQL: \dt
- SQLite: .tables
A difference operation
SELECT * FROM R MINUS SELECT * FROM S;
will not work in PostgreSQL. The correct syntax is
SELECT * FROM R EXCEPT SELECT * FROM S;
which will output the difference ommiting the duplicate instances.
A difference operation in PostgreSQL with the duplicate (if existing) is performed with
SELECT * FROM R EXCEPT ALL SELECT * FROM S;
but this command will not work with SQLite or MySQL.
A difference in MySQL is done with
SELECT * FROM R WHERE A NOT IN (SELECT * FROM S);

Differences Between Relational Algebra And SQL

SQL data model is a multiset not a set

No order among rows (a row can't be identify as being the 1st row, 2nd row...)
It is possibe to count the number of times a particular row appears in the table
Need to specify how to handle the duplicates
Need to know whether duplicates are removed

SQL contains all the power of relational algebra and more
Many redundant operators (in relational algebra only intersection, which can be computed using difference, is redundant)
Special element: NULL
Operations for: inserting, deleting, updating

Multisets

In a relational algebra, the basic object is a set:
It is not possible to count how many time an element appears.
In SQL the basic element is a multiset:
It is possible to count how many time an element appears.
The following two tables are equal, because:
- They contain the same rows with the same multiplicity
- The order of rows does not matter
- The order of columns does not matter, as they are labeled

R	A	B
	1	10
	2	20
	2	20
	2	20

S	B	A
	20	2
	20	2
	10	1
	20	2

The following two tables are not equal, because:
- There is a row that appears with different multiplicities in the two tables
- Row (2,20) appears twice in R but only once in S
R A B

1 10

2 20

2 20

2 20

S A B

1 10

2 20

2 20

But in a set they would be equal!

Queries On A Single Table

Order of execution of a query:
- 1. FROM: a single table (or a Cartesian product of the same table with an alias)
- 2. [WHERE]: choose the rows according to condition
- 3. SELECT: choose the columns

Find full data on every customer located in Boston:

SELECT *
FROM Customer
WHERE Ccity = 'Boston';

Find Pname for all plants that are located in Boston:
- SELECT Pname
- FROM Plant
- WHERE Pcity = 'Boston';
Find every C who is supplied from a plant in the same city they it is in and the plant’s profit is at least 50000
- SELECT C
- FROM Plant, Customer
- WHERE Plant.Pcity = Customer.Ccity
- AND Plant.P = Customer.P
- AND Profit >= 50000;

We want to produce a table with the schema (Bigger,Smaller), where bigger and smaller are two P located in the same city and the Profit of the Bigger is bigger than that of the Smaller
- SELECT First.P AS Bigger, Second.P AS Smaller
- FROM Plant AS First, Plant AS Second
- WHERE First.City = Second.City AND First.Profit > Second.Profit;
Note: the term following "AS" are alias of the original table or attribute name.

In some database management systems, the data manipulation language omits the "AS" particle by substituting it with a space:
- SELECT First.P Bigger, Second.P Smaller
- FROM Plant First, Plant Second
- WHERE First.City = Second.City AND First.Profit > Second.Profit;
These last two example will output an identical result.

With the use of the INTO statement, a new table called CnameInCcity is created containing the result of the query.
SELECT Ccity, Cname INTO CnameInCcity
FROM Customer WHERE Ccity = "Boston";

Existential Quantifier vs. Universal Quantifier

An universal quantification is the expression that all the instances of a a relational multiset satisfy a proposition, while an existencial quantifier is the expression that the proposition can be satisfied by at least one of the instance of the multise.
It is easier to demonstrate that in the multiset there exists at least one instance following the proposition than to show that all the instances universally follow the proposition.

Data Management Class Notes for 17/03/2014

Relational Database Management System Syntax

Differences Between Relational Algebra And SQL

Multisets

Relational Algebra vs. SQL

Queries On A Single Table

Existential Quantifier vs. Universal Quantifier