Relational Databases
CRM (Customer Relationship Management)
An information system that is used to plan, schedule, and control the presales and post-sales activities in an organization. SFDC (SalesForce.com) is one example of CRM. Siebel (Oracle CRM) and SageCRM are others. SugarCRM is an open source CRM solution.
Data Warehouse - ETL
ETL (Extract, Transform, & Load) The functions performed when pulling data out of one database and placing it into another of a different type. ETL is used to migrate data, often from relational databases into decision support systems (Data Warehouses) for business intelligence usage. Data is extracted from operational databases. The data is validated, cleansed, transformed, aggregated and is loaded into the data warehouse.
Small Business DBMS Examples
Microsoft Access is also a cross-platform database application. It comes bundled with Microsoft Office Professional or in a standalone version. FileMaker Pro is a cross-platform database application from FileMaker Inc., known for its combination of power and ease of use. Databases are built from Wizards (templates) which require little to no knowledge of SQL.
Data Warehouse - Business Intelligence Cont
Often BI applications use data gathered from a data warehouse or a data mart. However, not all data warehouses are used for business intelligence nor do all business intelligence applications require a data warehouse. Examples Reporting & querying SW OLAP & Data mining Business performance management Examples Crystal Reports, Cognos, SSRS Microsoft Analysis Server, Hyperion, Cognos, Business Objects, SAP BI, Microstrategy, OBIEE EIS, DSS, business dashboards
PL/SQL (Procedural Language/SQL)
PL/SQL (Procedural Language/SQL) Oracle's extension for their databases T-SQL (Transact - SQL) Also seen as MSSQL (Microsoft SQL) Allows Microsoft SQL Server compatibility
Key Terms: Relational Databases
Relational Database CRM ERP MRP OLTP Data Warehouse Data Mart ETL Business Intelligence (BI) Enterprise DBMS Oracle, IBM, MySQL, SAP, MS SQL Server Job Titles & Functions Database Admin / Engineer / Architect Data Analyst
Enterprise DBMS Examples 4
SAP(System Application & Products) is the world's 3rd largest software company located in Germany. Most common product is SAP Business Suite. Close Source, multiplatform (Windows, Mac OS, & UNIX) RDBMS for enterprise, customer relationship, and supply chain management. Coded in C/C++ and SQL.
SQL - (Structured Query Language)
SQL - (Structured Query Language) Pronounced "SQL" or "sequel," a language used to interrogate and process data in a relational database. SQL commands can be used to interactively work with a database or can be embedded within a programming language to interface to a database.
Example of Relational Database
Sourcers Table - Name, ID, Team Metrics Table - ID, Calls, Hire, Resume Data in a database can be queried or interrogated
What is a Relational Database?
The term database refers to a collection of related records. All databases need a DBMS to interface with the database file. Most of these databases can also be queried with SQL, which is a high level 4th generation database programming language used to interrogate and process data in a relational database.
Major Database Companies/Brands
Today, DBMSs can manage any form of data including text, images, sound and video. Oracle is one of the most popular database makers. IBM, Microsoft, SAP and MySQL are the next biggest players in large enterprise databases. Some database software is for use on personal computers (FileMaker, Microsoft Access).
Where is a Data Warehouse housed?
Typically, a data warehouse is housed on an enterprise mainframe server. Data from various applications and sources is selectively extracted and organized on the data warehouse database for use by analytical applications and business intelligence. Applications of data warehouses include data mining, web mining, and decision support systems (DSS). Characteristics Subject Oriented - a concentrated subject to analyze Integrated - resolves data discrepancies from multiple sources Nonvolatile - data does not change after being entered in the warehouse Time Variant - focus on data with regards to change over time
DBMS Examples
4D Adabas Adaptive Server Enterprise Apache Derby Corel Paradox C-tree Dataflex Dataphor DB2 FileMaker Firebird Hadoop (Map/Reduce) Helix database HSQLDB Hyperion Essbase Cloudscape Information Management System Informix Ingres Intersystem Cache Kx Microsoft Access Microsoft SQL Server MySQL Netezza NoSQL OpenOffice.org Base Oracle OpenLink Virtuoso PostgreSQL Progress Rel (DBMS) SQLite SQL Anywhere Studio Sybase Teradata VistaDB
What is a Data Warehouse?
A Data Warehouse is a central repository for all data that an enterprise's various business systems collect. The data warehouse is designed to support decision making in an organization
Data Warehouse - Data Mart
A data mart is a subset of a data warehouse for a single department or function. A data mart may have tens of gigabytes of data rather than hundreds of gigabytes for the entire enterprise.
Enterprise DBMS Examples 1
A relational database management system (RDBMS) from Oracle, which runs on more than 80 platforms. The Oracle database is Oracle's flagship product, and the current versions are Oracle 11g (grid) for installed systems and Oracle 12c (cloud) for cloud systems. Typically used for enterprises, financial systems and web databases. When people refer to working with an Oracle database, this can mean: The collected data is managed by an Oracle RDBMS The Oracle RDBMS/database as a whole The Oracle RDBMS software that manages the data
Enterprise DBMS Examples 3
A very popular open source, multiplatform RDBMS from MySQL AB, (a Swedish company now owned by Oracle). Widely used as the database for Web applications; MySQL is available for free under the GNU license and is the most widely used open source RDBMS. MySQL includes a C/C++ interface. There are also ODBC (Open Database Connectivity) and JDBC (Java Database Connectivity) drivers available; allowing MySQL to connect to open source and Java based programs.
MRP (Material Requirements Planning)
An information system that determines what finished products must be built, which pieces (raw materials or parts) are needed, how many of them must be procured in order to build the product, and how quickly all these items can be acquired. It queries the bill of materials (bom) and inventory databases to derive the necessary elements.
ERP (Enterprise Resource Planning)
An information system that serves all departments within an enterprise. Allows separate database systems/modules to be referenced against each other. Major players in this space are: Oracle (Oracle E-Business Suite OR Oracle EBS) SAP (SAP R/3 OR SAP ECC OR SAP ERP) Microsoft Dynamics ERP System: FRM Finance Resource Management SCM Supply Chain Management HRM Human Resource Management CRM Customer Relationship Management MRP Manufacturing Resource Planning
Data Warehouse - Business Intelligence
Business intelligence (BI) refers to skills, technologies, applications and practices used to help a business acquire a better understanding of its commercial context. BI technologies provide historical, current, and predictive views of business operations. Common functions of business intelligence technologies are: OLAP Benchmarking Predictive Analytics Data Mining Text Mining General Reporting
Enterprise DBMS Examples 2
DB2 is IBM's family of information management software products. Most often, though, when people say DB2 they are referring to IBM's flagship relational database management system, DB2 Universal Database (DB2 UDB). DB2 was one of the first databases use SQL.
Job Titles & Functions 1
Data Architect or Data Modeler Is someone who can understand all the sources of data and work out a plan for integrating, centralizing and maintaining all the data. They are involved with developing metadata, defining enterprise use, logic data models are established. Decides what data is collected and how it will be used and stored. Database Architect or Database Engineer Is an individual who is responsible for creating and managing the data aspects within a database architecture. They are generally responsible for designing, developing, and implementing databases; ensuring the integrity of data; and working with a variety of database management tools. Decides what Database will be used and builds it.
What is a Relational Database?
Data is organized in tables. Tables consist of rows and columns. Finds common identifiers from different databases to pull records - That relate to each other
Job Titles & Functions 2
Database Admin Is a person responsible for the installation, configuration, upgrading, administration, monitoring, maintenance, and security of databases in an organization Maintains and supports an existing database. Data Analysts (Sometimes called Reporting Analysts) create, modify, and/or run database reports. Uses Excel, Access, or other specialized tools such as Business Objects (by SAP), Oracle Business Intelligence (also known as OBIEE or OBI) or Crystal Reports
Enterprise DBMS Examples 5
Microsoft SQL Server is a relational database management system (RDBMS) produced by Microsoft. It is the database backend to the .NET platform Microsoft SQL Azure is the cloud version of the database. Both SQL and T-SQL (a Microsoft specific language) can be used to make queries in the database.
OLTP for ERP, CRM, and MRP
OLTP (Online Transactional Processing) updates the appropriate ERP, CRM, & MRP database records as soon as a transaction (order, payment, ext.) is entered into the computer. The key goals of OLTP applications are availability (24/7 availability), speed, concurrency, and recoverability. Characteristics: Concurrency Control - Completes one set of transactions at a time Clusters - Uses data from a small number of columns (attributes), but a large group of tables Volatile - Data is usually changing within a short time frame Time Sensitive - System is always kept up to date, reflecting each transaction Typical OLTP applications are airline reservation systems, ecommerce applications, and banking applications. Examples: Ticketmaster, Amazon, PayPal, E-Trade