Relational Database Design Clearly Explained / Edition 1

Chapter 11: Database Design Case Study #3: Independent Intelligence Agency

In the preceding two case studies, we have encountered several database design challenges, including the need to determine whether one or more databases are required, incomplete specifications, and a large number of many-to-many relationships. The final case study we are going to consider presents two different challenges: First, the specifications have been written by people who are more concerned with security than they are with helping database designers; second, the environment contains an enormous number of repeating groups. In addition, this is the largest database you have seen so far. Although at the outset it may appear that there are two or three separate databases, the parts of the database share just enough data that the only way to meet all the organization's requirements is to maintain a single schema.

Note; The cases in the two preceding chapters have to some extent been based on real organizations, However, this case is a pure flight of fancy (The author had been watching too many spy movies when she wrote it!)

Organizational Overview

A group of database designers have been hired to provide updated information systems for the Independent Intelligence Agency (IIA). Given the nature of the agency's work, the designers realize that they will, in some cases, be working with limited information about the way in which the organization functions. In particular, the design team must accept specifications in whatever format they are provided; team members are not permitted to identify or question agency personnel other than the Vice President for InformationServices, who has been their sole contact with the organization. In addition, they will be given few details about the application programs that will be interacting with the database they design.

The Independent Intelligence Agency, headquartered in Geneva, is a nonaligned, worldwide organization that specializes in the gathering and dissemination of covert intelligence. Established during the aftermath of World War 11 (1947), the IIA is quick to emphasize that while it employs undercover field agents and uses other methods common to intelligence agencies, it has not, is not, and will not be involved in manipulating or in any way influencing the affairs of any country; its sole purpose is to gather information and to sell it to whoever is willing to pay. The IIA will accept commissions to gather specific intelligence as long as doing so does not violate the restriction on becoming active in the affairs of governments. All of IINs financial resources come from fees paid by clients for information and from investments made with those fees. All transactions are strictly confidential; unauthorized disclosure of information by any employee is cause for immediate dismissal.

The IIA's headquarters building in Geneva contains offices for the Executive Director, Internal Affairs (responsible for monitoring the conduct of field agents, readers, and administrative personnel), Human Resources, and Finance. Because space in the headquarters building is limited, Information Systems has been moved to a renovated warehouse behind the headquarters.

IIA's organization divides the planet into six bureaus, one for each continent except Antarctica. (Any intelligence that comes out of the research stations in Antarctica is handled through one of the northern Canadian stations.) Within the bureaus there are separate stations that conduct field operations. Each bureau has a Director, and each station has a Chief who reports to his or her bureau Director. (Note that the central offices for the European Bureau and the Geneva station are on the other side of the city. This arrangement was created to give the Director of the European Bureau and the Chief of the Geneva station the freedom to operate without the constant surveillance of top-level management.)

Stations have three types of people with whom they interact:

• Field agents are actively involved in the collection of intelligence. They are typically full-time employees of the IIA.
• Readers work inside station buildings reviewing print, audio, video, and electronic materials. Much of a reader's work involves intercepting and decoding international cable and satellite transmissions. Readers are typically full-time employees of the IIA.
• Informants are people who are contacted by field agents for information. Informants are usually paid based on the perceived value of the information they provide. They are not IIA employees. Some informants have become so valuable to the IIA, however, that they have been hired as field agents.

Occasionally field agents and readers will exchange roles. For example, a field agent who has spent too long in the field may become a reader and work at that job until retirement. By the same token, a promising reader may undergo field agent training and move into that role.

Stations also have administrative personnel. Although many administrative personnel have little contact with intelligence data or client lists, the security requirements for those employees are as rigorous as for field agents and readers.

Each station has its own budget and manages its own accounting. The Finance Department works at the top organizational level to determine the budget, but once funds are allocated, each station handles the ordering of its own supplies, pays its own expenses (including payments to informants), and cuts its own paychecks. Sales of information may be handled by any level in the organizational hierarchy. Payment is received by the agency making the sale, but all funds are deposited in a single, centralized account in Geneva. The Finance Department's auditors and Internal Affairs staff keep close watch to ensure that all funds collected end up in the bank.

Current Information Systems

IIA maintains a distributed file processing system using ISAM file organization that has been in place since 1974. Each station has its own minicomputer or server that is used to handle the station's accounting functions. These data are available to the Finance Department at the Geneva headquarters building, the station's bureau, and the station's area. Personnel files are kept on the Geneva headquarters' mainframe. The data are available to all stations.

The personnel files, however, contain data about IIA employees only. Each field agent keeps his or her own list of informants. In some cases, those lists have been placed on station computers, but the use is not consistent and in many cases, agents have been reluctant to share their sources.

Data gathered by field agents are stored in the file processing system. The files containing these raw facts are then indexed to allow retrieval by major topics -countries, individuals, and events. An online query language is available, but attempts to retrieve by a characteristic on which there is no index are unacceptably slow.

The IIA does not sell raw facts, but instead sells verified pieces of intelligence. To obtain a sellable piece of intelligence, the IIA must receive confirmation of the same raw fact from several sources. When a field agent or reader reports raw data, he or she also reports a confidence level (from 0 to 100) in the accuracy of that data. Printouts of sorted raw data along with their confidence levels are then examined by readers to find data that support each other with increasing confidence levels. When the confidence levels reach a specific point-IIA will not reveal exactly what that level is-a sellable piece of intelligence is entered into the data file containing sellable merchandise. The reader certifying the piece of intelligence affixes an approximate price, but the exact selling price will be negotiated when the intelligence is actually sold. Of all the tasks that the IIA undertakes, this is the most labor-intensive and delays in certifying sellable intelligence can cost the IIA a significant amount in sales.

The IIA has informed the database designers working on a new information system that application programmers will be preparing an expert system to automate the verification of raw data into sellable pieces of intelligence. Although the database designers will not be given any details on how the expert system will work, they will be told exactly what data the expert system will need to function.

The file processing system has become difficult to maintain. The volume of data added to the files is so high that the station IS staff must reblock the files as often as once every two weeks. The reblocking is time consuming and results in unacceptable downtime during working hours.

Security Concerns

Many of IIA's clients are extremely sensitive to the exclusiveness of the data they are buying: They wish to be the only purchasers. Exclusive sales command much higher fees than those for pieces of intelligence that can be sold to more than one buyer.

Some data are also extremely sensitive. The IIA believes that the release of those data to the public would violate the IIA!s policy against manipulating or influencing existing governments. Therefore, the security of the intelligence data is of primary concern to the IIA.

The ISAM file system they are currently using has no built-in security. Instead, each record of data added to the files contains a field with a security classification. Each user name that the computer recognizes has a security classification as well. Access to data is granted if the user has a classification equal to or above the classification of the data. The classification scheme is an all-or-nothing affair. Anyone who can supply a user name and password that the computer will recognize receives the classification level of the user name and can read all data at that level or below and can modify all data at that classification level. Since users can modify only data at their classification level, highly privileged users who need to modify data at lower classification levels will have more than one account on the system, once for each classification level below them.

The need for a single user to maintain more than one account has led to numerous security breaches. Users who could not remember all of the user names, passwords, and their associated classification levels have written the information down and either taped it to their monitors in full view of an entire office or placed it in the top center drawer of a desk that is never locked. IIA would therefore like a security system where access can be more tightly tailored to an individual's needs so that only a single account is needed for each user.

The IlA's VP for Information Services has told the design team that the IIA is willing to install retina scanners for user identification in all locations. Portable scanners are also available to be issued with laptop computers....