CSE 670: Mid Term Exam
 
Open Notes, Open Books
The exam consists of $\underset{¯}{four}$ problems
Answers not appearing in designated spaces WILL NOT be graded

• Problem #1 (10 points) Draw an ER diagram for the following application of a database.
  1. A movie has a name and release date.

  2. A studio has a numeric id, name, and address

  3. A director has a SSN and a name

  4. An actor has a SSN and a name

  5. A character in a movie has a name and a role.

  6. Each movie is produced by a single studio.

  7. Each movie is directed by a single director.

  8. An actor plays a character in a movie

  The diagram should show at least one weak entity type, at least one partial participation constraint, and at least one total participation constraint.

• Problem #2 (10 points) Consider the following relations.

  EMPLOYEE
  SSN	Name	DeptNum
  111	Aaa	2
  222	Bbb	1
  333	Ccc	2
  444	Ccc	2
  555	Ddd	3

  DEPARTMENT
  Num	DeptName	MgrSSN
  1	Xxx	222
  2	Yyy	111
  3	Zzz	222

  Answer the following questions based on the above data.

  1. The MANAGING relationship is defined by the foreign key from DEPARTMENT(MgrSSN) to EMPLOYEE(SSN). Which of the following cases hold?
     1. The MANAGING relationship is 1-M from EMPLOYEE to DEPARTMENT
     2. The MANAGING relationship is 1-M from DEPARTMENT to EMPLOYEE
     3. The MANAGING relationship is 1-1
     4. The MANAGING relationship is M-N
     5. EMPLOYEE partially participates in the MANAGING relationship
     6. EMPLOYEE totally participates in the MANAGING relationship
     7. DEPARTMENT partially participates in the MANAGING relationship
     8. DEPARTMENT totally participates in the MANAGING relationship

  2. The BELONGS relationship is defined by the foreign key from EMPLOYEE(DeptNum) to DEPARTMENT(Num). Which of the following cases hold?
     1. The BELONGS relationship is 1-M from EMPLOYEE to DEPARTMENT
     2. The BELONGS relationship is 1-M from DEPARTMENT to EMPLOYEE
     3. The BELONGS relationship is 1-1
     4. The BELONGS relationship is M-N
     5. EMPLOYEE partially participates in the BELONGS relationship
     6. EMPLOYEE totally participates in the BELONGS relationship
     7. DEPARTMENT partially participates in the BELONGS relationship
     8. DEPARTMENT totally participates in the BELONGS relationship

  3. What is the outcome of the following relational algebra query?
     $${\rho }_{\left(SSN,Name,Num\right)}\left(EMPLOYEE\right)\bowtie {\rho }_{\left(Num,DeptName,SSN\right)}\left(DEPARTMENT\right)$$

• Problem #3 (10 points) Consider the following schema.
  Likes(Dancer, Song) 
  Frequents(Dancer, Disco) 
  Plays(Disco, Song) 
  

  A dancer can like many songs and a song can be liked by many dancers. A dancer can frequent many discos, and a disco can be frequented by many dancers. A disco can play many songs, and a song can be played by many discos.

  1. Write a relational algebra query for the following case: Find all dancers who frequent at least one disco that plays a song they like.

  2. Write a relational algebra query for the following case: Find all the dancers that frequent only discos that play some song that they like.

  3. Translate the following SQL query into relational algebra.
     select Song 
     from (select Song, count 
           from Plays 
           grouped by Song 
           having count=1 
          ) 
     

• Problem #4 (10 points) Consider the relations.
  Student:     Name         SSN            Attends:  SSN          Number 
               --------     ---------                ---------    ------ 
               Tom          123456789                123456789    670 
               Del          234567891                123456789    680 
               Dan          891234567                891234567    680 
                                                     234567891    670 
  Course:      Number       Name 
               ------       ---------- 
               670          database 
               680          data struc 
  

  Show the selection for each of the following queries.

  1. select Student.Name, Course.Name 
     from Student,Course,Attends 
     where Student.Name like ’%D%’ 
     

  2. select  S2.Number 
     from  Course as S1, Course as S2 
     where S1.Number > S2.Number 
     

  3. select  Number, count(*) 
     from Student, Attends 
     where Student.SSN=Attends.SSN 
     group by Number 
     

  4. select Number 
     from Attends S1 
     where not exists (select * 
                from Attends S2 
                where S1.SSN < S2.SSN)