Unit 17 Data Base Design concepts


Unit 17 Data Base Design concepts



* -------------------------------------------------
Database Management System (DBMS)

* Stands for "Database Management System." In short, a DBMS is a database program. Technically speaking, it is a software system that uses a standard method of cataloging, retrieving, and running queries on data. The DBMS manages incoming data, organizes it, and provides ways for the data to be modified or extracted by users or other programs. Some DBMS examples include MySQL, PostgreSQL, Microsoft Access, SQL Server, FileMaker, Oracle, RDBMS, dBASE, Clipper, and FoxPro. Since there are so many database management systems available, it is important for there to be a way for them to communicate with each other. For this reason, most database software comes with an Open Database Connectivity (ODBC) driver that allows the database to integrate with other databases. For example, common SQL statements such as SELECT and INSERT are translated from a program's proprietary syntax into a syntax other databases can understand.

* Objectives of DBMS

The objectives that the management should keep in mind when they design and organize their data base management systems are:

(i) Provide for mass storage of relevant data,
(ii) Make access to the data easy for the user,
(iii) Provide prompt response to user requests for data,
(iv) Make the latest modifications to the database available immediately, (v) Eliminate redundant data,
(vi) Allow for multiple users to be active at one time,
(vii) Allow for growth in the database system,
(viii) Protect the data from physical harm and unauthorised access.

* Importance of DBMS

Databases are collections of independently stored information pieces (data), and management of a database involves initial indexing of available data by 'tagging' the individually stored information based on common factors or lack thereof. This is done through assignment of values which represent relevant criteria (i.e. phone numbers, names, addresses, etc.).

When initial values have been assigned one or more relationships between assigned tags can further be defined which allows for the creation of 'groups' or 'pools' which meet programed criteria to make use of a database more user friendly (ie. allows users to search within preset groups like: new clients + high volume + US + wholesale + online sales OR new clients + high volume + US + retail + offline sales, etc.)

If values and relationships are not assigned to the information stored within a database (comparable to a book without numbered pages to refer to) than desired requests for data will take much longer to be processed thereby wasting time and energy that could be used in a constructive and profitable way by available resources which for businesses based on provision of data obviously has a considerable impact.

* Advantages of the DBMS:

The DBMS serves as the intermediary between the user and the database. The database structure itself is stored as a collection of files, and the only way to access the data in those files is through the DBMS. The DBMS receives all application requests and translates them into the complex operations required to fulfill those requests. The DBMS hides much of the database’s internal complexity from the application programs and users.

The different advantages of DBMS are as follows.

1. Improved data sharing.
The DBMS helps create an environment in which end users have better access to more and better-managed data. Such access makes it possible for end users to respond quickly to changes in their environment.

2. Improved data security.
The more users access the data, the greater the risks of data security breaches. Corporations invest considerable amounts of time, effort, and money to ensure that corporate data are used properly. A DBMS provides a framework for better enforcement of data privacy and security policies.

3. Better data integration.
Wider access to well-managed data promotes an integrated view of the organization’s operations and a clearer view of the big picture. It becomes much easier to see how actions in one segment of the company affect other segments.

4. Minimized data inconsistency.
Data inconsistency exists when different versions of the same data appear in different places. For example, data inconsistency exists when a company’s sales department stores a sales representative’s name as “Bill Brown” and the company’s personnel department stores that same person’s name as “William G. Brown,” or when the company’s regional sales office shows the price of a product as $45.95 and its national sales office shows the same product’s price as $43.95. The probability of data inconsistency is greatly reduced in a properly designed database.

5. Improved data access.
The DBMS makes it possible to produce quick answers to ad hoc queries. From a database perspective, a query is a specific request issued to the DBMS for data manipulation—for example, to read or update the data. Simply put, a query is a question, and an ad hoc query is a spur-of-the-moment question. The DBMS sends back an answer (called the query result set) to the application. For example, end users, when dealing with large amounts of sales data, might want quick answers to questions (ad hoc queries) such as: - What was the dollar volume of sales by product during the past six months? - What is the sales bonus figure for each of our salespeople during the past three months? - How many of our customers have credit balances of $3,000 or more?

6.Improved decision making.
Better-managed data and improved data access make it possible to generate better-quality information, on which better decisions are based. The quality of the information generated depends on the quality of the underlying data. Data quality is a comprehensive approach to promoting the accuracy, validity, and timeliness of the data. While the DBMS does not guarantee data quality, it provides a framework to facilitate data quality initiatives.

7.Increased end-user productivity.
The availability of data, combined with the tools that transform data into usable information, empowers end users to make quick, informed decisions that can make the difference between success and failure in the global economy.

* Disadvantages of Database:

Although the database system yields considerable advantages over previous data management approaches, database systems do carry significant disadvantages. For example:

1. Increased costs.
Database systems require sophisticated hardware and software and highly skilled personnel. The cost of maintaining the hardware, software, and personnel required to operate and manage a database system can be substantial. Training, licensing, and regulation compliance costs are often overlooked when database systems are implemented.

2. Management complexity.
Database systems interface with many different technologies and have a significant impact on a company’s resources and culture. The changes introduced by the adoption of a database system must be properly managed to ensure that they help advance the company’s objectives. Given the fact that database systems hold crucial company data that are accessed from multiple sources, security issues must be assessed constantly.

3. Maintaining currency.
To maximize the efficiency of the database system, you must keep your system current. Therefore, you must perform frequent updates and apply the latest patches and security measures to all components. Because database technology advances rapidly, personnel training costs tend to be significant. Vendor dependence. Given the heavy investment in technology and personnel training, companies might be reluctant to change database vendors. As a consequence, vendors are less likely to offer pricing point advantages to existing customers, and those customers might be limited in their choice of database system components.

4. Frequent upgrade/replacement cycles.
DBMS vendors frequently upgrade their products by adding new functionality. Such new features often come bundled in new upgrade versions of the software. Some of these versions require hardware upgrades.

* Functions of DBMS

A DBMS performs several important functions that guarantee the integrity and consistency of the data in the database. Most of those functions are transparent to end users, and most can be achieved only through the use of a DBMS. They include data dictionary management, data storage management, data transformation and presentation, security management, multiuser access control, backup and recovery management, data integrity management, database access languages and application programming interfaces and database communication interfaces. Each of these functions is explained below.

1. Data dictionary management.
The DBMS stores definitions of the data elements and their relationships (metadata) in a data dictionary. In turn, all programs that access the data in the database work through the DBMS. The DBMS uses the data dictionary to look up the required data component structures and relationships, thus relieving you from having to code such complex relationships in each program. Additionally, any changes made in a database structure are automatically recorded in the data dictionary, thereby freeing you from having to modify all of the programs that access the changed structure. In other words, the DBMS provides data abstraction, and it removes structural and data dependence from the system.

2. Data storage management.
The DBMS creates and manages the complex structures required for data storage, thus relieving you from the difficult task of defining and programming the physical data characteristics. A modern DBMS provides storage not only for the data, but also for related data entry forms or screen definitions, report definitions, data validation rules, procedural code, structures to handle video and picture formats, and so on. Data storage management is also important for database performance tuning. Performance tuning relates to the activities that make the database perform more efficiently in terms of storage and access speed.

3. Data transformation and presentation.
The DBMS transforms entered data to conform to required data structures. The DBMS relieves you of the chore of making a distinction between the logical data format and the physical data format. That is, the DBMS formats the physically retrieved data to make it conform to the user’s logical expectations. For example, imagine an enterprise database used by a multinational company. An end user in England would expect to enter data such as July 11, 2010, as “11/07/2010.” In contrast, the same date would be entered in the United States as “07/11/2010.” Regardless of the data presentation format, the DBMS must manage the date in the proper format for each country.

4. Security management.
The DBMS creates a security system that enforces user security and data privacy. Security rules determine which users can access the database, which data items each user can access, and which data operations (read, add, delete, or modify) the user can perform. This is especially important in multiuser database systems.

5. Multiuser access control.
To provide data integrity and data consistency, the DBMS uses sophisticated algorithms to ensure that multiple users can access the database concurrently without compromising the integrity of the database.

6. Backup and recovery management.
The DBMS provides backup and data recovery to ensure data safety and integrity. Current DBMS systems provide special utilities that allow the DBA to perform routine and special backup and restore procedures. Recovery management deals with the recovery of the database after a failure, such as a bad sector in the disk or a power failure. Such capability is critical to preserving the database’s integrity.

7. Data integrity management.
The DBMS promotes and enforces integrity rules, thus minimizing data redundancy and maximizing data consistency. The data relationships stored in the data dictionary are used to enforce data integrity. Ensuring data integrity is especially important in transaction-oriented database systems.

8. Database access languages and application programming interfaces. The DBMS provides data access through a query language. A query language is a nonprocedural language—one that lets the user specify what must be done without having to specify how it is to be done. Structured Query Language (SQL) is the de facto query language and data access standard supported by the majority of DBMS vendors.

9. Database communication interfaces.
Current-generation DBMSs accept end-user requests via multiple, different network environments. For example, the DBMS might provide access to the database via the Internet through the use of Web browsers such as Mozilla Firefox or Microsoft Internet Explorer. In this environment, communications can be accomplished in several ways: - End users can generate answers to queries by filling in screen forms through their preferred Web browser. - The DBMS can automatically publish predefined reports on a Website. - The DBMS can connect to third-party systems to distribute information via e-mail or other productivity applications.

* Five Steps To Ensure The Success Of Your Database
Every year associations collectively invest millions of dollars to upgrade their current membership database systems or to buy brand new systems. Yet it seems that the more money we invest in our databases, the louder the protests are from staff, complaining that the database just doesn’t work. How can this be? There are five steps you can take to ensure that your database lives up to the expectations of its users. By addressing each of these areas, association executives and those responsible for the associations’ database can help to minimize complaining from staff and maximize the usefulness of the database.

Step #1: Review, refine and re-engineer business processes — Today’s increasingly sophisticated relational databases offer a myriad of functions: membership dues processing, events registration and invoicing, publications sales, fundraising, exhibit management, and many more. One element common to all is process. We define process as how information moves through the association and through the database. For example, we process membership dues by reviewing the membership application, entering the key data elements, creating an invoice, entering payment against the invoice, printing a welcome letter, sending a welcome packet, and so on. When contemplating a new database or an upgrade, completely review your business processes. This will help you determine if you have inefficient or improper (or just plain outdated) procedures in place. Discuss these processes with your association software developer. Your developer has created the software to handle each process in a particular manner. This may differ from yours. Find out what it is, and if you can adapt your process to fit more closely what the developer had in mind, without sacrificing your business practice, do it. This may require some fairly radical changes to how you do business internally, but in the long run will help to make the database more useful. The primary danger of implementing a new database without reviewing your business process is that you may wind up automating bad process. Bad process automated will continue to give you the wrong information, only faster than before. Don’t do it. (In addition, if bad process is causing your database to function improperly, you can bet the staff will blame the software.)

Step #2: Develop Proper Training and Documentation – These two go hand-in-hand and are perhaps the issues most commonly complained about by association executives. Once a review of the business process is complete and the installation or upgrade has begun, training and documentation become vital. Unfortunately, association software providers are able to provide only generalized documentation and training for their system.

Neither their training nor their documentation will be specific to your particular membership database and its special elements. This is where you need a specialist on the inside. Some associations are now creating full-time positions, such as a member records administrator or database coordinator. The position is responsible for providing documentation and training for the membership database. From processing new members, to changing membership entitlements, to simply changing an email address, this position is responsible for providing step-by-step instructions for each of the processes. If a full-time position is not possible, consider using part-time help. You may also want to contract with a database consultant. Have the consultant learn your processes and your database, and then work with staff to document processes and develop training materials.

Step #3: Develop and Rigidly Adhere to Data Standards – You may have heard the term GIGO; garbage in, garbage out. Simply put, this means that if the information you store in your database is not entered accurately or uniformly, no amount of reporting, consulting, or crying is going to give you the information you need. This is where data standards come into play. At one association we spent many months working with key database users developing a set of standards by which to enter all data. Not only do the data standards state explicitly how to enter data (e.g., Street, not St., PO, not P.O., etc.), but it also clearly explains where to enter data and what fields must absolutely be entered in order for a record to be complete. Standardizing data entry is critical for developing useful and accurate management reports. Without strict standards, sorting of data can become difficult if not impossible. For example, without clearly defined standards for titles, selecting and sorting on the title of vice president quickly becomes impossible, due to the myriad variations of this title (Vice President, VP, V.P., Vice Pres., etc.). With a data entry standard that clearly states that one enters the title vice president as “Vice President,” selecting on this title now becomes very easy. The same will be true of any other data fields you may choose to select or sort on.

Step #4: Continuously Collect Data – The old saying goes, the minute a directory is printed, it’s already out of date. The same is true for association databases. Depending on the industry or profession you serve, the addresses in your database may change as frequently as 5% per month. Over the course of the year, with no updating being done, you could have a database with over 50% incorrect information! So how do you keep up? Build data collection into the process. Develop a laundry list of methods, both passive and active, for collecting data to update your database. Some of the more obvious data collection mechanisms are publication order forms, meeting registration forms, and an annual directory update. But there are other methods for collecting and updating data in your database. These include establishing a web site that lets your members review their contact information, setting up computers with your database at your annual meeting for members to review, and working with your regions, chapters, or affiliates to assist you with data collection. Data is gold to an association. Keep your gold shining by actively and continuously seeking to update your data.

Step #5: Know What You Need and Want From Your Database — Too often, associations select an association database system based on what it is capable of doing, rather than on whether it does what the association needs it to do. Understanding what you need from your database is not only key in the selection of your database but is essential to its continued effectiveness. Typically there are several functions that a database needs to provide: contact management (address, phone, fax, email), membership tracking and invoicing, volunteer/committee activity, and more. But what are some of the other items you need to track now or may need to track in the future? How about meeting attendance, publications purchasing history, continuing education credits, or areas of interests? All of these issues require the collection of data. If you’re not collecting it, you can’t report it out, and you have to know you want to report it out to begin collecting it. Understanding what your needs are now, and anticipating what your needs may be in the future can go a long way toward creating an effective database. Be sure that you understand your needs and can articulate them to your software provider.

When you create a table or add a field to a table in the geodatabase, fields are created as a specific done on the data, as well as the way the data in that field is stored in the database. When you import data of one type into a field of another data type, you need to understand what the equivalent data types are between ArcSDE and your database management system (DBMS) because it can impact data content. Also, when creating new datasets in ArcGIS, it is helpful to know the equivalent data types between ArcGIS and your DBMS. For example, if you add a floating point (float) column to an existing feature class, that equates to a numeric data type column in a SQL Server database.

* Data types

Learn how data converts from one type to another.
File geodatabase data types are the same as ArcGIS data types. For DBMS products, though, data types can differ. The following sections contain information on how DBMS data types map to ArcGIS data types. Access data types

When you create a feature class or table in ArcGIS, there are 11 different data types available for each column. These types are mapped to Access data types in the following table.

DB2 data types
When you create a feature class or table in ArcGIS, there are 11 different data types available for each column. These types are mapped to DB2 types in the following table.

Informix data types
When you create a feature class or table in ArcGIS, there are 11 different data types available for each column. These types are mapped to Informix types in the following table.

Oracle data types
When you create a feature class or table in ArcGIS, there are 11 different data types available for each column. These types are mapped to Oracle types in the following table.

PostgreSQL data types
When you create a feature class or table in ArcGIS, there are 11 different data types available for each column. These types are mapped to PostgreSQL types in the following table. SQL Server data types

When you create a feature class or table in ArcGIS, there are 11 different data types available for each column. These types are mapped to SQL Server types in the following table.



* -------------------------------------------------
Database Management System (DBMS)

* Stands for "Database Management System." In short, a DBMS is a database program. Technically speaking, it is a software system that uses a standard method of cataloging, retrieving, and running queries on data. The DBMS manages incoming data, organizes it, and provides ways for the data to be modified or extracted by users or other programs. Some DBMS examples include MySQL, PostgreSQL, Microsoft Access, SQL Server, FileMaker, Oracle, RDBMS, dBASE, Clipper, and FoxPro. Since there are so many database management systems available, it is important for there to be a way for them to communicate with each other. For this reason, most database software comes with an Open Database Connectivity (ODBC) driver that allows the database to integrate with other databases. For example, common SQL statements such as SELECT and INSERT are translated from a program's proprietary syntax into a syntax other databases can understand.

* Objectives of DBMS

The objectives that the management should keep in mind when they design and organize their data base management systems are:

(i) Provide for mass storage of relevant data,
(ii) Make access to the data easy for the user,
(iii) Provide prompt response to user requests for data,
(iv) Make the latest modifications to the database available immediately, (v) Eliminate redundant data,
(vi) Allow for multiple users to be active at one time,
(vii) Allow for growth in the database system,
(viii) Protect the data from physical harm and unauthorised access.

* Importance of DBMS

Databases are collections of independently stored information pieces (data), and management of a database involves initial indexing of available data by 'tagging' the individually stored information based on common factors or lack thereof. This is done through assignment of values which represent relevant criteria (i.e. phone numbers, names, addresses, etc.).

When initial values have been assigned one or more relationships between assigned tags can further be defined which allows for the creation of 'groups' or 'pools' which meet programed criteria to make use of a database more user friendly (ie. allows users to search within preset groups like: new clients + high volume + US + wholesale + online sales OR new clients + high volume + US + retail + offline sales, etc.)

If values and relationships are not assigned to the information stored within a database (comparable to a book without numbered pages to refer to) than desired requests for data will take much longer to be processed thereby wasting time and energy that could be used in a constructive and profitable way by available resources which for businesses based on provision of data obviously has a considerable impact.

* Advantages of the DBMS:

The DBMS serves as the intermediary between the user and the database. The database structure itself is stored as a collection of files, and the only way to access the data in those files is through the DBMS. The DBMS receives all application requests and translates them into the complex operations required to fulfill those requests. The DBMS hides much of the database’s internal complexity from the application programs and users.

The different advantages of DBMS are as follows.

1. Improved data sharing.
The DBMS helps create an environment in which end users have better access to more and better-managed data. Such access makes it possible for end users to respond quickly to changes in their environment.

2. Improved data security.
The more users access the data, the greater the risks of data security breaches. Corporations invest considerable amounts of time, effort, and money to ensure that corporate data are used properly. A DBMS provides a framework for better enforcement of data privacy and security policies.

3. Better data integration.
Wider access to well-managed data promotes an integrated view of the organization’s operations and a clearer view of the big picture. It becomes much easier to see how actions in one segment of the company affect other segments.

4. Minimized data inconsistency.
Data inconsistency exists when different versions of the same data appear in different places. For example, data inconsistency exists when a company’s sales department stores a sales representative’s name as “Bill Brown” and the company’s personnel department stores that same person’s name as “William G. Brown,” or when the company’s regional sales office shows the price of a product as $45.95 and its national sales office shows the same product’s price as $43.95. The probability of data inconsistency is greatly reduced in a properly designed database.

5. Improved data access.
The DBMS makes it possible to produce quick answers to ad hoc queries. From a database perspective, a query is a specific request issued to the DBMS for data manipulation—for example, to read or update the data. Simply put, a query is a question, and an ad hoc query is a spur-of-the-moment question. The DBMS sends back an answer (called the query result set) to the application. For example, end users, when dealing with large amounts of sales data, might want quick answers to questions (ad hoc queries) such as: - What was the dollar volume of sales by product during the past six months? - What is the sales bonus figure for each of our salespeople during the past three months? - How many of our customers have credit balances of $3,000 or more?

6.Improved decision making.
Better-managed data and improved data access make it possible to generate better-quality information, on which better decisions are based. The quality of the information generated depends on the quality of the underlying data. Data quality is a comprehensive approach to promoting the accuracy, validity, and timeliness of the data. While the DBMS does not guarantee data quality, it provides a framework to facilitate data quality initiatives.

7.Increased end-user productivity.
The availability of data, combined with the tools that transform data into usable information, empowers end users to make quick, informed decisions that can make the difference between success and failure in the global economy.

* Disadvantages of Database:

Although the database system yields considerable advantages over previous data management approaches, database systems do carry significant disadvantages. For example:

1. Increased costs.
Database systems require sophisticated hardware and software and highly skilled personnel. The cost of maintaining the hardware, software, and personnel required to operate and manage a database system can be substantial. Training, licensing, and regulation compliance costs are often overlooked when database systems are implemented.

2. Management complexity.
Database systems interface with many different technologies and have a significant impact on a company’s resources and culture. The changes introduced by the adoption of a database system must be properly managed to ensure that they help advance the company’s objectives. Given the fact that database systems hold crucial company data that are accessed from multiple sources, security issues must be assessed constantly.

3. Maintaining currency.
To maximize the efficiency of the database system, you must keep your system current. Therefore, you must perform frequent updates and apply the latest patches and security measures to all components. Because database technology advances rapidly, personnel training costs tend to be significant. Vendor dependence. Given the heavy investment in technology and personnel training, companies might be reluctant to change database vendors. As a consequence, vendors are less likely to offer pricing point advantages to existing customers, and those customers might be limited in their choice of database system components.

4. Frequent upgrade/replacement cycles.
DBMS vendors frequently upgrade their products by adding new functionality. Such new features often come bundled in new upgrade versions of the software. Some of these versions require hardware upgrades.

* Functions of DBMS

A DBMS performs several important functions that guarantee the integrity and consistency of the data in the database. Most of those functions are transparent to end users, and most can be achieved only through the use of a DBMS. They include data dictionary management, data storage management, data transformation and presentation, security management, multiuser access control, backup and recovery management, data integrity management, database access languages and application programming interfaces and database communication interfaces. Each of these functions is explained below.

1. Data dictionary management.
The DBMS stores definitions of the data elements and their relationships (metadata) in a data dictionary. In turn, all programs that access the data in the database work through the DBMS. The DBMS uses the data dictionary to look up the required data component structures and relationships, thus relieving you from having to code such complex relationships in each program. Additionally, any changes made in a database structure are automatically recorded in the data dictionary, thereby freeing you from having to modify all of the programs that access the changed structure. In other words, the DBMS provides data abstraction, and it removes structural and data dependence from the system.

2. Data storage management.
The DBMS creates and manages the complex structures required for data storage, thus relieving you from the difficult task of defining and programming the physical data characteristics. A modern DBMS provides storage not only for the data, but also for related data entry forms or screen definitions, report definitions, data validation rules, procedural code, structures to handle video and picture formats, and so on. Data storage management is also important for database performance tuning. Performance tuning relates to the activities that make the database perform more efficiently in terms of storage and access speed.

3. Data transformation and presentation.
The DBMS transforms entered data to conform to required data structures. The DBMS relieves you of the chore of making a distinction between the logical data format and the physical data format. That is, the DBMS formats the physically retrieved data to make it conform to the user’s logical expectations. For example, imagine an enterprise database used by a multinational company. An end user in England would expect to enter data such as July 11, 2010, as “11/07/2010.” In contrast, the same date would be entered in the United States as “07/11/2010.” Regardless of the data presentation format, the DBMS must manage the date in the proper format for each country.

4. Security management.
The DBMS creates a security system that enforces user security and data privacy. Security rules determine which users can access the database, which data items each user can access, and which data operations (read, add, delete, or modify) the user can perform. This is especially important in multiuser database systems.

5. Multiuser access control.
To provide data integrity and data consistency, the DBMS uses sophisticated algorithms to ensure that multiple users can access the database concurrently without compromising the integrity of the database.

6. Backup and recovery management.
The DBMS provides backup and data recovery to ensure data safety and integrity. Current DBMS systems provide special utilities that allow the DBA to perform routine and special backup and restore procedures. Recovery management deals with the recovery of the database after a failure, such as a bad sector in the disk or a power failure. Such capability is critical to preserving the database’s integrity.

7. Data integrity management.
The DBMS promotes and enforces integrity rules, thus minimizing data redundancy and maximizing data consistency. The data relationships stored in the data dictionary are used to enforce data integrity. Ensuring data integrity is especially important in transaction-oriented database systems.

8. Database access languages and application programming interfaces. The DBMS provides data access through a query language. A query language is a nonprocedural language—one that lets the user specify what must be done without having to specify how it is to be done. Structured Query Language (SQL) is the de facto query language and data access standard supported by the majority of DBMS vendors.

9. Database communication interfaces.
Current-generation DBMSs accept end-user requests via multiple, different network environments. For example, the DBMS might provide access to the database via the Internet through the use of Web browsers such as Mozilla Firefox or Microsoft Internet Explorer. In this environment, communications can be accomplished in several ways: - End users can generate answers to queries by filling in screen forms through their preferred Web browser. - The DBMS can automatically publish predefined reports on a Website. - The DBMS can connect to third-party systems to distribute information via e-mail or other productivity applications.

* Five Steps To Ensure The Success Of Your Database
Every year associations collectively invest millions of dollars to upgrade their current membership database systems or to buy brand new systems. Yet it seems that the more money we invest in our databases, the louder the protests are from staff, complaining that the database just doesn’t work. How can this be? There are five steps you can take to ensure that your database lives up to the expectations of its users. By addressing each of these areas, association executives and those responsible for the associations’ database can help to minimize complaining from staff and maximize the usefulness of the database.

Step #1: Review, refine and re-engineer business processes — Today’s increasingly sophisticated relational databases offer a myriad of functions: membership dues processing, events registration and invoicing, publications sales, fundraising, exhibit management, and many more. One element common to all is process. We define process as how information moves through the association and through the database. For example, we process membership dues by reviewing the membership application, entering the key data elements, creating an invoice, entering payment against the invoice, printing a welcome letter, sending a welcome packet, and so on. When contemplating a new database or an upgrade, completely review your business processes. This will help you determine if you have inefficient or improper (or just plain outdated) procedures in place. Discuss these processes with your association software developer. Your developer has created the software to handle each process in a particular manner. This may differ from yours. Find out what it is, and if you can adapt your process to fit more closely what the developer had in mind, without sacrificing your business practice, do it. This may require some fairly radical changes to how you do business internally, but in the long run will help to make the database more useful. The primary danger of implementing a new database without reviewing your business process is that you may wind up automating bad process. Bad process automated will continue to give you the wrong information, only faster than before. Don’t do it. (In addition, if bad process is causing your database to function improperly, you can bet the staff will blame the software.)

Step #2: Develop Proper Training and Documentation – These two go hand-in-hand and are perhaps the issues most commonly complained about by association executives. Once a review of the business process is complete and the installation or upgrade has begun, training and documentation become vital. Unfortunately, association software providers are able to provide only generalized documentation and training for their system.

Neither their training nor their documentation will be specific to your particular membership database and its special elements. This is where you need a specialist on the inside. Some associations are now creating full-time positions, such as a member records administrator or database coordinator. The position is responsible for providing documentation and training for the membership database. From processing new members, to changing membership entitlements, to simply changing an email address, this position is responsible for providing step-by-step instructions for each of the processes. If a full-time position is not possible, consider using part-time help. You may also want to contract with a database consultant. Have the consultant learn your processes and your database, and then work with staff to document processes and develop training materials.

Step #3: Develop and Rigidly Adhere to Data Standards – You may have heard the term GIGO; garbage in, garbage out. Simply put, this means that if the information you store in your database is not entered accurately or uniformly, no amount of reporting, consulting, or crying is going to give you the information you need. This is where data standards come into play. At one association we spent many months working with key database users developing a set of standards by which to enter all data. Not only do the data standards state explicitly how to enter data (e.g., Street, not St., PO, not P.O., etc.), but it also clearly explains where to enter data and what fields must absolutely be entered in order for a record to be complete. Standardizing data entry is critical for developing useful and accurate management reports. Without strict standards, sorting of data can become difficult if not impossible. For example, without clearly defined standards for titles, selecting and sorting on the title of vice president quickly becomes impossible, due to the myriad variations of this title (Vice President, VP, V.P., Vice Pres., etc.). With a data entry standard that clearly states that one enters the title vice president as “Vice President,” selecting on this title now becomes very easy. The same will be true of any other data fields you may choose to select or sort on.

Step #4: Continuously Collect Data – The old saying goes, the minute a directory is printed, it’s already out of date. The same is true for association databases. Depending on the industry or profession you serve, the addresses in your database may change as frequently as 5% per month. Over the course of the year, with no updating being done, you could have a database with over 50% incorrect information! So how do you keep up? Build data collection into the process. Develop a laundry list of methods, both passive and active, for collecting data to update your database. Some of the more obvious data collection mechanisms are publication order forms, meeting registration forms, and an annual directory update. But there are other methods for collecting and updating data in your database. These include establishing a web site that lets your members review their contact information, setting up computers with your database at your annual meeting for members to review, and working with your regions, chapters, or affiliates to assist you with data collection. Data is gold to an association. Keep your gold shining by actively and continuously seeking to update your data.

Step #5: Know What You Need and Want From Your Database — Too often, associations select an association database system based on what it is capable of doing, rather than on whether it does what the association needs it to do. Understanding what you need from your database is not only key in the selection of your database but is essential to its continued effectiveness. Typically there are several functions that a database needs to provide: contact management (address, phone, fax, email), membership tracking and invoicing, volunteer/committee activity, and more. But what are some of the other items you need to track now or may need to track in the future? How about meeting attendance, publications purchasing history, continuing education credits, or areas of interests? All of these issues require the collection of data. If you’re not collecting it, you can’t report it out, and you have to know you want to report it out to begin collecting it. Understanding what your needs are now, and anticipating what your needs may be in the future can go a long way toward creating an effective database. Be sure that you understand your needs and can articulate them to your software provider.

When you create a table or add a field to a table in the geodatabase, fields are created as a specific done on the data, as well as the way the data in that field is stored in the database. When you import data of one type into a field of another data type, you need to understand what the equivalent data types are between ArcSDE and your database management system (DBMS) because it can impact data content. Also, when creating new datasets in ArcGIS, it is helpful to know the equivalent data types between ArcGIS and your DBMS. For example, if you add a floating point (float) column to an existing feature class, that equates to a numeric data type column in a SQL Server database.

* Data types

Learn how data converts from one type to another.
File geodatabase data types are the same as ArcGIS data types. For DBMS products, though, data types can differ. The following sections contain information on how DBMS data types map to ArcGIS data types. Access data types

When you create a feature class or table in ArcGIS, there are 11 different data types available for each column. These types are mapped to Access data types in the following table.

DB2 data types
When you create a feature class or table in ArcGIS, there are 11 different data types available for each column. These types are mapped to DB2 types in the following table.

Informix data types
When you create a feature class or table in ArcGIS, there are 11 different data types available for each column. These types are mapped to Informix types in the following table.

Oracle data types
When you create a feature class or table in ArcGIS, there are 11 different data types available for each column. These types are mapped to Oracle types in the following table.

PostgreSQL data types
When you create a feature class or table in ArcGIS, there are 11 different data types available for each column. These types are mapped to PostgreSQL types in the following table. SQL Server data types

When you create a feature class or table in ArcGIS, there are 11 different data types available for each column. These types are mapped to SQL Server types in the following table.

Comments