Create an Entity-Relationship logical database design diagram for the
following scenario:
The Utopia College in North Bay wants to create a course registration system for its students.
The system should allow students to register for courses, drop courses, view their schedule, and check
their grades. The particulars of the system follows:
(20%)
\begin{itemize}
\item Each course has a unique course code, course name, and credits.
\item Each course offering has a unique section number, start date, end date, meeting time, room
number, and instructor.
\item The system should allow the college to maintain a record of all enrolled students. The data
captured for each student can be as follows:
\begin{itemize}
\item First and Last name
\item Home address (street, city, province, and postal code)
\item Cell phone number
\item Email address,
\item Major
\item Current GPA.
\end{itemize}
\end{itemize}
\begin{itemize}
\item A course may exist even if no students are currently enrolled in that course.
\item Each course is taught by exactly one instructor.
\item Instructors are supervised by a faculty member called a Dean.
\item A student must select at least one course per term; and therefore registers in at least one section.
\item (HINT: Give each table a unique Primary Key.)
\end{itemize}
Complete the following questions:
1. The relationship between Student and Section is _______________. (1%)
2. The relationship between the Dean and the Instructor is called a(n) _______________. (2%)
3. Complete the CREATE Table statement for the Faculty table:
\texttt{CREATE TABLE Faculty (
InstructorID INT PRIMARY KEY,
,LName VARCHAR(20)
,LName VARCHAR (20)
Dean\_ID INT,
FOREIGN KEY (?????) REFERENCES Faculty (?????)
);}
(2%)
