The relational data model and relational algebra

1
The relational data model and relational algebra

• E. Milios

2
Example

• Company database employees, departments and
  projects
• A department controls a number of projects
• Employee records
• Keep track of hours per project
• Dependents for insurance purposes

3
What is relational algebra?

• A set of operations that manipulate relations in
  the relational model.
• A relation is a subset of the Cartesian product
  of n sets.Example A1,2,3, Ba,b,
  n2AxB(1,a),(1,b),(2,a),(2,b),(3,a),(3,b)a
  relation (1,a),(2,b),(3,a)
• A table is a relation over the cartesian product
  of the domains of the attributes

4
Relational model concepts

• Relation a table
• Tuple table Row
• Attribute table Column (header)
• Domain type of values in a column

5
Relation schema

• Relation schema R(A1, A2, , An)
• R name
• Ai attributes
• Dom(Ai) domain of Ai
• ExamplesEMPLOYEE(Name, SIN, Bdate, HomePhone,
  Address, Salary, DNO, SupervisorSIN)Department(DN
  ame, DNumber, ManagerSIN, ManagerStartDate)

6
Relation instance

• Relation instance r(R) of the relation schema
  R(A1, A2, , An) is a set of tuplesr t1, t2,
  , tm
• Each tuple is an ordered set of valuest lt v1,
  v2, , vngt
• Each vi is a value from dom(Ai) or null (unknown)
  (domain constraint)
• No two tuples are the same (set)

7
Keys

• Superkey of R subset of attributes such that no
  two tuples in any relation instance r of R have
  the same combination of values
• Key of R a minimal superkey
• Example for EMPLOYEE
• A superkey SIN, Name, BDate
• A key SIN
• Key constraints
• Possible to have more than one key
• One key is designated as primary

8
Integrity constraints

• Relational database consists of many relations
• Tuples in different relations are related in
  various ways
• Relational database schema
• A set of relation schemas S R1, , Rm
• A set of integrity constraints IC

9
Integrity constraints

• Domain constraintsAttribute values must belong
  to the domain of the attribute
• Key constraints tuples can be identified by
  certain attributes (keys)
• Entity integrity constraint no primary key can
  be null
• Referential integrity constraint a tuple in one
  relation that refers to another relation must
  refer to an existing tuple in that relation
• Semantic integrity constraints

10
Referential integrity constraint

• A set of attributes FK in relation schema R1 is a
  foreign key of R1 if it satisfies
• The attributes in FK have the same domain as the
  primary key attributes PK of another relation
  schema R2 (FK refers to R2)
• A value of FK in tuple t1 of R1 either occurs as
  a value of PK for some tuple t2 in R2 (t1 refers
  to t2) or is null
• Constraint Foreign key of a tuple in R1 must
  match the primary key of some tuple in R2.
• Example in the employee relation, DeptNo in an
  employee tuple must match a value of the primary
  key DNO of a department tuple.

11
Example schema R1
EMPLOYEE
NAME
SUPERSIN
SIN
DNO
BDATE
ADDRESS
SALARY
HOMEPH
12
Example schema R2
EMPLOYEE
NAME
SUPERSIN
SIN
DNO
BDATE
ADDRESS
SALARY
HOMEPH
DEPARTMENT
DNAME
DNUMBER
MGRSIN
MGRSTARTDATE
13
Example schema Referential Integrity
Constraints
Within a relation
EMPLOYEE
NAME
SUPERSIN
SIN
DNO
BDATE
ADDRESS
SALARY
HOMEPH
Between relations
DEPARTMENT
DNAME
DNUMBER
MGRSIN
MGRSTARTDATE
14
Semantic Integrity Constraints

• Examples
• The salary of an employee should not exceed the
  salary of his/her supervisor
• The maximum number of hours an employee can work
  on all projects per week is 56
• Not enforced by standard DBMS
• Mechanisms for specifying and enforcing are being
  developed (e.g. triggers in MySQL)

15
Update operations on relations

• Insert provide list of attribute values for a
  new tuple
• Key constraints may be violated (how?key already
  exists in another tuple)
• Entity integrity may be violated (how?primary key
  is null)
• Referential integrity may be violated (how?FK
  refers to nonexistent tuple)
• Delete
• Can violate referential integrity (how?tuple
  being deleted is referenced by foreign key of
  another relation)
• Modify Change values of one or more attributes
  in a tuple
• Modifying neither primary nor foreign key is OK
• Modifying primary key is like deleting and
  inserting
• Modifying foreign key new value refers to
  existing tuple

16
Relational algebra

• Operations to manipulate entire relations
• Set operations (on relation as set of tuples)
• UNION
• INTERSECTION
• DIFFERENCE
• PRODUCT
• DB-specific operations
• SELECT
• PROJECT
• JOIN

17
Set theoretic operations

• UNION of R and S includes all tuples either in
  R or S or both. Duplicates eliminated.
• INTERSECTION of R and Sincludes all tuples in
  both R and S
• DIFFERENCE R - Sall tuples in R that are not
  in S
• PRODUCT R x Ssupertuples combining tuples from
  R with tuples from S

18
PRODUCT

• New table has all fields from both R and S
• Each row in the new table is the concatenation of
  a tuple from R with a tuple from S
• Questions
• If R has 25 rows and S 10 rows, how many rows
  does RxS have?
• Are all rows of RxS meaningful in a given context?

19
SELECT

• Select a subset of tuples (rows) in a relation
  that satisfy a condition
• Selection condition applied independently to each
  tuple of the relation
• Selected tuples form the result
• Applies to a single relation
• Applies to each tuple individually
• In view of relation as table, SELECT selects
  some rows
• SQL SELECT FROM Table WHERE Test

20
PROJECT

• In relation as table, PROJECT selects some
  columns
• Duplicate elimination (result must be a set)
• If projection includes a key, result has same
  number of tuples
• SQL PROJECT Field_List FROM Table

21
JOIN

• Combine related tuples from two relations R and S
  into a single tuple
• Like the product, but selective
• Join condition
• Ai ? Bj, Ai, Bj attributes of R and S
• ? is a comparison operation , lt, , gt, , ?
• only tuples satisfying it are combined.
• Most common condition is equality

22
Example

• Retrieve name of manager of each department

23
Name
Dob
Sin
Num
Addr
Loc
WORKS_FOR
Name
?
1
Slry
DEPARTMENT
EMPLOYEE
No of employees
StartDate
MANAGES
1
1
supervisor
1
supervisee
CONTROLS
SUPERVISION
1
1
N
WORKS_ON
M
DEPENDENT_OF
?
?
PROJECT
Rln
?
Dob
Hrs
Name
Loc
DEPENDENT
Num
Name
24
Example

• Retrieve name of manager of each department
• Join department and employee relations
• Project onto employee name / department name

25
What is SQL?

• A practical implementation approximating the
  (ideal) relational algebra operations

26
ER-to-relational mapping

• Idea map ER components to relational components
• 1. Entity type E ? relation R with simple
  attributes of E

27
Name
Dob
Sin
Num
Addr
Loc
WORKS_FOR
Name
?
1
Slry
DEPARTMENT
EMPLOYEE
No of employees
StartDate
MANAGES
1
1
1
supervisee
CONTROLS
SUPERVISION
1
N
WORKS_ON
M
DEPENDENT_OF
?
?
PROJECT
Rln
?
Dob
Hrs
Name
Loc
DEPENDENT
Num
Name
28
ER-to-relational mapping

• 2. Weak entity type W ? relation R
• As foreign key, the primary key of owner entity
  type
• primary key of R is combination of
• partial key of W and
• primary key of owner entity type

29
Name
Dob
Sin
Num
Addr
Loc
WORKS_FOR
Name
?
1
Slry
DEPARTMENT
EMPLOYEE
No of employees
StartDate
MANAGES
1
1
supervisor
1
supervisee
CONTROLS
SUPERVISION
1
N
WORKS_ON
M
DEPENDENT_OF
?
?
PROJECT
Rln
?
Dob
Hrs
Name
Loc
DEPENDENT
Num
Name
30
ER-to-relational mapping

• 3. 11 relationship type R in ER diagram
• Identify entity types S and T in R
• Choose S and include as foreign key the primary
  key of T
• Include all simple attributes of R as attributes
  of S

31
Name
Dob
Sin
Num
Addr
Loc
WORKS_FOR
Name
?
1
Slry
DEPARTMENT
EMPLOYEE
No of employees
StartDate
MANAGES
1
1
supervisor
1
supervisee
CONTROLS
SUPERVISION
1
N
WORKS_ON
M
DEPENDENT_OF
?
?
PROJECT
Rln
?
Dob
Hrs
Name
Loc
DEPENDENT
Num
Name
32
ER-to-relational mapping

• 4. 1N relationship type R in ER diagram
• Include as foreign key in S the primary key of T
• entity types S, T on the N, 1 side of R resp.

33
Name
Dob
Sin
Num
Addr
Loc
WORKS_FOR
Name
?
1
Slry
DEPARTMENT
EMPLOYEE
No of employees
StartDate
MANAGES
1
1
supervisor
1
supervisee
CONTROLS
SUPERVISION
1
N
WORKS_ON
M
DEPENDENT_OF
?
?
PROJECT
Rln
?
Dob
Hrs
Name
Loc
DEPENDENT
Num
Name
34
ER-to-relational mapping

• 5. For each MN relationship type R, create a new
  relation S to represent R.
• Include as foreign key attributes in S the
  primary keys of the participating entity types
• Their combination forms the primary key of S

35
Name
Dob
Sin
Num
Addr
Loc
WORKS_FOR
Name
?
1
Slry
DEPARTMENT
EMPLOYEE
No of employees
StartDate
MANAGES
1
1
supervisor
1
supervisee
CONTROLS
SUPERVISION
1
N
WORKS_ON
M
DEPENDENT_OF
?
?
PROJECT
Rln
?
Dob
Hrs
Name
Loc
DEPENDENT
Num
Name
36
ER-to-relational mapping

• 6. For multivalued attribute A, create relation
  Rincluding
• An attribute corresponding to A
• Primary key attribute K of the relation
  representing the ER type that has A as an
  attribute
• Example relation DEPT_LOCATIONSattribute
  DLOCATION (multivalued)attribute DNUMBER
  (foreign key)separate tuple DNUMBER,DLOCATION
  for each location

37
Name
Dob
Sin
Num
Addr
Loc
WORKS_FOR
Name
?
1
Slry
DEPARTMENT
EMPLOYEE
No of employees
StartDate
MANAGES
1
1
supervisor
1
supervisee
CONTROLS
SUPERVISION
1
N
WORKS_ON
M
DEPENDENT_OF
?
?
PROJECT
Rln
?
Dob
Hrs
Name
Loc
DEPENDENT
Num
Name
38
ER-to-relational mapping

• 7. For an n-ary relationship type R, ngt2, create
  a new relation S to represent R.
• Foreign key attributes in Sthe primary keys of
  the participating entity types.
• Primary key of Sthe combination of all foreign
  keys.

39
Operator precedence in SQL

• In SELECT WHERE ltconditiongt
• ltconditiongt is a boolean expression involving
  constraints on attributes specifying the rows to
  be selected.
• Boolean expressions are a combination of logical
  and numerical tests.Example (a gt 10) AND (b
  lt2) OR (c5)
• Boolean expressions can get very complex
• MySQL optimizes processing of WHERE clauses.

40
Operator precedence in SQL

• OR
• AND
• NOT
• BETWEEN, CASE, WHEN, THEN, ELSE
• , ltgt, gt, gt, lt, lt, ltgt, !, IS, LIKE, IN
• -,
• , /, DIV, , MOD
• BINARY, COLLATE
• Parentheses are allowed to make arithmetic and
  logical expressions clearer
• Comparison operations result in 1(True),
  0(False), NULL
• If any of the operands is NULL, then result is
  NULL
• To compare different types, use CONVERT