Seed Data - s566466div/GDP-Project-EduLearn-03 GitHub Wiki

SQL Script

Here is the schema created by the SQL script:

• Create database

CREATE DATABASE edulearn; 

• Use the created database

USE edulearn; 

• Creating Tables

• users table:

  CREATE TABLE users (
      user_id INT PRIMARY KEY,
      email VARCHAR(255) NOT NULL,
      password VARCHAR(255) NOT NULL,
      user_type VARCHAR(50) NOT NULL,
      name VARCHAR(255) NOT NULL
  );
  

• courses table:

  CREATE TABLE courses (
      course_id INT PRIMARY KEY,
      user_id INT,
      course_name VARCHAR(255) NOT NULL,
      course_description TEXT,
      enrollment_status VARCHAR(50),
      FOREIGN KEY (user_id) REFERENCES users(user_id)
  );
  

• course_content table:

  CREATE TABLE course_content (
      id INT PRIMARY KEY,
      course_id INT,
      content_type VARCHAR(255),
      content_title VARCHAR(255),
      content_file_path VARCHAR(255),
      FOREIGN KEY (course_id) REFERENCES courses(course_id)
  );
  

• quizzes table:

  CREATE TABLE quizzes (
      id INT PRIMARY KEY,
      course_id INT,
      quiz_title VARCHAR(255),
      description TEXT,
      due_date DATE,
      maximum_score INT,
      grade VARCHAR(2),
      FOREIGN KEY (course_id) REFERENCES courses(course_id)
  );
  

• assignments table:

  CREATE TABLE assignments (
      id INT PRIMARY KEY,
      course_id INT,
      assignment_title VARCHAR(255),
      description TEXT,
      due_date DATE,
      maximum_score INT,
      grade VARCHAR(2),
      FOREIGN KEY (course_id) REFERENCES courses(course_id)
  );
  

• user_progress table:

  CREATE TABLE user_progress (
      user_id INT,
      course_id INT,
      completed_tasks INT,
      pending_tasks INT,
      progress_percentage DECIMAL(5,2),
      FOREIGN KEY (user_id) REFERENCES users(user_id),
      FOREIGN KEY (course_id) REFERENCES courses(course_id),
      PRIMARY KEY (user_id, course_id)
  );
  

Insert Seed Data

Here is a summary of the seed data insertion commands:

• users table:

  INSERT INTO users (user_id, email, password, user_type, name) VALUES
  ('101', '[email protected]', 'mansaManny@3_7_3', 'student', 'Lakshmi Manasa Gundala'),
  ('202', '[email protected]', 'sri@919563', 'student', 'SriLatha Yadala'),
  ('303', '[email protected]', 'sarathy89@92', 'student', 'Partha Sarathy Boda'),
  ('404', '[email protected]', 'divyaNWM@5692', 'instructor', 'Divya Bathala'),
  ('505', '[email protected]', 'VaniNWM@9_1_9', 'instructor', 'Vani Battu');
  

• courses table:

  INSERT INTO courses (course_id, user_id, course_name, course_description, enrollment_status) VALUES
  ('111', '101', 'Web Applications', 'Introduction to Web Applications', 'enrolled'),
  ('222', '202', 'Java', 'Introduction to Java', 'enrolled'),
  ('333', '101', 'Advanced Data Base', 'Introduction to Advanced Data Base', 'enrolled'),
  ('444', '303', 'Project Management', 'Introduction to Project Management', 'enrolled');
  

• course_content table:

  INSERT INTO course_content (id, course_id, content_type, content_title, content_file_path) VALUES
  ('11', '111', 'video lectures', 'Introduction to Web Applications', '/content/file/web_applications_lecture.mp4'),
  ('22', '111', 'reading materials', 'Web Applications Basics', '/content/file/web_applications_basics.pdf'),
  ('33', '222', 'video lectures', 'Introduction to Java', '/content/file/java_lecture.mp4'),
  ('44', '222', 'reading materials', 'Java Fundamentals', '/content/file/java_fundamentals.pdf');
  

• quizzes table:

  INSERT INTO quizzes (id, course_id, quiz_title, description, due_date, maximum_score, grade) VALUES
  ('1', '111', 'Web Applications Quiz 1', 'Test your knowledge on web applications', '2024-05-20', '100', 'A'),
  ('2', '222', 'Java Quiz 1', 'Test your knowledge on Java programming', '2024-05-22', '100', 'A');
  

• assignments table:

  INSERT INTO assignments (id, course_id, assignment_title, description, due_date, maximum_score, grade) VALUES
  ('1011', '111', 'Web Applications Assignment 1', 'Complete the web applications exercises', '2024-05-25', '100', 'A'),
  ('1012', '222', 'Java Assignment 1', 'Complete the Java programming tasks', '2024-05-27', '100', 'A');
  

• user_progress table:

  INSERT INTO user_progress (user_id, course_id, completed_tasks, pending_tasks, progress_percentage) VALUES
  ('101', '111', '2', '1', '66.67'),
  ('202', '222', '1', '2', '33.33');
  

Instructions for Loading Test Data into MySQL Database

1. If the attached SQL dump file doesn't download, you can clone the project from GitHub using the following command:

   git clone https://github.com/s566466div/GDP-Project-EduLearn-03.git
   

2. In the cloned project, navigate to the images directory where you will find the SQL file named edulearn_seed_data.sql.

3. Save the SQL dump content from the GitHub repository into a file named edulearn_seed_data.sql.

4. Open the MySQL Command Line or MySQL Workbench.

5. To use the MySQL Command Line:

   • Start MySQL by entering your user details.
   • Load the SQL dump file using the following command:

     source path/to/edulearn_seed_data.sql;
     

   Replace path/to/edulearn_seed_data.sql with the actual path to your SQL file.

6. To use MySQL Workbench:

   • Open MySQL Workbench and connect to your MySQL server.
   • Go to File -> Open SQL Script... and select the edulearn_seed_data.sql file.
   • Click the Execute button to run the script.

Adding More Records

If you want to add more records to the users table after the initial insertion, you can simply run additional INSERT INTO statements. Here’s how you can do it:

USE edulearn;

INSERT INTO users (user_id, email, password, user_type, name) VALUES
('606', '[email protected]', 'johnDoe@123', 'student', 'John Doe'),
('707', '[email protected]', 'janeDoe@456', 'instructor', 'Jane Doe');

Verify Data Insertion

Once the script execution is complete, verify the data insertion by running simple SELECT queries:

USE edulearn;

SELECT * FROM users;
SELECT * FROM courses;
SELECT * FROM course_content;
SELECT * FROM quizzes;
SELECT * FROM assignments;
SELECT * FROM user_progress;