MCSL-054 ASSIGNMENT SOLUTION (2019-20)

If you have any queries please leave a message here
Your Message
×


PART-1 : MCS-051

Q1. Develop a web page using servlet and JDBC to display the details of books on topic DBMS available in library. Make necessary assumptions.

Answer : -

Tables are used in this program -
books (book_id, title, edition, price)
author (book_id, author_name)

DisplayDBMSBook.java

import java.io.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
import oracle.jdbc.driver.*;
public class DisplayDBMSBook extends HttpServlet
{
public void doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException
{
response.setContentType("text/html");
PrintWriter out = response.getWriter();
String url="jdbc:oracle:thin:@localhost:1521:xe";
String user="system";
String password="9748516231";
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection(url, user, password);
Statement stmt1 = conn.createStatement();
ResultSet rs1 = stmt1.executeQuery("SELECT * FROM books WHERE category = 'DBMS'");
out.println("<center><p><table border='3' cellpadding='5'>");
out.println("<tr>");
out.println("<th>Book ID</th>");
out.println("<th>Book Title</th>");
out.println("<th>Author</th>");
out.println("<th>Edition</th>");
out.println("<th>Price</th>");
out.println("</tr>");
while(rs1.next())
{
out.println("<tr>");
out.println("<td>" + rs1.getString("book_id") + "</td>");
out.println("<td>" + rs1.getString("title") + "</td>");
int id = Integer.parseInt(rs1.getString("book_id"));
String SQL = "SELECT * FROM author WHERE book_id = " + id;
Statement stmt2 = conn.createStatement();
ResultSet rs2 = stmt2.executeQuery(SQL);
out.println("<td>");
while(rs2.next())
{
out.println(rs2.getString("author_name"));
out.println("<br />");
}
out.println("</td>");
out.println("<td>" + rs1.getString("edition") + "</td>");
out.println("<td>" + rs1.getString("price") + "</td>");
out.println("</tr>");
}
conn.close();
out.println("</table></p></center>");
}
catch(Exception e) { e.printStackTrace(); }
}
}




Q2. Write a program using JDBC and JSP to display the name, address and account number of all the saving account holders of a branch of a Bank, having balance of Rs. 5000/- or more in their account. Make necessary assumptions.

Answer : -

Tables are used in this program -
customer(cust_id, cust_name, cust_address)
account(account_no, balance, branch_code)
account_holder(account_no, cust_id)

BankCustomerDetails.jsp

<html>
<head><title>Display Customer Details</title></head>
<body>
<center><table border="3" cellpadding="10">
<tr><th>Customer Name</th><th>Customer Address</th><th>Account Number</th></tr>
<%@page import="java.sql.*"%>
<%@page import="oracle.jdbc.driver.*"%>
<%
String url = "jdbc:oracle:thin:@localhost:1521:xe";
String user = "system";
String password = "9748516231";
String SQL = "SELECT cust_name, cust_address, account.account_no FROM customer, account, account_holder WHERE customer.cust_id = account_holder.cust_id AND account_holder.account_no = account.account_no AND balance >= 5000 AND branch_code = 'SBI0000456'";
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection(url, user, password);
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(SQL);
while(rs.next())
{
%>
<tr><td><%= rs.getString("cust_name") %></td><td><%= rs.getString("cust_address") %></td><td><%= rs.getString("account_no") %></td></tr>
<%
}
con.close();
}
catch(Exception e) { e.printStackTrace(); }
%>
</table></center>
</body>
</html>




Q3. Write a JSP program using JDBC to keep records of personal information of its employees. The program should provide facility of view details of employees, modify their details and add details of new employees. Design appropriate User Interface and implement proper validation mechanism for proper and correct data entry. Make necessary assumptions.

Answer : - For this JSP program i use the following table :
employee(emp_id, emp_name, emp_address, mobile, email)

CREATE TABLE employee (emp_id INT, emp_name VARCHAR2(50) NOT NULL, emp_address VARCHAR2(100) NOT NULL, mobile INT NOT NULL, email VARCHAR2(100) NOT NULL, PRIMARY KEY(emp_id));


HomePage.html

<html>
<head>
<title>Home Page</title>
<style>
input[type="button"] {
font-family:Cambria; font-weight: bold; font-size:14pt; width: 200px; height: 45px;
}
</style></head>
<body><center>
<p><a href="InsertPage.jsp"><input type="button" value="Insert Record"></a></p>
<p><a href="UpdatePage.jsp"><input type="button" value="Update Record"></a></p>
<p><a href="DisplayPage.jsp"><input type="button" value="Display Record"></a></p>
</center></body>
</html>


InsertPage.jsp

<html>
<head>
<title>Insert New Employee</title>
<style>
input[type="text"], input[type="submit"], input[type="button"], input[type="email"], input[type="date"] {
font-family:Cambria; font-weight: bold; font-size:14pt; height: 45px; padding: 0px 10px;
}
.textcss { font-family:Cambria; font-size:14pt; color: #FF0000; }
</style>
<script type="text/javascript">
function AlphabetAndSpace(event) {
var key = event.which || event.keyCode;
return ((key >= 65 && key <= 90) || (key >= 97 && key <= 122) || key == 32 || key == 8);
}
function Number(event)
{
var key = event.which || event.keyCode;
return ((key >= 48 && key <= 57) || key == 8);
}
function PageField()
{
document.getElementById("blankLabel").style.display = "none";
}
function BlankField()
{
var id, name, address, mobile, email;
var notblank=true;
id = document.getElementById("emp_id").value;
name = document.getElementById("name").value;
address = document.getElementById("address").value;
mobile = document.getElementById("mobile").value;
email = document.getElementById("email").value;
if(id.length > 0 && name.length > 0 && address.length > 0 && mobile.length > 0 && email.length > 0)
{
document.getElementById("blankLabel").style.display = "none";
}
if(id.length == 0 || name.length == 0 || address.length == 0 || mobile.length == 0 || email.length == 0)
{
document.getElementById("blankLabel").innerHTML = "Blank fields are not allow";
document.getElementById("blankLabel").style.display = "block";
notblank=false;
}
return notblank;
}
</script></head>
<body onload="PageField()"><center>
<br /><p><a href="HomePage.html"><input type="button" value="Home Page"></a></p>
<form name="InsertDetails" method="post" onsubmit="return BlankField()" action="InsertRecord.jsp">
<p><input type="text" id="emp_id" name="emp_id" size="20" maxlength="10" placeholder="Employee ID" onkeypress="return Number(event);"></p>
<p><input type="text" id="name" name="name" size="20" maxlength="50" placeholder="Name" onkeypress="return AlphabetAndSpace(event);"></p>
<p><input type="text" id="address" name="address" size="20" maxlength="100" placeholder="Address"></p>
<p><input type="text" id="mobile" name="mobile" size="20" maxlength="10" placeholder="Mobile Number" onkeypress="return Number(event);"></p>
<p><input type="email" id="email" name="email" size="20" maxlength="100" placeholder="E-mail" pattern="[a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,3}$"></p>
<p class="textcss"><label id="blankLabel"></label></p>
<p><input type="submit" value="Insert"></p>
</form>
</center></body>
</html>


InsertRecord.jsp

<html>
<head>
<title>Insert New Employee</title>
<style>
input[type="button"] {
font-family:Cambria; font-weight: bold; font-size:14pt; height: 45px; padding: 0px 10px;
}
</style></head>
<body>
<%@page import="java.sql.*"%>
<%@page import="oracle.jdbc.driver.*"%>
<%
int id = Integer.parseInt(request.getParameter("emp_id"));
String name = request.getParameter("name");
String address = request.getParameter("address");
long mobile = Long.parseLong(request.getParameter("mobile"));
String email = request.getParameter("email");
String url = "jdbc:oracle:thin:@localhost:1521:xe";
String user = "system";
String password = "9748516231";
String SQL = "INSERT INTO employee VALUES(" + id + ",'" + name + "','" + address + "'," + mobile + ",'" + email + "')";
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection(url, user, password);
Statement stmt = con.createStatement();
stmt.executeUpdate(SQL);
con.close();
}
catch(Exception e) { e.printStackTrace(); }
%>
<center><br /><h1>INSERT SUCCESSFULLY COMPLETED</h1>
<br /><p><a href="HomePage.html"><input type="button" value="Home Page"></a></p></center>
</body></html>


UpdatePage.jsp

<html>
<head>
<title>Update Employee Details</title>
<style>
input[type="text"], input[type="submit"], input[type="button"], input[type="email"], input[type="date"] {
font-family:Cambria; font-weight: bold; font-size:14pt; height: 45px; padding: 0px 10px;
}
.textcss { font-family:Cambria; font-size:14pt; color: #FF0000; }
</style>
<script type="text/javascript">
function AlphabetAndSpace(event) {
var key = event.which || event.keyCode;
return ((key >= 65 && key <= 90) || (key >= 97 && key <= 122) || key == 32 || key == 8);
}
function Number(event)
{
var key = event.which || event.keyCode;
return ((key >= 48 && key <= 57) || key == 8);
} function PageField()
{
document.getElementById("blankLabel").style.display = "none";
}
function ID_Field()
{
var id;
var notblank=true;
id = document.getElementById("e_id").value;
if(id.length > 0)
{
document.getElementById("blankLabel").style.display = "none";
}
if(id.length == 0)
{
document.getElementById("blankLabel").innerHTML = "ID field is blank";
document.getElementById("blankLabel").style.display = "block";
notblank=false;
}
return notblank;
}
function BlankField()
{
var name, address, mobile, email;
var notblank=true;
name = document.getElementById("name").value;
address = document.getElementById("address").value;
mobile = document.getElementById("mobile").value;
email = document.getElementById("email").value;
if(name.length > 0 && address.length > 0 && mobile.length > 0 && email.length > 0)
{
document.getElementById("blankLabel").style.display = "none";
}
if(name.length == 0 || address.length == 0 || mobile.length == 0 || email.length == 0)
{
document.getElementById("blankLabel").innerHTML = "Blank fields are not allow";
document.getElementById("blankLabel").style.display = "block";
notblank=false;
}
return notblank;
}
</script></head>
<body onload="PageField()"><center>
<br /><p><a href="HomePage.html"><input type="button" value="Home Page"></a></p>
<% if(request.getParameter("e_id") != null) { %>
<%@page import="java.sql.*"%>
<%@page import="oracle.jdbc.driver.*"%>
<%
int id = Integer.parseInt(request.getParameter("e_id"));
String url = "jdbc:oracle:thin:@localhost:1521:xe";
String user = "system";
String password = "9748516231";
String SQL = "SELECT * FROM employee WHERE emp_id=" + id;
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection(url, user, password);
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(SQL);
while(rs.next())
{
%>
<form name="UpdateDetails" method="post" onsubmit="return BlankField()" action="UpdateRecord.jsp">
<p><input type="text" id="emp_id" name="emp_id" size="20" maxlength="10" value="<%= rs.getString("emp_id") %>" readonly></p>
<p><input type="text" id="name" name="name" size="20" maxlength="50" placeholder="Name" value="<%= rs.getString("emp_name") %>" onkeypress="return AlphabetAndSpace(event);"></p>
<p><input type="text" id="address" name="address" size="20" maxlength="100" placeholder="Address" value="<%= rs.getString("emp_address") %>"></p>
<p><input type="text" id="mobile" name="mobile" size="20" maxlength="10" placeholder="Mobile Number" value="<%= rs.getString("mobile") %>" onkeypress="return Number(event);"></p>
<p><input type="email" id="email" name="email" size="20" maxlength="100" placeholder="E-mail" value="<%= rs.getString("email") %>" pattern="[a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,3}$"></p>
<p class="textcss"><label id="blankLabel"></label></p>
<p><input type="submit" value="Update"></p>
</form>
<%
}
con.close();
}
catch(Exception e) { e.printStackTrace(); }
} else {
%>
<form name="RetriveData" method="post" onsubmit="return ID_Field()" action="UpdatePage.jsp">
<p><input type="text" id="e_id" name="e_id" size="20" maxlength="10" onkeypress="return Number(event);"></p>
<p class="textcss"><label id="blankLabel"></label></p>
<p><input type="submit" value="Retrive"></p>
</form>
<% } %>
</center></body></html>


UpdateRecord.jsp

<html>
<head>
<title>Update Employee Details</title>
<style>
input[type="button"] {
font-family:Cambria; font-weight: bold; font-size:14pt; height: 45px; padding: 0px 10px;
}
</style></head>
<body>
<%@page import="java.sql.*"%>
<%@page import="oracle.jdbc.driver.*"%>
<%
int id = Integer.parseInt(request.getParameter("emp_id"));
String name = request.getParameter("name");
String address = request.getParameter("address");
long mobile = Long.parseLong(request.getParameter("mobile"));
String email = request.getParameter("email");
String url = "jdbc:oracle:thin:@localhost:1521:xe";
String user = "system";
String password = "9748516231";
String SQL = "UPDATE employee SET emp_name='" + name + "', emp_address='" + address + "', mobile=" + mobile + ",email='" + email + "' WHERE emp_id=" + id;
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection(url, user, password);
Statement stmt = con.createStatement();
stmt.executeUpdate(SQL);
con.close();
}
catch(Exception e) { e.printStackTrace(); }
%>
<center><br /><h1>UPDATE SUCCESSFULLY COMPLETED</h1>
<br /><p><a href="HomePage.html"><input type="button" value="Home Page"></a></p></center>
</body></html>


DisplayPage.jsp

<html>
<head>
<title>Display Employee Details</title>
<style>
input[type="text"], input[type="submit"], input[type="button"] {
font-family:Cambria; font-weight: bold; font-size:14pt; height: 45px; padding: 0px 10px;
}
.textcss { font-family:Cambria; font-size:14pt; }
</style>
<script type="text/javascript">
function Number(event)
{
var key = event.which || event.keyCode;
return ((key >= 48 && key <= 57) || key == 8);
} function PageField()
{
document.getElementById("blankLabel").style.display = "none";
}
function ID_Field()
{
var id;
var notblank=true;
id = document.getElementById("e_id").value;
if(id.length > 0)
{
document.getElementById("blankLabel").style.display = "none";
}
if(id.length == 0)
{
document.getElementById("blankLabel").innerHTML = "ID field is blank";
document.getElementById("blankLabel").style.display = "block";
notblank=false;
}
return notblank;
}
</script></head>
<body onload="PageField()"><center>
<br /><p><a href="HomePage.html"><input type="button" value="Home Page"></a></p>
<% if(request.getParameter("e_id") != null) { %>
<%@page import="java.sql.*"%>
<%@page import="oracle.jdbc.driver.*"%>
<%
int id = Integer.parseInt(request.getParameter("e_id"));
String url = "jdbc:oracle:thin:@localhost:1521:xe";
String user = "system";
String password = "9748516231";
String SQL = "SELECT * FROM employee WHERE emp_id=" + id;
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection(url, user, password);
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(SQL);
while(rs.next())
{
%>
<table cellpadding="10" class="textcss">
<tr><td>Employee ID</td><td>:</td><td><%= rs.getString("emp_id") %></td></tr>
<tr><td>Name</td><td>:</td><td><%= rs.getString("emp_name") %></td></tr>
<tr><td>Address</td><td>:</td><td><%= rs.getString("emp_address") %></td></tr>
<tr><td>Mobile</td><td>:</td><td><%= rs.getString("mobile") %></td></tr>
<tr><td>E-mail</td><td>:</td><td><%= rs.getString("email") %></td></tr>
</table>
<%
}
con.close();
}
catch(Exception e) { e.printStackTrace(); }
} else {
%>
<form name="RetriveData" method="post" onsubmit="return ID_Field()" action="DisplayPage.jsp">
<p><input type="text" id="e_id" name="e_id" size="20" maxlength="10" onkeypress="return Number(event);"></p>
<p class="textcss" style="color: #FF0000;"><label id="blankLabel"></label></p>
<p><input type="submit" value="Retrive"></p>
</form>
<% } %>
</center></body></html>




Q4. Create an XML document for keeping MCA students information at a study centre of IGNOU.

Answer : -

MCA_Student.xml

<?xml version="1.0" ?>
<!DOCTYPE Student
[
<!ELEMENT Student (EnrolmentNo, Name, Address+, Phone+, DateOfBirth, Semester, Assignment*)>
<!ELEMENT EnrolmentNo (#PCDATA)>
<!ELEMENT Name (FirstName, MiddleName?, LastName)>
<!ELEMENT FirstName (#PCDATA)>
<!ELEMENT MiddleName (#PCDATA)>
<!ELEMENT LastName (#PCDATA)>
<!ELEMENT Address (#PCDATA)>
<!ATTLIST Address AddressType (Permanent|Present) "Permanent">
<!ELEMENT Phone (#PCDATA)>
<!ELEMENT DateOfBirth (#PCDATA)>
<!ELEMENT Semester (#PCDATA)>
<!ELEMENT Assignment (CourseCode, SubmitDate, Marks)>
<!ELEMENT CourseCode (#PCDATA)>
<!ELEMENT SubmitDate (#PCDATA)>
<!ELEMENT Marks (#PCDATA)>
]>

<Student>
<EnrolmentNo>105508022</EnrolmentNo>
<Name>
<FirstName>Debabrata</FirstName>
<LastName>Panchadhyay</LastName>
</Name>
<Address>12/18 Padmapukur Road Kolkata - 700092</Address>
<Address AddressType="Present">3/92 Sanghati Colony Kolkata - 700047</Address>
<Phone>9748516231</Phone>
<Phone>9073919231</Phone>
<DateOfBirth>26/08/1991</DateOfBirth>
<Semester>5</Semester>
<Assignment>
<CourseCode>MCS-051</CourseCode>
<SubmitDate>24/10/2019</SubmitDate>
<Marks>85</Marks>
</Assignment>
<Assignment>
<CourseCode>MCS-052</CourseCode>
<SubmitDate>24/10/2019</SubmitDate>
<Marks>80</Marks>
</Assignment>
<Assignment>
<CourseCode>MCSL-054</CourseCode>
<SubmitDate>25/10/2019</SubmitDate>
<Marks>92</Marks>
</Assignment>
</Student>





ABOUT US

QuestionSolves.com is an educational website that helps worldwide students in solving computer education related queries.

Also, different software like Visual Studio, SQL Server, Oracle etc. are available to download in different versions.

Moreover, QuestionSolves.com provides solutions to your questions and assignments also.


MORE TOPIC


Windows Command

UNIX Command

IGNOU Assignment Solution

IGNOU Question Paper Solution

Solutions of Different Questions


WHAT WE DO


Website Devlopment

Training

Home Learning

Provide BCA, MCA Projects

Provide Assignment & Question Paper Solution


CONTACT US


Follow Us