1. Defining a Mission Statement and Mission Objectives

Mission Statement

Let's study on the Case Study first: Mike’s Bikes

Mike’s Bikes, our case-study business, is a new bike shop located in a small suburb called Greenlake, not far from downtown Seattle. It has been open for only two months, and business is growing steadily. Mike, the shop’s owner, has been conducting his daily business on paper. He records sales on preprinted forms, maintains employee and vendor information on sheets of paper (storing them in folders), and writes information about his regular customers on index cards. As a result, Mike spends a lot of time maintaining all of this data. He owns a computer but uses it mainly to play games, watch videos on YouTube, write email, keep in touch with friends on Facebook, and visit various golf sites. The only business-related task he performs on the computer is keeping track of the bike shop’s inventory using a spreadsheet program.

Although he thinks a database is a good idea, he’s aware of the fact that he doesn’t know the first thing about properly designing one. Undaunted, Mike has decided to hire a database consultant to design the database for him.

You are, in this fable, the consultant he has hired for the project. you’ll apply each technique to design the database for Mike’s Bikes. As you learn new concepts or techniques, Mike will supply you with the information you need to complete the design of his database.


Defining the Mission Statement

It provides you with a focus for your design efforts and keeps you from getting diverted and making the database structure unnecessarily large or complex.
The Well-Written Mission Statement

Lets' take an example
► The purpose of the New Starz Talent Agency database is to maintain the data we generate, and to supply information that supports the engagement services we provide to our clients and the management services we provide to our entertainers.

► Think of a mission statement as the flame of a candle located at the end of a dark tunnel. The light produced by the flame guides you to the end of the tunnel, so long as you focus on it. In the same manner, the mission statement guides you to the end of the database design process.


Here’s an example of a poorly worded mission statement:
-------------------------------------------------
The purpose of the Whatcom County Hearing Examiner’s database is to keep track of applications for land use, maintain data on applicants, keep a record of all hearings, keep a record of all decisions, keep a record of all appeals, maintain data on department employees, and maintain data for general office use.

It should be immediately apparent that there are a few things wrong with this mission statement.
• It’s slightly verbose. Remember that the ideal mission statement should be succinct and to the point.
• The specific purpose of the database is unclear. This mission statement is written in such a way that it is difficult for you to ascertain the specific purpose of the database.
• It describes several specific tasks. Two issues arise when a mission statement is written in this manner. First, the description of the tasks does nothing to define the specific purpose of the database. Second, the statement somehow appears to be incomplete. It raises the question, “Are there any tasks we’ve forgotten to include in the mission statement?”

You can fix this mission statement by removing the references to specific tasks (be sure to save them for the next step) and rewriting the statement. Here is an example of one of the possible ways you could rewrite this mission statement:

The purpose of the Whatcom County Hearing Examiner’s database is to maintain the data the examiner’s office uses to make decisions on land-use requests submitted by citizens of Whatcom County.
-------------------------------------------------

Case Study: Defining a Mission Statement for Mike’s Bikes

Now you need to define a mission statement for Mike’s Bikes. Before you can define the mission statement, you must conduct an interview with the owner to gather information about his business. Assume you have an assistant named Zachary who is conducting the interview for you. The interview may go something like this:
ZACHARY:     “Can you tell me why you believe you need a database?”
MIKE:            “I think we need a database just to keep track of all our inventory. I’d also like to keep track of all our sales as well.”
ZACHARY:     “I’m sure the database will address those issues. Now, what would you say is the single most important function of your business?”
MIKE:            “To provide a wide array of bicycle products and bicycle-related services to our customers. We have a lot of great customers. And regular ones too! They’re our biggest asset.”
(The interview continues until Zachary has finished asking all the questions on his list.)

After the interview, review the information you’ve gathered and define the mission statement. You can ascertain a few points from the previous dialogue with Mike, such as the fact that he’ll need to be able to track products, customers, and customer sales. But the most valuable point is provided by his reply to the second question. You can use the first sentence in that reply to formulate the mission statement. Taking into account some of the other points you’ve identified in the interview, you can rewrite Mike’s reply to create the following mission statement:

The purpose of the Mike’s Bikes database is to maintain the data we need to support our retail sales business and our customer service operations.

Mission Objectives

Mission objectives help you define table structures, field specifications, relationship characteristics, and views. They also help you establish data integrity and define business rules. Finally, mission objectives guide your development efforts and ensure that your final database structure supports the mission statement.

Here are some examples of typical mission objectives:

► Maintain complete patient address information.
► Keep track of all customer sales.
► Make sure an account representative is responsible for no more than 20 accounts at any given time.
► Keep track of vehicle maintenance.
► Produce employee phone directories.

Here is an example of a poorly written mission objective:

We need to keep track of the entertainers we represent and the type of entertainment they provide, as well as the engagements that we book for them.
There are two problems with this mission objective.

1. It defines more than a single general task. It is clear that there are two tasks represented in this statement—keeping track of entertainers and keeping track of engagements.
2. It contains unnecessary detail. It’s unnecessary to refer to the entertainer’s “type of entertainment” in this mission objective. The phrase type of entertainment either refers to a distinct characteristic of an entertainer, or represents a new task that should be declared as a mission objective. If it refers to a distinct characteristic of an entertainer, it should be removed from the statement; otherwise, it should be used as the basis for a new mission objective.

You can fix this mission objective by removing the unnecessary detail and rewriting it as two mission objectives. (Keep the details you discard on a separate list; they may be useful later in the design process.) Here is an example of one possible revision:

► Maintain complete entertainer information.
► Keep track of all the engagements we book.

What types of reports do you generate?
What types of things do you keep track of?
What types of services does your organization provide?
How would you describe the type of work you do?
following above question
“Is there any type of customer information incorporated within the procedure you just described?”

Here is a set of mission objectives that you could derive from the participant’s original response:
Maintain information on customer vehicles.
Keep track of work orders.
Maintain information on our service teams.
Maintain information on our mechanics.
Maintain information on our customers.


Case Study: Defining Mission Objectives for Mike’s Bikes

ZACHARY:     “Can you give me an idea of the things you’d like to track in the database?”
MIKE:            “Oh sure, that’s pretty easy. I want to keep track of our inventory, our customers, and our sales.”
ZACHARY:     “Is there anything else that you can think of that is related to these subjects?”
MIKE            “Well, I guess if we’re going to keep track of our inventory, we should know who our suppliers are.”
ZACHARY:     “What about the sales reps involved in each sale?”
MIKE:            “Oh yeah, we should definitely keep information about our employees. If nothing else, it’s a good idea to do this from a human resources point of view. At least, that’s what my wife tells me!”


Here are a few possible mission objectives for the Mike’s Bikes database.
Maintain complete inventory information.
Maintain complete customer information.
Track all customer sales.
Maintain complete supplier information.
Maintain complete employee information.

Design Process

1. Defining a Mission Statement and Mission Objectives

These are statements that represent the general tasks your users can perform against the data in the database. You use these objectives to support your mission statement and to help you determine various aspects of the database structure.

There are two separate groups of people who will be involved in defining the mission statement and the mission objectives.
►    The first group the database developer (you)

►    The second group the database developer (you again), management personnel, and end users, and it will be responsible for defining the mission objectives.

2. Analyzing the Current Database

The second phase in the database design process involves analyzing the current database, if one exists. Depending on your organization, the database will typically be a legacy database or a paper-based database. 

Another part of the analysis involves conducting interviews with users and management to identify how they interact with the database on a daily basis.

Next, you use the information you’ve gathered from the analysis and the interviews to compile an initial list of fields. You then refine this list by removing all calculated fields and placing them on their own list—you’ll use these calculated fields later in the design process. 

Once your initial field list is complete, you send it to your users and management for a brief review and possible refinement. You encourage feedback and take their suggestions for modifications into consideration. If you think the suggestions are reasonable and well supported, you make the appropriate modifications, record the list in its current state, and move on to the next phase.

3. Creating the Data Structures

You define tables and fields, establish keys, and define field specifications for every field.

4. Determining and Establishing Table Relationships

The fourth phase of the database design process involves establishing table relationships. You conduct interviews with users and management once again, identify relationships, identify relationship characteristics, and establish relationship-level integrity.

After you’ve identified the relationships, you establish a logical connection between the tables in each relationship with a primary key or with a linking table. What you actually use depends upon the type of relationship you’re establishing between the tables.

Next, you determine the type of participation and degree of participation for the tables in each relationship. In some cases, these participation characteristics will be obvious to you due to the nature of the data stored in the tables. In other cases, you’ll base the participation characteristics on specific business rules.

5. Determining and Defining Business Rules

During this phase, you’ll hold interviews, identify limitations on various aspects of the database, establish business rules, and define and implement validation tables.

6. Determining and Defining Views

The sixth phase of the design process involves determining and defining views. Here you’ll conduct interviews (once again), identify various ways of working with the data, and establish the views.

7. Reviewing Data Integrity

First, you review each table to ensure that it meets the criteria of a properly designed table and you check the fields within each table for proper structure.
Second, you review and check the field specifications for each field.
Third, you review the validity of each relationship, confirm the relationship type, and confirm the participation characteristics for each table within the relationship.
Finally, you review the business rules that you identified earlier in the database design process and confirm the constraints you’ve placed on various aspects of the database.

You’re ready to implement your logical database structure in an RDBMS program once you’ve completed the entire database design process. However, the process is never really complete because the database structure will always need refinement as your organization evolves.

Terminology

In order to get Database Design Methods we need to be familiar with some terminologies.
► Records
► Fields 

You’ll typically encounter three other types of fields in an improperly or poorly designed database:
1. A multipart field (also known as a composite field), which contains two or more distinct items within its value
2. A multivalued field, which contains multiple instances of the same type of value
3. And a calculated field, which contains a concatenated text value or the result of a mathematical expression

► A record (known as a tuple in relational database theory) represents a unique instance of the subject of a table. It is composed of the entire set of fields in a table, regardless of whether the fields contain values.

► Degree of Participation
The degree of participation determines the minimum number of records that a given table must have associated with a single record in the related table and the maximum number of records that a given table is allowed to have associated with a single record in the related table.

Consider, once again, a relationship between two tables called TABLE_A and TABLE_B. You establish the degree of participation for TABLE_B by indicating a minimum and maximum number of records in TABLE_B that can be related to a single record in TABLE_A. If a single record in TABLE_A can be related to no less than one but no more than ten records in TABLE_B, then the degree of participation for TABLE_B is 1,10. (The notation for the degree of participation shows the minimum number on the left and the maximum number on the right, separated by a comma.) You can establish the degree of participation for TABLE_A in the same manner. You can identify the degree of participation for each table in a relationship by determining the way the data in each table is related and how you’re using the data.

Integrity-Related Terms
Field Specification
A field specification (traditionally known as a domain) represents all the elements of a field. Each field specification incorporates three types of elements: general, physical, and logical.
• General elements constitute the most fundamental information about the field and include items such as Field Name, Description, and Parent Table.
• Physical elements determine how a field is built and how it is represented to the person using it. This category includes items such as Data Type, Length, and Display Format.
• Logical elements describe the values stored in a field and include items such as Required Value, Range of Values, and Default Value.

Data Integrity
Data integrity refers to the validity, consistency, and accuracy of the data in a database. I cannot overstate the fact that the level of accuracy of the information you retrieve from the database is in direct proportion to the level of data integrity you impose upon the database. Data integrity is one of the most important aspects of the database design process, and you cannot underestimate, overlook, or even partially neglect it. To do so would put you at risk of being plagued by errors that are very hard to detect or identify. As a result, you would be making important decisions on information that is inaccurate at best, or totally invalid at worst.

There are four types of data integrity that you’ll implement during the database design process. Three types of data integrity are based on various aspects of the database structure and are labeled according to the area (level) in which they operate. The fourth type of data integrity is based on the way an organization perceives and uses its data. The following is a brief description of each.


1. Table-level integrity (traditionally known as entity integrity) ensures that there are no duplicate records within the table and that the field that identifies each record within the table is unique and never null.
2. Field-level integrity (traditionally known as domain integrity) ensures that the structure of every field is sound; that the values in each field are valid, consistent, and accurate; and that fields of the same type (such as CITY fields) are consistently defined throughout the database.
3. Relationship-level integrity (traditionally known as referential integrity) ensures that the relationship between a pair of tables is sound and that the records in the tables are synchronized whenever data is entered into, updated in, or deleted from either table.
4. Business rules impose restrictions or limitations on certain aspects of a database based on the ways an organization perceives and uses its data. These restrictions can affect aspects of database design, such as the range and types of values stored in a field, the type of participation and the degree of participation of each table within a relationship, and the type of synchronization used for relationship-level integrity in certain relationships.

Type of database in database management

                                                     
There are two types of databases in database management, operational databases
and  analytical databases.
                                                      
 operational databases
 ─────────────────────
► This type of database is primarily used in online transaction processing (OLTP)
  scenarios, that is, in situations where there is a need to collect, modify, and
  maintain data on a daily basis.
► The type of data stored in an operational database is dynamic, meaning that it
  changes constantly and always reflects up-to-the-minute information.
► Organizations such as retail stores, manufacturing companies, hospitals and
  clinics, and publishing houses use operational databases because their data
  is in a constant state of flux.

 analytical databases.
 ─────────────────────
► Analytical databases are primarily used in online analytical processing (OLAP)
  scenarios.
► where there is a need to store and track historical and time-dependent data.
  An analytical database is a valuable asset when there is a need to track trends,
  view statistical data over a long period of time, and make tactical or strategic
  business projections.
► This type of database stores static data, meaning that the data is never (or very
  rarely) modified. The information gleaned from an analytical database reflects a
  point-in-time snapshot of the data.
► Chemical labs, geological companies, and marketing analysis firms are examples of
  organizations that use analytical databases.
►► Analytical databases often use data from operational databases as their main data
   source, so there can be some amount of association between them;
►► operational and analytical databases fulfill very specific types of data processing
   needs and creating their structures requires radically different design methodologies.