Databases

Before I get into today’s topic I’d like to mention a software project I am working on. For those of you who have subscribed to my blog via Mastering The Machine or otherwise, I have already sent you an e-mail. I am doing a validation study on whether this enterprise software package would be a viable commercial product. It is designed to assist engineers who quote, design and program custom or OEM machinery.

I have a short questionnaire that I will send to anyone interested in providing some feedback. After returning it to me I will send a description of what the software is about, but I don’t want to bias your answers.

And now on to the topic at hand… I have pulled this excerpt directly from my book.

*****************************************************************************

Automation systems must often save data in an organized and easily retrievable way. Production data—such as machine OEE, operator log-in, product and password management, and historical information on each machine—are often managed through data exchange with a database, which is simply an organized collection of data. Information is stored in a way that it can easily be accessed by categories at a later date. Pieces of data can relate to each other in various ways. Correlations of one type of information with another must be made in a meaningful way from the factory floor or from within management software.

Data is usually categorized in classifications that can support the process of relating the information pieces to each other and drawing conclusions from it later. An example might be relating machine faults to product selection to determine if a machine may need mechanical work on a station to accommodate a specific product.
Here is another example of how a database might be used in a business scenario. An engineer keeps a schedule of critical production and task dates for each job number. Purchasing uses a spreadsheet with component part numbers and delivery dates, also by job number. A project manager then uses a database to enter customer information and link to the engineer and purchasing data sets by using the job number as the primary key. Reports can then be generated using this information for a specific project, perhaps by determining whether a project schedule slipped because of late part shipments or other causes. By changing the primary key to look at reports by vendor, comparisons can be made to track on-time and correct part shipment percentages.

A database is technically only the data and its structures, not the management and relational aspects that control it. The engines that actually perform searches and access data are contained within the database management system (DBMS). The data and DBMS together are called a database system.

Commonly used database management systems include Microsoft Access and SQL Server, Oracle, IBM DB2, and several SQL-based variants. SQL is an abbreviation for Structured Query Language, a method of relating data categories in multiple ways. Object-oriented and object-relational databases often use a query language called Object Query Language (OQL), which uses many of the same rules, grammar, and keywords as SQL. A DBMS is required to manage data according to availability to multiple users simultaneously, accuracy, usability or user-friendliness, and resilience or recovery from errors. This can be a complex task, and systems often connect many different servers and data collections. Nearly every aspect of business uses databases in various ways and on different platforms that often do not communicate directly. This requires additional software to perform translation and control of data and its acquisition, usually using standards like SQL and ODBC together.
Databases can be classified in a number of ways—by their content, such as text, images, or file types, or by their application area, such as production, accounting, or maintenance. The term may also refer to the logical programming and data retrieval aspect or the data content in computer storage. Following are some of the different types of database in use.

A relational database stores data in rows and columns. Each row has a primary key that uniquely identifies each record in the table. It can either be a normal attribute that is guaranteed to be different for each record, such as a social security number, or it can be generated by the DBMS. Each column also must have a unique name. Access, MySQL, and SQL Server are examples of relational databases.

An object-oriented database (OODB) stores data in objects. Objects are items that contain data as well as the procedures that read or manipulate it. Members of the database are objects that might contain several related data characteristics, such as name, address, and age, as well as the instructions for how to print the record or a formula to calculate a member’s paycheck. A relational database would contain only the data about the member. GemFire and Versant are two OODBs.

There are several programs that are known as object-relational databases, which have the ability to do both. Examples of these are DB2, Oracle, and Visual FoxPro.
A multidimensional database stores data in dimensions. While a relational database uses columns and rows (two dimensions), a multidimensional database allows for more than two dimensions, allowing users to access and analyze the view from any aspect. This multidimensional table is sometimes called a “hypercube.” The number of dimensions used will depend on the requirements of the application. An example might be a database with product, manufacturer, vendor, time, and the model of machine it is used on. A user would be able to look up data by date, product number, vendor, and so on. Multidimensional databases are also faster at consolidating data than relational databases. A query that takes minutes to process in a relational database may take seconds in a multidimensional database. A well-known multidimensional database is Oracle Express.

Databases are also classified by their content. A multimedia database stores images, audio clips, and video clips. A voice mail system database is an example. A groupware database stores schedules, calendars, manuals, memos, and reports. Searching a schedule for available rooms or meeting times would use a groupware database. A CAD database stores data about engineering designs and drawings. It might include lists of components, relationships between parts or drawings, and drawing revision data.

When designing a database, it is a good idea to follow some basic guidelines. First, the purpose of the database must be determined. This will help the designer with what kind of information will be required. Next, the tables or files should be designed. Each table should contain data about one subject. For example, the product file should only contain data about the product. Third, the records and fields are designed for each table or file. Each record should have a primary key. Separate fields should be used for logically distinct items; a name, for instance, should have fields for title, first, last, and so on. Fields should not be created for information that can be derived from other fields (for example, the field for age can be derived from a birth date field). After the tables and files are completed, the relationships between them can be determined, completing the design.

For database users, macros can be used to remember sequences of operations, automating repetitive functions. These macros can be saved and reused any time a similar task needs to be performed, creating easily modified user tools.
*****************************************************************

The software package I am trying to develop makes extensive use of databases. To educate myself further on the subject I am reading an excellent book on the subject: “Database Design For Mere Mortals”, by Michael J. Hernandez and published by Addison-Wesley. This project is far too large for me to accomplish by myself, so I am also using various software development resources here in Nashville. I am also a member of The Entrepreneur Center, also in Nashville. They have also been very helpful in guiding me through a very complex process.

If you are interested in knowing more about this project or becoming involved, let me know!