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