CSc4710/6710 Assignment 1

Description

Problem 1 (100 points)

Consider the following relational schema (the primary keys are underlined):

department(dName, location)

course(cID, clevel, cName, prerequisite, duration, credits)

offers(dName, cID)

instructor(instID, instName, office)

works_for(instID, dName)

teaches(instID, cID)

student(sID, sName, dob)

enrolled(sID, cID)

For (1) to (8) provide the following queries: (a) Relational Algebra; (b) Domain Relational Calculus; and

3. SQL. For (9) and (10) provide only SQL queries.

3. 1. Which courses are of 3 credits?

3. 2. Which courses are offered by the department of computer science?

3. 3. Which departments offer courses with ‘3’ credits but not ‘4’ credits?

   4. Which department has the highest number of enrolled students?

3. 5. Which instructor teaches the highest number of credits?

3. 6. Which courses belong to more than one department?

3. 7. Which students are taking courses from both “Computer science” and “Mathematics” departments?

3. 8. Which courses are provided at both undergraduate (clevel = 4XXX) and graduate (clevel = 6XXX) levels?

3. 9. For each department and course, give the number of courses provided at each level (1xxx,2xxx,3xxx,4xxx,6xxx). Display the results in ascending order of department name, and within each department, in ascending order of course levels (clevel).

3. 10. For each group of students, in a department, give the average number of students, minimum number of students, maximum number of students. Display the results in descending on average number of students.