M3a Create and Use Database – Part 1

Submit the statements for the Create and Use Database based on the diagram (as corrected) from the M2a Ivey School of Business: Doctoral Tracking Database – Part 1 assignment.

For the SQL portion of this assignment, the submission must be a single file that includes
• a header, subheaders, and all necessary comments
• drop statements for all the tables you are creating (in case they exist)
• the table creation statements (including constraints) for the Ivey Database Model
• insertion statements for the sample data.
• select statements to generate the reports indicated below.
Also submit a separate document explaining what test data you used to test your constraints. This would be data that you aren’t including in your insert data for this assignment because you were using it to make sure the constraints and other restrictions were working (that is, as long as everything is working correctly, the database won’t accept it). Your testing should include restrictions related to foreign key,uniqueness,nullbility, data types, data lengths, and any other constraints and restrictions in your tables.
Reports
Faculty: List of all faculty
 Fields: first name, last name
 Sort: last name
 Input: none
Advisors: List of all students and their advisors
 Fields: student’s last name and student’s first name (shown as one field in comma separated form), advisor’s last name and advisor’s first name (again, comma separated)
 Sort: student’s last name
 Input: none
Student status: List of all students and their enrollment status
 Fields: student last name and first name (comma separated), years enrolled, academic advisor last name and first name (comma separated)
 Sort: years enrolled
 Input: none
 Filter: only include currently active students

Please review the complete Create and Use Database Instructions page before submitting this assignment.