Логички и физички дизајн

Релациска шема (со мапирачка трансформација)

• Primary key: Underline
• Foreign key: *
• not null: Bold

---

• User(userID, fullName, username, password)

• Administrator(userID*(user))

• Moderator(userID*(user))

• Quiz Taker(userID*(user))

• Tournament Organizer(tournamentOrganizerID, userID*(moderator))

• Tournament(tournamentID, tournamentName)

• Tournament Phase(tournamentPhaseID, tournamentID*(tournament), tournamentPhaseName)

• Subject(subjectID, name)

• Category(categoryID, name, subjectID*(subject))

• Quiz(quizID, categoryID*(category), quizName)

• Medal(medalID, tournamentID*(tournament), medalName, description)

• Question(questionID, questionText)

• Hint(hintID, hintText, questionID*(question))

• Text Question(questionID*(question))

• Selection Question(questionID*(question))

• Choice(choiceID, questionID*(selectionQuestion), isCorrect, choiceText)

• Selected Choice(selectedChoiceID, choiceID*(choice), attemptID*(attempt))

• Attempt(attemptID, userID*(quizTaker), attemptDate, quizID*(quiz))

• Result(resultID, attemptID*(attempt), points)

• Badge(badgeID, badgeName, description, resultID*(result))

• quiz_is_part_of_tournament(quizID*(quiz), tournamentID*(tournament))

• quiz_is_part_of_tournamentPhase(quizID*(quiz), tournamentPhaseID*(tournamentPhase))

• tournamentOrganizer_hosts _tournament(userID*(tournamentOrganizer), tournamentID*(tournament))

• question_is_part_of_quiz(questionID*(question), quizID*(quiz))

• quizTaker_participates_in_tournament(userID*(quizTaker), tournamentID*(tournament))

• quizTaker_participates_in_tournamentPhase(userID*(quizTaker), tournamentPhaseID*(tournament Phase))

• medal_is_awarded_to_quizTaker(medalID*(medal), userID*(quizTaker))

• badge_is_awarded_to_quizTaker(badgeID*(medal), userID*(quizTaker))

DDL скрипта за бришење на табелите и креирање на табелите

KreiranjeTabeli.sql​

DML скрипта за полнење на табелите со податоци

PolnenjeTabeli.sql​

Релациски дијаграм изваден од DBeaver