May 13th, 2020 
A small university is in the process of creating its first database system, and you are the chief architect of this system. This university does not have majors, faculties, and departments, but it does have programs. A program usually has 5 courses (in real situation, a program usually has much more courses, but to make the test data preparation easier, we set this number as 5), For simplicity, all the courses in a program are required and fixed. Prerequisite is not considered.
This university runs in a two-semester system: every year has Winter semester and Fall semester. In every semester, a course may be offered once or not be offered. A course-offering entity is necessary. A professor may teach any courses, and will teach one or more courses each semester. A student may register in a few programs. A course is passed when the grade is 60 or more.
Every person in the campus, a student or a professor, has an ID. This ID is uniformly managed, that means all the IDs are from the same pool, there is no special digit can be used to distinguish professors or students.
(6 marks) Using MySQL Workbench to create an ER model of this new database.
(4 marks) Convert the model into SQL statements which create tables. All the tables must be normalized or denormalized
(5 marks) Create a view Transcript, which is the transcript of all students. It should contain the following columns: studentID, studentName, course, year, semester, grade, professorName. Insert a few data items into the tables. Prepare a select statement to show a students transcript.
(5 marks) Create a view: TeachingProfile, which shows the teaching history of all professors. It should contain the following columns: ProfessorID, ProfessorName, course, year, semester, enrollment. Insert a few data items into the tables. Prepare a select statement to show a professors teaching profile.
(5 marks) Create a view Audit, which is the audit of all students. It should contain the following columns: studentID, programID, neededCourse, (such a row indicates that this course has not been taken or has not passed.). Insert a few data items into the tables. Prepare a select statement to show a students audit.
The design of the tables/relationships should be intuitive, straightforward, and should support university activities. Item 3, 4, 5 are the university activities.
Posted in 