数据库原理 Exercises 3&4&5

Database System Concepts Exercises of Chapter 5&6&8

Database System Concepts Exercises of Chapter 5&6

Exercise 5.8 Consider the bank database of Figure 5.25. Write an sQL trigger to carryout the following action: On delete of an account, for each owner of theaccount, check if the owner has any remaining accounts, and if she doesnot, delete her from the depositor relation.

branch(branch_name, branch_city, assets)

customer ( customer_name, customer_street, customer_city )

loan( loan_number, branch_name, amount)

borrower ( customer_name, loan_number )

account ( account_number, branch_name, balance )

depositor ( customer_name, account_number )

Figure 5.25

My answer:

create trigger check-delete-trigger after delete on account 
referencing old row as orow
for each row
delete from depositor
where depositor.customer_name not in
   ( select customer_name from depositor 
    where account_number <> orow.account_number ) 
end

Exercise 5.15 Consider an employee database with two relations employee (\(\underline{employee\_name}\), street, city) works (\(\underline{employee\_name}\), company_name, salary) where the primary keys are underlined. Write a query to find companies whose employees earn a higher salary, on average, than the average salary at "First Bank Corporation". a. Using SQL functions as appropriate. b. Without using SQL functions.

My answer:

create function avg_salary(cname varchar(15))
return integer
     declare result integer;
select avg(salary) into result
from works
where works.company.name = cname
return result;
end
select company_name
from works
where avg_salary(company_name) > avg_salary(“First Bank Corporation”)

select company_name
from works
group by company_name
having avg(salary) > (select avg(salary)
								from works
where company_name=”First Bank Corporation”)

---

Exercise 6.1 Write the following queries in relational algebra, using the university schema. a. Find the titles of courses in the Comp. Sci. department that have 3 credits. b. Find the IDs of all students who were taught by an instructor named Einstein; make sure there are no duplicates in the result. c. Find the highest salary of any instructor. d. Find all instructors earning the highest salary (there may be more than one with the same salary).

My answer: