Problem 2. has decided to consolidate the functionality of three small overlapping database systems, which support applications for 1) teaching (e.g. instructor assignment and evaluation), for 2) registration (e.g. online course status, waiting lists), and for 3) student records (e.g. transcript generation). The resulting new system will support the following enterprise description: Professors and GTAs are assigned to teach the sections of each class being offered in a semester. At the end of the semester, they get a "team rating" (professors and GTAs together get one rating per section, rating is not done on individual). To support the assignment of professors to sections, a record is kept of which class each professor can teach. Classes can have one or more prerequisite classes. Students can take several sections each semester, and receive a grade for taking each section. Students may end up waiting for some sections, and receive a "rank" (determining the order they will be admitted if other students drop). However, no more than 10 students can wait on a class at the same time. Note that GTAs are students, however they differ in that they have a salary. All people (e.g. students, professors) are uniquely identified by their social security number. All classes are identified by department name (e.g. "INFS") and course number (e.g. "614"). Sections of classes are distinguished by their section number (e.g. "02"). Given the above description, do the following: 1. Draw an ER-diagram for the database, identifying the following: (i) all the entity sets; (ii) all the relationship sets and their multiplicity; (iii) the primary key for each entity set (and weak entity set, if any) and each relationship set. Invent more attribute(s) for the entity sets if you like. 2. Indicate (what and why) feature(s)/property(ies) (if any) in the above description that are NOT captured by your ER-diagram; 3. Give 2 examples of the types of reports that can be obtained from the database, and state the involved entity sets and/or relationship sets. Each report example must involve at least two entity/relationship sets; (For example, an report can be "List all the GTAs who have NOT taken all the prerequisite classes for the classes that are assigned to teach".) 4. Create an SQL script and run it on Oracle) to create all the relational tables corresponding to the ER diagram in (1) and insert at least two tuples to each table. When you create tables in SQL, for each table Indicate its attributes Indicate its primary key Indicate additional uniqueness constraints if any Indicate foreign keys if any Indicate NOT NULL constraints if any Note: Please type your solution if possible. Otherwise, write clearly. Always assume that the grader will INCORRECTLY decipher difficult handwriting. For question (4) you need to submit a separate file with the SQL script. Problem 2. has decided to consolidate the functionality of three small overlapping database systems, which support applications for 1) teaching (e.g. instructor assignment and evaluation), for 2) registration (e.g. online course status, waiting lists), and for 3) student records (e.g. transcript generation). The resulting new system will support the following enterprise description: Professors and GTAs are assigned to teach the sections of each class being offered in a semester. At the end of the semester, they get a "team rating" (professors and GTAs together get one rating per section, rating is not done on individual). To support the assignment of professors to sections, a record is kept of which class each professor can teach. Classes can have one or more prerequisite classes. Students can take several sections each semester, and receive a grade for taking each section. Students may end up waiting for some sections, and receive a "rank" (determining the order they will be admitted if other students drop). However, no more than 10 students can wait on a class at the same time. Note that GTAs are students, however they differ in that they have a salary. All people (e.g. students, professors) are uniquely identified by their social security number. All classes are identified by department name (e.g. "INFS") and course number (e.g. "614"). Sections of classes are distinguished by their section number (e.g. "02"). Given the above description, do the following: 1. Draw an ER-diagram for the database, identifying the following: (i) all the entity sets; (ii) all the relationship sets and their multiplicity; (iii) the primary key for each entity set (and weak entity set, if any) and each relationship set. Invent more attribute(s) for the entity sets if you like. 2. Indicate (what and why) feature(s)/property(ies) (if any) in the above description that are NOT captured by your ER-diagram; 3. Give 2 examples of the types of reports that can be obtained from the database, and state the involved entity sets and/or relationship sets. Each report example must involve at least two entity/relationship sets; (For example, an report can be "List all the GTAs who have NOT taken all the prerequisite classes for the classes that are assigned to teach".) 4. Create an SQL script and run it on Oracle) to create all the relational tables corresponding to the ER diagram in (1) and insert at least two tuples to each table. When you create tables in SQL, for each table Indicate its attributes Indicate its primary key Indicate additional uniqueness constraints if any Indicate foreign keys if any Indicate NOT NULL constraints if any Note: Please type your solution if possible. Otherwise, write clearly. Always assume that the grader will INCORRECTLY decipher difficult handwriting. For question (4) you need to submit a separate file with the SQL script