In SQL Server, create a new database called University.

Given the ERD in this document, write the CREATE TABLE statements to construct the tables for this database. Be sure to use reasonable data types and create all tables, attributes, primary keys, foreign keys, and $$not null$$ constraints in your statements where appropriate.

To receive full credit for this assignment, your CREATE TABLE statements must be executed in SQL Server.Narrative Description of the University Database

Students take courses at the university. A student can serve as a mentor for one or more other students, but he$/$she is not required to do so$.$ Further, each student can have one other student as his$/$her mentor, but this, too, is not required. A student, however, is required to be assigned to one and only one faculty member. In turn, a faculty member advises many students but is not required to advise any students.

Courses are offered in multiple sections. A course consists of a prefix as well as a course number. For example, in this course, CSIS is the course prefix, and $325$ is the course number. CSIS $325$ is offered in multiple sections, some residentially and some online. All residential sections must be taught in a classroom on a campus; whereas online classes are not assigned to a classroom. Sections are represented by a three$-$digit section number such as $001,002,$ B$01,$ etc. A given section is identified by a unique SectionID field. The term of a section is $$Fall$$ or $$Spring$$ for residential courses and $$Fall$-$B$,$Fall$-$D$,$Spring$-$B$,$Spring$-$D$,$ etc. for online courses. The year represents a $4-$digit year. All sections are taught by a faculty member, whether the section is offered online or residentially. A faculty member may not be assigned to teach any classes, however. This situation occurs when a new faculty member is hired and added to the Faculty table but is not yet assigned to individual classes.

Students can enroll in multiple sections $($of different classes, of course$),$ and each section can have multiple students enrolled in it$.$ Each student receives a grade in every class in which he or she is enrolled. For undergraduate students, this grade can be $$A$,$B$,$C$,$ etc. For graduate students, the grade can be $$A$,$A$-,$B$+,$ etc. Therefore, the grade field must be large enough to accommodate the $+$ or $$ as needed. This grade is not entered until the end of the semester or term; however, the student is still recorded as being enrolled in the class from the minute he or she registers for it$.$ That is$,$ a start date is assigned to the enrollment record as soon as the student registers for the class. If a student withdraws from a class, the enrollment record is given an end date representing the date of withdrawal, and the grade reflects a $$W$.$ If the student drops the course before the official $$drop$/$add$$ date, the record is removed from the table as if the student had never enrolled in it$.$

Residential faculty members are assigned to offices. Online faculty members, unless they are on campus, are typically not assigned to offices. In some cases, faculty members share offices, and sometimes, offices exist but are empty until they can be assigned to faculty members. Each faculty office is associated with a Building. There are multiple Room $100$s throughout the various campuses, and a room does not have an identity outside of its association with a Building. The same situation holds for classrooms. A classroom does not have an identity outside of its association with a building. Buildings, in turn are not dependent on any other entity. They are assigned to a particular campus, but they do not derive their identities from the campus.

ZipCodes are stored in the format $24515-0000.$ Counting the dash, each zipcode can be as long as $10$ characters. Although it may seem strange now, in a later chapter, we will see why ZipCodes are often broken out into a separate table.Lab: Structured Query Language $($SQL$)$ Queries Data Definition Language

$($DDL$)$ Create Table Example

EXAMPLE $-$ How your output should look for each table

Campuses:

CREATE TABLE Campuses

$($CampusID int,

CampusName varchar$(20),$

CONSTRAINT pk$\_$campuses PRIMARY KEY $($CampusID$))$