Database Systems Midterm Exam - Fall 2007, Exams of Deductive Database Systems

Download Database Systems Midterm Exam - Fall 2007 and more Exams Deductive Database Systems in PDF only on Docsity!

EEL 4852 Database Systems

Fall 2007

Midterm Exam

Oct 2, 2007

Time: 75 minutes

Your Name: ___________________________

USF ID: __U_________________________

Choose ONE from Problems I and V, you will NOT get credits for both

problems.

Problem No. Score

I

II

III

IV

V

Total

Problem II. (30pts, 5pts for each question)

Consider the following schemas of a corporate database:

Empolyees( eid : integer , ename : string , age : integer , salary : real ) Works( eid : integer , did : integer , pct_time : integer ) Departments( did : integer , location : string , managerid : integer )

where the underlined attributes are primary keys. We also have the following referential integrities: In table Works , eid is a foreign key to table Employess and did is a foreign key to table Departments ; In table Departments , managerid is a foreign key to table Employees (in other words, a manager is also an employee). Write the following queries in SQL. Clearly state any assumptions you make.

1. Find the names of all employees who are younger than 45 and with salaries within the range of [80000, 100000];
2. Find the ID(s) of the department(s) John Doe works. Also print the percentage of time John spends in each department (Hint: use the pct_time attribute in Works table);
3. Find the employee(s) with the highest salary;

4. Find the average salaries of employees with the same age, print out both age and average salary. (Hint, use group by , …)
5. Find the names of all employees who work for all departments located in Brooklyn;
6. Find the names of all employees who are older than his/her department manager.
7. Bonus question (5 pts): In the above questions, we apparently assume that one employee can work for multiple departments. Actually, the design of our schemas implicitly allows this to happen. Explain this. How can we modify our schema to disallow this?

4. Find the names of all employees who work in a Brooklyn department and a Manhattan department;
5. Find the information (did, location, and managerid) of all Brooklyn departments and copy the results to a new table named Brooklyn_Dept;
6. Find the names of all employees who work for all departments located in Brooklyn;

Problem IV. (20pts, 5pts each)

Consider the following two tables:

Relation r Relation s

B C D D E F a 5 b b 10 6 b 6 a c 25 3 c 25 b b 10 5 a 5 c

Give the resulting table of the following relational algebraic operations, you should specify the schema as well as the values of all attributes for all tuples in your solutions.

1. 𝜎𝐹>5(𝑟 × 𝑠)

2. 𝑟 ⋈𝑟.𝐶>𝑠.𝐸 𝑠 (hint: this is a theta join)

Problem V. (10pts.)

Following the schemas seen in Problem II and III, write the following queries in Query-By- Example (QBE). The skeleton tables are drawn for you.

1. (3pts) Print out the age and salary of an employee named John Doe;

Employees eid ename age salary

Works eid did pct_time

Departments did location managerid

2. (3pts) Find the names of all employees who work in a department that is NOT located in Brooklyn;

Employees eid ename age salary

Works eid did pct_time

Departments did location managerid

3. (4pts) Find the names of all employees who are older than his/her department manager.

Employees eid ename age salary

Works eid did pct_time

Departments did location managerid

Conditions