Last Lecture

1
Last Lecture

• The relational data model
• data structure
• a relation has attributes and tuples
• A relation has candidate keys (a set of
  attributes)
• data integrity
• primary key constraint
• referential integrity constraint /foreign key
  constraint

2
Relational Databases Relational Operators

• Database Systems Lecture 3

3
The Relational Algebra

• Normal algebra
• A set of operations (, -, , , etc.)
• Operators take numbers as input and return
  numbers as results
• Some operators have restrictions - you cant
  divide by 0

• Relational algebra - A set of operators
• Operators take one or two relations as input and
  return relations as results
• Select Returns the rows of a table that satisfy
  a given condition
• Project Returns the specified columns of a table
• Join Returns some rows from two tables that can
  be joined together

4
Select Operator

• Select is a unary operator - it takes a single
  relation
• Select chooses those tuples in the relation which
  satisfy some conditions - it selects certain rows
  of the table

5
Select Operator
Employee
Select Employee Where Salary gt 18,000
SQL Select From Employee Where Salary gt 18,000
6
Project Operator

• Project is a unary operator - it takes a single
  relation
• Project chooses some of the attributes of the
  relation, or columns of the table

7
Project Operator
Employee
Project Employee Over FName
SQL Select Fname From Employee
8
Product Operator

• Product is a binary operator - it takes two
  relations
• The tuples of the product A ? B are all tuples
  which can be formed by coalescing a tuple from A
  and a tuple from B
• Coalescing
• (a,b,c) and (x,y,z) become (a,b,c,x,y,z)

9
Product Operator
A a 123
B b xy
SQL Select From A, B
10
Natural Join (Inner Join)

• Natural join is a binary operator that allows to
  combine Product and Select operators into one
  operation
• The result of the natural join of A and B is the
  combinations of tuples a from A and b from B
  where certain conditions are satisfied

11
Natural Join (Inner Join)
Employee
Department
Emp Name EDept 1 John 2 2 Mary 1 3 Mark 3
Dept DName 1 Marketing 2 Sales
Employee Join Department On EDeptDept
Emp Name Edept Dept Dname 1 John 2 2 Sales 2 M
ary 1 1 Marketing
SQL Select From Employee, Department Where
EDeptDept
12
Outer Join

• Natural join can lose some tuples from one
  relation which do not have a match in the other
  relation
• Outer join may retain the tuples which would be
  lost by natural join
• Left outer join of A and B retains all of A
• Right outer join of A and B retains all of B

13
Outer Join
SQL Select From A Left Outer Join B On ac
14
Division Operator

• Division is a binary operator
• For A / B to be defined, the attributes of B must
  be a subset of the attributes of A
• The tuples of A / B are all tuples d, such that
  for EVERY b in B, the combination of d and b is a
  tuple in A

15
Division Operator
Course Student C1 S1 C2 S1 C2 S2 C1
S3 C2 S3 C3 S3
Enrolment / Student courses taken by ALL
students find all tuples of the table that are
related to each and every one of the tuples of a
second table.
16
Other Operators

• The union, A ? B, consists of all the tuples in A
  and all the tuples in B
• The intersection, A ? B, consists of those tuples
  that are in both A and B
• The difference, A ? B, consists of those tuples
  in A which are not in B
• These are binary operators - they take two
  relations, A and B, A and B must have the same
  set of attributes

17
Relational Operator Exercise
S
SP
P
18
Relational Operator Exercise

• Get supplier names for suppliers who supply part
  P2

tmp1 Select SP Where PP2 tmp2 S Join (tmp1)
On S.Stmp1.S Project (tmp2) Over SNAME Project
( S Join ( Select SP Where P P2 ) On S )
Over SNAME
19
Relational Operator Exercise

• Get supplier names for suppliers who supply at
  least one red part

tmp1Select P Where COLOUR Red tmp2(tmp1)
Join SP On P tmp3(tmp2) Join S On S Project
(tmp3) Over SNAME Project ((( Select P Where
COLOURRed) Join SP On P) Join S On S) Over
SNAME
20
This Lecture in Exam
21
This Lecture in Exams

• Give the two conditions that must be satisfied
  for a set of
• attributes to be a candidate key of a relation
  (2 marks)
• Given the data shown above, what are the
  candidate
• key(s) of the tables DVD, Stars, and Actor?
  (3 marks)
• Explain, with reference to the relations given,
  the term
• entity integrity. (2 marks)
• Explain, with reference to the relations given,
  the term
• referential integrity.
  (2 marks)

22
Next Lecture

• Entity/Relationship models
• Entities and Attributes
• Relationships
• Attributes
• E/R Diagrams
• For more information
• Connolly and Begg chapter 11