Course( course_no , course_name) Prereqs( course_no , pre_req_no) Student( stude
ID: 3548110 • Letter: C
Question
Course( course_no, course_name)
Prereqs(course_no, pre_req_no)
Student(student_id, fname, lname, major)
Faculty(faculty_id, fname, lname, department)
TakenBy(student_id, course_no, grade)
TaughtBy(faculty_id, course_no, semester, year)
Answer the following about the above relational schema:
1) Can more than one student take the same course? Why or why not?
2) Can a faculty teach more than one course? Why or why not?
3) Can a student have more than one grade for the same course? Why or why not?
4) Give a query in relational algebra to answer the following: give names of students who have never taken courses with ProfessorX.
Thank you!
Explanation / Answer
Course( course_no, course_name)
Prereqs(course_no, pre_req_no)
Student(student_id, fname, lname, major)
Faculty(faculty_id, fname, lname, department)
TakenBy(student_id, course_no, grade)
TaughtBy(faculty_id, course_no, semester, year)
Answer the following about the above relational schema:
1) Can more than one student take the same course? Why or why not?
Yes can. Between student and taken by tables 1:M relation ship. That means One student can take more then one course.
2) Can a faculty teach more than one course? Why or why not?
No. Faculty and TaughtBy tables are 1:1 relation ship. One faculty can teach only one course
3) Can a student have more than one grade for the same course? Why or why not?
Student(student_id, fname, lname, major)
TakenBy(student_id, course_no, grade)
No. If you see above tables Student(student_id) and TakenBy(student_id, course_no) had 1:1 relationship. that means one student can have one grade for the same course.
4) Give a query in relational algebra to answer the following: give names of students who have never taken courses with Professor X.
select fname ||' '|| lname from Student where student_id not in(select student_id from student,TakenBy,TaughtBy,Faculty where TakenBy.student_id = student.student_id and TakenBy. course_no=TaughtBy. course_no and Faculty.faculty_id = TaughtBy.faculty_id and fname = 'X);