Lesson 3:Identifying System Requirements | MCSE Training Kit (Exam 70-229): Microsoft SQL Server(TM) 2000 Database Design and Implementation (Pro Certification)

3 4

Before creating a database, you must have a thorough understanding of the job that the database is expected to do. You can gain this understanding only by identifying specific types of information that are essential to developing an efficient database design. This lesson discusses the types of information that you must gather before you can begin creating a logical data model. To develop this model, you must identify the goals of your database project. You must also understand the type and amount of data with which you will be working, how you will use the data, and any business rules that apply to the new system that you are implementing.

After this lesson, you will be able to:

Identify the goals and scope of a database development project.
Identify the types of data that a database will manage, the current amount of data, the expected growth, and how it is currently managed.
Identify how you will use the data in a new database.
Identify any business rules that will be placed on the system.

Estimated lesson time: 35 minutes

The Process of Identifying System Requirements

The process of identifying system requirements includes a variety of steps. The number of steps included in this process, how these steps are defined, and the detail in which they are defined can vary greatly from resource to resource (with no one method necessarily being the most accurate). For the purpose of this training kit, however, this process has been divided into four primary tasks:

Identifying system goals
Identifying the amount and types of data
Identifying how the data will be used
Identifying business rules

You do not necessarily have to perform these tasks one at a time. For example, while identifying the amount and types of data, you might also find it useful to determine how the data will be used and what constraints should be placed on the data. Figure 3.8 illustrates the process of identifying system requirements.

figure 3.8-identifying system requirements.

Figure 3.8 Identifying system requirements.

NOTE

For additional information about designing relational database systems, refer to Designing Relational Databases by Rebecca M. Riordan (Microsoft Press, 1999). This book provides a thorough discussion of database design and can be a valuable resource in building on the information in this training kit.

Identifying System Goals

Designing a database requires an understanding of the business functions that you want to model. As much as possible, your database design should accurately model the business. It is time-consuming to change the design of a database significantly once it has been implemented. A well-designed database also performs better. When designing a database, you must consider the purpose of the database and how it affects the design. In other words, you must determine the goals of the new system. Why are you creating this database?

The system goals are the reasons why you are implementing the new database. To create an effective database design, you must have thorough knowledge of the job that the database is expected to perform. Without this understanding, you cannot make informed decisions about how the database should be designed. The system goals are the reasons why the database is being developed.

Determining the system goals is not always a straightforward process. Most database development projects have many goals (both tangible and intangible), and trying to discover them can often take a fair amount of detective work. For example, a manufacturing company might decide to automate its process for managing inventory. One of the company's stated goals for the project is "to make it easer to manage inventory." Your job is to take this intangible goal and try to determine the underlying tangible goal(s). Does the company want to speed up the process of managing inventory? Does it want to more accurately track inventory? Does it want to reduce costs? The intangible goal of "making it easier" might include all of these more tangible goals and more.

Although these goals are more tangible, they are still vague. Vague goals tend to be stated in general terms, such as "increase productivity" or "improve performance." As you go through the process of identifying goals, you must determine the degree to which these goals should be achieved. If the goal is to increase productivity, you should try to find out from what to what. Whenever possible, your goals should be directly measurable.

You should be aware, however, of the dangers of going overboard when measuring certain goals. Often, in order to determine a measurable goal, you must be able to establish an initial measurement. For example, if a goal of an inventory database is to improve accuracy, it might take a great deal of resources to study how much inaccuracy exists in the current process. A study of this sort could span years of inventory history and perhaps cost more to conduct than it would to design and implement the database. In cases such as these, it might be better to talk to managers and bookkeepers first to get a general sense of where the problems lie and what can be done to solve them. When determining the extent to which a base measurement should be studied, you should keep in mind the scale of the project and its practical application while always maintaining a sense of proportion.

Sometimes, intangible goals can be difficult to translate into more tangible goals. This situation is particularly true for goals that adopt popular marketing jargon, such as product position, getting on the same page, or thinking out of the box. For example, the stated goal might be something that seems to have no meaning or relevance: "We want the new system to show our clients that we're thinking out of the box and getting on the same page as them—in order to improve product positioning." In these cases, you must work closely with the organization to clearly define what its stated goal means.

After you have defined the initial goals for the new database, you can begin looking at the type and amount of data that the system will support. As you move forward with the database design process, however, be prepared to re-evaluate these goals. As projects move forward, management changes, business requirements change, and company expectations are revised. As a result, goals evolve—which means that the database design might need to be modified. You might also discover, as you dig deeper into the project, that some goals are unattainable or inappropriate. As new understandings and additional information continue to unfold, you must be prepared to act accordingly.

Identifying the Amount and Types of Data

The amount and types of data that your database will store can affect database performance and should be taken into consideration when creating your database. The amount of data will, of course, affect the size of your database, and the types of data are a factor in determining the kinds of constraints that are incorporated into the database design.

In many cases, determining the amount and types of data is a straightforward process because a system is already implemented and you are simply upgrading or replacing that system. In these situations, you can examine the body of data that already exists.

In those cases in which you are implementing a new system—or radically altering the existing one—your job might be a little more difficult because you might have to spend a fair amount of time determining what types of data will be stored and how much data there will be. You might need to interview key participants and collect copies of relevant documents and forms, such as customer statements, inventory lists, management reports, and any other documents that are currently being used.

Whatever the current system, you must determine the volume of data that the system will manage. When examining data volume, you should identify the actual amount of data and its growth pattern. For example, a warehouse might currently carry only a few thousand items, but it might be planning to add several hundred a day over the next few years to substantially increase how many items are kept on hand. Another warehouse might carry millions of items but it might plan to add only a few new items a day, never letting the inventory go much beyond its current capacity. The growth patterns for these two systems are very different, and as a result, the design approach will vary.

When looking at the types of data, you are basically trying to get a general sense of the categories of information that you will be storing and what details about the categories are necessary to store. This process will prepare you for mapping out the entities and attributes that you will incorporate into your database design. For example, if you are developing a database for a clothing retailer, the types of data might include information about customers who purchase products from the store. Customer information could include names, addresses, phone numbers, and even style preferences.

At this point in the design process, you do not have to get too specific about how data should be categorized or grouped. You are merely attempting to gain a general sense of the types of data involved and creating a centralized list for those types of data. When you actually begin to identify database objects, you will take the information you gather here and use it to develop a data model.

Identifying How the Data Will Be Used

As you gather system requirements, you must determine how information in your database will be used. The purpose of this step is to identify who will be using the data, the number of users who will be accessing the data, and the tasks they will be performing when they access that data.

When determining who will be using the data, you should think in terms of categories of users. For example, one category of users might be the general public (who accesses data through the Internet). You might also have another category of users who access data through the company's intranet. Some organizations might have only one type of user, while other organizations might have many types. In addition, there is no set minimum or maximum number of users that each category must contain. The only limitations are those dictated by hardware configurations and database design. One category might contain only one user, while another category might contain 100,000 users.

As you determine who will be using the data, you must also identify how many users in each category will be accessing the data. This estimate should include not only current numbers but projected figures, as well. In addition, you will have to define user concurrency. You should know how many people will be connected to the database at one time and how many might be trying to update the database at one time.

Once you have defined who your users are and how many there are, you must identify the tasks they will be performing when they access the database. For example, suppose a manufacturing company includes a category of users who take orders from customers. This order-taking group must be able to access and update customer information. In addition, it must be able to view inventory data in order to place these orders. The company might also include a category of users from human resources. The human resources group must be able to view and update employee information. By identifying these tasks, you can determine how the database will be accessed and how data will be viewed and manipulated. When you combine this information with the number and type of users, you will be able to implement a database design that serves the needs of everyone in the organization.

Identifying Business Rules of the System

By identifying the business rules, you are determining the constraints that govern how data and the system should be handled and protected. These constraints refer to more than the individual integrity applied to entity attributes. Business rules are much broader and incorporate all of the constraints on the system, including data integrity as well as system security. In other words, you are defining what each category of users can or cannot do.

Returning to the example of the manufacturing company, the order-taking group can access and update customer records and view inventory data. You might determine that these users should not be able to update inventory data and should not be able to view employee data, however. You might also determine that no customer records can be created without a full mailing address and phone number. Another constraint might be that any item added to a customer order should be removed from inventory. Business rules can include a wide spectrum of constraints, some pertaining to the system as a whole and others pertaining to specific types of data.

Exercise 2: Identifying the System Requirements for Your Database Design

In this exercise, you will review the following scenario. From the information in the scenario, you will identify the system requirements for a database design. You will be using this scenario and the result of this exercise in subsequent exercises. The end product will be a database that you have designed and implemented on your SQL Server computer. To complete this exercise, you need paper and a pencil. Because you need to save the result of this exercise, however, you might want to copy it into a word processing file or text file.

NOTE

When designing a relational database system, your design specifications often include the applications that are necessary to access the data. For the purposes of this training kit, however, the exercises will focus on designing and implementing only the database component of the entire system.

Book Shop Scenario

The manager of a small book shop has asked you to design and implement a database that centralizes information so that it is easier and more efficient to manage inventory and track orders and sales. The shop handles rare and out-of-print books and tends to carry only a few thousand titles at any one time. Currently, the manager tracks all of the sales and inventory on paper. For each book, the manager records the title, author, publisher, publication date, edition, cost, suggested retail price, and a rating that indicates the condition of the book. Each book is assigned one of the following ratings: superb, excellent, good, fair, poor, or damaged. The manager would like to be able to add a description to each rating (just a couple of sentences), but the description should not be required. The information about each book must include the title, author, cost, suggested retail price, and rating. The publisher, publication date, and edition are not always available. If the year a book was published is available, the year will never be before 1600. And for purposes of the new database system, the publication date will never fall after the year 2099.

Because these books are rare, each title must be tracked individually—even if they are the same book (identical title, author, publisher, publication date, and edition). Currently, the manager assigns a unique ID to each book so that identical titles can be differentiated. This ID must be included with the book information. The book ID assigned by the manager is an eight-character ID made up of numbers and letters.

The manager also maintains limited information about each author whose books the store has carried or is carrying. The store might carry more than one book by an author, and sometimes more than one author will have written a book. The manager currently maintains information about approximately 2500 authors. The information includes the author's first name, last name, year of birth, and year of death (if applicable). The information must include—at the very least—the author's last name. The manager would like to include a brief description of each author, if available, when the author is added to the list. The description will usually be no longer than one or two sentences.

The bookstore has 12 employees (including the manager and assistant manager). The manager expects to hire an additional employee every year for the next few years. Both the manager and the assistant manager must be able to access and modify information about each employee as necessary. Employee information must include each employee's first name, last name, address, phone number, date of birth, hire date, and position in the store. Positions include Manager, Assistant Manager, Full Time Sales Clerk, and Part Time Sales Clerk. The manager might at some point want to add new job titles to the list or change existing ones and would eventually like to add a brief description of job duties to each title (at least, to some of the titles). An employee can hold only one position at any one time. No employee—other than the two managers—should have access to the employee information. The manager also likes to track how many books and which books each employee is selling.

The bookstore currently maintains information about customers. For each customer, the information includes the customer's first name, last name, telephone number, mailing address, books that the customer has purchased, and when the purchase was made. Because some customers do not like to give out personal information, only a first name or a last name is required. The manager currently has a list of about 2000 customers. Not all customers who are included in the list have bought books, although most have.

The manager maintains a record of sales by tracking each order from when a sales clerk takes the order to when the sale is complete. In some cases, such as for walk-in customers, these two events occur concurrently. Each order must include information about the book sold, the customer who bought the book, the salesperson who sold the book, the amount of the sale, and the date of the order. The order must also include the delivery or pickup date, which is added after the merchandise is actually picked up or delivered. An order is completed when a book has been paid for and picked up at the store or paid for and shipped to the customer. A book cannot be taken out of the store or shipped unless it is paid for. Each order includes the payment method and the status of the order. Payment methods include cash, check, and credit cards. The status of an order must be one of the following: (1) to be shipped, (2) customer will pick up, (3) shipped, or (4) picked up. An order can contain only one customer, salesperson, order date, delivery date, payment method, and order status; however, an order can contain one or more books.

Currently, orders are generated, tracked, and modified on paper order forms. The forms are used to make sure that the orders get shipped (if applicable) and to maintain a record of sales. Whenever a book is added to an order, it is removed from the inventory list. This process has been very tedious and not always very efficient. This situation can also lead to confusion and mistakes. Ideally, the manager would like sold books to remain in the list of books but be marked somehow to show that the book has been sold.

The store sells about 20 books a day. The store is open five days a week for about 10 hours a day. There are one to two salespeople working at the same time, and there are two sales counters where people pick up and pay for books and where salespeople process orders. At least one manager is in the store at one time.

The manager expects sales to increase by about 10 percent each year. As a result, the number of books on hand, authors, and customers should all increase at about the same rate.

In order to serve customers effectively, each employee must be able to access a centralized source of information about authors, books in stock, customers, and orders. Currently, employees access this information from index cards and lists. Often, these lists are not up-to-date, and errors are made. In addition, each employee should be able to create, track, and modify orders online, rather than having to maintain paper order forms. Only the managers should be able to modify information about authors, books, and customers, however.

NOTE

You can find a copy of this scenario in the BookstoreProject.doc file in the Chapter03\Exercise2 subfolder of the Training Kit Supplemental CD-ROM. You can use this copy to mark up and make notes as necessary to complete the steps in this exercise. This document will also come in handy for subsequent exercises.

To identify system goals

Review the scenario. Do not try to memorize all the details; instead, try to get a general sense of what the project is trying to accomplish.
Write down the system goals that you can identify in the scenario.

What are those goals?

Review each goal to determine whether it is measurable.

Which goals are measurable?

To identify the amount and type of data

Write down the categories of data that you can identify in this scenario.

What categories of data can you identify?

For each category of data that you identified in Step 1, write down the type of information that you should track for each category.

What types of information can you identify?

For each category of data that you identified in Step 1, write down the current amount of data for each category.

What is the volume of data for each category?

For each category of data that you identified in Step 1, write down the expected growth pattern.

What is the growth pattern for each category?

To identify how the data will be used

Write down the categories of users that you can identify in this scenario.

What are those categories of users?

For each category of user that you identified in Step 1, write down the number of users.

What are the current number of users and the projected number of users in each category?

For each category of user that you identified in Step 1, write down the tasks that they will be performing.

What tasks will each type of user be performing?

To identify business rules

Write down the business rules that you can identify in this scenario.

What are the business rules?

Lesson Summary

Before you can develop a data model, you must identify the goals of your database project, the type and amount of data that you will be working with, how the data will be used, and any business constraints that should exist on the data. You must consider the purpose of the database and how it affects the design. You should have a clear understanding of why you are creating this database. Another area of concern when identifying system requirements is the amount and types of data that your database will store. Whatever the current system, you must determine the volume of data that the system will manage. When examining data volume, you should determine the actual amount of data and its growth pattern. When looking at the types of data, you are basically trying to get a general sense of the categories of information you will be storing and what details about the categories are necessary to store. As you gather system requirements, you must identify who will be using the data, the number of users who will be accessing the data, and the tasks they will be performing when they access that data. By identifying the constraints on the data, you are determining the business rules that govern how data should be handled and protected. Business rules include data integrity as well as system security. They enable you to define what each category of users can and cannot do.