Data Management Using Microsoft SQL Server

RDBMS Concepts

1.1 Introduction
Organizations often maintain large amounts of data, which are generated as a result of day-to-day
operations. A database is an organized form of such data. It may consist of one or more related data items
called records. Think of a database as a data collection to which different questions can be asked. For
example, 'What are the phone numbers and addresses of the five nearest post offices?' or 'Do we have
any books in our library that deal with health food? If so, on which shelves are they located?' or 'Show
me the personnel records and sales figures of five best-performing sales people for the current quarter,
but their address details need not be shown'.
1.2 Data and Database
Data means information and it is the most important component in any work that is done. In the
day-to-day activity, either existing data is used or more data is generated. When this data is gathered and
analyzed, it yields information. It can be any information such as information about the vehicle, sports,
airways, and so on. For example, a sport magazine journalist (who is a soccer enthusiast) gathers the
score (data) of Germany's performance in 10 world cup matches. These scores constitute data. When
this data is compared with the data of 10 world cup matches played by Brazil, the journalist can obtain
information as to which country has a team that plays better soccer.
Information helps to foresee and plan events. Intelligent interpretation of data yields information. In the
world of business, to be able to predict an event and plan for it could save time and money. Consider an
example, where a car manufacturing company is planning its annual purchase of certain parts of the car,
which has to be imported since it is not locally available. If data of the purchase of these parts for the last
five years is available, the company heads can actually compile information about the total amount of
parts imported. Based on these findings, a production plan can be prepared. Therefore, information is a
key-planning factor.
A database is a collection of data. Some like to think of a database as an organized mechanism that has
the capability of storing information. This information can be retrieved by the user in an effective and
efficient manner.
A phone book is a database. The data contained consists of individuals' names, addresses, and telephone
numbers. These listings are in alphabetical order or indexed. This allows the user to reference a particular
local resident with ease. Ultimately, this data is stored in a database somewhere on a computer. As
people move to different cities or states, entries may have to be added or removed from the phone book.
Likewise, entries will have to be modified for people changing names, addresses, or telephone numbers,
and so on.

Thus, a database is a collection of data that is organized such that its contents can be easily accessed,
managed, and updated.
1.3 Data Management
Data management deals with managing large amount of information, which involves both the storage
of information and the provision of mechanisms for the manipulation of information. In addition, the
system should also provide the safety of the information stored under various circumstances, such as
multiple user access and so on.
The two different approaches of managing data are file-based systems and database systems.
1.3.1 File-based Systems
Storage of large amounts of data has always been a matter of huge concern. In early days, file-based
systems were used. In this system, data was stored in discrete files and a collection of such files was
stored on a computer. These could be accessed by a computer operator. Files of archived data were
called tables because they looked like tables used in traditional file keeping. Rows in the table were called
records and columns were called fields.
Conventionally, before the database systems evolved, data in software systems was stored in flat files.

An example of the file-based system is illustrated in table
First Name Last Name Address Phone
Eric David [email protected] 213-456-0987
Selena Sol [email protected] 987-765-4321
Jordan Lim [email protected] 222-3456-123

Disadvantages of File-based Systems
In a file-based system, different programs in the same application may be interacting with different
private data files. There is no system enforcing any standardized control on the organization and
structure of these data files.
Data redundancy and inconsistency
Since data resides in different private data files, there are chances of redundancy and resulting
inconsistency. For example, a customer can have a savings account as well as a mortgage
loan. Here, the customer details may be duplicated since the programs for the two functions
store their corresponding data in two different data files. This gives rise to redundancy in the
customer's data. Since the same data is stored in two files, inconsistency arises if a change
made in the data of one file is not reflected in the other.
Unanticipated queries
In a file-based system, handling sudden/ad-hoc queries can be difficult, since it requires
changes in the existing programs. For example, the bank officer needs to generate a list of all
the customers who have an account balance of $20,000 or more. The bank officer has two
choices: either obtain the list of all customers and have the needed information extracted
manually, or hire a system programmer to design the necessary application program. Both
alternatives are obviously unsatisfactory. Suppose that such a program is written, and several
days later, the officer needs to trim that list to include only those customers who have opened
their account one year ago. As the program to generate such a list does not exist, it leads to
a difficulty in accessing the data.
Data isolation
Data are scattered in various files, and files may be in a different format. Though data used by
different programs in the application may be related, they reside as isolated data files.
Concurrent access anomalies
In large multi-user systems, the same file or record may need to be accessed by multiple
users simultaneously. Handling this in a file-based system is difficult.
Security problems
In data-intensive applications, security of data is a major concern. Users should be given
access only to required data and not to the whole database.

For example, in a banking system, payroll personnel need to view only that part of the
database that has information about the various bank employees. They do not need access
to information about customer accounts. Since application programs are added to the system
in an ad-hoc manner, it is difficult to enforce such security constraints. In a file-based system,
this can be handled only by additional programming in each application.
Integrity problems
In any application, there will be certain data integrity rules, which need to be maintained.
These could be in the form of certain conditions/constraints on the elements of the data
records. In the savings bank application, one such integrity rule could be 'Customer ID, which
is the unique identifier for a customer record, should not be empty'. There can be several
such integrity rules. In a file-based system, all these rules need to be explicitly programmed
in the application program.
Though all these are common issues of concern to any data-intensive application, each application
had to handle all these problems on its own. The application programmer needs to bother not
only about implementing the application business rules but also, about handling these common
issues.