Two Level Architecture

Two level architecture is similar to a basic client-server model. The application at the client end directly communicates with the database at the server side.

API's like ODBC, JDBC are used for this interaction.

The server side is responsible for providing query processing and transaction management functionalities.

On the client side, the user interfaces and application programs are run.

The application on the client side establishes a connection with the server side in order to communicate with the DBMS.

An advantage of this type is that maintenance and understanding are easier, compatible with existing systems.

However, this model gives poor performance when there are a large number of users.

DBMS 3-tier Architecture

DBMS 3-tier architecture divides the complete system into three inter-related but independent modules.

1. Presentation or User Tier: This tier is presented before the user of who knows nothing regarding the other existing complex databases underneath. This tier can provide multiple views of the databases, generated by the application residing in the application tier.

2. Application Tier: This is the middle lined tier between the Database and the Presentation tier and acts as a connection between the end-user and the database. This tier holds the programs and the application server along with the programs that could access the database. This is the last layer that the users are made aware of. It provides a complete abstraction to the database layer.

3. Database Tier: This tier holds the primary database along with all the data and the query languages for processing. The relations of data with their constraints are also defined in this level.

Advantages:

• Enhanced scalability due to distributed deployment of application servers. Now, individual connections need not be made between client and server.

• Data Integrity is maintained. Since there is a middle layer between client and server, data corruption can be avoided/removed.

• Security is improved. This type of model prevents direct interaction of the client with the server thereby reducing access to unauthorized data.

Disadvantages:

Increased complexity of implementation and communication. It becomes difficult for this sort of interaction to take place due to the presence of middle layers.

Data Independence

Data independence means a change of data at one level should not affect another level.

Two types of data independence are present in this architecture:

1. Physical Data Independence: Any change in the physical location of tables and indexes should not affect the conceptual level or external view of data. This data independence is easy to achieve and implemented by most of the DBMS.

2. Conceptual Data Independence: The data at conceptual level schema and external level schema must be independent. This means, change in conceptual schema should not affect external schema. e.g. Adding or deleting attributes of a table should not affect the user’s view of the table. But this type of independence is difficult to achieve as compared to physical data independence because the changes in conceptual schema are reflected in user’s view.

Phases of database design

Database designing for a real world application starts from capturing the requirements to physical implementation using DBMS software.

Conceptual Design: The requirements of a database are captured using a high level conceptual data model. For Example, ER model is used for conceptual design of database.

Logical Design: Logical Design represents data in the form of relational model. The ER diagram produced in the conceptual design phase is used to convert the data into the Relational Model.

Physical Design: In physical design, data in relational model is implemented using commercial DBMS like Oracle, DB2.