Show sourcecode
The following files exists in this folder. Click to view.
webbserverprogrammering/projects/anton-quiz/setup/
create_admin.php
dbsetup.sql
drop_tables.php
install.php
show_tables.php
dbsetup.sql
61 lines UTF-8 Windows (CRLF)
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061
CREATE DATABASE IF NOT EXISTS quizdb;
USE quizdb;
-- Tabell för användare (samma tabell används för vanliga användare och admins)
CREATE TABLE IF NOT EXISTS quizdb_users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
password VARCHAR(255) NOT NULL,
latest_login DATETIME DEFAULT CURRENT_TIMESTAMP,
is_admin TINYINT(1) NOT NULL DEFAULT 0 -- 0 = kund, 1 = admin
);
-- Tabell för frågetester
CREATE TABLE IF NOT EXISTS quizdb_tests (
id INT AUTO_INCREMENT PRIMARY KEY,
test_name VARCHAR(100) NOT NULL,
is_enabled TINYINT(1) NOT NULL DEFAULT 1,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
-- Tabell för frågor
CREATE TABLE IF NOT EXISTS quizdb_questions (
id INT AUTO_INCREMENT PRIMARY KEY,
test_id INT NOT NULL,
question_text VARCHAR(255) NOT NULL,
is_enabled TINYINT(1) NOT NULL DEFAULT 1, -- 1 = aktiv, 0 = inaktiv
FOREIGN KEY (test_id) REFERENCES quizdb_tests(id) ON DELETE CASCADE
);
-- Tabell för svarsalternativ
CREATE TABLE IF NOT EXISTS quizdb_answers (
id INT AUTO_INCREMENT PRIMARY KEY,
question_id INT NOT NULL,
answer_text VARCHAR(255) NOT NULL,
is_correct TINYINT(1) NOT NULL DEFAULT 0,
is_enabled TINYINT(1) NOT NULL DEFAULT 1,
FOREIGN KEY (question_id) REFERENCES quizdb_questions(id) ON DELETE CASCADE
);
-- Tabell för resultat
CREATE TABLE IF NOT EXISTS quizdb_results (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
test_id INT NOT NULL,
score INT NOT NULL DEFAULT 0,
taken_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES quizdb_users(id) ON DELETE CASCADE,
FOREIGN KEY (test_id) REFERENCES quizdb_tests(id) ON DELETE CASCADE
);
-- Tabell för användarens svar (för att kunna visa vilka svar som är rätt och fel)
CREATE TABLE IF NOT EXISTS quizdb_user_answers (
id INT AUTO_INCREMENT PRIMARY KEY,
result_id INT NOT NULL,
question_id INT NOT NULL,
answer_id INT NOT NULL,
is_correct TINYINT(1) NOT NULL,
FOREIGN KEY (result_id) REFERENCES quizdb_results(id) ON DELETE CASCADE,
FOREIGN KEY (question_id) REFERENCES quizdb_questions(id) ON DELETE CASCADE,
FOREIGN KEY (answer_id) REFERENCES quizdb_answers(id) ON DELETE CASCADE
);