A database management system (DBMS) is a computer-based, record-keeping system used for storing and maintaining information that helps users in creating, modifying and efficiently accessing records. Various examples of database applications are railway reservation systems, inventory management systems, payroll management systems, etc.
We saw in the last edition of Login…. Tribune how the electronic systems of maintaining databases scored over the age-old manual systems due to convenient data-entry procedures, secure storage, and precise and fast data retrieval. In this second and concluding article we will look into the various types and models of databases.
Components of database
and meaningful facts that can be recorded and from which a conclusion
can be drawn. For example, names and addresses in a telephone
directory or the inventory list of a factory are all databases.
Different users can access any particular part of data at the same
time and use it for different purposes according to their needs.
Hardware: Hardware consists of input/output devices and secondary storage devices like disc drives, magnetic disc, etc. The processor and main memory helps users in the execution of programs.
Users: There are three types of users. 1) Application programmers — who write programs that use the database. 2) End-users — those who make use of application programs or query language for accessing a database. 3) Database administrator — one who is the most familiar with the database and is responsible for creating, modifying and maintaining databases, defines authorisation checks, strategies for backup and recovery.
Languages: Some of the facilities provided by a DBMS used for interaction with the database are: Language to create, use and maintain the database, utilities for report generation, backup and recovery. Users interact with the data using a data sub-language (DSL) that has two parts — data definition language (DDL), which gives the definition or description of the database objects, and the data manipulation language (DML) for retrieving, updating or deleting data, or adding new data.
All these are combined into one (SQL) structured query language. This is a standardised query language for requesting information from a database. The original version called SEQUEL (Structured English Query Language) was designed by an IBM research centre in 1974-75. SQL was introduced as a commercial database system in 1979 by Oracle Corporation.
Flat-file database: A relatively simple database that is contained in a single table and is used for small applications.
Relational database system: Contained in multiple tables and used for large applications. It is also called a multidimensional database system. A relational database is powerful because it requires few assumptions about how data is related or how it is to be extracted from the database. One of the most powerful features of a relational database is that it can return a complete result set in response to a single database query. This feature of the relational database makes it the first choice for utilisation in a client/server environment. The standard query language utilised to access relational database is SQL.
Distributed database: Consists of two or more data files located at different sites on a computer network. Because the database is distributed, different users can access it without interfering with one another.
The three-level architecture model of DBMS called ANSI/SPARC (American National Standard Institute/Standard Planning and Requirement Committee) consists of the external level, conceptual level and the internal level. The view of each of these levels is described by a scheme (which is a plan that describes the records and relationship existing in a view). The idea behind a layered approach is to minimise the effects of changes occurring at any one level on the other levels.
External or user view: This is what a user sees. At this level only parts of the database that are of concern to a user or application program are included. Each external view is described by a scheme called external schema that consists of a definition of logical records and their relationship in the external view. Any number of views may exist for a given external view. At the external level, a change in one user’s view of the database need not affect other users.
Conceptual view or global view: At this level all database entities and the relationship among them are included; to that extent it is an overall view. This view represents the entire database. Only one conceptual schema per database exists. At the conceptual level, adding in more data does not affect either the existing external views or internal data storage.
Internal view: This is the lowest level of abstraction, which is closest to the physical storage method used. Internal schema contains the definition of the stored record, method of representing data fields and the access methods. At the internal level, changing a storage device does not affect either the conceptual level or external level.
The client/server architecture of a database system consists of two parts — the server that acts as backend and the client that acts as the frontend which uses various applications. Applications may be divided into two categories: user-written applications and vendor-provided applications. Some of the vendor-provided applications are spreadsheets, application generators, CASE tools, etc.
Databases need to structured to give flexibility; the chosen structure should reflect the natural relationships occurring in the real world. The database model is a tool for representation of data in logical form. Models allow us to limit the complexity of the problem, while maintaining its essential characteristics. The different database models are hierarchical, network and relational.
Hierarchical: There is a superior subordinate relationship in a hierarchical structure. Below the single root level, data components are subordinate elements or nodes each of which, in turn, owns one or more elements. A parent-child relationship is there.
Network: This model is a superset of the hierarchical model. It permits the connection of the elements or nodes in a multi-directional manner. This is also known as the CODASYL model. In this model elements or nodes can have many subordinate elements or nodes and can have one or more owners.
Relational: The relational model is based on the idea of a table, called a relation, where all data items included within the table are strongly associated. Any relationship between two entities is contained in a separate relation table. Proposed in 1968 by E. F. Codd, it is now the most common model used. Its examples are INGRES, ORACLE, and DB2, all of which offer an SQL interface.