What happens in the background within DBMS when an SQL Statement is submitted to it.

Advanced Java: 08

Write a java program to display the Account no & name of the accounts whose balance is more than Rs. 70000

Note: When you execute a program, program should not be silent, what it says with this balance no record is there. (Application should respond good or bad application must not be silent)

So, changes are

Note: Now application starts responding but we miss first record due to this logic (Application should respond good or bad application must not be silent) Just check the following code.

Now how to access first record then. very easy instead of using while loop or condition you have to use do while loop.

Just check out the following code.

Q.1 What happens in the background within DBMS when an SQL Statement is submitted to it?

 

Queue operation and its types (Circular, Multi, Linked, Deque, Priority)

Reference: Computer architecture - Wikipedia

FPP Python Assignment no 1 to 5 Theory and Flowchart.

 
Assignment No:01

Problem Statement: 

a) Display your name, count length and display what will be your age in year. 

b) Write python program to calculate arithmetic operation using type conversion.

Aim:  Basic operations on numbers and strings using python.

Learning Objectives:

• To understand the basics about python programming.
• To learn how to write and execute python program.

Learning Outcomes:

• Upon completing this lab exercise, the learner will be able to
• Write a basic python program.
• Interpreted and run python program.
• Understand the identifiers and operators in python. 

Theory:

1. What is Python?
2. Why is python called as dynamic typing language?
3. How to store different types of data inside python identifier?
4. What is the difference between interpreter and compiler?
5. What is classpath? why do need it?
6. Explain the steps to execute python program through command prompt?
7. What is IDLE?
8. What is mean by comment in python and how many types of comments? 
9. What is mean by type conversion in python?
10. How do we accept various types of input from the user in python programming?

Algorithm:

a) Algorithm for display your name, count length and display what will be your age in year.

Step 1: Start

Step 2: Accept the name from the user.

Step 3: Find out the length of that name.

Step 4: Accept your birth year from the user.

Step 5: Find the age = current_year - birth_year.

Step 6: Display the name along with its length and age in year.

Step 7: Stop.

b) Algorithm for write python program to calculate arithmetic operation using type conversion.

Step 1: Start

Step 2: Accept the two numbers num1 and num2 from user.

Step 3: Find out the addition of these two numbers (sum = num1+num2)

Step 4: Display the addition of two numbers

Step 5: Find out the subtraction of these two numbers (sub = num1-num2)

Step 6: Display the subtraction of two numbers

Step 7: Find out the multiplication of these two numbers (mul = num1*num2)

Step 8: Display the multiplication of two numbers

Step 9: Find out the division of these two numbers (div = num1/num2)

Step 10: Display the division of two numbers

Step 11: Stop.

Flowchart:

a) Flowchart for display your name, count length and display what will be your age in year.

b) Flowchart for python program to calculate arithmetic operation using type conversion.

Conclusion: Hence, we learn how to execute the python program through command line argument and also learn how to accept input from the user and how to process this input with basic arithmetic operations.

-----------------------------------------------------------------------------------------------------------------------------

Assignment No:02

Problem Statement: Write simple python program that demonstrates the use of lists, tuples, and dictionaries

Aim: To learn basic data structures of python.

Learning Objectives:

• To understand the basics about python data structures.
• To learn how to create and manipulate data inside lists, tuples and dictionaries.

Learning Outcomes:

• Upon completing this lab exercise, the learner will be able to
• Write a basic python data structures syntax and semantics.
• How to create empty list and how to perform various operations on lists for data manipulations.
• How to create empty tuple and how to perform various operations on tuple for data manipulations.
• How to create empty dictionary and how to perform various operations on dictionary for data manipulations.

Theory:

1. What are the mutable and immutable objects in python?
2. How to create and access elements from lists?
3. How to modify and slice the lists elements?
4. What is mean by nested list explain with example?
5. What type of data we can store in list?
6. How to create and access elements from Sets?
7. How to perform various operations on the Sets?
8. What is the difference between set and frozen set?
9. Why do we require dictionaries in python?
10. Explain the copying collections: shallow copy and deep copy with example?

Algorithm:

Algorithm:

a) Algorithm for list creation and various operation.

Step 1: Start

Step 2: Create an empty list like my_empty_list.

Step 3: Create list of string, integer, float number.

Step 4: Create list of different data types and duplicate numbers.

Step 5: Find the age = current_year - birth_year.

Step 6: Display the name along with its length and age in year.

Step 7: Stop.

Flowchart:

1) Flowchart for python program that demonstrates the use of lists, tuples, and dictionaries

Conclusion: Hence, we learn how to implement the python basic data structures like list, tuple, dictionary and also learn various operation of those data structures.

-----------------------------------------------------------------------------------------------------------------------------

Assignment No:03

Problem Statement: Write a function to calculate factorial of a number. Develop a program to compute binomial coefficient (Given N and R).

Aim: To implement function using python programming.

Learning Objectives:

• To understand the basics about python functions.
• To learn how to create and manipulate data inside functions.
• To learn types of functions.

Learning Outcomes:

• Upon completing this lab exercise, the learner will be able to
• Write a basic python function syntax and semantics.
• To implement functions for various task.
• Clear the difference between user defined function and built in function.

Theory:

1. Why is indenting required in python?
2. Explain with example all decision-making statement in python?
3. Explain with example nested if else statement?
4. Why do we require iteration statement in python?
5. Explain with example while loop in python?
6. Explain with example for loop with break and continue statement in python?
7. What is function? also explain why do we require functions in python programming language?
8. What is map () function explain with example?
9. What is filter () function explain with example?
10. What is lambda () function explain with example?

Algorithm:

a) Algorithm for factorial of any number and compute binomial coefficient.

Step 1: Start

Step 2: Accept N and R from the user.

Step 3: using function of factorial compute the factorial of N.

Step 4: Display the factorial of value N.

Step 5: Compute the binomial coefficient of N and R (N!) /((N!) *(N-R)!)

Step 6: Display the binomial coefficient of N and R.

Step 7: Stop.

Flowchart:

1) Flowchart for python program that demonstrates the factorial of any number.

Conclusion: Hence, we learn how to implement the function like user defined function for computation of factorial of any number n.

-----------------------------------------------------------------------------------------------------------------------------

Assignment No:04

Problem Statement: Develop a Python program to check whether a given number is palindrome or not and also count the number of occurrences of each digit in the input number.

Aim: To understand the uses of looping and decision-making statement.

Learning Objectives:

• To understand the basics about python decision making statement.
• To understand the basics about python looping statements.

Learning Outcomes:

• Upon completing this lab exercise, the learner will be able to
• Write a basic python looping statement syntax and semantics.
• How and when to use decision making statements.

Theory:

1. What is object-oriented programming?
2. What is class and objects explain with example?
3. What encapsulation and abstraction explain with example?
4. What is mean by Inheritance explain with example?
5. What does constructor explain all types of constructors?

Algorithm:

a) Algorithm to check whether a given number is palindrome or not and also count the number of occurrences of each digit in the input number.

Step 1: Start

Step 2: Accept number n from user.

Step 3: using user defined function of reverse, reverse the number n and store it into rev identifier.

Step 4: Check number n and rev are equal or not. if equal show palindrome else shoe not palindrome message to the user.

Step 5: Compute the occurance of each digit inside the number n using user defined function.

Step 6: Display the count of occurance of each digit.

Step 7: Stop

Flowchart: Draw this flowchart by your own.

Conclusion: Hence, we learn how to implement the function, looping and decision-making statements to develop a logic to check whether given number is palindrome or not.

-----------------------------------------------------------------------------------------------------------

Assignment No:05

Problem Statement: Develop a program to read the student details like name, USN and marks in 3 subjects. Display the student details, total marks and percentage with suitable messages

Aim: To implement class by using student entity.

Learning Objectives:

• To understand difference between procedure-oriented language and object-oriented language.
• To understand the uses of oops features like encapsulation and data hiding.
• To understand the syntax of writing class.

Learning Outcomes:

• Upon completing this lab exercise, the learner will be able to
• Able to map any entity into the class.
• Able to analyze the problem statement.
• Able to identify properties and behavior of entity.

Theory:

 

1. Explain different types of Inheritance?
2. Explain the term: Operator overloading, Method Overloading, Method Overriding
3. What is exception handling? 
4. What is file? how to perform reading and writing operation on file?
5. Explain seek (), tell (), readline(), readlines() methods.

Algorithm:

a) Algorithm for display the student details, total marks and percentage with suitable messages

Step 1: Start

Step 2: Accept student name, USN and marks of three subjects (FPP, AC, SPM)

Step 3: using function find out the total of three subjects.

Step 4: Using function find out the percentage of students.

Step 5: Display all the details of student.

Step 6: Stop

Flowchart: Draw this flowchart by your own.

Conclusion: Hence, we learn how to implement the class in python along with its object creation and calling of functions as per the priority user requirement. 

Use case of interactive balance enquiry using java JDBC.

 

Advanced Java: 07

Q. Write a Java program to implement the use case of interactive balance enquiry.

Use Case 1: Enter account no: 1001                                        Use Case 2: Enter account no: 10001

                     Balance is Rs. 66022.0                                                             Account does not exist.

---------------------------------------------------------------------------------------------------------------------------

Memory Representation of above program:

Explanation of above program:

JDBC driver first duty is establishing the connection. Driver is connection creator.

> javac BalanceEnquiryApplication.java  // it compiles the code

> java BalanceEnquiryApplication

Java command goes to the operating system then OS calls the JVM. now JVM loads the .class file into RAM, after that JVM gives call to main method. accordingly, STACK AREA gets created for the main method and which remains present in main memory still main method is in execution.

Once main method completely executes then control goes to JVM. Now JVM will release memory of all the stack area.

Heap Area all the three objects 1. Connection object. 2. Statement Object and 3. Resultset object are there, and they become unreachable objects.

JVM internally keeps eyes on the Heap Area, and he observes there are three unreachable objects, so it automatically releases the memory of the objects, that is deallocation of memory. This is called Garbage Collection in JVM. Even if you don't close then also JVM will take care of this.

Q. How does database server know where the java program in the world is?

Ans: Client IP address and Port Number available in Socket inside database server as a client socket.

Now, imagine 100 of client connected to database server, then its duty of database server to maintain the client socket of each client. It's a burden on database server.

Now, assume if some java program without closing the database connection exit from the connection, then foolish (poor) database server still maintaining that socket of client.

if a greater number of such connections are there which ultimately blocks the real people who wants to connect the database server due to connection limit. So, until and unless you are not closing connection.close() in your java program database server maintains its client socket.

Q. Why to close the connection and statement object?

Ans: To release networking resources maintained by database server. when statement object created at database server some memory is allocated for SQL Statement from client side. So, once we done release that memory also that's why statement.close() instruction must be there in client-side java program.

Q. Why to close Resultset.close()?

Ans: The answer is as follows:

• When the java program submit statement to database server, it gets data from the Hard disk. after that it keeps that data in cache memory (Faste memory) and then send it to JDBC driver.
• Now, question comes to your mind is why do we need cache memory? So, as we know always hard disk operation are slower and time consuming (database is secondary memory slower 1000 times than cache memory) so; to improve the performance we keep data in cache memory. Cache memory is faster and costlier.
• To close this cache memory, we have to close resultSet object. So, this is called graceful exit.
• Note: if same query comes it fetch from cache memory for faster retrieval, if not it returns from the database. 

How to retrieve data from the database into a JDBC Client?

Advanced Java: 06

 Q. How to retrieve data from the database into a JDBC client?

By Submitting "SELECT SQL" Statement we can retrieve data from the database. executeQuery() method of statement object is used to submit select statement to the DBMS.

• executeQuery() method takes SELECT SQL statement as a string argument.
• It returns Resultset object.

Q. Explain about executeQuery() Method?

• executeQuery() method is JDBC API Method.
• used to send SELECT Statement.
• It belongs to statement object.
• It's return type is ResultSet.
• It throws SQLException if anything goes wrong.

Q. Explain about ResultSet()?

• Object oriented representation of database records received from the DBMS (Database server) is nothing but Resultset Object.
• ResultSet Object is logically divided into 3 Partitions.

1. Zero Record Area.
2. Record Area.
3. No Record Area.

• How resultset object internally look like?

• When ResultSet is open, a logical pointer known as Cursor points to zero record area of the resultset.
• When the cursor is pointing to ZERO Record Area or NO RECORD AREA, we should not try to read the data from the resultset. otherwise SQLException is raised.
• When the cursor is pointing to Record holding area then only you have to read the data. so how to move the cursor to record holding area as a programmer?
• java.sql.resultset has a method. "Next ()" to move the cursor.
• This method does two things: 1. Moves the cursor in forward direction by one record area. 2. After moving cursor, it returns true if record is present. It returns false if record is not there.
• ResultSet Object has a getter method to read data from the resultset record. these method takes column number of the resultset record as arguments and return the value.

After Execution of above code output will look like as follows.

Note:

getter methods are overloaded method you can give column names also or column number.

Develop a python program using class concept to display student details.

First of all we have to write an algorithm for the same:

Step1: Start

Step2: Accept student details from the user.

Step3: Find out the total of all three subjects.

Step4: Find out the percentage of marks.

Step5: Show the student information.

Step6: Stop

As we know we are implementing this assignment by using the concept of class are as follows:

Created class student with parameterized constructor.

Five data members with public access specifier. One show_student( ) method to display student information, accept_info( ) method to accept all details of students from the user at runtime, find_total() method to find total of three subjects, find_percentage() method to compute percentage of marks.

Line no 43 we have created one object s1 of student with details. so when object is going to be created the memory is going to be allocated for particular object and then corresponding methods are going to be called for that object.

After Executing this code you will see the output like this:

Program to implement an interactive deposit service using JDBC programming model.

 Advanced Java: 05

Q.1 Explain about getConnection() method in Driver-Manager.?

• getConnection() is a JDBC API method (Library Method)
• It belongs to java.sql.DriverManager class.
• It is a static method, so it is being called by class name. like DriverManager.getConnection()
• This method takes 3 Arguments.1. Database URL: Address of the database server. 2. Username. 3. Password.
• This method throws java.sql.SQLException if anything goes wrong while establishing connection with the database server.
• This method is factory method: Inside a method also an object is going to be created and send reference of it to the connection interface.
• This method provides database connection to a JDBC client.

Q.2 What is Port number? Please explain with example.

• Consider a scenario where you want to book a railway ticket manually, so what is the first thing he must know is the railway station address he must know. Similarly, Server IP address reach to server machine. accordingly, getConnection() method connect us to the server.
• As we know on railway station there are several counters for each one like ladies there is sperate counter, senior citizen there is a separate counter, in all general there is a separate counter. accordingly, the counter can work and proceed the task. 
• So counter number is nothing but port number. port number is unique number on server. MySQL 3306 is port number.

Q.3 What is the meaning of D:\> java DepositApplication

• java command given to operating system (OS) then OS calls the JVM, It's JVM duty to load DepositeApplication.class file into memory.
• Now, how can JVM know where this file is located? It knows through classpath. SET Classpath=""
• Note: Without installing MySQL driver, you cannot connect with database server. (mysql-connector-java 8.0.19)
• d:/> SET CLASSPATH=.; D:\mysql-connector-java 8.0.19
• Classpath is an environment variable used by JVM to locate class file.

Q.4 Explain about executeUpdate() method?

• executeUpdate() is a JDBC API method.
• It belongs to java.sql.Statement interface object.
• In real time projects 90% of java objects are interface objects only.
• This method is used to submit INSERT, UPDATE, DELETE SQL statements from the JDBC client to the DBMS.
• This method returns an integer this number represents the number of records affected in the database a result of that submitted SQL Statement.

Q.5 Write a JDBC Program to implement an interactive deposit service.

• Our program should ask the user to enter the account number and amount to be deposited.
• The database status before program execution is as follows.