Student Course Faculty

Phase 1

Initially, you must design and create a database that organizes and stores data. This includes designing the ER diagram, and then converting your designs to relational tables/schemas to store this data, and designing rules (when necessary) for how these tables relate to each other so that the data they store can be combined in meaningful ways. As part of the report for this phase, you will turn in an E-R diagram of your database design, and the translation to the relational schema

Consider the following set of requirements for a university database and application that are used to keep track of students' transcripts.

• The university keeps track of each student's name, student number, and social security number, current address and phone, permanent address and phone, date of birth, sex, class (freshman, sophomore, ..., graduate), major department, minor department (if any), and degree program (B.A., B.S., ..., Ph.D.). Some user applications need to refer to the city, state, and zip of the student's permanent address, and to the student's last name. Both social security number and student number have unique values for each student.
• The university keeps track of each faculty’s name, social security number, academic department, office number including building code and name, ranks (part-time adjunct instructor, assistant professor, associate, full professors), annual salary, courses and sections taught by the faculty. Graduate students supervised by the faculty also need to be maintained. Dependants of each full-time faculty member are also maintained in the database. Updates on this information can be done by the department secretary. Students in graduate doctoral program can teach sections.
• Each department is described by a name, department code, office number, building name and code, office phone, and college. Both name and code have unique values for each department.
• Each course has a course name, description, course number, number of semester hours, level, and offering department. The value of course number is unique for each course. A course may have one or more prerequisite courses.
• Each section has an instructor, semester, year, course, and section number, classroom number and building code. The section number distinguishes different sections of the same course that are taught during the same semester/year; its values are 1, 2, 3,.. up to the number of sections taught during each semester.
• A grade report has a student, section, letter grade, and numeric grade (0, 1, 2, 3,4 for F, D, C, B, A, respectively).
• On-line grade report can be accessed and updated if the faculty has taught the section. Update is allowed only for a month after the on-line submission. To update a grade after the time frame, the faculty must submit on-line change of grade form to the department chairperson. Only chairperson can update the grade when it is approved. Faculty needs to register for an account to access on-line grading system.
• To access on-line grade reports, a student must register for an account.