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:
o Define the tables that you need in the database
o Define the attributes that need to be included in the tables
o More attributes (e.g., unique ID's) may need to be added to the tables
o Some attributes may need to be split apart (e.g., Name - split into -> LNAME, FNAME, MINITIAL)
o Define the relationships (e.g., 1-to-1, 1-to-many, many-to-many) between the tables
o You will need to identify the primary and foreign keys.
o 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
o School name (e.g., Business, Engineering, etc.)
o Building name (e.g., Gorman, Agnese-Sosa, etc.)
o Office number
o Dean name
o Phone Number
o Mail Stop
• Department
o Department name (e.g., Management Information Systems, Management, etc.)
o Coordinator name
o Building name
o Office number
o Phone number
o Mail stop
• Class
o Course number (e.g., MIS2321)
o Course name (e.g., Introduction to MIS)
• Section
o Unique ID number (e.g., CRN00101)
o Section number (e.g., 1,2,3,4)
• Room
o Building name (e.g., Gorman, ATT, etc.)
o Room name/number (e.g., 107, 104, etc.)
o Capacity
• Faculty
o Faculty ID
o Faculty name
o Building name
o Office number
o Office phone
o Title
• Student
o Student ID Number
o Student Name
o Phone Number
o Address
o Major (e.g., Management Information Systems, Management, etc.)
o Major2
o Minor