University System
A university consists of multiple courses, students, instructors, and classrooms. You have been hired
as a database designer to model the university’s data management system.
Entities and Attributes:
1. Students:
Each student has a unique StudentID, Name, Email, and Phone Number.
Each student can enroll in multiple courses.
2. Courses:
Each course has a unique CourseID, Course Name, and Number of Credits.
Each course may have prerequisite courses.
Each course is taught by one instructor.
3. Instructors:
Each instructor has a unique Instructor ID and Name.
Each instructor has a Salary.
An instructor may teach multiple courses.
4. Sections:
Each section has a unique SectionID, Year, and Semester.
Each section belongs to a specific course.
Each student enrolls in a specific section of a course.
5. Classrooms:
Each classroom is located in a Building and has a specific Room Number.
Each section is assigned to a specific classroom for lectures.
6. Course Enrollment:
The system records all required data each time a student enrolls in a specific course.
Relationships in the University System
The university database consists of multiple entities that interact with each other through
relationships. Below is a breakdown of these relationships and how they connect different entities:
1. Student – Course (Enrolls in)
Relationship: A many-to-many relationship exists between Students and Courses because a student
can enroll in multiple courses, and each course can have multiple students.
Implementation: This requires a junction table (e.g., Enrollment) with:
StudentID
CourseID
2. Instructor – Course (Teaches)
Relationship: A one-to-many relationship exists between Instructors and Courses because each
course is taught by one instructor, but an instructor can teach multiple courses.
Implementation: A Teaches relationship with:
InstructorID
CourseID
3. Course – Prerequisite (Requires)
Relationship: A self-referential relationship exists within the Course entity, where some courses
require prerequisites before enrollment.
Implementation: A Prerequisite table that links courses:
CourseID
PrerequisiteCourseID
4. Student – Section (Takes)
Relationship: A many-to-many relationship exists between Students and Sections because a student
can take multiple sections, and a section can have multiple students
Implementation: A junction table (e.g., Takes) with:
StudentID
SectionID
5. Course – Section (Has)
Relationship: A one-to-many relationship exists between Courses and Sections because each course
can have multiple sections, but each section belongs to only one course.
Implementation: A Has relationship with:
CourseID
SectionID
6. Section – Classroom (Held in)
Relationship: A one-to-one or one-to-many relationship exists between Sections and Classrooms
because each section is assigned to a specific classroom, but a classroom can host multiple sections
at different times.
Implementation: A Held in relationship with:
SectionID
RoomNumber and Building
CourseName Credits
StudentID
Email
CourseID
Name
Student enroll in Course Prerequisite
N M
Phone
Takes Salary
N
Semeaster
ID
M 1
Section Teaches Instructor
Section ID
Name
M
Year
Section
class
One
Class Room
Building
Room Number
