Question

For the group project, you will be creating a database system to solve a business problem of your choice. The database system must meet the criteria shown below. In addition to the database solution, each student will prepare a system summary.

Database System Requirements (Group Work):

• Create a database and name it ITCO630_GPx where "x" is your group letter. Populate your database with appropriate test data.
• Include all of the scripts in a single file called ITCO630_GPx.SQL where x is your group letter.
• Create at least 3 related tables in the database.
• Create at least 2 stored procedures and a script to use each of them.
• Create at least 1 view (using a script).
• Create at least 1 trigger.
• Create at least 5 useful queries. At least one of these queries should use a subquery and at least 1 of the queries should use an aggregate function.
• All scripts should be well-documented.

Answer 1

`Hey,

Note: Brother if you have any queries related the answer please do comment. I would be very happy to resolve all your queries.

Business problem :

ResolveQ -The Ultimate Solution

ResolveQ is intended to be a Paid portal where a developer, whether novice or expert treated on an equal level. A place where questions related to information technology (IT) solved. If a user puts up a request and the admin ratifies it, then he can expect to get a solution simply because there are so many people out there and it is very likely that one of them may have faced the problem before and are willing to help.

A user may post a question request or submit an entire specification for a project. The facilities provided by the website will handle the request. The organisation handling the website employs the Admin and Experts. These are the backbone of the website and ensure its smooth functioning. Unlike other request-reply sites where nearly everything is dependent on voluntary contributions by users, here we have an efficient support structure in place.

An Admin processes all incoming requests or questions. He view the questions and their and there category or zone to classify them, and according assign an expert of the same zone. After his assignment, the assignment viewed by the expert and has to give the answer in specific time. On other hand registered user can also provide the answers to the questions.

This system contains below tables :

1.Questions

2.Answers

3.Expert

4.Category

Database Creation :

This demonstration is using SQL Server.

Database Name :ITCO630_GPx (Note : where "x" is your group letter change this)

SQL Query :

/*Creating database ITCO630_GPx */

create database ITCO630_GPx

Screen in SSMS (SQL Server Management Studio) :

CREATING TABLES :

1.Questions : This table is used to store the questions asked by user.

Table Structure :

Field	Field Name	Data Type	Width	Constraints	Discription
1	Qid	Int		Primary key	Question Id
2	Question	Text		Not Null	Question
3	CategoryId	Int		Foreign Key	Category
4	Time	Datetime		Not Null	Time of asking
5	Empuserid	Varchar	20	-	Expert assigned to give answer

SQL Script for creating table:

/*Table : Questions*/
create table Questions(
Qid int primary key Identity ,
Question Text not null,
CategoryId int foreign key references category ,
Time DateTime not null,
Empuserid varchar(20) references expert)

SQL script for inserting records :

/*Inserting records into Questions table*/
insert into Questions values('Database that is owned by Microsoft',1001,18/06/2018,'viratkohli')
insert into Questions values('Javascript is server side scripting language',1002,18/06/2018,'SachinTendulkar')
insert into Questions values('Visual Studio is an',1003,18/06/2018,'RohitSharma')

Fetching records from Questions Table :

/* Fetching records from Questions Table */
select * from Questions

Screen in SSMS :

2.Answers : This table is used to store the answers of each question

Field	Field Name	Data Type	Width	Constraints	Discription
1	Ansid	Int		Primary key	Answer ID
2		Text		Not Null	Answer body
3	Qid	Int		Foreign key	Related question
4	Views	Int		Not Null	Number of views
5	Empuserid	Varchar	20	Not Null	Employee id

SQL Script for creating table:

/*Table : Answers*/
create table answers (
AnsId int primary key Identity(501,1),
Answer Text not null,
Qid int foreign key references questions,
views int not null,
Empuserid varchar(20) not null)

SQL script for inserting records :

/* Inserting records into Answers */
insert into answers values('SQL Server',2,1,'viratkohli')
insert into answers values('False',3,2,'sachintendulkar')
insert into answers values('IDE',4,5,'RohitSharma')

Fetching records from Answer table :

/*Fetching records from Answer Table*/
select * from answers

Screen in SSMS :

3.Expert : This table is used to store the details of expert.

Field	Field Name	Data Type	Width	Constraints	Discription
1	Empuserid	Varchar	20	Primary key	Employee id
2	Password	Varchar	20	Not Null	Password of the employee
3	CategoryId	Int		Foreign key	Category Id

SQL Script for creating table:

/*Table : Expert */
create table Expert(
Empuserid varchar(20) primary key,
Password varchar(20) not null,
CategoryId int foreign key references category)

SQL script for inserting records :

/*Inserting records into Expert table*/
insert into Expert values('ViratKohli','virat@123',1001)
insert into Expert values('SachinTendulkar','sachin@123',1002)
insert into Expert values('RohitSharma','rohit@123',1003)
insert into Expert values('AjinkyaRahne','Ajinkya@123',1004)
insert into Expert values('HardikPandya','Hardik@123',1005)

Fetching Records from Expert Table:

/*Records from Expert Table*/
select * from Expert

Screen in SSMS :

4.Category : This table is used to store category of question.

Field	Field Name	Data Type	Width	Constraints	Discription
1	CategoryId	Int		Primary key	Category Id
2	Category	Varchar	50	Not Null	Category Name
3	Description	Text		-	Description

SQL Script for creating table:

/*Table : Category */
create table Category(
CategoryId int primary key Identity(1001,1),
Category varchar(50) not null,
Description Text not null
)

SQL script for inserting records :

/*Inserting records into Category table*/
insert into Category values('Database','This category contains questions related to all databases')
insert into Category values('Programming','This category contains questions related to all programming languages')
insert into Category values('Software','This category contains questions related to all Software')
insert into Category values('Hardware','This category contains questions related to all Hardware')
insert into Category values('Storage','This category contains questions related to storage devices')

Fetching records from Category table :

/*Records from Category Table*/
select * from Category

Screen in SSMS :

SQL Queries :

1. Selecting all questions along with answers

SQL Script :

select questions.Qid, Question , answer from questions , answers where questions.qid=answers.qid

Screen in SSMS :

2.select all the experts that belongs to each category

SQL Query :

/*select all the experts that belongs to each category */

select Empuserid , password , Category, Description from expert full outer join Category on
expert.CategoryId=Category.CategoryId

Screen in SSMS :

3. Counting all experts (using aggregate function)

SQL Script :

/*counting all experts*/
select count(Empuserid) as count , Empuserid from expert group by Empuserid

Screen in SSMS :

Creating View :

create view to get all the records from question table :

/* view */
create view getQuestions as (
select * from questions)

/*executing views*/

select * from getQuestions;

Screen in SSMS :

Stored procedure :

1. To get all categories

SQL Script :

/* stored procedure */
create procedure getAllCategories
as
begin
select * from Category;
end

Executing stored procedure :

exec getAllCategories;

Screen in SSMS :

2.to get all answers :

SQL Script :

/*Stored procedure to get all answers*/
create procedure getAllAnswers
as
begin
select * from answers;
end

Executing procedure :

/*executing stored procedure*/
exec getAllAnswers;

Screen in SSMS :

Trigger :

A trigger is a special type of stored procedure that automatically executes when an event occurs in the database server.

Kindly revert for any queries

Thanks.