(MMU) 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 Indicate (what and why) feature(s)/property(ies) (if any) in the above description that are NOT captured by your ER-diagram; 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".) Convert the entity-relationship design (in 1) to a scheme for a relational database List all relation schemes. For each relation scheme, state (i) the name of the relation, (ii) the names of its attributes, (iii) the domain (or data type) of each attribute, (iv) the primary key, (v) the foreign key(s); 2. 3. 4. (MMU) 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 Indicate (what and why) feature(s)/property(ies) (if any) in the above description that are NOT captured by your ER-diagram; 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".) Convert the entity-relationship design (in 1) to a scheme for a relational database List all relation schemes. For each relation scheme, state (i) the name of the relation, (ii) the names of its attributes, (iii) the domain (or data type) of each attribute, (iv) the primary key, (v) the foreign key(s); 2. 3. 4
