数据库系统概念 IV

练习

4.7

Consider the relational database of Figure 4.11.

Give an SQL DDL defifinition of this database.

Identify referential-integrity constraints that should hold,

and include them in the DDL defifinition.

考虑图4.11的关系数据库。

给出此数据库的SQL DDL定义。

确定应包含的引用完整性约束，

并将其包含在DDL定义中。

Figure 4.11 Employee database for Figure 4.7

employee ( employee name , street , city )

works ( employee name , company name , salary )

company ( company name , city )

manages ( employee name , manager name )

Answer

create table employee (person name char(20), 
street char(30), 
city char(30), 
primary key (person name) )

create table works (person name char(20), 
company name char(15), 
salary integer, 
primary key (person name),
foreign key (person name) references employee, 
foreign key (company name) references company)

create table company (company name char(15), 
city char(30),
primary key (company name))

create table manages (person name char(20), 
manager name char(20),
primary key (person name), 
foreign key (person name) references employee, 
foreign key (manager name) references employee)

4.12

For the database of Figure 4.11, write a query to fifind those employees with no manager.

Note that an employee may simply have no manager listed or may have a null manager.

Write your query using an outer join and then write it again using no outer join at all.

对于图4.11中的数据库，编写一个查询来查找那些没有经理的员工。

请注意，员工可能只是没有列出经理，或者经理为空。

使用外部联接编写查询，然后完全不使用外部联接再次编写查询。

Figure 4.11 Employee database for Figure 4.12

employee ( employee name , street , city )

works ( employee name , company name , salary )

company ( company name , city )

manages ( employee name , manager name )

Answer

select employee_name
from employee natural left outer join manages
where manager_name is NULL;

4.14

Show how to defifine a view tot credits ( year, num credits),

giving the total number of credits taken by students in each year.

演示如何定义视图tot credits（year，num credits），

给出学生每年修读的学分总数。

Answer 

create view tot_credits(year,num_credits)
as 
(select year,sum(credits)
from takes natural join course 
group by year)；

 5.8

Consider the bank database of Figure 5.25.

Write an SQL trigger to carry out the following action:

On delete of an account, for each owner of the account,

check if the owner has any remaining accounts,

and if she does not,

delete her from the depositor relation.

考虑图5.25中的银行数据库。

编写SQL触发器以执行以下操作：

在删除帐户时，对于帐户的每个所有者，检查所有者是否有任何剩余帐户，如果没有，则将其从存款人关系中删除。

 Figure 5.25 Banking database for Exercises 5.8

branch ( branch name , branch city, assets )

customer ( customer name , customer street, cust omer city )

loan ( loan number , branch name, amount )

borrower ( customer name , loan number )

account ( account number , branch name, balance )

depositor ( customer name , account number )

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

5.12

Consider the following relations for a company database:

• emp (ename, dname, salary)
• mgr (ename, mname)

and the Java code in Figure 5.26, which uses the JDBC API .

Assume that the userid, password, machine name, etc. are all okay.

Describe in concise  English what the Java program does.

(That is, produce an English sentence like “It finds the manager of the toy department,” not a line-by-line description of what each Java statement does.)

考虑公司数据库的以下关系：

emp（姓名、数据名、薪水）

经理（ename，mname）

以及图5.26中使用JDBCAPI的Java代码。

假设用户ID、密码、机器名等都正常。

用简明的英语描述Java程序的功能。

（也就是说，生成一个类似“It finds the manager of the toy department”的英语句子，而不是逐行描述每个Java语句的功能。）

 Figure 5.26 Java code for Exercise 5.12

import java.sql.*;
public class Mystery
public static void main(String[] args)
{ {
try {
Connection con=null;
Class.forName("oracle.jdbc.driver.OracleDriver");
con=DriverManager.getConnection(
"jdbc:oracle:thin:star/X@//edgar.cse.lehigh.edu:1521/XE");
Statement s=con.createStatement();
String q;
String empName = "dog";
boolean more;
ResultSet result;
do {
q = "select mname from mgr where ename = ’" + empName + "’";
result = s.executeQuery(q);
more = result.next();
if (more)
empName = result.getString("mname");
{
System.out.println (empName);
} } while (more);
s.close();
con.close();
} catch(Exception e){e.printStackTrace();} }}

Answer

It prints out the manager of “dog.” 
that manager’s manager, etc.
until we reach a manager who has no manager 
(presumably, the CEO, 
who most certainly is a cat.) 
NOTE: if you try to run this, 
use your OWN Oracle ID and password, 
since Star,
crafty cat that she is, 
changes her password.

5.16

Rewrite the query in Section 5.2.1 that returns the name and budget of all departments with more than 12 instructors, using the with clause instead  of using a function call.

重写第5.2.1节中的查询，该查询使用with子句而不是函数调用，返回超过12名讲师的所有部门的名称和预算。  

 Section 5.2.1

select dept name, budget
from instructor
where dept count(dept name) > 12;

Answer 

with instr count (dept name, number) as
(select dept name, count (ID)
from instructor
group by dept name)
select dept name, budget
from department, instr count
where department.dept name = instr count.dept name
and number > 12