2006~2007数据库原理

数据库原理复习专栏

• 复习计算属性闭包等几个算法，务必熟练！

选择题

1. C
2. D
3. D
4. A
5. B
   
   • 双箭头是圆箭头的意思
6. D
7. B
   
   • 下划虚线表示弱实体集的候选键
8. D
9. B
10. B
    
    • 参考《数据库系统实现》第三章，玛雅..
11. B 外关键字Foreign key
12. C 拥有reference权就可以了
13. B 除了默认拒绝更新，显式指定的只有CASCADE和SET NULL两种选项
14. B
15. D
16. A $\huge{}?$
    
    • 据测试，MySQL的列是从1开始数的？所以肯定是4列和6列啊？
17. C
18. B 下划线表示任意一个字符，百分号表示任意个字符
19. B
20. C
21. A
22. D 增加索引create index idx1 on tab(col)或alter table tab add index on (col)
23. D
24. C
25. D
26. x $B!$
27. B
28. D
29. B
30. B
31. C
32. D $C\huge{}?$
33. A $D\huge{}?$
34. D
35. D $A!$
36. D
37. 看看JDBC... $C\huge{}?$
38. ? 《数据库系统实现》第八章 $C!$
39. B D中应为X是一个超键
40. B $C \huge{}?$
    
    • insert into tab2 是打错了？

41. CD $\huge{}A!$
42. B
43. BC
44. BD
45. B $AB!$
46. BD $D!$
47. B $\huge{}CD?$
48. AB $CD?$
49. BC $\huge{}A?$
50. D $\huge{}ABD?$

简答题

1. $${\pi}_{sid}(Sailors)-{\pi}_{sid}\left({\rho}_{R_1}(Reserves)\\{\Join}_{{R_1}.sid={R_2}.sid\,and\,{R_1}.bid<>{R_2}.bid}\\{\rho}_{R_2}(Reserves)\right)$$

2. $$R_1=Sailors\Join Reserves\Join Boats$$ $$R_2={\sigma}_{color=red}\left(R_1\right)$$ $$R_3={\pi}_{sid}(R_2)$$ 结果为$\,R_3$。

3. • a) select sname from Sailors natural join Reserves natural join Boats where color = 'green' or color = 'red';
   • b) select rating from Sailors group by rating having avg(age) <= all (select avg(age) from Sailors group by rating);或select rating from (select rating, avg(age) as avg_age from Sailors group by rating) as gps where avg_age = (select min(avg_age) from gps);
   • c) ref: 关于双重NOT EXISTS的一个撕烤
     

     select sname from Sailors
     where not exists (
     select * from Boats
     where color = 'red' and not exists (
     select * from Reserves
     where Reserves.bid = Boats.bid and Reserves.sid = Sailors.sid
     ));

4. 
   

   create table work_in (
   ssn char(10) not null,
   did char(5) not null,
   from date not null,
   to date not null,
   constraint pk_work_in primary key (ssn, did, from);
   constraint fk_work_in_employees foreign key (ssn) references employees (ssn),
   constraint fk_work_in_departments foreign key (did) references departments (did)
   );
   create assertion ast check (
   (select count(ssn) from employees) = (select count(distinct ssn) from work_in)
   and
   (select count(did) from departments) = (select count(distinct did) from work_in)
   );

5. 啥？
6. 啥？