ZipCodes Buildings -AssignedTo- Zecode City StateAbbr Budina Address House H Buldinglame +-Belongto- Campuses Campuao Campusliame Facultyoffices o Contain Livein Faculty Easuto Firstiame Lastiame Address Rank Salary Speciaty Faciv - ClassRooms Boomtiumber Occupancy Students Studento Firstiame LastName StreetAddress Phone Advise Sections Courses 04Sesten Sectionumber Term Year Enrol CoursePrefix Coursellumber Courseliame Birthdate StartDate EndDate Grade H0...

(SQL Table) ERD for the University database

Narrative Description of the University Database

Students take courses at the university.  A student can serve as a mentor for one or more other students, but he/she is not required to do so.  Further, each student can have one other student as his/her mentor, but this, too, is not required.  A student, however, is required to be assigned to one and only one faculty member.  In turn, a faculty member advises many students but is not required to advise any students.

Courses are offered in multiple sections.  A course consists of a prefix as well as a course number.  For example, in this course, CSIS is the course prefix, and 325 is the course number.  CSIS 325 is offered in multiple sections, some residentially and some online.  All residential sections must be taught in a classroom on a campus; whereas online classes are not assigned to a classroom.  Sections are represented by a three-digit section number such as 001, 002, B01, etc.  A given section is identified by a unique SectionID field.  The term of a section is “Fall” or “Spring” for residential courses and “Fall-B”, “Fall-D”, “Spring-B”, “Spring-D”, etc. for online courses.  The year represents a 4-digit year.  All sections are taught by a faculty member, whether the section is offered online or residentially.  A faculty member may not be assigned to teach any classes, however.  This situation occurs when a new faculty member is hired and added to the Faculty table but is not yet assigned to individual classes.

Students can enroll in multiple sections (of different classes, of course), and each section can have multiple students enrolled in it.  Each student receives a grade in every class in which he or she is enrolled.  For undergraduate students, this grade can be “A”, “B”, “C”, etc.  For graduate students, the grade can be “A”, “A-“, “B+”, etc.  Therefore, the grade field must be large enough to accommodate the + or – as needed.  This grade is not entered until the end of the semester or term; however, the student is still recorded as being enrolled in the class from the minute he or she registers for it.   That is, a start date is assigned to the enrollment record as soon as the student registers for the class.  If a student withdraws from a class, the enrollment record is given an end date representing the date of withdrawal, and the grade reflects a “W”.  If the student drops the course before the official “drop/add” date, the record is removed from the table as if the student had never enrolled in it.

Residential faculty members are assigned to offices.  Online faculty members, unless they are on campus, are typically not assigned to offices.  In some cases, faculty members share offices, and sometimes, offices exist but are empty until they can be assigned to faculty members.  Each faculty office is associated with a Building.  There are multiple Room 100s throughout the various campuses, and a room does not have an identity outside of its association with a Building.  The same situation holds for classrooms.  A classroom does not have an identity outside of its association with a building.  Buildings, in turn are not dependent on any other entity.  They are assigned to a particular campus, but they do not derive their identities from the campus. 

ZipCodes are stored in the format 24515-0000.  Counting the dash, each zipcode can be as long as 10 characters.  Although it may seem strange now, in a later chapter, we will see why ZipCodes are often broken out into a separate table.