Cater you with knowledge7.

BT0075 RDBMS with MYSQL_2







Write the syntax used to connect to MySQL server.




To connect to the server, you will usually need to provide a MySQL user name when you invoke mysql and, most likely, a password. If the server runs on a machine other than the one where you log in, you will also need to specify a host name. Contact your administrator to





find out what connection parameters you should use to connect (that is, what host, user name, and password to use). Once you know the proper parameters, you should be able to connect like this:


host and user represent the host name where your MySQL server is running and the user name of your MySQL account. Substitute appropriate values for your setup. The ******** represents your password; enter it when mysql displays the Enter password: prompt.

If that works, you should see some introductory information followed by a mysql> prompt:








Write about float and double data types.




1. FLOAT [(M,D)] [UNSIGNED] [ZEROFILL]: A small (single-precision) floating-point number. Allowable values are -3.402823466E+38 to -1.175494351E-38, 0, and

1.175494351E-38 to 3.402823466E+38.These are the theoretical limits, based on the IEEE standard. The actual range might be slightly smaller depending on your hardware or operating system.


M is the total number of digits and D is the number of digits following the decimal point. If M and D are omitted, values are stored to the limits allowed by the hardware. A single-precision floating-point number is accurate to approximately 7 decimal places.

UNSIGNED, if specified, disallows negateve values.

These are the theoretical limits, based on the IEEE standard. The actual range might be slightly smaller depending on your hardware or operating system.

M is the total number of digits and D is the number of digits following the decimal point. If M and D are omitted, values are stored to the limits allowed by the hardware. A double-precision floating-point number is accurate to approximately 15 decimal places.

UNSIGNED, if specified, disallows negative values.


DOUBLE [(M,D)] [UNSIGNED] [ZEROFILL]: A normal – size (double-precision) floating-point number. Allowable values are -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308.




Write and explain the ALTER DATABASE Syntax.



You can later an existing table with the ALTER TABLE command, which allows you to add, remove, or modify table fields or indices without the need to re-create the entire table






The action component here can be any of the keywords ADD, DROP, ALTER, or CHANGE, and is followed by a field definition similar to that used by the CREATE TABLE command. This definition consists of the name of the field to be modified and (depending on the operation) a field definition consisting of a new field name, type, and constraints.



Discuss the syntax of Insert statement with a suitable example.



Once a database and its tables have been created, the next step is to enter data into them. This is accomplished by means of the INSERT command, whose basic syntax is given below:











You can also use an abbreviated form of the INSERT statement, in which the field list is left unspecified. The following example, an equivalent of the previous INSERT statement is given below:




Give the syntaxes of the following SQL statements:


a. Union







UNION is used to combine the result from multiple SELECT statements into a single result set.

The column names from the first SELECT statement are used as the column names for the results returned. Selected columns listed in corresponding positions of each SELECT statement should have the same data type. (For example, the first column selected by the first statement should have the same type as the first column selected by the other statements.)

If the data types of corresponding SELECT columns do not match, the types and lengths of the columns in the UNION result take into account the values retrieved by all of the SELECT statements.

The default behavior for UNION is that duplicate rows are removed from the result. The optional DISTINCT keyword has no effect other than the default because it also specifies duplicate-row removal. With the optional ALL keyword, duplicate-row removal does not occur and the result includes all matching rows from all the SELECT statements.

You can mix UNION ALL and UNION DISTINCT in the same query.



b. Delete







The DELETE statement deletes rows from tbl_name and returns a count of the number of deleted rows. This count can be obtained by calling the ROW_COUNT() function.

The WHERE clause, if given, specifies the conditions that identify which rows to delete. With no WHERE clause, all rows are deleted.

If the ORDER BY clause is specified, the rows are deleted in the order that is specified. The LIMIT clause places a limit on the number of rows that can be deleted. As stated, a DELETE statement with no WHERE clause deletes all rows.

A faster way to do this, when you do not need to know the number of deleted rows, is to use TRUNCATE TABLE. However, within a transaction or if you have a lock on the table, TRUNCATE TABLE cannot be used whereas DELETE can.



Update






For the single-table syntax, the UPDATE statement updates columns of existing rows in tbl_name with new values.

The SET clause indicates which columns to modify and the values they should be given. Each value can be given as an expression, or the keyword DEFAULT to set a column explicitly to its default value.


The WHERE clause, if given, specifies the conditions that identify which rows to update. With no WHERE clause, all rows are updated. If the ORDER BY clause is specified, the rows are updated in the order that is specified.

The LIMIT clause places a limit on the number of rows that can be updated.

Single-table UPDATE assignments are generally evaluated from left to right.

If you set a column to the value it currently has, MySQL notices this and does not update it. If you update a column that has been declared NOT NULL by setting to NULL, the column is set to the default value appropriate for the data type and the warning count is incremented. The default value is 0 for numeric types, the empty string ('') for string types, and the “zero” value for date and time types.



Define a correlated subquery with an example.



Most of the time, if a subquery is going to produce the same result set every time it runs, it makes sense, performance-wise, to run it only once and use the same result set to test every record generated in the main query.

Sometimes a situation arises in which a subquery uses a field from the main query in its clause. Such a reference by a subquery to a field in its enclosing query, is called an outer reference, and the corresponding subquery is called a correlated subquery, because it’s correlated with the result set of one or more of the queries enclosing it.

When an outer reference appears within a subquery, MySQL has to run the subquery once for every record generated by the outer query, and therefore test the subquery as many times as there are records in the outer query’s result set. In such a context, the EXISTS operator comes in handy, to filter out certain records from the final result set.




If you look at the data of the corresponding tables mentioned in the above example query, you will see in fact, only one branch is using four or more services (branch ID 1011).

In this case since the inner query contains a reference to a field in the outer query, MySQL cannot run the inner query only once. Rather, it has to run it over and over, once for every row in the outer table, substitute the value of the named field from that row in the subquery, and then decide whether to include that outer row in the final result set on the basis of whether the corresponding subquery returns a result.




Define the following terms:


a. Parentheses




Use parentheses to force the order of evaluation in an expression.

Example:




b. Comparison Operators


When working with MySQL SELECT queries, you may find the need to use the numerous comparison operators that allow you to compare the left side of an expression with its right side. The result of such a comparison operator is always 1(TRUE) or 0 (FALSE) or NULL (cannot be determined).



A comparison test using the BETWEEN operator returns true if the expression being tested lies between the specified range, inclusive of both end points of the range. This works not just with numbers, but with strings as well,




The <> operator can also be used for comparison with NULL values:









The comparison operators can be used to compare both numbers and strings. Numbers are compared as floating point values, while strings are compared in a case-insensitive manner.

A comparison test using the BETWEEN operator returns true if the expression being tested lies between the specified range, inclusive of both end points of the range. This works not just with numbers, but with strings as well, as shown in the example below:



Logical Operators


MySQL uses four logical operators, which makes it possible to test the logical validity of one or more expressions (or sets of expressions). The result of an operation involving these operators is always 1(true), 0 (false), or NULL (could not be determined).


The NOT Operator: It reverses the logical sense of the test following it, turning true into false and false into true.


The AND Operator: This operator makes it possible to test the validity of two or more values (or expressions evaluating to values); it returns true if all its components are true and not NULL, and it returns false otherwise.



The OR Operator: This operator returns true if any of the values or expressions involved are true and NOT NULL, and false otherwise.


The XOR (Exclusive OR) Operator: This operator is included in the MySQL 4.x or later versions. This operator returns true if if either one (but not both) of its arguments is true.


Explain few Encryption functions.



These functions allow encryption/decryption of data using the official AES (Advanced Encryption Standard) algorithm, previously known as Rijndael. Encoding with a 128-bit key length is used, but you can extend it up to 256 bits by modifying the source. We chose 128 bits because it is much faster and it is usually secure enough.



ENCRYPT(str[,salt])

Encrypt str using the Unix crypt() system call. The salt argument should be a string with two characters. (As of MySQL 3.22.16, salt may be longer than two characters.)




MD5(str)

Calculates an MD5 128-bit checksum for the string. The value is returned as a string of 32 hex digits, or NULL if the argument was NULL. The return value can, for example




SHA1(str)

SHA(str) Calculates an SHA1 160-bit checksum for the string, as described in RFC 3174 (Secure Hash Algorithm). The value is returned as a string of 40 hex digits, or NULL if the argument was NULL. One of the possible uses for this function is as a hash key. You can also use it as cryptographically safe function for storing passwords.




Discuss some of the transactional statements that do cause an implicit commit.


UNLOCK TABLES also ends a transaction if any tables currently are locked.

Prior to MySQL 4.0.13, CREATE TABLE ends a transaction if the binary update log is enabled.

Transactions cannot be nested. This is a consequence of the implicit COMMIT performed for any current transaction when you issue a START TRANSACTION statement or one of its synonyms.

ALTER TABLE, CREATE TABLE, and DROP TABLE do not commit a transaction if the TEMPORARY keyword is used. (This does not apply to other operations on temporary tables such as CREATE INDEX, which do cause a commit.).



However, although no implicit commit occurs, neither can the statement be rolled back. Therefore, use of such statements will violate transaction atomicity: For example, if you use CREATE TEMPORARY TABLE and then roll back the transaction, the table remains in existence.

The CREATE TABLE statement in InnoDB is processed as a single transaction. This means that a ROLLBACK from the user does not undo CREATE TABLE statements the user made during that transaction.

Beginning with MySQL 5.1.3, ALTER VIEW, CREATE TRIGGER, CREATE VIEW, DROP TRIGGER, and DROP VIEW cause an implicit commit.

Beginning with MySQL 5.1.15, CREATE TABLE … SELECT causes an implicit commit before and after the statement is executed when you are creating non-temporary tables. (No commit occurs for CREATE TEMPORARY TABLE … SELECT.)



This is to prevent an issue during replication where the table could be created on the master after a rollback, but fail to be recorded in the binary log, and therefore not replicated to the slave.



Statements that implicitly use or modify tables in the mysql database: Beginning with MySQL 5.1.3, CREATE USER, DROP USER, and RENAME USER cause an implicit commit. Beginning with MySQL 5.1.23, GRANT, REVOKE, and SET PASSWORD statements cause an implicit commit.

· Transaction-control and locking statements: BEGIN, LOCK TABLES, SET autocommit = 1 (if the value is not already 1), START TRANSACTION, UNLOCK TABLES.



UNLOCK TABLES commits a transaction only if any tables currently have been locked with LOCK TABLES. This does not occur for UNLOCK TABLES following FLUSH TABLES WITH READ LOCK because the latter statement does not acquire table-level locks.



· Transactions cannot be nested. This is a consequence of the implicit commit performed for any current transaction when you issue a START TRANSACTION statement or one of its synonyms.



Statements that cause an implicit commit cannot be used in an XA transaction while the transaction is in an ACTIVE state.

The BEGIN statement differs from the use of the BEGIN keyword that starts a BEGIN … END compound statement. The latter does not cause an implicit commit.

· Data loading statements: LOAD DATA INFILE. Before MySQL 5.1.12, LOAD DATA INFILE caused an implicit commit for all storage engines. As of MySQL 5.1.12, it causes an implicit commit only for tables using the NDB storage engine.

· Administrative statements: CACHE INDEX, LOAD INDEX INTO CACHE. Beginning with MySQL 5.1.10, ANALYZE TABLE, CHECK TABLE, OPTIMIZE TABLE, and REPAIR TABLE cause an implicit commit.


Explain DECLARE statement.



The DECLARE statement is used to define various items local to a routine: local variables, , conditions and handlers, and cursors.

SIGNAL and RESIGNAL statements are not currently supported.

The DECLARE statement may only be used inside a BEGIN … END




compound statement and must be at its start, before any other statements.



This command is used to declare local variables. The scope of a variable is within the BEGIN … END block.





The SET statement in stored procedures is an extended version of the general SET command. Referenced variables may be ones declared inside a routine, or global server variables. The SET statement in stored procedures is implemented as part of the pre-existing SET syntax. This allows an extended syntax of SET a=x, b=y, … where different variable types (locally declared variables, server variables, and global and session server variables) can be mixed. This also allows combinations of local variables and some options that only make sense for global/system variables; in that case the options are accepted but ignored.



Certain conditions may require specific handling. These conditions can relate to errors, as well as general flow control inside a routine.










This statement specifies conditions that will need specific handling. It associates a name with a specified error condition. The name can subsequently be used in a DECLARE HANDLER statement. In addition to SQLSTATE values, MySQL error codes are also supported.

BT0075 RDBMS with MYSQL_1







Mention 4 features of MySQL.




Security:


· A privilege and password system that is very flexible and secure, and that allows host-based verification.

· Passwords are secure because all password traffic is encrypted when you connect to a server.



Scalability and Limits:


· Handles large databases. We use MySQL Server with databases that contain 50 million records. We also know of users who use MySQL Server with 60,000 tables and about 5,000,000,000 rows.

· Up to 64 indexes per table are allowed (32 before MySQL 4.1.2). Each index may consist of 1 to 16 columns or parts of columns. The maximum index width is 1000 bytes (767 for InnoDB); before MySQL 4.1.2, the limit is 500 bytes. An index may use a prefix of a column for CHAR, VARCHAR, BLOB, or TEXT column types.



Connectivity:


· Clients can connect to MySQL Server using several protocols:

- Clients can connect using TCP/IP sockets on any platform.

- On Windows systems in the NT family (NT, 2000, XP, 2003, or Vista), clients can connect using named pipes if the server is started with the – enable-named-pipe option. In MySQL 4.1 and higher, Windows servers also support shared-memory connections if started with the – shared-memory option. Clients can connect through shared memory by using the – protocol=memory option.

- On Unix systems, clients can connect using Unix domain socket files.

· MySQL client programs can be written in many languages. A client library written in C is available for clients written in C or C++, or for any language that provides C bindings.

· APIs for C, C++, Eiffel, Java, Perl, PHP, Python, Ruby, and Tcl are available, allowing MySQL clients to be written in many languages.

· The Connector/ODBC (MyODBC) interface provides MySQL support for client programs that use ODBC (Open Database Connectivity) connections.



For example, you can use MS Access to connect to your MySQL server. Clients can be run on Windows or Unix. MyODBC source is available. All ODBC 2.5 functions are supported, as are many others.

· The Connector/J interface provides MySQL support for Java client programs that use JDBC connections. Clients can be run on Windows or Unix. Connector/J source is available.

· MySQL Connector/NET enables developers to easily create .NET applications that require secure, high-performance data connectivity with MySQL. It implements the required ADO.NET interfaces and integrates into ADO.NET aware tools. Developers can build applications using their choice of .NET languages. MySQL Connector/NET is a fully managed ADO.NET driver written in 100% pure C#.


Localization:


· The server can provide error messages to clients in many languages.

· Full support for several different character sets, including latin1 (cp1252), german, big5, ujis, and more. For example, the Scandinavian characters “å”, “ä” and “ö” are allowed in table and column names. Unicode support is available as of MySQL 4.1.

· All data is saved in the chosen character set.

· Sorting and comparisons are done according to the chosen character set and collation (using latin1 and Swedish collation by default). It is possible to change this when the MySQL server is started. To see an example of very advanced sorting, look at the Czech sorting code. MySQL Server supports many different character sets that can be specified at compile time and runtime.

· As of MySQL 4.1, the server time zone can be changed dynamically, and individual clients can specify their own time zone.



Write the differences between char and varchar data types in MySQL.


The CHAR and VARCHAR types are similar, but differ in the way they are stored and retrieved. They also differ in maximum length and in whether trailing spaces are retained.

The CHAR and VARCHAR types are declared with a length that indicates the maximum number of characters you want to store. For example, CHAR(30) can hold up to 30 characters.



The length of a CHAR column is fixed to the length that you declare when you create the table. The length can be any value from 0 to 255. When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed unless the PAD_CHAR_TO_FULL_LENGTH SQL mode is enabled.



Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 65,535. The effective maximum length of a VARCHAR is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used.



In contrast to CHAR, VARCHAR values are stored as a one-byte or two-byte length prefix plus data. The length prefix indicates the number of bytes in the value. A column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes.

If strict SQL mode is not enabled and you assign a value to a CHAR or VARCHAR column that exceeds the column’s maximum length, the value is truncated to fit and a warning is generated. For truncation of non-space characters, you can cause an error to occur (rather than a warning) and suppress insertion of the value by using strict SQL mode.

For VARCHAR columns, trailing spaces in excess of the column length are truncated prior to insertion and a warning is generated, regardless of the SQL mode in use. For CHAR columns, truncation of excess trailing spaces from inserted values is performed silently regardless of the SQL mode.

VARCHAR values are not padded when they are stored. Trailing spaces are retained when values are stored and retrieved, in conformance with standard SQL.


Write about the operations supported by indexes in MySQL.



Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more is the cost. If the table has an index for the columns in question, MySQL can quickly determine the position to seek to in the middle of the data file without having to look at all the data. If a table has 1,000 rows, this is at least 100 times faster than reading sequentially. If you need to access most of the rows, it is faster to read sequentially, because this minimizes disk seeks. If a multiple-column index exists on col1 and col2, the appropriate rows can be fetched directly. If separate single-column indexes exist on col1 and col2, the optimizer will attempt to use the Index Merge optimization, or attempt to find the most restrictive index by deciding which index finds fewer rows and using that index to fetch the rows.

Index Merge optimization: The Index Merge method is used to retrieve rows with several range scans and to merge their results into one. The merge can produce unions, intersections, or unions-of-intersections of its underlying scans. This access method merges index scans from a single table; it does not merge scans across multiple tables.MySQL uses indexes for these operations:


· To find the rows matching a WHERE clause quickly.

· To eliminate rows from consideration. If there is a choice between multiple indexes, MySQL normally uses the index that finds the smallest number of rows.


· To retrieve rows from other tables when performing joins. MySQL can use indexes on columns more efficiently if they are declared as the same type and size. In this context, VARCHAR and CHAR are considered the same if they are declared as the same size. For example, VARCHAR(10) and CHAR(10) are the same size, but VARCHAR(10) and CHAR(15) are not.


Comparison of dissimilar columns may prevent use of indexes if values cannot be compared directly without conversion. Suppose that a numeric column is compared to a string column. For a given value such as 1 in the numeric column, it might compare equal to any number of values in the string column such as '1', ' 1', '00001', or '01.e1'. This rules out use of any indexes for the string column.



· To find the MIN() or MAX() value for a specific indexed column key_col. This is optimized by a preprocessor that checks whether you are using WHERE key_part_N = constant on all key parts that occur before key_col in the index. In this case, MySQL does a single key lookup for each MIN() or MAX() expression and replaces it with a constant. If all expressions are replaced with constants, the query returns at once.


Write the SQL statements to demonstrate the following using SELECT command:



Expression Evaluation








b. Using table aliases






ORDER BY





Define the following types of Joins:



a. Inner Join


they are also known as Equi Joins. They are so called because the where statement generally compares two columns from two tables with the equivalence operator =. Many systems use this type as the default join. This type can be used in situations where selecting only those rows that have values in common in the columns specified in the ON clause, is required. In short, the Inner Join returns all rows from both tables where there is a match.






mysql> SELECT ProdName, Quantity FROM Products, Sales WHERE Products.ProdID = Sales.ProdID AND Sales.Quantity > 2000;


b. Left Outer Join


In this type, all the records from the table on the left side of the join and matching the WHERE clause in appear in the final result set.

mysql> SELECT * FROM users LEFT JOIN users_groups ON users.uid = users_groups.uid;

Right Outer Join


All the records matching the WHERE clause from the table on the right appear.

mysql> SELECT * FROM users_groups RIGHT JOIN groups USING (gid);


Give the advantages of subqueries.



A Subquery is exactly what it sounds like: a SELECT query that is subordinate to another query. This unit covers the various concepts of subqueries.


1. The subquery as scalar operand: A scalar subquery is a simple operand, and you can use it almost anywhere a single column value or literal is legal, and you can expect it to have those characteristics that all operands have: a data type, a length, an indication whether it can be NULL, and so on.


2. Comparisons Using Subqueries: The output of the subqueries can be used to compare the output of the outer most queries. This can be done using various comparison operators like >, <, IN, and so on.


3. Subqueries with ANY, IN, and SOME: These type of subqueries can be used in cases where a set of values need to be compared and the output determined.


4. Subqueries with ALL:
These type of subqueries can be used in cases where a set of values are the output generated by the inner or subqueries and the output is possible only if all the values match with the outer query.



5. Correlated Subqueries: Sometimes a situation arises in which a subquery uses a field from the main query in its clause. Such a reference by a subquery to a field in its enclosing query, is called an outer reference, and the corresponding subquery is called a correlated subquery, because it’s correlated with the result set of one or more of the queries enclosing it.



6. EXISTS and NOT EXISTS: The Exists operator can be used to check if a subquery produces any results at all. The NOT EXISTS operator is exactly the opposite of the output produced by NOT EXISTS.


7. Row Subqueries: A row subquery is a subquery variant that returns a single row and can thus return more than one column value.

Subqueries in the FROM Clause: Subqueries are legal in a SELECT statement’s FROM clause. Any columns in the subquery select list must have unique names.


Discuss any five string functions.



String-valued functions return NULL if the length of the result would be greater than the value of the max_allowed_packet system variable. For functions that operate on string positions, the first position is numbered 1.


1,ASCII(str)-Returns the numeric value of the leftmost character of the string str. Returns 0 if str is the empty string. Returns NULL if str is NULL. ASCII() works for characters with numeric values from 0 to 255.1. SELECT ASCII(’2′);


2,BIN(N)-Returns a string representation of the binary value of N, where N is a longlong (BIGINT) number. SELECT BIN(12);


3,BIT_LENGTH(str)-Returns the length of the string str in bits.SELECT BIT_LENGTH(’text’);


4,CHAR(N,…)--CHAR() interprets the arguments as integers and returns a string consisting of the characters given by the code values of those integers. NULL values are skipped.1.SELECT CHAR(77,121,83,81,’76′);SELECT INSERT(’Quadratic’, 3, 4, ‘What’);


5,INSERT(str,pos,len,newstr)--Returns the string str, with the substring beginning at position pos and len characters long replaced by the string newstr.

Describe the operators that support the boolean full-text searches.


MySQL can perform boolean full-text searches using the IN BOOLEAN MODE modifier:· +: A leading plus sign indicates that this word must be present in each row that is returned.

· -: A leading minus sign indicates that this word must not be present in any of the rows that are returned.



Note: The – operator acts only to exclude rows that are otherwise matched by other search terms. Thus, a boolean-mode search that contains only terms preceded by – returns an empty result. It does not return “all rows except those containing any of the excluded terms.”




· (no operator): By default (when neither + nor – is specified) the word is optional, but the rows that contain it are rated higher. This mimics the behavior of MATCH() … AGAINST() without the IN BOOLEAN MODE modifier.




· > <:
These two operators are used to change a word’s contribution to the relevance value that is assigned to a row. The > operator increases the contribution and the < operator decreases it.



· ( ): Parentheses group words into subexpressions. Parenthesized groups can be nested.

· ~: A leading tilde acts as a negation operator, causing the word's contribution to the row's relevance to be negative. This is useful for marking “noise” words. A row containing such a word is rated lower than others, but is not excluded altogether, as it would be with the - operator.

· *: The asterisk serves as the truncation (or wildcard) operator. Unlike the other operators, it should be appended to the word to be affected. Words match if they begin with the word preceding the * operator.

If a stopword or too-short word is specified with the truncation operator, it will not be stripped from a boolean query. For example, a search for '+word +stopword*' will likely return fewer rows than a search for '+word +stopword' because the former query remains as is and requires stopword* to be present in a document. The latter query is transformed to +word.

· ": A phrase that is enclosed within double quote (“"”) characters matches only rows that contain the phrase literally, as it was typed. The full-text engine splits the phrase into words, performs a search in the FULLTEXT index for the words. Non-word characters need not be matched exactly: Phrase searching requires only that matches contain exactly the same words as the phrase and in the same order.



Describe the Savepoint and Rollback to Savepoint Syntaxes.









Starting from MySQL 4.0.14 and 4.1.1, InnoDB supports the SQL statements SAVEPOINT and ROLLBACK TO SAVEPOINT.

The SAVEPOINT statement sets a named transaction savepoint with a name of identifier. If the current transaction already has a savepoint with the same name, the old savepoint is deleted and a new one is set.


The ROLLBACK TO SAVEPOINT statement rolls back a transaction to the named savepoint. Modifications that the current transaction made to rows after the savepoint was set are undone in the rollback, but InnoDB does not release the row locks that were stored in memory after the savepoint. (Note that for a new inserted row, the lock information is carried by the transaction ID stored in the row; the lock is not separately stored in memory. In this case, the row lock is released in the undo.) Savepoints that were set at a later time than the named savepoint are deleted.




Give the syntax of BEGIN...END compound statement.







Stored routines may contain multiple statements, using a BEGIN … END compound statement. begin_label and end_label must be the same, if both are specified. Please note that the optional [NOT] ATOMIC clause is not yet supported. This means that no transactional savepoint is set at the start of the instruction block and the BEGIN clause used in this context has no effect on the current transaction.


Multiple statements requires that a client is able to send query strings containing ‘;’. This is handled in the mysql command-line client with the delimiter command. Changing the ‘;’ end-of-query delimiter (for example, to ‘|’) allows ‘;’ to be used in a routine body.

BT0074 OOPS with Java2










What is bytecode? Explain.




Java is both interpreted and compiled. The code is complied to a bytecode that is binary and platform independent. When the program has to be executed, the code is fetched into the memory and interpreted on the user’s machine. As an interpreted language, Java has simple syntax.

When you compile a piece of code, all errors are listed together. You can execute only when all the errors are rectified. An interpreter, on the other hand, verifies the code and executes it line by line. Only when the execution reaches the statement with error, the error is reported. This makes it easy for a programmer to debug the code. The drawback is that this takes more time than compilation.



Compilation is the process of converting the code that you type, into a language that the computer understands – machine language. When you compile a program using a compiler, the compiler checks for syntactic errors in code and list all the errors on the screen. You have to rectify the errors and recompile the program to get the machine language code. The Java compiler compiles the code to a bytecode that is understood by the Java environment.Bytecode is the result of compiling a Java program. You can execute this code on any platform. In other words, due to the bytecode compilation process and interpretation by a browser, Java programs can be executed on a variety of hardware and operating systems. The only requirement is that the system should have a Java-enabled Internet browser.



The Java interpreter can execute Java code directly on any machine on which a Java interpreter has been installed.a Java program can run on any machine that has a Java interpreter. The bytecode supports connection to multiple databases. Java code is portable. Therefore, others can use the programs that you write in Java, even if they have different machines with different operating systems.



Bytecode is a highly optimized set of instructions designed to be executed by the Java run-time system, which is called the Java Virtual Machine (JVM). That is, in its standard form, the JVM is an interpreter for bytecode. This may come as a bit of surprise.Translating a Java program into bytecode helps it to run much easier in a wide variety of environments. The reason is straightforward: only the JVM needs to be implemented for each platform. Once the run-time package exists for a given system, any Java program can run on it. Remember, although the details of the JVM will differ from platform to platform, all interpret the same Java bytecode. If a Java program was compiled to native code, then different versions of the same program should exist for each type of CPU connected to the Internet. This is, of course, not a feasible solution. Thus, the interpretation of bytecode is the easiest way to create truly portable programs.




How do you compile a Java program?





The programs that you write in Java should be saved in a file, which has the following name format:

<class_name>.java



Compiling


A program is a set of instructions. In order to execute a program, the operating system needs to understand the language. The only language an operating system understands is in terms of 0’s and 1’s i.e. the binary language. Programs written in language such as C and C++ are converted to binary code during the compilation process. However, that binary code can be understood only by the operating system for which the program is compiled. This makes the program or application as operating system dependent.

In Java, the program is compiled into bytecode (.class file) that run on the Java Virtual Machine, which can interpret and run the program on any operating system. This makes Java programs platform-independent.


At the command prompt, type

javac <filename>.java to compile the Java program.



What do you mean by operator precedence?


When more than one operator is used in an expression, Java will use operator precedence rule to determine the order in which the operators will be evaluated. For example, consider the following expression:

Result=10+5*8-15/5


In the above expression, multiplication and division operations have higher priority over the addition and subtraction. Hence they are performed first. Now, Result = 10+40-3.


Addition and subtraction has the same priority. When the operators are having the same priority, they are evaluated from left to right in the order they appear in the expression. Hence the value of the result will become 47. In general the following priority order is followed when evaluating an expression:


· Increment and decrement operations.

· Arithmetic operations.

· Comparisons.

· Logical operations.

· Assignment operations.



To change the order in which expressions are evaluated, parentheses are placed around the expressions that are to be evaluated first. When the parentheses are nested together, the expressions in the innermost parentheses are evaluated first. Parentheses also improve the readability of the expressions. When the operator precedence is not clear, parentheses can be used to avoid any confusion.



What is an array? Explain with examples.




An array represents a number of variables which occupy contiguous spaces in the memory. Each element in the array is distinguished by its index. All elements in an array must be of the same data type. For example, you cannot have one element with int data type and another belonging to the boolean data type in the same array. An array is a collection of elements of the same type that are referenced by a common name. Each element of an array can be referred to by an array name and a subscript or index. To create and use an array in Java, you need to first declare the array and then initialize it. The syntax for creating an array is:

data- type [ ] variablename;

Example:

int [ ] numbers;



The above statement will declare a variable that can hold an array of int type variables. After declaring the variable for the array, the array needs to be allocated in memory. This can be done using the new operator in the following way:



numbers = new int [10];

This statement assigns ten contiguous memory locations of the type int to the variable numbers. The array can store ten elements. Iteration can be used to access all the elements of the array, one by one.

How will you implement inheritance in Java?



Inheritance can create a general class that defines traits common to a set of related items. This class can then be inherited by other, more specific classes, each adding those things that are unique to it. In the terminology of Java, a class that is inherited is called a superclass. The class that does the inheriting is called a subclass. Therefore, a subclass is a specialized version of a superclass. Java provides a mechanism for partitioning the class name space into more manageable chunks. This mechanism is the package. The package is both a naming and a visibility control mechanism. You can define classes inside a package that are not accessible by code outside that package. You can also define class members that are only exposed to other members of the same package. Using the keyword interface,


Inheritance is one of the cornerstones of object-oriented programming, because it allows the creation of hierarchical classifications. Using inheritance, you can create a general class that defines traits common to a set of related items. This class can then be inherited by other, more specific classes, each adding those things that are unique to it. In the terminology of Java, a class that is inherited is called a superclass. The class that does the inheriting is called a subclass. Therefore, a subclass is a specialized version of a superclass. It inherits all of the instance variables and methods defined by the superclass and add its own, unique elements.



The extends keyword is used to derive a class from a superclass, or in other words, extend the functionality of a superclass.

Syntax

public class <subclass_name> extends <superclass_name>

Example

public class Confirmed extends Ticket

{

}



Rules for Overriding Methods

· The method name and the order of arguments should be identical to that of the superclass method.

· The return type of both the methods must be the same.

· The overriding method cannot be less accessible than the method it overrides. For example, if the method to override is declared as public in the superclass, you cannot override it with the private keyword in the subclass.

· An overriding method cannot raise more exceptions than those raised by the superclass.


Explain different kinds of Exceptions in Java.


The term exception denotes an exceptional event. It can be defined as an abnormal event that occurs during program execution and disrupts the normal flow of instruction.


The class at the top of the exception classes hierarchy is Throwable class. Two classes are derived from the Throwable class – Error and Exception. The Exception class is used for the exceptional conditions that has to be trapped in a program. The Error class defines a condition that does not occur under normal circumstances. In other words, the Error class is used for catastrophic failures such as VirtualMachineError

Java has several predefined exceptions. The most common exceptions that you may encounter are described below.

· Arithmetic Exception


This exception is thrown when an exceptional arithmetic condition has occurred. For example, a division by zero generates such an exception.


· NullPointer Exception


This exception is thrown when an application attempts to use null where an object is required. An object that has not been allocated memory holds a null value. The situations in which an exception is thrown include:

- Using an object without allocating memory for it.

- Calling the methods of a null object.

- Accessing or modifying the attributes of a null object.

· ArrayIndexOutOfBounds Exception

This exception is thrown when an attempt is made to access an array element beyond the index of the array. For example, if you try to access the eleventh element of an array that has only ten elements, the exception will be thrown.


What are the uses of stream class?



Stream Classes are classified as FileInputStream, FileOutputStream, BufferedInputStream, BufferedOutputStream, DataInputStream, and DataOutputStream classes.

The FileInputStream and FileOutputStream Classes

These streams are classified as mode streams as they read and write data from disk files. The classes associated with these streams have constructors that allow you to specify the path of the file to which they are connected. The FileInputStream class allows you to read input from a file in the form of a stream. The FileOutputStream class allows you to write output to a file stream.


Example:

FileInputStream inputfile = new FileInputStream (“Employee.dat”);

FileOutputStream outputfile = new FileOutputStream (“binus.dat”);

The BufferedInputStream and BufferedOutputStream Classes

The BufferedInputStream class creates and maintains a buffer for an input stream. This class is used to increase the efficiency of input operations. This is done by reading data from the stream one byte at a time. The BufferedOutputStream class creates and maintains a buffer for the output stream. Both the classes represent filter streams.

The DataInputStream and DataOutputStream Classes

The DataInputStream and DataOutputStream classes are the filter streams that allow the reading and writing of Java primitive data types.

The DataInputStream class provides the capability to read primitive data types from an input stream. It implements the methods presents in the DataInput interface.


What is AWT? Explain.


The Abstract Windowing Toolkit, also called as AWT is a set of classes, enabling the user to create a user friendly, Graphical User Interface (GUI). It will also facilitate receiving user input from the mouse and keyboard. The AWT classes are part of the java.awt package. The user interface consists of the following three:

· Components – Anything that can be put on the user interface. This includes buttons, check boxes, pop-up menus, text fields, etc.

· Containers – This is a component that can contain other components.

· Layout Manager – These define how the components will be arranged in a container.

The statement import java.awt.*; imports all the components, containers and layout managers necessary for designing the user interface.

The AWT supplies the following components.

· Labels (java.awt.Label)

· Buttons (java.awt.Button)

· Checkboxes (java.awt.Checkbox)

· Single- line text field (java.awt.TextField)

· Larger text display and editing areas (java.awt.TextArea)

· Pop-up lists of choices (java.awt.Choice)

· Lists (java.awt.List)

· Sliders and scrollbars (java.awt.Scrollbar )

· Drawing areas (java.awt.Canvas)

· Menus (java.awt.Menu, java.awt.MenuItem, java.awt.CheckboxMenuItem )

· Containers (java.awt.Panel, java.awt.Window and its subclasses)


What are the different components of an event?


An event comprises of three components:


· Event Object – When the user interacts with the application by pressing a key or clicking a mouse button, an event is generated. The operating system traps this event and the data associated with it, for example, the time at which the event occurred, the event type (like a keypress or a mouseclick). This data is then passed on to the application to which the event belongs.


In Java, events are represented by objects that describe the events themselves. Java has a number of classes that describe and handle different categories of event.


· Event Source – An event source is an object that generates an event. For example, if you click on a button, an ActionEvent object is generated. The object of the ActionEvent class contains information about the event.

· Event-handler – An event-handler is a method that understands the event and processes it. The event-handler method takes an event object as a parameter.


Draw and explain the JDBC Application Architecture.








Connection to a Database


The java.sql package contains classes that help in connecting to a database, sending SQL statements to the database, and processing query results.


The Connection Objects


The Connection object represents a connection with a database. You may have several Connection objects in an application that connects to one or more databases.


Loading the JDBC-ODBC Bridge and Establishing Connection

To establish a connection with a database, you need to register the ODBC-JDBC Driver by calling the forName() method from the Class class and then calling the getConnection() method from the DriverManager class.

The getConnection() method of the DriverManager class attempts to locate the driver that can connect to the database represented by the JDBC URL passed to the getConnection() method.

The JDBC URL

The JDBC URL is a string that provides a way of identifying a database. A JDBC URL is divided into three parts:

<protocol>:<subprotocol>:<subname>

· <protocol> in a JDBC URL is always jdbc.

· <subprotocol> is the name of the database connectivity mechanism. If the mechanism of retrieving the data is ODBC-JDBC bridge, the subprotocol must be odbc.

· <subname> is used to identify the database.

Example: JDBC URL

String url = “jdbc:odbc:MyDataSource”;

Class.forName (“sun.jdbc.odbc.JdbcOdbcDriver“);

Connection con = DriverManager.getConnection(url);

Using the Statement Object You can use the statement object to send simple queries to the database as shown in the sample QueryApp program.

The Statement object allows you to execute simple queries. It has the following three methods that can be used for the purpose of querying:

§ The executeQuery() method executes a simple query and returns a single ResultSet object.

§ The executeUpdate() method executes an SQL INSERT, UPDATE or DELETE statement.

§ The execute() method executes an SQL statement that may return multiple results.



The ResultSet Object

The ResultSet object provides you with methods to access data from the table. Executing a statement usually generates a ResultSet object. It maintains a cursor pointing to its current row of data. Initially the cursor is positioned before the first row. The next() method moves the cursor to the next row. You can access data from the ResultSet rows by calling the getXXX() method where XXX is the data type. The following code queries the database and process the ResultSet.


Using the PreparedStatement Object

You have to develop an application that queries the database according to the search criteria specified by a user. For example, the user supplies the publisher ID and wants to see the details of that publisher.

select * from publishers where pub_id=?

To make it possible, you need to prepare a query statement at runtime with an appropriate value in the where clause.

The PreparedStatement object allows you to execute parameterized queries. The PreparedStatement object is created using the prepareStatement() method of the Connection object.

stat=con.prepareStatement (“select * from publishers where pub_id=?”);

The prepareStatement(), method of the Connection object takes an SQL statement as a parameter. The SQL statement can contain placeholders that can be replaced by INPUT parameters at runtime.

The ‘?’ symbols is a placeholder that can be replaced by the INPUT parameters at runtime.


Passing INPUT Parameters:

Before executing a PreparedStatement object, you must set the value of each ‘?’ parameter. This is done by calling an appropriate setXXX() method, where XXX is the data type of the parameter.

stat.setString(1, pid.getText());

ResultSet result=stat.executeQuery();

BT0074 OOPS with Java



1.Give the features of Java.



Java defines data as objects with methods that support the objects. Java is purely object-oriented and provides abstraction, encapsulation, inheritance and polymorphism. Even the most basic program has a class. Any code that you write in Java is inside a class.



Java is tuned for Web. Java programs can access data across the Web as easily as they access data from a local system. You can build distributed applications in Java that use resources from any other networked computer.



Java is both interpreted and compiled. The code is complied to a bytecode that is binary and platform independent. When the program has to be executed, the code is fetched into the memory and interpreted on the user’s machine. As an interpreted language, Java has simple syntax.



When you compile a piece of code, all errors are listed together. You can execute only when all the errors are rectified. An interpreter, on the other hand, verifies the code and executes it line by line. Only when the execution reaches the statement with error, the error is reported. This makes it easy for a programmer to debug the code. The drawback is that this takes more time than compilation.



Compilation is the process of converting the code that you type, into a language that the computer understands – machine language. When you compile a program using a compiler, the compiler checks for syntactic errors in code and list all the errors on the screen. You have to rectify the errors and recompile the program to get the machine language code. The Java compiler compiles the code to a bytecode that is understood by the Java environment.



Bytecode is the result of compiling a Java program. You can execute this code on any platform. In other words, due to the bytecode compilation process and interpretation by a browser, Java programs can be executed on a variety of hardware and operating systems. The only requirement is that the system should have a Java-enabled Internet browser. The Java interpreter can execute Java code directly on any machine on which a Java interpreter has been installed.


Thanks to bytecode, a Java program can run on any machine that has a Java interpreter. The bytecode supports connection to multiple databases. Java code is portable. Therefore, others can use the programs that you write in Java, even if they have different machines with different operating systems.


Java forces you to handle unexpected errors. This ensures that Java programs are robust (reliable), bug free and do not crash.

Due to strong type-checking done by Java on the user’s machine, any changes to the program are tagged as error and the program will not execute. Java is, therefore, secure.

Java is faster than other interpreter-based language like BASIC since it is compiled and interpreted.

Multithreading is the ability of an application to perform multiple tasks at the same time. You can create multithreading programs using Java. The core of Java is also multithreaded.

The following definition of Java by Sun Microsystems lists all the features of Java.

‘Java is a simple, object-oriented, distributed, interpreted, robust, secure, architecture neutral, portable, high-performance, multi-threaded and dynamic language.’


How do you execute a Java program?



The programs that you write in Java should be saved in a file, which has the following name format: program is a set of instructions. In order to execute a program, the operating system needs to understand the language. The only language an operating system understands is in terms of 0’s and 1’s i.e. the binary language.

.Before the Java virtual machine (VM) can run a Java program, the program's Java source code must be compiled into byte-code using the javac compiler. Java byte-code is a platform independent version of machine code; the target machine is the Java VM rather than the underlying architecture. To compile a Java source code file Foo.java, you would do the following:

% javac -g Foo.java

The -g command line option is optional, but we recommend using it as it makes debugging easier.

If there are no errors in your source file, the Java compiler will produce one or more .class files (one .class file for each class defined in the Foo.java source file). For example, the results of a successful compile of Foo.java will produce a byte-code version of the class in a file named Foo.class.

Every public class that you write must be in a separate .java file where the first part of the file name is identical to the class name. The .java file additionally can contain code for protected and private classes.

In Java, the program is compiled into bytecode (.class file) that run on the Java Virtual Machine, which can interpret and run the program on any operating system. This makes Java programs platform-independent.

At the command prompt, type

javac <filename>.java

to compile the Java program.

Executing

When the code is compiled and error-free, the program can be executed using the command:

java <class filename>



What are the different types of operators used in Java?



Operators play an important role in Java. There are three kinds of operators in Java. They are (i) Arithmetic Operators (ii) Comparison / Relational Operators and (iii) Logical Operators

Addition, Subtraction, Multiplication, Division and Modulus are the various arithmetic operations that can be performed in Java.Java provides eight Arithmetic operators. They are for addition, subtraction, multiplication, division, modulo (or remainder), increment (or add 1), decrement (or subtract 1), and negation. An example program is shown below that demonstrates the different arithmetic operators in java.


The binary operator + is overloaded in the sense that the operation performed is determined by the type of the operands. When one of the operands is a String object, the other operand is implicitly converted to its string representation and string concatenation is performed.

Relational operators in Java are used to compare 2 or more objects. Java provides six relational operators:
greater than (>), less than (<), greater than or equal (>=), less than or equal (<=), equal (==), and not equal (!=).
All relational operators are binary operators, and their operands are numeric expressions.




Binary numeric promotion is applied to the operands of these operators. The evaluation results in a boolean value. Relational operators have precedence lower than arithmetic operators, but higher than that of the assignment operators. An example program is shown below that demonstrates the different relational operators in java.The increment operator is ++ and decrement operator is –. This is used to add 1 to the value of a variable or subtract 1 from the value of a variable. These operators are placed either before the variable or after the variable nameComparison operators are used to compare two values and give the resultsLogical operators are used to perform Boolean operations on the operands.Logical operators return a true or false value based on the state of the Variables. There are six logical, or boolean, operators. They are AND, conditional AND, OR, conditional OR, exclusive OR, and NOT. Each argument to a logical operator must be a boolean data type, and the result is always a boolean data type. An example program is shown below that demonstrates the different Logical operators in java.Java provides Bit wise operators to manipulate the contents of variables at the bit level.

These variables must be of numeric data type ( char, short, int, or long). Java provides seven bitwise
operators. They are AND, OR, Exclusive-OR, Complement, Left-shift, Signed Right-shift, and Unsigned Right-shift. An example program is shown below that demonstrates the different Bit wise operators in java.


The Conditional operator is the only ternary (operator takes three arguments) operator in Java. The operator evaluates the first argument and, if true, evaluates the second argument. If the first argument evaluates to false, then the third argument is evaluated. The conditional operator is the expression equivalent of the if-else statement. The conditional expression can be nested and the conditional operator associates from right to left: (a?b?c?d:e:f:g) evaluates as (a?(b?(c?d:e):f):g)



What are the various character extraction functions available in Java?


The String class provides a number of ways in which characters can be extracted from a String object. Each is examined here. Although the characters that comprise a string within a String object cannot be indexed as if they were a character array, many of the String methods employ an index (or offset) into the string for their operation. Like arrays, the string indexes begin at zero.

charAt( )

To extract a single character from a String, you can refer directly to an individual character via the charAt( ) method. It has this general form:

char charAt(int where)

Here, where is the index of the character that you want to obtain. The value of where must be nonnegative and specify a location within the string. charAt( ) returns the character at the specified location. For example,

char ch;
ch = "abc".charAt(1);
assigns the v0alue "b" to ch.

getChars( )

If you need to extract more than one character at a time, you can use the getChars( ) method. It has this general form:

void getChars(int sourceStart, int sourceEnd, char target[ ], int targetStart)

Here, sourceStart specifies the index of the beginning of the substring, and sourceEnd specifies an index that is one past the end of the desired substring. Thus, the substring contains the characters from sourceStart through sourceEnd–1. The array that will receive the characters is specified by target. The index within target at which the substring will be copied is passed in targetStart. Care must be taken to assure that the target array is large enough to hold the number of characters in the specified substring. The following program demonstrates getChars( ):

class getCharsDemo {
public static void main(String args[]) {
String s = "This is a demo of the getChars method.";
int start = 10;
int end = 14;
char buf[] = new char[end - start];
s.getChars(start, end, buf, 0);
System.out.println(buf);
}
}

Here is the output of this program:

Demo


getBytes( )

There is an alternative to getChars( ) that stores the characters in an array of bytes. This method is called getBytes( ), and it uses the default character-to-byte conversions provided by the platform. Here is its simplest form:

byte[ ] getBytes( )

Other forms of getBytes( ) are also available. getBytes( ) is most useful when you are exporting a String value into an environment that does not support 16-bit Unicode characters. For example, most Internet protocols and text file formats use 8-bit ASCII for all text interchange.

toCharArray( )

If you want to convert all the characters in a String object into a character array, the easiest way is to call toCharArray( ). It returns an array of characters for the entire string. It has this general form:

char[ ] toCharArray( )

This function is provided as a convenience, since it is possible to use getChars( ) to achieve the same result.



What are the various types of relationships?



Relationships are classified as followsA Kind-Of relationship.· A Is-A relationship.· A Part-Of-relationship.· A Has-A relationship.

A-Kind-Of Relationship


Taking the example of a human being and an elephant, both are ‘kind-of’ mammals. As human beings and elephants are ‘kind-of’ mammals, they share the attributes and behaviors of mammals. Human being and elephants are subset of the mammals class. The following figure depicts the relationship between the Mammals and Human Being classes












Is-A Relationship


Let’s take an instance of the human being class – peter, who ‘is –a’ human being and, therefore, a mammal. The following figure depicts the ‘is –a’ relationship.






Has-A Relationship/Part-Of Relationship


A human being has a heart. This represents has-a relationship. Heart is a part of the human being. This represents part-of relationship. The following figure depicts the relationship between a human being and a heart.










Differentiate between errors and exceptions.






The term exception denotes an exceptional event. It can be defined as an abnormal event that occurs during program execution and disrupts the normal flow of instruction.

Error-handling becomes a necessity when you develop applications that need to take care of unexpected situations. The unexpected situations that may occur during program execution are:

· Running out of memory.

· Resource allocation errors.

· Inability to find a file.

· Problems in network connectivity.

If an above-mentioned situation is encountered, a program may stop working. You cannot afford to have an application stop working or crashing, if the requested file is not present on the disk. Traditionally, programmers use return values of methods to detect the errors that has occurred at runtime. A variable errno was used for a numeric representation of the error. When multiple errors has occurred in a method, errno would have only one value-that of the last error that occurred in the method.

Java handles exceptions in the object-oriented way. You can use a hierarchy of exception classes to manage runtime errors.


The class at the top of the exception classes hierarchy is Throwable class. Two classes are derived from the Throwable class – Error and Exception. The Exception class is used for the exceptional conditions that has to be trapped in a program. The Error class defines a condition that does not occur under normal circumstances. In other words, the Error class is used for catastrophic failures such as VirtualMachineError. These classes are available in the java.lang package.


Java has several predefined exceptions. The most common exceptions that you may encounter are described below.



· Arithmetic Exception


This exception is thrown when an exceptional arithmetic condition has occurred. For example, a division by zero generates such an exception.




· NullPointer Exception


This exception is thrown when an application attempts to use null where an object is required. An object that has not been allocated memory holds a null value. The situations in which an exception is thrown include:



Give the syntax for FileInputStream and FileOutputStream classes.




These streams are classified as mode streams as they read and write data from disk files. The classes associated with these streams have constructors that allow you to specify the path of the file to which they are connected. The FileInputStream class allows you to read input from a file in the form of a stream. The FileOutputStream class allows you to write output to a file stream.

Example:

FileInputStream inputfile = new FileInputStream (“Employee.dat”);

FileOutputStream outputfile = new FileOutputStream (“binus.dat”);

The BufferedInputStream and BufferedOutputStream Classes

The BufferedInputStream class creates and maintains a buffer for an input stream. This class is used to increase the efficiency of input operations. This is done by reading data from the stream one byte at a time. The BufferedOutputStream class creates and maintains a buffer for the output stream. Both the classes represent filter streams.


The DataInputStream and DataOutputStream Classes

The DataInputStream and DataOutputStream classes are the filter streams that allow the reading and writing of Java primitive data types.

The DataInputStream class provides the capability to read primitive data types from an input stream. It implements the methods presents in the DataInput interface.



What is an applet? Explain with an example.



An applet is a Java program that can be embedded in a web page. Java applications are run by using a Java interpreter. Applets are run on any browser that supports Java. Applets can also be tested using the appletviewer tool included in the Java Development Kit. In order to run an applet it must be included in a web page, using HTML tags. When a user browses a web server and it runs applets on the user’s system. Applets have certain restrictions put on them.

· They can not read or write files on the user’s system.

· They can not load or run any programs stored on the user’s system.



All applets are subclasses of the Applet class in the java.applet package. Applets do not have main() method. All applets must be declared public. An applet displays information on the screen by using the paint method. This method is available in java.awt.Component class. This method takes an instance of the class Graphics as parameter. The browser creates an instance of Graphics class and passes to the method paint(). Graphics class provides a method drawString to display text. It also requires a position to be specified as arguments.




The Applet tag is used to embed an applet in an HTML document. The Applet tag takes zero or more parameters.


The Applet Tag

The Applet tag is written in the body tag of an HTML document.

Syntax:

<APPLET

CODE = “name of the class file that extends java.applet.Applet”

CODEBASE = “path of the class file”

HEIGHT = “maximum height of the applet, in pixels”

WIDTH = “maximum width of the applet, in pixels”

VSPACE = “vertical space between the applet and the rest of the HTML”

HSPACE = “horizontal space between the applet and the rest of the HTML”

ALIGN = “alignment of the applet with respect to the rest of the web page”

ALT = “alternate text to be displayed if the browser does not support applets”

>

<PARAM NAME=“parameter_name” value=“value_of_parameter”>

……..

</APPLET>

The most commonly used attributes of the Applet tag are CODE, HEIGHT, WIDTH, CODEBASE and ALT. You can send parameters to the applet using the PARAM tag. The PARAM tag must be written between <APPLET> and </APPLET>


Example:

<applet

Code = “clock. class”

Height = 200

Width = 200 >

</applet>


Give the uses of adapter class.



The Java programming language provides adapter classes that implement the corresponding listener interfaces containing more than one method. The methods in these classes are empty. The listener class that you define can extend the Adapter class and override the methods that you need. The Adapter class is used for the WindowListener interface in the WindowAdapter class.


In the following program code, the adapter class has been used. This class has been used as an anonymous inner class to draw a rectangle within an applet. This example demonstrates the functionality of the mouse press. That is on every click of the mouse from top left corner, we get a rectangle on the release of the bottom right.

Import java.applet.*;
import java.awt.*;
importjava.awt.event.*;


public class AdapterDemo extends Applet{
public void init(){
addMouseListener(
newMouseAdapter(){
int topX, bottomY;
public void mousePressed(MouseEventme){
topX=me.getX();
bottomY=me.getY();
}
public voidmouseReleased(MouseEvent me){
Graphics g = AdapterDemo.this.getGraphics();
g.drawRect(topX, bottomY, me.getX()-topX, me.getY()-bottomY);
}
});
}
}



What is JDBC? Explain.


The JDBC API (Java Data Base Connectivity Application Program Interface) can access any kind of tabular data, especially data stored in a Relational Database. It works on top of ODBC (Open Data Base Connectivity) which was the driver for database connectivity since age old days but since ODBC was implemented in C so people from the VB background had some problems in understanding the implementation intricacies. Since JDBC works on top of ODBC we have something called as a JDBC-ODBC bridge to access the database. JDBC helps you to write Java applications that manage mainly three programming activities listed below namely:


a) connecting to a data source, like a database,

b) sending queries and updating statements to the database and

c) retrieving and processing the results received from the database in answer to your query

JDBC is Java application programming interface that allows the Java programmers to access database management system from Java code. It was developed by JavaSoft, a subsidiary of Sun Microsystems.


Database Management


Java Database Connectivity in short called as JDBC. It is a java API which enables the java programs to execute SQL statements. It is an application programming interface that defines how a java programmer can access the database in tabular format from Java code using a set of standard interfaces and classes written in the Java programming language.


JDBC has been developed under the Java Community Process that allows multiple implementations to exist and be used by the
same application. JDBC provides methods for querying and updating the data in Relational Database Management system such as SQL, Oracle etc.

The Java application programming interface provides a mechanism for dynamically loading the correct Java packages and drivers and registering them with the JDBC Driver Manager that is used as a connection factory for creating JDBC connections which supports creating and executing statements such as SQL INSERT, UPDATE and DELETE.Driver Manager is the backbone of the jdbc architecture.


Generally all Relational Database Management System supports SQL and we all know that Java is platform independent, so JDBC makes it possible to write a single database application that can run on different platforms and interact with different Database Management Systems.Java Database Connectivity is similar to Open Database Connectivity (ODBC) which is used for accessing and managing database, but the difference is that JDBC is designed specifically for Java programs, whereas ODBC is not depended upon any language.


In short JDBC helps the programmers to write java applications that manage these three programming activities:

1. It helps us to connect to a data source, like a database.
2. It helps us in sending queries and updating statements to the database and

3. Retrieving and processing the results received from the database in terms of answering to your query.

BT0072 Computer Networks -2








1. Define the term routing. Differentiate static and dynamic routing.


Routing (or routeing) is the process of selecting paths in a network along which to send network traffic. Routing is performed for many kinds of networks, including the telephone network, electronic data networks (such as the Internet), and transportation networks. In packet switching networks, routing directs packet forwarding, the transit of logically addressed packets from their source toward their ultimate destination through intermediate nodes; typically hardware devices called routers, bridges, gateways, firewalls, or switches

Static Routing


Static routing is not really a protocol, simply the process of manually entering routes into the routing table via a configuration file that is loaded when the routing device starts up. As an alternative, these routes can be enterd by a network administrator who configures the routes. Since these routes don't change after they are configured (unless a human changes them) they are called 'static' routes.

Static routing is the simplest form of routing, but it is a manual process and does not work well when the routing information has to be changed frequently or needs to be configured on a large number of routing devices (routers). Static routing also does not handle outages or down connections well because any route that is configured manually must be reconfigured manually to fix or repair any lost connectivity.


Dynamic Routing


Dynamic routing protocols are software applications that dynamically discover network destinations and how to get to them.

A router will 'learn' routes to all directly connected networks first. It will then learn routes from other routers that run the same routing protocol. The router will then sort through it's list of routes and select one or more 'best' routes for each network destination it knows or has learned.

Dynamic protocols will then distribute this 'best route' information to other routers running the same routing protocol, thereby extending the information on what networks exist and can be reached. This gives dynamic routing protocols the ability to adapt to logical network topology changes, equipment failures or network outages 'on the fly'.


2. Differentiate IPv4 and IPv6 addressing schemes.


Pv6 is based on IPv4, it is an evolution of IPv4. So many things that we find with IPv6 are familiar to us. The main differences are:

1.Simplified header format. IPv6 has a fixed length header, which does not include most of the options an IPv4 header can include. Even though the IPv6 header contains two 128 bit addresses (source and destination IP address) the whole header has a fixed length of 40 bytes only. This allows for faster processing.
Options are dealt with in extension headers, which are only inserted after the IPv6 header if needed. So for instance if a packet needs to be fragmented, the fragmentation header is inserted after the IPv6 header. The basic set of extension headers is defined in RFC 2460.
2.Address extended to 128 bits. This allows for hierarchical structure of the address space and provides enough addresses for almost every 'grain of sand' on the earth. Important for security and new services/devices that will need multiple IP addresses and/or permanent connectivity.
3.A lot of the new IPv6 functionality is built into ICMPv6 such as Neighbor Discovery, Autoconfiguration, Multicast Listener Discovery, Path MTU Discovery.
4.Enhanced Security and QoS Features.

IPv4 means Internet Protocol version 4, whereas IPv6 means Internet Protocol version 6. IPv4 is 32 bits IP address that we use commonly, it can be 192.168.8.1, 10.3.4.5 or other 32 bits IP addresses. IPv4 can support up to 232 addresses, however the 32 bits IPv4 addresses are finishing to be used in near future, so IPv6 is developed as a replacement. IPv6 is 128 bits, can support up to 2128 addresses to fulfill future needs with better security and network related features. Here are some examples of IPv6 address: 1050:0:0:0:5:600:300c:326b
ff06::c3
0:0:0:0:0:0:192.1.56.10

3. Describe the following:



a. Congestion


Network congestion occurs when a queue buffer of a network node is full and starts to drop packets. Automatic repeat request may keep the network in a congested state. This situation can be avoided by adding congestion avoidance to the flow control, including slow-start. This keeps the bandwidth consumption at a low level in the beginning of the transmission, or after packet retransmission.


b. Congestion Control


One of the main principles for congestion control is avoidance. TCP tries to detect signs of congestion before it happens and to reduce or increase the load into the network accordingly. The alternative of waiting for congestion and then reacting is much worse because once a network saturates, it does so at an exponential growth rate and reduces overall throughput enormously. It takes a long time for the queues to drain, and then all senders again repeat this cycle. By taking a proactive congestion avoidance approach, the pipe is kept as full as possible without the danger of network saturation. The key is for the sender to understand the state of the network and client and to control the amount of traffic injected into the system. Flow control is accomplished by the receiver sending back a window to the sender. The size of this window, called the receive window, tells the sender how much data to send. Often, when the client is saturated, it might not be able to send back a receive window to the sender to signal it to slow down transmission. However, the sliding windows protocol is designed to let the sender know, before reaching a meltdown, to start slowing down transmission by a steadily decreasing window size. At the same time these flow control windows are going back and forth, the speed at which ACKs come back from the receiver to the sender provides additional information to the sender that caps the amount of data to send to the client. This is computed indirectly.


4. Discuss the Remote Procedure Calls and their importance in Session Layer.



Remote Procedure Call (RPC) is a protocol which works in session layer of OSI model and in the Application Layer of TCP/IP model. It is useful in developing network applications which need services from a remote computer in the network. With the help of Remote Procedure Call the programmer need not worry about the complex structure of OSI layer.

Working of Remote Procedure Call

1. RPC is working as a client server model

2. RPC uses different Authentication methods to validate the client request.

3. RPC protocol is independent of transport layer protocols.

4. RPC reuests can use both UDP and TCP but prefer UDP format

RPC uses the client/server model. The requesting program is a client and the service-providing program is the server. First, the caller process sends a call message that includes the procedure parameters to the server process. Then, the caller process waits for a reply message (blocks). Next, a process on the server side, which is dormant until the arrival of the call message, extracts the procedure parameters, computes the results, and sends a reply message. The server waits for the next call message. Finally, a process on the caller receives the reply message, extracts the results of the procedure, and the caller resumes execution.



5. Discuss Simple Mail Transfer Protocol in detail.


Simple Mail Transfer Protocol (SMTP), documented in RFC 821, is Internet's standard host-to-host mail transport protocol and traditionally operates over TCP, port 25. In other words, a UNIX user can type telnet hostname 25 and connect with an SMTP server, if one is present. SMTP uses a style of asymmetric request-response protocol popular in the early 1980s, and still seen occasionally, most often in mail protocols. The protocol is designed to be equally useful to either a computer or a human, though not too forgiving of the human. From the server's viewpoint, a clear set of commands is provided and well-documented in the RFC. For the human, all the commands are clearly terminated by newlines and a HELP command lists all of them. From the sender's viewpoint, the command replies always take the form of text lines, each starting with a three-digit code identifying the result of the operation, a continuation character to indicate another lines following, and then arbitrary text information designed to be informative to a human.

If mail delivery fails, sendmail (the most important SMTP implementation) will queue mail messages and retry delivery later. However, a backoff algorithm is used, and no mechanism exists to poll all Internet hosts for mail, nor does SMTP provide any mailbox facility, or any special features beyond mail transport. For these reasons, SMTP isn't a good choice for hosts situated behind highly unpredictable lines (like modems). A better-connected host can be designated as a DNS mail exchanger, then arrange for a relay scheme. Currently, there two main configurations that can be used. One is to configure POP mailboxes and a POP server on the exchange host, and let all users use POP-enabled mail clients. The other possibility is to arrange for a periodic SMTP mail transfer from the exchange host to another, local SMTP exchange host which has been queuing all the outbound mail. Of course, since this solution does not allow full-time Internet access, it is not too preferred. RFC 1869 defined the capability for SMTP service extensions, creating Extended SMTP, or ESMTP. ESMTP is by definition extensible, allowing new service extensions to be defined and registered with IANA. Probably the most important extension currently available is Delivery Status Notification (DSN), defined in RFC 1891.


6. Explain any two static routing algorithms.


Dijkstra's algorithm, conceived by Dutch computer scientist Edsger Dijkstra in 1959,[1] is a graph search algorithm that solves the single-source shortest path problem for a graph with nonnegative edge path costs, producing a shortest path tree. This algorithm is often used in routing. An equivalent algorithm was developed by Edward F. Moore in 1957. Let the node at which we are starting be called the initial node. Let the distance of node Y be the distance from the initial node to Y. Dijkstra's algorithm will assign some initial distance values and will try to improve them step-by-step.


Assign to every node a distance value. Set it to zero for our initial node and to infinity for all other nodes.


Mark all nodes as unvisited. Set initial node as current.


For current node, consider all its unvisited neighbors and calculate their distance (from the initial node). For example, if current node (A) has distance of 6, and an edge connecting it with another node (B) is 2, the distance to B through A will be 6+2=8. If this distance is less than the previously recorded distance (infinity in the beginning, zero for the initial node), overwrite the distance.


When we are done considering all neighbors of the current node, mark it as visited. A visited node will not be checked ever again; its distance recorded now is final and minimal.


If all nodes have been visited, finish. Otherwise, set the unvisited node with the smallest distance (from the initial node) as the next "current node" and continue from step 3.

Suppose you want to find the shortest path between two intersections on a map, a starting point and a destination. To accomplish this, you could highlight the streets (tracing the streets with a marker) in a certain order, until you have a route highlighted from the starting point to the destination. The order is conceptually simple: at each iteration, create a set of intersections consisting of every unmarked intersection that is directly connected to a marked intersection, this will be your set of considered intersections. From that set of considered intersections, find the closest intersection to the destination (this is the "greedy" part, as described above) and highlight it and mark that street to that intersection, draw an arrow with the direction, then repeat. In each stage mark just one new intersection. When you get to the destination, follow the arrows backwards. There will be only one path back against the arrows, the shortest one.

A flooding algorithm is an algorithm for distributing material to every part of a connected network. The name derives from the concept of inundation by a flood.Flooding algorithms are used in systems such as Usenet and peer-to-peer file sharing systems and as part of some routing protocols, including OSPF, DVMRP, and those used in ad-hoc wireless networks.There are several variants of flooding algorithm: most work roughly as follows.

Each node acts as both a transmitter and a receiver.


Each node tries to forward every message to every one of its neighbors except the source node.

This results in every message eventually being delivered to all reachable parts of the network Real-world flooding algorithms have to be more complex than this, since precautions have to be taken to avoid wasted duplicate deliveries and infinite loops, and to allow messages to eventually expire from the system. Flooding algorithms are also useful for solving many mathematical problems, including maze problems and many problems in graph theory.



7. Discuss IPv4 addressing schemes.


Internet Protocol version 4 (IPv4) is the fourth revision in the development of the Internet Protocol (IP) and it is the first version of the protocol to be widely deployed. Together with IPv6, it is at the core of standards-based internetworking methods of the Internet. IPv4 is still by far the most widely deployed Internet Layer protocol. As of 2010, IPv6 deployment is still in its infancy. IPv4 uses 32-bit (four-byte) addresses, which limits the address space to 4,294,967,296 (232) possible unique addresses. However, some are reserved for special purposes such as private networks (~18 million addresses) or multicast addresses (~270 million addresses). This reduces the number of addresses that can potentially be allocated for routing on the public Internet. As addresses are being incrementally delegated to end users, an IPv4 address shortage has been developing, however network addressing architecture redesign via classful network design, Classless Inter-Domain Routing, and network address translation (NAT) has significantly delayed the inevitable exhaustion.This limitation has stimulated the development of IPv6, which is currently in the early stages of deployment, and is the only long-term solution.Pv4 addresses are usually written in dot-decimal notation, which consists of the four octets of the address expressed in decimal and separated by periods. This is the base format used in the conversion in the following table:



Notation

Value

Conversion from dot-decimal


Dot-decimal notation

192.0.2.235

N/A


Dotted Hexadecimal

0xC0.0x00.0x02.0xEB

Each octet is individually converted to hexadecimal form


Dotted Octal

0300.0000.0002.0353

Each octet is individually converted into octal


Hexadecimal

0xC00002EB

Concatenation of the octets from the dotted hexadecimal


Decimal

3221226219

The 32-bit number expressed in decimal


Octal

030000001353

The 32-bit number expressed in octal


Most of these formats should work in all browsers. Additionally, in dotted format, each octet can be of any of the different bases. For example, 192.0x00.0002.235 is a valid (though unconventional) equivalent to the above addresses.A final form is not really a notation since it is rarely written in an ASCII string notation. That form is a binary form of the hexadecimal notation in binary. This difference is merely the representational difference between the string "0xCF8E83EB" and the 32-bit integer value 0xCF8E83EB. This form is used for assigning the source and destination fields in a software program.

8. Discuss Congestion Avoidance in Transport Layer.



Open loop solutions solve the problem by good design, in essence, to make sure the problem does not occur in the first place.

Tools include deciding when to accept new traffic, when to discard packets and which ones, and how to schedule packets at various points in the network. A common fact: they make decisions without regard to the current state of the network.


Closed loop solutions are based on the concept of a feedback loop, which consists of the following three parts:


Monitor the system to detect when and where congestion occurs.


Pass this information to places where actions can be taken.


Adjust system operation to correct the problem.






9. Discuss various design issues of Session Layer.


The session layer allows users on different machines to establish sessions between them. A session allows ordinary data transport, as does the transport layer, but it also provides enhanced services useful in some applications.

Some of these services are:


Dialog control - session can allow traffic to go in both directions at the same time, or in only one direction at a time. If traffic can go only in one way at a time, the session layer can help to keep track of whose turn it is. Simplex Dialogs - One way transfers, similar to an old computer/printer. Data could be sent to the printer but the printer could not communicate back.


Half-Duplex Dialogs - Two-way transfers. Each device must take their turn. Both cannot transfer at the same time.


Full-Duplex Dialogs - Two-way simultaneous data transfers. Each device has it's own channel. Telephones are full duplex, as are most modems. Full duplex is the most expensive to implement.




Token management - for some protocols it is essential that both sides do not attempt the same operation at the same time. The session layer provides tokens that can be exchanged. Only the side holding the token may perform the critical action.


Synchronization - by inserting checkpoints into the data stream the layer eliminates problems with potential crashes at long operations. After a crash, only the data transferred after the last checkpoint have to be repeated.

The user of the session layer is in similar position as the user of the transport layer but having larger possibilities. Session Administration: A session is a dialog or conversation between a service requester and a service provider. There are three phases:

Connection Establishment

Several tasks can be done at this phase:


Specification of required services


Login authentication and other security procedures


Protocol negotiation and parameters


Notification of connection IDs


Establishment of dialog control, as well as acknowledgment of numbering, and retransmission procedures.

Data Transfer

Once the connection has been established, the devices exchange data, acknowledgments, and other control data that manage the dialog. The session layer can also incorporate protocols to resume broken dialogs. If a connection has not been properly released, the session can be resumed without re-establishment. The devices have a specified time period to do so.

Connection Release

Consists of shutting down communications and releasing resources on the service provider in an orderly fashion.

10. Describe the Multipurpose Internet Mail Extensions.


Multipurpose Internet Mail Extensions (MIME) is an Internet standard that extends the format of e-mail to support:


Text in character sets other than ASCII


Non-text attachments


Message bodies with multiple parts


Header information in non-ASCII character sets

MIME's use, however, has grown beyond describing the content of e-mail to describing content type in general, including for the web (see Internet media type).Virtually all human-written Internet e-mail and a fairly large proportion of automated e-mail is transmitted via SMTP in MIME format. Internet e-mail is so closely associated with the SMTP and MIME standards that it is sometimes called SMTP/MIME e-mail.The content types defined by MIME standards are also of importance outside of e-mail, such as in communication protocols like HTTP for the World Wide Web. HTTP requires that data be transmitted in the context of e-mail-like messages, although the data most often is not actually e-mail. The basic Internet e-mail transmission protocol, SMTP, supports only 7-bit ASCII characters (see also 8BITMIME). This effectively limits Internet e-mail to messages which, when transmitted, include only the characters sufficient for writing a small number of languages, primarily English. Other languages based on the Latin alphabet typically include diacritics not supported in 7-bit ASCII, meaning text in these languages cannot be correctly represented in basic e-mail. MIME defines mechanisms for sending other kinds of information in e-mail. These include text in languages other than English using character encodings other than ASCII, and 8-bit binary content such as files containing images, sounds, movies, and computer programs. MIME is also a fundamental component of communication protocols such as HTTP, which requires that data be transmitted in the context of e-mail-like messages even though the data might not (and usually doesn't) actually have anything to do with e-mail. Mapping messages into and out of MIME format is typically done automatically by an e-mail client or by mail servers when sending or receiving Internet (SMTP/MIME) e-mail.


Popular Posts

Pages

Blog Archive

Scroll To Top