Questions

Problem $1(10$ pts$).$

Read the following the previous section $$Prepare for SQL Coding".

Run all the commands in the above example. Insert your own data in the three tables: Professor, Course, Teaching $($feel free to make up the values $$ courses, departments, professors, etc. Insert a few rows in each of the tables$).$

Try violating each of the primary key constraints by inserting rows with duplicate values of primary key column$($s$).$ Make a screenshot of each error message you see in MySQL when trying to insert two rows with the same primary key value. You should make a total of three screenshots here, one for violating each of the primary key constraints.

Try violating each of the foreign key constraints. Take screenshot$($s$)$ of the error messages you see. You should have two screenshots here, one for each foreign key constraint.

Provide five screenshots of the results.

Provide the Q$1.$sql file that contains all your source code. here is the SQL: mysql$>$ create database xyz;

Query OK$,1$ row affected $(0\mathrm{.}00$ sec$)$

mysql$>$ use xyz;

Database changed

mysql$>$ CREATE TABLE Course $($

$->$ CrsCode varchar$(7),$

$->$ DeptId varchar$(3),$

$->$ CrsName varchar$(30),$

$->$ Descr varchar$(100),$

$->$ PRIMARY KEY $($CrsCode$),$

$->$ UNIQUE $($DeptId$,$ CrsName$)$

$->)$;

Query OK$,0$ rows affected $(0\mathrm{.}01$ sec$)$

mysql$>$ desc Course;

$+---------+--------------+------+-----+---------+-------+$

$|$ Field $|$ Type $|$ Null $|$ Key $|$ Default $|$ Extra $|$

$+---------+--------------+------+-----+---------+-------+$

$|$ CrsCode $|$ varchar$(7)|$ NO $|$ PRI $|$ NULL $||$

$|$ DeptId $|$ varchar$(3)|$ YES $|$ MUL $|$ NULL $||$

$|$ CrsName $|$ varchar$(30)|$ YES $||$ NULL $||$

$|$ Descr $|$ varchar$(100)|$ YES $||$ NULL $||$

$+---------+--------------+------+-----+---------+-------+$

$4$ rows in set $(0\mathrm{.}00$ sec$)$

mysql$>$ INSERT INTO Course VALUES$(\text{'}$CSCI$001\text{'},\text{'}$CS$\text{'},$ 'BIG DATA I$\text{'},$ 'Introduction to Big Data'$)$;

Query OK$,1$ row affected $(0\mathrm{.}01$ sec$)$

mysql$>$ INSERT INTO Course VALUES$(\text{'}$CSCI$002\text{'},\text{'}$CS$\text{'},$ 'Programming Languages', 'Formal definition of programming languages'$)$;

Query OK$,1$ row affected $(0\mathrm{.}00$ sec$)$

mysql$>$ SELECT $*$ FROM Course;

$+---------+--------+-----------------------+--------------------------------------------+$

$|$ CrsCode $|$ DeptId $|$ CrsName $|$ Descr $|$

$+---------+--------+-----------------------+--------------------------------------------+$

$|$ CSCI$002|$ CS $|$ Programming Languages $|$ Formal definition of programming languages $|$

$|$ CSCI$001|$ CS $|$ BIG DATA I $|$ Introduction to Big Data $|$

$+---------+--------+-----------------------+--------------------------------------------+$

$2$ rows in set $(0\mathrm{.}00$ sec$)$

mysql$>$ CREATE TABLE Professor $($

$->$ Id integer PRIMARY KEY,

$->$ Name varchar$(25),$

$->$ DeptId varchar$(3)$

$->)$;

Query OK$,0$ rows affected $(0\mathrm{.}00$ sec$)$

mysql$>$ DESC Professor;

$+--------+-------------+------+-----+---------+-------+$

$|$ Field $|$ Type $|$ Null $|$ Key $|$ Default $|$ Extra $|$

$+--------+-------------+------+-----+---------+-------+$

$|$ Id $|$ int$(11)|$ NO $|$ PRI $|$ NULL $||$

$|$ Name $|$ varchar$(25)|$ YES $||$ NULL $||$

$|$ DeptId $|$ varchar$(3)|$ YES $||$ NULL $||$

$+--------+-------------+------+-----+---------+-------+$

$3$ rows in set $(0\mathrm{.}01$ sec$)$

mysql$>$ INSERT INTO Professor VALUES $(1,$ 'Weitian Tong', $\text{'}$CS$\text{'})$;

Query OK$,1$ row affected $(0\mathrm{.}00$ sec$)$

mysql$>$ CREATE TABLE Teaching $($

$->$ ProfId integer,

$->$ CrsCode varchar$(7),$

$->$ Semester varchar$(6),$

$->$ PRIMARY KEY $($CrsCode$,$ Semester$),$

$->$ FOREIGN KEY $($CrsCode$)$ REFERENCES Course $($CrsCode$),$

$->$ FOREIGN KEY $($ProfId$)$ REFERENCES Professor $($Id$)$

$->)$;

Query OK$,0$ rows affected $(0\mathrm{.}00$ sec$)$

mysql$>$ desc Teaching;

$+----------+------------+------+-----+---------+-------+$

$|$ Field $|$ Type $|$ Null $|$ Key $|$ Default $|$ Extra $|$

$+----------+------------+------+-----+---------+-------+$

$|$ ProfId $|$ int$(11)|$ YES $|$ MUL $|$ NULL $||$

$|$ CrsCode $|$ varchar$(7)|$ NO $|$ PRI $|$ NULL $||$

$|$ Semester $|$ varchar$(6)|$ NO $|$ PRI $|$ NULL $||$

$+----------+------------+------+-----+---------+-------+$

$3$ rows in set $(0\mathrm{.}00$ sec$)$

mysql$>$ INSERT INTO Teaching VALUES$(1,$ 'CSCI$001\text{'},\text{'}$S$2023\text{'})$;

Query OK$,1$ row affected $(0\mathrm{.}00$ sec$)$

mysql$>$ SELECT $*$ FROM Teaching;

$+--------+---------+----------+$

$|$ ProfId $|$ CrsCode $|$ Semester $|$