The Relational Data Model

The Relational Data Model • E.F Codd A Relational Data Model for Large Shared Data Banks (1970) • The basic model used by Access, Oracle, DB2 • 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

Relation Column (field) Relation Deptno Dname Loc 10 Accounting New York 20 Research Dallas 30 Sales Chicago 40 Operations Boston tuple general integer string domain 0<int<99 city name specific

Relations • 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. • The ordering of rows is not significant. • Each cell or column/row intersection in a relation should contain only a so-called atomic value.

Primary Keys, Foreign Keys and Domains Each relation must have a primary key. This is to enforce the property that duplicate rows are forbidden in a relation. A primary key is one or more columns of a table whose values are used to uniquely identify each of the rows in a table. Foreign keys are the means of interconnecting the data stored in a series of disparate tables. A foreign key is a column or group of columns of some table which draws its values from the same domain as the primary key of some related table in the database. Domains are pools of values from which actual values appearing in the columns of a table are drawn. A special character is used in relational systems to indicate incomplete or unknown information - the character null. This character which is distinct from zero or space is particularly useful in the context of primary-foreign key links

Relational Algebra Define a new, derived relation as a sequence of operations on Relations Based on operational algebra Relational Calculus Defines a new, derived relation as a proposition which is true for every tuple in the new table Based on predicate calculus Two approaches to defining a derived relation

Algebra?? • Algebra: x + 6 = 10 - what’s x? • An Algebra: • a SET of values defined by INTENSION • e.g. whole numbers - 0,1,2,3,.. • OPERATORS which combine values to compute new values • e.g. + * - / - (4+5)*6 = 120 • Operators can only produce values in the original set - ‘closure’

Is it an Algebra?? • Whole numbers and + - * / • 5 - 3 = 2 but 3 - 5 = -2 • 6/3 = 2 but 6/4 = 1.5 • So not an algebra • can redefine / as remainder • e.g 6/4 = 1 - then / OK • Integers ( +ve and -ve) and + - * • 8 - 9 = -1 and -8 - -9 = 1 • 3 * 4 = 12 and 3 * -4 = -12

More Algebra • Operators act on different number of values • 1 unary - • 2 - + * / • Order may matter • 2 + 3 = 3 + 2 • but 6/3 not = 3/6 • Brain teazer - make 13 from 2,3,5,7 • ( (3 * 7) + 5 ) /2 = 13

Relational Algebra • Base operations • RESTRICT, PROJECT, PRODUCT • Convenience operations • EQUI-JOIN, (Natural) JOIN, Outer Joins • Set operations • UNION, INTERSECTION, DIFFERENCE, DIVISION

The Relational Algebra Restrict Union Project Intersect Join Difference Division

EMP-DEPT example Three relations: Department : DEPT Employee : EMP Salary Grade : SALGRADE

DEPT Table

EMP - table ( reduced) 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

Salgrade Table Grade Losal Hisal 1 £700.00 £1,200.00 2 £1,201.00 £1,400.00 3 £1,401.00 £2,000.00 4 £2,001.00 £3,000.00 5 £3,001.00 £99,999.00

Restrict Subset of the Rows in a Table • RESTRICT EMP WHERE sal > 2000 empno ename mgr sal deptno 7566 JONES 7839 £2,975.00 20 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 7902 FORD 7566 £3,000.00 20

Project: subset the Columns in a Table Empno sal Deptno 7369 £800.00 20 7499 £1,600.00 30 7521 £1,250.00 30 7566 £2,975.00 20 7654 £1,250.00 30 7698 £2,850.00 30 7782 £2,450.00 10 7788 £3,000.00 20 7839 £5,000.00 10 7844 £1,500.00 30 7876 £1,100.00 20 7900 £950.00 30 7902 £3,000.00 20 7934 £1,300.00 10 • PROJECT EMP • [EMPNO, SAL,DEPTNO]

Restrict-Project RESTRICT EMP WHERE SAL >2000 PROJECT EMP[EMPNO, SAL, DEPTNO] empno sal deptno 7566 £2,975.00 20 7698 £2,850.00 30 7782 £2,450.00 10 7788 £3,000.00 20 7839 £5,000.00 10 7902 £3,000.00 20 call this EMPX Could you reverse these operations - always? ( project then restrict?)

Product:Combine each row of one table with each row of the other: PRODUCT DEPT with EMPX empno sal EMPX. DEPT. Dname Loc Deptno Depno 7566 £2,975.00 20 10 Accounting New York 7698 £2,850.00 30 10 Accounting New York 7782 £2,450.00 10 10 Accounting New York 7788 £3,000.00 20 10 Accounting New York 7839 £5,000.00 10 10 Accounting New York 7902 £3,000.00 20 10 Accounting New York 7566 £2,975.00 20 20 Research Dallas 7698 £2,850.00 30 20 Research Dallas 7782 £2,450.00 10 20 Research Dallas 7788 £3,000.00 20 20 Research Dallas 7839 £5,000.00 10 20 Research Dallas 7902 £3,000.00 20 20 Research Dallas

7566 £2,975.00 20 30 Sales Chicago 7698 £2,850.00 30 30 Sales Chicago 7782 £2,450.00 10 30 Sales Chicago 7788 £3,000.00 20 30 Sales Chicago 7839 £5,000.00 10 30 Sales Chicago 7902 £3,000.00 20 30 Sales Chicago 7566 £2,975.00 20 40 Operations Boston 7698 £2,850.00 30 40 Operations Boston 7782 £2,450.00 10 40 Operations Boston 7788 £3,000.00 20 40 Operations Boston 7839 £5,000.00 10 40 Operations Boston 7902 £3,000.00 20 40 Operations Boston

Product : • DEPT has 4 records • EMPX has 6 records • so DEPT x EMPX has 24 records • but not very useful

Equi-Join :Product restricted to rows which have matching common domain Empno sal EMPX. Dept. Dname Loc Deptno Deptno 7566 £2,975.00 20 20 Research Dallas 7698 £2,850.00 30 30 Sales Chicago 7782 £2,450.00 10 10 Accounting New York 7788 £3,000.00 20 20 Research Dallas 7839 £5,000.00 10 10 Accounting New York 7902 £3,000.00 20 20 Research Dallas

Natural Join:Equi-join projected with the duplicate column removed empno sal deptno dname loc 7566 £2,975.00 20 Research Dallas 7698 £2,850.00 30 Sales Chicago 7782 £2,450.00 10 Accounting New York 7788 £3,000.00 20 Research Dallas 7839 £5,000.00 10 Accounting New York 7902 £3,000.00 20 Research Dallas

Basic SQL • SELECT * FROM EMP WHERE SAL > 2000; • SELECT ENAME,SAL,DEPTNO FROM EMP; • SELECT ENAME,SAL,DEPTNO FROM EMP WHERE SAL > 2000; • SELECT * FROM EMP, DEPT WHERE SAL > 2000; • SELECT * FROM EMP,DEPT WHERE SAL > 2000 AND EMP.DEPTNO = DEPT.DEPTNO; • SELECT EMPNO, SAL, DEPTNO, DNAME FROM EMP,DEPT WHERE SAL > 2000 AND EMP.DEPTNO = DEPT.DEPTNO;

The Relational Data Model