Database

Document Sample
Database Powered By Docstoc
					Table of Contents
DBMS VS RDBMS........................................................................................................................................... 2
Normalization and DeNormalization ............................................................................................................ 2
       What is Normalization?......................................................................................................................... 2
       The Normal Forms................................................................................................................................. 2
       First Normal Form (1NF) ....................................................................................................................... 3
       Second Normal Form (2NF)................................................................................................................... 3
       Third Normal Form (3NF) ...................................................................................................................... 4
       Fourth Normal Form (4NF).................................................................................................................... 4
       What is Denormalization?..................................................................................................................... 5
JDBC Drivers .................................................................................................................................................. 5
   Type 1 JDBC Driver .................................................................................................................................... 5
   Type 2 JDBC Driver .................................................................................................................................... 6
   Type 3 JDBC Driver .................................................................................................................................... 7
   Type 4 JDBC Driver .................................................................................................................................... 8
Relationship .................................................................................................................................................. 9
       Many to Many: ...................................................................................................................................... 9
       One to Many: ........................................................................................................................................ 9
       One to One: ........................................................................................................................................... 9
   Getting Started with JDBC....................................................................................................................... 10
   Simple JDBC Example .............................................................................................................................. 11
   Statement VS PreparedStatement.......................................................................................................... 12
   Connection Pooling ................................................................................................................................. 12
       Creating a Database Connection Pool ................................................................................................ 13
   Joins ........................................................................................................................................................ 13
   Other Notes: ........................................................................................................................................... 15




Database                                                                                                                                                 Page 1
Database
DBMS VS RDBMS

DBMS                                                  RDBMS
  1. DBMS is an ordinary                                1. RDBMS is an specific
     system to maintain the                                system which is based
     data.                                                 on relational model in
  2. There is no concept of                                which data is
     PRIMARY KEY and FOREIGN                               represented in the form
     KEY                                                   of relations
  3. DBMS contains only flat                            2. RDBMS has PRIMARY KEY
     data                                                  and FOREIGN KEY concept
  4. No normalization                                   3. In RDBMS there will be
  5. It treats Data as Files                              some relation between
     internally                                           the entities.
  6. Ex: Foxpro                                         4. Normalization is
                                                           present
                                                        5. It treats data as
                                                           Tables internally
                                                        6. Ex: Oracle, Mysql



Normalization and DeNormalization
What is Normalization?
Normalization is the process of efficiently organizing data in a database. There are two goals of the
normalization process: eliminating redundant data (for example, storing the same data in more than one
table) and ensuring data dependencies make sense (only storing related data in a table). Both of these
are worthy goals as they reduce the amount of space a database consumes and ensure that data is
logically stored.

The Normal Forms
The database community has developed a series of guidelines for ensuring that databases are
normalized. These are referred to as normal forms and are numbered from one (the lowest form of
normalization, referred to as first normal form or 1NF) through five (fifth normal form or 5NF). In
practical applications, you'll often see 1NF, 2NF, and 3NF along with the occasional 4NF. Fifth normal
form is very rarely seen and won't be discussed in this article.




Database                                                                                           Page 2
First Normal Form (1NF)
First normal form (1NF) sets the very basic rules for an organized database:

       Eliminate duplicative columns from the same table.
       Create separate tables for each group of related data and identify each row with a unique
        column or set of columns (the primary key).




Second Normal Form (2NF)
Second normal form (2NF) further addresses the concept of removing duplicative data:

       Meet all the requirements of the first normal form.
       Remove subsets of data that apply to multiple rows of a table and place them in separate tables.
       Create relationships between these new tables and their predecessors through the use of
        foreign keys.




Database                                                                                            Page 3
Third Normal Form (3NF)
Third normal form (3NF) goes one large step further:

      Meet all the requirements of the second normal form.
      Remove columns that are not dependent upon the primary key.




Fourth Normal Form (4NF)
Finally, fourth normal form (4NF) has one additional requirement:

      Meet all the requirements of the third normal form.
      A relation is in 4NF if it has no multi-valued dependencies.


Database                                                              Page 4
Remember, these normalization guidelines are cumulative. For a database to be in 2NF, it must first
fulfill all the criteria of a 1NF database.

A complete normalization of tables is desirable, but you may find that in practice that full normalization
can introduce complexity to your design and application. More tables often mean more JOIN operations,
and in most database management systems (DBMSs) such JOIN operations can be costly, leading to
decreased performance. The key lies in finding a balance where the first three normal forms are
generally met without creating an exceedingly complicated schema.

Normalization may lead to poor performance

What is Denormalization?
Denormalization is a technique to move from higher to lower normal forms of database modeling in
order to speed up database access. You may apply Denormalization in the process of deriving a physical
data model from a logical form

Only one valid reason exists for denormalizing a relational design is to enhance performance


JDBC Drivers

JDBC drivers are divided into four types or levels. The different types of jdbc drivers are:

Type 1: JDBC-ODBC Bridge driver (Bridge)
Type 2: Native-API/partly Java driver (Native)
Type 3: All Java/Net-protocol driver (Middleware)
Type 4: All Java/Native-protocol driver (Pure)



Type 1 JDBC Driver

JDBC-ODBC Bridge driver

The Type 1 driver translates all JDBC calls into ODBC calls and sends them to the ODBC
driver. ODBC is a generic API. The JDBC-ODBC Bridge driver is recommended only for
experimental use or when no other alternative is available.




Database                                                                                           Page 5
Type 1: JDBC-ODBC Bridge

Advantage

The JDBC-ODBC Bridge allows access to almost any database, since the database's ODBC
drivers are already available.

Disadvantages

1. Since the Bridge driver is not written fully in Java, Type 1 drivers are not portable.
2. A performance issue is seen as a JDBC call goes through the bridge to the ODBC driver, then
to the database, and this applies even in the reverse process. They are the slowest of all driver
types.
3. The client system requires the ODBC Installation to use the driver.
4. Not good for the Web.


Type 2 JDBC Driver

Native-API/partly Java driver

The distinctive characteristic of type 2 jdbc drivers are that Type 2 drivers convert JDBC calls
into database-specific calls i.e. this driver is specific to a particular database. Some distinctive
characteristic of type 2 jdbc drivers are shown below. Example: Oracle will have oracle native
api.




Database                                                                                        Page 6
Type 2: Native api/ Partly Java Driver

Advantage
The distinctive characteristic of type 2 jdbc drivers are that they are typically offer better performance
than the JDBC-ODBC Bridge as the layers of communication (tiers) are less than that of Type
1 and also it uses Native api which is Database specific.

Disadvantage

1. Native API must be installed in the Client System and hence type 2 drivers cannot be used for
the Internet.
2. Like Type 1 drivers, it’s not written in Java Language which forms a portability issue.
3. If we change the Database we have to change the native api as it is specific to a database
4. Mostly not in use now
5. Usually not thread safe.

Type 3 JDBC Driver

All Java/Net-protocol driver

Type 3 database requests are passed through the network to the middle-tier server. The middle-
tier then translates the request to the database. If the middle-tier server can in turn use Type1,
Type 2 or Type 4 drivers.




Database                                                                                             Page 7
Type 3: All Java/ Net-Protocol Driver

Advantage

1. This driver is server-based, so there is no need for any vendor database library to be present on
client machines.
2. This driver is fully written in Java and hence Portable. It is suitable for the web.
3. There are many opportunities to optimize portability, performance, and scalability.
4. The net protocol can be designed to make the client JDBC driver very small and fast to load.
5. The type 3 driver typically provides support for features such as caching (connections, query
results, and so on), load balancing, and advanced
system administration such as logging and auditing.
6. This driver is very flexible allows access to multiple databases using one driver.
7. They are the most efficient amongst all driver types.

Disadvantage

It requires another server application to install and maintain. Traversing the recordset may take
longer, since the data comes through the backend server.

Type 4 JDBC Driver

Native-protocol/all-Java driver
The Type 4 uses java networking libraries to communicate directly with the database server.




Database                                                                                     Page 8
Type 4: Native-protocol/all-Java driver

Advantage

1. The major benefit of using a type 4 jdbc drivers are that they are completely written in Java to
achieve platform independence and eliminate deployment administration issues. It is most
suitable for the web.
2. Number of translation layers is very less i.e. type 4 JDBC drivers don't have to translate
database requests to ODBC or a native connectivity interface or to pass the request on to another
server, performance is typically quite good.
3. You don’t need to install special software on the client or server. Further, these drivers can be
downloaded dynamically.

Disadvantage

With type 4 drivers, the user needs a different driver for each database.


Relationship
       Many to Many: To represent a many-to-many relationship in a relational database we need a
       third table to serve as a link between the two.

       One to Many: When we have a one-to-many relationship, we place a foreign key in the table
       representing the many, pointing to the primary key of the table representing the one

       One to One:




Database                                                                                      Page 9
Getting Started with JDBC

1. Make the JDBC classes available to your Java program using:

       import java.sql.*;

2. Load the driver for the database system using:

       Class.forName(<driverName>);

3. Establish a connection to the database using :

       Connection con= DriverManage.getConnection(<url>,<user>,<password>);


Now we have a connection object called con which we can use to create statements. There are 2
methods to create statements:

       a) createStatement() returns a Statement object

       b) prepareStatement(Q) where Q is a SQL query passed as a string argument, returns a
       preparedStatement object


There are 4 methods that execute SQL statements.

       a) executeQuery(Q) takes a statement Q and is applied to a Statement object. This method
       returns a ResultSet object, which is the set of tuples produced by the query Q

       b) executeQuery() is applied to a preparedStatement object. This method also returns a
       ResultSet object.

       c) excuteUpdate(U) takes a nonquery statement U and, when applied to a statement object,
       executes U.

       d) executeUpdate() is applied to a preparedStatement object. The SQL statement associated
       with the prepared statement is executed.




Database                                                                                        Page 10
Simple JDBC Example

      Below is the example for both java.sql and javax.sql API to create connection object.
      Direct connection uses java.sql package and datasource connection uses javax.sql &
      javax.naming package.

      import java.sql.*;

      import javax.naming.*;
      import javax.sql.*;

      private   static String dataSourceName="test";
      private   static String url = "jdbc:mysql://localhost:3306/";
      private   static String dbName = "jointest";
      private   static String driver = "com.mysql.jdbc.Driver";
      private   static String userName = "root";
      private   static String password = "mysql";
      private   Connection conn = null;
      private   PreparedStatement pstmt=null;
      private   ResultSet rs = null;
      private   static final String SELECT_EMP_NAME = "Query here";

      /**
        * Direct database connection example using java.sql package
        */
      public Connection getDirectConnection(){
      try {
             Class.forName(driver);
             conn = DriverManager.getConnection(url+dbName,userName,password);
      }catch(SQLException e){
      }catch(Exception ex){
      }
      return conn;
      }

      /**
      * Database connection using datasource using javax.sql and
      * javax.naming package
        */
      public Connection getDataSourceConnection(){
      DataSource dataSource;
      try {
            InitialContext ctx = new InitialContext();
            dataSource = (DataSource) ctx.lookup(dataSourceName);
            conn = dataSource.getConnection();
      }catch (NamingException ne) {
      }catch(SQLException e){
      }catch(Exception ex){
      }
      return conn;
      }




Database                                                                                      Page 11
       public void getEmpDetails(){
       try {
             conn = getDataSourceConnection();
             conn = getDirectConnection();
             pstmt = con.prepareStatement(SELECT_EMP_NAME);
             pstmt.setInt(1, deptId);
             rs=pstmt.executeQuery();
             while(rs.next())
                   System.ouy.println("Name:"+rs.getString(1));

       }catch(SQLException e){
       }catch(Exception ex){
       }finally{
             try{
                   if(con != null)
                         con.close();
                   if(rs != null)
                         rs.close();
                   if(pstmt!=null)
                         pstmt.close();
             }catch (SQLException e) {}
       }
       }


Statement VS PreparedStatement
Statement                                       PreparedStatement
    1. The object used for executing a static      1. An object that represents a precompiled
       SQL statement and returning the                SQL statement.
       results it produces                         2. A SQL statement is precompiled only once
    2. SQL statement will precompiled every           and stored in a PreparedStatement
       time, this will happen in database             object. This object can then be used to
       server                                         efficiently execute this statement multiple
    3. SQL statements without parameters              times
       are executed using Statement                3. SQL statements with parameters are
       objects                                        executed using Statement objects
    4. If the same SQL statement is executed       4. If the same SQL statement is executed
       single time use Statement object.              many times use a PreparedStatement
    5. Connection.createStatement()                   object.
                                                   5. Connection.prepareStatement(query)



Connection Pooling
Creating number of database connection object and holding those objects together is known as
Connection pooling and the collection of objects itself is called a pool.

Connection pooling is a technique used to avoid the overhead of making a new database connection
every time an application or server object requires access to a database.

Database                                                                                       Page 12
Because pools are kept completely initialized and ready to use, you effectively can grab an object from
one, use the object to get your work/task executed, and then return the object to the pool.



Creating a Database Connection Pool
The following are the major steps involved in accessing a connection object from a connection pool:
    1. Request a connection from the pool.
    2. Perform the required database operations.
    3. Return the connection to the pool.

In addition to the above, the connection pool manager has to do the following:
    1. Create the connection pool.
    2. Create the database connections and add them to the pool.
    3. Keep track of how many of them are being used.
    4. When all the elements in the pool are used up, return the appropriate information to the users.
    5. Allocate connections to the clients when the client requests them.
    6. De-allocate and put the connections back in the pool when the client returns them.
    7. Destroy the connection pool when the application is being shutdown


Joins
The SQL JOIN clause is used whenever we have to select data from 2 or more tables

        Types of Joins
           1. Inner Join[Default Join]
           2. Outer Join
           3. Self Join

For Examples consider employee and department tables
employee
empname                           deptId
John                              31
Jack                              32
Rose                              31

employee
deptname                             deptId
31                                   Sales
32                                   Production
33                                   Marketing



The INNER JOIN will select all rows from both tables as long as there is a match between the columns
we are matching on
Ex:

Database                                                                                         Page 13
SELECT * FROM employee INNER JOIN department ON employee.deptId = department.deptId;
                            OR
SELECT * FROM employee JOIN department ON employee.deptId = department.deptId;

Result:
+---------+--------+--------+------------+
| empname | deptId | deptId | deptname |
+---------+--------+--------+------------+
| John | 31 | 31 | Sales |
| Jack | 32 | 32 | Production |
+---------+--------+--------+------------+


The OUTER JOIN has 2 sub-types called LEFT OUTER JOIN and RIGHT OUTER JOIN.

The LEFT OUTER JOIN or simply LEFT JOIN (you can omit the OUTER keyword in most databases), selects
all the rows from the first table listed after the FROM clause, no matter if they have matches in the
second table.
EX:
SELECT * FROM employee LEFT JOIN department ON employee.deptId = department.deptId;

Result:
+---------+--------+--------+------------+
| empname | deptId | deptId | deptname |
+---------+--------+--------+------------+
| John | 31 | 31 | Sales |
| Jack | 32 | 32 | Production |
| Jose | NULL | NULL | NULL                |
+---------+--------+--------+------------+


The RIGHT OUTER JOIN or just RIGHT JOIN behaves exactly as SQL LEFT JOIN, except that it returns all
rows from the second table (the right table in our SQL JOIN statement).
Ex:
SELECT * FROM employee RIGHT JOIN department ON employee.deptId = department.deptId;
Result:
+---------+--------+--------+------------+
| empname | deptId | deptId | deptname |
+---------+--------+--------+------------+
| John | 31 | 31 | Sales |
| Jack | 32 | 32 | Production |
| NULL | NULL | 33 | Marketing |
+---------+--------+--------+------------+




Database                                                                                      Page 14
Other Notes:
   1. MySql server uses Type-4 JDBC driver
   2. The use of a DataSource object is the preferred means of connecting to a data source
   3. DriverManager is class and Connection, Statement, PrepaidStatement, CallableStatement
      and Resultsets are interfaces. All are available in java.sql
   4. CallableStatement interface to execute stored procedures
   5. javax .sql.Datasource is interface and javax.naming.InitialContext is class
   6. All interface implementation are done in their respective driver classes i.e., for mysql,
      implementation method will in com.mysql.jdbc package
      Example:
      com.sql.Connection is implemented by com.mysql.jdbc.Connection
      com.mysql.jdbc.ConnectionImpl implements com.mysql.jdbc.Connection
      ConnectionImpl has all implementations of com.sql.Connection interface methods.




Database                                                                                      Page 15

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:82
posted:8/20/2012
language:English
pages:15