![\"\"](\"http:\/\/www.hybridclassroom.com\/blog\/wp-content\/uploads\/2019\/01\/database-1024x313.jpg\")
<\/figure>\n\n\n\nThe MySQL database consisted of three tables that teams of students would interact with. There is the users<\/strong> table which included their a The second table was the problems<\/strong> table, with fields for the Finally there was the successes<\/strong> table which identified for each problem successfully solved the Manipulating these three tables with various queries allowed the site to display the Leaderboard with a running total for all teams: For people who have worked with PHP, MySQL, and querying databases, putting together this kind of thing can range in difficulty from a trivial activity for an afternoon to a week-long exercise in PHP debugging hell. For myself, it was just<\/em> enough of a challenge for me that I found the process to be entertaining and engaging.<\/p>\n\n\n\n If you are not a PHP\/MySQL person and don’t have any experience with writing “normalized” databases, you may be wondering whether or not it would be possible to find some way to avoid this whole “build an entire database-driven website just so students can solve a few puzzles.” These are just puzzles, of course, each with a simple answer that a student could submit on paper or whisper in your ear. You’re still almost certainly going to have to deliver computer-based problems by computer, however\u2014a 6MB text file for students to search through, for example, is not something that you can hand out on paper\u2014and without the database, there’s no practical way to create a live leaderboard indicating who is in the lead at any point. For a do-it-yourself CTF, I’d strongly recommend finding a way to make the website experience happen.<\/p>\n\n\n\n If you don’t decide to conduct your own Capture the Flag event, however, there are a number of publicly available competitions for high school-aged students that will work for your students. A quick search online will yield results for CTF events and their corresponding schedules.<\/p>\n\n\n\n I encourage all Computer Science teachers to give these activities a try, and consider making them a part of your curriculum.<\/p>\n","protected":false},"excerpt":{"rendered":" In the last post I was talking about the Homemade Capture-the-Flag competition that I’d created for my students as part of a culminating, end-of-semester activity. Students, working in teams, used their computers and technological\/programming skills to solve a series of puzzles that I’d created for them. As mentioned before, there are two main challenges in … Continue reading Homemade Capture-the-Flag, part 2<\/span> user_id<\/code> (a primary key), a team name (identified by the email<\/code> field in the screenshot here), a hash of their password (pass<\/code>, and the time\/date of the team’s last login.<\/p>\n\n\n\n![\"\"](\"http:\/\/www.hybridclassroom.com\/blog\/wp-content\/uploads\/2019\/01\/database-users-1024x141.jpg\")
<\/figure>\n\n\n\nproblem_id<\/code>, the problem_num<\/code>, the problem itself (statement<\/code>, the correct answer<\/code>, and the points<\/code> awarded for successfully solving the problem.<\/p>\n\n\n\n![\"\"](\"http:\/\/www.hybridclassroom.com\/blog\/wp-content\/uploads\/2019\/01\/database-problems-1024x356.jpg\")
<\/figure>\n\n\n\nsuccess_id<\/code>, the problem_id<\/code>, the solver_id<\/code> of the problem, and the time it was solved.<\/p>\n\n\n\n![\"\"](\"http:\/\/www.hybridclassroom.com\/blog\/wp-content\/uploads\/2019\/01\/database-successes-1024x577.jpg\")
<\/figure>\n\n\n\nSELECT users.email, SUM(problems.points) from users, problems, successes WHERE successes.solver_id = users.user_id AND problems.problem_id = successes.problem_id GROUP BY users.user_id ORDER BY SUM(problems.points) DESC<\/code><\/p>\n\n\n\n