The design of a DBMS depends on its architecture. It can be centralized or decentralized or hierarchical. The architecture of a DBMS can be seen as either single tier or multi-tier. An n-tier architecture divides the whole system into related but independent n modules, which can be independently modified, altered, changed, or replaced.
In 1-tier architecture, the DBMS is the only entity where the user directly sits on the DBMS and uses it. Any changes done here will directly be done on the DBMS itself. It does not provide handy tools for end-users. Database designers and programmers normally prefer to use single-tier architecture.
If the architecture of DBMS is 2-tier, then it must have an application through which the DBMS can be accessed. Programmers use 2-tier architecture where they access the DBMS by means of an application. Here the application tier is entirely independent of the database in terms of operation, design, and programming.
A 3-tier architecture separates its tiers from each other based on the complexity of the users and how they use the data present in the database. It is the most widely used architecture to design a DBMS.
- Database (Data) Tier − At this tier, the database resides along with its query processing languages. We also have the relations that define the data and their constraints at this level.
- Application (Middle) Tier − At this tier reside the application server and the programs that access the database. For a user, this application tier presents an abstracted view of the database. End-users are unaware of any existence of the database beyond the application. At the other end, the database tier is not aware of any other user beyond the application tier. Hence, the application layer sits in the middle and acts as a mediator between the end-user and the database.
- User (Presentation) Tier − End-users operate on this tier and they know nothing about any existence of the database beyond this layer. At this layer, multiple views of the database can be provided by the application. All views are generated by applications that reside in the application tier.
Multiple-tier database architecture is highly modifiable, as almost all its components are independent and can be changed independently.
The components of MySQL are the MySQL server, MySQL clients and utilities, SQL and MySQL data directory. MySQL server is the hub of MySQL that performs all manipulation of databases and tables. MySQL clients and utilities help in communicating with the server. The server’s language, SQL, helps us to talk to the server in its own language. MySQL data directory is where the server stores its databases and status files.
In SQL, the client requests come into the server via SQL Server’s Net-Libraries. These requests are then schedules for processing via User Mode Scheduler (UMS). The language processing and execution (LPE) component within the server then takes each request and passes it to the query processor (QP) for optimization. Once a client query is optimized and an execution plan is produced for it, LPE executes it via calls from the relational engine to the storage engine (SE). The storage engine carries out the physical I/O, table and index traversal, data retrieval, and so on necessary to carry out the request from the relational engine. The communication between the LPE and SE components occurs via COM using calls to OLE DB interfaces.
The major components or Oracle are the files, memory structures and physical processes/threads. The files can be parameter, data, temp, control, and redo log files. The memory structures are referred to as the System Global Area (SGA), Process Global Area (PGA), and User Global Area (UGA). The processes could be server processes, background processes, and slave processes.
MySQL is open source architecture. It supports only partial support for Unicode whereas SQL and Oracle provide complete support for Unicode. MySQL has maximum row size of 64KB. The maximum column size is 4096. My SQL does not support intersection and parallel query. It does not support security certificate and table locking capability. It supports more types of tables. They are used mainly by web applications. One of its major disadvantages is that it lacks add-on possibilities. MySQL does not support user-defined functions, triggers, cursors, stored procedures, full join, and import/export capabilities. It has limited transaction support. It requires very less disk space and has poor recovery feature.
SQL is closed source software. SQL server has a maximum database size of 524258 terabytes. SQL server has unlimited row size. The maximum column size is 30000. SQL does not support list and hash function. SQL requires more disk space and supports extensive foreign key features. It has an efficient recovery mechanism.
Oracle is used by companies for enterprise products. Oracle has maximum row size of 8KB. The maximum column size is 1000. Oracle is an expensive software for higher end capabilities. Oracle has the auditing capability, better user management and better security. Oracle has row-locking feature. Supports optimized algorithms for concurrency, query and storage. Oracle supports advanced security features. Oracle provides more flexibility for programming languages.