Within the e-learning system, teachers can create tests, assign them to students, and monitor how the students did in the test. For simplicity, we will consider only tests of the ABCD type, always with one correct and 3 incorrect answers. We therefore want to record the following data:

• Test questions: Question text, correct answer, incorrect answers. For simplicity, we will consider only tests of the ABCD type, each with one correct and 3 incorrect answers.
• Test: the teacher can create a test consisting of several questions (any number). About the test, we need to record the name of the test, the ID of the author (the owner of the test) and the ID of the questions that the test contains.
• Assignment: The teacher can assign the test to students. About the assignment, it is necessary to record who assigned the test (i.e. teacher ID), to whom the test is assigned (student ID), test ID and the time when the test was assigned to the student.
• Result: After completing the assigned test, we want to record the student's score. We need to know about the result (elaboration), which assignment it concerns, the time the test was taken and the score in percentage. A student can take the test any number of times within one assignment.
• Answer: When developing the assigned test, i.e. assignment, the student chose one of the possible answers to the test questions. We want to record which answers he chose, i.e. for the given result (or elaboration) and test question, we want to record the text of the answer.
• Student, Teacher: We want to record the name and surname of each student and teacher.

Your task is:

1. Design the structure of the database described above and create the testy.sql file, which will contain SQL commands that will create the tables of the database you designed. Let the tables have the names question, test, question_test, assignment, result, answer, student, teacher.
   Add the DROP TABLE IF EXISTS command before each CREATE TABLE command so that the testy.sql file can be run several times in a row without error messages (or add all DROP TABLE IF EXISTS commands to the beginning of the file).
2. Where appropriate, set the connection between the tables via foreign keys (FOREIGN KEY). Don't forget to set ON DELETE / ON UPDATE correctly.
3. Fill each table with at least 3 records. I.e. add queries to the tests.sql file that populate each table with at least 3 records.
4. When a teacher leaves the school, we want to transfer his tests to another teacher. Write a query that changes the author/owner of the test to another teacher (teacher IDs can be constants). Add the query to the testy.sql.
file
5. Write queries that, for a given test ID (constant), delete this test from the database along with all its assignments and results. Add the queries to the testy.sql.
file
6. Teachers have a request to add information about when the student can complete the assigned test. Write a query that adjusts the structure of the tables so that for each assignment it is possible to record the date by which the student must complete the test
7. At the end of the testy.sql file, write the following SELECT queries:
   1. List the students and their assigned tests, i.e. 5-tice [Name of the student, Surname of the student, Name of the Test, Time of Assignment, Time of Completion], where Name of the Test is the name of the test that was assigned to the student StudentID at the time of the Time of Assignment. If the student prepared the test, let Time of Preparation be the time of the last preparation of the test. If the student has not taken the test yet, the Completion Cas is NULL. Sort the list in descending order by assignment time.
   2. Complete the list from the previous task with information about the student's result from the given test, i.e. write the 6s [Student's Name, Student's Surname, Test Name, Time of Assignment, Time of Processing, ResultPercentage], where ResultPercentage is the last result (if any) of the given student from the given assignment (i.e. ResultPercentage corresponds to the same elaboration as Time of Processing). If the student has not solved the test yet, let ResultPercentage be NULL (similar to Time of Processing).
   3. Complete the previous list with information on whether the student answered all the test questions. I.e. write the 6s [Student's Name, Student's Surname, Name of the Test, Date of Assignment, ResultPercentage, Finished], where Finished is "yes" if the student answered all the questions of the given test and "no" if there is a question that he did not answer (i.e. there is no record in the answer table)
   4. Write the questions, i.e. touples [QuestionText,CorrectAnswer] answered correctly by each student (within any assignment, i.e. there is no incorrect answer to this question).

Your homework is therefore one file tests.sql, which you send to the address rjasko (zavinac) dcs.fmph.uniba.sk no later than 22nd december 2024 23 :59:59.