A data model is a model that describes in an abstract way how data are represented in a business organization, an information system or a database management system - Wikipedia

[Section notes PDF 310Kb].

- Relational model
- Abstract operations on relations
- Set theoretic operations
- Relational-specific operations
- Basic algebra operations
- Union, Intersection, Difference
- Cross product

- Table as relation
- Row as tuple
- real world entity or relationship
- fact
- Column as attribute
- Domain

The concept of a relation is abstract, therefore we have a number of different ways of visualising it.

- Relation schema R(A
_{1}, A_{2}, A_{3}.. A_{n}) - Design side
- Assertion/declaration
- Relation state
- Data side
- set of n-tuples
- each one an ordered list of values
- 1NF: each value is atomic, no composite/multivalue

- Database lifecycle
- design, populate, evolve
- Insert
- tuple (a
_{1},a_{2},a_{3}…a_{n}) - Delete
- tuple (a
_{1},a_{2},a_{3}…a_{n}) - Update (or modify)
- tuple (a
_{1},a_{2},a_{3}…a_{n}) - attribute to change, new value

All the operations described in the next few sections are abstract. We're going to see how valuable they can be in processing real world data later.

- Two categories
- Set theoretic operations
- Union, Intersection etc.
- Relational specific
- Select, project and join

At this stage we're talking about set theoretical operators on the Relational model, not SQL instructions which confusingly have identical names and only similar behaviour.

- SELECT a subset of tuples from a relation
- Uses selection condition
- Evaluate each tuple to true of false
- False tuples discarded
- Sigma (s)
- output = s
_{(cond)}(input_relation) - Relation schema: R(output) = R(input_relation)
- Commutative

- PROJECT a subset of attributes for all tuples from a relation
- Pi (p)
- p
_{<attribute list>}(R) - If sublist is only non-key attributes
- might get duplicates
- Removes duplicates
- Attribute list:sublist example

The result set of the operation is itself a relational. That output relation will contain the same number of rows as the input, however it may contain a different number of columns; fewer if a subset of attributes is projected; more if derived or aggregated attributes are included.

- Select followed by projection
- Area clipping: rows then columns
- p
_{<attr list>}

(s_{(select_cond)}(R)) - Rename operation (r)
- Renames attributes list2 from list1
- r
_{(new_attr_names)}(R)

- Attribute renaming only
- Cannot alter domain, or add/remove attr
- Rename operation (r)
- Renames attributes list2 from list1
- r
_{(new_attr_names)}(R) - Implicit renaming
- Order dictated by relational schema

- Binary operation: two relations
- Sets of tuples
- Union compatibility (same attributes)
- Union (R u S)
- Intersection (R n S)
- Commutative (R u (S u T) = (R u S) u T)

- Set difference
- Non-commutative (R-S != S-R)

- Cartesian product of two relations
- R x S
- Also known as
- Cross product
- Cross join
- Cross product diagram
- Introduction to complexity
- Computationally explosive

- Relational schema R(A
_{1}, A_{2},…,A_{n}) - Relation state r or r(R)
- Set of unordered tuples
- r = {t
_{1}, t_{2},…,t_{n}} - Each n-tuple is an ordered list of values
- t = <v
_{1}, v_{2},…,v_{n}> - i
^{th}value in t = v_{i}called t[A_{i}] - r(R) subset of (dom(A
_{1}) x dom(A_{2})... x dom(A_{n}))

- Domain constraint
- For all v in t of r(R)

v_{i}is an element of dom(A_{i}) - Entity constraint
- K = SK
_{min} - t[K] != null
- Key constraint
- Superkey SK as identifying subset of attributes
- t
_{1}[SK] != t_{2}[SK]

- Given two relations R
_{1}and R_{2} - R
_{1}contains a foreign key (FK) that references - A primary key (PK) in R
_{2} - R
_{1}referencing relation, R_{2}referenced relation - Shared domains: dom(FK) = dom(PK)
- Foreign exists: t
_{1}in r(R_{1}), t_{2}in r(R_{2}) - t
_{1}[FK] = t_{2}[PK] || NULL