[home page]

NAME$\underset{¯}{}$

 
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 bank has a name, numerical code, and address. A code uniquely identifies the bank.

  2. A bank branch has a numerical code and address. The code provides an identification for a branch of a bank. Branches of different banks need not have different codes.

  3. An account has a numerical code, type (saving or checking), and balance

  4. A loan has a numerical code, balance, and interest rate

  5. A customer has a social security number, name, and address.

  6. Each branch belongs to a bank.

  7. Each account is held by a bank branch and owned by a customer

  8. Each loan is provided by a bank branch and owed by a customer

  9. Money of a specified amount can be transfered between a loan and a saving or a checking account

• 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. If the issue of foreign keys is ignored, what are the possible single-attribute keys for the EMPLOYEE table and for the DEPARTMENT table?

  2. What are the possible single-attribute foreign keys for the EMPLOYEE table and for the DEPARTMENT table?

  3. What is the outcome of the OUTER NATURAL JOIN operation on the relations ${\rho }_{\left(SSN,Name,Num\right)}\left(EMPLOYEE\right)$ and ${\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 the dancers who like only songs that none of the discos play

  2. Write a relational algebra query for the following case: Find the dancers that like the most popular songs (i.e., the songs liked most by the dancers)

  3. Translate the following SQL query into relational algebra.
     select distinct S1.LN 
     from Students as S1, 
          Students as S2 
     where S1.LN = S2.LN 
       and S1.FN <> S2.FN 
     

• 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 
     where Course.Name like ’%a__’ 
     

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

  3. select  Number 
     from Student, Attends 
     where Student.SSN<Attends.SSN 
     

  4. select SSN, count(*), avg(Number) 
     from Attends S1 
     where exists (select * 
                from Attends S2 
                where S1.SSN <> S2.SSN) 
     group by SSN