Use the following relations to answer the questions below using relational algeb
ID: 3745860 • Letter: U
Question
Use the following relations to answer the questions below using relational algebra.
Branch
Staff
2. Give the relational algebra statement that returns a count of how many staff work in each city. Also give the relation that will be the output of your statement.
3. Give the relational algebra statement that returns the branchNo, street, and city of all branches that have no staff working at it. Also give the relation that will be the output of your statement.
4. Give the relational algebra statement that combines these two relations into one, with no duplicate rows or columns. You can NOT use the JOIN operation, only primitives. There are multiple ways to solve. Also give the relation that will be the output of your statement.
branchNo street city postcode B005 22 Deer Rd London SW1 4EW B007 16 Argyll St Aberdeen AB2 3SU B003 163 Main St Glasgow G11 9QX B004 32 Manse Rd Bristol BS99 1NZ B002 56 Clover Dr London NW10 6EUExplanation / Answer
follwing are basic relational algebra operators
1)Selection ()----------->It is used to select certain rows from relation that satisfy certain criteria
2)Natural Join ()------------> It join tuples of a relations .It joins them along the same attribute.
3)Projection ()------------->It selects only certain attributes from a relation to display
4)union(U)-------------> It selects tuples that are present in relation "a" or in relation "b" or both.
Question:Give the relational algebra statement that returns first and last names of all staff and which city they work in. Also give the relation that will be the output of your statement.
answer
fName,lName,city (Staff Branch)
The explaination of above statement:
(Staff Branch) : joins two relation along the comon attribute BranchNo
fName,lName,city : It displays only attributes fName,lName,city of relation obtained as a reuslt of statement (Staff Branch)
Ouput relation obtained:
fName lName city
John White London
Ann Beech Glasgow
David Ford Glasgow
Mary Howe Aberdeen
Susan Brand Glasgow
Julie Lee London