We want to maintain a database for a Faculty within a multi-campus regional university. The faculty has a number of schools and each school is characterized by its school-code which is unique, name and campus location. Schools employ professors who are characterized by their unique staff-id, name and the school they work for. A school offers courses that are characterized by their course-code which is unique, course name and the school. There are subjects offered within the faculty and these are characterized by unique subject-code, title and the school that offers them. Students enroll into courses that are characterized by student-id which is unique, name, address and phone. The following business rules apply • A course must belong to only one school, but a school may have many courses • A student must be enrolled in a course, but a course may have 0 or more students • A course must have one or more subjects, but a subject may be taught as part of many courses • A student may be enrolled in many subjects, and a subject may have many students enrolled in it • A subject must be taught by a professor, and a processor may teach many subjects • A professor is employed by only one school, and a school may employ many professors ERD diagram is given in the picture below. The question now is: What can you say about the relationships between Courses and Subject, and Courses and Students? Will there be any problems in creating a Relational Database from your ER diagram? If there is, then describe how you will address this and re-draw the ER diagram. also Create the DDL statements to create the tables described in your modifies ER diagram.
We want to maintain a
has a number of schools and each school is characterized by its school-code which is unique, name
and campus location. Schools employ professors who are characterized by their unique staff-id, name
and the school they work for. A school offers courses that are characterized by their course-code which
is unique, course name and the school. There are subjects offered within the faculty and these are
characterized by unique subject-code, title and the school that offers them. Students enroll into
courses that are characterized by student-id which is unique, name, address and phone.
The following business rules apply
• A course must belong to only one school, but a school may have many courses
• A student must be enrolled in a course, but a course may have 0 or more students
• A course must have one or more subjects, but a subject may be taught as part of many courses
• A student may be enrolled in many subjects, and a subject may have many students enrolled
in it
• A subject must be taught by a professor, and a processor may teach many subjects
• A professor is employed by only one school, and a school may employ many professors
ERD diagram is given in the picture below. The question now is:
What can you say about the relationships between Courses and Subject, and Courses and Students?
Will there be any problems in creating a Relational Database from your ER diagram? If there is, then
describe how you will address this and re-draw the ER diagram.
also
Create the DDL statements to create the tables described in your modifies ER diagram.
Step by step
Solved in 3 steps with 2 images
