Java Messaging using JMS & MOM
Application 1 similar setup as
A. It is hard to find a project, which does not use XML documents
• It is a universally accepted standard.
• Free and easy to use tools are available. Also can be stored in a database.
• Fast access due to its hierarchical structure.
• Easy to identify and use due to its markup tags.
Q. What is your favorite XML framework or a tool?
A. My favorite XML framework is JiBX, which unmarshals an XML document to graph of Java objects and marshals a graph of Java objects back to an XML document. It is simple to use, very flexible and fast. It can be used with existing Java classes.
Q. Explain where your project needed XML documents?
A. It is hard to find a project, which does not use XML documents.
• XML is used to communicate with disparate systems via messaging or Web Services.
• XML based protocols and standards like SOAP, ebXML, WSDL etc are used in Web Services.
• XML based deployment descriptors like web.xml, ejb-jar.xml, etc are used to configure the J2EE containers.
• XML based configuration files are used by open-source frameworks like Hibernate, Spring, Struts, and Tapestry etc.
Enterprise – SQL, Database, and O/R mapping
Q 96: Explain inner and outer joins? SF FAQ
A 96: Joins allow database users to combine data from one table with data from one or more other tables (or views, or synonyms). Tables are joined two at a time making a new table containing all possible combinations of rows from the original two tables. Lets take an example (syntax vary among RDBMS):
Joins and Set operations in Relational Databases
Inner join Left outer join Right outer join Full outer join
Employees table
Id Firstname Surname State
1001 John Darcy NSW
1002 Peter Smith NSW
1003 Paul Gregor NSW
1004 Sam Darcy VIC
Executives table
Id Firstname Surname State
1001 John Darcy NSW
1002 Peter Smith NSW
1005 John Gregor WA
Inner joins: Chooses the join criteria using any column names that happen to match between the two tables. The example below displays only the employees who are executives as well.
SELECT emp.firstname, exec.surname FROM employees emp, executives exec WHERE emp.id = exec.id;
The output is:
Firstname Surname
John Darcy Peter Smith
Left Outer joins: A problem with the inner join is that only rows that match between tables are returned. The example below will show all the employees and fill the null data for the executives.
SELECT emp.firstname, exec.surname FROM employees emp left join executives exec ON emp.id = exec.id;
On oracle
SELECT emp.firstname, exec.surname FROM employees emp, executives exec WHERE emp.id = exec.id(+);
The output is:
Firstname Surname
John Darcy Peter Smith Paul Sam
Right Outer join: A problem with the inner join is that only rows that match between tables are returned. The example below will show all the executives and fill the null data for the employees.
SELECT emp.firstname, exec.surname FROM employees emp right join executives exec ON emp.id = exec.id;
On oracle
SELECT emp.firstname, exec.surname FROM employees emp, executives exec WHERE emp.id(+) = exec.id;
The output is:
Firstname Surname
John Darcy Peter Smith Gregor
Full outer join: To cause SQL to create both sides of the join
SELECT emp.firstname, exec.surname FROM employees emp full join executives exec ON emp.id = exec.id;
On oracle
SELECT emp.firstname, exec.surname FROM employees emp, executives exec WHERE emp.id = exec.id (+)
UNION
SELECT emp.firstname, exec.surname FROM employees emp, executives exec WHERE emp.id(+) = exec.id
Note: Oracle9i introduced the ANSI compliant join syntax. This new join syntax uses the new keywords inner join, left outer join, right outer join, and full outer join, instead of the (+) operator.
The output is:
Firstname Surname
John Darcy Paul Peter Smith Sam
Gregor
Self join: A self-join is a join of a table to itself. If you want to find out all the employees who live in the same city as employees whose first name starts with “Peter”, then one way is to use a sub-query as shown below:
SELECT emp.firstname, emp.surname FROM employees emp WHERE
city IN (SELECT city FROM employees where firstname like ‘Peter’)
The sub-queries can degrade performance. So alternatively we can use a self-join to achieve the same results.
On oracle
SELECT emp.firstname, emp.surname FROM employees emp, employees emp2 WHERE emp.city = emp2.city
AND emp2.firstname LIKE 'Peter' The output is:
Firstname Surname
John Darcy Peter Smith Paul Gregor
Q 97: Explain a sub-query? How does a sub-query impact on performance? SF PI FAQ
A 97: It is possible to embed a SQL statement within another. When this is done on the WHERE or the HAVING statements, we have a subquery construct. What is subquery useful for? It is used to join tables and there are cases where the only way to correlate two tables is through a subquery.
SELECT emp.firstname, emp.surname FROM employees emp WHERE
emp.id NOT IN (SELECT id FROM executives);
There are performance problems with sub-queries, which may return NULL values. The above sub-query can be re-written as shown below by invoking a correlated sub-query:
SELECT emp.firstname, emp.surname FROM employees emp WHERE emp.id NOT EXISTS (SELECT id FROM executives);
The above query can be re-written as an outer join for a faster performance as shown below:
SELECT emp.firstname, exec.surname FROM employees emp left join executives exec on emp.id = exec.id AND exec.id IS NULL;
The above execution plan will be faster by eliminating the sub-query.
Q 98: What is normalization? When to denormalize? DC PI FAQ
A 98: Normalization is a design technique that is widely used as a guide in designing relational databases. Normalization is essentially a two step process that puts data into tabular form by removing repeating groups and then removes duplicated data from the relational tables (Additional reading recommended).
Redundant data wastes disk space and creates maintenance problems. If data that exists in more than one place must be changed, the data must be changed in exactly the same way in all locations which is time consuming and prone to errors. A change to a customer address is much easier to do if that data is stored only in the Customers table and nowhere else in the database.
Inconsistent dependency is a database design that makes certain assumptions about the location of data. For example, while it is intuitive for a user to look in the Customers table for the address of a particular customer, it may not make sense to look there for the salary of the employee who calls on that customer. The employee's salary is related to, or dependent on, the employee and thus should be moved to the Employees table.
Inconsistent dependencies can make data difficult to access because the path to find the data may not be logical, or may be missing or broken.
First Normal Form Second Normal Form Third Normal Form A database is said to be in First
Normal Form when all entities have a unique identifier or key, and when every column in every table contains only a single value and doesn't contain a repeating group or composite field.
A database is in Second Normal Form when it is in First Normal Form plus every non-primary key column in the table must depend on the entire primary key, not just part of it, assuming that the primary key is made up of composite columns.
A database is in Third Normal Form when it is in Second Normal Form and each column that isn't part of the primary key doesn't depend on another column that isn't part of the primary key.
When to denormalize? Normalize for accuracy and denormalize for performance.
Typically, transactional databases are highly normalized. This means that redundant data is eliminated and replaced with keys in a one-to-many relationship. Data that is highly normalized is constrained by the primary key/foreign key relationship, and thus has a high degree of data integrity. Denormalized data, on the other hand, creates redundancies; this means that it's possible for denormalized data to lose track of some of the relationships between atomic data items. However, since all the data for a query is (usually) stored in a single row in the table, it is much faster to retrieve.
Q 99: How do you implement one-to-one, one-to-many and many-to-many relationships while designing tables? SF A 99: One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign
key relationships.
One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships.
Many-to-Many relationships are implemented using join table with the keys from both the tables forming the composite primary key of the junction table.
Q 100: How can you performance tune your database? PI FAQ
A 100:
Denormalize your tables where appropriate.
Proper use of index columns: An index based on numeric fields is more efficient than an index based on character columns.
Reduce the number of columns that make up a composite key.
Proper partitioning of tablespaces and create a special tablespace for special data types like CLOB, BLOB etc.
Data access performance can be tuned by using stored procedures to crunch data in the database server to reduce the network overhead and also caching data within your application to reduce the number of accesses.
Q 101: How will you map objects to a relational database? How will you map class inheritance to relational data model?
DC FAQ
A 101: Due to impedance mismatch between object and relational technology you need to understand the process of mapping classes (objects) and their relationships to tables and relationships between them in a database.
Classes represent both behavior and data whereas relational database tables just implement data. Database schemas have keys (primary keys to uniquely identify rows and foreign keys to maintain relationships between rows) whereas object schema does not have keys and instead use references to implement relationships to other objects. Let us look at some basic points on mapping:
-EmployeeId (PK) Object to Relational (O/R) mapping of class inheritance structure
+getRate()
Map class hierarchy to a single database table
Refactored
Map each class to its own table
Map each concrete class to its own table
Classes map to tables in a way but not always directly.
An attribute of a class can be mapped to zero or more columns in a database. Not all attributes are persistent.
Some attributes of an object are objects itself. For example an Employee object has an Address object as an attribute. This is basically an association relationship between two objects (i.e. Employee and