M$3$a Create and Use Database $-$ Part $1$

Listen

Instructions

Submit the statements for the Create and Use Database based on the diagram $($as corrected$)$ from the M$2$a Part $2$ 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 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 the foreign key, uniqueness, nullability, 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

Course completion status report: List of all courses a student has started and the status

Fields: student last name, student first name, course number, course name, status

Sort: Student last name, course number

Input: none