Relational Model

You can find the lecture as a postgresql script
First published by E.F. Codd in 1970
A relational database consists of a collection of tables
A table consists of rows and columns
each row represents a record
each column represents an attribute of the records contained in the table

Exmple: The Suppliers and Parts Database

A DB with three tables (SUPPLIER, PART, SELLS):

SUPPLIER has attributes: number (SNO), the name (SNAME) and the city (CITY)
PART has attributes: number (PNO) the name (PNAME) and the price (PRICE)
SELLS has attributes: part (PNO) and supplier (SNO). SELLS connects SUPPLIER and PART.

SUPPLIER:                   SELLS:
 SNO |  SNAME  |  CITY       SNO | PNO
----+---------+--------     -----+-----
 1  |  Smith  | London        1  |  1
 2  |  Jones  | Paris         1  |  2
 3  |  Adams  | Vienna        2  |  4
 4  |  Blake  | Rome          3  |  1
                              3  |  3
                              4  |  2
PART:                         4  |  3
 PNO |  PNAME  |  PRICE       4  |  4
----+---------+---------
 1  |  Screw  |   10
 2  |  Nut    |    8
 3  |  Bolt   |   15
 4  |  Cam    |   25

Example Continued

Row (1,Smith, London) in SUPPLIER represents a supplier with supplier number 1 whose name is Smith and which is based in London.
Row (2,Nut,8) in PART represents a part with part number 2, part name Nut and price 8.
Row (1,2) in SELLS represents

Entities and Relations

An entity represents something real
A relation represents a connection between entities
The tables PART and SUPPLIER may be regarded as entities
SELLS may be regarded as a relationship between a particular part and a particular supplier.

Some Formalities

A domain is a set of values. The set of integers is a domain, so is the set of all strings.
The cartesian product of domains D₁ x D₂ x ... x D_k is the set of all k-tuples (v₁,...,v_k) where v₁ is in D₁,..., v_k is in D_k
Example: If k=2 and D₁={0,1} and D₂={a,b,c} then D₁ × D₂ is {(0,a),(0,b),(0,c),(1,a),(1,b),(1,c)}.

Structured Query Language (SQL)

Used on a RDBMS to create, search and modify tables.

Table creation (by example): Executing the following causes the creation of the Suppliers and Parts database above.

CREATE TABLE SUPPLIER (SNO INTEGER, SNAME VARCHAR(20), CITY VARCHAR(20));
CREATE TABLE PART (PNO INTEGER, PNAME VARCHAR(20), PRICE DECIMAL(4,2));
CREATE TABLE SELLS (SNO INTEGER, PNO INTEGER);

SQL Data types

Some SQL Data types your database may support

INTEGER: signed fullword binary integer
DECIMAL (p[,q]): signed packed decimal number of up to p digits, with q digits to the right of the decimal point. If q is omitted it is assumed to be 0. MSAccess supports NUMBER instead of DECIMAL
FLOAT: signed doubleword floating point number.
CHAR(n): fixed length character string of length n.
VARCHAR(n): varying length character string of maximum length n
DATE: A date attribute in a DBMS-specific format.

A note about NULL

SQL allows the NULL value to appear in tuples (table rows). A NULL indicates a non-initialized attribute in a row. This can be disallowed by adding a NOT NULL constraint in table creation

CREATE TABLE SUPPLIER (SNO INTEGER, 
	SNAME VARCHAR(20) NOT NULL, 
	CITY VARCHAR(20));

When adding a row to the SUPPLIER table, an SNAME must be specified!

Searching using SQL (by example)

The result of a SQL search is a table.

(Query1) SELECT * FROM PART;
Results in the whole PART table.

(Query2) SELECT * FROM PART WHERE PRICE > 10;
Results:

 PNO |  PNAME  |  PRICE
-----+---------+--------
  3  |  Bolt   |   15
  4  |  Cam    |   25

(Query3) SELECT PNAME, PRICE FROM PART WHERE PRICE > 10;
Results:

 PNAME  |  PRICE
--------+--------
 Bolt   |   15
 Cam    |   25

(Query4) SELECT PNAME, PRICE FROM PART WHERE PNAME = 'Bolt' AND
         (PRICE = 0 OR PRICE <= 15);

Results:

 PNAME  |  PRICE
--------+--------
 Bolt   |   15

Cartesian products

(Query5) SELECT * FROM SUPPLIER, PART;

The above example forms the table SUPPLIER x PART with rows of the form (s,p) where s is a row in SUPPLIER, p is a row in PART. In total SUPPLIER x PART has a total of 4*4 rows.

Cartesian products cont.

(Query6) SELECT * FROM SUPPLIER S1, SUPPLIER S2;

The above example forms the table SUPPLIER x SUPPLIER with rows of the form (s,p) where s and p are rows in SUPPLIER. This table has 4*4 rows.
Notice that we have aliased (SUPPLIER S1) the tables so we can identify where attributes are from.
Results:

S1.SNO    S1.SNAME    S1.CITY    S2.SNO    S2.SNAME    S2.CITY
1         Smith       London     1         Smith       London
2         Jones       Paris      1         Smith       London
3         Adams       Vienna     1         Smith       London
4         Blake       Rome       1         Smith       London
1         Smith       London     2         Jones       Paris
2         Jones       Paris      2         Jones       Paris
3         Adams       Vienna     2         Jones       Paris
4         Blake       Rome       2         Jones       Paris
1         Smith       London     3         Adams       Vienna
2         Jones       Paris      3         Adams       Vienna
3         Adams       Vienna     3         Adams       Vienna
4         Blake       Rome       3         Adams       Vienna
1         Smith       London     4         Blake       Rome
2         Jones       Paris      4         Blake       Rome
3         Adams       Vienna     4         Blake       Rome
4         Blake       Rome       4         Blake       Rome

Joins

Matching up rows in tables based on the same value for columns.

For each supplier, we want a list of the parts they sell. Join SUPPLIER and SELLS on SNO, join SELLS and PART on PNO.

(Query7) SELECT S.SNAME, P.PNAME
    FROM SUPPLIER S, PART P, SELLS SE
    WHERE S.SNO = SE.SNO AND P.PNO = SE.PNO;

Note: The tables have been aliased in the above query. SUPPLIER is also known as S, PART as P etc. S.SNO is the SNO attribute of the SUPPLIER table.
Results:

 SNAME | PNAME
-------+-------
 Smith | Screw
 Smith | Nut
 Jones | Cam
 Adams | Screw
 Adams | Bolt
 Blake | Nut
 Blake | Bolt
 Blake | Cam

Exercise: Modify the above query to list all suppliers of Bolts.

Order By

(Query7-1) SELECT S.SNAME, P.PNAME
    FROM SUPPLIER S, PART P, SELLS SE
    WHERE S.SNO = SE.SNO AND P.PNO = SE.PNO
    ORDER BY S.SNAME;

Results:

 SNAME | PNAME
-------+-------
 Adams | Bolt
 Adams | Screw
 Blake | Cam
 Blake | Bolt
 Blake | Nut
 Jones | Cam
 Smith | Nut
 Smith | Screw

Exercise: Write a query to simplify finding the cheapest supplier of Bolts.

Data Manipulation

Add a row to SUPPLIER

(Query8)
INSERT INTO SUPPLIER (SNO, SNAME, CITY) VALUES (1, 'Smith', 'London');

Update rows in a table

(Query9)
UPDATE PART SET PRICE = 15 WHERE PNAME = 'Screw';

Delete rows from a table

(Query10)
DELETE FROM SUPPLIER WHERE SNAME = 'Smith';

Creating tables and indexes

(createSupplier)
CREATE TABLE SUPPLIER (SNO INTEGER, SNAME VARCHAR(20), CITY VARCHAR(20));

(supplierIndex)
CREATE UNIQUE  INDEX supplierIndex
ON supplier (SNO);

Etc...

A relational database is a powerfull tool. We have not covered...

Transaction processing
Concurrent access
Aggregate queries
Stored procedures (PL/SQL, embedded Java)
Integrity constraints
Design
Indexes
Fault tolerence
Online backups
Database distribution
etc...

We have just scratched the surface.