Logical Layer

The Relational Data Model

+ SQL
Abstraction Layers
Conceptual
What data is held
An Image and its meta-data
Entity-Relationship model (ERM)
Logical
How data is organised in storage
Block and Directory structure
Tables, keys
Physical
How data is stored in bits
JPEG as a stream of bytes
A Database as files and records stored in a DBMS-specific
format
Abstraction
Realisation
(Refinement
Reification)
(Reverse
Engineering)
(Engineering,
Model-Driven
development
The Relational Data Model

The Theory underlying Relational
Databases Access, Oracle, MySQL..
E.F Codd A Relational Data Model for
Large Shared Data Banks (1970)
All Relational DBMSs depart from the
basic model

Components
The concepts available to represent the
UoD
Relations (tables) of
Tuples (rows) , of
Columns (fields) containing values drawn
from a Domain
Base Relations - facts
Derived Relations -
Relations constructed by extracting,
combining base relations
EMP-DEPT example (from SQL
workbook)
Two relations:

Department : DEPT

Employee : EMP

DEPT Table


Deptno Dname Location
10 Accounting New York
20 Research Dallas
30 Sales Chicago
40 Operations Boston
EMP - table
Empno Ename Mgr Sal Deptno
7369 SMITH 7902 800.00 20
7499 ALLEN 7698 1,600.00 30
7521 WARD 7698 1,250.00 30
7566 JONES 7839 2,975.00 20
7654 MARTIN 7698 1,250.00 30
7698 BLAKE 7839 2,850.00 30
7782 CLARK 7839 2,450.00 10
7788 SCOTT 7566 3,000.00 20
7839 KING 5,000.00 10
7844 TURNER 7698 1,500.00 30
7876 ADAMS 7788 1,100.00 20
7900 JAMES 7698 950.00 30
7902 FORD 7566 3,000.00 20
7934 MILLER 7782 1,300.00 10
Relation
Deptno Dname Loc
10 Accounting New York
20 Research Dallas
30 Sales Chicago
40 Operations Boston
Relation
(table)
Tuple
(row)
Column (field)
domain
string
city name
general
specific
integer
0<int<99
Relations are everything
There is only one data structure in the relational
data model - the relation
Every relation in a database must have a distinct
name.
Every column in a relation must have a distinct name
within the relation.
All entries in a column must be of the same kind
The ordering of columns in a relation is not
significant.
Each row in a relation must be distinct
Leads to Primary Key in a Relational Database
The ordering of rows is not significant.
Each cell or column/row intersection in a relation
should contain only a so-called atomic value.






Relational Algebra
A group of operations on relations which yield
only other relations Closed
A single tuple (row) is a relation
A single value is also a relation
Base operations
RESTRICT, PROJECT, PRODUCT
Convenience operations
EQUI-JOIN, (Natural) JOIN, Outer Joins
Set operations
UNION, INTERSECTION, DIFFERENCE, DIVISION

The Relational Algebra
Restrict
Project
Product
Union
Intersect
SQL (SeQueL)
There are a number of languages for
manipulating relations, but the one most
commonly implemented is SQL
SQL1 - 1986
SQL2 - 1992
better support for Algebraic operations
SQL3 - 1999 Post-Relational
row and column types, stored procedures,
triggers, references, large objects
SQL
DATA MANIPULATION (DML) - factbase
QUERY
SELECT
UPDATE
UPDATE
DELETE
INSERT
DATA DEFINITION (DDL) -schema
CREATE, ALTER, DROP
DATA CONTROL (DCL) - access control
GRANT,REVOKE

RESTRICT:
SELECT * FROM EMP WHERE sal > 2000

Empno Ename Mgr Sal Deptno
7369 SMITH 7902 800.00 20
7499 ALLEN 7698 1,600.00 30
7521 WARD 7698 1,250.00 30
7566 JONES 7839 2,975.00 20
7654 MARTIN 7698 1,250.00 30
7698 BLAKE 7839 2,850.00 30
7782 CLARK 7839 2,450.00 10
7788 SCOTT 7566 3,000.00 20
7839 KING 5,000.00 10
7844 TURNER 7698 1,500.00 30
7876 ADAMS 7788 1,100.00 20
7900 JAMES 7698 950.00 30
7902 FORD 7566 3,000.00 20
7934 MILLER 7782 1,300.00 10
Project:
Select Empno,Mgr,Deptno from Emp

Empno Ename Mgr Sal Deptno
7369 SMITH 7902 800.00 20
7499 ALLEN 7698 1,600.00 30
7521 WARD 7698 1,250.00 30
7566 JONES 7839 2,975.00 20
7654 MARTIN 7698 1,250.00 30
7698 BLAKE 7839 2,850.00 30
7782 CLARK 7839 2,450.00 10
7788 SCOTT 7566 3,000.00 20
7839 KING 5,000.00 10
7844 TURNER 7698 1,500.00 30
7876 ADAMS 7788 1,100.00 20
7900 JAMES 7698 950.00 30
7902 FORD 7566 3,000.00 20
7934 MILLER 7782 1,300.00 10

Empno Ename Mgr Sal Deptno
7369 SMITH 7902 800.00 20
7499 ALLEN 7698 1,600.00 30
7521 WARD 7698 1,250.00 30
7566 JONES 7839 2,975.00 20
7654 MARTIN 7698 1,250.00 30
7698 BLAKE 7839 2,850.00 30
7782 CLARK 7839 2,450.00 10
7788 SCOTT 7566 3,000.00 20
7839 KING 5,000.00 10
7844 TURNER 7698 1,500.00 30
7876 ADAMS 7788 1,100.00 20
7900 JAMES 7698 950.00 30
7902 FORD 7566 3,000.00 20
7934 MILLER 7782 1,300.00 10
Restrict - Project:
Select Empno,Mgr,Deptno from Emp where sal > 2000;
Restrict - Project:
Select Empno,Mgr,Deptno from Emp where Sal > 2000
and Sal < 3000;
Empno Ename Mgr Sal Deptno
7369 SMITH 7902 800.00 20
7499 ALLEN 7698 1,600.00 30
7521 WARD 7698 1,250.00 30
7566 JONES 7839 2,975.00 20
7654 MARTIN 7698 1,250.00 30
7698 BLAKE 7839 2,850.00 30
7782 CLARK 7839 2,450.00 10
7788 SCOTT 7566 3,000.00 20
7839 KING 5,000.00 10
7844 TURNER 7698 1,500.00 30
7876 ADAMS 7788 1,100.00 20
7900 JAMES 7698 950.00 30
7902 FORD 7566 3,000.00 20
7934 MILLER 7782 1,300.00 10
Some queries to write:

http://www.cems.uwe.ac.uk/~cjwallac/sql/mysq
l/queryemp.php

List the names of the employees whose
manager is 7698
List the empnos of the employees in
Department no 20 whose salary is over $2500


Empno Mgr Deptno
7566 7839 20
7698 7839 30
7782 7839 10
Deptno Dname Location
10 Accounting New York
20 Research Dallas
30 Sales Chicago
40 Operations Boston
Product:
Select * from EmpX , Dept;
Empno Mgr Deptno Deptno Dname Location
7566 7839 20 10 Accounting New York
7566 7839 20 20 Research Dallas
7566 7839 20 30 Sales Chicago
7566 7839 20 40 Operations Boston
7698 7839 30 10 Accounting New York
7698 7839 30 20 Research Dallas
7698 7839 30 30 Sales Chicago
7698 7839 30 40 Operations Boston
7782 7839 10 10 Accounting New York
7782 7839 10 20 Research Dallas
7782 7839 10 30 Sales Chicago
7782 7839 10 40 Operations Boston
Product :
DEPT has 4 records
EMPX has 3 records

so DEPT x EMPX has 12 records

but not very useful

Empno Mgr Deptno Deptno Dname Location
7566 7839 20 10 Accounting New York
7566 7839 20 20 Research Dallas
7566 7839 20 30 Sales Chicago
7566 7839 20 40 Operations Boston
7698 7839 30 10 Accounting New York
7698 7839 30 20 Research Dallas
7698 7839 30 30 Sales Chicago
7698 7839 30 40 Operations Boston
7782 7839 10 10 Accounting New York
7782 7839 10 20 Research Dallas
7782 7839 10 30 Sales Chicago
7782 7839 10 40 Operations Boston
Product Project - Restrict
Select * from EmpX ,Dept where Emp.Deptno=Dept.Deptno;

Empno Mgr Deptno Deptno Dname Location
7566 7839 20 10 Accounting New York
7566 7839 20 20 Research Dallas
7566 7839 20 30 Sales Chicago
7566 7839 20 40 Operations Boston
7698 7839 30 10 Accounting New York
7698 7839 30 20 Research Dallas
7698 7839 30 30 Sales Chicago
7698 7839 30 40 Operations Boston
7782 7839 10 10 Accounting New York
7782 7839 10 20 Research Dallas
7782 7839 10 30 Sales Chicago
7782 7839 10 40 Operations Boston
Product Project - Restrict
Select * from EmpX natural join Dept;
Restrict Project Product Restrict Project :
Select Empno,Mgr,Deptno,Dname
from Emp Natural Join Dept
where Sal > 2000 and Sal < 3000;
Join queries
List the names of all staff in department
number 10
SQL Functions
vary with RDBMS
STRINGS
LIKE, CONCAT, SUBSTR
DATE
SYSDATE..
STATISTICAL FUNCTIONS
COUNT, AVG, MIN, MAX, SUM
GENERATE AGGREGATE VALUES
SELECT SUM(sal) FROM emp;
shows total salary over all emps
Sorting Rows (tuples)
Select ename, sal from emp order by sal;
Grouping Rows
Select deptno, count(*) from emp group by
deptno;
Limiting the number of Rows
Select ename, sal from emp order by sal
desc limit 1;
RDMS
Relational Database Management System
Maps Relations and values into the Physical
Layer
Interprets SQL statements and executes the
requested updates on the physical data
Controls access to data, recovery from
errors..
MySQL
Free pay for support
Command line interface or use PHPMyAdmin
Installed for student use on shares usual Unix login
Personal copy easily be installed
Multiple databases can be created you get just one
Several different file systems for physical storage
(ISAM, INNODB)
ISAM
Three files per table
definition (schema) .FRM
Data .MYD
Index .MYI
What would you expect to see at the physical layer?
Department Table data file
ASCII
String
length
Record
header
Dept no
Big or Little
Endian?
Learning SQL
Workbook
MySQL, Oracle and MS Access
We will be using MySQL with PHP
http://www.cems.uwe.ac.uk/~cjwallac/sql/mysql
SQL/PHP Textbook
Chapter 10 of Welling and Thomson