Assignment 01 – Data storage and databases
You are required to complete and submit answers to both Task 1 and Task 2 of this assignment. Answers should be submitted in a single Word document.
Please rename your document with your student ID followed by ‘_Assignment01’.
Example: 12345678_Assignment01.docx
Important Background Information
Before starting the tasks, it is helpful to understand how Moodle stores information about quizzes and the questions they contain.
Moodle separates quizzes and questions into different tables. When you add questions to a quiz, Moodle does not store the questions directly inside the quiz. Instead, it uses a linking table:
Key Tables:
• quiz: Stores information about the quiz (e.g., name, course, timing).
• question: Stores all questions in the Moodle system.
• quiz_slots: Connects quizzes to questions.
Table relationships:
quiz.id-------quiz_slots.quizid
question.id--------quiz_slots.questionid So:
• A quiz can have many slots (quiz_slots),
• Each slot points to a specific question in the question bank.
You will use this understanding in both the ERD and SQL tasks below.
Task 1
This task centres around the Quiz activity in Moodle. As preparation, create at least three quizzes in your Moodle courses. Add a few questions to each quiz (e.g., multiple- choice or short answer), and then attempt your quizzes using student accounts.
To ensure a variety of data for your queries:
• Set different values for the “Attempts allowed” setting across your quizzes.
• You must have at least one quiz where only 1 attempt is allowed.
• You must also have at least two quizzes where more than 1 attempt is allowed.
• Each student account should have attempted at least one quiz more than once.
These steps will help generate the data needed to complete the tasks in this assignment effectively. You will use Usermin to explore the Moodle database. In Usermin, search for quiz and question to locate the related tables. To view the latest data, remember to Refresh the tables after each activity on Moodle.
a) Exploring key tables [3 marks]
Explain the main purpose of the following tables. For each, provide a couple of sentences describing what the table is for and mention a few meaningful fields that illustrate its role (Do not just list the fields)
• mdl_quiz
• mdl_quiz_attempts
• mdl_quiz_slots
• mdl_question
Provide screenshots of these tables showing the table data after you have created quizzes, added questions, and made attempts as students.
b) Entity Relationship Diagram (ERD) [5 marks]
Draw an ERD for the Quiz concept using the following six entities:
• quiz
• quiz_attempts
• quiz_slots
• question
• user
• course
In your ERD:
• Indicate Primary Key (PK) and Foreign Key (FK) using standard notation
• Show cardinalities between entities
• Include a few meaningful fields (e.g., quiz name, user ID, question next) to make the domain concept clear.
• You do not need to list all the fields for every entity.
Notes:
• Use the same ERD notation style. as shown in course materials.
• You can draw by hand or use a diagramming tool.
• Test your model with actual data from your Moodle site. Creating a couple of
quizzes and answering questions will help you see how the tables are connected.
c) Understanding Quiz settings [3 marks]
Quizzes allow teachers to control how many attempts students are allowed. This is set using the “Attempts allowed” setting in the Moodle quiz activity.
For this part, perform. the following:
1. Locate this setting in your Moodle site and take a screenshot.
2. Explain:
a. What happens in the database when a student takes the quiz multiple times? and what entries appear in mdl_quiz_attempts table?
b. What happens ifa teacher changes the number of allowed attempts after some students have already attempted the quiz? For example, ifa quiz
initially allows only 1 attempt and later this is changed to 3 attempts, what happens to:
i. Students who had already attempted the quiz once before the change.
ii. Students who had not yet attempted it.
Task 2
For each of the given scenarios, provide the SQL statement (typed as text so we can copy/paste) and a screenshot of the query result in Usermin.
Notes:
• Make sure your Moodle site contains enough data (multiple quizzes, questions, and attempts) so that your query results are meaningful.
• Do not just provide screenshots. You must include the SQL code as well.
• Remember to refresh your tables in Usermin after making changes in Moodle.
a) [1.5 marks]
Show the id and name of all quiz activities on your site, along with the id, shortname, and fullname of the course each quiz belongs to.
b) [1.5 marks]
List all quizzes that allow more than one attempt. Show the quiz id, quiz name, and the number of attempts allowed.
c) [2 marks]
Display the slot number, question name, question type and question text for all questions in two selected quizzes.
d) [2 marks]
Show the total number of quiz attempts across your site, and the number of unique users who have attempted any quiz.
e) [2 marks]
List all students who have attempted quizzes. Show the quiz name, the student’s first name and last name, and their attempt number.
Order the results by quiz name, then last name.