Activity 17: CREATE DATABASE WITH CONSTRAINT

QuizDb

Create Database:

CREATE DATABASE quiz_system;

Creating the User Table

Select the Database:

 USE quiz_system;

Create User Table:

 CREATE TABLE User (
     user_id INT AUTO_INCREMENT PRIMARY KEY,
     name VARCHAR(50) NOT NULL,
     email VARCHAR(100) UNIQUE NOT NULL,
     password VARCHAR(255) NOT NULL,
     role ENUM('teacher', 'student') NOT NULL,
     createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
     updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
 );

Insert User Records:

 INSERT INTO User (name, email, password, role) VALUES
 ('Alice Johnson', 'alice.johnson@example.com', 'password1', 'teacher'),
 ('Bob Smith', 'bob.smith@example.com', 'password2', 'student'),
 ('Charlie Brown', 'charlie.brown@example.com', 'password3', 'student'),
 ('Diana White', 'diana.white@example.com', 'password4', 'teacher'),
 ('Ethan Black', 'ethan.black@example.com', 'password5', 'student');

Verify Inserted Data:

 SELECT name, email, password, role FROM User;

Creating the Quiz Database

Create Quiz Database:
```
 CREATE DATABASE Quiz;
```
Select the Quiz Database:
```
 USE Quiz;
```

Create Quiz Table:

 CREATE TABLE Quiz (
     quiz_id INT AUTO_INCREMENT PRIMARY KEY,
     title VARCHAR(100) NOT NULL,
     description TEXT NOT NULL,
     quiz_code VARCHAR(20) UNIQUE NOT NULL,
     teacher_id INT,
     duration INT NOT NULL
 );

Insert Data into Quiz Table:

 INSERT INTO Quiz (quiz_id, title, description, quiz_code, teacher_id, duration) VALUES
 (1, 'Introduction to SQL', 'A quiz covering basic SQL concepts', 'SQL101', 123, 30),
 (2, 'Data Types and Operators', 'Quiz on data types and operators in SQL', 'SQL201', 123, 45),
 ...
 (10, 'SQL for Data Analysis', 'Quiz on using SQL for data analysis', 'SQL1001', 123, 60);

Create Database Question

CREATE DATABASE Question;

USE Question;

Creating the Question Table

Create Question Table:

 CREATE TABLE Question (
     question_id INT PRIMARY KEY AUTO_INCREMENT,
     quiz_id INT,
     type ENUM('MCQ', 'TrueFalse', 'ShortAnswer', 'Multimedia') NOT NULL,
     created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
     updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
     question_text TEXT NOT NULL,
     FOREIGN KEY (quiz_id) REFERENCES Quiz(quiz_id) ON DELETE CASCADE
 ) ENGINE=InnoDB;

Insert Data into Question Table:

 INSERT INTO Question (quiz_id, question_text, type) VALUES
 (1, 'What is the capital of France?', 'MCQ'),
 (1, 'True or False: The Earth is flat.', 'TrueFalse'),
 ...

Creating the Answer Table

Create Answer Database:

 CREATE DATABASE Answer;

Create Answer Table:

 CREATE TABLE Answer (
     answer_id INT PRIMARY KEY AUTO_INCREMENT,
     question_id INT,
     student_id INT,
     answer_text TEXT NOT NULL
 );

Insert Data into Answer Table:

 INSERT INTO Answer (question_id, student_id, answer_text) VALUES
 (1, 1, 'This is my first answer to question 1.'),
 (1, 2, 'This is my second answer to question 1.'),
 (2, 1, 'This is my first answer to question 2.'),
 (2, 2, 'This is my second answer to question 2.'),
 (3, 1, 'This is my first answer to question 3.'),
 (3, 2, 'This is my second answer to question 3.'),
 (4, 1, 'This is my first answer to question 4.'),
 (4, 2, 'This is my second answer to question 4.'),
 (5, 1, 'This is my first answer to question 5.'),
 (5, 2, 'This is my second answer to question 5.');