Page 623
Page 624
There are several steps involved in developing an effective database design. As with all types of applications, the process begins with requirements analysis. In terms of relational database design, this phase answers questions regarding what data elements must be stored, who will access them, and how.
The second step is to define the logical database. This phase does not deal with how the data will be stored physically, but with how information is grouped logically. The requirements are translated into a model that provides a level of abstraction from the physical database, representing data in terms of business entities and relationships, rather than in terms of tables and columns .
Physical design is the final phase, in which individual data elements are given attributes and defined as columns in tables. This phase also deals with performance considerations relating to the creation of indexes, rollback segments, temporary segments, and the physical layout of data files on disk. DDL (Data Definition Language) scripts are written to create database objects and to be used for capacity planning.
A simple contact manager will be used as a sample application throughout this chapter, illustrating the concepts and techniques presented for each phase of the design process.
System requirements are typically gathered through a series of interviews with the end users. This is an iterative process in which systems designers provide structure to the ongoing dialog and document findings, and solicit feedback from the users. Although requirements definition is not normally considered part of the design process, the design is driven by the requirements, and the two processes often overlap. For example, the logical model might bring out new requirements that were not recognized in the earlier phases of analysis. It is important, however, to identify all requirements before developing a physical design, because capacity planning and hardware purchasing decisions are ineffective without a full understanding of system requirements.
A common technique used to define and document database requirements is to develop a data dictionary. As the name implies, a data dictionary simply enumerates and defines the individual data elements that must be stored. An initial draft of the data dictionary for a simple contact manager might look like Table 25.1.
Table 25.1. Data dictionary for the contact manager sample application.
Item | Description |
Last Name | The individual contact's last name |
First Name | The individual's first name |
Middle Initial | The individual's middle initial |
Page 625
Item | Description |
Contact Type | individual represents a client, a prospect, a vendor, or some other type of contact |
Individual Notes | Additional information related to the individual |
Company | The name of the company that the individual represents |
Company Notes | Additional information related to the individual's company |
Address Line 1 | Line 1 of the individual's street address |
Address Line 2 | Line 2 of the individual's street address |
Address Line 3 | Line 3 of the individual's street address |
City | City name of the individual's mailing address |
State | State name for the individual's mailing address |
Zip Code | Zip code for the individual's mailing address |
Address Type | Standardized description indicating whether this is a work, home, or some other type of address |
Phone Number | The individual's area code and phone number |
Phone Type | Standardized description indicating whether this is a home, office, or other type of phone number |
Contact Date | The date that this individual was contacted |
Contacted By | The name of the salesperson or employee who contacted this individual |
Contact Method | Standardized description indicating whether the individual was contacted by phone, mail, fax, or some other method |
Contact Reason | Standardized description of the reason that the individual was contacted |
Contact Notes | Additional information related to this specific contact |
Although this is a good way to start defining database requirements, there are obvious shortcomings. The data dictionary does not describe how these individual items are related. It also lacks information regarding how the data is created, updated, and retrieved, among other things.
A functional specification should document the system requirements in plain English and should fill in details concerning who will be using the system, when, and how. Information concerning the number of concurrent users accessing the system, how frequently records are inserted and updated, and how information will be retrieved are particularly important topics to be covered in the functional specification. These factors will help determine hardware and software licensing requirements, and have a significant impact on issues relating to performance, security, and database integrity.
Page 626
The functional description for the sample contact manager might include a summary similar to the text that follows :
The system will be available to 40 sales representatives, 5 sales managers, 25 sales assistants, 6 purchasing agents , 1 purchasing department manager, and 2 purchasing assistants, for a total of 79 users. Of these 79 possible users, it is expected that a maximum of 20 would be actively using the system at any given time. Purchasing department personnel should have access only to purchasing department contacts, and sales department personnel should have access only to sales contacts.
All users may add information regarding a specific contact at any time, but whereas sales representatives and purchasing agents can add new prospects, only assistants can add new vendors and clients (after obtaining proper approval from a manager). Sales representatives and purchasing agents should have access only to their accounts and prospects, whereas managers should have full access to the entire database for their specific departments.
One assistant from each department will be designated as a system administrator. Only the system administrators will be able to add and modify address, phone, contact types, contact methods , and contact reasons. With the approval of a manager, a system administrator will be able to reassign a vendor or client to a new purchasing agent or sales representative.
For audit purposes, every time information is added or modified, the individual who made the modification, and the date and time that the information was modified, should be recorded.
In the preceding example, the functional specification added several new data elements to the requirements, in addition to pertinent information regarding access and security. The functional specification and data dictionary are often developed simultaneously , because one document can provide relevant information that should be reflected in the other.
An important part of requirements analysis is to anticipate the needs of the users, because they will not always be able to fully explain the system requirements on their own. Based on information from the previous examples, the system designers might have these follow-up questions:
These are obviously just a few of the questions that come to mind. In practice, the functional description should describe the system to the fullest extent and detail possible. The importance of thorough requirements analysis and documentation is often underestimated. Put simply,