CS403 assignment no 3 Complete Solution | How to create database and tables

HTML

• CSS
• JAVA
• PHP
• PHP and MYSQL
• C++
• VU Solution
  Solution CS411 Assignment 3 CS402 Assignment Solution CS304 Assignment no 3 Solution CS403 assignment no 3 Complete Solution

CS403 ASSIGNMENT NO 3 SOLUTION SPRING 2019
you are required to enter your id in database name if you didn’t enter your id your marks will be zero. Just replace the Enteryouridhere with your Virtual university id i.e (mc111111111)
Q1:

Query for CreatingDatebase:

CREATE DATABASE Enteryouridhere

Screenshot of Query Execution:



Q2:

Query  forCreatRoleTable;


CREATE TABLE Role ( Role_idvarchar(255) NOT NULL PRIMARY KEY, Name varchar(255))

Screenshot of Query Execution:


Query  toCreatUserTable;

CREATE TABLE Users ( User_idvarchar(255) NOT NULL PRIMARY KEY, Name varchar(255),Role_idvarchar(255) FOREIGN KEY REFERENCES Role(Role_ID))

Screenshot of Query Execution:




Query  toCreatTask Table;
CREATE TABLE Task ( Task_idvarchar(255) NOT NULL PRIMARY KEY,
titlevarchar(255),
creation_date date,
dead_line date,
assign_tovarchar(255),
)


Screenshot of Query Execution:


Query  toCreatDiscussionThread Table;

CREATE TABLE DiscussionThread(Thread_idvarchar(255) NOT NULL PRIMARY KEY,
Task_idvarchar(255) FOREIGN KEY REFERENCES Task(Task_id),
titlevarchar(255)
)
Screenshot of Query Execution:



Query  toCreat Message Table;

CREATE TABLE Messages(Msg_idvarchar(255) NOT NULL PRIMARY KEY,
Tread_idvarchar(255) FOREIGN KEY REFERENCES DiscussionThread(Thread_id),
message_body text,
sender_idvarchar(255)
)
Screenshot of Query Execution:






Q3: After creation of tables in database you are required to add the following records into the Taskand DiscussionThreadtables by writing the appropriate SQL Query.

Query  for Adding Data into Task Table;

INSERT INTO Task (Task_id, title,creation_date,dead_line,assign_to)
VALUES ('T0001','Computer Science','2019/01/01','2019/01/15','1');

INSERT INTO Task (Task_id, title,creation_date,dead_line,assign_to)
VALUES ('T0002','Management Science','2019/02/02','2019/02/16','2');

INSERT INTO Task (Task_id, title,creation_date,dead_line,assign_to)
VALUES ('T0003','Mass Communication','2019/03/03','2019/03/17','3');

INSERT INTO Task (Task_id, title,creation_date,dead_line,assign_to)
VALUES ('T0004','Bio Informatics','2019/04/04','2019/04/20','4');

Screenshot of Query Execution:



Query for Add Data into DisscussionThread Table:

INSERT INTO DiscussionThread (Thread_id,Task_id,title)
VALUES ('Th001','T0001','BSCS');

INSERT INTO DiscussionThread (Thread_id,Task_id,title)
VALUES ('Th002','T0002','BBA');

INSERT INTO DiscussionThread (Thread_id,Task_id,title)
VALUES ('Th003','T0003','MSC zoology');






Screenshot of Query Execution:



Q4: query for retrieving a record from the Task table whoseTask_id is T0003.

Query to Select Data From Task Table:

SELECT * FROM Task WHERE Task_id='T0003'

Screenshot of Query Execution:


Q5: Alter the table of the DiscussionThread and include the “Status” column in the DiscussionThread table.

Query  to Alter DisscussionThread Table;

ALTER TABLE DiscussionThread
ADD Status varchar(100);
Screenshot of Query Execution: