Kutz M. Handbook of materials selection

Подождите немного. Документ загружается.

3 CREATING THE DATABASE 485

3.1 Deﬁning the Project Team

In creating the project team for designing and creating the databases, the fol-

lowing functions are suggested. It is highly recommended to have one individual,

generally the project leader, who provides the focal point for all communications

involved in the database design and building process.

The project leader must champion the cause, organize the project, and co-

ordinate the effort. This person sets the milestones, establishes acceptance cri-

teria, periodically reviews the project, and puts in place the approval procedures.

The end user deﬁnes the end use of the database, which must supply all

necessary information in a usable form. Therefore, the end users should be

involved in every level of database design and development. Their input deter-

mines what materials and what information for each material are stored in the

database, how the data is stored, how the data is retrieved, how the user interface

is customized, how the data is exported, and what unit conversions are required.

Note that a database may be developed primarily for the organization and storage

of data. In this case, the end user is often also the data provider.

The data provider is a focal point for all data sources. This person is respon-

sible for gathering the information from all data sources required to meet the

needs of the end users. He compiles the data, identiﬁes and documents the

storage media and format, and organizes it for entry into the database. Input

from the data provider helps to determine the most efﬁcient methods for data

entry.

The programmer is responsible for translating electronically formatted data

into formatted ﬁles for loading data into the database. This person is also valued

for his expertise if data reduction or manipulation is required prior to data entry.

The database builder creates the database using the tools provided by the

DBMS application. The database builder compiles all the information provided

by the end users, creates the schema design, creates or compiles the data loading

ﬁles and/or techniques, initializes and loads the database, creates all required

auxiliary ﬁles, and corrects errors discovered during the quality assurance pro-

cess.

3.2 Deﬁning the End User Data Requirements

While efﬁcient data storage may seem to be the purpose of a database system,

the utility of the database is severely limited if it is not designed to meet the

needs of the end use application(s). Therefore, a clear understanding of both the

short-term and long-term end user applications is required.

For each application identiﬁed in the initial planning phase, specify the fol-

lowing materials data storage requirements should be speciﬁed:

●

What materials are to be included?

●

What information is applied globally to all end users?

●

What pedigree information is required for each material?

●

What properties are required for each material to optimize use of the

database by the end user?

●

What information is best provided as footnotes or metadata? (i.e., it is too

sparse to require an attribute or too irregular to categorize)

486 MANAGING MATERIALS DATA

●

What are the user access privileges? Is some data restricted?

●

What assumptions must be avoided?

●

How often must this data be updated?

3.3 Deﬁning the Functional Requirements

Most of the functional requirements are a result of or deﬁned by the end user

application. When deﬁning the functional requirements, try to anticipate future

applications for the database. Typically, the following questions should be an-

swered:

●

What types of queries (groupings of subsets of data) or data searches, list

them if possible, will be performed on the database?

●

What information is required to adequately identify the material records

returned by the query or search?

●

Will curve, table, or matrix data need to be represented?

●

Will data be mathematically manipulated, required by pre- or postpro-

cessing, and what is the required accuracy?

●

What information is routinely accessed?

●

Will the data be exchanged with or exported to other applications?

●

What will be the acceptance criteria? Deﬁne the individuals responsible

for approval.

3.4 Designing the Database

Materials data management systems typically have similar interface require-

ments, but the underlying database will be one of three common structures:

relational, hierarchical, or a hybrid of the two. The following discussion relates

to the process of creating a hybrid hierarchical-relational database. Comments

are provided for their adaptation to relational databases.

Before beginning the database design, it must be determined whether to store

the data in one database to be accessed by all end users or to divide the data

into separate databases. This relates to decisions regarding the requirement for

a distributed database system. Note that data can be stored in one database and

then subdivided into separated databases for the individual end use applications.

This often makes the process of updating the databases simpler. Some of the

questions to be answered include:

●

Will the total volume of data be unwieldy? Smaller databases are always

faster and easier to update.

●

Are the end uses similar or radically different? Is one or many databases

expected by the end user?

●

Will the data sources be updated on disparate schedules (i.e., archival data

vs. ongoing test data)?

●

How well do the categories of attributes that deﬁne the materials from

different end users overlap? Can multiple databases use the same schema?

3 CREATING THE DATABASE 487

●

Are the materials properties tested to similar standards?

●

Does the database size pose a problem for any of the end users based on

performance requirements?

Having developed an understanding of the end users and how they intend to

use the database and data, it can then be determined which elements are to be

stored in the database (or each database). More data may be available than is

required to meet the end user requirements. Data that is not speciﬁcally required

by any of the end users should be evaluated for its potential utility before being

added to the database. Often it is desirable to keep all available data in one

place as an ofﬁcial repository.

Once all data elements are listed, the relationship between the elements is

established. This information is used for the development of the database

schema, which deﬁnes how the data is structured within the database. The fol-

lowing information, preferably compiled in sequence, is required for each ap-

plication.

1. The Data Elements (Attributes). Database attributes are a numerical, tex-

tual, or graphical representation of the data. Textual attributes are useful for

storing documents related to materials, tests, test data, property data, engineering

speciﬁcations, revision histories, etc. Images are often included to demonstrate

test layouts, engineering drawings, photomicrographs of test results, photos of

specimens or products, etc.

All attributes that are required to provide the end user with the information

necessary to qualify material property values and to provide the property values

themselves for the materials in the database must be identiﬁed. These attributes

should serve one of the following functions:

●

To characterize the material to the level required by the user. The Amer-

ican Society for Testing and Materials (ASTM) and other organizations

provide standards for accurately characterizing materials.

●

To identify the source of the data. Data provided by different organizations

may include the name of the organization, address, phone, test employee

identiﬁers, etc. Validated data may require different parameters than test

qualiﬁcation. In-house or in-process test data may include the test group

and employee identiﬁcation or similar parameters for tracking purposes.

●

To identify test parameters, conditions, methods, and the details of the

test specimen. ASTM provides standards for identifying and qualifying

test methods.

●

To identify the property values to be stored in the database for the ma-

terials. This includes single-point scalar and test values, curves (arrays),

images, and documents.

2. The Attribute Deﬁnitions. The attribute names and deﬁnitions provide a

dictionary relating the attributes to terminology easily understood by the end

user. These names and deﬁnitions are used in the schema that deﬁnes the da-

tabase structure, to create customization ﬁles, and in quality control procedures.

488 MANAGING MATERIALS DATA

Relational databases typically use ﬁeld speciﬁcations to deﬁne attributes. Field

speciﬁcations are composed of ﬁve types of elements: general elements, physical

elements, logical elements, and speciﬁcation information. Field-level and data-

level integrity are established using thorough deﬁnition of the ﬁeld speciﬁcations

for each attribute.

Attribute deﬁnitions should include, as a minimum, the following information

for a materials database:

●

Attribute Type (Physical Element). The attribute type determines the form

by which data can be searched and how it can be manipulated. This may

include character strings, integers, real numbers, curves, matrices, text

ﬁles, images, etc.

●

Units (Logical Element). A consistent unit system must be provided for

the entire database. The units must be consistent for each instance of an

attribute in the database. Unit conversions may be required to provide the

end user with useful information in the units system required by his ap-

plication.

●

Description (General Element). The attribute should provide a description

of the attribute name in terms the end user understands. This label is

typically used in displaying and searching data.

●

Numerical Precision (Physical Element). The attribute precision provides

the number of signiﬁcant digits to which data is to be stored in the da-

tabase and number of signiﬁcant digits to which data will be displayed.

This applies to all real, curve, and matrix values. American Iron and Steel

Institute (AISI) provides standards for precision of computed values.

●

Constraints for Allowable Values (Logical Element). Determine and record

data constraints to identify out-of-range data during the data entry process.

Also determine existence constraints when the data must be supplied for

a material or property in order for it to be a valid entry in the database.

This information can be used for data translation, database building, and

quality assurance.

●

Attributes for Thesaurus or On-Line Help (General Element). Identify in-

formation relating to the attributes that should be provided as end user

documentation.

●

Test Speciﬁcations (General Element). Identify and record test speciﬁca-

tions, conditions, or identiﬁcation that applies to all instances of property

attributes.

●

Quality Indicators (Speciﬁcation Information). Quality (statistical) indi-

cators or qualiﬁers required to qualify the data for the end user should be

provided for property data. For example, the National Institute of Stan-

dards and Technology (NIST) provides the following guidelines for use

of its on-line Ceramics WebBook, a collection of property values derived

from surveys of published data

Certiﬁed (standard reference values)

Validated (conﬁrmed via correlations and models)

Evaluated (basic acceptance criteria satisﬁed)

Commercial (manufacturer’s data)

3 CREATING THE DATABASE 489

Typical (derived from surveys from values for nominally similar ma-

terials)

Research (preliminary values; work in progress)

Unevaluated (all other data)

3. The Attribute Groups. Having identiﬁed the attributes to be included in

the database, they are now grouped by common characteristics in the context of

the end user. Typical groups of attributes are specimen identiﬁcation, material

composition, specimen condition, specimen preparation, test conditions, me-

chanical properties, electrical properties, physical properties, etc. These groups

of attributes are referred to as relations or tables.

It is important to verify that all the attributes belong to an appropriate relation.

If the database has more than one end use application, the requirements for each

are integrated, while avoiding redundancy. Additional tables may be added to

improve data normalization.

Purely relational databases require the addition of key attributes to each re-

lation that uniquely deﬁne each row in the table. These keys are used to link

the tables for the purposes of uniquely identifying a material in the database

and querying information from the database. Hierarchical databases use a com-

bined set of attribute values for a material stored in the database to uniquely

deﬁne the material record.

4. The Relation Names for Attribute Groups. A name must be assigned to

each group of attributes using a term that best characterizes the grouping. Re-

lation names are very important in relational databases, as they are speciﬁed in

the SQL queries used to retrieve data from the database. Therefore, they must

be unique, descriptive, and unambiguous. Relation names are not as important

in hierarchical databases because they are not typically used in querying, but it

is good practice to maintain their uniqueness.

5. The Relationship between Relations. For databases with a hierarchical

structure, the relationship between the groups of attributes deﬁnes the hierarchy

(parent–child relationships) of the database, dictating the structure for the

schema design. This hierarchy provides the path to the property data. The re-

lations should be separated into two groups: those that identify characteristics

for the materials and those that specify properties of the materials. The tables

storing deﬁning characteristics are typically ordered according to the level of

material differentiation the data provides, from the least differentiation (i.e., ma-

terial, test type, component) to the highest (i.e., test conditions, source data).

The relations that contain attributes that deﬁne the actual property data are

grouped into tables by type and are typically on the same level at the bottom of

the hierarchy.

For relational databases, the relationship between the groups of attributes

establishes the connection between relations that are logically related to each

other in some form or manner.

These relations are used to reﬁne table struc-

tures, minimize redundant data, allow data to be drawn together simultaneously,

and provide for relationship-level integrity.

Hierarchical database relations generally have one-to-many relationships pro-

gressing down the levels of the schema. Relational databases usually have one

or more of three types of relationships: one-to-one, one-to-many, and many-to-

many.

490 MANAGING MATERIALS DATA

6. The Schema. The information gathered in the previous steps is organized

to create the schema for a hierarchical database or can be used to establish the

relationships and create the ﬁeld speciﬁcations for a relational database. This

step must be performed before the actual data is loaded into the database.

3.5 Developing a Prototype Database

All the accumulated knowledge, expertise, and information are now used to

create a prototype database. This database aids in clarifying goals, identifying

problems in the design or content, identifying additional resources or information

required by the end user, establishing the end users’ expectations, and reﬁning

the project.

The prototype database should be built from a small subset of the data to be

stored in the database. This subset should include at least one example of each

of the attributes deﬁned in the schema. If data is obtained from several different

sources or serves different end users, the prototype database should contain at

least one complete data set from each source and for each end user. If curves

or graphs are to be included in the database, they should be included in the

prototype to verify presentation. The more realistic and comprehensive the pro-

totype, the more useful it will be.

When the prototype database is available, a group of end users is organized

to evaluate it. While each user may not be completely familiar with all the

capabilities of the application software, their input will be valuable in determin-

ing whether the attributes and database structure are complete and usable. A

questionnaire derived from the project requirements can be used to facilitate the

evaluation process.

Using the information gained from evaluation of the prototype, the schema

is revised, the loading ﬁles modiﬁed, and the database recompiled. The evalu-

ation, revision, and rebuild cycle is continued until the database is determined

to be acceptable. This prototype database should be archived for future reference

by moving it to another directory or renaming it.

While this iterative approach of database design, load, evaluate, and re-design

may seem time-consuming, it has been proven to be the best method for creating

a database that best meets the end users’ requirements and is the most efﬁcient

method in the long run. The most time-consuming aspect of creating a database

usually involves formatting and inputting the actual data. Revising the ﬁnal da-

tabase can mean revision of numerous large ﬁles.

The ﬁnal prototype should be subject to a ﬁnal review before data entry is

begun. Designating a review committee and formalizing the procedure will help

to ensure a database that meets the acceptance criteria. Final approval should

include a consensus of the end users. If the database is too difﬁcult to use or

does not provide a real beneﬁt over their current methods, they will continue to

use what is familiar to them.

3.6 Populating the Database

The data provider coordinates the effort of formatting the data for entry into the

database. Data often comes from disparate sources and is stored in a myriad of

media and formats. The process of entering or translating data into the database

should include documenting the status of the source data and establishing the

most efﬁcient method of loading it into the database

3 CREATING THE DATABASE 491

The individual values for each attribute may come from different sources. For

each attribute, the data provider should identify:

●

Where the source data is located

●

How to acquire a usable copy of the source data

●

What media it is stored on or will be delivered on

●

What format it is stored in and in what format it will be delivered in

To increase the efﬁciency of the data loading process, it is best to group the

data by format and media. Each format and media will have a particular data

entry process or method associated with it. Because data can be entered from

an unlimited number and type of data ﬁles, it is generally easy to accommodate

data stored or delivered in different forms.

Data entry of information stored on paper may be simpliﬁed by ﬁrst trans-

ferring it to a standard form. Future data acquisition for the same type of data

could be entered directly into the same form. This aids in assuring data consis-

tency and completeness. It may also be useful to store some information as

documents, which can be accessed in any form or location. Web-enabled appli-

cations have greatly increased the usefulness of this method.

For each group of data, the optimum procedure for data entry or transfer of

data into the database must be established. There are several ways to load data

into a database, largely dependent on the software application:

1. Load Files. Load ﬁles are typically formatted in ASCII text ﬁles but may

be any ﬁle that is used to populate materials property values into a da-

tabase. They can be created manually using any word processor or text

editor or translated from another electronic format.

2. Spreadsheets. Spreadsheet applications are often used to process raw data

and to load that data into a database. This method is particularly appro-

priate for relational databases, which are stored in spreadsheet-like tables.

3. Manual Entry. Disparate bits of data or minor revisions can usually be

keyed directly into the database using the software application interface.

4. EXPRESS/XML/MATML. These standard formats are useful for transfer-

ring data from existing software or database applications, if supported by

the individual applications.

Having established the best methods for loading data from each data source,

populate the database with the material property values required for each end

use application.

3.7 Building the Database

Using the best methods for the speciﬁed database software application, a ﬁnal

production database is compiled for distribution to the end users. All standard

database-building practices should be observed and incorporated. These tech-

niques are unique to the type of database used and are well documented in the

database administration literature.

492 MANAGING MATERIALS DATA

It is important to backup the ﬁnal database and to control versioning. The

security of data sources and the ﬁnal database should be given appropriate con-

sideration at this point, as these items represent a signiﬁcant investment.

3.8 Customizing the User Interface

The information gathered in the discovery phase of the database building process

can be used to customize the user interface, if required. Most user interface

customizations are accomplished with ﬁles that are external to the database.

Customizations can include creating data-speciﬁc reports or creating views (vir-

tual tables) that can be applied globally or on an individual basis. In most cases,

these user interface customizations involve queries that are executed to extract

subsets of data. Some typical database-related user interface customizations that

may have an impact on the database structure, deﬁnition, or organization include:

Units Conversion. Database values are generally entered and stored in the

units system in which they were measured. Different end-user applications

may require a different unit system. The database or user interface software

must accommodate this units conversion.

Export/Import Data Mapping and Reports. There are numerous uses for the

data extracted from the database. The end use can be simply creating re-

ports for viewing on screen or in hardcopy. Often it involves importing or

exporting data to external software programs, such as analysis codes. Data

manipulation may be involved during export to transform the database data

into the formats required by the external program. The required transfor-

mations or mappings may inﬂuence the data type, units, or accuracy of

values stored in the database.

Database Documentation. It is good practice to provide documentation to the

end user that details the database structure and content. This documentation

assists users in understanding the database and provides disclaimers or data

qualiﬁcations.

Indexing Database. The performance of access and query functions is im-

proved signiﬁcantly by indexing the database. Indexing is not required, but

highly recommended.

3.9 Qualifying the Database

The completed database and all associated interface customizations should meet

or exceed established acceptance criteria. Quality assurance measures the data-

base against predetermined acceptance criteria and generates feedback to the

project team. The project team corrects errors and rebuilds the database. This

process is repeated until the ﬁnished database and associated interface custom-

izations meet the acceptance criteria. When the database has passed the quality

assurance process, the database is submitted for ﬁnal approval.

Quality assurance should view the database and interface customizations from

the end user perspective, while understanding the source data, test methods, etc.

If acceptance criteria were not deﬁned as part of the project-planning phase,

assemble the project team and carefully deﬁne the criteria at the earliest oppor-

tunity. Do not wait until the project is complete before deﬁning the requirements.

3 CREATING THE DATABASE 493

Every member of the project team can provide input to the quality assurance

process. However, it is probably best that the focal point for this activity not be

the database builder. Just as it is difﬁcult to edit one’s own documents, often

others can more easily discover errors in the building process. The quality as-

surance team is analogous in its functions to the database-building team:

●

The project leader provides a focal point for all communications involved

in the quality assurance process and coordinates the effort. He reviews the

acceptance criteria and ﬁnalizes the approval procedures.

●

End users verify that the database meets speciﬁed requirements. For ex-

ample, that the database supplies all necessary information in usable for-

mat, that the required materials and information for each material is

available, that data is easily retrieved in a timely manner, that data trans-

fers accurately to the end-use applications, and that required units con-

versions are performed.

●

The data provider veriﬁes that all speciﬁed data was transferred and is

stored efﬁciently and accurately. In addition, he should be available for

veriﬁcation and validation of property.

●

The programmer may be required to modify data, translators, mapping

ﬁles, etc. if the data validation process indicates that data translation or

data reduction errors exist.

●

The database builder corrects errors in the schema, load ﬁles, and custom-

ization ﬁles, compiles the database, and resubmits it to quality assurance.

Acceptance Criteria. The following database acceptance criteria, speciﬁc to

materials data management systems, provide a minimum set of standards:

●

Does the database contain all the intended materials and data? Verify that

attributes have been created and populated for all the properties, materials,

and pedigree information requested by each end user to support each end-

use application.

●

Are commonly used queries included in the user interface? Commonly

used queries should be readily available in the user interface. Verify that

these queries exist, are intuitive, and return values reliably and in a timely

manner.

●

Is the data in the database stored correctly? Verify that curve data can be

extracted and manipulated, that data is stored in the correct format for the

required queries, and that attribute values are within established con-

straints.

●

Can data be exported or transferred to the required end-use applications?

Verify that exported values are mapped correctly.

●

Are units conversions performed correctly and accurately? Convert units

for all attributes in the database; check the returned values and conversion

factor for each attribute and each unit system.

494 MANAGING MATERIALS DATA

4 COMMERCIAL DATABASE MANAGEMENT SYSTEMS

Many manufacturing organizations have programmers on staff, so designing a

database management software system in-house is often an option. Cost, sched-

ule, and maintenance issues can quickly eliminate this option. Most commercial

DBMS providers can provide volumes of testimony from their customer base

regarding the failures of in-house DBMS solutions. ‘‘Off-the-shelf’’ applications,

customized to meet the speciﬁc database and interface requirements of the or-

ganization usually prove to be most cost and time effective.

In-house personnel, using the application programmatic interface (API) sup-

plied with the software, can often readily accomplish customizing a commercial

DBMS system. Additionally, most DBMS providers offer customization services

as part of an implementation or installation package. Using this approach, in-

house personnel can be trained and ready to use the installed DBMS system on

site.

Before examining the systems commercially available, it is recommended that

you carefully review the requirements for your organization. In addition to stor-

age of in-house data, the following should be considered:

●

Whether commercial data is available for use with the DBMS.

●

What hardware platforms are available and supported in-house.

●

Whether plans exist to link to a PDM system. If not, identify requirements

for interfacing between your DBMS and analysis or design software.

●

Whether interacting with the supply chain is important to the efﬁciency

of your organization.

●

Whether the DBMS will store all required data types.

●

Whether you intend to disperse your data locally or globally.

●

Whether the DBMS supports materials data standards embraced by your

organization and industry.

The following systems were commercially available at the time of this writing.

This is not an exhaustive list, but a compilation of several different approaches

to the problem of materials data management. Most are supplied by companies

that also offer complete implementation services. The following excerpts are

derived from product literature.

MSC.Mvision and MSC.Enterprise Mvision, by MSC.Software Corpora-

tion. MSC.Mvision is a suite of software applications based on a proprietary

database system that is optimized for engineering materials data. Designed and

built speciﬁcally for engineering applications, they include integrated X-Y

graphics, printing, spreadsheet, query language, and web browser capabilities.

The applications easily handle complex engineering data types such as numerical

values with units, footnotes, numerical precision, images, photographs, and doc-

uments.

MSC.Enterprise Mvision provides web-enabled access to MSC.Mvision da-

tabases. Using a client-server conﬁguration and a standard web browser, users