-1

I'm having trouble trying to implement this database; I have three tables Courses, Schedules, and Students.

Students have
studentID, name, GPA, etc.

Schedules have
ID, course1, course2, course3

Courses have
courseID, courseName, courseAbbv, courseCredits, courseDepartment


Students.studentID and Schedules.ID have a one to one relationship
and
Courses.courseID has a one to many relationship with Schedules.course1,course2,course3



I want to make a query where I can display a students schedule so:

studentName, course1, course2, course3
where course1, course2, course3= courseAbbv

Community
  • 1
  • 1
Rafi
  • 1
  • 3

1 Answers1

1

I imagine the tables to have the following relationships.

Student

• Student_Id (PK)

Schedule

• Schedule_Id (PK)

• Course_Id (FK)

• Student_Id (FK)

Course

• Course_Id (PK)

Your schedule table shouldn't contain columns per course, but rather a reference to the course table.

Jesse Petronio
  • 693
  • 5
  • 11
  • Sorry I forgot to add some of the foreign keys. The course1, course2, and course3 in my Schedules table are foreign keys referencing the Courses table. However I can't seem to display the course name whenever I create a query with the relationship – Rafi Nov 10 '15 at 07:03
  • What does your query look like? – Jesse Petronio Nov 10 '15 at 07:14
  • SELECT studentName, course1, course2, course3 FROM Students, Schedules, Courses WHERE Students.studentID = Schedules.studentID AND Schedules.course1 = Courses.ID AND Schedules.course2 = Courses.ID AND Schedules.course3 = Courses.ID; – Rafi Nov 10 '15 at 07:34
  • I'm sorry I'm new to this site, tried formatting it but couldn't get it to work – Rafi Nov 10 '15 at 07:35
  • You wont get any data back from the following query because Course 1, Course 2 and Course 3 are all going to be different, your query assumes they're the same. If you want to do it using a product join, you would need to provide aliases for the course table names – Jesse Petronio Nov 10 '15 at 07:52
  • SELECT studentName , Course1.CourseName , course2.CourseName , course3.CourseName FROM Students , Schedules , Courses Course1, Courses Course2, Courses Cource3 WHERE Students.studentID = Schedules.studentID AND Schedules.course1 = Course1.ID AND Schedules.course2 = Course2.ID AND Schedules.course3 = Course3.ID – Jesse Petronio Nov 10 '15 at 07:54
  • This is how the table structure should look like, except: **get rid of `Student.Schedule_Id`**. The junction table `Schedule` implements the n:m relation between `Student` and `Course`. – Andre Nov 10 '15 at 08:38