C H A P T E R2
Exercises
2.4Describe the differences in meaning between the terms relation and relation schema.
Answer:A relation schema is a type definition,and a relation is an instance of that schema.For example,student(ss#,name)is a relation schema and
is a relation based on that schema.
2.5Consider the relational database of Figure2.35,where the primary keys are un-
derlined.Give an expression in the relational algebra to express each of the fol-lowing queries:
a.Find the names of all employees who work for First Bank Corporation.
b.Find the names and cities of residence of all employees who work for First
Bank Corporation.
c.Find the names,street address,and cities of residence of all employees who
work for First Bank Corporation and earn more than$10,000per annum.
d.Find the names of all employees in this database who live in the same city
as the company for which they work.
e.Assume the companies may be located in several cities.Find all companies
located in every city in which Small Bank Corporation is located.
Answer:
a.Πperson-name(σcompany-name=“First Bank Corporation”(works))
7
8Chapter2Relational Model
employee(person-name,street,city)
works(person-name,company-name,salary)
company(company-name,city)
manages(person-name,manager-name)
Figure2.35.Relational database for Exercises2.1,2.3and2.9.
b.Πperson-name,city(employee1
(σ
company-name=“First Bank Corporation”(works)))
c.Πperson-name,street,city
(σ
(company-name=“First Bank Corporation”∧salary>10000)
works1employee)
d.Πperson-name(employee1works1company)
e.Note:Small Bank Corporation will be included in each answer.
Πcompany-name(company÷
(Πcity(σ
company-name=“Small Bank Corporation”
(company))))
2.6Consider the relation of Figure2.20,which shows the result of the query“Find
the names of all customers who have a loan at the bank.”Rewrite the query to include not only the name,but also the city of residence for each customer.
Observe that now customer Jackson no longer appears in the result,even though Jackson does in fact have a loan from the bank.
a.Explain why Jackson does not appear in the result.
b.Suppose that you want Jackson to appear in the result.How would you
modify the database to achieve this effect?
c.Again,suppose that you want Jackson to appear in the result.Write a query
using an outer join that accomplishes this desire without your having to modify the database.
Answer:The rewritten query is
Πcustomer-name,customer-city,amount(borrower1loan1customer)
a.Although Jackson does have a loan,no address is given for Jackson in the
customer relation.Since no tuple in customer joins with the Jackson tuple of borrower,Jackson does not appear in the result.
b.The best solution is to insert Jackson’s address into the customer relation.If
the address is unknown,null values may be used.If the database system does not support nulls,a special value may be used(such as unknown)for Jackson’s street and city.The special value chosen must not be a plausible name for an actual city or street.
c.Πcustomer-name,customer-city,amount((borrower1loan)1customer)
2.7Consider the relational database of Figure2.35.Give an expression in the rela-
tional algebra for each request:
a.Give all employees of First Bank Corporation a10percent salary raise.
Exercises 9
b.Give all managers in this database a 10percent salary raise,unless the salary would be greater than $100,000.In such cases,give only a 3percent raise.
c.Delete all tuples in the works relation for employees of Small Bank Corpora-tion.
Answer:
a.works ←Πperson -name,company -name,1.1∗salary (σ(company -name =“First Bank Corporation”)(works ))
∪(works −σcompany -name =“First Bank Corporation”(works ))
b.The same situation arises here.As before,t 1,holds the tuples to be updated and t 2holds these tuples in their updated form.
t 1←Πworks.person -name,company -name,salary (σworks.person -name =manager -name (works ×manages ))
t 2←Πworks.person -name,company -name,salary ∗1.03(σt 1.salary ∗1.1>100000(t 1))
work
t 2←t 2∪(Πworks.person -name,company -name,salary ∗1.1(σt 1.salary ∗1.1≤100000(t 1)))
works ←(works −t 1)∪t 2
c.works ←works −σcompany −name =“Small Bank Corporation”(works )
2.8Using the bank example,write relational-algebra queries to find the accounts
held by more than two customers in the following ways:
a.Using an aggregate function.
b.Without using any aggregate functions.
Answer:
a.t 1←account -number G count customer -name (depositor )Πaccount -number  σnum -holders>2 ρaccount -holders (account -number,num -holders )(t 1)
b.t 1←(ρd 1(depositor )×ρd 2(depositor )×ρd 3(depositor ))t 2←σ(d 1.account -number =d 2.account -number =d 3.account -number )(t 1)Πd 1.account -number (σ(d 1.customer -name =d 2.customer -name ∧
d 2.customer -nam
e =d 3.customer -name ∧d 3.customer -name =d 1.customer -name )(t 2))
2.9Consider the relational database of Figure 2.35.Give a relational-algebra expres-
sion for each of the following queries:
a.Find the company with the most employees.
b.Find the company with the smallest payroll.
c.Find those companies whose employees earn a higher salary,on average,than the average salary at First Bank Corporation.Answer:
10Chapter 2Relational Model
a.t 1←company -name G count-distinct person -name (works )
t 2←max num -employees (ρcompany -strength (company -name,num -employees )(t 1))Πcompany -n
ame (ρt 3(company -name,num -employees )(t 1)1ρt 4(num -employees )(t 2))b.t 1←company -name G sum salary (works )t 2←min payroll (ρcompany -payroll (company -name,payroll )(t 1))Πcompany -name (ρt 3(company -name,payroll )(t 1)1ρt 4(payroll )(t 2))c.t 1←company -name G avg salary (works )t 2←σcompany -name =“First Bank Corporation”(t 1)
Πt 3pany -name ((ρt 3(company -name,avg -salary )(t 1))1t 3.avg -salary >first -bank.avg -salary (ρfirst -bank (company -name,avg -salary )(t 2)))
2.10List two reasons why null values might be introduced into the database.
Answer:Nulls may be introduced into the database because the actual value is either unknown or does not exist.For example,an employee whose address has changed and whose new address is not yet known should be retained with a null address.If employee tuples have a composite attribute dependents ,and a particular employee has no dependents,then that tuple’s dependents attribute should be given a null value.
2.11Consider the following relational schema
employee (empno ,name ,office ,age )
books (isbn ,title ,authors ,publishe r )
loan (empno ,isbn ,date )
Write the following queries in relational algebra.
a.Find the names of employees who have borrowed a book published by McGraw-Hill.
b.Find the names of employees who have borrowed all books published by
McGraw-Hill.
c.Find the names of employees who have borrowed more than five different books published by McGraw-Hill.
d.For each publisher,find the names of employees who have borrowed more
than five books of that publisher.
Answer:No answer
Exercises
3.8Consider the insurance database of Figure 3.11,where the primary keys are un-derlined.Construct the following SQL queries for this relational database.a.Find the number of accidents in which the cars belonging to “John Smith ”were involved.
b.Update the damage amount for the car with license number “AABB2000”in the accident with report number “AR2197”to $3000.
Answer:Note:The participated relation relates drivers,cars,and accidents.a.SQL query:
select
count (distinct *)from
accident where exists
(select *
from participated,person
where participated.driver id =person.driver id
and person.name =’John Smith’
port number =port number )
b.SQL query:update participated
set damage amount =3000
where report number =“AR2197”and driver id in
(select driver id
from owns
where license =“AABB2000”)
11C H A P T E R
3

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。