Overview On Databases
What is a Database? :
A database is a systematic collection of data. Databases support storage and manipulation of data. Databases make data management easy. They do so through the use of tables. For example, a university database might contain information about the following:
• Entities such as students, faculty, courses, and classrooms.
• Relationships between entities, such as student’s enrolment in courses, faculty teaching courses, and the use of rooms for courses.
In computing, databases are sometimes classified according to their organizational approach. The most prevalent approach is the relational database, a tabular database in which data is defined so that it can be reorganized and accessed in a number of different ways. A distributed database is one that can be dispersed or replicated among different points in a network. An object-oriented programming database is one that is congruent with the data defined in object classes and subclasses. Computer databases typically contain aggregations of data records or files, such as sales transactions, product catalogs and inventories, and customer profiles.
Typically, a database manager provides users the capabilities of controlling read/write access, specifying report generation, and analyzing usage. Databases and database managers are prevalent in large mainframe systems, but are also present in smaller distributed workstation and mid-range systems and on personal computers. SQL (Structured Query Language) is a standard language for making interactive queries from and updating a database such as IBM’s DB2, Microsoft’s SQL Server, and database products from Oracle, Sybase, and Computer Associates.
Table & Record:
A table in a relational database is a predefined format of rows and columns that define an entity. Just like Excel tables, database tables consist of “columns and rows”. Each column contains a different type of attribute and each row corresponds to a single record. Each table is provided with a name. An example of a table called “student”:
What is DBMS? :
A database management system, or DBMS, is software designed to assist in maintaining and utilizing large collection of data. The need for such systems, as well as their use, is growing rapidly. The alternative to using a DBMS is to store the data in files and write application-specific code to manage it.
Using a DBMS to manage data has many advantages like
• Data independence
• Efficient data access
• Data integrity and security
• Data administration
• Concurrent access and data recovery
Types of DBMS: There are different types of DBMS, they are
Hierarchical – this type of DBMS employs the “parent-child” relationship of storing data. This type of DBMS is rarely used nowadays. Its structure is like a tree with nodes representing records and branches representing fields. The windows registry used in Windows XP is an example of a hierarchical database. Configuration settings are stored as tree structures with nodes.
Network DBMS – this type of DBMS supports many-to many relations. This usually results in complex database structures. RDM Server is an example of a database management system that implements the network model.
Relational DBMS – this type of DBMS defines database relationships in form of tables, also known as relations. Unlike network DBMS, RDBMS does not support many to many relationships. Relational DBMS usually have pre-defined data types that they can support. This is the most popular DBMS type in the market. Examples of relational database management systems include MySQL, Oracle, and Microsoft SQL Server.
Object Oriented Relation DBMS – this type supports storage of new data types. The data to be stored is in form of objects. The objects to be stored in the database have attributes (i.e. gender, ager) and methods that define what to do with the data. PostgreSQL is an example of an object oriented relational DBMS.
The most widely used DBMS is the relational model that saves data in table formats. It uses SQL as the standard query language. SQL is standard language used to query a database. The database approach has many advantages when it comes to storing data compared to the traditional flat file based systems.
What is RDBMS? :
RDBMS stands for Relational Database Management System. Relational Database Management System is a database system made up of files with data elements in two-dimensional array (rows and columns). This database management system has the capability to recombine data elements to form different relations resulting in a great flexibility of data usage. RDBMS is the basis for SQL, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.
It is a program that let you create, update, and administer a relational database. A relational database is a collection of data items organized as a set of formally-described tables from which data can be accessed or reassembled in many different ways without having to reorganize the database tables. The relational database was invented by E. F. Codd at IBM in 1970.
The relational model is the basis for a relational database management system (RDBMS). Essentially, an RDBMS moves data into a database, stores the data, and retrieves it so that it can be manipulated by applications. An RDBMS distinguishes between the following types of operations:
Logical operations: In this case, an application specifies what content is required. For example, an application requests an employee name or adds an employee record to a table.
Physical operations: In this case, the RDBMS determines how things should be done and carries out the operation. For example, after an application queries a table, the database may use an index to find the requested rows, read the data into memory, and perform many other steps before returning a result to the user. The RDBMS stores and retrieves data so that physical operations are transparent to database applications.
The following categories of the data integrity exist with each RDBMS:
Entity Integrity: There are no duplicate rows in a table.
Domain Integrity: Enforces valid entries for a given column by restricting the type, the format, or the range of values.
Referential integrity: Rows cannot be deleted, which are used by other records.
User-Defined Integrity: Enforces some specific business rules that do not fall into entity, domain, or referential integrity.
Overview on oracle:
Oracle is the world’s leading supplier of software for information management but it is best known for its sophisticated relational database products (notably Oracle9i), which are used in Fortune 1000 corporations and by many of the largest Web sites. Oracle’s relational database was the world’s first to support the Structured Query Language (SQL), now an industry standard.
Oracle Database is an RDBMS. An RDBMS that implements object-oriented features such as user-defined types, inheritance, and polymorphism is called an object-relational database management system (ORDBMS). Oracle Database has extended the relational model to an object-relational model, making it possible to store complex business models in a relational database.
An Oracle database is a collection of data treated as a unit. The purpose of a database is to store and retrieve related information. A database server is the key to solve the problems of information management. In general, a server reliably manages a large amount of data in a multiuser environment so that many users can concurrently access the same data. All this is accomplished while delivering high performance. A database server also prevents unauthorized access and provides efficient solutions for failure recovery.
Oracle Database is the first database designed for enterprise grid computing, the most flexible and cost effective way to manage information and applications. Enterprise grid computing creates large pools of industry-standard, modular storage and servers. With this architecture, each new system can be rapidly provisioned from the pool of components. There is no need for peak workloads, because capacity can be easily added or reallocated from the resource pools as needed.
Oracle versions history:
The current version of Oracle Database is the result of over 30 years of innovative development. The evolution of Oracle Database includes the following:
Founding of Oracle:
In 1977, Larry Ellison, Bob Miner, and Ed Oates started the consultancy Software Development Laboratories, which became Relational Software, Inc. (RSI). In 1983, RSI became Oracle Systems Corporation and then later Oracle Corporation.
First commercially available RDBMS:
In 1979, RSI introduced Oracle V2 (Version 2) as the first commercially available SQL-based RDBMS, a landmark event in the history of relational databases.
Portable version of Oracle Database:
Oracle Version 3, released in 1983, was the first relational database to run on mainframes, minicomputers, and PCs. The database was written in C, enabling the database to be ported to multiple platforms.
Enhancements to concurrency control, data distribution, and scalability:
Version 4 introduced multisession read consistency. Version 5, released in 1985, supported client/server computing and distributed database systems. Version 6 brought enhancements to disk I/O, row locking, scalability, and backup and recovery. Also, Version 6 introduced the first version of the PL/SQL language, a proprietary procedural extension to SQL.
PL/SQL stored program units:
Oracle7, released in 1992, introduced PL/SQL stored procedures and triggers.
Objects and partitioning:
Oracle8 was released in 1997 as the object-relational database, supporting many new data types. Additionally, Oracle8 supported partitioning of large tables.
Oracle8i Database, released in 1999, provided native support for internet protocols and server-side support for Java. Oracle8i was designed for internet computing, enabling the database to be deployed in a multitier environment.
Oracle Real Application Clusters (Oracle RAC):
Oracle9i Database introduced Oracle RAC in 2001, enabling multiple instances to access a single database simultaneously. Additionally, Oracle XML Database (Oracle XML DB) introduced the ability to store and query XML.
Oracle Database 10g introduced grid computing in 2003. This release enabled organizations to virtualize computing resources by building a grid infrastructure based on low-cost commodity servers. A key goal was to make the database self-managing and self-tuning. Oracle Automatic Storage Management (Oracle ASM) helped achieve this goal by virtualizing and simplifying database storage management.
Manageability, diagnosability, and availability:
Oracle Database 11g, released in 2007, introduced a host of new features that enable administrators and developers to adapt quickly to changing business requirements. The key to adaptability is simplifying the information infrastructure by consolidating information and using automation wherever possible.
Advantages of oracle:
Security and performance are the highlight of Oracle databases. There are numerous advantages associated with using Oracle databases.
Isolation: Oracle databases perform each transaction in isolation from others, which ensure higher level of security.
Durability: Once all the transactions are complete, the results are permanent and survive media failures.
Recycle bin option: The latest versions of Oracle databases have a recycle bin option. This works exactly like the Microsoft Recycling Bin.
Perfect for large data size: While MySQL may flutter when the data size goes up, Oracle is the ideal choice of database for such conditions.
Functionality: Oracle databases are used for practically all corporation level applications. Thus, Oracle databases dominate the banking industry. They provide a combination of high-level technology and integrated business solutions. This is a perfect for practically all corporations that have huge amounts of data to store and access.
Reliability: Another important advantage offered by Oracle databases are their reliability. Oracle is a database that delivers excellent performance when challenged with demanding tasks. The ACID test, which is an important tool used to ensure the integrity of data stored, was easily passed by Oracle databases. This test is important since reliable data storage is the main purpose of a database. Thus, Oracle databases have been proven to deliver high integrity of data storage.
Disadvantages of Oracle:
Some of its major disadvantages are:
Budget: Oracle databases are feasible only when you need large databases. With the amount of work and hardware needed to deploy, it is not ideal for small or mid-sized companies where other feasible options such as MySQL or Microsoft SQL can be more cost effective with similar functionality.
Complexity: Oracle databases are notorious for their complexity and therefore not the ideal choice when either the workforce isn’t highly technical or you want an easy-to-use database with basic features.
MS SQL Server:
Overview on SQL Server:
Microsoft SQL Server is a Relational Database Management System (RDBMS) designed to run on platforms ranging from laptops to large multiprocessor servers. SQL Server is commonly used as the backend system for websites and corporate CRMs and can support thousands of concurrent users. SQL Server comes with a number of tools to help you with your database administration and programming tasks. Microsoft SQL Server designed to create web, enterprise, and desktop database systems. It is used with various goals and at different levels.
MS SQL Server allows you to store large amount of data which handles components like video, photographs, numbers, text, and much more. Microsoft SQL Server is developed to manage terra bytes of data in comparison with Microsoft Access that can handle only 1 gigabyte of data.
SQL Server is much more robust and scalable than a desktop database management system such as Microsoft Access. Although SQL Server can also be run as a desktop database system, it is most commonly used as a server database system.
SQL Server versions history:
SQL 6.0/6.5 (1995): First version designed specifically for Windows NT. It supports Replication
SQL Server 4.2 (1992): Developed for Windows NT 3.1.
SQL Server 1.0 (1989): Developed by Microsoft, Sybase, and Ashton-Tate for OS/2.
SQL2000: it Focus on Performance and Scalability, XML support Data Mining, Reporting Services.
SQL Server 7.0 (1999): Restructure of Relational Server. It supports Data Transformation Services, Online Analytical Processing.
SQL2005: It supports High Availability(includes Database Mirroring),Security Enhancements, Integration Services, Developer Productivity and CLR, Native XML and Web services supports.
SQL2008/SQL2008 R2: SQL Server 2008 R2 which is used for the master planning of the database management system that will be referred as the data services which will add central management data entries and hierarchical consoles which will manages the multiple SQL Server 2008 instances and services using the relational databases and analysis services which will be finally used for the integration of the web services.
It also includes the number of new services that includes the Power Pivot for the Excel and SharePoint master data services for building the reports using the data tier application which will use as the utility for the visual studio that creates the utility control points for the multi-server users to manage the SQL Servers.
Advantages of SQL Server:
Replication is a technique used to create a full copy of the database on another server. Every time you insert, edit or delete a record on the SQL Server, the copy is also changed. If one of your database servers goes down, the copy can take over. This prevents long periods of downtime for the business, and it secures the database from data loss. Replication is also used to distribute the resources for the database server.
Triggers edit, insert, delete or select data from tables after an action occurs. This reduces the amount of programming needed in stored procedures. Triggers also make sure the data is always edited each time the action occurs. It reduces the chance of a stored procedure failing, creating data corruption.
Database drivers included with the SQL Server application allow any operating system platform to communicate with the server. Data drivers also allow the administrator to export and import data from different applications such as Access, Excel, text files and even other database servers such as Oracle or MySQL.
Database information can contain sensitive information for customers and the business. For this reason, security options are also available. Administrators can place security on specific objects, so users can select data from certain tables, and they are blocked from retrieving data from other areas of the database server. It can also disallow deletion of records, which causes data corruption if it is not executed properly.
Disadvantages of SQL Server:
Microsoft SQL Server is made to support millions of records across an enterprise. Because of its robust technology, it also comes with a high price. Enterprise editions of SQL Server can cost thousands of dollars, depending on the options and equipment needed to host the database application. For most small businesses, this price is too high and doesn’t fit the budget. For this reason, many small companies use MySQL for a database back-end. MySQL is free for the individual and it is widely known by Web and desktop programmers.
Microsoft SQL Server uses a core language, which is different from other database applications such as MySQL and Oracle. Companies that need programming or analysis services may find it more difficult to find a resource that hosts these services at a reasonable price. Programmers are also required to learn a different language and setup for the Microsoft SQL Server environment, so companies that want to upgrade need to teach current employees how to work with the application.
Older versions of SQL Server may install on older hardware equipment, but newer releases of the application require more advanced technologies to support the resources needed by the database. If the company plans to have a very large database, the hard drive also needs the appropriate amount of space in addition to the gigabytes of space required for the database engine alone.
Overview on MYSQL Server:
MySQL is one of the popular Relational Database Management System. The MySQL server provides a database management system with querying and connectivity capabilities, as well as the ability to have excellent data structure and integration with many different platforms. It can handle large databases reliably and quickly in high-demanding production environments. The MySQL server also provides rich function such as its connectivity, speed, and security that make it suitable for accessing databases.
The MySQL server works in a client and server system. This system includes a multiple-threaded SQL server that supports varied back ends, different client programs and libraries, administrative tools, and many application programming interfaces (API) s.
MYSQL actually supports most of the functionality you’ve come to expect in a commercial RDBMS. It ensures that transactions comply with the ACID Model, allows the building of indexes, supports standard data types, and allows for database replication, among other features. One major area where MySQL currently falls short is its lack of support for stored procedures and triggers.
MYSQL Server versions history:
• The software company MySQL AB created the original MySQL in 1995.
• In 1999, they release version 3.23.0, the first significant version.
• In October 2001, MySQL AB released version 4.0.0 and version 4.1.0 in April 2003. They released version 5.0.0 in December 2003.
• Version 5.5.x was released in December 2009.
In 2000, MySQL went Open Source, allowing users to enhance the functionality of the system based on their own needs. In 2005, the MySQL Network subscription service was launched, providing users with updates, notifications and product-level support. In 2008, Sun Microsystems purchased MySQL AB.
Advantages of MYSQL Server:
MYSQL is one of the top databases available in the market. MYSQL is a relational database with many advanced features and options. Over time, MYSQL has proved itself to be a fast, reliable and cost effective competitor to the other more expensive databases like MS SQL Server and Oracle. Some of the advantages of using MYSQL in database development are
MYSQL is an open source database system which means that anyone can use it for free. Developers can amend its code to suit their requirements which means that MYSQL is highly customizable as well. Another edge of using MYSQL over other database systems is that; it is available widely in the market with no ownership cost.
A lot of people around the globe are continuously developing new modules for integration with MYSQL. This means that it has a wider and faster development circle. Patches, upgrades and fixes are developed fast and become available in forums, blogs and developer sites on the internet.
Better for Small Businesses:
This relational database system is free so it reduces the cost of overall database solution for small businesses and companies. This database is relatively easy to learn and operate, so operational cost is reduced substantially which is again an important factor in classifying MYSQL as an applicable and practical tool for small businesses.
MYSQL as a relational database is secure as all access passwords are stored in an encrypted format restricting any unauthorised access to the system.
MYSQL clients can access this relational database through standard TCP/IP sockets, named pipes, UNIX sockets and many more. Standard ODBC 2.5 and above functions and commands are also supported in MYSQL.
Disadvantages of MYSQL Server:
• MySQL does not support a very large database size as efficiently
• MySQL does not support ROLE, COMMIT, and Stored procedures in versions less than 5.0
• Transactions are not handled very efficiently.
IBM DB2 Server
Overview on IBM DB2:
IBM’s DB2 is a ‘Relational Database Management System’ (RDBMS), where the ‘database’ contains the data you want to work with, the ‘management’ is what you want to do to the data, and the ‘system’ means that DB2 is a collection of computer software programs that perform this particular type of task. DB2 is designed to make the storage and analysis of data easier.
DB2 can be used to manage a wide variety of data, such as the results of research experiments, records of purchases for agency administrators, or the locations and serial numbers of pieces of equipment. DB2 is especially effective at handling data that need to be stored securely, data that need to be manipulated and analyzed interactively, and data that are the basis for one-time and recurring reports. Data input, data update, data storage, data security, data extraction and analysis, and report generation are all facilities of DB2. In DB2, data is stored in the form of tables (relations) comprised of rows (records) and columns (fields). All access to and manipulation of data in DB2 is accomplished via Structured Query Language (SQL).
IBM DB2 versions history:
In 1996, IBM announced DB2 Universal Database (UDB) Version 5 for distributed platforms. With this version, DB2 was able to store all kinds of electronic data, including traditional relational data, as well as audio, video, and text documents. It was the first version optimized for the Web, and it supported a range of distributed platforms.
After five years of development, the effort of 750 developers, architects, and engineers paid off with the release of the first hybrid data server in the market: DB2 9. DB2 9, available since July 2006, is a hybrid (also known as multi-structured) data server because it allows for storing relational data, as well as hierarchical data, natively. With DB2 9’s pureXML technology, XML documents are stored internally in a parsed hierarchical manner, as a tree; therefore, working with XML documents is greatly enhanced.
In 2007, IBM has gone even further in its support for pureXML, with the release of DB2 9.5. DB2 9.5, the latest version of DB2, not only enhances and introduces new features of pureXML, but it also brings improvements in installation, manageability, administration, scalability and performance, workload management and monitoring, regulatory compliance, problem determination, support for application development, and support for business partner applications.
Advantages and Disadvantages of IBM DB2:
Simplicity application development:
An improved set of application tools eases application employment and simplifies database developments, including improved functionality for Visual Studio 2004 and new Developer Workbench. In addition, it has a rapid deployment application and many other features which allow you to develop more improved applications that can work across several DB2 servers.
Uses Java technology:
Also, DB2 allows significant developments to the DB2 driver for SQLJ and JDBC have been created to keep enthusiast developers on the frontier of Java technology. These enhancements simplify the challenge associated with programming by adding two-phase commit abilities for multi-vendor information sources.
Reduce administration costs:
With new improved features like response file installation developments, DB2 connect allows you to support your business needs rather than deploying database system. On the other hand, autonomic features like memory allocation reduce the time needed to tune and administer your database system.
Provides application enablement:
DB2 connects is intended to leverage your business information. For those companies that have DB2 on IBM servers, DB2 provides application enablement, and highly robust communication substructure for connecting Windows, Web, Linux and so forth. It has new feature enhancements that help to improve programming challenges.
Improved auditing and security:
The system provides a secure and safe environment but new regulations necessitate the use of new application features within the system software. It offers more improved development to enhance audit ability and security in key areas.
Apart from the numerous benefits, DB2 also has its share of disadvantages as well. For starters, it is expensive and requires complicated licensing.