The minimum requirement for active attendance: 4 tasks done
1. Let us use again the company database.
a) Write an SQL query for printing the name of the department and the
number of employees for those departments where the average salary of
employees exceeds 30000.
b) How about to print the averages of male employees of those departments?
2. a) What superkeys and keys does a relation with only one row have?
b) Consider the relation
{(1,2,3,4,3), (1,2,3,5,4), (1,1,3,5,3), (1,1,3,5,4)\}
whose schema is ABCDE. What keys and superkeys there are in this
relation?
3. Consider a relation schema R(A,B,C) where the domains of the
attributes consist of positive integers. Give a relation of schema R
that has exactly three keys: AB, BC, and AC.
4. In a library database there are relations
BOOK(book_id, title, publisher_name),
BOOK_AUTHORS(book_id, author_name),
PUBLISHER(name, address, phone),
BOOK_COPIES(book_id, library_id, no_of_copies)
BOOK_LOANS(book_id, library_id, card_nr, date_out, due_date)
LIBRARY(library_id, library_name, address)
BORROWER(card_nr, name, address, phone).
What integrity constraints (key constraints, referential integrity
constraints) this database should have? Express the constraints using SQL2.
Express also the referential triggered actions concerning these
constraints. Which columns can contain a null value?
5. a) How is the natural join of relations r{ABC} and s{BCD} expressed
in SQL?
b) What can you say about the natural join for relations
of schemas R and S when R = S?
6. Consider a relation r of schema AB and the relational
expression
r - P (S (r * R (r))),
AB BltC BtoC
where * denotes natural join, R denotes the renaming operation, P
denotes projection, and S denotes selection. On the second line
('subindices') 'lt' means 'less than' and 'BtoC' means the renaming of
attribute B to attribute C.
a) Apply the expression to the relation
r = P (EMPLOYEE).
SEX, SALARY
b) Explain what is the result of the expression when r is arbitrary.
We assume that the domain of the attribute B in r is numeric.
7. Write the following queries against the Company database
in relational algebra:
a) the names of all employees who have a dependent with the first name
as themselves.
b) names of those departments which do not operate on every location
where there are some operations of the company. (How about: '... do
operate on every ...'?)