- Problem #1 (10 points)
Draw an ER diagram for the following application of a database.
- A movie has a name and release date.
- A studio has a numeric id, name, and address
- A director has a SSN and a name
- An actor has a SSN and a name
- A character in a movie has a name and a role.
- Each movie is produced by a single studio.
- Each movie is directed by a single director.
- 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.
- The MANAGING relationship is defined by the foreign key from DEPARTMENT(MgrSSN) to EMPLOYEE(SSN).
Which of the following cases hold?
- The MANAGING relationship is 1-M from EMPLOYEE to DEPARTMENT
- The MANAGING relationship is 1-M from DEPARTMENT to EMPLOYEE
- The MANAGING relationship is 1-1
- The MANAGING relationship is M-N
- EMPLOYEE partially participates in the MANAGING relationship
- EMPLOYEE totally participates in the MANAGING relationship
- DEPARTMENT partially participates in the MANAGING relationship
- DEPARTMENT totally participates in the MANAGING relationship
- The BELONGS relationship is defined by the foreign key from EMPLOYEE(DeptNum) to DEPARTMENT(Num).
Which of the following cases hold?
- The BELONGS relationship is 1-M from EMPLOYEE to DEPARTMENT
- The BELONGS relationship is 1-M from DEPARTMENT to EMPLOYEE
- The BELONGS relationship is 1-1
- The BELONGS relationship is M-N
- EMPLOYEE partially participates in the BELONGS relationship
- EMPLOYEE totally participates in the BELONGS relationship
- DEPARTMENT partially participates in the BELONGS relationship
- DEPARTMENT totally participates in the BELONGS relationship
- What is the outcome of the following relational algebra query?
- 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.
- Write a relational algebra query for the following case: Find all dancers who frequent at least one disco that plays a
song they like.
- Write a relational algebra query for the following case: Find all the dancers that frequent only discos that play some
song that they like.
- 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.
-
select Student.Name, Course.Name
from Student,Course,Attends
where Student.Name like ’%D%’
-
select S2.Number
from Course as S1, Course as S2
where S1.Number > S2.Number
-
select Number, count(*)
from Student, Attends
where Student.SSN=Attends.SSN
group by Number
-
select Number
from Attends S1
where not exists (select *
from Attends S2
where S1.SSN < S2.SSN)