CS 582 — Database Management System II— Spring 2007

Homework 2

The purpose of this homework is to refresh our SQL/Oracle skill. Access your Oracle account and do the following:

• Create the tables Student, Professor, Teaching, Department, Transcript, and Course

as they are described in the handout (Pg. 43) with the exception that the Department table has two attributes, DeptId and DeptName. Please make sure that your database enforces the following foreign key constraints

  – DeptId in Professor refers to DeptId in Department.
  – DeptId in Course refers to DeptId in Department.
  – ProfId in Teaching refers to Id in Professor.
  – CrsCode in Transcript and Teaching refers in CrsCode in Course.
  – StudId in Transcript refers to Id in Student.

and the following constraints – Grade can only be either ’A’, ’B’, ’C’, ’D’, and ’F’.

– Semester is a string of 5 characters. Submit your SQL commands for this purpose. Note: If you happen to forget how to do all this, the book does contain commands for the purpose of creating this database.

• Insert 5 students (3 CS students and 2 EE students) in your database, each have taken at least 5 courses, one has full ’A’, one has all the grades (i.e., for each letter grade, the guy does have this grade), one has more ’F’ than all other grades, and for other two you can put everything that you want. Make sure that you have data in other tables as well. Submit your SQL commands for this purpose.

• Write the following SQL queries:

– List the students from the CS department.

– List the students from the CS and EE students.

– List the courses that have more than 3 students.

– List the professors who teach the most. Submit your SQL queries and the results of the execution of these queries (you can capture the results into a text file).