Web Development Lab Assignment 06
Name :- Venkat Swaroop Veerla
Roll No:- HU21CSEN0500267
Welcome to the database schema design and implementation report for the "Solver" project. Solver is a platform
designed to address user doubts and queries effectively. This report outlines the creation of a database schema
tailored to accommodate various user roles, including administrators, question seekers, and experts. Through concise
steps, we demonstrate table creation, data insertion, and execution of diverse queries essential for the functionality
of the Solver project. This database schema serves as the backbone of the platform, facilitating seamless interaction
and resolution of user inquiries.
Task 1: Prepare tables along with required primary keys and foriegn key constraints
Code :
-- Create tables
CREATE TABLE Users (
UserID INT PRIMARY KEY,
UserName VARCHAR(50) NOT NULL,
Email VARCHAR(100) UNIQUE,
UserType ENUM('Admin', 'QuestionSeeker', 'Expert') NOT NULL,
ExpertiseDomain VARCHAR(50),
RecStatus ENUM('Active', 'Inactive') DEFAULT 'Active'
);
CREATE TABLE Questions (
QuestionID INT PRIMARY KEY,
UserID INT,
QuestionText TEXT,
DatePosted DATE,
FOREIGN KEY (UserID) REFERENCES Users(UserID)
);
CREATE TABLE Answers (
AnswerID INT PRIMARY KEY,
QuestionID INT,
UserID INT,
AnswerText TEXT,
DatePosted DATE,
FOREIGN KEY (QuestionID) REFERENCES Questions(QuestionID),
FOREIGN KEY (UserID) REFERENCES Users(UserID)
);
Output :
Task 2 : Fill the required data in the tables.
Code :
-- Insert data into Users table
INSERT INTO Users (UserID, UserName, Email, UserType, ExpertiseDomain, RecStatus) VALUES
(1, 'Admin1', '[email protected]', 'Admin', NULL, 'Active'),
(2, 'QuestionSeeker1', '[email protected]', 'QuestionSeeker', NULL, 'Active'),
(3, 'Expert1', '[email protected]', 'Expert', 'Mathematics', 'Active'),
(4, 'Expert2', '[email protected]', 'Expert', 'Programming', 'Inactive'),
(5, 'Expert3', '[email protected]', 'Expert', 'Physics', 'Active');
-- Insert more random data into Users table
INSERT INTO Users (UserID, UserName, Email, UserType, ExpertiseDomain, RecStatus) VALUES
(6, 'Expert4', '
[email protected]', 'Expert', 'Chemistry', 'Active'),
(7, 'QuestionSeeker2', '
[email protected]', 'QuestionSeeker', NULL, 'Active'),
(8, 'Expert5', '
[email protected]', 'Expert', 'Biology', 'Active'),
(9, 'Expert6', '
[email protected]', 'Expert', 'History', 'Active'),
(10, 'Expert7', '
[email protected]', 'Expert', 'Economics', 'Inactive');
-- Insert data into Questions table
INSERT INTO Questions (QuestionID, UserID, QuestionText, DatePosted) VALUES
(1, 2, 'How to solve linear equations?', '2024-03-01'),
(2, 7, 'What is the theory of relativity?', '2024-03-02');
-- Insert data into Answers table
INSERT INTO Answers (AnswerID, QuestionID, UserID, AnswerText, DatePosted) VALUES
(1, 1, 3, 'You can use the substitution method.', '2024-03-02'),
(2, 2, 5, 'The theory of relativity was proposed by Albert Einstein.', '2024-03-03');
Output :
Task 3: Execute various queries related to your project on the tables.
Queries:
Q1 Get all questions with their respective user:
Code:
SELECT q.QuestionID, q.QuestionText, u.UserName
FROM Questions q
JOIN Users u ON q.UserID = u.UserID;
Output:
Q2 Get all answers for a specific question:
Code:
SELECT a.AnswerID, a.AnswerText, u.UserName
FROM Answers a
JOIN Users u ON a.UserID = u.UserID
WHERE a.QuestionID = 1;
Output:
Q3 Get the user who posted a specific question:
Code:
SELECT u.UserName
FROM Questions q
JOIN Users u ON q.UserID = u.UserID
WHERE q.QuestionID = 1;
Output:
Q4 Get the number of questions posted by each user:
Code:
SELECT u.UserName, COUNT(q.QuestionID) AS NumQuestions
FROM Users u
LEFT JOIN Questions q ON u.UserID = q.UserID
GROUP BY u.UserID, u.UserName;
Output:
