People and student clubs at a university: Every person affiliated with Giant State University (GSU), be they a student, faculty member, or staff member, has a university ID card with a unique ID number. For every affiliated person, the university database stores a family name and given name, the person’s gender, a postal address consisting of a street address, city, state and zip code, and a primary e‐mail address. Assume that GSU has access to a zip code table. For specific categories of people, the university stores additional information: for students, the university stores the date of admission and expected graduation date. For administrative staff, the university stores the date of hire and administrative job classification code. For faculty, they store the name of the institution at which the faculty member earned their doctoral degree, the year of the degree, and the field it was obtained in. Note that the categories of faculty, administrative staff, and students can overlap. For example, an administrative staff member might take a course for credit and thus also be a student, or an administrator might also be a faculty member. GSU also has a large number of student clubs. For each club, the university wants to store a name, a description, and a date formed. Each club also has a single faculty advisor, who must be a member of GSU’s faculty; the database should remember the identity of the faculty advisor for each club. Although it is uncommon, it is possible for a faculty member to be the advisor for more than one club. Finally, the database should remember which students are members of each club. All club members must be students; a student can be a member of as many clubs as they wish, and there is no limit on the number of members of a given club. Design the portion of the university’s database needed to store all the information mentioned above, drawing an ER diagram and writing a database design outline. You may add “ID” fields wherever necessary. Assume that the university has access to a national zip code table, and try to avoid storing blank fields for attributes that do not apply to a particular class of person: for example, you should not have to store a blank administrative job classification field for students or faculty who are not also administra tive staff.
#Sales Offer!| Get upto 25% Off: