The following is post that can help you understand all the database lingo you may hear from your database architects/engineers. I hope this post helps you keep up in those technical conversations in the work place! This post will cover: Data Mart, Operational Data Store, Data Dictionary, Metadata, Master Data Management, Business Intelligence, Index, Primary Key and Foreign Key
Data Mart
A data mart can be defined as “a repository of data that is designed to serve a particular community of knowledge workers.” (Rouse, 2014). In other words, a data mart is meant to meet the demands of a certain group of users, or employee within a company. An example of this is the Project Management Team or Human Resources. It can be considered as a subset of the organization’s current data warehouse.
Operational Data Store
An operational data store (ODS) can be defined as “a type of database that’s often used as an interim logical area for a data warehouse.” (Rouse, 2009) Data that is in the ODS is scrubbed, and checked for redundancies and compliance. This is where the data passes through before being stored in the data warehouse.
Data Dictionary
A data dictionary is “a collection of descriptions of the data objects or items in a data model for the benefit of programmers and others who need to refer to them.” (Rouse, 2005) As mentioned above, the data dictionary accompanies the physical design of the database. It provides a helpful guide in understanding the database and what the attributes and records are supposed to indicate.
Metadata
Metadata can be described as “data that describes other data.“ (Rouse, 2014). For my current job, we require the hospitals we integrate with to send over metadata for documents they send over their interfaces. An example of this includes author, file size, and modification date for a particular clinical document. More practical examples include web pages, which contains metadata regarding key words, authors, and page contents. Search engine make use of metadata when returning results to you.
Master Data Management
Master Data Management (MDM) is “a comprehensive method of enabling an enterprise to link all of its critical data to one file, called a master file, that provides a common point of reference.” (Rouse, 2010). Essentially, MDM allows data to be shared between departments and personnel. MDM comes to play in the business world when mergers are complete. Usually at the completion of mergers, more departments are introduced to the original business that use different reporting techniques and softwares. MDM essentially allows these departments to be synced to one “master” way of reporting.
Business Intelligence
Business Intelligence (BI) can be described as “a technology-driven process for analyzing data and presenting actionable information to help corporate executives, business managers and other end users make more informed business decisions.” (Rouse, 2008) Some BI tools include Microsoft Excel, Tableau, R Studio, etc. These type of tools enable users to collect, analyze and neatly present data in reports, dashboards or other types of data visualizations.
Index
An index is an ordered array of index key values and row ID values (pointers). Indexes are generally used to speed up and facilitate data. Indexes allow you to retrieve data more efficiently or to retrieve data ordered by a specific attribute or attributes. Indexes play a major role in the implementation of primary keys as well. They are useful for ensuring uniqueness of data values in a column and to facilitate lookups. I would recommend that the designer identify all the required indexes and determine the best type of organization to be use based on the data usage patterns and performance requirements. The designer should not create an index for every column in a table. Too many indexes will slow down INSERT, UPDATE and DELETE operations, especially if the table contains many rows. Some query optimizers will choose only one index to be the driving index for a query, even if your query uses condition in many different indexed columns.
Primary Key
A primary key is an attribute or combination of attributes that uniquely identify any given row. A primary key must must uniquely identify all other attribute values in any given row. It can not contain null entries. A primary key should have the minimum number of attributes possible. Unique values can be better managed when they are numeric and thus, a primary key is better served as being numeric. A primary key should not change over time. A foreign key is an attribute or attributes in one table whose values must match the primary key in another table or whose values must be null.
Foreign Key
Foreign Key is usually mandatory in the entity relationship.