COMP 231 INTRODUCTION TO DATABASE SYSTEMS
MIDTERM EXAM - DURATION 75 MINUTES


PART I	ER Data Model

1.	Consider the following database requirement:	

A book has a unique identifier, book title, author names, publisher, edition, year and price. 
A student has a unique student identifier, name, year of study, and major department.
A course has a unique course identifier, and name.
A course has maximum of four books prescribed for it. A book must be prescribed to at least one course.
A student can take minimum of one and maximum of nine courses a semester. A semester is specified by attributes term (like, fall, and spring) and year (like, 1999). At least 15 and at most 240 students take a course.

a.	Draw the ER Model using (min, max) notation for cardinality and participation constraints for the above database requirement (state assumptions, if any).

b.	Does one need additional ternary relationship type between entity types student, course, and book to model the relationship expressed by the statement "a student buys some (may not be all) books prescribed for courses he/she takes". Justify your answer. Are there any integrity constraints that need to be maintained if a ternary relationship type is needed?	


PART II	Relational Algebra, SQL, and TRC
2.	Consider following relations:
		EMPLOYEE	(ENO, ENAME, EDEPTNO)
		PROJECT	(PNO, PNAME, PDEPTNO)
		WORKS-ON	(WENO, WPNO, HOURS)
WORKS-ON is a many-many relationship type.
Answer the following:
a.	Write following query using relational algebra.	
Retrieve names of employees who work for more than 30 hours in some project controlled by department number 8.
b.	Write the following query using SQL.
For projects having five or more employees, list the project names in descending order, the number of employees working in the project, and average number of hours worked by these employees.
c.	Write the following update statement using SQL.		
Delete all tuples in appropriate order in the database related to department number 5.
d.	Write the following query using tuple relational calculus.	
List the names of all employees who work on some project for more than 40 hours.



PART IV	SHORT ANSWER QUESTIONS 
3.	State which of the following statements are true or false,justify your answer.
a.	A weak entity type always participates in only one identifying relationship type.
b.	It is always possible to write a SQL query to check whether entity integrity constraint is violated in a relation.