Question

In this assignment, you will practice what your instructor taught in Week 6.  The assignment is worth 100 points.

• (50 Points)Create a new database in SSMS. This database should contain two relations with a primary to foregin key relationship.  Next, create a database diagram for these relations using SSMS as described in the lecture.  The relations can describe any reasonable business process (e.g. Employees-Dependents, Advisors-Students, Students-Interests, etc). Populate these relations with 10 records per relation.  
• (50 points) Script your new database as described in the lecture.  Save your script (schema and data) in the following name format: “Lastname-DBCreateScript” (e.g. Davis-DBCreateScript).

   

Answer 1

Dear Student ,

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

This demonstration is using SQL Server 2014.Database and Tables are created using SQL Server Management Studio (SSMS).

This database maintains records about the trainers and Training that has been taken in the different organizations.

Database :

--create database
create database Davis_DBCreateScript;

--use database to create tables
use Davis_DBCreateScript;

Tables :

1.Table Name :TrainersDetails

--1.Table Name : TrainersDetails
Create table TrainersDetails(
TID int primary key,
TName varchar(100) not null,
Experience decimal(4,2) not null check (Experience > 2),
skills varchar(100) not null,
address text
);

/*Inserting records into TrainersDetails*/
insert into TrainersDetails values(1,'Rohan',3.3,'MS Azure','Berlin');
insert into TrainersDetails values(2,'Ganesh',18,'DBMS','US');
insert into TrainersDetails values(3,'Mahesh Sabnis',18,'Angular 7','Chennai');
insert into TrainersDetails values(4,'Ajinkya Rahane',5.6,'MongoDB','Hyderabad');
insert into TrainersDetails values(5,'KL Rahul',2.2,'Responsive web Design','Bangluru');
insert into TrainersDetails values(6,'Sanju Samson',2.2,'Node.js','Kochi');
insert into TrainersDetails values(7,'Eoan Morgan',7.7,'Node with ExpressJS','Jaipur');
insert into TrainersDetails values(8,'Hardik Pandey',4.4,'Full Stack','Badoda');
insert into TrainersDetails values(9,'Virat Kohli',3,'Cricket coaching','Pune');
insert into TrainersDetails values(10,'Savroav',15.1,'DBA','Kolkatta');

/*selecting records*/
select * from TrainersDetails;

Screen in SSMS :

100% Messages Results TID TName Experience skills address 1Rohan 1 3.30 MS Azure Berin 2 Ganesh 18.00 DBMS US Mahe... 18.00 Angular 7 Chen... Ajinky... 5.60 4 Mongo... Hyder.. 2.20 5 5 KL R... Respon... Bangl.. Node js 6 6 Sanju.. 2.20 Kochi Eoan 7.70 7 7 Node w... Jaipur Badoda 8 8 Hardi... 4.40 Full Stack Cricket Virat 9 3.00 Pune 10 10 Savro... 15.10 DBA Kolka... LO CO LO CO

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

2.Table Name :TrainingsDetails

/*2.Table Name :TrainingsDetails*/
create table TrainingsDetails (
TDID int primary key,
TDName varchar(100) not null ,
TID int not null,
Client varchar(20) not null,
Availability char(4) not null,
TrainingDate date,
foreign key (TID) references TrainersDetails(TID)
);

/*insert records into TrainingsDetails table*/
insert into TrainingsDetails values (101,'JavaScript Basics',1,'NCA Bangluru','NO','02/10/2018');
insert into TrainingsDetails values (102,'MongoDB',1,'Fujitsu Pune','Yes','03/11/2018');
insert into TrainingsDetails values (103,'Mean Stack',9,'HCL Chennai','NO','02/12/2017');
insert into TrainingsDetails values (104,'Getting Started with Angular 7',3,'Microsoft USA','Yes','12/05/2019');
insert into TrainingsDetails values (105,'Cricket Speech',2,'NCA Bangluru','No','01/05/2019');
insert into TrainingsDetails values (106,'Docker Contrainer',10,'JP Mogran Mumbai','Yes','04/02/2019');
insert into TrainingsDetails values (107,'.NET',7,'Sama Software Nashik','Yes','06/06/2019');
insert into TrainingsDetails values (108,'Database',8,'SKS Software Pune','Yes','02/10/2018');
insert into TrainingsDetails values (109,'Angualr 5',2,'NCA Bangluru','Yes','05/05/2019');
insert into TrainingsDetails values (110,'Full stack Development',1,'C2L Solutions','Yes','07/07/2019');

/*selecting records*/
select * from TrainingsDetails;

Screen in SSMS :

100% Results Messages Availability TDID TDName TID Client Training Date NCA Bangluru 2018-02-10 1 101 JavaScript Basics 1 NO Fujitsu Pune 2018-03-11 2 102 Mongo DB 1 Yes 2017-02-12 3 103 Mean Stack 9 HCL Chennai NO 4 104 Getting Started with Anqular 7 3 Microsoft USA Yes 2019-12-05 Cricket Speech NCA Bangluru 5 105 2 No 2019-01-05 10 2019-04-02 6 106 Docker Contrainer JP Mogran Mumbai Yes 7 107 NET 7 Sama Software Nashik Yes 2019-06-06 2018-02-10 8 108 Database SKS Software Pune Yes Angualr 5 NCA Bangluru 2019-05-05 9 109 2 Yes Full stack Development C2L Solutions 2019-07-07 10 110 1 Yes 9 8

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