www.cardiff.ac.uk/medic/irg-clinicalepidemiology
Relational algebra
Copyright By PowCoder代写 加微信 powcoder
Information modelling
& database systems
in the last lecture, we learnt how to create a table and declare different types of constraint using SQL
in this lecture, we will learn about relational algebra
relational algebra is a theoretical framework for expressing operations on relations and for studying how queries may be optimised
Relational algebra
a collection of operators that take relations as operands and return relations as results
the operations fall broadly into three classes:
set operations: UNION, INTERSECTION, DIFFERENCE
“selective” operations: RESTRICT, PROJECT
“merging” operations: CARTESIAN PRODUCT, JOIN
a query is an expression of these operations, so relational algebra, though in mathematics, may be seen as a “query language”
Set operations
intersection
difference
Selective operations
selection retrieves data horizontally (rows),
projection vertically (columns)
Merging operations
Sample tables
sno sname dob
123 A Adam 20/01/1970
234 B Bruce 11/04/1968
345 C Charles 12/02/1950
456 D Dexter 01/11/1975
eno ename dob
123 A Adam 20/01/1970
246 E Evans 11/11/1971
912 F Fox 04/10/1945
345 C Charles 12/02/1950
Set operations
Union compatibility between relations
such compatibility is necessary to carry out union, intersection and difference
the same degree, i.e. number of attributes
the corresponding attributes must have the
same domains
student (sno, sname, dob)
employee (eno, ename, dob)
sno sname dob
123 A Adam 20/01/1970
234 B Bruce 11/04/1968
345 C Charles 12/02/1950
456 D Dexter 01/11/1975
eno ename dob
123 A Adam 20/01/1970
246 E Evans 11/11/1971
912 F Fox 04/10/1945
345 C Charles 12/02/1950
sno sname dob
123 A Adam 20/01/1970
234 B Bruce 11/04/1968
345 C Charles 12/02/1950
456 D Dexter 01/11/1975
246 E Evans 11/11/1971
912 F Fox 04/10/1945
STUDENT UNION EMPLOYEE
no duplicates!
Intersection
sno sname dob
123 A Adam 20/01/1970
234 B Bruce 11/04/1968
345 C Charles 12/02/1950
456 D Dexter 01/11/1975
eno ename dob
123 A Adam 20/01/1970
246 E Evans 11/11/1971
912 F Fox 04/10/1945
345 C Charles 12/02/1950
sno sname dob
123 A Adam 20/01/1970
345 C Charles 12/02/1950
STUDENT INTERSECT EMPLOYEE
Difference
sno sname dob
123 A Adam 20/01/1970
234 B Bruce 11/04/1968
345 C Charles 12/02/1950
456 D Dexter 01/11/1975
eno ename dob
123 A Adam 20/01/1970
246 E Evans 11/11/1971
912 F Fox 04/10/1945
345 C Charles 12/02/1950
sno sname dob
234 B Bruce 11/04/1968
456 D Dexter 01/11/1975
STUDENT MINUS EMPLOYEE
Selective operations
selection C(R) is the set of all tuples from relation R that satisfy condition C
sname = ‘B Bruce’ (STUDENT) =
conditions are constructed from:
attributes, constants e.g. sname, ‘B Bruce’
comparison operators e.g. =
logical operators e.g. OR
sno sname dob
234 B Bruce 11/04/1968
Projection
projection X(R) is the set of all tuples from relation R, but only with a subset of attributes X
{sno, sname}(STUDENT) =
note: duplicates are removed!
selection retrieves data horizontally
projection retrieves data vertically
123 A Adam
234 B Bruce
345 C Charles
456 D Dexter
Merging operations
product R S is the set of all tuples that are formed as follows:
the first n components of R S from a tuple of R and the last m components form a tuple of S
if R has k tuples and S has h tuples, then R S will have k h tuples
A1 A2 … An
B1 B2 … Bm
A1 A2 … An B1 B2 … Bm
sno sname dob
123 A Adam 20/01/1970
234 B Bruce 11/04/1968
345 C Charles 12/02/1950
456 D Dexter 01/11/1975
STUDENT TIMES COURSE
sno sname dob cno cname
123 A Adam 20/01/1970 135 MATHS
123 A Adam 20/01/1970 246 ART
234 B Bruce 11/04/1968 135 MATHS
234 B Bruce 11/04/1968 246 ART
345 C Charles 12/02/1950 135 MATHS
345 C Charles 12/02/1950 246 ART
456 D Dexter 01/11/1975 135 MATHS
456 D Dexter 01/11/1975 246 ART
natural join R ⨝ S is the set of tuples that are formed as follows
each value of the join attribute of R is compared with every value of the join attribute of S
if they are equal, the two tuples are joined and appear in the result
A1 A2 … An
B1 B2 … Bm
A1 A2 … An B2 … Bm
join attributes
/docProps/thumbnail.jpeg
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com