University table structures

Each table is represented as TableName(field1, field2, field3, … fieldN), where the underlined fields are the fields that make up the primary key for that table.

  • Bldg(name)
  • Room(bldg, room number, capacity)
    • bldg is a foreign key to BLDG
  • College(id, name)
  • Dept(id, chair, college)
    • chair is a foreign key to PROF
    • college is a foreign key to COLLEGE
  • Student(id, family name, given name, birth date)
  • Prof(id, name, bldg, office)
    • [bldg, office] is a foreign key to ROOM
  • Course(id, name)
  • Section(course, section num, year, semester, bldg, room)
    • course is a foreign key to COURSE
    • [bldg, room] is a foreign key to ROOM
  • Dorm-room(student, bldg, room)
    • student is a foreign key to STUDENT
    • [bldg, room] is a foreign key to ROOM
  • Enroll(student, course, section, year, semester)
    • student is a foreign key to STUDENT
    • [course, section, year, semester] is a foreign key to SECTION
  • Teach(course, section, year, semester, prof)
    • [course, section, year, semester] is a foreign key to SECTION
    • prof is a foreign key to PROF
  • Major(student, department)
    • student is a foreign key to STUDENT
    • department is a foreign key to DEPT
  • Position(dept, prof)
    • dept is a foreign key to DEPT
    • prof is a foreign key to PROF
  • Prereq(course, prereq)
    • course is a foreign key to COURSE
    • prereq is a foreign key to COURSE
  • Qual(course, prof)
    • course is a foreign key to COURSE
    • prof is a foreign key to PROF
  • Offered(course, dept)
    • course is a foreign key to COURSE
    • dept is a foreign key to DEPT
  • Advisor(student, prof)
    • student is a foreign key to STUDENT
    • prof is a foreign key to PROF

Go back to the original problem.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-NonCommercial-ShareAlike 3.0 License