Using the diagram below and the questions, create the T-SQL Statements (or MySQL if you are using that database) that will create the artifacts in the database described. Write your answers as a T-SQL script in SSMS (or equivalent tool) and submit to Blackboard.

For inserting the data, you do not have to be meticulous or perfect. Just put the correct data so that the statement will in fact insert data into the table.

All the statements below add up to 230 total points. The bonus question is worth 20 points.

Write the statement to create the university database. (5 points)

Write the statement to create the Department table. (15 Points)

Write the statement to create the Person table. (15 points)

Write the statement to create the Onsite Course Table. (12 points)

Write the statement to create the Online Course table. (6 points)

Write the statement to create the Student Grade table. (12 points)

Write the statement to create the Course Instructor table. (6 points)

Write the statement to create the Course table. (12 points)

Write the statement to create the Office Assignment Table. (9 points)

Assume that you do not have a primary key for the course table. Write a statement that would add one. (5 points)

Define the relationship between OnsiteCourse and Course (this is creating a foreign key). Make this a check constraint. (5 points)

Define the relationship between OnlineCourse and Course. (5 points)

Define the relationship between StudentGrade and Course. (5 points)

Define the relationship between StudentGrade and Student. (5 points)

Define the relationship between CourseInstructor and Course. (5 points)

Define the relationship between CourseInstructor and Person. (5 points)

Define the relationship between Course and Department. (5 points)

Define the relationship between OfficeAssignment and Person. (5 points)

Insert data into the Person table (ten rows). (10 points)

Insert data into the Department table (five rows). (5 points)

Insert data into the Course table (ten rows). (10 points)

Insert data into the OnlineCourse table (four rows). (4 points)

Insert data into OnsiteCourse table (six rows). (6 points)

Insert data into the CourseInstructor table (nine rows). (9 points)

Insert data into the OfficeAssignment table (nine rows). (9 points)

Insert data into the StudentGrade table (forty rows). (40 points)

Bonus Question:

Create a stored procedure to insert a person into the person table. (20 points)