0623-0626

Previous Table of Contents Next

Page 623

CHAPTER 25

Designing a
Database

IN THIS CHAPTER

  • Requirements Definition
  • The Logical Model
  • The Physical Model

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.

Requirements Definition

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:

  • Will a contact have only one address and phone number? One company? One type?
  • How does a prospect become a client or vendor?
  • How are client and vendor accounts initially assigned to sales representatives and purchasing agents?
  • How are client and vendor account numbers assigned?
  • Can contact and audit information be archived? If so, after how many months?

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,

Previous Table of Contents Next


Oracle Unleashed
Oracle Development Unleashed (3rd Edition)
ISBN: 0672315750
EAN: 2147483647
Year: 1997
Pages: 391

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net