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:

  • COLLEGE each college has a unique name, address, and phone number.
  • DEPARTMENT Each department has a unique departmental code, department name, office location, and phone number.
  • COURSE Each course has a unique CRN, course id, course name, and credit.
  • SECTION Each section has a section number (partial identifier), day, and time.
  • STUDENT Each student has a unique student ID, name (first name, middle initial, last name), address (street, city, state, zip), phone, and status.
  • PROFESSOR Each professor has a faculty ID (a unique number assigned to all faculty), their name (consisting of first name, MI and last name), office location, and their office phone number.
  • RESIDENCE HALL Each residence hall has a unique name and address (street, city, state, zip).

After reviewing what the Provost told you, the following information concerning the relationships between the entity types were identified:

  • Each college consists of one or more departments. A department can belong to only one college.
  • Each department offers numerous courses and must offer at least one course. A given course is offered by only one department.
  • A course may have several sections, and must have at least one section. Each section is an offering of exactly one course.
  • A student may enroll in one or more sections, or may not have 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. Also, when the section is completed, a grade is recorded.
  • Some students serve as counselors for other students. 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 assigned to it, but must have at least one student assigned.
  • All students are assigned to exactly one professor as an academic advisor. Most, though not all, professors serve as advisors and typically advise numerous students.
  • Professors teach the sections of courses that are offered. Most professors teach one or more section, but some professors may be involved exclusively with research and do not teach sections. 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. A professor is employed by only 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.

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:

  • A cover page with the names of the members of your group.
  • Use Visio to map the provided entity-relationship diagram for the University.
  • Map the E-R diagram to a relational schema. Most of the relational schema is completed for you. You should finish it by drawing the referential integrity arrows.

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.

Academic Honesty!
It is not our intention to break the school's academic policy. Projects posted are only used as a reference and should not be submitted as is. We are not held liable for any misuse of the solutions. Please see the frequently asked questions page for further questions and inquiries.
Kindly fill out the form. Please provide a valid email address and we'll get back to you in less than 24 hours. We will be sending an invoice through PayPal upon confirmation. We are a non profit organization however we need an amount to keep this organization running, and to be able to complete our research and development.