2. Develop a database to support the tracking needs of a university. For each student: a student ID, student name, student ID is unique; for each course: a course ID, course title, course ID is unique; for each professor: a professor ID, professor name, professor ID is unique. A student can take several courses, one course can be taken by many students; in addition, a student must take at least one course, but a course may not have any students. A professor can advise several students, and a student can only have one professor as the advisor; in addition, a professor may not advise any students, but a student must need a professor as the advisor. A professor can teach several courses, and a course can be taught by several professors; in addition, a professor may not teach any courses, and a course may not be assigned to any professors)

Please:

2.1. create an ERD for the university (7 points)

2.2. map the ERD to a relational schema (table structures), each table should be in 3NF (6 points)

2.3. show (make up by yourself) several records for each relation (table), and write sentence(s) to indicate the participations (6 points)

2.4. based on the relational schema (table structures), write SQL statement to answer (8 points)

2.4.1. List the title of the courses which the student Tom (only student name is known, you can use another name based on your data) takes.

2.4.2. List the advisor name of the student Tom (only student name is known, you can use another name based on your data).

2.4.3. List the number of advisees (students) and professor name for each professor.

2.4.4. List the title of the courses which Professor Smith (only professor name is known, you can use another name based on your data) teaches.