Question

Complete the following using Microsoft^® SQL Server^® 2016:

• Write SQL scripts in Microsoft^® SQL Server^® for OLTP that include:
• A Data Definition Language (DDL) script creating the four tables with appropriate data types, primary and foreign keys
• Data Manipulation Language (DML) scripts that insert a minimum of five records into each table
• Select scripts showing the full contents of each table
• Write and run a test script for Step Two.
• Save a screenshot of the results.

Create a 6-page Technical Design Document that includes the SQL scripts in Microsoft^® SQL Server^®, test script, and screenshots.

Compress the Technical Design Document that includes the SQL scripts in Microsoft^® SQL Server^®, test script, and screenshots into a ZIP file.

Submit the ZIP file.

Answer 1

Dear Student ,

As per the requirement submitted above , kindly find the below solution.

This demonstration is using Microsoft SQL Server 2016.

Database :

/*create database OrganizationDB*/
create database OrganizationDB;

/*use OrganizationDB to create table*/
use OrganizationDB;

Tables :

1.Table Name :Employee_Trainer

SQL DDL and DML Script :

/*1.Table Name : Employee_Trainer*/
Create table Employee_Trainer(
Employee_Trainer_ID int primary key identity,
Employee_Trainer_Name varchar(30) not null,
Employee_Trainer_Experience int not null check (Employee_Trainer_Experience > 5),
Employee_Trainer_Technologies text,
Employee_Trainer_Contact varchar(10)
);

/*Inserting records into Trainers*/
insert into Employee_Trainer (Employee_Trainer_Name,Employee_Trainer_Experience,Employee_Trainer_Technologies,Employee_Trainer_Contact)
values('KD Jadhav',10,'Java,C++','8888992233'),
('Tendulkar SR',25,'MongoDB','4455663322'),
('Mahesh Nigde',8,'AngularJS,Angular2,4,5,6,7','123456890'),
('Ajinkya Kamble',6,'ReactJS','7894561230'),
('Rahul D',15,'Responsive web Design','4477889922');

/*selecting records*/
select * from Employee_Trainer;

Screen in SSMS :

/*1. Table Name : Employee_Trainer*/ Create table Employee_Trainer Employee_Trainer_ID int primary key identity, Employee_Trainer_Name varchar(30) not null, Employee_Trainer_Experience int not null check (Employee_Trainer_Experience > 5), Employee_Trainer_Technologies text, Employee_Trainer_Contact varchar(10) /*Inserting records into Employee_Trainer*/ insert into Employee_Trainer (Employee_Trainer_Name, Employee_Trainer_Experience, Employee_Trainer_Technologies, Employee_Trainer_Contact) values('KD Jadhav',10,'Java, C++', '8888992233'), ('Tendulkar SR', 25, 'MongoDB', '4455663322'), ('Mahesh Nigde', 8, 'AngularJS, Angular2,4,5,6,7', '123456890'), ('Ajinkya Kamble', 6, 'ReactJS', '7894561230'), ('Rahul D',15, 'Responsive web Design', '4477889922'); /*selecting records*/ select * from Employee_Trainer:|| 100 % . Results Messages Employee_Trainer_ID Employee_Trainer_Name KD Jadhav Tendulkar SR 3 3 Mahesh Nigde 4 4 Ajinkya Kamble Rahul D Employee_Trainer_Experience 2 2 Employee_Trainer_Technologies Java.C++ MongoDB AngularJS, Angular2.4,5,6,7 ReactJS Responsive web Design Employee_Trainer_Contact 8888992233 4455663322 123456890 7894561230 4477889922

****************************************

2.Table Name :Organization_Training

SQL DDL and DML Script :

/*2.Table Name :Organization_Training*/
create table Organization_Training (
Organization_Training_ID int primary key,
Organization_Training_details text not null ,
Organization_Training_Name varchar(50) not null,
Start_Date date,
Number_of_Days int not null,
Employee_Trainer_ID int not null,
foreign key (Employee_Trainer_ID) references Employee_Trainer(Employee_Trainer_ID)
);

/*insert records into Trainings table*/
insert into Organization_Training values (101,'ReactJS','GEP Mumbai','2019-08-29',5,1);
insert into Organization_Training values (102,'Machine Learning','Persistent USA','2019-12-12',25,2);
insert into Organization_Training values (103,'Open Source Technologies','Microsoft India','2019-10-28',10,3);
insert into Organization_Training values (104,'.Net core','Oracle','2019-09-08',6,5);
insert into Organization_Training values (105,'Struts 2','ADP','2020-02-12',3,4);

/*selecting records*/
select * from Organization_Training;

Screen in SSMS :

/*2. Table Name : Organization Training*/ create table Organization Training Organization Training ID int primary key, Organization Training details text not null, Organization Training_Name varchar(50) not null, Start_Date date, Number_of_Days int not null, Employee_Trainer_ID int not null, foreign key (Employee_Trainer_ID) references Employee_Trainer (Employee_Trainer_ID) /*insert records into Organization Training table"/ insert into Organization Training values (101, 'ReactJS', 'GEP Mumbai', '2019-08-29',5,1); insert into Organization Training values (102, 'Machine Learning', 'Persistent USA', '2019-12-12', 25, 2); insert into Organization Training values (103, 'Open Source Technologies', 'Microsoft India', '2019-10-28',10,3); insert into Organization Training values (104,'.Net core', 'Oracle', '2019-09-08',6,5); insert into Organization_Training values (105, 'Struts 2', 'ADP', '2020-02-12',3,4); /*selecting records*/ select * from Organization_Training: Employee_Trainer_ID 100 % - Results Messages Organization_Training_ID 1 101 2 102 3 103 4 104 Organization Training_details ReactJS Machine Learning Open Source Technologies .Net core Struts 2 Organization_Training_Name GEP Mumbai Persistent USA Microsoft India Oracle ADP Start_Date 2019-08-29 2019-12-12 2019-10-28 2019-09-08 2020-02-12 Number_of_Days 5 25 10 6 3 w

****************************************

3.Table Name :ParticipantsDetails

SQL DDL and DML Script :

/*3.Table Name :ParticipantsDetails*/
create table ParticipantsDetails(
ParticipantID int primary key,
ParticipantName varchar(20) not null,
WorkingCompany varchar(20),
contact varchar(10)
);

/*Inserting records*/
insert into ParticipantsDetails (ParticipantID,ParticipantName,WorkingCompany,contact) values
(1,'Yo Mahesh','Magic Software','456237890'),
(2,'Peithvi Shaw','USB Corp','1234560000'),
(3,'Ben Stocks','Dream System','7845123690'),
(4,'Ken Jan','ABCD Corp','8888888888'),
(5,'Sam ram','Swiss Bank','9999999988');

/*selecting records*/
select * from ParticipantsDetails;

Screen in SSMS :

/*3. Table Name : ParticipantsDetails*/ create table ParticipantsDetails ParticipantID int primary key, Participant Name varchar(20) not null, Working Company varchar(20), contact varchar(10) /*Inserting records*/ Ginsert into ParticipantsDetails (ParticipantID, ParticipantName, Working Company, contact) values (1, 'Yo Mahesh', 'Magic Software', '456237890'), (2, 'Peithvi Shaw', 'USB Corp', '1234560000'), (3, 'Ben Stocks', 'Dream System', '7845123690'), (4,'Ken Jan', 'ABCD Corp', '8888888888'), (5, 'Sam ram', 'Swiss Bank', '9999999988'); /*selecting records*/ select * from ParticipantsDetails; 100% Results Messages ParticipantID Participant Name 1 / 1 Yo Mahesh 2 2 Peithvi Shaw 33 Ben Stocks 4 4 Ken Jan Sam ram Working Company Magic Software USB Corp Dream System ABCD Corp Swiss Bank contact 456237890 1234560000 7845123690 8888888888 9999999988

****************************************

4.Table Name :Trainer_Training_Feedback

SQL DDL and DML Script :

/*4.Table Name :Trainer_Training_Feedback*/
create table Trainer_Training_Feedback(
Feedback_ID int primary key,
Participant_ID int not null,
Organization_Training_ID int not null,
Ratings decimal(6,2),
Comments text,
foreign key (Organization_Training_ID) references Organization_Training (Organization_Training_ID),
foreign key (Participant_ID) references ParticipantsDetails(ParticipantID)
);

/*insert records into Trainer_Training_Feedback table*/
insert into Trainer_Training_Feedback values (1,1,101,8,'Excellent');
insert into Trainer_Training_Feedback values (2,2,102,6.5,'Okay');
insert into Trainer_Training_Feedback values (3,3,103,8.0,'Great');
insert into Trainer_Training_Feedback values (4,4,104,2.0,'Needs lots of improvements');
insert into Trainer_Training_Feedback values (5,5,101,10,'Excellent');

/*selecting records*/
select * from Trainer_Training_Feedback;

Screen in SSMS :

/*4. Table Name : Trainer_Training_Feedback*/ create table Trainer_Training Feedback Feedback_ID int primary key, Participant_ID int not null, Organization Training_ID int not null, Ratings decimal(6,2), Comments text, foreign key (Organization Training_ID) references Organization Training Organization Training_ID), foreign key (Participant_ID) references ParticipantsDetails(ParticipantID) /*insert records into Trainer Training Feedback table*/ insert into Trainer_Training Feedback values (1,1,101,8, 'Excellent'); insert into Trainer Training_Feedback values (2,2,102,6.5, 'Okay'); insert into Trainer Training_Feedback values (3,3,103,8., 'Great'); insert into Trainer_Training_Feedback values (4,4, 104,2.0, 'Needs lots of improvements'); insert into Trainer Training Feedback values (5,5,101,10,'Excellent'); /*selecting records*/ select * from Trainer_Training Feedback; 100 % . Results Messages Feedback_ID Participant_ID 1 11 2 2 3 3 Organization_Training_ID 101 102 103 Ratings 8.00 6.50 8.00 2.00 10.00 Comments Excellent Okay Great Needs lots of improvements Excellent

****************************************

NOTE : PLEASE FEEL FREE TO PROVIDE FEEDBACK ABOUT THE SOLUTION.