CS50-Codeforces-Clone-Db

Project description

• My final project implements a relational database for competitive programming platform similar to codeforce, it models users, problems, submissions, and support some common operations like tracking user submissionss.

Entities / Tables Overview

• Problems : stores problems data including titles , and rating.
• Users : stored the registered users data including name, email, and rating.
• Contests : represents the data of contests including titles , and duration.
• Submissions : represents the data of the user submissions on problems.
• contests_type : it is a lookup table represents the types of contests.
• topics : it is a lookup table represents the topics of the problems.
• submission_results : it is a lookup table represents the types of submission results.
• contest_problems : it is a junction table represents the contests problems.
• submissions_code : it represents the submission code of problems.
• submission_type : a lookup table defining different submission types.

Relationships

• A user can submit multiple submissions on problems, but each submission belongs to one user ( 1 - N )
• Each submission is for only one problem, and has a result and type.
• Each problem may be on one or more topics, represented in a junction table problem_topics.
• Each contest conatain at least one problem, and each problem may be included in multiple contests ( gyms ).

Design desicions

• I have used lookup tables for contest types, submsission types, submission results, and topics to ensure consistency.
• Stored procedures are implemented to handle common operations like ranking users and retrieving submission data.

DBMS

• MySQL

Schema