MS access homework help!

Database Design

You are going to design a database system for a small university. The database will be used for scheduling classes. The university consists of multiple schools, which each having multiple departments.

For this assignment I want you to use a crows-foot notation. You will need to:

• Define the tables that you need in the database

• Define the attributes that need to be included in the tables

  • More attributes (e.g., unique ID’s) may need to be added to the tables

  • Some attributes may need to be split apart (e.g., Name – split into -> LNAME, FNAME, MINITIAL)

• Define the relationships (e.g., 1-to-1, 1-to-many, many-to-many) between the tables

• You will need to identify the primary and foreign keys.

• DO NOT worry about differentiating between strong and weak relationships

The following are the business rules for the university:

1. Each school consists of multiple departments, but a department can only belong to a single school.

2. Each department can offer many different classes and each class offered may have multiple sections.

3. A class is only offered through one department and each section maps to a single class.

4. A section of a class is assigned to a single classroom at a specific time, but a classroom may have several sections assigned to it.

5. Departments may employ many faculty members and each faculty member is employed by one department.

6. A professor (faculty member) can teach many sections, but a section is taught by a single faculty member.

7. Multiple students can sign up for many sections and sections may have many students.

8. A student has one faculty advisor, but an advisor can advise many students.

Information maintained about the different entities includes:

• School

  • School name (e.g., Business, Engineering, etc.)

  • Building name (e.g., Gorman, Agnese-Sosa, etc.)

  • Office number

  • Dean name

  • Phone Number

  • Mail Stop

• Department

  • Department name (e.g., Management Information Systems, Management, etc.)

  • Coordinator name

  • Building name

  • Office number

  • Phone number

  • Mail stop

• Class

  • Course number (e.g., MIS2321)

  • Course name (e.g., Introduction to MIS)

• Section

  • Unique ID number (e.g., CRN00101)

  • Section number (e.g., 1,2,3,4)

• Room

  • Building name (e.g., Gorman, ATT, etc.)

  • Room name/number (e.g., 107, 104, etc.)

  • Capacity

• Faculty

  • Faculty ID

  • Faculty name

  • Building name

  • Office number

  • Office phone

  • Title

• Student

  • Student ID Number

  • Student Name

  • Phone Number

  • Address

  • Major (e.g., Management Information Systems, Management, etc.)

  • Major2

  • Minor