=========================preview======================
(COMP231)quizXX_sol1.pdf
Back to COMP231 Login to download
======================================================
COMP 231 Database Management Systems

Quiz 1 September 28, 1999 12:15 C 12:45

Name: __________________________ Student ID: _______________________

1.
For each of the following 4 ER diagrams, indicate if it is a valid ER diagram; if yes, explain what it means; if no, explain why it is invalid.





Valid. C is a N:1 relation. A has existence dependent on B, and A has to relate at lease 1 B entity (10%)



Invalid. C is used with weak entity, but there is not any weak entity. (10%)






Valid. C is a N:M relation. A & B have existence dependent on each other. (10%)




Valid. A is a weak entity type. A is existence dependent on B. C is a 1:1 relation. (10%)








A
B
C
A






2.
Consider following relations:




EMPLOYEE (ENO, ENAME, DEPTNO)
PROJECT (PNO, PNAME, DEPTNO)
WORKS-ON (ENO, PNO, HOURS)
WORKS-ON is a many-many relationship type.
Answer the following query using relational algebra and tuple relational calculus .

Retrieve names of employees who work for less than 20 hours in some project controlled by department number 6.

ENAME(PDEPTNO=6 AND HOURS<20(EMPLOYEE. WORKS_ON . PROJECT)) (20%)

{e[ENAME] | EMPLOYEE(e) and (. w)(WORKS_ON(w) and w.ENO=e.ENO and w.HOURS<20 and (. p)(PROJECT(p) and p.PNO=w.PNO and p.DEPTNO=6))} (20%)












Answer the following query using SQL.

List all project names which have at least one employee working for it.

select PNAME from PROJECT p, WORKS_ON w
where p.PNO = w.PNO
(20%)