Database Architecture and Administration - Essay Example

Database Architecture and Administration Evolution of Database Models The aim of this essay is to present an evolutionary history of various Database Models over the four decades of their existence. The essay attempts to list the strengths and weaknesses of each model and discuss the causes that led to their development. While databases have been used since the beginning of electronic computing, the earliest databases were custom made for organizations, difficult to operate and extremely inefficient. These file-based systems needed specific, custom-built programs to access and manage it. Since each file was isolated from others, it often resulted in redundancy and duplication of data. This took up a lot storage space and made the systems inefficient. As computers developed and became more and more efficient, the database systems also grew with the changing environment. The first multipurpose DBMS became commercially available in the 1960s and the first standardised DBMS was developed in 1971 by the “Database Task Group.” Navigational Model The navigational Model encompasses the “network model” and “hierarchical model” of database interfaces. The network model was developed by Charles Bachman in 1971 to define and set a standard for database systems. This model had a collection of records, connected to each other through links. Various record types were interconnected to each other, thus allowing a many-to-many relationship. IDS and IDMS are both examples of the network model. A network model can be explained using this schematic diagram. In the above example, employee Tom is assigned to sales department, Dick and Harry to Marketing department and John to Production department. In hierarchical model, the data is organized in a tree like structure so that each parent can have one or more children, but each child can have only one parent. Thus hierarchical model has a one-to-many relationship. IMS is an example of hierarchical database model. It was designed by IBM in 1966. A hierarchical model is schematically represented by a tree-structure diagram as follows: As seen in the above example, all sets of records are organized to originate from a single node. This tree like structure can result in replication of the same record in several different locations, resulting in wastage of space and inefficiency. Navigational databases are open-ended and are best suited to handle small-scale data. However, it is difficult to search for data in this system since it does not have the search functionality. The system had to be navigated “manually”, that is to reach a particular record, one had to start at the parent record and then navigate step-by-step until one reaches the desired record. Due to these limitations, navigational database models became outdated by 1980s, though a form of the hierarchical model is still used in XML applications. Relational Model In 1970, Edgar Codd, in an attempt to overcome the limitations of the navigational model, published a paper on the Relational Model. Relational model based Database Management Systems (DBMS) first became commercially available in the 1980s. By early 90s, RDBMS became the dominant database management system. The commercially available RDBMS software includes ORACLE, Sybase, Microsoft SQL server etc. The main distinguishing feature of the relational model is that the data is organized in a tabular form in rows and columns. The rows list the data while columns list the attributes. A key is chosen that uniquely identifies every row in the table and links different records from different tables. To find a particular set of data, a “query” is generated which mines the entire database to return the required answer. Structured Query Language (SQL) is the most commonly used query language. The relational model can be explained using the following example: Emp_ID Emp_name Emp_age Emp_salary Department 1234 Tom 25 25000 Sales 1235 Dick 30 30000 Marketing 1236 Harry 32 30000 Marketing 1237 John 29 20000 Production Figure 3: A simple example of Relational Model The main limitation of the RDBMS is that it only supports simple data involving strings, integers etc. However, when a database of complex data is required, the RDBMS proves inadequate. To overcome this problem Object Oriented Database Management System was developed. Object-oriented Model The Object Oriented Model was first developed in 1980s. OODBMS stores data as objects. Examples of such complex data objects include multimedia files, CAD drawings etc. OODBMS defines class for each object and the data is manipulated using the methods for this class. It has a hierarchical organization and therefore a navigational style of programming is required to access the data. OODBMS has limited usage in engineering and media databases. It has also developed its own query language known as the Object Query Language (OQL). Object-oriented model is used in the Versant software. Object-relational Model Object-relational Model attempts to bring together the object oriented database and relational database models. It has the easy-to-manage features of the relational method and the flexibility of the object-oriented model. It maintains the data in a tabular form but allows entries of complex data type known as Abstract Data Type (ADT). The first commercial products based on this model appeared in the mid 1990s. ORDBMS software include Informix, ObjectStore, Oracle etc. DDBMS Distributed Database Management System (DDBMS) manages logically interrelated collection of shared data, physically distributed over a network system. Large computer networks promote de-centralization to help better mange the network. The database may be organized into smaller units which store data close to where it is most frequently used. The DDBMS synchronizes the data periodically. Each unit is managed by its own DBMS locally. This improves data access and data processing and reduces operating costs. It also reduces the danger of a system wide crash. DDBMS are used by large companies which have several offices spread over different geographical locations. Relational Algebra Relational algebra, first defined by Edgar Codd in his paper Relational Model of Data published in 1970, is a mathematical system which uses a set of operations to manipulate relations defined by the relational model of databases. RA formally describes how a relational database operates and is the basic mathematics behind Structured Query Language (SQL). RA Operators RA operations can be divided into two groups, set theory based operations and special database operations. Together there are eight operators: UNION, INTERSECTION, PRODUCT, DIFFERENCE, SELECT, PROJECT, JOIN and DIVIDE. Examples of RA Operators UNION: Union-operators can only be used on tables which are union compatible, i.e. is they have identical columns and domain. Union of two tables returns a table that contains all the records that occur in either table. For example: Table A: Table B: Emp_name Emp_age John 32 Nick 25 Tom 29 A U B: Emp_name Emp_age Tom 29 Dick 30 Harry 35 John 32 Nick 25 INTERSECTION: Intersection of two tables yields a table that contains records common to both the table. The two table need to be compatible. Table A: Table B: Emp_name Emp_age John 32 Nick 25 Tom 29 A ∩ B Emp_name Emp_age Tom 29 DIFFERENCE: This operator yields records found in one table but not found in the second table. The tables need to be type compatible. Table A: Table B: Emp_name Emp_age John 32 Nick 25 Tom 29 A – B Emp_name Emp_age Dick 30 Harry 35 PRODUCT: Also known as the Cartesian Product, it yields all possible records from the two tables. The attributes of both the tables appear in the resultant table. For example: Table A: Emp_id Emp_name Emp_age 1234 Tom 29 1235 Dick 30 1236 Harry 35 Table B: Emp_id Emp_salary 1234 30000 1235 32000 1236 25000 A X B Emp_id Emp_name Emp_age Emp_id Emp_salary 1234 Tom 29 1234 30000 1234 Tom 29 1235 32000 1234 Tom 29 1236 25000 1235 Dick 30 1234 30000 1235 Dick 30 1235 32000 1235 Dick 30 1236 25000 1236 Harry 35 1234 30000 1236 Harry 35 1235 32000 1236 Harry 35 1236 25000 SELECT: This operator yields values of all the records in a table. It is a horizontal subset of a table. PROJECT: This operator yields all the values for a selected attribute. It is a vertical subset of a table. DIVIDE: This operator is used to solve queries involving “all” or “every”. It divides a two column table by a one column table. The result includes only the uncommon column and only the values shared by all tuples. For example: Table A Table B1 Colour Blue Green Table B2 Colour Blue A/B1 Sno 1234 A/B2 Sno 1234 1237 JOIN: This operator is used to join two or more tables and allows the linking of independent data through common attributes. There are several types of Join operators including: Natural JOIN Equi JOIN Outer JOIN An example of JOIN operator is given below: Table R Table S R l> Read More