part 1.

part 2.

You will design a database for an university (of some sort). This database will include information about departments, students, courses (and their offerings): Information about students includes their sid (id), name and age. The sid of a student is assumed to be unique, not shared by any other student. Each student is either a graduate or an undergraduate Students should be at least 18 years old to be admitted to university. Each student must be in one category or the other, and cannot be in both categories simultaneously, For graduate students, we record what their research field is. For undergraduate students, we record their major's department. Information about departments includes their name and school. The name of a department is assumed to be unique, not shared by any other department. . We need to be able to associate student with the departments with which they are affiliated. Each student has to be affiliated with exactly one department. Information about a course includes its number (e.g. "580"), name (e.g. "Introduction to Databases"), and capacity (e.g. 15). We also need to be able to know the unique department that owns each course: no cross-listing of courses across departments is allowed, and every course is owned by exactly one department Note/hint: you cannot assume that course number uniquely identifies a course, in fact, you cannot assume even that course number together with course name uniquely identify a course. However, course number uniquely identifies courses within a department. Finally, we need to note which professors teach each course. Note that a course can be taught by more than one professor (that is, Professors Mannion and Katz both teach Introduction to Databases), and instructors can teach multiple courses. Assume that for a course to be offered, it has at least one student enrolled and has a professor. Finally, assume that a student can take courses owned" by departments with which the student is not affiliated. And a student should be enrolled in at least one course . State any other assumptions you make. Your Task: Render the university database in the version of the E/R model that we studied in class with exactly the constraints and requirements specified above. Please state any assumptions that you make, but make sure that you don't introduce new constraints that are not listed in the problem definition Note/hint a weak entity set is a "regular" entity set in that it can participate in relationship sets other than the identifying relationship set just as any other entity set Show the SQL CREATE TABLE statements needed to convert your E/R diagram from item 1.1 into a relational schema. If your translation cannot capture any of the constraints in the E/R diagram, explain why. You will design a database for an university (of some sort). This database will include information about departments, students, courses (and their offerings): Information about students includes their sid (id), name and age. The sid of a student is assumed to be unique, not shared by any other student. Each student is either a graduate or an undergraduate Students should be at least 18 years old to be admitted to university. Each student must be in one category or the other, and cannot be in both categories simultaneously, For graduate students, we record what their research field is. For undergraduate students, we record their major's department. Information about departments includes their name and school. The name of a department is assumed to be unique, not shared by any other department. . We need to be able to associate student with the departments with which they are affiliated. Each student has to be affiliated with exactly one department. Information about a course includes its number (e.g. "580"), name (e.g. "Introduction to Databases"), and capacity (e.g. 15). We also need to be able to know the unique department that owns each course: no cross-listing of courses across departments is allowed, and every course is owned by exactly one department Note/hint: you cannot assume that course number uniquely identifies a course, in fact, you cannot assume even that course number together with course name uniquely identify a course. However, course number uniquely identifies courses within a department. Finally, we need to note which professors teach each course. Note that a course can be taught by more than one professor (that is, Professors Mannion and Katz both teach Introduction to Databases), and instructors can teach multiple courses. Assume that for a course to be offered, it has at least one student enrolled and has a professor. Finally, assume that a student can take courses owned" by departments with which the student is not affiliated. And a student should be enrolled in at least one course . State any other assumptions you make. Your Task: Render the university database in the version of the E/R model that we studied in class with exactly the constraints and requirements specified above. Please state any assumptions that you make, but make sure that you don't introduce new constraints that are not listed in the problem definition Note/hint a weak entity set is a "regular" entity set in that it can participate in relationship sets other than the identifying relationship set just as any other entity set Show the SQL CREATE TABLE statements needed to convert your E/R diagram from item 1.1 into a relational schema. If your translation cannot capture any of the constraints in the E/R diagram, explain why