Activity 17 CREATE DATABASE WITH CONSTRAINT
Step 1: Create Database and Tables
CREATE DATABASE quiz_db;
USE quiz_db;
CREATE TABLE User (
user_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
password VARCHAR(255) NOT NULL,
role ENUM('teacher', 'student') NOT NULL DEFAULT 'student',
createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
CREATE TABLE Quiz (
quiz_id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
description TEXT,
quiz_code VARCHAR(255) UNIQUE,
teacher_id INT NOT NULL,
duration INT,
createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (teacher_id) REFERENCES User(user_id)
);
CREATE TABLE Question (
question_id INT PRIMARY KEY AUTO_INCREMENT,
quiz_id INT NOT NULL,
question_text TEXT NOT NULL,
question_type ENUM('MCQ', 'True/False', 'Short Answer', 'Multimedia') NOT NULL,
createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (quiz_id) REFERENCES Quiz(quiz_id)
);
CREATE TABLE OptionTable (
option_id INT PRIMARY KEY AUTO_INCREMENT,
quiz_id INT NOT NULL,
question_id INT NOT NULL,
option_text VARCHAR(255) NOT NULL,
is_correct BOOLEAN NOT NULL DEFAULT 0,
FOREIGN KEY (quiz_id) REFERENCES Quiz(quiz_id),
FOREIGN KEY (question_id) REFERENCES Question(question_id)
);
CREATE TABLE Answer (
answer_id INT PRIMARY KEY AUTO_INCREMENT,
question_id INT NOT NULL,
student_id INT NOT NULL,
answer_text TEXT,
submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (question_id) REFERENCES Question(question_id),
FOREIGN KEY (student_id) REFERENCES User(user_id)
);
CREATE TABLE Result (
result_id INT PRIMARY KEY AUTO_INCREMENT,
quiz_id INT NOT NULL,
student_id INT NOT NULL,
score DECIMAL(5,2),
submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (quiz_id) REFERENCES Quiz(quiz_id),
FOREIGN KEY (student_id) REFERENCES User(user_id)
);
Step 2: Insert Dummy Data
Insert Dummy Data for User Table
INSERT INTO User (name, email, password, role)
VALUES
('John Doe', 'john.doe@example.com', 'password123', 'teacher'),
('Jane Smith', 'jane.smith@example.com', 'password123', 'student'),
('Bob Johnson', 'bob.johnson@example.com', 'password123', 'teacher'),
('Alice Williams', 'alice.williams@example.com', 'password123', 'student'),
('Charlie Brown', 'charlie.brown@example.com', 'password123', 'teacher'),
('Emily Davis', 'emily.davis@example.com', 'password123', 'student'),
('David Wilson', 'david.wilson@example.com', 'password123', 'teacher'),
('Grace Moore', 'grace.moore@example.com', 'password123', 'student'),
('Ethan Lee', 'ethan.lee@example.com', 'password123', 'teacher'),
('Sophia Taylor', 'sophia.taylor@example.com', 'password123', 'student'),
('William Anderson', 'william.anderson@example.com', 'password123', 'teacher'),
('Isabella Thomas', 'isabella.thomas@example.com', 'password123', 'student'),
('James Jackson', 'james.jackson@example.com', 'password123', 'teacher'),
('Lily White', 'lily.white@example.com', 'password123', 'student'),
('Benjamin Harris', 'benjamin.harris@example.com', 'password123', 'teacher'),
('Chloe Martin', 'chloe.martin@example.com', 'password123', 'student'),
('Lucas Thompson', 'lucas.thompson@example.com', 'password123', 'teacher'),
('Mia Garcia', 'mia.garcia@example.com', 'password123', 'student'),
('Henry Martinez', 'henry.martinez@example.com', 'password123', 'teacher'),
('Zoe Robinson', 'zoe.robinson@example.com', 'password123', 'student'),
('Samuel Clark', 'samuel.clark@example.com', 'password123', 'teacher'),
('Amelia Lewis', 'amelia.lewis@example.com', 'password123', 'student'),
('Elijah Walker', 'elijah.walker@example.com', 'password123', 'teacher'),
('Lila Young', 'lila.young@example.com', 'password123', 'student'),
('Nathan Allen', 'nathan.allen@example.com', 'password123', 'teacher'),
('Scarlett King', 'scarlett.king@example.com', 'password123', 'student'),
('Owen Scott', 'owen.scott@example.com', 'password123', 'teacher'),
('Evelyn Adams', 'evelyn.adams@example.com', 'password123', 'student'),
('Jacob Nelson', 'jacob.nelson@example.com', 'password123', 'teacher'),
('Avery Carter', 'avery.carter@example.com', 'password123', 'student'),
('Mason Perez', 'mason.perez@example.com', 'password123', 'teacher'),
('Hannah Roberts', 'hannah.roberts@example.com', 'password123', 'student'),
('James Lee', 'james.lee@example.com', 'password123', 'teacher'),
('Sophie Green', 'sophie.green@example.com', 'password123', 'student'),
('Daniel Hall', 'daniel.hall@example.com', 'password123', 'teacher'),
('Madison Allen', 'madison.allen@example.com', 'password123', 'student'),
('Matthew Carter', 'matthew.carter@example.com', 'password123', 'teacher'),
('Ava Perez', 'ava.perez@example.com', 'password123', 'student'),
('Liam Wilson', 'liam.wilson@example.com', 'password123', 'teacher'),
('Charlotte Baker', 'charlotte.baker@example.com', 'password123', 'student'),
('Michael Turner', 'michael.turner@example.com', 'password123', 'teacher'),
('Natalie Mitchell', 'natalie.mitchell@example.com', 'password123', 'student'),
('Daniel Evans', 'daniel.evans@example.com', 'password123', 'teacher'),
('Amos Ramirez', 'amos.ramirez@example.com', 'password123', 'teacher'),
('Maya Harris', 'maya.harris@example.com', 'password123', 'student'),
('Elena Murphy', 'elena.murphy@example.com', 'password123', 'student'),
('Ryan James', 'ryan.james@example.com', 'password123', 'teacher'),
('Olivia Bennett', 'olivia.bennett@example.com', 'password123', 'student'),
('Joshua Collins', 'joshua.collins@example.com', 'password123', 'teacher'),
('Lucas Hall', 'lucas.hall@example.com', 'password123', 'student'),
('Ella Gonzalez', 'ella.gonzalez@example.com', 'password123', 'student'),
('Dylan Carter', 'dylan.carter@example.com', 'password123', 'teacher'),
('Aidan Miller', 'aidan.miller@example.com', 'password123', 'student');
INSERT INTO Quiz (title, description, quiz_code, teacher_id, duration)
VALUES
('Math Quiz 1', 'Basic algebra and arithmetic questions.', 'MQZ12345', 1, 30),
('Science Quiz 1', 'General science quiz.', 'SQZ12345', 2, 30),
('History Quiz 1', 'Ancient history quiz.', 'HQZ12345', 3, 30),
('Geography Quiz 1', 'Geography of the world.', 'GQZ12345', 4, 30),
('English Quiz 1', 'Literature and grammar.', 'EQZ12345', 5, 30),
('Physics Quiz 1', 'Basic physics concepts.', 'PQZ12345', 6, 30),
('Chemistry Quiz 1', 'Chemistry basics.', 'CQZ12345', 7, 30),
('Biology Quiz 1', 'Cell biology and genetics.', 'BQZ12345', 8, 30),
('Math Quiz 2', 'Advanced algebra and geometry.', 'MQZ22345', 1, 30),
('Science Quiz 2', 'Physics and chemistry combined.', 'SQZ22345', 2, 30),
('History Quiz 2', 'Modern world history.', 'HQZ22345', 3, 30),
('Geography Quiz 2', 'Geography of countries.', 'GQZ22345', 4, 30),
('English Quiz 2', 'Poetry and prose.', 'EQZ22345', 5, 30),
('Physics Quiz 2', 'Energy and motion principles.', 'PQZ22345', 6, 30),
('Chemistry Quiz 2', 'Organic chemistry basics.', 'CQZ22345', 7, 30),
('Biology Quiz 2', 'Ecology and evolution.', 'BQZ22345', 8, 30),
('Math Quiz 3', 'Trigonometry and calculus.', 'MQZ32345', 1, 30),
('Science Quiz 3', 'Astronomy and biology combined.', 'SQZ32345', 2, 30),
('History Quiz 3', 'Historical figures and events.', 'HQZ32345', 3, 30),
('Geography Quiz 3', 'Physical geography concepts.', 'GQZ32345', 4, 30),
('English Quiz 3', 'Grammar and syntax.', 'EQZ32345', 5, 30),
('Physics Quiz 3', 'Wave theory and thermodynamics.', 'PQZ32345', 6, 30),
('Chemistry Quiz 3', 'Chemical reactions and elements.', 'CQZ32345', 7, 30),
('Biology Quiz 3', 'Microbiology and botany.', 'BQZ32345', 8, 30),
('Math Quiz 4', 'Algebra and statistics.', 'MQZ42345', 1, 30),
('Science Quiz 4', 'Human biology and anatomy.', 'SQZ42345', 2, 30),
('History Quiz 4', 'Renaissance period.', 'HQZ42345', 3, 30),
('Geography Quiz 4', 'Landforms and ecosystems.', 'GQZ42345', 4, 30),
('English Quiz 4', 'Shakespearean literature.', 'EQZ42345', 5, 30),
('Physics Quiz 4', 'Electricity and magnetism.', 'PQZ42345', 6, 30),
('Chemistry Quiz 4', 'Periodic table and reactions.', 'CQZ42345', 7, 30),
('Biology Quiz 4', 'Genetics and inheritance.', 'BQZ42345', 8, 30),
('Math Quiz 5', 'Calculus and functions.', 'MQZ52345', 1, 30),
('Science Quiz 5', 'Human physiology.', 'SQZ52345', 2, 30),
('History Quiz 5', 'Industrial revolution.', 'HQZ52345', 3, 30),
('Geography Quiz 5', 'Climate and environment.', 'GQZ52345', 4, 30),
('English Quiz 5', 'English composition.', 'EQZ52345', 5, 30),
('Physics Quiz 5', 'Relativity and quantum mechanics.', 'PQZ52345', 6, 30),
('Chemistry Quiz 5', 'Acids and bases.', 'CQZ52345', 7, 30),
('Biology Quiz 5', 'Evolutionary theory.', 'BQZ52345', 8, 30);
INSERT INTO Question (quiz_id, question_text, question_type)
VALUES
(1, 'What is 2+2?', 'MCQ'),
(2, 'What is the chemical symbol for water?', 'MCQ'),
(3, 'Who was the first president of the United States?', 'MCQ'),
(4, 'What is the capital of France?', 'MCQ'),
(5, 'Which of the following is a type of poem?', 'MCQ'),
(6, 'What is Newtons second law?', 'MCQ'),
(7, 'What is the atomic number of hydrogen?', 'MCQ'),
(8, 'What is the function of mitochondria in a cell?', 'MCQ'),
(9, 'What is the square root of 16?', 'MCQ'),
(10, 'Which planet is known as the Red Planet?', 'MCQ'),
(11, 'What is the main source of energy for photosynthesis?', 'MCQ'),
(12, 'What is the capital of Japan?', 'MCQ'),
(13, 'Which scientist proposed the theory of relativity?', 'MCQ'),
(14, 'What is the boiling point of water in Celsius?', 'MCQ'),
(15, 'What is the primary language spoken in Brazil?', 'MCQ'),
(16, 'What is the largest continent by area?', 'MCQ'),
(17, 'What is the smallest planet in the solar system?', 'MCQ'),
(18, 'Who is the author of "1984"?', 'MCQ'),
(19, 'What is the formula for calculating the area of a circle?', 'MCQ'),
(20, 'What is the main function of the circulatory system?', 'MCQ'),
(21, 'What is the color of chlorophyll?', 'MCQ'),
(22, 'Who invented the telephone?', 'MCQ'),
(23, 'What is the symbol for gold on the periodic table?', 'MCQ'),
(24, 'Which ocean is the largest?', 'MCQ'),
(25, 'What is the process by which plants make food?', 'MCQ'),
(26, 'What is the distance between the Earth and the Sun?', 'MCQ'),
(27, 'What is the chemical formula for methane?', 'MCQ'),
(28, 'Who painted the Mona Lisa?', 'MCQ'),
(29, 'What is the capital of Australia?', 'MCQ'),
(30, 'What is the largest animal on Earth?', 'MCQ'),
(31, 'How many continents are there?', 'MCQ'),
(32, 'Which country is the largest by population?', 'MCQ'),
(33, 'What is the tallest mountain in the world?', 'MCQ'),
(34, 'What is the national flower of Japan?', 'MCQ'),
(35, 'Who was the first man to step on the moon?', 'MCQ'),
(36, 'What is the deepest ocean?', 'MCQ'),
(37, 'What is the most common gas in Earths atmosphere?', 'MCQ'),
(38, 'What is the chemical symbol for oxygen?', 'MCQ'),
(39, 'What is the main gas responsible for global warming?', 'MCQ'),
(40, 'Who wrote the play "Romeo and Juliet"?', 'MCQ'),
(41, 'What is the boiling point of liquid nitrogen?', 'MCQ'),
(42, 'What is the name of the longest river in the world?', 'MCQ'),
(43, 'What is the capital of Canada?', 'MCQ'),
(44, 'Which animal is known as the king of the jungle?', 'MCQ'),
(45, 'Who is known as the father of modern physics?', 'MCQ'),
(46, 'What is the main element in the sun?', 'MCQ'),
(47, 'Who developed the theory of evolution?', 'MCQ'),
(48, 'What is the largest desert in the world?', 'MCQ'),
(49, 'Who discovered penicillin?', 'MCQ'),
(50, 'Which country is known as the Land of the Rising Sun?', 'MCQ');
INSERT INTO OptionTable (quiz_id, question_id, option_text, is_correct)
VALUES
(1, 1, '3', 0),
(1, 1, '4', 1),
(1, 1, '5', 0),
(1, 1, '6', 0),
(2, 2, 'O', 0),
(2, 2, 'H2O', 1),
(2, 2, 'H2O2', 0),
(2, 2, 'CO2', 0),
(3, 3, 'George Washington', 1),
(3, 3, 'Thomas Jefferson', 0),
(3, 3, 'Abraham Lincoln', 0),
(3, 3, 'John Adams', 0),
(4, 4, 'Paris', 1),
(4, 4, 'London', 0),
(4, 4, 'Rome', 0),
(4, 4, 'Berlin', 0),
(5, 5, 'Haiku', 0),
(5, 5, 'Sonnet', 1),
(5, 5, 'Limerick', 0),
(5, 5, 'Ballad', 0),
(6, 6, 'F=ma', 1),
(6, 6, 'E=mc²', 0),
(6, 6, 'v=at', 0),
(6, 6, 'P=mv', 0),
(7, 7, '1', 0),
(7, 7, '2', 0),
(7, 7, '8', 1),
(7, 7, '10', 0),
(8, 8, 'Respiration', 0),
(8, 8, 'Digestion', 0),
(8, 8, 'Protein synthesis', 0),
(8, 8, 'Energy production', 1),
(9, 9, '2', 0),
(9, 9, '4', 1),
(9, 9, '6', 0),
(9, 9, '8', 0),
(10, 10, 'Mars', 1),
(10, 10, 'Venus', 0),
(10, 10, 'Earth', 0),
(10, 10, 'Jupiter', 0),
(11, 11, 'Sunlight', 1),
(11, 11, 'Wind', 0),
(11, 11, 'Soil nutrients', 0),
(11, 11, 'Water', 0),
(12, 12, 'Beijing', 0),
(12, 12, 'Tokyo', 1),
(12, 12, 'Seoul', 0),
(12, 12, 'New Delhi', 0),
(13, 13, 'Albert Einstein', 1),
(13, 13, 'Isaac Newton', 0),
(13, 13, 'Galileo Galilei', 0),
(13, 13, 'Marie Curie', 0),
(14, 14, '90°C', 0),
(14, 14, '100°C', 1),
(14, 14, '110°C', 0),
(14, 14, '120°C', 0),
(15, 15, 'Spanish', 0),
(15, 15, 'Portuguese', 1),
(15, 15, 'English', 0),
(15, 15, 'French', 0),
(16, 16, 'Asia', 1),
(16, 16, 'Africa', 0),
(16, 16, 'North America', 0),
(16, 16, 'Europe', 0),
(17, 17, 'Mercury', 0),
(17, 17, 'Venus', 0),
(17, 17, 'Earth', 0),
(17, 17, 'Pluto', 1),
(18, 18, 'Mark Twain', 0),
(18, 18, 'George Orwell', 1),
(18, 18, 'Charles Dickens', 0),
(18, 18, 'Jane Austen', 0),
(19, 19, 'πr²', 1),
(19, 19, '2πr', 0),
(19, 19, 'r²', 0),
(19, 19, '2r', 0),
(20, 20, 'Transport oxygen', 0),
(20, 20, 'Transport nutrients', 0),
(20, 20, 'Fight infection', 1),
(20, 20, 'Remove waste', 0),
(21, 21, 'Red', 0),
(21, 21, 'Blue', 0),
(21, 21, 'Green', 1),
(21, 21, 'Yellow', 0),
(22, 21, 'Alexander Graham Bell', 1),
(22, 22, 'Thomas Edison', 0),
(22, 22, 'Nikola Tesla', 0),
(22, 22, 'Michael Faraday', 0),
(23, 23, 'Au', 1),
(23, 23, 'Ag', 0),
(23, 23, 'Fe', 0),
(23, 23, 'Cu', 0),
(24, 24, 'Atlantic', 0),
(24, 24, 'Pacific', 1),
(24, 24, 'Indian', 0),
(24, 24, 'Arctic', 0),
(25, 25, 'Photosynthesis', 1),
(25, 25, 'Respiration', 0),
(25, 25, 'Fermentation', 0),
(25, 25, 'Transpiration', 0),
(26, 26, '93 million miles', 1),
(26, 26, '50 million miles', 0),
(26, 26, '150 million miles', 0),
(26, 26, '200 million miles', 0),
(27, 27, 'CH4', 1),
(27, 27, 'C2H6', 0),
(27, 27, 'CO2', 0),
(27, 27, 'O2', 0),
(28, 28, 'Leonardo da Vinci', 1),
(28, 28, 'Vincent van Gogh', 0),
(28, 28, 'Pablo Picasso', 0),
(28, 28, 'Claude Monet', 0),
(29, 29, 'Ottawa', 0),
(29, 29, 'Toronto', 0),
(29, 29, 'Vancouver', 0),
(29, 29, 'Ottawa', 1),
(30, 30, 'Blue Whale', 1),
(30, 30, 'Elephant', 0),
(30, 30, 'Giraffe', 0),
(30, 30, 'Shark', 0),
(31, 31, '7', 1),
(31, 31, '6', 0),
(31, 31, '5', 0),
(31, 31, '8', 0),
(32, 32, 'China', 1),
(32, 32, 'India', 0),
(32, 32, 'United States', 0),
(32, 32, 'Indonesia', 0),
(33, 33, 'Mount Everest', 1),
(33, 33, 'K2', 0),
(33, 33, 'Kangchenjunga', 0),
(33, 33, 'Mount Kilimanjaro', 0),
(34, 34, 'Cherry Blossom', 1),
(34, 34, 'Rose', 0),
(34, 34, 'Lotus', 0),
(34, 34, 'Tulip', 0),
(35, 35, 'Neil Armstrong', 1),
(35, 35, 'Buzz Aldrin', 0),
(35, 35, 'Yuri Gagarin', 0),
(35, 35, 'Michael Collins', 0),
(36, 36, 'Pacific', 0),
(36, 36, 'Atlantic', 0),
(36, 36, 'Indian', 0),
(36, 36, 'Southern', 1),
(37, 37, 'Nitrogen', 0),
(37, 37, 'Oxygen', 1),
(37, 37, 'Carbon Dioxide', 0),
(37, 37, 'Hydrogen', 0),
(38, 38, 'O', 1),
(38, 38, 'H', 0),
(38, 38, 'N', 0),
(38, 38, 'He', 0),
(39, 39, 'CO2', 1),
(39, 39, 'O2', 0),
(39, 39, 'N2', 0),
(39, 39, 'CH4', 0),
(40, 40, 'William Shakespeare', 1),
(40, 40, 'Christopher Marlowe', 0),
(40, 40, 'John Milton', 0),
(40, 40, 'Ben Jonson', 0);
INSERT INTO Answer (question_id, student_id, answer_text)
VALUES
(1, 2, '4'),
(2, 3, 'H2O'),
(3, 4, 'George Washington'),
(4, 5, 'Paris'),
(5, 6, 'Sonnet'),
(6, 7, 'F=ma'),
(7, 8, '8'),
(8, 9, 'Energy production'),
(9, 10, '4'),
(10, 11, 'Mars'),
(11, 12, 'Sunlight'),
(12, 13, 'Tokyo'),
(13, 14, 'Albert Einstein'),
(14, 15, '100°C'),
(15, 16, 'Portuguese'),
(16, 17, 'Asia'),
(17, 18, 'Pluto'),
(18, 19, 'George Orwell'),
(19, 20, 'πr²'),
(20, 21, 'Fight infection'),
(21, 22, 'Green'),
(22, 23, 'Alexander Graham Bell'),
(23, 24, 'Au'),
(24, 25, 'Pacific'),
(25, 26, 'Photosynthesis'),
(26, 27, '93 million miles'),
(27, 28, 'CH4'),
(28, 29, 'Leonardo da Vinci'),
(29, 30, 'Ottawa'),
(30, 31, 'Blue Whale'),
(31, 32, '7'),
(32, 33, 'China'),
(33, 34, 'Mount Everest'),
(34, 35, 'Cherry Blossom'),
(35, 36, 'Neil Armstrong'),
(36, 37, 'Pacific'),
(37, 38, 'Oxygen'),
(38, 39, 'O'),
(39, 40, 'CO2'),
(40, 41, 'William Shakespeare'),
(41, 42, '−196°C'),
(42, 43, 'Nile'),
(43, 44, 'Ottawa'),
(44, 45, 'Lion'),
(45, 46, 'Albert Einstein'),
(46, 47, 'Hydrogen'),
(47, 48, 'Charles Darwin'),
(48, 49, 'Sahara'),
(49, 50, 'Alexander Fleming'),
(50, 51, 'Japan');
INSERT INTO Result (quiz_id, student_id, score)
VALUES
(1, 2, 95.00),
(2, 3, 90.00),
(3, 4, 85.00),
(4, 5, 88.00),
(5, 6, 92.00),
(6, 7, 80.00),
(7, 8, 75.00),
(8, 9, 85.00),
(9, 10, 78.00),
(10, 11, 92.00),
(11, 12, 88.00),
(12, 13, 85.00),
(13, 14, 90.00),
(14, 15, 85.00),
(15, 16, 92.00),
(16, 17, 90.00),
(17, 18, 85.00),
(18, 19, 94.00),
(19, 20, 92.00),
(20, 21, 88.00),
(21, 22, 93.00),
(22, 23, 85.00),
(23, 24, 90.00),
(24, 25, 80.00),
(25, 26, 94.00),
(26, 27, 87.00),
(27, 28, 91.00),
(28, 29, 95.00),
(29, 30, 89.00),
(30, 31, 92.00),
(31, 32, 91.00),
(32, 33, 80.00),
(33, 34, 94.00),
(34, 35, 88.00),
(35, 36, 92.00),
(36, 37, 80.00),
(37, 38, 85.00),
(38, 39, 90.00),
(39, 40, 85.00),
(40, 41, 87.00),
(41, 42, 90.00),
(42, 43, 94.00),
(43, 44, 85.00),
(44, 45, 88.00),
(45, 46, 92.00),
(46, 47, 87.00),
(47, 48, 90.00),
(48, 49, 85.00),
(49, 50, 92.00),
(50, 51, 93.00);
Step 1: Create the Database and Tables
CREATE DATABASE ecommerce_db;
USE ecommerce_db;
CREATE TABLE USER (
user_id INT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL,
password VARCHAR(255) NOT NULL,
name VARCHAR(255),
address VARCHAR(255),
phone VARCHAR(255),
role ENUM('customer', 'admin') DEFAULT 'customer',
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE PRODUCTS (
product_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
brand VARCHAR(255),
description TEXT,
price DECIMAL(10, 2),
stock INT,
category VARCHAR(255),
SKU VARCHAR(255) UNIQUE,
image_url VARCHAR(255),
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE ORDERS (
order_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
total_amount DECIMAL(10, 2),
status ENUM('pending', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES USER(user_id)
);
CREATE TABLE ORDER_ITEMS (
order_item_id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT,
product_id INT,
quantity INT NOT NULL,
price DECIMAL(10, 2),
FOREIGN KEY (order_id) REFERENCES ORDERS(order_id),
FOREIGN KEY (product_id) REFERENCES PRODUCTS(product_id)
);
CREATE TABLE REVIEWS (
review_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
product_id INT,
rating INT CHECK (rating BETWEEN 1 AND 5),
comment TEXT,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES USER(user_id),
FOREIGN KEY (product_id) REFERENCES PRODUCTS(product_id)
);
Step 2: Insert Dummy Data
INSERT INTO USER (email, password, name, address, phone, role)
VALUES
('user1@example.com', 'password123', 'John Doe', '123 Elm St', '555-1001', 'customer'),
('user2@example.com', 'password123', 'Jane Smith', '456 Oak St', '555-1002', 'customer'),
('user3@example.com', 'password123', 'Alice Brown', '789 Pine St', '555-1003', 'customer'),
('user4@example.com', 'password123', 'Bob Johnson', '101 Maple St', '555-1004', 'customer'),
('user5@example.com', 'password123', 'Charlie Davis', '202 Birch St', '555-1005', 'customer'),
('user6@example.com', 'password123', 'David Evans', '303 Cedar St', '555-1006', 'admin'),
('user7@example.com', 'password123', 'Eva Wilson', '404 Willow St', '555-1007', 'customer'),
('user8@example.com', 'password123', 'Frank Moore', '505 Redwood St', '555-1008', 'customer'),
('user9@example.com', 'password123', 'Grace Taylor', '606 Spruce St', '555-1009', 'customer'),
('user10@example.com', 'password123', 'Harry Lee', '707 Chestnut St', '555-1010', 'customer'),
('user11@example.com', 'password123', 'Isla Harris', '808 Birchwood St', '555-1011', 'customer'),
('user12@example.com', 'password123', 'Jack Clark', '909 Maplewood St', '555-1012', 'customer'),
('user13@example.com', 'password123', 'Kathy Robinson', '101 Oakwood St', '555-1013', 'customer'),
('user14@example.com', 'password123', 'Leo Martinez', '202 Pinewood St', '555-1014', 'customer'),
('user15@example.com', 'password123', 'Mona Wright', '303 Cedarwood St', '555-1015', 'admin'),
('user16@example.com', 'password123', 'Nancy Young', '404 Redwoodwood St', '555-1016', 'customer'),
('user17@example.com', 'password123', 'Oscar King', '505 Chestnutwood St', '555-1017', 'customer'),
('user18@example.com', 'password123', 'Pamela Scott', '606 Willowwood St', '555-1018', 'customer'),
('user19@example.com', 'password123', 'Quinn Adams', '707 Pinewood St', '555-1019', 'customer'),
('user20@example.com', 'password123', 'Rita Nelson', '808 Maplewood St', '555-1020', 'customer'),
('user21@example.com', 'password123', 'Sammy Phillips', '909 Birchwood St', '555-1021', 'customer'),
('user22@example.com', 'password123', 'Tommy Davis', '101 Oakwood St', '555-1022', 'customer'),
('user23@example.com', 'password123', 'Uma Gonzalez', '202 Redwoodwood St', '555-1023', 'admin'),
('user24@example.com', 'password123', 'Vera Brooks', '303 Sprucewood St', '555-1024', 'customer'),
('user25@example.com', 'password123', 'Willie Harris', '404 Cedarwood St', '555-1025', 'customer'),
('user26@example.com', 'password123', 'Xander Evans', '505 Pinewood St', '555-1026', 'customer'),
('user27@example.com', 'password123', 'Yara Stewart', '606 Maplewood St', '555-1027', 'customer'),
('user28@example.com', 'password123', 'Zane Simmons', '707 Oakwood St', '555-1028', 'customer'),
('user29@example.com', 'password123', 'Ava Johnson', '808 Pinewood St', '555-1029', 'customer'),
('user30@example.com', 'password123', 'Bryce Walker', '909 Cedarwood St', '555-1030', 'customer'),
('user31@example.com', 'password123', 'Carmen Evans', '101 Birchwood St', '555-1031', 'customer'),
('user32@example.com', 'password123', 'Derek White', '202 Redwood St', '555-1032', 'customer'),
('user33@example.com', 'password123', 'Eliza Martinez', '303 Sprucewood St', '555-1033', 'customer'),
('user34@example.com', 'password123', 'Freddie Scott', '404 Oakwood St', '555-1034', 'customer'),
('user35@example.com', 'password123', 'Gwen Cooper', '505 Maplewood St', '555-1035', 'admin'),
('user36@example.com', 'password123', 'Harvey Carter', '606 Birchwood St', '555-1036', 'customer'),
('user37@example.com', 'password123', 'Ivy Perry', '707 Redwoodwood St', '555-1037', 'customer'),
('user38@example.com', 'password123', 'Jake Mitchell', '808 Pinewood St', '555-1038', 'customer'),
('user39@example.com', 'password123', 'Kyle Palmer', '909 Oakwood St', '555-1039', 'customer'),
('user40@example.com', 'password123', 'Lily Foster', '101 Chestnutwood St', '555-1040', 'customer'),
('user41@example.com', 'password123', 'Mark Diaz', '202 Willowwood St', '555-1041', 'customer'),
('user42@example.com', 'password123', 'Nina Clark', '303 Pinewood St', '555-1042', 'customer'),
('user43@example.com', 'password123', 'Oscar Green', '404 Cedarwood St', '555-1043', 'admin'),
('user44@example.com', 'password123', 'Paula Knight', '505 Birchwood St', '555-1044', 'customer'),
('user45@example.com', 'password123', 'Quincy Lewis', '606 Oakwood St', '555-1045', 'customer'),
('user46@example.com', 'password123', 'Randy Hall', '707 Maplewood St', '555-1046', 'customer'),
('user47@example.com', 'password123', 'Sandy Moore', '808 Pinewood St', '555-1047', 'customer'),
('user48@example.com', 'password123', 'Tina Taylor', '909 Chestnutwood St', '555-1048', 'customer'),
('user49@example.com', 'password123', 'Ursula Griffin', '101 Redwoodwood St', '555-1049', 'customer'),
('user50@example.com', 'password123', 'Vince King', '202 Willowwood St', '555-1050', 'customer');
INSERT INTO PRODUCTS (name, brand, description, price, stock, category, SKU, image_url)
VALUES
('Laptop', 'Dell', 'A powerful laptop for gaming and work', 999.99, 10, 'Electronics', 'LAP123', 'laptop.jpg'),
('Smartphone', 'Samsung', 'Latest smartphone with amazing features', 799.99, 25, 'Electronics', 'PHO123', 'phone.jpg'),
('Headphones', 'Sony', 'Noise-cancelling headphones for ultimate sound', 149.99, 50, 'Electronics', 'HEA123', 'headphones.jpg'),
('Watch', 'Rolex', 'Luxury wristwatch with timeless design', 4999.99, 5, 'Fashion', 'WAT123', 'watch.jpg'),
('Shirt', 'Nike', 'Comfortable and stylish athletic shirt', 39.99, 100, 'Fashion', 'SHI123', 'shirt.jpg'),
('Shoes', 'Adidas', 'Running shoes for comfort and speed', 89.99, 50, 'Fashion', 'SHO123', 'shoes.jpg'),
('Couch', 'Ikea', 'Modern and stylish couch for your living room', 349.99, 20, 'Furniture', 'COU123', 'couch.jpg'),
('Lamp', 'Philips', 'Bright LED lamp for your home', 29.99, 50, 'Furniture', 'LAM123', 'lamp.jpg'),
('Table', 'Ikea', 'Elegant dining table for family meals', 249.99, 30, 'Furniture', 'TAB1233', 'table.jpg'),
('Chair', 'Herman Miller', 'Ergonomic chair for your office', 599.99, 10, 'Furniture', 'CHA123', 'chair.jpg'),
('Blender', 'Ninja', 'High-speed blender for smoothies and soups', 89.99, 15, 'Home Appliances', 'BLE123', 'blender.jpg'),
('Toaster', 'Cuisinart', 'Smart toaster with digital controls', 49.99, 40, 'Home Appliances', 'TOA123', 'toaster.jpg'),
('Microwave', 'Samsung', 'Microwave oven with quick cooking features', 149.99, 30, 'Home Appliances', 'MIC123', 'microwave.jpg'),
('Refrigerator', 'LG', 'Energy-efficient refrigerator for your kitchen', 999.99, 10, 'Home Appliances', 'REF123', 'fridge.jpg'),
('Washing Machine', 'Whirlpool', 'Automatic washing machine with advanced features', 399.99, 20, 'Home Appliances', 'WAS123', 'washer.jpg'),
('Oven', 'Bosch', 'Convection oven for precise cooking', 499.99, 10, 'Home Appliances', 'OVE123', 'oven.jpg'),
('Car', 'Toyota', 'Reliable car with great fuel economy', 24999.99, 3, 'Automotive', 'CAR123', 'car.jpg'),
('Bike', 'Trek', 'Mountain bike for outdoor adventures', 799.99, 20, 'Sports', 'BIK123', 'bike.jpg'),
('Helmet', 'Bell', 'Safety helmet for biking and sports', 49.99, 40, 'Sports', 'HEM123', 'helmet.jpg'),
('Tennis Racket', 'Wilson', 'Tennis racket for professional players', 149.99, 15, 'Sports', 'TEN123', 'racket.jpg'),
('Golf Club', 'Callaway', 'High-performance golf club for precision', 299.99, 5, 'Sports', 'GOL123', 'golf_club.jpg'),
('Camera', 'Canon', 'DSLR camera for high-quality photography', 899.99, 10, 'Electronics', 'CAM123', 'camera.jpg'),
('Tripod', 'Manfrotto', 'Sturdy tripod for professional photography', 129.99, 50, 'Electronics', 'TRI123', 'tripod.jpg'),
('Drone', 'DJI', 'Aerial drone for photography and fun', 699.99, 8, 'Electronics', 'DRO123', 'drone.jpg'),
('Game Console', 'Sony', 'PlayStation 5 with amazing game library', 499.99, 30, 'Electronics', 'GAM123', 'console.jpg'),
('Action Camera', 'GoPro', 'Waterproof camera for action shots', 249.99, 12, 'Electronics', 'ACT123', 'gopro.jpg'),
('Tablet', 'Apple', 'iPad for work and entertainment', 499.99, 25, 'Electronics', 'TAB123', 'ipad.jpg'),
('TV', 'Samsung', '4K UHD Smart TV for an immersive experience', 799.99, 20, 'Electronics', 'TV123', 'tv.jpg'),
('Printer', 'HP', 'All-in-one printer for home and office use', 129.99, 50, 'Electronics', 'PRI123', 'printer.jpg'),
('Coffee Maker', 'Nespresso', 'Automatic coffee maker for coffee lovers', 179.99, 40, 'Home Appliances', 'COF123', 'coffee_maker.jpg'),
('Tea Kettle', 'Breville', 'Electric kettle for fast boiling', 69.99, 30, 'Home Appliances', 'TEA123', 'tea_kettle.jpg'),
('Iron', 'Philips', 'Steam iron for wrinkle-free clothes', 49.99, 50, 'Home Appliances', 'IRO123', 'iron.jpg'),
('Hair Dryer', 'Dyson', 'Powerful hair dryer for faster drying', 399.99, 10, 'Beauty', 'HAI123', 'hair_dryer.jpg'),
('Shampoo', 'Head & Shoulders', 'Shampoo for healthy scalp and hair', 5.99, 100, 'Beauty', 'SHA123', 'shampoo.jpg'),
('Conditioner', 'Pantene', 'Conditioner for silky smooth hair', 5.99, 100, 'Beauty', 'CON123', 'conditioner.jpg'),
('Perfume', 'Chanel', 'Luxury fragrance for women', 99.99, 10, 'Beauty', 'PER123', 'perfume.jpg'),
('Lipstick', 'MAC', 'Long-lasting lipstick for a perfect look', 19.99, 50, 'Beauty', 'LIP123', 'lipstick.jpg'),
('Sunglasses', 'Ray-Ban', 'Classic sunglasses for outdoor style', 149.99, 25, 'Fashion', 'SUN123', 'sunglasses.jpg'),
('Jeans', 'Levi', 'Comfortable and stylish jeans', 59.99, 40, 'Fashion', 'JEAN123', 'jeans.jpg'),
('Sweater', 'H&M', 'Soft sweater for cozy winter days', 29.99, 30, 'Fashion', 'SWE123', 'sweater.jpg'),
('Jacket', 'The North Face', 'Waterproof jacket for outdoor adventures', 129.99, 15, 'Fashion', 'JAC123', 'jacket.jpg'),
('Pants', 'Uniqlo', 'Casual pants for everyday wear', 39.99, 50, 'Fashion', 'PAN123', 'pants.jpg');
INSERT INTO ORDERS (user_id, total_amount, status)
VALUES
(1, 999.99, 'pending'),
(2, 799.99, 'shipped'),
(3, 149.99, 'delivered'),
(4, 4999.99, 'pending'),
(5, 39.99, 'cancelled'),
(6, 899.99, 'shipped'),
(7, 349.99, 'pending'),
(8, 129.99, 'delivered'),
(9, 29.99, 'shipped'),
(10, 499.99, 'pending'),
(11, 799.99, 'shipped'),
(12, 1199.99, 'delivered'),
(13, 49.99, 'cancelled'),
(14, 299.99, 'shipped'),
(15, 599.99, 'delivered'),
(16, 79.99, 'pending'),
(17, 229.99, 'shipped'),
(18, 149.99, 'delivered'),
(19, 99.99, 'pending'),
(20, 179.99, 'cancelled'),
(21, 249.99, 'shipped'),
(22, 1299.99, 'delivered'),
(23, 59.99, 'pending'),
(24, 99.99, 'shipped'),
(25, 289.99, 'delivered'),
(26, 199.99, 'pending'),
(27, 799.99, 'shipped'),
(28, 599.99, 'delivered'),
(29, 249.99, 'pending'),
(30, 499.99, 'shipped'),
(31, 899.99, 'delivered'),
(32, 129.99, 'pending'),
(33, 159.99, 'shipped'),
(34, 49.99, 'delivered'),
(35, 149.99, 'pending'),
(36, 999.99, 'shipped'),
(37, 799.99, 'delivered'),
(38, 99.99, 'cancelled'),
(39, 249.99, 'shipped'),
(40, 1499.99, 'delivered'),
(41, 99.99, 'pending'),
(42, 299.99, 'shipped'),
(43, 499.99, 'delivered'),
(44, 179.99, 'pending'),
(45, 219.99, 'shipped'),
(46, 159.99, 'delivered'),
(47, 249.99, 'cancelled'),
(48, 179.99, 'shipped'),
(49, 99.99, 'delivered'),
(50, 299.99, 'pending');
INSERT INTO ORDER_ITEMS (order_id, product_id, quantity, price)
VALUES
(1, 1, 1, 999.99),
(2, 2, 1, 799.99),
(3, 3, 1, 149.99),
(4, 4, 1, 4999.99),
(5, 5, 1, 39.99),
(6, 6, 1, 899.99),
(7, 7, 1, 349.99),
(8, 8, 1, 129.99),
(9, 9, 1, 29.99),
(10, 10, 1, 499.99),
(11, 11, 1, 799.99),
(12, 12, 1, 1199.99),
(13, 13, 1, 49.99),
(14, 14, 1, 299.99),
(15, 15, 1, 599.99),
(16, 16, 1, 79.99),
(17, 17, 1, 229.99),
(18, 18, 1, 149.99),
(19, 19, 1, 99.99),
(20, 20, 1, 179.99),
(21, 21, 1, 249.99),
(22, 22, 1, 1299.99),
(23, 23, 1, 59.99),
(24, 24, 1, 99.99),
(25, 25, 1, 289.99),
(26, 26, 1, 199.99),
(27, 27, 1, 799.99),
(28, 28, 1, 599.99),
(29, 29, 1, 249.99),
(30, 30, 1, 499.99),
(31, 31, 1, 899.99),
(32, 32, 1, 129.99),
(33, 33, 1, 159.99),
(34, 34, 1, 49.99),
(35, 35, 1, 149.99),
(36, 36, 1, 999.99),
(37, 37, 1, 799.99),
(38, 38, 1, 99.99),
(39, 39, 1, 249.99),
(40, 40, 1, 1499.99),
(41, 41, 1, 99.99),
(42, 42, 1, 299.99);
INSERT INTO REVIEWS (user_id, product_id, rating, comment)
VALUES
(1, 68, 5, 'Excellent laptop for gaming and work!'),
(2, 60, 4, 'Great smartphone but a bit pricey.'),
(3, 53, 5, 'Amazing sound quality, best headphones!'),
(4, 64, 5, 'A luxury watch thats worth the investment.'),
(5, 59, 4, 'Comfortable shirt for workouts.'),
(6, 52, 5, 'Fantastic shoes, very comfortable.'),
(7, 72, 3, 'Nice couch but took a long time to assemble.'),
(8, 77, 5, 'Perfect lamp for reading at night.'),
(9, 49, 4, 'Sturdy table, fits perfectly in my dining room.'),
(10, 66, 5, 'Super comfortable ergonomic chair.'),
(11, 67, 4, 'Blender is fast and efficient, good for smoothies.'),
(12, 63, 5, 'Great toaster, perfect toast every time.'),
(13, 75, 5, 'Microwave works great for quick meals.'),
(14, 45, 5, 'Excellent refrigerator with a lot of space.'),
(15, 61, 4, 'Washing machine works well but is a bit loud.'),
(16, 74, 5, 'Oven is great for cooking everything from roasts to cakes.'),
(17, 83, 5, 'The car is reliable and great for long trips.'),
(18, 81, 5, 'Love my new bike, perfect for trails.'),
(19, 50, 4, 'Helmet fits well, feels secure while biking.'),
(20, 43, 5, 'Excellent racket, great for professionals.'),
(21, 79, 5, 'Perfect golf club for precise shots.'),
(22, 55, 5, 'Awesome camera, takes fantastic photos.'),
(23, 58, 5, 'Tripod is sturdy and easy to adjust.'),
(24, 84, 4, 'Drone is fun to use, but battery life could be better.'),
(25, 78, 5, 'PS5 is fantastic, best console experience.'),
(26, 44, 5, 'Action camera is durable and takes amazing shots.'),
(27, 71, 5, 'iPad is perfect for both work and entertainment.'),
(28, 56, 4, 'Smart TV works well, but apps can be a bit slow at times.'),
(29, 76, 5, 'Printer is efficient and quick.'),
(30, 47, 5, 'Coffee maker is a lifesaver in the mornings.'),
(31, 48, 5, 'Tea kettle boils water quickly and is easy to use.'),
(32, 80, 5, 'Iron works well, clothes come out wrinkle-free.'),
(33, 82, 5, 'Hair dryer dries quickly and is easy to handle.'),
(34, 69, 5, 'Shampoo leaves my hair feeling soft and clean.'),
(35, 51, 5, 'Conditioner makes my hair super silky.'),
(36, 73, 5, 'The perfume smells amazing and lasts for hours.'),
(37, 62, 5, 'Lipstick is long-lasting and comfortable to wear.'),
(38, 54, 5, 'Sunglasses are stylish and provide good UV protection.'),
(39, 65, 5, 'Jeans fit perfectly and are very comfortable.'),
(40, 70, 5, 'Sweater is soft and perfect for winter days.'),
(41, 57, 5, 'Jacket is very warm and waterproof.'),
(42, 46, 4, 'Pants are great for casual wear, but a bit tight around the waist.');
1 Create Tables and Define Relationships
CREATE DATABASE jobboard_db;
USE jobboard_db;
CREATE TABLE Authentication (
authentication_id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(255),
email VARCHAR(255),
password_hash VARCHAR(255),
role ENUM('user', 'admin'),
created_at DATETIME,
updated_at DATETIME,
deleted_at DATETIME
);
CREATE TABLE User (
user_id INT AUTO_INCREMENT PRIMARY KEY,
authentication_id INT,
name VARCHAR(255),
birth_date DATE,
skills TEXT,
work_experience TEXT,
updated_at DATETIME,
FOREIGN KEY (authentication_id) REFERENCES Authentication(authentication_id)
);
CREATE TABLE Job_Posting (
job_id INT AUTO_INCREMENT PRIMARY KEY,
employer_id INT,
job_title VARCHAR(255),
job_description TEXT,
location VARCHAR(255),
category VARCHAR(255),
industry VARCHAR(255),
min_salary DECIMAL(10, 2),
max_salary DECIMAL(10, 2),
created_at DATETIME,
updated_at DATETIME,
deleted_at DATETIME
);
CREATE TABLE Job_Performance (
performance_id INT AUTO_INCREMENT PRIMARY KEY,
job_id INT,
applicants_count INT,
views_count INT,
open_date DATETIME,
close_date DATETIME,
time_to_fill INT,
updated_at DATETIME,
FOREIGN KEY (job_id) REFERENCES Job_Posting(job_id)
);
CREATE TABLE Job_Interaction (
interaction_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
job_id INT,
interaction_type ENUM('view', 'apply', 'save'),
interaction_date DATETIME,
is_applied BOOLEAN,
FOREIGN KEY (user_id) REFERENCES User(user_id),
FOREIGN KEY (job_id) REFERENCES Job_Posting(job_id)
);
CREATE TABLE Application (
application_id INT AUTO_INCREMENT PRIMARY KEY,
job_seeker_id INT,
job_id INT,
resume BLOB,
status ENUM('pending', 'interview', 'rejected', 'accepted'),
skills TEXT,
work_experience TEXT,
applied_at DATETIME,
updated_at DATETIME,
deleted_at DATETIME,
FOREIGN KEY (job_seeker_id) REFERENCES User(user_id),
FOREIGN KEY (job_id) REFERENCES Job_Posting(job_id)
);
CREATE TABLE Message (
message_id INT AUTO_INCREMENT PRIMARY KEY,
sender_id INT,
recipient_id INT,
application_id INT,
message_text TEXT,
is_read BOOLEAN,
message_type ENUM('text', 'image', 'file'),
sent_at DATETIME,
deleted_at DATETIME,
FOREIGN KEY (sender_id) REFERENCES User(user_id),
FOREIGN KEY (recipient_id) REFERENCES User(user_id),
FOREIGN KEY (application_id) REFERENCES Application(application_id)
);
2. Insert Dummy Data
INSERT INTO Authentication (username, email, password_hash, role, created_at, updated_at, deleted_at)
VALUES
('john_doe', 'john.doe@example.com', 'hashed_password_1', 'user', NOW(), NOW(), NULL),
('jane_smith', 'jane.smith@example.com', 'hashed_password_2', 'user', NOW(), NOW(), NULL),
('alice_johnson', 'alice.johnson@example.com', 'hashed_password_3', 'user', NOW(), NOW(), NULL),
('bob_williams', 'bob.williams@example.com', 'hashed_password_4', 'user', NOW(), NOW(), NULL),
('admin_user', 'admin@example.com', 'admin_password', 'admin', NOW(), NOW(), NULL),
('david_miller', 'david.miller@example.com', 'hashed_password_5', 'user', NOW(), NOW(), NULL),
('emily_white', 'emily.white@example.com', 'hashed_password_6', 'user', NOW(), NOW(), NULL),
('paul_brown', 'paul.brown@example.com', 'hashed_password_7', 'user', NOW(), NOW(), NULL),
('susan_wilson', 'susan.wilson@example.com', 'hashed_password_8', 'user', NOW(), NOW(), NULL),
('james_moore', 'james.moore@example.com', 'hashed_password_9', 'user', NOW(), NOW(), NULL),
('mary_taylor', 'mary.taylor@example.com', 'hashed_password_10', 'user', NOW(), NOW(), NULL),
('luke_anderson', 'luke.anderson@example.com', 'hashed_password_11', 'user', NOW(), NOW(), NULL),
('lucy_hernandez', 'lucy.hernandez@example.com', 'hashed_password_12', 'user', NOW(), NOW(), NULL),
('michael_smith', 'michael.smith@example.com', 'hashed_password_13', 'user', NOW(), NOW(), NULL),
('laura_clark', 'laura.clark@example.com', 'hashed_password_14', 'user', NOW(), NOW(), NULL),
('daniel_jackson', 'daniel.jackson@example.com', 'hashed_password_15', 'user', NOW(), NOW(), NULL),
('olivia_lee', 'olivia.lee@example.com', 'hashed_password_16', 'user', NOW(), NOW(), NULL),
('jack_king', 'jack.king@example.com', 'hashed_password_17', 'user', NOW(), NOW(), NULL),
('lily_harris', 'lily.harris@example.com', 'hashed_password_18', 'user', NOW(), NOW(), NULL),
('henry_garcia', 'henry.garcia@example.com', 'hashed_password_19', 'user', NOW(), NOW(), NULL),
('victoria_fox', 'victoria.fox@example.com', 'hashed_password_20', 'user', NOW(), NOW(), NULL),
('william_martin', 'william.martin@example.com', 'hashed_password_21', 'user', NOW(), NOW(), NULL),
('joseph_lee', 'joseph.lee@example.com', 'hashed_password_22', 'user', NOW(), NOW(), NULL),
('charlotte_davis', 'charlotte.davis@example.com', 'hashed_password_23', 'user', NOW(), NOW(), NULL),
('oliver_taylor', 'oliver.taylor@example.com', 'hashed_password_24', 'user', NOW(), NOW(), NULL),
('scarlett_evans', 'scarlett.evans@example.com', 'hashed_password_25', 'user', NOW(), NOW(), NULL),
('sebastian_roberts', 'sebastian.roberts@example.com', 'hashed_password_26', 'user', NOW(), NOW(), NULL),
('amelia_james', 'amelia.james@example.com', 'hashed_password_27', 'user', NOW(), NOW(), NULL),
('benjamin_scott', 'benjamin.scott@example.com', 'hashed_password_28', 'user', NOW(), NOW(), NULL),
('ella_moore', 'ella.moore@example.com', 'hashed_password_29', 'user', NOW(), NOW(), NULL),
('lucas_jones', 'lucas.jones@example.com', 'hashed_password_30', 'user', NOW(), NOW(), NULL),
('mila_rodriquez', 'mila.rodriquez@example.com', 'hashed_password_31', 'user', NOW(), NOW(), NULL),
('ryan_walker', 'ryan.walker@example.com', 'hashed_password_32', 'user', NOW(), NOW(), NULL),
('lucy_garcia', 'lucy.garcia@example.com', 'hashed_password_33', 'user', NOW(), NOW(), NULL),
('elizabeth_jones', 'elizabeth.jones@example.com', 'hashed_password_34', 'user', NOW(), NOW(), NULL),
('nathaniel_williams', 'nathaniel.williams@example.com', 'hashed_password_35', 'user', NOW(), NOW(), NULL),
('grace_wilson', 'grace.wilson@example.com', 'hashed_password_36', 'user', NOW(), NOW(), NULL),
('josephine_miller', 'josephine.miller@example.com', 'hashed_password_37', 'user', NOW(), NOW(), NULL),
('thomas_davis', 'thomas.davis@example.com', 'hashed_password_38', 'user', NOW(), NOW(), NULL),
('isabella_roberts', 'isabella.roberts@example.com', 'hashed_password_39', 'user', NOW(), NOW(), NULL),
('henry_evans', 'henry.evans@example.com', 'hashed_password_40', 'user', NOW(), NOW(), NULL),
('elizabeth_hall', 'elizabeth.hall@example.com', 'hashed_password_41', 'user', NOW(), NOW(), NULL),
('sophie_martin', 'sophie.martin@example.com', 'hashed_password_42', 'user', NOW(), NOW(), NULL),
('charles_anderson', 'charles.anderson@example.com', 'hashed_password_43', 'user', NOW(), NOW(), NULL),
('chloe_stevens', 'chloe.stevens@example.com', 'hashed_password_44', 'user', NOW(), NOW(), NULL),
('jackson_garcia', 'jackson.garcia@example.com', 'hashed_password_45', 'user', NOW(), NOW(), NULL),
('ella_williams', 'ella.williams@example.com', 'hashed_password_46', 'user', NOW(), NOW(), NULL),
('thomas_brown', 'thomas.brown@example.com', 'hashed_password_47', 'user', NOW(), NOW(), NULL),
('riley_jackson', 'riley.jackson@example.com', 'hashed_password_48', 'user', NOW(), NOW(), NULL),
('ella_rodriquez', 'ella.rodriquez@example.com', 'hashed_password_49', 'user', NOW(), NOW(), NULL),
('nathan_scott', 'nathan.scott@example.com', 'hashed_password_50', 'user', NOW(), NOW(), NULL);
INSERT INTO User (authentication_id, name, birth_date, skills, work_experience, updated_at)
VALUES
(1, 'John Doe', '1990-05-15', 'Java, SQL', 'Software Developer at ABC Corp', NOW()),
(2, 'Jane Smith', '1985-03-12', 'Python, Machine Learning', 'Data Scientist at XYZ Inc.', NOW()),
(3, 'Alice Johnson', '1992-07-22', 'HTML, CSS, JavaScript', 'Frontend Developer at WebWorks', NOW()),
(4, 'Bob Williams', '1988-02-01', 'Java, Spring, Hibernate', 'Java Developer at TechSolutions', NOW()),
(5, 'Admin User', '1980-11-30', 'Management, HR', 'HR Manager at Admin Corp', NOW()),
(6, 'David Miller', '1990-04-10', 'C++, Java', 'Software Engineer at CodeBase', NOW()),
(7, 'Emily White', '1994-08-18', 'React, Node.js', 'Frontend Developer at Innovate Inc.', NOW()),
(8, 'Paul Brown', '1987-06-17', 'PHP, Laravel', 'Backend Developer at CodeCraft', NOW()),
(9, 'Susan Wilson', '1991-09-30', 'Marketing, SEO', 'Marketing Specialist at GrowthCorp', NOW()),
(10, 'James Moore', '1989-01-25', 'JavaScript, Angular', 'Web Developer at NextGen', NOW()),
(11, 'Mary Taylor', '1993-12-14', 'Ruby on Rails, JavaScript', 'Full Stack Developer at CloudNet', NOW()),
(12, 'Luke Anderson', '1990-11-09', 'SQL, Python', 'Data Analyst at DataLab', NOW()),
(13, 'Lucy Hernandez', '1992-05-07', 'UX Design, Sketch', 'UX Designer at DesignCo', NOW()),
(14, 'Michael Smith', '1987-07-21', 'PHP, MySQL', 'Backend Developer at WebSolutions', NOW()),
(15, 'Laura Clark', '1994-10-02', 'Product Management', 'Product Manager at DevHub', NOW()),
(16, 'Daniel Jackson', '1986-09-11', 'Java, Spring', 'Senior Java Developer at TechTonic', NOW()),
(17, 'Olivia Lee', '1993-03-05', 'SQL, Data Analysis', 'Business Intelligence Analyst at DataEdge', NOW()),
(18, 'Jack King', '1991-06-15', 'React, Redux', 'Frontend Developer at StartUpCo', NOW()),
(19, 'Lily Harris', '1989-04-20', 'SEO, Content Marketing', 'Content Marketing Specialist at BrightStar', NOW()),
(20, 'Henry Garcia', '1985-01-14', 'Sales, Business Development', 'Sales Manager at TechTrade', NOW()),
(21, 'Victoria Fox', '1992-08-11', 'Product Management', 'Project Manager at DevWorks', NOW()),
(22, 'William Martin', '1987-11-23', 'Machine Learning, Python', 'Data Scientist at BigDataTech', NOW()),
(23, 'Joseph Lee', '1984-05-25', 'C#, .NET', 'Software Developer at .NET Solutions', NOW()),
(24, 'Charlotte Davis', '1990-10-13', 'React, JavaScript', 'Frontend Developer at WebStar', NOW()),
(25, 'Oliver Taylor', '1991-04-19', 'HTML, CSS', 'Web Developer at Tech World', NOW()),
(26, 'Scarlett Evans', '1993-07-30', 'Data Visualization, Python', 'Data Analyst at DataPlus', NOW()),
(27, 'Sebastian Roberts', '1988-12-05', 'Business Analysis, SQL', 'Business Analyst at BizTech', NOW()),
(28, 'Amelia James', '1992-10-22', 'Data Science, R', 'Data Scientist at Alpha Corp', NOW()),
(29, 'Benjamin Scott', '1987-02-17', 'Java, Spring', 'Java Developer at SoftTech', NOW()),
(30, 'Ella Moore', '1994-09-01', 'JavaScript, Node.js', 'Full Stack Developer at WebTeam', NOW()),
(31, 'Lucas Jones', '1989-03-13', 'Marketing, Advertising', 'Marketing Lead at MarketPro', NOW()),
(32, 'Mila Rodriquez', '1990-06-08', 'UX Design, Web Design', 'UX/UI Designer at DesignPro', NOW()),
(33, 'Ryan Walker', '1986-07-17', 'Project Management', 'Project Manager at TeamWorks', NOW()),
(34, 'Lucy Garcia', '1994-12-25', 'Python, SQL', 'Data Analyst at DataWorks', NOW()),
(35, 'Elizabeth Jones', '1992-01-15', 'JavaScript, HTML', 'Frontend Developer at WebExperts', NOW()),
(36, 'Nathaniel Williams', '1985-11-01', 'PHP, Laravel', 'Backend Developer at DevLabs', NOW()),
(37, 'Grace Wilson', '1993-09-12', 'Java, Spring', 'Software Engineer at GlobalTech', NOW()),
(38, 'Josephine Miller', '1991-06-09', 'SEO, SEM', 'SEO Specialist at SearchGuru', NOW()),
(39, 'Thomas Davis', '1990-02-22', 'PHP, MySQL', 'Software Developer at WebMasters', NOW()),
(40, 'Isabella Roberts', '1988-04-14', 'Ruby, Rails', 'Ruby on Rails Developer at CodeX', NOW()),
(41, 'Henry Evans', '1992-08-23', 'React, Node.js', 'Frontend Developer at TechForce', NOW()),
(42, 'Elizabeth Hall', '1993-11-30', 'Product Management', 'Product Manager at DevLeads', NOW()),
(43, 'Sophie Martin', '1985-01-20', 'Project Management', 'Senior Project Manager at WebWave', NOW()),
(44, 'Charles Anderson', '1994-06-03', 'Python, R', 'Data Scientist at DataStream', NOW()),
(45, 'Chloe Stevens', '1992-07-14', 'C++, Python', 'Software Developer at CodeMasters', NOW()),
(46, 'Jackson Garcia', '1989-12-11', 'Business Development', 'Sales Director at SalesForce', NOW()),
(47, 'Ella Williams', '1994-09-29', 'Marketing, Content', 'Content Specialist at BrightIdeas', NOW()),
(48, 'Thomas Brown', '1991-03-19', 'Machine Learning, Python', 'Data Scientist at DataNow', NOW()),
(49, 'Riley Jackson', '1987-04-26', 'Java, Angular', 'Software Engineer at DevNet', NOW()),
(50, 'Ella Rodriquez', '1992-02-17', 'SQL, R', 'Data Analyst at InfoTech', NOW());
INSERT INTO Job_Posting (employer_id, job_title, job_description, location, category, industry, min_salary, max_salary, created_at, updated_at, deleted_at)
VALUES
(5, 'Software Developer', 'Developing new features for web applications', 'San Francisco, CA', 'Software Engineering', 'Technology', 70000, 100000, NOW(), NOW(), NULL),
(6, 'Data Scientist', 'Analyze and model data for better decision making', 'New York, NY', 'Data Science', 'Technology', 80000, 120000, NOW(), NOW(), NULL),
(7, 'Frontend Developer', 'Create beautiful and responsive user interfaces', 'Chicago, IL', 'Web Development', 'Technology', 65000, 95000, NOW(), NOW(), NULL),
(8, 'Backend Developer', 'Build and maintain server-side applications', 'Austin, TX', 'Software Engineering', 'Technology', 70000, 100000, NOW(), NOW(), NULL),
(9, 'SEO Specialist', 'Improve website rankings and organic search traffic', 'Remote', 'Digital Marketing', 'Marketing', 45000, 75000, NOW(), NOW(), NULL),
(10, 'Project Manager', 'Manage software projects and teams', 'Los Angeles, CA', 'Project Management', 'Technology', 90000, 130000, NOW(), NOW(), NULL),
(11, 'UX Designer', 'Design user-centric interfaces and experiences', 'Remote', 'Design', 'Technology', 70000, 95000, NOW(), NOW(), NULL),
(12, 'Full Stack Developer', 'Develop both front-end and back-end features', 'Seattle, WA', 'Software Engineering', 'Technology', 85000, 120000, NOW(), NOW(), NULL),
(13, 'Content Marketing Specialist', 'Create and optimize content for marketing', 'Boston, MA', 'Marketing', 'Marketing', 50000, 80000, NOW(), NOW(), NULL),
(14, 'Senior Java Developer', 'Lead development of Java applications', 'Austin, TX', 'Software Engineering', 'Technology', 100000, 140000, NOW(), NOW(), NULL),
(15, 'Web Developer', 'Build and maintain responsive websites', 'Denver, CO', 'Web Development', 'Technology', 60000, 90000, NOW(), NOW(), NULL),
(16, 'Data Analyst', 'Perform data analysis and create reports', 'San Jose, CA', 'Data Science', 'Technology', 55000, 85000, NOW(), NOW(), NULL),
(17, 'Sales Manager', 'Manage the sales team and develop strategies', 'Chicago, IL', 'Sales', 'Technology', 65000, 100000, NOW(), NOW(), NULL),
(18, 'Marketing Lead', 'Lead the marketing efforts for the company', 'Miami, FL', 'Marketing', 'Marketing', 80000, 120000, NOW(), NOW(), NULL),
(19, 'Software Engineer', 'Develop software applications and systems', 'Houston, TX', 'Software Engineering', 'Technology', 75000, 105000, NOW(), NOW(), NULL),
(20, 'Ruby Developer', 'Develop Ruby-based applications', 'Remote', 'Software Engineering', 'Technology', 80000, 110000, NOW(), NOW(), NULL),
(21, 'Business Analyst', 'Analyze business requirements and define solutions', 'Dallas, TX', 'Business Analysis', 'Consulting', 70000, 95000, NOW(), NOW(), NULL),
(22, 'DevOps Engineer', 'Automate and manage deployment processes', 'Los Angeles, CA', 'Software Engineering', 'Technology', 85000, 115000, NOW(), NOW(), NULL),
(23, 'Sales Director', 'Lead sales efforts and grow the customer base', 'San Francisco, CA', 'Sales', 'Technology', 120000, 160000, NOW(), NOW(), NULL),
(24, 'Senior Web Developer', 'Develop high-quality web applications', 'Chicago, IL', 'Web Development', 'Technology', 90000, 130000, NOW(), NOW(), NULL),
(25, 'Python Developer', 'Develop Python applications and services', 'New York, NY', 'Software Engineering', 'Technology', 75000, 105000, NOW(), NOW(), NULL),
(26, 'Cloud Engineer', 'Design and manage cloud-based infrastructure', 'Remote', 'Cloud Computing', 'Technology', 80000, 120000, NOW(), NOW(), NULL),
(27, 'HR Manager', 'Lead the HR department and manage employee relations', 'Remote', 'Human Resources', 'Business', 60000, 85000, NOW(), NOW(), NULL),
(28, 'Business Development Manager', 'Develop new business opportunities and partnerships', 'San Francisco, CA', 'Sales', 'Technology', 70000, 100000, NOW(), NOW(), NULL),
(29, 'React Developer', 'Develop React-based front-end applications', 'Austin, TX', 'Software Engineering', 'Technology', 70000, 100000, NOW(), NOW(), NULL),
(30, 'Data Engineer', 'Build data pipelines and ETL processes', 'Denver, CO', 'Data Science', 'Technology', 85000, 115000, NOW(), NOW(), NULL),
(31, 'Mobile Developer', 'Develop mobile applications for iOS and Android', 'Boston, MA', 'Mobile Development', 'Technology', 75000, 105000, NOW(), NOW(), NULL),
(32, 'UI Designer', 'Design and improve user interfaces', 'Remote', 'Design', 'Technology', 70000, 95000, NOW(), NOW(), NULL),
(33, 'Machine Learning Engineer', 'Develop and deploy machine learning models', 'San Francisco, CA', 'Data Science', 'Technology', 95000, 130000, NOW(), NOW(), NULL),
(34, 'Software Architect', 'Design and implement complex software systems', 'Remote', 'Software Engineering', 'Technology', 120000, 150000, NOW(), NOW(), NULL),
(35, 'Business Intelligence Analyst', 'Analyze business data and provide insights', 'Chicago, IL', 'Data Science', 'Technology', 75000, 105000, NOW(), NOW(), NULL),
(36, 'Front-End Developer', 'Create modern, responsive web applications', 'Dallas, TX', 'Web Development', 'Technology', 65000, 95000, NOW(), NOW(), NULL),
(37, 'SEO Specialist', 'Optimize websites for search engine ranking', 'Miami, FL', 'Digital Marketing', 'Marketing', 45000, 75000, NOW(), NOW(), NULL),
(38, 'System Administrator', 'Manage and maintain computer systems and networks', 'Los Angeles, CA', 'IT Support', 'Technology', 60000, 85000, NOW(), NOW(), NULL),
(39, 'Network Engineer', 'Design and maintain computer networks', 'San Jose, CA', 'IT Support', 'Technology', 70000, 100000, NOW(), NOW(), NULL),
(40, 'Operations Manager', 'Oversee daily operations of the company', 'Chicago, IL', 'Operations', 'Business', 80000, 120000, NOW(), NOW(), NULL),
(41, 'Marketing Manager', 'Lead marketing strategies and campaigns', 'Remote', 'Marketing', 'Marketing', 80000, 110000, NOW(), NOW(), NULL),
(42, 'Software Tester', 'Test and ensure the quality of software applications', 'Boston, MA', 'Quality Assurance', 'Technology', 60000, 85000, NOW(), NOW(), NULL),
(43, 'Chief Technology Officer', 'Lead the technical strategy of the company', 'San Francisco, CA', 'Leadership', 'Technology', 150000, 200000, NOW(), NOW(), NULL),
(44, 'Content Writer', 'Write and edit content for digital marketing', 'Austin, TX', 'Content', 'Marketing', 45000, 70000, NOW(), NOW(), NULL),
(45, 'Customer Support Manager', 'Manage customer service team and strategies', 'New York, NY', 'Customer Support', 'Technology', 65000, 95000, NOW(), NOW(), NULL),
(46, 'Product Manager', 'Oversee product development and strategy', 'Remote', 'Product Management', 'Technology', 95000, 130000, NOW(), NOW(), NULL),
(47, 'DevOps Engineer', 'Manage deployment and infrastructure automation', 'San Francisco, CA', 'Software Engineering', 'Technology', 85000, 120000, NOW(), NOW(), NULL),
(48, 'Senior Data Scientist', 'Lead data science projects and teams', 'Chicago, IL', 'Data Science', 'Technology', 110000, 140000, NOW(), NOW(), NULL),
(49, 'Talent Acquisition Specialist', 'Handle recruitment for tech roles', 'Remote', 'Human Resources', 'Technology', 60000, 85000, NOW(), NOW(), NULL),
(50, 'Sales Representative', 'Manage customer accounts and increase sales', 'Austin, TX', 'Sales', 'Technology', 45000, 75000, NOW(), NOW(), NULL);
INSERT INTO Job_Performance (job_id, applicants_count, views_count, open_date, close_date, time_to_fill, updated_at)
VALUES
(1, 100, 5000, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 15, NOW()),
(2, 150, 7000, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 20, NOW()),
(3, 80, 4000, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 12, NOW()),
(4, 120, 4500, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 17, NOW()),
(5, 90, 6000, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 18, NOW()),
(6, 110, 5000, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 16, NOW()),
(7, 130, 5500, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 14, NOW()),
(8, 140, 6000, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 19, NOW()),
(9, 60, 3500, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 13, NOW()),
(10, 100, 4500, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 15, NOW()),
(11, 80, 4000, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 12, NOW()),
(12, 70, 3900, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 11, NOW()),
(13, 50, 2500, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 10, NOW()),
(14, 90, 5500, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 14, NOW()),
(15, 100, 7000, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 20, NOW()),
(16, 110, 6200, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 22, NOW()),
(17, 95, 4800, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 16, NOW()),
(18, 130, 6500, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 19, NOW()),
(19, 150, 7000, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 20, NOW()),
(20, 140, 6000, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 18, NOW()),
(21, 110, 5000, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 17, NOW()),
(22, 120, 5800, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 19, NOW()),
(23, 200, 8000, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 25, NOW()),
(24, 110, 5200, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 21, NOW()),
(25, 80, 4300, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 16, NOW()),
(26, 130, 6500, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 22, NOW()),
(27, 90, 4700, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 18, NOW()),
(28, 110, 5600, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 19, NOW()),
(29, 100, 5400, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 18, NOW()),
(30, 70, 3500, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 13, NOW()),
(31, 110, 5300, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 17, NOW()),
(32, 120, 5900, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 19, NOW()),
(33, 140, 6800, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 21, NOW()),
(34, 150, 7000, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 22, NOW()),
(35, 100, 5000, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 16, NOW()),
(36, 130, 6200, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 19, NOW()),
(37, 60, 3100, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 12, NOW()),
(38, 100, 4900, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 17, NOW()),
(39, 110, 5500, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 20, NOW()),
(40, 140, 6800, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 23, NOW()),
(41, 120, 6000, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 21, NOW()),
(42, 50, 2600, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 10, NOW()),
(43, 180, 7500, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 28, NOW()),
(44, 90, 4600, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 15, NOW()),
(45, 100, 5300, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 17, NOW()),
(46, 130, 6400, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 20, NOW()),
(47, 100, 5200, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 19, NOW()),
(48, 140, 6900, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 22, NOW()),
(49, 90, 4500, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 16, NOW());
Create the Database Tables
CREATE DATABASE event_management_db;
USE event_management_db;
CREATE TABLE Admin (
admin_id INT AUTO_INCREMENT PRIMARY KEY,
admin_username VARCHAR(255),
admin_email VARCHAR(255),
admin_password VARCHAR(255)
);
CREATE TABLE User (
user_id INT AUTO_INCREMENT PRIMARY KEY,
user_gmail VARCHAR(255),
user_password VARCHAR(255),
user_firstname VARCHAR(255),
user_lastname VARCHAR(255)
);
CREATE TABLE Events (
event_id INT AUTO_INCREMENT PRIMARY KEY,
event_title VARCHAR(255),
event_description TEXT,
event_additional_description TEXT,
event_address VARCHAR(255),
event_planner VARCHAR(255),
event_image VARCHAR(255),
event_status VARCHAR(255),
event_start DATE
);
CREATE TABLE Invited (
invitation_id INT AUTO_INCREMENT PRIMARY KEY,
invited_name VARCHAR(255),
event_id INT,
attendee_type VARCHAR(255),
seat_number VARCHAR(255),
FOREIGN KEY (event_id) REFERENCES Events(event_id) ON DELETE CASCADE
);
CREATE TABLE Attendees (
attendee_id INT AUTO_INCREMENT PRIMARY KEY,
invitation_id INT,
FOREIGN KEY (invitation_id) REFERENCES Invited(invitation_id) ON DELETE CASCADE
);
CREATE TABLE Agenda (
agenda_id INT AUTO_INCREMENT PRIMARY KEY,
event_id INT,
agenda_name VARCHAR(255),
agenda_time_start VARCHAR(255),
agenda_time_end VARCHAR(255),
FOREIGN KEY (event_id) REFERENCES Events(event_id) ON DELETE CASCADE
);
- STEP 2 Dummy DATA insertion
INSERT INTO Admin (admin_username, admin_email, admin_password)
VALUES
('admin1', 'admin1@example.com', 'password123'),
('admin2', 'admin2@example.com', 'password123'),
('admin3', 'admin3@example.com', 'password123'),
('admin4', 'admin4@example.com', 'password123'),
('admin5', 'admin5@example.com', 'password123'),
('admin6', 'admin6@example.com', 'password123'),
('admin7', 'admin7@example.com', 'password123'),
('admin8', 'admin8@example.com', 'password123'),
('admin9', 'admin9@example.com', 'password123'),
('admin10', 'admin10@example.com', 'password123'),
('admin11', 'admin11@example.com', 'password123'),
('admin12', 'admin12@example.com', 'password123'),
('admin13', 'admin13@example.com', 'password123'),
('admin14', 'admin14@example.com', 'password123'),
('admin15', 'admin15@example.com', 'password123'),
('admin16', 'admin16@example.com', 'password123'),
('admin17', 'admin17@example.com', 'password123'),
('admin18', 'admin18@example.com', 'password123'),
('admin19', 'admin19@example.com', 'password123'),
('admin20', 'admin20@example.com', 'password123'),
('admin21', 'admin21@example.com', 'password123'),
('admin22', 'admin22@example.com', 'password123'),
('admin23', 'admin23@example.com', 'password123'),
('admin24', 'admin24@example.com', 'password123'),
('admin25', 'admin25@example.com', 'password123'),
('admin26', 'admin26@example.com', 'password123'),
('admin27', 'admin27@example.com', 'password123'),
('admin28', 'admin28@example.com', 'password123'),
('admin29', 'admin29@example.com', 'password123'),
('admin30', 'admin30@example.com', 'password123'),
('admin31', 'admin31@example.com', 'password123'),
('admin32', 'admin32@example.com', 'password123'),
('admin33', 'admin33@example.com', 'password123'),
('admin34', 'admin34@example.com', 'password123'),
('admin35', 'admin35@example.com', 'password123'),
('admin36', 'admin36@example.com', 'password123'),
('admin37', 'admin37@example.com', 'password123'),
('admin38', 'admin38@example.com', 'password123'),
('admin39', 'admin39@example.com', 'password123'),
('admin40', 'admin40@example.com', 'password123'),
('admin41', 'admin41@example.com', 'password123'),
('admin42', 'admin42@example.com', 'password123'),
('admin43', 'admin43@example.com', 'password123'),
('admin44', 'admin44@example.com', 'password123'),
('admin45', 'admin45@example.com', 'password123'),
('admin46', 'admin46@example.com', 'password123'),
('admin47', 'admin47@example.com', 'password123'),
('admin48', 'admin48@example.com', 'password123'),
('admin49', 'admin49@example.com', 'password123'),
('admin50', 'admin50@example.com', 'password123');
INSERT INTO User (user_gmail, user_password, user_firstname, user_lastname)
VALUES
('user1@gmail.com', 'password123', 'John', 'Doe'),
('user2@gmail.com', 'password123', 'Jane', 'Smith'),
('user3@gmail.com', 'password123', 'Michael', 'Johnson'),
('user4@gmail.com', 'password123', 'Emily', 'Davis'),
('user5@gmail.com', 'password123', 'Daniel', 'Martinez'),
('user6@gmail.com', 'password123', 'Sarah', 'Garcia'),
('user7@gmail.com', 'password123', 'David', 'Rodriguez'),
('user8@gmail.com', 'password123', 'Laura', 'Lopez'),
('user9@gmail.com', 'password123', 'James', 'Gonzalez'),
('user10@gmail.com', 'password123', 'Megan', 'Wilson'),
('user11@gmail.com', 'password123', 'Christopher', 'Anderson'),
('user12@gmail.com', 'password123', 'Linda', 'Thomas'),
('user13@gmail.com', 'password123', 'Robert', 'Taylor'),
('user14@gmail.com', 'password123', 'Jessica', 'Moore'),
('user15@gmail.com', 'password123', 'William', 'Jackson'),
('user16@gmail.com', 'password123', 'Patricia', 'Martin'),
('user17@gmail.com', 'password123', 'Richard', 'Lee'),
('user18@gmail.com', 'password123', 'Karen', 'Harris'),
('user19@gmail.com', 'password123', 'Thomas', 'Clark'),
('user20@gmail.com', 'password123', 'Maria', 'Lewis'),
('user21@gmail.com', 'password123', 'Charles', 'Young'),
('user22@gmail.com', 'password123', 'Elizabeth', 'King'),
('user23@gmail.com', 'password123', 'Joseph', 'Wright'),
('user24@gmail.com', 'password123', 'Barbara', 'Scott'),
('user25@gmail.com', 'password123', 'Kevin', 'Adams'),
('user26@gmail.com', 'password123', 'Nancy', 'Baker'),
('user27@gmail.com', 'password123', 'Steven', 'Gonzalez'),
('user28@gmail.com', 'password123', 'Betty', 'Nelson'),
('user29@gmail.com', 'password123', 'Paul', 'Carter'),
('user30@gmail.com', 'password123', 'Helen', 'Mitchell'),
('user31@gmail.com', 'password123', 'Mark', 'Perez'),
('user32@gmail.com', 'password123', 'Shirley', 'Roberts'),
('user33@gmail.com', 'password123', 'Edward', 'Evans'),
('user34@gmail.com', 'password123', 'Dorothy', 'Collins'),
('user35@gmail.com', 'password123', 'Gary', 'Stewart'),
('user36@gmail.com', 'password123', 'Anna', 'Morris'),
('user37@gmail.com', 'password123', 'Scott', 'Rogers'),
('user38@gmail.com', 'password123', 'Ruth', 'Reed'),
('user39@gmail.com', 'password123', 'Anthony', 'Cook'),
('user40@gmail.com', 'password123', 'Deborah', 'Morgan'),
('user41@gmail.com', 'password123', 'Jason', 'Bell'),
('user42@gmail.com', 'password123', 'Linda', 'Bailey'),
('user43@gmail.com', 'password123', 'Brian', 'Gomez'),
('user44@gmail.com', 'password123', 'Jack', 'Kelly'),
('user45@gmail.com', 'password123', 'Mildred', 'Howard'),
('user46@gmail.com', 'password123', 'Timothy', 'Ward'),
('user47@gmail.com', 'password123', 'George', 'Cox'),
('user48@gmail.com', 'password123', 'Sandra', 'Diaz'),
('user49@gmail.com', 'password123', 'Joshua', 'Sanders'),
('user50@gmail.com', 'password123', 'Cynthia', 'Price');
INSERT INTO Events (event_title, event_description, event_additional_description, event_address, event_planner, event_image, event_status, event_start)
VALUES
('Corporate Gala', 'A corporate event for business networking.', 'Dress code: Formal', '123 Business St., NY', 'John Doe', 'event1.jpg', 'Scheduled', '2024-12-01'),
('Tech Conference', 'A conference about the latest in tech.', 'Includes workshops and guest speakers.', 'Tech Center, San Francisco', 'Jane Smith', 'event2.jpg', 'Scheduled', '2024-11-20'),
('Wedding Reception', 'Celebrate the union of two families.', 'Reception followed by dinner.', '1234 Wedding Rd., LA', 'Michael Johnson', 'event3.jpg', 'Scheduled', '2024-11-15'),
('Charity Auction', 'An event to raise money for charity.', 'Silent auction available.', '456 Charity Blvd., NY', 'Emily Davis', 'event4.jpg', 'Scheduled', '2024-12-05'),
('Music Concert', 'Live performance by popular artists.', 'Open seating.', '789 Music Ave., Austin', 'Sarah Garcia', 'event5.jpg', 'Scheduled', '2024-12-10'),
('Art Exhibition', 'An exhibition of contemporary art.', 'Includes guest artists.', '123 Art St., NY', 'David Rodriguez', 'event6.jpg', 'Scheduled', '2024-12-01'),
('Wedding Ceremony', 'A traditional wedding ceremony.', 'Dinner to follow.', '789 Love St., LA', 'Laura Lopez', 'event7.jpg', 'Scheduled', '2024-11-25'),
('Sports Tournament', 'Annual sports tournament event.', 'Final match on Sunday.', '890 Sports Rd., Chicago', 'James Gonzalez', 'event8.jpg', 'Scheduled', '2024-11-30'),
('Christmas Party', 'Company Christmas party.', 'Food, drinks, and entertainment.', '123 Office Ave., Boston', 'Megan Wilson', 'event9.jpg', 'Scheduled', '2024-12-15'),
('New Year’s Eve Party', 'A celebration for ringing in the new year.', 'Includes fireworks display.', '456 Party Blvd., Miami', 'Christopher Anderson', 'event10.jpg', 'Scheduled', '2024-12-31'),
('Job Fair', 'Hiring event for job seekers.', 'Bring your resume for interviews.', '789 Career St., Austin', 'Linda Thomas', 'event11.jpg', 'Scheduled', '2024-12-03'),
('Leadership Summit', 'A summit for business leaders.', 'Meet top industry experts.', '101 Leadership Rd., NY', 'Mark Stevens', 'event11.jpg', 'Scheduled', '2024-12-05'),
('Tech Startup Pitch', 'Pitch event for tech startups.', 'Present your startup ideas to investors.', '500 Tech Park, Silicon Valley', 'Sarah Thompson', 'event12.jpg', 'Scheduled', '2024-11-25'),
('Fashion Show', 'Annual fashion show by top designers.', 'Featuring the latest collections.', '200 Fashion Ave., NY', 'Lily Brown', 'event13.jpg', 'Scheduled', '2024-11-30'),
('Food Festival', 'A celebration of food from around the world.', 'Tasting booths and live cooking shows.', '550 Food St., LA', 'Carlos Perez', 'event14.jpg', 'Scheduled', '2024-12-12'),
('Photography Exhibition', 'Exhibition of contemporary photographers.', 'A showcase of digital and film photography.', '80 Gallery St., SF', 'John Adams', 'event15.jpg', 'Scheduled', '2024-12-03'),
('Film Premiere', 'Exclusive premiere of a new blockbuster movie.', 'After-party included.', '200 Cinema Blvd., Hollywood', 'Emma Wilson', 'event16.jpg', 'Scheduled', '2024-12-07'),
('Startup Expo', 'Exhibit for new startup businesses.', 'Network and explore new innovations.', '300 Startup Ave., NYC', 'David Harris', 'event17.jpg', 'Scheduled', '2024-12-02'),
('Music Festival', '3-day outdoor music festival with top bands.', 'Outdoor performances, camping available.', '100 Music Rd., Austin', 'Jessica Clark', 'event18.jpg', 'Scheduled', '2024-12-20'),
('Career Expo', 'Explore various career opportunities and meet recruiters.', 'Free resume workshops.', '150 Career Blvd., Miami', 'Robert Martin', 'event19.jpg', 'Scheduled', '2024-12-15'),
('Gaming Tournament', 'Competitive gaming event with prize money.', 'Live streaming of the games.', '500 Gaming Plaza, NY', 'Megan Lee', 'event20.jpg', 'Scheduled', '2024-12-10'),
('Cooking Masterclass', 'Learn from the best chefs in the world.', 'Hands-on cooking experience.', '600 Chef Rd., NY', 'Sophia Gonzalez', 'event21.jpg', 'Scheduled', '2024-12-18'),
('Annual Conference', 'Gathering of professionals in various fields.', 'Workshops and networking sessions.', '400 Conference Ave., Chicago', 'Michael Clark', 'event22.jpg', 'Scheduled', '2024-12-04'),
('Virtual Reality Expo', 'Expo showcasing the latest in VR technology.', 'Try out the latest VR gadgets and games.', 'VR Center, Silicon Valley', 'Daniel Turner', 'event23.jpg', 'Scheduled', '2024-12-08'),
('Holiday Gala', 'Elegant gala for celebrating the holiday season.', 'Cocktail party and silent auction.', '100 Gala St., LA', 'Alice Walker', 'event24.jpg', 'Scheduled', '2024-12-21'),
('Marathon Event', 'Annual marathon for charity.', 'Sign up for the race or volunteer.', '50 Run Rd., Boston', 'Evan White', 'event25.jpg', 'Scheduled', '2024-12-04'),
('Leadership Workshop', 'Workshop for aspiring leaders.', 'Includes team-building activities.', '200 Leadership Blvd., LA', 'Karen Taylor', 'event26.jpg', 'Scheduled', '2024-12-02'),
('Digital Marketing Summit', 'Marketing professionals discuss the future of digital marketing.', 'Workshops on the latest trends in marketing.', '300 Digital St., SF', 'Paul Edwards', 'event27.jpg', 'Scheduled', '2024-12-10'),
('Networking Mixer', 'Networking event for young professionals.', 'Meet people from diverse industries.', '150 Network Blvd., NY', 'Linda Carter', 'event28.jpg', 'Scheduled', '2024-12-05'),
('Photography Workshop', 'Learn photography from expert photographers.', 'Practical sessions with one-on-one coaching.', '200 Photo Rd., LA', 'Gary Davis', 'event29.jpg', 'Scheduled', '2024-12-15'),
('Art Auction', 'Auction of fine art pieces for charity.', 'Bidding starts at 5 PM.', '250 Art Ave., NY', 'Rachel Lee', 'event30.jpg', 'Scheduled', '2024-12-08'),
('Blockchain Summit', 'Conference discussing the future of blockchain technology.', 'Keynote speakers and networking opportunities.', '150 Blockchain St., SF', 'Chris Evans', 'event31.jpg', 'Scheduled', '2024-12-12'),
('Startup Growth Seminar', 'Seminar on how to grow and scale your startup.', 'Expert speakers and case studies.', '500 Startup Blvd., Silicon Valley', 'Jessica Miller', 'event32.jpg', 'Scheduled', '2024-12-18'),
('Luxury Brand Launch', 'Exclusive launch event for a new luxury brand.', 'Invites only. RSVP required.', '100 Brand St., NY', 'David Gonzalez', 'event33.jpg', 'Scheduled', '2024-12-09'),
('Health & Wellness Expo', 'Expo for health and wellness enthusiasts.', 'Meet top health experts and try new products.', '200 Wellness Ave., LA', 'Evelyn Ross', 'event34.jpg', 'Scheduled', '2024-12-14'),
('Fundraising Dinner', 'Dinner event for raising funds for a non-profit organization.', 'Fine dining and entertainment.', '400 Charity Rd., NY', 'Liam Scott', 'event35.jpg', 'Scheduled', '2024-12-16'),
('Global Tourism Conference', 'Conference focused on global tourism trends.', 'Seminars and workshops on travel and tourism.', '500 Travel Blvd., London', 'John Mitchell', 'event36.jpg', 'Scheduled', '2024-12-10'),
('Pop-Up Shop', 'One-day pop-up shop featuring local designers.', 'Limited-time sales event.', '100 Shop Rd., SF', 'Kimberly Brooks', 'event37.jpg', 'Scheduled', '2024-12-05'),
('Wine Tasting Event', 'Exclusive wine tasting event with sommeliers.', 'Sample wines from around the world.', '200 Vine St., Napa', 'Sandra Green', 'event38.jpg', 'Scheduled', '2024-12-13'),
('Eco-Friendly Expo', 'Expo featuring eco-friendly products and innovations.', 'Workshops and live demonstrations.', '150 Green St., SF', 'Ethan Murphy', 'event39.jpg', 'Scheduled', '2024-12-07'),
('Tech Talks', 'Series of tech talks from industry leaders.', 'Hear from the innovators of tech.', '250 Tech Ave., NY', 'Olivia King', 'event40.jpg', 'Scheduled', '2024-12-01'),
('Gaming Expo', 'Convention showcasing the latest in gaming technology.', 'Meet top game developers and try out new games.', '300 Game St., LA', 'Aaron Lewis', 'event41.jpg', 'Scheduled', '2024-12-04'),
('Startup Networking', 'Networking event for startup founders and investors.', 'Pitch your startup to potential investors.', '400 Innovators Ave., Silicon Valley', 'Nancy Hall', 'event42.jpg', 'Scheduled', '2024-12-09'),
('Cultural Festival', 'Festival celebrating various cultures.', 'Music, food, and performances from different cultures.', '500 Culture St., Austin', 'Sophia Martin', 'event43.jpg', 'Scheduled', '2024-12-11'),
('Book Launch Party', 'Launch event for a bestselling author’s new book.', 'Author readings and signing sessions.', '200 Book Rd., NY', 'Benjamin Clark', 'event44.jpg', 'Scheduled', '2024-12-06'),
('Pet Expo', 'Expo for pet lovers and pet care businesses.', 'Products, services, and entertainment for pets.', '300 Pet Ave., LA', 'Sophia Turner', 'event45.jpg', 'Scheduled', '2024-12-12'),
('Social Media Influencer Meet-up', 'Meet-up for social media influencers and brands.', 'Network and learn how to grow your personal brand.', '400 Media Blvd., Miami', 'Dylan Gray', 'event46.jpg', 'Scheduled', '2024-12-01'),
('Startup Funding Seminar', 'Seminar on how to secure funding for your startup.', 'Pitching advice and tips from investors.', '500 Investor Rd., SF', 'Emily Peterson', 'event47.jpg', 'Scheduled', '2024-12-14'),
('E-Commerce Summit', 'Conference on the latest trends in e-commerce.', 'Case studies and guest speakers from the industry.', '200 E-Commerce Ave., NY', 'James Bennett', 'event48.jpg', 'Scheduled', '2024-12-16'),
('Annual Charity Run', 'Charity event featuring a 5K run.', 'Raising money for local charities.', '100 Run Blvd., Austin', 'Angela White', 'event49.jpg', 'Scheduled', '2024-12-20'),
('Fashion Expo', 'Exhibit for fashion designers and enthusiasts.', 'Spot the latest fashion trends.', '300 Fashion St., LA', 'Linda Martinez', 'event50.jpg', 'Scheduled', '2024-12-10');
INSERT INTO Invited (invited_name, event_id, attendee_type, seat_number)
VALUES
('John Doe', 1, 'VIP', 'A1'),
('Jane Smith', 2, 'Guest', 'B1'),
('Michael Johnson', 3, 'VIP', 'C1'),
('Emily Davis', 4, 'Guest', 'D1'),
('Daniel Martinez', 5, 'VIP', 'E1'),
('Sandra Price', 6, 'Speaker', 'F1'),
('Christopher Clark', 7, 'Guest', 'G1'),
('Tina Robinson', 8, 'VIP', 'H1'),
('Laura Adams', 9, 'Guest', 'I1'),
('Olivia Taylor', 10, 'VIP', 'J1'),
('David Brown', 11, 'Guest', 'K1'),
('Sophia Walker', 12, 'VIP', 'L1'),
('Matthew Lewis', 13, 'Guest', 'M1'),
('Elizabeth Scott', 14, 'VIP', 'N1'),
('Aaron King', 15, 'Guest', 'O1'),
('Lily Harris', 16, 'VIP', 'P1'),
('Ryan White', 17, 'Guest', 'Q1'),
('Emma Turner', 18, 'VIP', 'R1'),
('Henry Young', 19, 'Guest', 'S1'),
('Grace Martin', 20, 'VIP', 'T1'),
('Isabella Evans', 21, 'Guest', 'U1'),
('Ethan Clark', 22, 'VIP', 'V1'),
('Chloe Davis', 23, 'Guest', 'W1'),
('James Wilson', 24, 'VIP', 'X1'),
('Sophia Brown', 25, 'Guest', 'Y1'),
('Alexander Garcia', 26, 'VIP', 'Z1'),
('Ava Robinson', 27, 'Guest', 'A2'),
('Mason Harris', 28, 'VIP', 'B2'),
('Lily Walker', 29, 'Guest', 'C2'),
('Daniel Lee', 30, 'VIP', 'D2'),
('Matthew Young', 31, 'Guest', 'E2'),
('Ella Martinez', 32, 'VIP', 'F2'),
('Amelia Brown', 33, 'Guest', 'G2'),
('Ethan Gonzalez', 34, 'VIP', 'H2'),
('Aiden Clark', 35, 'Guest', 'I2'),
('Lucas King', 36, 'VIP', 'J2'),
('Isabella Harris', 37, 'Guest', 'K2'),
('Sophia White', 38, 'VIP', 'L2'),
('Jackson Robinson', 39, 'Guest', 'M2'),
('Mia Adams', 40, 'VIP', 'N2'),
('Liam Evans', 41, 'Guest', 'O2'),
('Charlotte Martinez', 42, 'VIP', 'P2'),
('Landon Brown', 43, 'Guest', 'Q2'),
('Lucas Young', 44, 'VIP', 'R2'),
('Isabella Clark', 45, 'Guest', 'S2'),
('Sophie Garcia', 46, 'VIP', 'T2'),
('Ethan Lewis', 47, 'Guest', 'U2'),
('Mason King', 48, 'VIP', 'V2'),
('Lily Wilson', 49, 'Guest', 'W2'),
('Benjamin Walker', 50, 'VIP', 'X2');
INSERT INTO Attendees (invitation_id)
VALUES
(1), (2), (3), (4), (5), (6), (7), (8), (9), (10),
(11), (12), (13), (14), (15), (16), (17), (18), (19), (20),
(21), (22), (23), (24), (25), (26), (27), (28), (29), (30),
(31), (32), (33), (34), (35), (36), (37), (38), (39), (40),
(41), (42), (43), (44), (45), (46), (47), (48), (49), (50);
INSERT INTO Agenda (event_id, agenda_name, agenda_time_start, agenda_time_end)
VALUES
(1, 'Opening Remarks', '09:00 AM', '09:30 AM'),
(2, 'Tech Pitching', '10:00 AM', '11:30 AM'),
(3, 'Runway Walk', '06:00 PM', '07:00 PM'),
(4, 'Food Tastings', '12:00 PM', '02:00 PM'),
(5, 'Photo Session', '04:00 PM', '06:00 PM'),
(6, 'Film Screening', '08:00 PM', '10:00 PM'),
(7, 'Exhibitor Networking', '11:00 AM', '01:00 PM'),
(8, 'Main Stage Performances', '01:00 PM', '06:00 PM'),
(9, 'Job Interview Sessions', '09:00 AM', '03:00 PM'),
(10, 'Game Demo', '10:00 AM', '12:00 PM'),
(11, 'Cooking Class', '11:00 AM', '01:00 PM'),
(12, 'Networking Breakfast', '08:00 AM', '09:00 AM'),
(13, 'Panel Discussion', '02:00 PM', '03:00 PM'),
(14, 'Art Viewing', '05:00 PM', '06:30 PM'),
(15, 'Blockchain Talk', '10:00 AM', '11:30 AM'),
(16, 'Keynote Address', '09:00 AM', '10:00 AM'),
(17, 'Startup Pitching', '10:00 AM', '12:00 PM'),
(18, 'Headliner Performance', '07:00 PM', '09:00 PM'),
(19, 'Resume Workshop', '08:00 AM', '11:00 AM'),
(20, 'Cosplay Contest', '11:00 AM', '01:00 PM'),
(21, 'Social Media Workshop', '03:00 PM', '04:30 PM'),
(22, 'Charity Auction', '06:00 PM', '08:00 PM'),
(23, 'Virtual Reality Demo', '10:00 AM', '12:00 PM'),
(24, 'Networking Dinner', '07:00 PM', '09:00 PM'),
(25, '5K Run', '09:00 AM', '11:00 AM'),
(26, 'Leadership Roundtable', '10:00 AM', '12:00 PM'),
(27, 'Marketing Case Study', '02:00 PM', '03:30 PM'),
(28, 'Young Professional Networking', '11:00 AM', '12:30 PM'),
(29, 'Photo Walk', '01:00 PM', '03:00 PM'),
(30, 'Fine Art Auction', '04:00 PM', '06:00 PM'),
(31, 'Investor Pitching', '10:00 AM', '12:00 PM'),
(32, 'Funding Seminar', '02:00 PM', '04:00 PM'),
(33, 'Luxury Collection Unveiling', '06:00 PM', '08:00 PM'),
(34, 'Health Product Demos', '01:00 PM', '03:00 PM'),
(35, 'Fundraising Dinner', '07:00 PM', '09:00 PM'),
(36, 'Tourism Panel', '02:00 PM', '03:30 PM'),
(37, 'Pop-Up Sales', '12:00 PM', '06:00 PM'),
(38, 'Wine Tasting', '05:00 PM', '07:00 PM'),
(39, 'Sustainability Talks', '10:00 AM', '11:30 AM'),
(40, 'Influencer Meet-and-Greet', '01:00 PM', '03:00 PM'),
(41, 'Game Tournament', '10:00 AM', '02:00 PM'),
(42, 'Founder Pitches', '02:00 PM', '04:00 PM'),
(43, 'Cultural Performances', '12:00 PM', '04:00 PM'),
(44, 'Book Signing', '03:00 PM', '05:00 PM'),
(45, 'Pet Adoption', '10:00 AM', '02:00 PM'),
(46, 'Social Media Strategies', '01:00 PM', '03:00 PM'),
(47, 'Funding Pitching', '02:00 PM', '04:00 PM'),
(48, 'E-Commerce Success Stories', '11:00 AM', '01:00 PM'),
(49, 'Charity Run', '08:00 AM', '10:00 AM'),
(50, 'Closing Ceremony', '09:00 PM', '10:00 PM');
1. Create the Database Tables
CREATE DATABASE travel_booking_db;
USE travel_booking_db;
CREATE TABLE User (
user_id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50),
password_hash VARCHAR(255),
email VARCHAR(100),
phone_number VARCHAR(20),
first_name VARCHAR(50),
last_name VARCHAR(50),
user_role ENUM('ADMIN', 'USERS'),
created_at DATETIME,
updated_at DATETIME,
deleted_at DATETIME
);
CREATE TABLE Tours (
tour_id INT AUTO_INCREMENT PRIMARY KEY,
tour_name VARCHAR(100),
description TEXT,
price DECIMAL(10,2),
start_date DATE,
end_date DATE,
seats_available INT,
image_url VARCHAR(255),
created_at DATETIME,
updated_at DATETIME,
deleted_at DATETIME
);
CREATE TABLE Bookings (
booking_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
tour_id INT,
booking_date DATETIME,
travel_date DATE,
seats_booked INT,
total_amount DECIMAL(10,2),
payment_status ENUM('SUCCESS', 'FAILED'),
created_at DATETIME,
updated_at DATETIME,
FOREIGN KEY (user_id) REFERENCES User(user_id) ON DELETE CASCADE,
FOREIGN KEY (tour_id) REFERENCES Tours(tour_id) ON DELETE CASCADE
);
CREATE TABLE Payment (
payment_id INT AUTO_INCREMENT PRIMARY KEY,
booking_id INT,
payment_date DATETIME,
amount DECIMAL(10,2),
payment_method ENUM('GCASH'),
payment_status ENUM('SUCCESS', 'FAILED'),
transaction_id VARCHAR(100),
FOREIGN KEY (booking_id) REFERENCES Bookings(booking_id) ON DELETE CASCADE
);
CREATE TABLE Reviews (
review_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
tour_id INT,
rating INT,
comment TEXT,
created_at DATETIME,
FOREIGN KEY (user_id) REFERENCES User(user_id) ON DELETE CASCADE,
FOREIGN KEY (tour_id) REFERENCES Tours(tour_id) ON DELETE CASCADE
);
CREATE TABLE AdminLogs (
log_id INT AUTO_INCREMENT PRIMARY KEY,
admin_id INT,
action_type ENUM('ADD', 'DELETE', 'UPDATE'),
description TEXT,
timestamp DATETIME,
FOREIGN KEY (admin_id) REFERENCES User(user_id) ON DELETE CASCADE
);
- Inserting Dummy Data
INSERT INTO User (username, password_hash, email, phone_number, first_name, last_name, user_role, created_at, updated_at, deleted_at)
VALUES
('user1', 'hash1', 'user1@example.com', '1234567890', 'John', 'Doe', 'USERS', NOW(), NOW(), NULL),
('user2', 'hash2', 'user2@example.com', '1234567891', 'Jane', 'Smith', 'USERS', NOW(), NOW(), NULL),
('user3', 'hash3', 'user3@example.com', '1234567892', 'Mike', 'Johnson', 'USERS', NOW(), NOW(), NULL),
('user4', 'hash4', 'user4@example.com', '1234567893', 'Emily', 'Davis', 'USERS', NOW(), NOW(), NULL),
('user5', 'hash5', 'user5@example.com', '1234567894', 'James', 'Wilson', 'USERS', NOW(), NOW(), NULL),
('user6', 'hash6', 'user6@example.com', '1234567895', 'Sarah', 'Taylor', 'USERS', NOW(), NOW(), NULL),
('user7', 'hash7', 'user7@example.com', '1234567896', 'David', 'Brown', 'USERS', NOW(), NOW(), NULL),
('user8', 'hash8', 'user8@example.com', '1234567897', 'Olivia', 'Moore', 'USERS', NOW(), NOW(), NULL),
('user9', 'hash9', 'user9@example.com', '1234567898', 'William', 'Anderson', 'USERS', NOW(), NOW(), NULL),
('user10', 'hash10', 'user10@example.com', '1234567899', 'Isabella', 'Thomas', 'USERS', NOW(), NOW(), NULL),
('user11', 'hash11', 'user11@example.com', '1234567800', 'Sophia', 'Jackson', 'USERS', NOW(), NOW(), NULL),
('user12', 'hash12', 'user12@example.com', '1234567801', 'Mason', 'White', 'USERS', NOW(), NOW(), NULL),
('user13', 'hash13', 'user13@example.com', '1234567802', 'Ethan', 'Harris', 'USERS', NOW(), NOW(), NULL),
('user14', 'hash14', 'user14@example.com', '1234567803', 'Charlotte', 'Martin', 'USERS', NOW(), NOW(), NULL),
('user15', 'hash15', 'user15@example.com', '1234567804', 'Amelia', 'Garcia', 'USERS', NOW(), NOW(), NULL),
('user16', 'hash16', 'user16@example.com', '1234567805', 'Benjamin', 'Martinez', 'USERS', NOW(), NOW(), NULL),
('user17', 'hash17', 'user17@example.com', '1234567806', 'Lucas', 'Lopez', 'USERS', NOW(), NOW(), NULL),
('user18', 'hash18', 'user18@example.com', '1234567807', 'Ava', 'Gonzalez', 'USERS', NOW(), NOW(), NULL),
('user19', 'hash19', 'user19@example.com', '1234567808', 'Jack', 'Perez', 'USERS', NOW(), NOW(), NULL),
('user20', 'hash20', 'user20@example.com', '1234567809', 'Grace', 'Wilson', 'USERS', NOW(), NOW(), NULL),
('user21', 'hash21', 'user21@example.com', '1234567810', 'Henry', 'Lee', 'USERS', NOW(), NOW(), NULL),
('user22', 'hash22', 'user22@example.com', '1234567811', 'Zoe', 'Kim', 'USERS', NOW(), NOW(), NULL),
('user23', 'hash23', 'user23@example.com', '1234567812', 'Isaac', 'Clark', 'USERS', NOW(), NOW(), NULL),
('user24', 'hash24', 'user24@example.com', '1234567813', 'Ella', 'Lewis', 'USERS', NOW(), NOW(), NULL),
('user25', 'hash25', 'user25@example.com', '1234567814', 'Daniel', 'Young', 'USERS', NOW(), NOW(), NULL),
('user26', 'hash26', 'user26@example.com', '1234567815', 'Liam', 'Walker', 'USERS', NOW(), NOW(), NULL),
('user27', 'hash27', 'user27@example.com', '1234567816', 'Mila', 'Hall', 'USERS', NOW(), NOW(), NULL),
('user28', 'hash28', 'user28@example.com', '1234567817', 'Samuel', 'Allen', 'USERS', NOW(), NOW(), NULL),
('user29', 'hash29', 'user29@example.com', '1234567818', 'Scarlett', 'King', 'USERS', NOW(), NOW(), NULL),
('user30', 'hash30', 'user30@example.com', '1234567819', 'David', 'Scott', 'USERS', NOW(), NOW(), NULL),
('user31', 'hash31', 'user31@example.com', '1234567820', 'Leah', 'Adams', 'USERS', NOW(), NOW(), NULL),
('user32', 'hash32', 'user32@example.com', '1234567821', 'Jayden', 'Baker', 'USERS', NOW(), NOW(), NULL),
('user33', 'hash33', 'user33@example.com', '1234567822', 'Mackenzie', 'Nelson', 'USERS', NOW(), NOW(), NULL),
('user34', 'hash34', 'user34@example.com', '1234567823', 'Owen', 'Carter', 'USERS', NOW(), NOW(), NULL),
('user35', 'hash35', 'user35@example.com', '1234567824', 'Chloe', 'Mitchell', 'USERS', NOW(), NOW(), NULL),
('user36', 'hash36', 'user36@example.com', '1234567825', 'Megan', 'Perez', 'USERS', NOW(), NOW(), NULL),
('user37', 'hash37', 'user37@example.com', '1234567826', 'Caleb', 'Roberts', 'USERS', NOW(), NOW(), NULL),
('user38', 'hash38', 'user38@example.com', '1234567827', 'Emily', 'Evans', 'USERS', NOW(), NOW(), NULL),
('user39', 'hash39', 'user39@example.com', '1234567828', 'Carter', 'Gonzalez', 'USERS', NOW(), NOW(), NULL),
('user40', 'hash40', 'user40@example.com', '1234567829', 'Zara', 'James', 'USERS', NOW(), NOW(), NULL),
('user41', 'hash41', 'user41@example.com', '1234567830', 'Adeline', 'Morris', 'USERS', NOW(), NOW(), NULL),
('user42', 'hash42', 'user42@example.com', '1234567831', 'Jackson', 'Rodriguez', 'USERS', NOW(), NOW(), NULL),
('user43', 'hash43', 'user43@example.com', '1234567832', 'Sophie', 'Brown', 'USERS', NOW(), NOW(), NULL),
('user44', 'hash44', 'user44@example.com', '1234567833', 'Abigail', 'Miller', 'USERS', NOW(), NOW(), NULL),
('user45', 'hash45', 'user45@example.com', '1234567834', 'Nathan', 'Wilson', 'USERS', NOW(), NOW(), NULL),
('user46', 'hash46', 'user46@example.com', '1234567835', 'Zane', 'Taylor', 'USERS', NOW(), NOW(), NULL),
('user47', 'hash47', 'user47@example.com', '1234567836', 'Sienna', 'Thomas', 'USERS', NOW(), NOW(), NULL),
('user48', 'hash48', 'user48@example.com', '1234567837', 'Caden', 'Jackson', 'USERS', NOW(), NOW(), NULL),
('user49', 'hash49', 'user49@example.com', '1234567838', 'Lily', 'Gonzalez', 'USERS', NOW(), NOW(), NULL),
('user50', 'hash50', 'user50@example.com', '1234567839', 'Lucas', 'Moore', 'USERS', NOW(), NOW(), NULL);
INSERT INTO Tours (tour_name, description, price, start_date, end_date, seats_available, image_url, created_at, updated_at, deleted_at)
VALUES
('Paris Adventure', 'A journey through the City of Lights, visiting famous landmarks like the Eiffel Tower and the Louvre.', 1500.00, '2024-06-01', '2024-06-10', 30, 'paris.jpg', NOW(), NOW(), NULL),
('Swiss Alps Escape', 'Trek through the picturesque Swiss Alps, with stunning views and hiking opportunities.', 2000.00, '2024-07-01', '2024-07-10', 20, 'swiss_alps.jpg', NOW(), NOW(), NULL),
('Rome Uncovered', 'Discover the history and culture of ancient Rome, with visits to the Colosseum and Roman Forum.', 1200.00, '2024-08-01', '2024-08-07', 25, 'rome_ancient.jpg', NOW(), NOW(), NULL),
('Kenya Safari', 'A thrilling safari adventure through the savannahs of Kenya, encountering wildlife like lions, elephants, and giraffes.', 2500.00, '2024-09-01', '2024-09-12', 15, 'kenya_safari.jpg', NOW(), NOW(), NULL),
('Bali Beach Holiday', 'Relax on the white sandy beaches of Bali with beautiful resorts and water sports activities.', 1100.00, '2024-10-01', '2024-10-07', 40, 'bali_beach.jpg', NOW(), NOW(), NULL),
('Tokyo City Lights', 'Explore the vibrant city of Tokyo, from modern skyscrapers to historic temples.', 1300.00, '2024-11-01', '2024-11-05', 35, 'tokyo_city.jpg', NOW(), NOW(), NULL),
('Sydney Under the Sun', 'Enjoy the sunny beaches and landmarks like the Sydney Opera House and Harbour Bridge.', 1450.00, '2024-12-01', '2024-12-08', 45, 'sydney_sun.jpg', NOW(), NOW(), NULL),
('Amazon Rainforest Trek', 'Venture deep into the Amazon Rainforest, experiencing incredible biodiversity and indigenous cultures.', 3000.00, '2025-01-01', '2025-01-10', 10, 'amazon_rainforest.jpg', NOW(), NOW(), NULL),
('New York City Tour', 'A fast-paced tour of New York City, from Times Square to Central Park and beyond.', 1700.00, '2025-02-01', '2025-02-07', 50, 'nyc.jpg', NOW(), NOW(), NULL),
('Egyptian Wonders', 'Visit the pyramids of Giza, the Great Sphinx, and explore the ancient history of Egypt.', 2200.00, '2025-03-01', '2025-03-07', 30, 'egypt.jpg', NOW(), NOW(), NULL),
('Greek Isles Cruise', 'Sail across the beautiful Greek Islands, including Mykonos, Santorini, and Crete.', 2400.00, '2025-04-01', '2025-04-10', 20, 'greece_cruise.jpg', NOW(), NOW(), NULL),
('Machu Picchu Expedition', 'Hike the Inca Trail to the ancient city of Machu Picchu, one of the New Seven Wonders of the World.', 2700.00, '2025-05-01', '2025-05-07', 15, 'machu_picchu.jpg', NOW(), NOW(), NULL),
('Iceland Adventure', 'Explore the stunning glaciers, volcanoes, and hot springs of Iceland.', 2300.00, '2025-06-01', '2025-06-07', 25, 'iceland.jpg', NOW(), NOW(), NULL),
('Northern Lights Tour', 'Witness the spectacular Northern Lights in Norway, with scenic drives and cozy cabins.', 1800.00, '2025-07-01', '2025-07-05', 30, 'northern_lights.jpg', NOW(), NOW(), NULL),
('Dubai Skyline Adventure', 'Explore the futuristic architecture and luxury shopping of Dubai.', 2000.00, '2025-08-01', '2025-08-05', 50, 'dubai.jpg', NOW(), NOW(), NULL),
('African Wildlife Safari', 'Embark on a safari to see the “Big Five” in Kruger National Park, South Africa.', 3000.00, '2025-09-01', '2025-09-10', 15, 'africa_safari.jpg', NOW(), NOW(), NULL),
('Hawaiian Islands Tour', 'Visit the stunning beaches, volcanic parks, and cultural landmarks across Hawaii.', 1600.00, '2025-10-01', '2025-10-07', 40, 'hawaii.jpg', NOW(), NOW(), NULL),
('South American Expedition', 'A multi-country adventure across Peru, Bolivia, and Argentina, visiting ancient ruins and natural wonders.', 3500.00, '2025-11-01', '2025-11-14', 25, 'south_america.jpg', NOW(), NOW(), NULL),
('Great Wall of China Trek', 'Hike along the Great Wall of China, with stops at historical sections of the wall.', 1900.00, '2025-12-01', '2025-12-07', 30, 'great_wall_china.jpg', NOW(), NOW(), NULL),
('New Zealand Wilderness', 'Explore the diverse landscapes of New Zealand, including beaches, mountains, and lakes.', 2200.00, '2026-01-01', '2026-01-07', 25, 'new_zealand.jpg', NOW(), NOW(), NULL),
('Antarctic Expedition', 'Travel to the South Pole and explore the vast white wilderness of Antarctica.', 5000.00, '2026-02-01', '2026-02-07', 10, 'antarctica.jpg', NOW(), NOW(), NULL),
('Canadian Rockies Adventure', 'Hike the majestic Canadian Rockies, visiting Banff and Jasper National Parks.', 2500.00, '2026-03-01', '2026-03-07', 20, 'canadian_rockies.jpg', NOW(), NOW(), NULL),
('Scandinavian Road Trip', 'Drive through Denmark, Sweden, and Norway, enjoying scenic coastal views and charming villages.', 2200.00, '2026-04-01', '2026-04-07', 35, 'scandinavia.jpg', NOW(), NOW(), NULL),
('Tanzania Wildlife Safari', 'Explore Tanzania’s Serengeti and Ngorongoro Crater to witness some of the most diverse wildlife on Earth.', 3000.00, '2026-05-01', '2026-05-10', 15, 'tanzania_safari.jpg', NOW(), NOW(), NULL),
('Peruvian Highlands Trek', 'Hike through the Peruvian Andes, exploring ancient Incan ruins and remote villages.', 1800.00, '2026-06-01', '2026-06-07', 30, 'peru.jpg', NOW(), NOW(), NULL),
('Alaskan Cruise', 'Set sail through Alaska’s stunning fjords and glaciers, with opportunities to see wildlife and natural beauty.', 2800.00, '2026-07-01', '2026-07-10', 25, 'alaska_cruise.jpg', NOW(), NOW(), NULL),
('Indian Ocean Odyssey', 'Relax on the tropical islands of the Indian Ocean, including Seychelles and Mauritius.', 3200.00, '2026-08-01', '2026-08-10', 20, 'indian_ocean.jpg', NOW(), NOW(), NULL),
('Grand Canyon Adventure', 'Explore one of the world’s most iconic natural wonders with guided hikes and river trips.', 1500.00, '2026-09-01', '2026-09-07', 50, 'grand_canyon.jpg', NOW(), NOW(), NULL),
('Croatian Coastline Tour', 'Cruise along the stunning coastline of Croatia, visiting the UNESCO-listed city of Dubrovnik.', 2200.00, '2026-10-01', '2026-10-07', 30, 'croatia_coast.jpg', NOW(), NOW(), NULL),
('Japanese Cherry Blossom Tour', 'Visit Japan during cherry blossom season, with stops in Tokyo, Kyoto, and Osaka.', 1800.00, '2026-11-01', '2026-11-07', 35, 'cherry_blossoms.jpg', NOW(), NOW(), NULL),
('Australia Outback Expedition', 'Venture into the remote Australian Outback, visiting Uluru and Kakadu National Park.', 2700.00, '2026-12-01', '2026-12-07', 20, 'australian_outback.jpg', NOW(), NOW(), NULL),
('Borneo Rainforest Exploration', 'Discover the wildlife and biodiversity of Borneo’s rainforests, with a focus on orangutans and other rare species.', 2800.00, '2027-01-01', '2027-01-10', 15, 'borneo_rainforest.jpg', NOW(), NOW(), NULL),
('Vietnam Culture and Nature Tour', 'Tour the cultural and natural highlights of Vietnam, from Halong Bay to the ancient town of Hoi An.', 1600.00, '2027-02-01', '2027-02-07', 40, 'vietnam.jpg', NOW(), NOW(), NULL),
('Patagonia Wilderness Trek', 'Trek through the stunning Patagonian wilderness in Argentina and Chile, with breathtaking landscapes and glaciers.', 3500.00, '2027-03-01', '2027-03-10', 20, 'patagonia.jpg', NOW(), NOW(), NULL),
('Istanbul & Ephesus Tour', 'Visit the iconic city of Istanbul and the ancient ruins of Ephesus in Turkey.', 1800.00, '2027-04-01', '2027-04-07', 25, 'istanbul_ephesus.jpg', NOW(), NOW(), NULL),
('Galapagos Islands Expedition', 'Explore the unique wildlife and pristine beaches of the Galapagos Islands.', 4000.00, '2027-05-01', '2027-05-07', 10, 'galapagos.jpg', NOW(), NOW(), NULL),
('Canadian Eastern Seaboard', 'Discover the charming cities and coastal beauty of Canada’s eastern provinces, including Quebec and Nova Scotia.', 2200.00, '2027-06-01', '2027-06-07', 30, 'canadian_east.jpg', NOW(), NOW(), NULL),
('Malaysian Paradise Tour', 'Explore the beautiful islands and beaches of Malaysia, including Langkawi and Penang.', 2000.00, '2027-07-01', '2027-07-07', 40, 'malaysia_paradise.jpg', NOW(), NOW(), NULL),
('Peruvian Amazon Adventure', 'Embark on a unique adventure through the Peruvian Amazon Rainforest, with river cruises and wildlife spotting.', 3200.00, '2027-08-01', '2027-08-10', 15, 'peruvian_amazon.jpg', NOW(), NOW(), NULL),
('Antarctic Peninsula Cruise', 'Embark on a cruise through the Antarctic Peninsula, witnessing massive icebergs and wildlife like penguins and seals.', 4500.00, '2027-09-01', '2027-09-10', 10, 'antarctic_peninsula.jpg', NOW(), NOW(), NULL),
('Sri Lanka Heritage Tour', 'Explore the ancient temples, beaches, and wildlife of Sri Lanka, a perfect blend of culture and nature.', 2200.00, '2027-10-01', '2027-10-07', 35, 'sri_lanka_heritage.jpg', NOW(), NOW(), NULL),
('Czech Republic Castle Tour', 'Visit the beautiful castles and medieval towns of the Czech Republic, including Prague and Cesky Krumlov.', 1600.00, '2027-11-01', '2027-11-07', 40, 'czech_castles.jpg', NOW(), NOW(), NULL),
('South Korea Tour', 'Explore the vibrant cities and natural beauty of South Korea, including Seoul, Jeju Island, and historical sites.', 1700.00, '2027-12-01', '2027-12-07', 25, 'south_korea.jpg', NOW(), NOW(), NULL);
INSERT INTO Bookings (user_id, tour_id, booking_date, travel_date, seats_booked, total_amount, payment_status, created_at, updated_at)
VALUES
(1, 1, NOW(), '2024-06-01', 2, 3000.00, 'SUCCESS', NOW(), NOW()),
(2, 2, NOW(), '2024-07-05', 3, 6000.00, 'SUCCESS', NOW(), NOW()),
(3, 3, NOW(), '2024-08-03', 1, 1200.00, 'FAILED', NOW(), NOW()),
(4, 4, NOW(), '2024-09-05', 4, 10000.00, 'SUCCESS', NOW(), NOW()),
(5, 5, NOW(), '2024-10-02', 2, 2200.00, 'SUCCESS', NOW(), NOW()),
(6, 6, NOW(), '2024-11-01', 1, 1300.00, 'SUCCESS', NOW(), NOW()),
(7, 7, NOW(), '2024-12-01', 3, 4350.00, 'FAILED', NOW(), NOW()),
(8, 8, NOW(), '2025-01-02', 5, 15000.00, 'SUCCESS', NOW(), NOW()),
(9, 9, NOW(), '2025-02-01', 2, 3400.00, 'SUCCESS', NOW(), NOW()),
(10, 10, NOW(), '2025-03-01', 1, 2200.00, 'FAILED', NOW(), NOW()),
(11, 11, NOW(), '2025-04-01', 2, 4800.00, 'SUCCESS', NOW(), NOW()),
(12, 12, NOW(), '2025-05-01', 4, 8800.00, 'SUCCESS', NOW(), NOW()),
(13, 13, NOW(), '2025-06-01', 3, 5400.00, 'FAILED', NOW(), NOW()),
(14, 14, NOW(), '2025-07-01', 2, 4000.00, 'SUCCESS', NOW(), NOW()),
(15, 15, NOW(), '2025-08-01', 1, 1600.00, 'SUCCESS', NOW(), NOW()),
(16, 16, NOW(), '2025-09-01', 3, 4800.00, 'FAILED', NOW(), NOW()),
(17, 17, NOW(), '2025-10-01', 2, 4400.00, 'SUCCESS', NOW(), NOW()),
(18, 18, NOW(), '2025-11-01', 5, 11000.00, 'SUCCESS', NOW(), NOW()),
(19, 19, NOW(), '2025-12-01', 4, 6800.00, 'SUCCESS', NOW(), NOW()),
(20, 20, NOW(), '2026-01-01', 3, 6600.00, 'FAILED', NOW(), NOW()),
(21, 21, NOW(), '2026-02-01', 2, 3400.00, 'SUCCESS', NOW(), NOW()),
(22, 22, NOW(), '2026-03-01', 1, 1800.00, 'FAILED', NOW(), NOW()),
(23, 23, NOW(), '2026-04-01', 3, 5400.00, 'SUCCESS', NOW(), NOW()),
(24, 24, NOW(), '2026-05-01', 4, 8800.00, 'SUCCESS', NOW(), NOW()),
(25, 25, NOW(), '2026-06-01', 2, 4000.00, 'SUCCESS', NOW(), NOW()),
(26, 26, NOW(), '2026-07-01', 3, 6000.00, 'FAILED', NOW(), NOW()),
(27, 27, NOW(), '2026-08-01', 1, 1500.00, 'SUCCESS', NOW(), NOW()),
(28, 28, NOW(), '2026-09-01', 2, 4400.00, 'SUCCESS', NOW(), NOW()),
(29, 29, NOW(), '2026-10-01', 4, 8800.00, 'FAILED', NOW(), NOW()),
(30, 30, NOW(), '2026-11-01', 3, 6600.00, 'SUCCESS', NOW(), NOW()),
(31, 31, NOW(), '2026-12-01', 5, 11000.00, 'SUCCESS', NOW(), NOW()),
(32, 32, NOW(), '2027-01-01', 2, 3400.00, 'FAILED', NOW(), NOW()),
(33, 33, NOW(), '2027-02-01', 1, 1700.00, 'SUCCESS', NOW(), NOW()),
(34, 34, NOW(), '2027-03-01', 4, 8800.00, 'SUCCESS', NOW(), NOW()),
(35, 35, NOW(), '2027-04-01', 3, 5100.00, 'FAILED', NOW(), NOW()),
(36, 36, NOW(), '2027-05-01', 5, 10500.00, 'SUCCESS', NOW(), NOW()),
(37, 37, NOW(), '2027-06-01', 2, 4400.00, 'SUCCESS', NOW(), NOW()),
(38, 38, NOW(), '2027-07-01', 1, 1500.00, 'SUCCESS', NOW(), NOW()),
(39, 39, NOW(), '2027-08-01', 3, 5100.00, 'FAILED', NOW(), NOW()),
(40, 40, NOW(), '2027-09-01', 2, 4000.00, 'SUCCESS', NOW(), NOW()),
(41, 41, NOW(), '2027-10-01', 4, 8800.00, 'SUCCESS', NOW(), NOW()),
(42, 42, NOW(), '2027-11-01', 3, 5100.00, 'FAILED', NOW(), NOW()),
(43, 43, NOW(), '2027-12-01', 2, 4000.00, 'SUCCESS', NOW(), NOW()),
(44, 44, NOW(), '2028-01-01', 1, 1600.00, 'SUCCESS', NOW(), NOW()),
(45, 45, NOW(), '2028-02-01', 3, 4800.00, 'FAILED', NOW(), NOW()),
(46, 46, NOW(), '2028-03-01', 5, 10000.00, 'SUCCESS', NOW(), NOW()),
(47, 47, NOW(), '2028-04-01', 2, 4000.00, 'SUCCESS', NOW(), NOW()),
(48, 48, NOW(), '2028-05-01', 1, 1500.00, 'FAILED', NOW(), NOW()),
(49, 49, NOW(), '2028-06-01', 4, 8800.00, 'SUCCESS', NOW(), NOW()),
(50, 50, NOW(), '2028-07-01', 3, 5100.00, 'FAILED', NOW(), NOW());
INSERT INTO Payment (booking_id, payment_date, amount, payment_method, payment_status, transaction_id)
VALUES
(1, NOW(), 3000.00, 'GCASH', 'SUCCESS', 'TXN001'),
(2, NOW(), 6000.00, 'GCASH', 'SUCCESS', 'TXN002'),
(3, NOW(), 1200.00, 'GCASH', 'FAILED', 'TXN003'),
(4, NOW(), 10000.00, 'GCASH', 'SUCCESS', 'TXN004'),
(5, NOW(), 2200.00, 'GCASH', 'SUCCESS', 'TXN005'),
(6, NOW(), 1300.00, 'GCASH', 'SUCCESS', 'TXN006'),
(7, NOW(), 4350.00, 'GCASH', 'FAILED', 'TXN007'),
(8, NOW(), 15000.00, 'GCASH', 'SUCCESS', 'TXN008'),
(9, NOW(), 3400.00, 'GCASH', 'SUCCESS', 'TXN009'),
(10, NOW(), 2200.00, 'GCASH', 'FAILED', 'TXN010'),
(11, NOW(), 4800.00, 'GCASH', 'SUCCESS', 'TXN011'),
(12, NOW(), 8800.00, 'GCASH', 'SUCCESS', 'TXN012'),
(13, NOW(), 5400.00, 'GCASH', 'FAILED', 'TXN013'),
(14, NOW(), 4000.00, 'GCASH', 'SUCCESS', 'TXN014'),
(15, NOW(), 1600.00, 'GCASH', 'SUCCESS', 'TXN015'),
(16, NOW(), 4800.00, 'GCASH', 'FAILED', 'TXN016'),
(17, NOW(), 4400.00, 'GCASH', 'SUCCESS', 'TXN017'),
(18, NOW(), 11000.00, 'GCASH', 'SUCCESS', 'TXN018'),
(19, NOW(), 6800.00, 'GCASH', 'SUCCESS', 'TXN019'),
(20, NOW(), 6600.00, 'GCASH', 'FAILED', 'TXN020'),
(21, NOW(), 3400.00, 'GCASH', 'SUCCESS', 'TXN021'),
(22, NOW(), 1800.00, 'GCASH', 'FAILED', 'TXN022'),
(23, NOW(), 5400.00, 'GCASH', 'SUCCESS', 'TXN023'),
(24, NOW(), 8800.00, 'GCASH', 'SUCCESS', 'TXN024'),
(25, NOW(), 4000.00, 'GCASH', 'SUCCESS', 'TXN025'),
(26, NOW(), 6000.00, 'GCASH', 'FAILED', 'TXN026'),
(27, NOW(), 1500.00, 'GCASH', 'SUCCESS', 'TXN027'),
(28, NOW(), 4400.00, 'GCASH', 'SUCCESS', 'TXN028'),
(29, NOW(), 8800.00, 'GCASH', 'FAILED', 'TXN029'),
(30, NOW(), 6600.00, 'GCASH', 'SUCCESS', 'TXN030'),
(31, NOW(), 11000.00, 'GCASH', 'SUCCESS', 'TXN031'),
(32, NOW(), 3400.00, 'GCASH', 'FAILED', 'TXN032'),
(33, NOW(), 1700.00, 'GCASH', 'SUCCESS', 'TXN033'),
(34, NOW(), 8800.00, 'GCASH', 'SUCCESS', 'TXN034'),
(35, NOW(), 5100.00, 'GCASH', 'FAILED', 'TXN035'),
(36, NOW(), 10500.00, 'GCASH', 'SUCCESS', 'TXN036'),
(37, NOW(), 4400.00, 'GCASH', 'SUCCESS', 'TXN037'),
(38, NOW(), 1500.00, 'GCASH', 'SUCCESS', 'TXN038'),
(39, NOW(), 5100.00, 'GCASH', 'FAILED', 'TXN039'),
(40, NOW(), 4000.00, 'GCASH', 'SUCCESS', 'TXN040'),
(41, NOW(), 8800.00, 'GCASH', 'SUCCESS', 'TXN041'),
(42, NOW(), 5100.00, 'GCASH', 'FAILED', 'TXN042'),
(43, NOW(), 4000.00, 'GCASH', 'SUCCESS', 'TXN043'),
(44, NOW(), 1600.00, 'GCASH', 'SUCCESS', 'TXN044'),
(45, NOW(), 4800.00, 'GCASH', 'FAILED', 'TXN045'),
(46, NOW(), 10000.00, 'GCASH', 'SUCCESS', 'TXN046'),
(47, NOW(), 4000.00, 'GCASH', 'SUCCESS', 'TXN047'),
(48, NOW(), 1500.00, 'GCASH', 'FAILED', 'TXN048'),
(49, NOW(), 8800.00, 'GCASH', 'SUCCESS', 'TXN049'),
(50, NOW(), 5100.00, 'GCASH', 'FAILED', 'TXN050');
INSERT INTO Reviews (user_id, tour_id, rating, comment, created_at)
VALUES
(1, 1, 5, 'Absolutely fantastic! Paris was stunning, and the guide was wonderful.', NOW()),
(2, 2, 4, 'The views were great, but it was a bit crowded.', NOW()),
(3, 3, 3, 'The tour was okay, but I expected more history.', NOW()),
(4, 4, 5, 'A once-in-a-lifetime safari! Highly recommend it.', NOW()),
(5, 5, 4, 'The beach was beautiful, but the hotel could use some improvements.', NOW()),
(6, 6, 5, 'Tokyo was an amazing blend of modernity and tradition. Highly recommended!', NOW()),
(7, 7, 2, 'Sydney was nice, but the weather didnt cooperate. Not the tours fault though.', NOW()),
(8, 8, 4, 'The Amazon rainforest was breathtaking. Some parts were a bit challenging, but totally worth it.', NOW()),
(9, 9, 5, 'New York City was a whirlwind of excitement! A great way to see it all in a short time.', NOW()),
(10, 10, 3, 'The Egyptian sites were impressive, but it felt a little rushed.', NOW()),
(11, 11, 4, 'Greece was beautiful, but the cruise could have had better food options.', NOW()),
(12, 12, 5, 'The Swiss Alps were mesmerizing. A must-see for nature lovers.', NOW()),
(13, 13, 4, 'Rome is steeped in history. The Colosseum tour was incredible.', NOW()),
(14, 14, 5, 'Kenya Safari was the highlight of my travels. Seeing the animals up close was unforgettable!', NOW()),
(15, 15, 4, 'Bali was paradise! The resorts were great, but the water sports could have been better organized.', NOW()),
(16, 16, 4, 'Sydneys beaches and landmarks were spectacular. Would love to return!', NOW()),
(17, 17, 5, 'The Galapagos Islands were otherworldly. A photographer’s dream!', NOW()),
(18, 18, 3, 'South Africa was okay, but the game drives could have been more frequent.', NOW()),
(19, 19, 5, 'Istanbul was an amazing mix of cultures. The historical sites are a must-see!', NOW()),
(20, 20, 4, 'The Maldives was a dream. Beautiful beaches, but a few activities were overpriced.', NOW()),
(21, 21, 4, 'The UK tour was fun, but there was too much time spent on the bus.', NOW()),
(22, 22, 5, 'A very well-organized tour through Italy. The guides were excellent!', NOW()),
(23, 23, 3, 'Sri Lanka had its charm, but I expected more from the beaches.', NOW()),
(24, 24, 4, 'The French Riviera was stunning. Nice balance of relaxation and sightseeing.', NOW()),
(25, 25, 4, 'The Canadian Rockies were beautiful, though I wish we had more time to explore.', NOW()),
(26, 26, 5, 'Peru’s Sacred Valley and Machu Picchu were unforgettable. Highly recommend!', NOW()),
(27, 27, 3, 'Costa Rica was nice, but it felt a bit too touristy for my taste.', NOW()),
(28, 28, 4, 'Dubai was extravagant, and the desert safari was a lot of fun.', NOW()),
(29, 29, 4, 'Egypt was amazing, but the heat made it a little challenging to enjoy at times.', NOW()),
(30, 30, 5, 'The Greek Islands were beyond beautiful! The sailing experience was fantastic!', NOW()),
(31, 31, 3, 'The Caribbean was lovely, but the tour could have been more informative.', NOW()),
(32, 32, 5, 'Patagonia was absolutely breathtaking. A photographer’s paradise.', NOW()),
(33, 33, 4, 'The Swiss cities were charming, but some of the hikes were too difficult for beginners.', NOW()),
(34, 34, 3, 'Istanbul was great, but the tour was a bit too rushed for my liking.', NOW()),
(35, 35, 5, 'Japan’s combination of modernity and tradition made it an unforgettable trip.', NOW()),
(36, 36, 4, 'The Costa Rican rainforests were lovely, though a bit too many mosquitoes.', NOW()),
(37, 37, 5, 'Hawaii was paradise! A great mix of adventure and relaxation.', NOW()),
(38, 38, 4, 'Ireland’s countryside was beautiful, but the pubs were too crowded for my liking.', NOW()),
(39, 39, 5, 'The Amazon was incredibly lush and vibrant. Definitely a once-in-a-lifetime experience.', NOW()),
(40, 40, 3, 'South Korea was interesting, but the city tours could have been better planned.', NOW()),
(41, 41, 4, 'The Mediterranean coast of Spain was beautiful, but the beaches were a little too crowded.', NOW()),
(42, 42, 5, 'The French countryside was spectacular. A very peaceful and serene experience.', NOW()),
(43, 43, 4, 'Scotland’s highlands were magnificent, though the weather could have been better.', NOW()),
(44, 44, 3, 'Thailand was nice, but I expected a bit more from the cultural sites.', NOW()),
(45, 45, 4, 'The Czech castles were beautiful, but the tour was a bit long for my liking.', NOW()),
(46, 46, 5, 'The Northern Lights in Iceland were magical. A trip I’ll never forget!', NOW()),
(47, 47, 4, 'Peru’s mountains were stunning, but the hike was tougher than I expected.', NOW()),
(48, 48, 5, 'The tour through Egypt was perfect. Seeing the pyramids in person was surreal!', NOW()),
(49, 49, 3, 'The Bahamas was beautiful, but the tour was too short to fully enjoy the islands.', NOW()),
(50, 50, 4, 'New York City never disappoints! The tour was amazing, though a bit rushed.', NOW());
INSERT INTO AdminLogs (admin_id, action_type, description, timestamp)
VALUES
(1, 'ADD', 'Added new tour: Paris Adventure', NOW()),
(2, 'UPDATE', 'Updated price for Swiss Alps Escape', NOW()),
(3, 'DELETE', 'Removed booking for user 3', NOW()),
(4, 'ADD', 'Added new user: user 4', NOW()),
(5, 'UPDATE', 'Updated booking status for user 5', NOW()),
(6, 'ADD', 'Added new tour: Tokyo City Lights', NOW()),
(7, 'DELETE', 'Deleted review for tour 7', NOW()),
(8, 'UPDATE', 'Updated payment status for booking 8', NOW()),
(9, 'ADD', 'Added new booking for user 9', NOW()),
(10, 'UPDATE', 'Updated email address for user 10', NOW()),
(11, 'DELETE', 'Deleted booking for user 11', NOW()),
(12, 'ADD', 'Added new tour: Greek Isles Cruise', NOW()),
(13, 'UPDATE', 'Updated price for tour 13', NOW()),
(14, 'DELETE', 'Removed payment record for booking 14', NOW()),
(15, 'ADD', 'Added new user: user 15', NOW()),
(16, 'UPDATE', 'Updated user role for user 16', NOW()),
(17, 'DELETE', 'Removed review for tour 17', NOW()),
(18, 'ADD', 'Added new booking for user 18', NOW()),
(19, 'UPDATE', 'Updated seats booked for booking 19', NOW()),
(20, 'DELETE', 'Deleted user 20 from the system', NOW()),
(21, 'ADD', 'Added new tour: Bali Beach Holiday', NOW()),
(22, 'UPDATE', 'Updated payment method for booking 22', NOW()),
(23, 'DELETE', 'Deleted payment for booking 23', NOW()),
(24, 'ADD', 'Added new user: user 24', NOW()),
(25, 'UPDATE', 'Updated travel dates for booking 25', NOW()),
(26, 'DELETE', 'Removed tour: Sydney Under the Sun', NOW()),
(27, 'ADD', 'Added new booking for user 27', NOW()),
(28, 'UPDATE', 'Updated payment status for booking 28', NOW()),
(29, 'DELETE', 'Deleted booking for user 29', NOW()),
(30, 'ADD', 'Added new review for tour 30', NOW()),
(31, 'UPDATE', 'Updated tour description for tour 31', NOW()),
(32, 'DELETE', 'Deleted review for user 32', NOW()),
(33, 'ADD', 'Added new tour: Kenya Safari', NOW()),
(34, 'UPDATE', 'Updated tour dates for tour 34', NOW()),
(35, 'DELETE', 'Removed booking for user 35', NOW()),
(36, 'ADD', 'Added new user: user 36', NOW()),
(37, 'UPDATE', 'Updated payment amount for booking 37', NOW()),
(38, 'DELETE', 'Deleted booking for user 38', NOW()),
(39, 'ADD', 'Added new tour: Rome Uncovered', NOW()),
(40, 'UPDATE', 'Updated booking status for user 40', NOW()),
(41, 'DELETE', 'Removed review for tour 41', NOW()),
(42, 'ADD', 'Added new user: user 42', NOW()),
(43, 'UPDATE', 'Updated user details for user 43', NOW()),
(44, 'DELETE', 'Deleted user 44 from the system', NOW()),
(45, 'ADD', 'Added new booking for user 45', NOW()),
(46, 'UPDATE', 'Updated payment status for booking 46', NOW()),
(47, 'DELETE', 'Removed payment for booking 47', NOW()),
(48, 'ADD', 'Added new tour: Swiss Alps Escape', NOW()),
(49, 'UPDATE', 'Updated description for tour 49', NOW()),
(50, 'DELETE', 'Deleted user 50 from the system', NOW());
