期中考试点评

期中考试点评 2005-11-25

外模式1 外模式2 DCL 1 EMP , 2 EMP# CHAR(6) 2 SAL FIXED BIN(31) 0 1 EMPC, 02 EMPNO PIC X(6) 02 DEPTNO PIC X(4) 概念模式 EMPLOYEE EMPLOYEE_NUMBER CHAR (6) DEPARTMENT_NUMBER CHAR (4) SALARY NUMERIC(5) 内模式 STORED_EMP BYTES=20 PREFIX TYPE=BYTE(6),OFFSET=0 EMP# TYPE=BYTE(6),OFFSET=6, INDEX=EMPX DEPT# TYPE=BYTE(4),OFFSET=12 PAY TYPE=FULLWORD,OFFSET=16 一、三级体系结构与数据独立性

二、1、2题 • 1、三元联系与二元联系 • 2、{A, C} ，{C，D}, {E} • 码与超码

二、3题 R S

三、3题 • 自然连接与等值连接

三、3 • 试证明如果R = R1  R2, 那么R1 = R  R2. • 证明：因 R = R1 X R2. 由卡氏积的定义： if a tuple r1R1, then for every tuple r2 in R2, r1r2 is in R. According to the definition of , we will have r1RR2. Thus,R1RR2. Using similar method we can also show that RR2R1. Therefore, R1 = R  R2 is TRUE!

三、3 • 另一种证明 • RR2 = πR-R2 ( R ) – πR-R2 ( (πR-R2 ( R) X R2 ) – R ) = πR-R2 (R1  R2 ) – πR-R2 ( (πR-R2 (R1  R2) X R2 ) – R1  R2 ) = πR-R2 (R1  R2 ) =R1

四、1.SQL • a. 找出与Smith有相同GPA的学生；（用非嵌套查询语句） • (i) 关系代数: student.Name (Student student.GPA=s2.GPA Name=‘Smith’ (ρs2(Student)) ) • SQL: select s1.cname from student s1, student s2 where s1.GPA = s2.GPA and s2.Name= ‘Smith'

四、1. • b.找出至少选修了一门CS系开设的课程的所有学生的姓名；（用非嵌套查询语句） • (i) 关系代数: student.Name (Student s.SSN = e.SSN Enrollmentstudent.GPA=s2.GPA c.Dept_Name = 'CS’(Course) ) • SQL: select Name from Students s, Enrollment e, Courses c where s.SSN = e.SSN and e.Course_no = c.Course_no and c.Dept_Name = 'CS’

四、1. • c.找出选修了Smith所选修的所有课程的学生的姓名和GPA • (i) 关系代数: student.Name,GPA (Student (Enrollment ÷Enrollment.Cno(EnrollmentName = ‘Smith’(Student) )

四、1. • SQL: • selectName, GPA from Students s • wherenot exists (select * from Enrollment e1 , Student s1 where SSN = s1.SSN and s1.name=‘Smith’ and not exists • (select * from Enrollment • where SSN = s.SSN and • Course_no = c.Course_no))

四、2. select s1.cname from student , where s1.GPA in ( select GPA from student where Name= ‘Smith‘)

四、2. select Name from Students s where exist (select SSN from Enrollment e, Courses c where s.SSN = e.SSN and e.Course_no = c.Course_no and c.Dept_Name = 'CS’)

四、3.(a) Create a view for top students without the age attribute. create view Top_Students as select SSN, Name, GPA from Students where GPA >= 3.8

五、1.(b) view_name text_length text --------------------------------------------------------------- Top_Students 35 select SSN, Name, GPA from Students where GPA >= 3.8

四、3.(c) Find the names and GPAs of those top students whose name starts with `K'. select Name, GPA from Top_Students where Name like 'K%'

Query Modification (2) ==> select Name, GPA from (select SSN, Name, GPA from Students where GPA >= 3.8) where Name like 'K%' ==> select Name, GPA from Students where GPA >= 3.8 and Name like 'K%'

四、3.(d) Insertion, deletion and update can be performed on simple views. • Simple view: created based on one table with no function, no order by, no distinct and no group by. • Complex view: defined using multiple tables, or using group by, order by, etc. Complex views are generally not updatable.

五 (a) Apair(key value and address) in the B+tree occupies 6+4 = 10 bytes Each page contain 2K/10=200 pairs There are 10,000 tuples in R, there will be 10,000 pairs So we need 10000/200+1 51 11 … 36 8 11 14 25 34 36 叶结点

五 • 2。 • R 有1000块，S 500 • 500+500/250 * 1000=2500 • 500+1000+(1000-1)=2499

五 • 3。 • R 有1000块，S 500 • 500+1000=1500

五 4。 R的索引有50+1 S的索引有25+1 50+25=75 因为没有返回结果。如果有增么计算？ 11 … 36 8 11 14 25 34 36 叶结点

期中成绩总结 95 分以上的同学：屈博，王宝龙，王仲远，张建梅，曹玉谨

作业和实验迟交 • 下面是未交作业名单： • 作业1：刘君阳穆大鹏朱颜国 • 作业2：穆大鹏朱颜国 • 作业3：穆大鹏 • 作业4：刘君阳穆大鹏张哲朱颜国 • 下面是各次未交实验名单： • 实验2：刘君阳穆大鹏汪宝成张秀芳李长展朱颜国王喜春唐滔张忠余 • 实验3：穆大鹏张哲张雪非朱颜国张余忠王仲远胡健康

期中考试点评

期中考试点评

Presentation Transcript