RelationalDesign: KreiranjeTabeli.sql

drop table administrator cascade; 
drop table attempt cascade; 
drop table badge cascade;
drop table badge_is_awarded_to_quiztaker cascade;
drop table category cascade;
drop table choice cascade; 
drop table hint cascade; 
drop table medal cascade; 
drop table medal_is_awarded_to_quiztaker cascade; 
drop table moderator cascade; 
drop table question cascade; 
drop table question_is_part_of_quiz cascade; 
drop table quiz cascade; 
drop table quiz_is_part_of_tournament cascade; 
drop table quiz_is_part_of_tournamentphase cascade;
drop table quiztaker cascade; 
drop table quiztaker_participates_in_tournament cascade; 
drop table quiztaker_participates_in_tournamentphase cascade; 
drop table result_table cascade;
drop table selectedchoice cascade; 
drop table selectionquestion cascade; 
drop table subject cascade; 
drop table textquestion cascade; 
drop table tournament cascade; 
drop table tournamentorganizer cascade; 
drop table tournamentorganizer_hosts_tournament cascade; 
drop table tournamentphase cascade; 
drop table user_table cascade; 


create table user_table(
        userID serial primary key,
        fullName varchar(100),
        username varchar(50) not null unique,
        password_attr varchar(50) not null
);

create table administrator(
        userID integer primary key,
        
        constraint userID_fk foreign key(userID)
                references user_table(userID)
);

create table moderator(
        userID integer primary key,
        
        constraint userID_fk foreign key(userID)
                references user_table(userID)
);

create table tournament(
        tournamentID serial primary key,
        tournamentName varchar(50) not null
);

create table tournamentPhase(
        tournamentPhaseID serial primary key,
        tournamentID integer not null,
        tournamentPhaseName varchar(50) not null,
        
        constraint tournamentID_fk foreign key (tournamentID)
                references tournament(tournamentID)
);


create table quizTaker(
        userID integer primary key,

        constraint userID_fk foreign key(userID)
                references user_table(userID)
        
);

create table tournamentOrganizer(
        userID integer not null,
        tournamentOrganizerID serial not null unique,
        
        constraint userID_fk foreign key(userID)
                references moderator(userID),
                
        constraint tournamentOrganizer_pk primary key(tournamentOrganizerID, userID)
                
        
        
);

create table subject(
        subjectID serial primary key,
        subjectName varchar(100) not null

);

create table category(
        categoryID serial primary key,
        subjectID integer not null,
        categoryName varchar(100) not null,
        
        constraint subjectID_fk foreign key(subjectID)
                references subject(subjectID)

);

create table quiz(
        quizID serial primary key,
        categoryID integer not null,
        quizName varchar(100) not null,
        
        constraint categoryID_fk foreign key(categoryID)
                references category(categoryID)
                
);

create table medal(
        medalID serial primary key,
        tournamentID integer not null,
        medalName varchar(50) not null,
        description varchar(500) not null,
        
        constraint tournamentID_fk foreign key(tournamentID)
                references tournament(tournamentID)

);


create table question(

        questionID serial primary key,
        questionText varchar(500) not null
        
);

create table hint(
        hintID serial primary key,
        hintText varchar(500) not null,
        questionID integer not null,
        
        constraint questionID_fk foreign key(questionID)
                references question(questionID)
);

create table textQuestion(
        questionID integer primary key,
        
        constraint questionID_fk foreign key(questionID)
                references question(questionID)
);

create table selectionQuestion(
        questionID integer primary key,
        
        constraint questionID_fk foreign key(questionID)
                references question(questionID)
);

create table choice(
        choiceID serial primary key,
        questionID integer not null,
        isCorrect boolean not null,
        choiceText varchar(500) not null,
        
        constraint questionID_fk foreign key(questionID)
                references selectionQuestion(questionID)
);

create table attempt(
        attemptID serial primary key,
        userID integer not null,
        attemptDate Date not null,
        quizID integer not null,
        
        constraint userID_fk foreign key(userID)
                references quizTaker(userID),
                
        constraint quizID_fk foreign key(quizID)
                references quiz(quizID)
);

create table selectedChoice (

        selectedChoiceID serial not null,

        choiceID integer not null,
        
        attemptID integer not null,
        
        constraint choiceID_fk foreign key(choiceID)
                references choice(choiceID),
                
        constraint attemptID_fk foreign key(attemptID)
                references attempt(attemptID),
                
        constraint selectedChoice_pk primary key(selectedChoiceID, choiceID)

);


create table result_table(
        resultID serial primary key,
        attemptID integer not null,
        points integer not null,
        
        constraint attemptID_fk foreign key(attemptID)
                references attempt(attemptID)
);

create table badge(
        badgeID serial primary key,
        badgeName varchar(100) not null,
        description varchar(500) not null,
        resultID integer not null,
        
        constraint resultID_fk foreign key(resultID)
                references result_table(resultID)
);

create table quiz_is_part_of_tournament(
quizID integer not null,
tournamentID integer not null,

constraint quizID_fk foreign key(quizID)
        references quiz(quizID),
        
constraint tournamentID_fk foreign key(tournamentID)
        references tournament(tournamentID),
        
        constraint pom1_pk primary key(quizID, tournamentID)
);

create table quiz_is_part_of_tournamentPhase(
quizID integer not null,
tournamentPhaseID integer not null,

constraint quizID_fk foreign key(quizID)
        references quiz(quizID),
        
constraint tournamentPhaseID_fk foreign key(tournamentPhaseID)
        references tournamentPhase(tournamentPhaseID),
        
        constraint pom8_pk primary key(quizID, tournamentPhaseID)
);

create table tournamentOrganizer_hosts_tournament(
userID integer not null,
tournamentID integer not null,

constraint tournamentOrganizerID_fk foreign key(userID)
        references tournamentOrganizer(tournamentorganizerid),

constraint tournamentID_fk foreign key(tournamentID)
        references tournament(tournamentID),
        
        constraint pom2_pk primary key(userID, tournamentID)
);

create table question_is_part_of_quiz(
        questionID integer not null,
        quizID integer not null,
        
        constraint questionID_fk foreign key(questionID)
                references question(questionID),
                
        constraint quizID_fk foreign key(quizID)
                references quiz(quizID),
        
        constraint pom3_pk primary key(questionID, quizID)
);

create table quizTaker_participates_in_tournament(
        userID integer not null,
        tournamentID integer not null,
        
        constraint userID_fk foreign key(userID)
                references quizTaker(userID),
                
        constraint tournamentID_fk foreign key(tournamentID)
                references tournament(tournamentID),
                
        constraint pom4_pk primary key(userID, tournamentID)
        
);

create table quizTaker_participates_in_tournamentPhase(
        userID integer not null,
        tournamentPhaseID integer not null,
        
        constraint userID_fk foreign key(userID)
                references quizTaker(userID),
                
        constraint tournamentPhaseID_fk foreign key(tournamentPhaseID)
                references tournamentPhase(tournamentPhaseID),
                
        constraint pom5_pk primary key(userID, tournamentPhaseID)
        
);

create table medal_is_awarded_to_quizTaker(
        medalID integer not null,
        userID integer not null,
        
        constraint medalID_fk foreign key(medalID)
                references medal(medalID),
                
        constraint userID_fk foreign key(userID)
                references quizTaker(userID),
                
        constraint pom6_pk primary key(medalID, userID)
);

create table badge_is_awarded_to_quizTaker(
        badgeID integer not null,
        userID integer not null,
        
        constraint badgeID_fk foreign key(badgeID)
                references badge(badgeID),
                
        constraint userID_fk foreign key(userID)
                references quizTaker(userID),
                
        constraint pom7_pk primary key(badgeID, userID)
);