3,4,5 2. (10 Points) Now, the university does not think that it is necessary to keep track...

3,4,5
 

Transcribed Image Text:

2. (10 Points) Now, the university does not think that it is necessary to keep track student's registration on the same course multiple times. They just want to keep track only the last semester that the student registers for the course. For example, if Sam registers for CSci 4707 on Fall 2021 and Spring 2022, we only need to keep track of the registration on Spring 2022. HINT: you cannot really specify which semester we want to keep in the database as this is the application logic not the database logic. What you can do is to ensure each registrations only appear once. 3. (10 Points) Following the scenario in Problem 2, the university wants to introduce a category (uniquely identified by an id) for each course. Each course must belong to one and only one category and each category can have zero or many courses. Categories can be nested where a category may have zero or many super-categories and zero or many sub-categories. For example, course CSci 4707 may belong to the "Database" category. The "Database" category has a super-category of "Computer Science" which also has a super-category of "Science and Engineering". 4. (10 Points) Following the scenario in Problem 3, the university wants to introduce two types of students, namely undergraduate students and graduate students. Both types of student have the same attributes as a student, however, undergraduate student has an additional attribute of year while graduate student has an additional attribute of research area. Each graduate student must have one or more professors as their advisor. Professors may or may not advise graduate students. 5. (10 Points) Following the scenario in Problem 4, each professor has zero or many projects. A project is uniquely identified by both its id and its name. Each project is owned by exactly one professor. A graduate student can work on at most one project. However, when a professor leaves the university, all projects under the professor must be removed from the university database. 2. (10 Points) Now, the university does not think that it is necessary to keep track student's registration on the same course multiple times. They just want to keep track only the last semester that the student registers for the course. For example, if Sam registers for CSci 4707 on Fall 2021 and Spring 2022, we only need to keep track of the registration on Spring 2022. HINT: you cannot really specify which semester we want to keep in the database as this is the application logic not the database logic. What you can do is to ensure each registrations only appear once. 3. (10 Points) Following the scenario in Problem 2, the university wants to introduce a category (uniquely identified by an id) for each course. Each course must belong to one and only one category and each category can have zero or many courses. Categories can be nested where a category may have zero or many super-categories and zero or many sub-categories. For example, course CSci 4707 may belong to the "Database" category. The "Database" category has a super-category of "Computer Science" which also has a super-category of "Science and Engineering". 4. (10 Points) Following the scenario in Problem 3, the university wants to introduce two types of students, namely undergraduate students and graduate students. Both types of student have the same attributes as a student, however, undergraduate student has an additional attribute of year while graduate student has an additional attribute of research area. Each graduate student must have one or more professors as their advisor. Professors may or may not advise graduate students. 5. (10 Points) Following the scenario in Problem 4, each professor has zero or many projects. A project is uniquely identified by both its id and its name. Each project is owned by exactly one professor. A graduate student can work on at most one project. However, when a professor leaves the university, all projects under the professor must be removed from the university database.