You are to assume the role of a database analyst that has been assigned to develop a database for a small-town university. In your discussion with the Provost, the following information was provided:
The university consists of a number of separate colleges, each with a unique name (e.g., Business Administration, Engineering, etc.). Each college has one main address and a phone numbers (to accommodate the large number of calls received by each college. Each college is made up of one or more departments. (A given department can belong to only one college.) Each department is assigned a unique three-letter departmental code (e.g., BIO, MKT, etc.).In addition to the code, each department has a name (e.g., biology, marketing, etc.), office location, and phone number. Each department offers numerous courses. A given course is offered by only one department. Each course is identified by a CRN (Course Reference Number). Other information recorded for each course is the course ID (e.g., IS3063), course name (e.g. Introduction to Database Management) and the number of credits that the course is worth. A course may be offered in several different sections, but must have at least one section. A given section is an offering of exactly one course. Each section has a section number, which is a partial identifier (e.g., 001). Other information stored for each section includes the days offered (e.g., MWF, TR, etc.) and the time of day offered (e.g., 2:30 pm).
Information on students is also desired to be stored. Included in this information is each students name and home address. (Specific details to record include the students first name, MI, and last name, and the students street, city, state, and zip). The students phone number (for simplification, record only the students main telephone number), and status (e.g., FR, SO, JR, SR, GR) is also recorded. Students are identified by a unique student identification number assigned to each student by the university. Students may enroll in one or more course sections. Some students, however, are not currently enrolled in any sections. Sections typically enroll numerous students; however, it is possible for a section to be recorded that has no students yet enrolled in it. When a student enrolls in a section, the year, semester, and location are recorded. When the section is completed, a grade is recorded.
Some students serve as counselors for other students (e.g., incoming freshmen). Counselors are volunteers that help other students with problems related with student life. Not all students serve as counselors, but those that do often counsel multiple students. Not all students have a counselor, but those that do are assigned to only one counselor. Students may live on campus, in campus housing, or may not live on campus. If living in campus housing, a student may be assigned to only one Residence Hall. A Residence Hall can have several students living in it, but must have at least one student living there. All students are also assigned one professor as an academic advisor. Most, though not all, professors serve as advisors and typically advise numerous students. Information to be stored on each professor includes their faculty ID (a unique number assigned to all faculty), their name, (consisting of first name, middle and last name), office location, and their office phone number.
Professors, of course, also teach the sections of courses that are offered. Most professors teach one or more sections, but some professors may be involved exclusively with research and perform no teaching role. A given section may be taught by a single professor, or team taught by more than one professor. All professors are employed by the individual departments. No professor is employed by more than one department. Each department employs at least one professor. A professor is qualified to teach at least one course, but may be qualified to teach several courses. A course may not have any professors qualified to teach a particular course, and some courses have several professors that are qualified to teach the courses.
To clarify and organize what the Provost told you, the following entity types have been identified:
After reviewing what the Provost told you, the following information concerning the relationships between the entity types were identified:
On the next page you will find the Entity-Relationship Diagram for San Antonio University.
For the completion of the project, the following are the deliverables:
On the project due date, create and turn in the following:
1. A report including the following:
The report should be a Word document
2. Implement the relational model (from #1c above) as a MS Access database. Make sure that you join the tables in the relationships window and set the referential integrity constraint for each joined table.
3. Enter sample data in each table including a minimum of 10 records for each table.
4. Create a Student Information Form based on the Student table. Include all of the fields from the Student table in the form. This form can be used to conveniently enter new students information and to edit existing students information.
5. Create a Course form based on the Course table. Include all of the fields from the Course table in the form.
6. Create a Faculty Advisor Form. This form should include all of the fields from the Professor table in a main form, and a sub-form with the students ID and student name for each student who is advised by a professor.
7. Create a query to show the Colleges and the Departments that are in the Colleges. You should include the College_Name, Address, Department_Name, and Department_Office.
8. Create a report based on the query in #7.