数据库第六版第四章答案4.doc

4.1

a.

select ID,name,count(year) as num_of_sections

from instructor natural left outer join teaches

group by ID,name;

b. select ID, name,

(select count(*) as num_of_sections

from teaches T where t.ID = I.ID)

from instructor I;

c. select course_id,section_id ,ID ,

decode(name,null,'-',name )

from (section natural left outer join teaches natural left outer join instructor)

natural left outer join instructor

where semester='Spring' and year='2010';

d. select dept_name ,count(ID)

from department natural left outer join instructor

group by dept_name;

4.2

a. select * from student natural join takes

union

select ID,name,dept_name,tot_cred,null,null,null,null,null

from student S1 where not exists

(select ID from takes T1 where T1.id = S1.id);

b. //

4.4

a. Consider that,if a teacher teaches ELec.Eng. in physics department,

but it will be lost in the natural join of instructor,teaches and course.because the instructor department name does not match the name of department.The example is :

instructor={(12345,'math','physics',10000)}

teaches={(12345,'EE123',1,'spring',2009)}

course={('EE123','Eng.','Elec.Eng.',6)}

b.Instructors who have not taught a signal course should have a number of sections

c. Consider the query:

select * from teaches natural join instructor.

in this query, some sections will be lost if teaches.id is allowed to be null,and thus tuples do exist. And if teaches.id is o foregin key to instructor ,such tuples will not be created and sush error won't be detected in the query above.

4.8

a.//

b.//

4.14

create view tot_credits(year,tot_credits)

as

(select year,sum(credits)

from takes natural join course

group by year)