3.3. Database Management Systems

Introduction

Creating and implementing the right database system ensures that the database will support both business activities and goals. Using the database management system it is possible to create, implement, use and update the database.

Databases range from those for a single user with a desktop computer to those on mainframe
computers with thousands of users.

• Personal databases
• Workgroup databases
• Departmental databases
• Enterprise databases

Personal databases are designed to support single user with a stand alone PC. MS Access is
commonly used to create personal databases.

Example:

• A sales person keeping track of this customer information with contact details
• Names of 50 relatives and friends to whom Christmas Cards are sent each year

Workgroup to enterprise databases target small to large user groups. These systems allow
hundreds of people to access the same database system at the same time. Oracle, Informix
and SQL Server are examples for this kind of databases.

All DBMS systems provide common functions.

Example: Providing a user view, physically storing and retrieving data in a database, allowing for
database modification, manipulating data and generating reports.

3.3.1 Overview of Database Types

Databases range from small, inexpensive packages to huge, expensive packages. Following are
some of the alternatives.

Flat File

This is a simple database program which has no relationship between its records and
normally used to store and manipulates a single table or file.

Example: Spreadsheet programs
Word processing programs

Single User

The databases are normally for personal computers. Only one person can use the
database at a time.

Example: Access
Quicken

Multiple Users

Large mainframe computers use multiple user databases. These are more powerful,
expensive systems which allow large number of people to access the database at the
same time.

Example: Oracle, Sybase, IBM

3.3.2. Providing a User view



The DBMS is responsible for access to a database. Among the first steps in installing and using a
database involves telling the DBMS the logical and physical structure of the data and relationships

among the data in the database. This description of the entire database is known as a schema.
Using the schema the DBMS can find where to access the requested data in relation to another
piece of data.

A DBMS also acts as a user interface by providing a view of the database. A user view is the
portion of the database a user can access. For the purpose of giving different user views different
sub schemas have been developed. A subschema is a file that contains a description of a subset
of the database and identifies which users can view and modify the data items in that subset. As a schema is a description of the entire database, a subschema shows only some records and their
relationships in the database. Normally programmers and the managers need to access only a
subset of the database.

Example: A sales representative might need only data describing customers in his region and not
the sales data for the entire nation. So the subschema is the customer data in his region.
Number of different sub schemas can be created according to the user requirement. On the other hand sub schemas provide security as the users are typically allowed to view only certain parts of the database.

3.3.3. Creating and Modifying the Database

Schemas and sub schemas are entered into the DBMS through a data definition language (DDL).
A DDL is a collection of instructions and commands used to define and describe data and data
relationships in a specific database.

Another important step in creating a database is to establish a data dictionary. Data dictionary is
a detailed description of all data used in the database. It contains the name of the data item, other names to describe the item, the range of values that can be used, the type of data, the amount of storage needed for the item, a notation of the person responsible for updating it and the various users who can access it and a list of reports that use the data item.
Some uses of the data dictionary are as follows.

• Provide a standard definition of terms and data elements.
• Assist programmers in designing and writing programs
• Simplify database notification
With the help of a data dictionary the database approach achieve following advantages
• Reduce data redundancy
• Increase data reliability
• Faster program development
• Easier modification of data and information

3.3.4. Storing and Retrieving Data

When an application program needs data, it requests that data through the DBMS. The process of a user or application requesting data is first done through a logical access path, and subsequently through a physical access path.

Example: To calculate the total price of an invoice the application program needs the unit price for each item purchased.

• The application program requests this data from the DBMS using a logical path.
• The DBMS working in conjunction with various system software programs, access a
storage device.
• The DBMS goes to the storage device to retrieve the data; it follows a path to the physical
location where the prices of these items are stored. In this example the DBMS may go to a
disk drive to retrieve the unit price data for each item purchased.

When two or more people or programs attempt to access the same record in the same database
at the same time, there can be a problem. Concurrency control can be used to avoid this
potential problem. According to this when one program is using or updating a record, all other
application programs’ access to that record is controlled through a locking mechanism.

3.3.5. Manipulating and Generating Reports



Once a DBMS has been installed, employees and managers can use it to generate reports and
obtain important information. It is possible to perform queries and other database tasks by opening windows and clicking on the data or features according to the requirement.
Database commands can be used in a programming language also.

Example:
COBOL commands can be used in simple programs that will access or manipulate
certain pieces of data in the database. In general, the commands that are used to manipulate the
database are part of the Data Manipulation Language (DML). The DML, with the DBMS allows database users to access, modify and make queries about the data that contained in the database.

SQL (Structured Query Language) is the data manipulation language for relational databases.
Once a database has been set up and loaded with data, it can produce desired reports,
documents and other outputs. By the output control feature a database program allows to select the records and fields to appear in reports.

A database program can produce a wide variety of documents, reports and other outputs that can help organization achieve their goals. The most common reports select and organize data to
present summary information about some aspect of company operations.
Database programs can produce literally hundreds of documents and reports.

Example: Form letters with address labels
Payroll checks and reports
Invoices
Orders for materials and supplies
A variety of financial performance reports

3.3.6. Database Administration

A database administrator (DBA) is a highly skilled systems professional who directs or performs
all activities necessary to maintain a successful database environment.

The DBA’s responsibilities include

• designing, implementing and maintaining the database system and the DBMS.
• establishing policies and procedures pertaining to the management, security, maintenance
(performance), use of DBMS (availability of data).
• training employees in database management and use.
A DBA must need to,
• have a clear understanding of the fundamental business of the organization.
• be proficient in the use of selected database management system.
• stay abreast of emerging database technologies and new design tools and approaches.
The DBA works with,
• users to decide the content of the database
• programmers as they build applications to ensure that their programs comply with
database management system standards and conventions.

Data Administration: A high-level function that is responsible for the overall management of
data resources in an organization, including maintaining corporate-wide definitions and
standards

The data administrator,

• is responsible for defining and implementing consistent principles for a variety of data
issues.
• works with business managers to identify who should have read and/or update access
to certain databases and to selected attributes within those databases.
• can be a high level position reporting to top-level managers.

3.3.7. Popular database management systems

According to the latest generation of database management systems the end users can build their own database applications. Using these tools the end users address how to manage a mounting pile of information on employees, customers, inventory or sales.
Like other software products, there are a number of commercial (SQL Server, DB2, Oracle,
Informix) and open-source (PostgreSQL and mySQL) database systems.
MySQL : This is the most popular open-source database management system.

3.3.8 Special–purpose Database Systems

In addition to the popular database management systems, there are some specialized database
packages used for specific purposes (document management) or in specific industries
(manufacturing).

Example: Summation and Concordance – used in law firms to organize legal documents.

CaseMap – Organizes information about a case
LiveNote – Display and analyze transcripts
Geographical Information Systems (GIS), Computer Aided Design (CAD), Statistical Databases,
etc. are kind of special purpose database systems.

3.3.9. Selecting a Database Management System

The database administrator often selects the best database management system for an
organization. The process begins by analyzing database needs and characteristics. The
information needs of the organization affect the type of data that is collected and the type of
database management system that is used.

The important features that have to consider when selecting a Database Management System are as follows.

• Database size
Database size depends on the number of records or files in the database. The size determines
the overall storage requirement for the database.
To maintain good performance and reduce costs companies are trimming the size of their
databases.

• Number of concurrent users
Number of simultaneous users that can access the contents of the database is also an
important factor. A database that is used by a large workgroup must be able to support a
number of concurrent users. If it cannot, then the efficiency of the user requests will be
lowered. To provide flexibility to the database, highly scalable DBMS is preferred by the
companies. Scalability describes how well a database performs as the size of the database or
the number of concurrent users increase.

• Performance
How fast the database is able to update records can be the most important performance
criterion for some organizations.

Example: Credit and airline companies must have database systems that can immediately
update customer records and check credit or make a plane reservation in seconds not
minutes. However payroll applications can be process once a week or less frequently and do
not require immediate processing. When an application demands immediacy, it also demands
rapid recovery facilities in the event that the computer system shuts down temporarily. Other
performance considerations include the number of concurrent users that can be supported and the amount of memory that is required to execute the database management program.

• Integration
A key aspect of any database is its’ ability to be integrated with other applications and
databases. A key determinant here is what operating systems it can run under – such as
Linux, UNIX or Windows. Some companies use several databases for different applications at
different locations.

• Features
The features of the database management system can also make a big difference. Most
database programs come with security procedures, privacy protection and a variety of tools.

• The vendor
The size, reputation and financial stability of the vendor is also an important aspect. Some
organisations would rely on vendor support to handle operational aspects of the system.

• Cost
Cost of a database system varies from few thousands to millions of rupees based on the
number of users and functionalities. In addition to the initial cost of the database package,
annual or monthly maintenance or operating costs should be considered. Subsequent
additional hardware and software upgrade cost could come up to cope with future data
volumes and performance issues.

3.3.10 Using Databases with other Software

Database management systems are used combined with other software packages or internet.
A DBMS may work as a front-end application or a back-end application.
Front-end application directly interacts with people or users.

Example: Marketing researches Back-end applications interact with other applications or programs, it only indirectly interacts with people or users

Example: Finding the details of the database books available in the university library
through the library web site.