Consider the following schemas for a course registration sys

Consider the following schemas for a course registration system: Student(jNumber, firstName. lastName, streetAddress, city, state, zipcode) Courset(courseld, courseNumber, sectionNumber, year, semester, courseTitle, hours, instructor) RegisteringForCourse(jNumber, courseld) Answer the following questions: a) In the relation (table) Course, do you find another key among the attributes? If you do, list the attributes of the key you found. b). Do you see any table that implements a relationship? If you do. describe how that table (representing a relationship) relates other tables(entities)? c). Write a SQL statement that will return all the courses that all the students take. The result set should have the attributes: jNumber, firstName, lastName, courseNumber, sectionNumber, year, and semester. Also write a relational algebra expression to do the same. d). Write a SQL statement that will return jNumber, firstName, lastName of all students who take Ann Smith\'s courses. Note that the attribute instructor = \'Ann Smith\'. Also write a relational algebra expression to do the same.

Solution

a) courseNumber + sectionNumber (another key)

b) RegisteringForCourse
Is the relationship between Student and Course(i.e student enrolling to course) implememted by mapping studentid to some courseid
: c) select s.jNumber,s.fname,s.lname,c.courseNumber,c.sectionNumber,c.year,c.semester from Student s,Course c,RegisteringForCourse rc where s.jNumber=rc.jNumber and c.courseNumber=rc.courseNumber;

d) select s.jNumber,s.fname,s.lname,c.courseNumber,c.sectionNumber,c.year,c.semester from Student s,Course c,RegisteringForCourse rc where s.jNumber=rc.jNumber and c.courseNumber=rc.courseNumber and rc.courseNumber in(select courseNumber from Course where instructor=\'Ann Smith\');