project_management_sql.sql

DROP DATABASE project_management;

CREATE DATABASE project_management;

use project_management;

 CREATE TABLE IF NOT EXISTS project (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255),
  description VARCHAR(255),
  date_created DATE DEFAULT (CURRENT_DATE)
);
CREATE TABLE IF NOT EXISTS member (
   id INT AUTO_INCREMENT PRIMARY KEY,
   name VARCHAR(255),
   email VARCHAR(255),
   gender ENUM('male', 'female', 'other'),
   contact_number CHAR(11)
);
CREATE TABLE IF NOT EXISTS project_member (
  project_id INT,
  member_id INT,
  FOREIGN KEY (project_id) REFERENCES project(id) ON DELETE CASCADE,
  FOREIGN KEY (member_id) REFERENCES member(id) ON DELETE CASCADE,
  PRIMARY KEY (project_id, member_id)
);
CREATE TABLE IF NOT EXISTS task (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255),
  description VARCHAR(255),
  due_date DATE,
  status ENUM('pending', 'ongoing', 'completed'),
  project_id INT,
  FOREIGN KEY (project_id) REFERENCES project(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS task_member (
  task_id INT,
  member_id INT,
  FOREIGN KEY (task_id) REFERENCES task(id) ON DELETE CASCADE,
  FOREIGN KEY (member_id) REFERENCES member(id) ON DELETE CASCADE,
  PRIMARY KEY (task_id, member_id)
);

-- Insert sample data into the project table
INSERT INTO project (name, description, date_created)
VALUES
  ('Project 1', 'Description for Project 1', '2023-01-01'),
  ('Project 2', 'Description for Project 2', '2023-02-01'),
  ('Project 3', 'Description for Project 3', '2023-03-01'),
  ('Project 4', 'Description for Project 4', '2023-04-01'),
  ('Project 5', 'Description for Project 5', '2023-05-01'),
  ('Project 6', 'Description for Project 6', '2023-06-01'),
  ('Project 7', 'Description for Project 7', '2023-07-01'),
  ('Project 8', 'Description for Project 8', '2023-08-01'),
  ('Project 9', 'Description for Project 9', '2023-09-01'),
  ('Project 10', 'Description for Project 10', '2023-10-01');

-- Insert sample data into the member table
INSERT INTO member (name, email, gender, contact_number)
VALUES
  ('John Doe', 'john.doe@example.com', 'male', '1234567890'),
  ('Jane Smith', 'jane.smith@example.com', 'female', '9876543210'),
  ('Mike Johnson', 'mike.johnson@example.com', 'male', '5555555555'),
  ('Emily Davis', 'emily.davis@example.com', 'female', '1111111111'),
  ('Alex Brown', 'alex.brown@example.com', 'male', '9999999999'),
  ('Sarah Wilson', 'sarah.wilson@example.com', 'female', '4444444444'),
  ('Tom Thompson', 'tom.thompson@example.com', 'male', '7777777777'),
  ('Olivia White', 'olivia.white@example.com', 'female', '2222222222'),
  ('Michael Clark', 'michael.clark@example.com', 'male', '8888888888'),
  ('Sophia Taylor', 'sophia.taylor@example.com', 'female', '6666666666'),
  ('James Anderson', 'james.anderson@example.com', 'male', '3333333333'),
  ('Emma Martin', 'emma.martin@example.com', 'female', '5555555555'),
  ('William Harris', 'william.harris@example.com', 'male', '7777777777'),
  ('Ava Thompson', 'ava.thompson@example.com', 'female', '9999999999'),
  ('Daniel Wilson', 'daniel.wilson@example.com', 'male', '2222222222'),
  ('Mia Johnson', 'mia.johnson@example.com', 'female', '4444444444'),
  ('Alexander Davis', 'alexander.davis@example.com', 'male', '6666666666'),
  ('Charlotte Brown', 'charlotte.brown@example.com', 'female', '8888888888'),
  ('Benjamin Taylor', 'benjamin.taylor@example.com', 'male', '1111111111'),
  ('Harper White', 'harper.white@example.com', 'female', '3333333333');

-- Insert sample data into the project_member table
INSERT INTO project_member (project_id, member_id)
VALUES
  (1, 1),
  (1, 2),
  (1, 3),
  (1, 4),
  (1, 5),
  (2, 6),
  (2, 7),
  (2, 8),
  (2, 9),
  (2, 10),
  (3, 11),
  (3, 12),
  (3, 13),
  (3, 14),
  (3, 15),
  (4, 16),
  (4, 17),
  (4, 18),
  (4, 19),
  (4, 20);

-- Insert sample data into the task table
INSERT INTO task (title, description, due_date, status, project_id)
VALUES
  ('Task 1', 'Description for Task 1', '2023-01-10', 'pending', 1),
  ('Task 2', 'Description for Task 2', '2023-02-15', 'ongoing', 1),
  ('Task 3', 'Description for Task 3', '2023-03-20', 'completed', 1),
  ('Task 4', 'Description for Task 4', '2023-04-05', 'pending', 2),
  ('Task 5', 'Description for Task 5', '2023-05-15', 'ongoing', 2),
  ('Task 6', 'Description for Task 6', '2023-06-30', 'completed', 2),
  ('Task 7', 'Description for Task 7', '2023-07-10', 'pending', 3),
  ('Task 8', 'Description for Task 8', '2023-08-20', 'ongoing', 3),
  ('Task 9', 'Description for Task 9', '2023-09-05', 'completed', 3),
  ('Task 10', 'Description for Task 10', '2023-10-15', 'pending', 4),
  ('Task 11', 'Description for Task 11', '2023-11-30', 'ongoing', 4),
  ('Task 12', 'Description for Task 12', '2023-12-10', 'completed', 4),
  ('Task 13', 'Description for Task 13', '2023-01-20', 'pending', 5),
  ('Task 14', 'Description for Task 14', '2023-02-28', 'ongoing', 5),
  ('Task 15', 'Description for Task 15', '2023-03-15', 'completed', 5),
  ('Task 16', 'Description for Task 16', '2023-04-25', 'pending', 6),
  ('Task 17', 'Description for Task 17', '2023-05-10', 'ongoing', 6),
  ('Task 18', 'Description for Task 18', '2023-06-20', 'completed', 6),
  ('Task 19', 'Description for Task 19', '2023-07-05', 'pending', 7),
  ('Task 20', 'Description for Task 20', '2023-08-15', 'ongoing', 7);

-- Insert sample data into the task_member table
INSERT INTO task_member (task_id, member_id)
VALUES
  (1, 1),
  (1, 2),
  (2, 3),
  (2, 4),
  (3, 5),
  (4, 6),
  (4, 7),
  (5, 8),
  (5, 9),
  (6, 10),
  (7, 11),
  (8, 12),
  (8, 13),
  (9, 14),
  (9, 15),
  (10, 16),
  (10, 17),
  (11, 18),
  (12, 19),
  (12, 20),
  (13, 1),
  (14, 2),
  (15, 3),
  (15, 4),
  (16, 5),
  (17, 6),
  (18, 7),
  (18, 8),
  (19, 9),
  (20, 10);