Phase 1 ERD

In this phase of the project you will create an ERD based upon the following requirements and business rules. Limit your ERD to entities and relationships based on the business rules showed here. In other words, do not add realismto your design by expanding or refining the business rules. However, make sure you include all attributes needed that would permit the model to be successfully implemented, including all primary and foreign keys.

1.Trinity College (TC) is divided into several schools: a school of business, a school of arts and sciences, a school of education, and a school of applied sciences. Each school is administered by a dean who is a professor. Each dean can administer only one school. Each school must have a school name.

2.Each school is composed of several departments. The smallest number of departments operated by a school is one, and the largest number of departments is indeterminate. Each department belongs to only one school. A department may be classified as research only. Each department must have a department name.

3.Each department may have many professors assigned to it. One of those professors chairs the department. Only one professor may chair the department to which he or she is assigned. No professor is required to accept the chair position. Each professor must have a first name, last name, rank, specialty, and an email.

4.Each department may offer courses. Each course is offered by only one department. If a department is research only, it will offer no courses. Each course must have a course title, description, and number of credits.

5.A class will be a section of a course. A course may exist in Trinity Colleges catalog even when it is not offered as a class in a current schedule. Each class must have a class section and class time.

6.Each professor can teach up to four classes. A professor may also be on a research contract and teach no classes at all.

7.A student may enroll in up to six classes, but takes each class only once during each enrollment period. Each class may have up to 35 students. A class can exist even though no students are enrolled in it.

8.Each department has several students whose major is offered by that department, but a student can have only one major and is, therefore, associated with only one department. A student is not required to declare a major field of study when first enrolling.

9.Each student has an advisor in his or her department, each advisor counsels several students. An advisor is a professor, but not all professors advise students.

10.Each class is taught in a room, and each room is located in a building. A building can contain many rooms, but a room can only be contained in one building. Some buildings do not contain class rooms (e.g., maintenance building). A room must have a room type, a building must have a building name and location.

Phase 2. After reviewing the various ERDs, Trinity College has decided on the following tables and attributes.

PROFESSOR Professor ID Department Professor Specialty Professor Rank Professor Last Name Professor First Name Professor Initial Professor Email	SCHOOL School ID School Name SCHOOLDEAN School Dean	DEPARTMENT Department Code Department Name School DEPARTMENTCHAIR Department Chair
STUDENT Student ID Department Student Last Name Student First Name Student Initial Student Email Advisor	COURSE Course Code Department Course Title Course Description Course Credits	CLASS Class Code Class Section Class Time Course Professor Room	ENROLL Class Student Enrollment Date Enrollment Grade
BUILDIING Building Name Building Location	ROOM Room Code Room Type Building

Write the SQL code to drop and then create the above tables. Remember that when creating tables, the tables with foreign keys have to be created afterthe table that the foreign key points to. When dropping tables, they have to be dropped in the reverse order, so that the tables with foreign keys are dropped beforethe tables that the foreign keys point to. You can create all the drop SQL statements first followed by all the create tables. The first time you run them the drop statements will work as no tables have been created and therefore no drops will occur. You will need to run them twice to insure the drops will work.

Please be sure to review the Phase 1 requirements so you can determine the primary and foreign keys for each table and then determine the order of DROP and CREATE STATEMENTS. Please note that some fields are foreign keys but not identified as such by their names. Also know which fields are required fields and which are not. Use appropriate data types for each field and meaningful field names.