1.  ( TOTAL 23 POINTS )
1A. The most common mistakes in question 1A were:

• Not underlining the primary keys
• Incorrectly specifying the schemas for the sub classes.
• Not representing the manages+branch+allocated staff relations and entity types correctly. I have given full points for people who have assumed the existence of a branchid and have come up with appropriate schemas. Another way to represent this is have a manager_id associated with each staff member.

2.  ( TOTAL 24 POINTS )
 

 2A.

(step1) T1 <- Sigma                        (OFFERING)
                    semester='spring 2000'

(step2) T2 <-          GROUPING                (T1)
              Course#           COUNT section#

(step3) Result <- Rho                                  T2
                      (course#,No_of_sections_offered)

          ** Rho line is optional
 

 2B.

(step1) T1 <- Sigma                     (OFFERING)
                    semester='Fall 99'

(step2) T2 <- T1 * TEXT

(step3) T3 <- Sigma                           (BOOK)
                   publisher='Addison Wesley'

(step4) T4 <- T2  LEFT_OUTER_JOIN                          T3
                                  T2.BookISBN=T3.BookISBN

(step5) Result <- PI                           T4
                     section#,course#,BookISBN
 

  2C. Most of you missed the question which is asking you to identify
the books being used by both departments. Out of 35 three of you got this
completely correct and a handful had the right idea.


(step1) T1 <- Sigma         COURSE
                   Dept='CS'

        T2 <- TEXT * OFFERING * T1

(step2) T3 <- Sigma         COURSE
                   Dept='EE'

        T4 <- TEXT * OFFERING * T1

(step3) T5 <- Pi        T2
                BookISBN

        T6 <- Pi        T4
                BookISBN

(step4) T7 <- T5 INTERSECTION T6
 

3.  ( TOTAL 25 POINTS )

3A
select o.Section#, o.InstructorName, b.bookISBN
from TEXT t ,OFFERING o,  BOOK b
where b.BookISBN = t.BookISBN AND t.Section# = o.Section# AND
b.AuthorName= o.Instructor_name
 

3B
Create View  CSBOOKS
AS select o.Section # , o.Course #, o.InstructorName, t.BookISBN
from  TEXT t, OFFERING o, COURSE c,
where c.Dept = CS AND t.Section# = o.section# and c.Course# = o.Course#
AND  o.Semester = "Spring 2000"

No. In general, join views are considered NOT updatable.
Credit was given for Yes with a valid reason.
 

3C
select. s.Stud#, count(*)
from student s, enroll e, offering o
where o.Semester = "Spring 2000"  AND s.Major = "ISYE" AND s.stud# =
e.student#  AND e.section# = o.section #
Group BY stud#
Having count(*) > 4
 

4. ( TOTAL 18 POINTS )
 

   4A.
         { t.C_Name, t.Dept | COURSE(t) and
                ( Exist s )( OFFERING(s) and s.semester='spring 2000' and
                             s.course# = t.course# ) }
 

   4B.  Quantifier : Existential Quantifier
        Free tuple Variable : t
        Bound tuple Variable : s