Question

2.a Please inte rpret the ERD as below. You need to interpret the entities and the meaning of the relationship, including the participation (5 points) 2.b Mapping the ERD into a relational schema (table structures) (5 points). 2.c Show several records in each relation (table) and mark those values that indicate the participation (5 points). DegreeName

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 2014.

Database :

--create database
create database empDegreeDB;

--create tables in the database
use empDegreeDB;

Tables :

1.Table Name :Employee

SQL Query :

/*1.Table Name :Employee*/
create table Employee(
EmployeeID int primary key,
EmployeeName varchar(20) not null
);

/*inserting records into employee table*/
insert into employee values (1,'Virat Kohli');
insert into employee values (2,'Sachin Tendulkar');
insert into employee values (3,'KL Rahul');

/*selecting records*/
select * from employee;

Screen in SSMS :

empDegreeDB.sql - NAGESH-PC.empDegreedB (NAGESH-PCNagesh (55)* File Edit View Query Project Debug Tools Window Help Execute Debug empDegreeDB empDegreeDB.sgl-...Н-PCNagesh (55))* X --create database Ecreate database empDegreeDB; --create tables in the database use empDegreeDB; /*1 . Table Name : Employee*/ create table Employee( EmployeeID int primary key EmployeeName varchar (20) not null /*inserting records into employee table*/ insert into employee values (1, 'Virat Kohli'); insert into employee values (2, 'Sachin Tendulkar') insert into employee values (3, 'KL Rahul'); /*selecting records*/ select from employee; 100 % Results Fa Messages EmployeelD EmployeeName Virat Kohli Sachin Tendulkar KL Rahul

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

2.Table Name :Degree

SQL Query :

/*2.Table Name :Degree*/
create table Degree(
DegreeCode varchar(3) primary key,
DegreeName varchar(20) not null
);

/*inserting records into Degree table*/
insert into Degree values ('C1','MBA');
insert into Degree values ('C2','MCA');
insert into Degree values ('C3','UG');

/*selecting records*/
select * from Degree;

Screen in SSMS :

嫛empDegreeDB.sql _ NAGESH-PC.empDegreeDB (NAGESH-PC\Nagesh File Edit View Query Project Debug Tools Window Help ? Execute Debug empDegreeDB empDegreeDB.sgl-...Н-PCNagesh (55))* X *2.Table Name :Degree/ create table Degree( DegreeCode varchar (3) primary key, DegreeName varchar (20) not null /*inserting records into Degree table*/ insert into Degree values 'C1', 'MBA); insert into Degree values ("C2', 'MCA); insert into Degree values ("C3', 'UG') /*selecting records*/ select * from Degree; 100 % ResultsMessages DegreeCode DegreeName 1 C1 MBA 2 C2 MCA 3 C3 UG

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

3.Table Name :EmployeeDegree

SQL Query :

/*3.Table Name :EmployeeDegree*/
create table EmployeeDegree(
EmployeeID int foreign key references Employee(EmployeeID),
DegreeCode varchar(3) foreign key references Degree(DegreeCode)
);

/*inserting records into EmployeeDegree table*/
insert into EmployeeDegree values (1,'C1');
insert into EmployeeDegree values (1,'C2');
insert into EmployeeDegree values (2,'C3');
insert into EmployeeDegree values (3,'C3');

/*selecting records*/
select * from EmployeeDegree;

Screen in SSMS :

嫛empDegreeDB.sql _ NAGESH-PC.empDegreeDB (NAGESH-PCNagesh (55)"-Microsoft SQL File Edit View Query Project Debug Tools Window Help Execute Debug empDegreeDB.sgl-...Н-PCNagesh (55))* X /*3.Table Name EmployeeDegree* create table EmployeeDegree( EmployeeID int foreign key references Employee (EmployeeID), DegreeCode varchar (3) foreign key references Degree(DegreeCode) /*inserting records into EmployeeDegree table*/ insert into EmployeeDegree values (1, 'c1'); insert into EmployeeDegree values (1, 'C2"); insert into EmployeeDegree values (2, 'C3"); insert into EmployeeDegree values (3, "C3"); /*selecting records select from EmployeeDegree 100 % ▼ < ResultsMessages C1 C2 C3 C3

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