Pure language
Define a new relation whose features are defined over some variables.
Variables are tuples in this format:
{ t | COND(t) }
Any tuple that satisfy this condition will be a member of the result.
{t | Employee(t) AND t.salary > 50K}
Question: Find employees whose salaries are more than 50K.
{ t.Fname, t.SSN | Employee(t) AND t.salary > 50K}
Question 1: Find the names and SSN of the employees whose salaries are more than 50K.
{t.Fname, t.address | Employee(t) AND Эd, Department(d) AND d.Dname = 'Research' AND
t.Dno = d.Dnumber}
Question 2: Find the Fnames and address of all employees who work for the Research
department.
SQL = a real-world t.r.c
○
Tuple R.C
1.
Variables are defined as columns
{x1x2 ...xn | COND (x1, … xn, xn+1, …xm)}
Generic Format:
{Z5, Z6| Э(Z1), Э(Z2), Э(Z3), Э(Z4), Э( …..ЭZ10), Employee (Z1, Z2, Z3, Z4 …. Z10) AND Z1= 'John'}
Question 3: Find the b_date and address of employees whom Fname is 'John'
Query -By- Example is an implementation of d.r.c.
○
Domain r.c.
2.
SQL is an implementation of the tuple relation calculus.
Employees(Eid, ename, age, salary)
Works (eid, did, pct_time)
Departments (did, location, manager_id)
∏E.name [σE.age < 45 ^ E.salary >= 80K ^ E.salary >= 100K (E)]
Find the names of all employees who are younger than 45 and with salaries in the range [80000,
100000].
a.
Find the 10s and the pct_time of the department John Doe works.
b.
∏e.did, pct_time [σename = 'John Doe' E W]
∏mdid, pct_time [σename = 'John Doe' E W D]
Find manager_id and the pct_time of the department John Doe works.
c.
∏ename, age{ [σpct_time = 100(W)] E}
Find the names and age of all employees who work full time in one position(One department).
d.
Find the names of employees who works for a Brooklyn dept. and a Manhattan dept.
e.
Relational Algebra Examples:
Existential mark
Relational Calculus
Tuesday, September 16, 2008
5:00 PM
Databas e Systems Page 1
