Reference no: EM132003052

Consider the following set of requirements for a county Public School System which has many schools at elementary, middle, and high school levels.

1. For each school, the system needs to keep track of its unique name, address, classification (Value could be Elementary, Middle, or High), and number of students studying in it.

2. For each School System Employee, we need to keep track of the unique employee number, full name, address, salary, and the school where (s)he works. An individual works only in one school.

3. For each student, we keep track of the student's name (at times, we need to refer to student's first name, middle initial, and last name individually), address (at times, we need to refer to the street address, city, state, and zip code individually), the school (s)he attends, and what grade (s)he is in.

4. The system sends letters to High School students frequently, and hence, needs to keep track of each High School student along with the year when (s)he enrolled in the High School.

5. A system-wide list of courses offered is kept. Information about a course consists of its unique number, unique title, and number of credits.

6. For each school, the information about which courses are taught there is kept.

7. For each student, we keep a grade report that provides the grade (Value could be A, B, C, D, or F) for the student for a specific course.

8. The School System owns buses which are identified uniquely by their registration numbers. Some students take them to commute between their home and their school, while others use their personal means to commute. We keep track of which student takes which bus to commute. We also keep track of drivers assigned to buses (a driver is a school system employee who could be assigned to multiple buses, and a bus could have multiple drivers assigned to it - consider this a weekly assignment of buses and drivers).

Design an ER schema for this application, and draw an ER diagram for that schema. Specify key attributes of each entity type and structural constraints on each relationship type. Specify the structural constraints using partial/total participation and specification of cardinality ratio. If you find that the specifications are incomplete, then make appropriate assumptions and document them precisely. Do a complete job. Your design must follow the user requirements. For example, if the client wants an entity type, it must show up in your design.