Tag: database management system

Introducing DBVisualizer

Advertisements

It is difficult for most businesses to effectively use numerous data of information since enterprise data analysis and management is becoming more difficult and complex. With the growing chances of failure and higher stakes at risk, businesses need to choose the proper software application or software tool that will extract insights from the inside information and manage the database of their enterprise.

What is DBVisualizer?

DBVisualizer is designed as a universal database tool to be used by data analysts, database administrators, and software developers. This software application offers a straightforward and all-in-one UI or user interface for enterprise database management. It comes in both a paid professional edition that provides a wider variation of features and a free edition.

Is DBVisualizer an open-source application?

No, it is a proprietary software application.

Will DBVisualizer run on both Linux and Windows?

DBVisualizer is also dubbed as the universal database tool. It implies that it is capable of running on all of the major operating systems. Hence, the DBVisualizer SQL editor runs smoothly on Windows, Linux/UNIX, and macOS.

Which technical roles would use DBVisualizer most?

Technical roles that deal with databases regularly such as database administrators, developers, and analysts require specific aspects that can be of help to make their work easier. With DBVisualizer, developers can access the advanced DBVisualizer SQL editor that includes smart features that are needed in writing queries, avoiding errors, and speeding up the coding process. For analysts, it will be easier and quicker for them to understand and access the data with the insight feature. They can also easily manage and create the database visually. Lastly, database administrators can be assured that data is secured and preserved during sessions with the autosave feature of DBVisualizer. The software application is also highly optimized and customized to fit the workflow of the user.

Databases or databases types that the DBVisualizer supports

  • Db2
  • Exasol
  • Derby
  • Amazon Redshift
  • Informix
  • H2
  • Mimer SQL
  • MariaDB
  • Microsoft SQL Server
  • MySQL
  • Netezza
  • Oracle
  • SAP ASE
  • PostgreSQL
  • NuoDB
  • Snowflake
  • SQLite
  • Vertica
  • IBM DB2 LUW

Databases that are accessible with JDBC (Java Database Connectivity) driver is capable of working or running with DBVisualizer. You can also see DBVisualizer’s official website that some users have successfully used the software with other non-official database systems such as IBM DB2 iSeries, Firebird, Teradata, and Hive. Aside from that, you can also see the list of other databases that will soon be supported by DBVisualizer.

What are the most essential DBVisualizer documentation links?

Here are the following links that can cover the basic downloads to the application and basic information.

DBVisualizer Site

Installer download link for macOS, Windows 64-bit, Windows 32-bit, Linux, and Unix:

DbVisualizer Users Guide

List of features for free and pro version:

Introducing SQuirreL SQL

Advertisements

The business landscape of today is controlled and influenced by big data and it is also getting bigger and bigger as time goes by. Since the amount of data that is needed to be stored and organized is massive, data workers use SQL to access the information in a relational database. Software applications such as SQL clients can let users create SQL queries, access the database’s information, and view the models of relational databases. One of the most famous and sought out option for SQL clients is the SQuirreL SQL Client.

What is SQuirreL SQL?

It is a client for examining and retrieving SQL databases via a user-friendly and simple graphical user interface (GUI). It can run on any computer that has a Java Virtual Machine (JVM) since SQuirreL SQL is a programming language written in Java. You can download the SQuirreL SQL editor for free and is available in different languages such as English, Chinese, German, Russian, Portuguese, French, and Spanish.

Which technical roles would use SQuirreL SQL most?

SQuirreL SQL is useful and convenient for anyone who works on SQL databases regularly such as software developers, database administrators, application administrators, software testers, etc. For application administrators, they can use SQuirreL SQL to fix a bug at the level of the database. Aside from that, correcting and scanning for incorrect values in a table is easy using SQuirreL SQL. It can also help database administrators in overseeing huge varieties of relational databases, checking problems in tables, manage databases using commands, and viewing metadata.

Is it an open-source application?

SQuirreL SQL Client is a single, open-source graphical front end, Java-written program that enables you to issue SQL commands, perform SQL functions, and view the contents of a database. JDBC-compliant databases are supported by the built graphical front end. It also uses the most popular choice for the open-source software which is the GNU General Public License v2.0.

Will SQuirreL SQL run on both Linux and Windows?

SQuirreL is available under an open-source license and a popular Java written SQL database client. It runs under Microsoft Windows, Linux, and macOS.

Here are the supported databases of SQuirreL SQL:

  • Apache Derby
  • Hypersonic SQL
  • Axion Java RDBMS
  • H2 (DBMS)
  • ClickHouse
  • InterBase
  • Ingres (also OpenIngres)
  • Informix
  • InstantDB
  • IBM DB2 for Windows, Linux, and OS/400
  • Microsoft SQL Server
  • Microsoft Access with the JDBC/ODBC bridge
  • MySQL
  • Mimer SQL
  • Mckoi SQL Database
  • MonetDB
  • Netezza
  • Oracle Database 8i, 9i, 10g, 11g
  • PostgreSQL 7.1.3 and higher
  • Pointbase
  • Sybase
  • SAPDB
  • Sunopsis XML Driver (JDBC Edition)
  • Teradata Warehouse
  • Vertica Analytic Database
  • Firebird with JayBird JCA/JDBC Driver

What are the most essential SQuirreL SQL documentation links?

SQuirreL SQL Universal SQL Client

Install SQuirreL for Linux/Windows/others:

Install SQuirreL for MacOS x:

Install latest snapshots:

Overview of all available downloads:

Introducing DBeaver

Advertisements

With high data volumes and complex systems, database management is becoming more in-demand in today’s economy. Aside from keeping up with the business, organizations also need to innovate new ideas to progress further in the industry. With the use of database management tools, a web interface is provided for database administrators, allowing SQL queries to run.

What is DBeaver?

DBeaver is an open-source universal management tool that can help anyone in professionally working with their data. It will help you maneuver your data similar to a typical spreadsheet, construct analytical reports of various data storage records, and convey information. Effective SQL-editor, connection sessions monitoring, many administration features, and schema and data migration capabilities are imparted with DBeaver’s user on the advanced database. Aside from its usability, it also supports a wide array of databases.

Here are the other offers of DBeaver:

  • Cloud data sources support
  • Security standard of enterprise support
  • Support of multiplatform
  • Meticulous design and implementation of user interface
  • Can work with other integration extensions

Will it run on both Linux and Windows?

DBeaver is downloadable for Windows 9/8/10, Mac OS X, and Linux. It requires at least Java 1.8 version, and OpenJDK 11 bundle is already included in DBeaver’s MacOS and Windows installer.

Main features of DBeaver

DBeaver main features include:

  • Various data sources connection
  • Edit and view data
  • Advanced security
  • Generate mock-data
  • Built-in SQL editor
  • Builds the visual query
  • Transfer data
  • Compares several database structures
  • Search metadata
  • And generates schema/database ER diagrams.

Which databases or database types does DBeaver support?

More than 80 databases are supported by DBeaver, and it includes some of the well-known databases such as:

  • MySQL
  • Oracle
  • MS Access
  • SQLite
  • Apache Hive
  • DB2
  • PostgreSQL
  • Firebird
  • Presto
  • Phoenix
  • SQL Server
  • Teradata
  • Sybase

What are the most essential documentation links?

Related References

Technology – Understanding Data Model Entities

Advertisements

Data Modeling is an established technique of comprehensively documenting an application or software system with the aid of symbols and diagrams. It is an abstract methodology of organizing the numerous data elements and thoroughly highlighting how these elements relate to each other. Representing the data requirements and elements of a database gra phically is called an Entity Relationship Diagram, or ERD.

What is an Entity?

Entities are one of the three essential components of ERDs and represent the tables of the database. An entity is something that depicts only one information concept. For instance, order and customer, although related, are two different concepts, and hence are modeled as two separate entities.

A data model entity typically falls in one of five classes – locations, things. events, roles, and concepts. Examples of entities can be vendors, customers, and products. These entities also have some attributes associated with them, which are some of the details that we would want to track about these entities.

A particular example of an entity is referred to as an instance. Instances form the various rows or records of the table. For instance, if there is a table titled ‘students,’ then a student named William Tell will be a single record of the table.

Why Do We Need a Data Model Entity?

Data is often stored in various forms. An organization may store data in XML files, spreadsheets, reports, and relational databases. Such a fragmented data storage methodology can present challenges during application design and data access. Writing maintainable and efficient code becomes all the more difficult when one has to think about easy data access, scalability, and storage. Additionally, moving data from one form to the other is difficult. This is where the Entity Data Model comes in. Describing the data in the form of relationships and entities, the structure of the data becomes independent of the storage methodology. As the application and data evolve, so does the Data Model Entity. The abstract view allows for a much more streamlined method of transforming or moving data.

SQL Server Length Function Equivalent

Advertisements

The purpose of the Length function in SQL

The SQL LENGTH function returns the number of characters in a string. The LENGTH function is available in many Database Management Systems (DBMS).

The LENGTH Function Syntax

  • LENGTH(string)

LENGTH Function Notes

  • If the input string is empty, the LENGTH returns 0.
  • If the input string is NULL, the LENGTH returns NULL.

Length Function Across Databases

When working as a technical consultant, one has to work with customer’s databases and as you move from one database to another you will find that the function commands may vary–assuming the database has an equivalent function.

Working with VQL and SQL Server got me thing about the LENGTH() function, so, here is a quick references list, which does include the SQL Server.  

IBM DB2

  • LENGTH( )

IBM Informix

  • CHAR_LENGTH() Or CHARACTER_LENGTH()

MariaDB

  • LENGTH( )

Microsoft SQL Server

  • LEN( )

MySQL

  • CHAR_LENGTH() Or CHARACTER_LENGTH()

Netezza

  • LENGTH( )

Oracle

  • LENGTH( )

PostgreSQL

  • CHAR_LENGTH() Or CHARACTER_LENGTH()

SOQL (SalesForce)

  • SOQL has no LENGTH function

VQL (Denodo)

  • LEN( )

Technology – Denodo SQL Type Mapping

Advertisements

denodo 7.0 saves some manual coding when building the ‘Base Views’ by performing some initial data type conversions from ANSI SQL type to denodo Virtual DataPort data types. So, where is a quick reference mapping to show to what the denodo Virtual DataPort Data Type mappings are:

ANSI SQL types To Virtual DataPort Data types Mapping

ANSI SQL TypeVirtual DataPort Type
BIT (n)blob
BIT VARYING (n)blob
BOOLboolean
BYTEAblob
CHAR (n)text
CHARACTER (n)text
CHARACTER VARYING (n)text
DATElocaldate
DECIMALdouble
DECIMAL (n)double
DECIMAL (n, m)double
DOUBLE PRECISIONdouble
FLOATfloat
FLOAT4float
FLOAT8double
INT2int
INT4int
INT8long
INTEGERint
NCHAR (n)text
NUMERICdouble
NUMERIC (n)double
NUMERIC (n, m)double
NVARCHAR (n)text
REALfloat
SMALLINTint
TEXTtext
TIMESTAMPtimestamp
TIMESTAMP WITH TIME ZONEtimestamptz
TIMESTAMPTZtimestamptz
TIMEtime
TIMETZtime
VARBITblob
VARCHARtext
VARCHAR ( MAX )text
VARCHAR (n)text

ANSI SQL Type Conversion Notes

  • The function CAST truncates the output when converting a value to a text, when these two conditions are met:
  1. You specify a SQL type with a length for the target data type. E.g. VARCHAR(20).
  2. And, this length is lower than the length of the input value.
  • When casting a boolean to an integer, true is mapped to 1 and false to 0.

Related References

denodo 8.0 / User Manuals / Virtual DataPort VQL Guide / Functions / Conversion Functions

Technology – Denodo Data Virtualization Project Roles

Advertisements

A Denodo virtualization project typically classifies the project duties of the primary implementation team into four Primary roles.

Denodo Data Virtualization Project Roles

  • Data Virtualization Architect
  • Denodo Platform Administrator
  • Data Virtualization Developer
  • Denodo Platform Java Programmer
  • Data Virtualization Internal Support Team

Role To Project Team Member Alignment

While the denodo project is grouped into security permissions and a set of duties, it is import to note that the assignment of the roles can be very dynamic as to their assignment among project team members.  Which team member who performs a given role can change the lifecycle of a denodo project.  One team member may hold more than one role at any given time or acquire or lose roles based on the needs of the project.

Denodo virtualization Project Roles Duties

Data Virtualization Architect

The knowledge, responsibilities, and duties of a denodo data virtualization architect, include:

  • A Deep understanding of denodo security features and data governance
  • Define and document5 best practices for users, roles, and security permissions.
  • Have a strong understanding of enterprise data/information assets
  • Defines data virtualization architecture and deployments
  • Guides the definition and documentation of the virtual data model, including, delivery modes, data sources, data combination, and transformations

Denodo Platform Administrator

The knowledge, responsibilities, and duties of a Denodo Platform Administrator, Include:

  • Denodo Platform Installation and maintenance, such as,
    • Installs denodo platform servers
    • Defines denodo platform update and upgrade policies
    • Creates, edits, and removes environments, clusters, and servs
    • Manages denodo licenses
    • Defines denodo platform backup policies
    • Defines procedures for artifact promotion between environments
  • Denodo platform configuration and management, such as,
    • Configures denodo platform server ports
    • Platform memory configuration and Java Virtual Machine (VM) options
    • Set the maximum number of concurrent requests
    • Set up database configuration
      • Specific cache server
      • Authentication configuration for users connecting to denodo platform (e.g., LDAP)
      • Secures (SSL) communications connections of denodo components
      • Provides connectivity credentials details for clients tools/applications (JDBC, ODBC,,,etc.)
      • Configuration of resources.
    • Setup Version Control System (VCS) configuration for denodo
    • Creates new Virtual Databases
    • Create Users, roles, and assigns privileges/roles.
    • Execute diagnostics and monitoring operations, analyzes logs and identifies potentials issues
    • Manages load balances variables

Data Virtualization Developer

The Data Virtualization Developer role is divided into the following sub-roles:

  • Data Engineer
  • Business Developer
  • Application Developer

the knowledge, responsibilities, and duties of a Denodo Data Virtualization Developer, by sub-role, Include:

Data Engineer

The denodo data engineer’s duties include:

  • Implements the virtual data model construction view by
    • Importing data sources and creating base views, and
    • Creating derived views applying combinations and transformations to the datasets
  • Writes documentation, defines testing to eliminate development errors before code promotion to other environments

Business Developer

The denodo business developer’s duties include:

  • Creates business vies for a specific business area from derived and/or interface views
  • Implements data services delivery
  • Writes documentation

Application Developer

The denodo application developer’s duties include:

  • Creates reporting vies from business views for reports and or datasets frequently consumed by users
  • Writes documentation

Denodo Platform Java Programmer

The Denodo Platform Java Programmer role is an optional, specialized, role, which:

  • Creates custom denodo components, such as data sources, stored procedures, and VDP/iTPilot functions.
  • Implements custom filters in data routines
  • Tests and debugs any custom components using Denodo4e

Data Virtualization Internal Support Team

The denodo data virtualization internal support team’s duties include

  • Access to and knowledge of the use and trouble of developed solutions
  • Tools and procedures to manage and support project users and developers

denodo Virtualization – Useful Links

Advertisements
  • denodo Virtualization

Here are some denodo Virtualization references, which may be useful.

Reference Name Link
denodo Home Page https://www.denodo.com/en/about-us/our-company
denodo Platform 7.0 Documentation https://community.denodo.com/docs/html/browse/7.0/
denodo Knowledge Base and Best Practices https://community.denodo.com/kb/
denodo Tutorials https://community.denodo.com/tutorials/
denodo Express 7.0 Download https://community.denodo.com/express/download
Denodo Virtual Data Port (VDP) https://community.denodo.com/kb/download/pdf/VDP%20Naming%20Conventions?category=Operation
JDBC / ODBC drivers for Denodo https://community.denodo.com/drivers/
Denodo Governance Bridge – User Manual https://community.denodo.com/docs/html/document/denodoconnects/7.0/Denodo%20Governance%20Bridge%20-%20User%20Manual
Virtual DataPort VQL Guidehttps://community.denodo.com/docs/html/browse/7.0/vdp/vql/introduction/introduction
Denodo Model Bridge – User Manualhttps://community.denodo.com/docs/html/document/denodoconnects/7.0/Denodo%20Model%20Bridge%20-%20User%20Manual
Denodo Connects Manualshttps://community.denodo.com/docs/html/browse/7.0/denodoconnects/index
Denodo Infosphere Governance Bridge – User Manualhttps://community.denodo.com/docs/html/document/denodoconnects/7.0/Denodo%20Governance%20Bridge%20-%20User%20Manual

PostgreSQL – Useful Links

Advertisements
  • PostgreSQL

PostgreSQL – Useful Links

Here are some PostgreSQL references, which may be useful.

Reference Type Link
PostgreSQL Home page & Download https://www.postgresql.org/
PostgreSQL Online Documentation https://www.postgresql.org/docs/manuals/
Citus – Scalability and Parallelism Extension https://github.com/citusdata
Free PostgreSQL Training https://www.enterprisedb.com/free-postgres-training
PostgreSQL Wiki https://wiki.postgresql.org/wiki/Main_Page

The Business Industries Using PostgreSQL

Advertisements
  • PostgreSQL

PostgreSQL is an open-source database, which was released in 1996. So, PostgreSQL has been around a long time.   So, among the many companies and industries which know they are using PostgreSQL, many others are using PostgreSQL and don’t know it because it is embedded as the foundation in some other application’s software architecture.  

I hadn’t paid much attaint to PostgreSQL even though it as been on the list leading databases used by business for years.  Mostly I have been focused on the databases my customer were using (Oracle, DB2, Microsoft SQL Server, and MySQL/MariaDB).  However, during a recent meeting I was surprised to learn that io had been using and administering PostrgresSQL embedded as part of another software vendors application, which made me take the time to pay attention to PostgreSQL. Especially, who is using PostgreSQL and what opportunities that may provide for evolving my career? 

The Industries Using PostgreSQL

According to enlyft, the major using the PostgreSQL are Computer Software and Information Technology And services companies.  

  PostgreSQL Consumers Information

Here is the  link to enlyft page, which provides additional information companies and industries using PostgreSQL:

How To Get A List Of Oracle Database Schemas?

Advertisements
  • Oracle Database

Well, this is one of those circumstances, where your ability to answer this question will depend upon your user’s assigned security roles and what you actually want. 

To get a complete list, you will need to use the DBA_ administrator tables to which most of us will not have access.  In the very simple examples below, you may want to add a WHERE clause to eliminate the system schemas from the list, like ‘SYS’ and ‘SYSTEM,’ if you have access to them.

Example Administrator (DBA) Schema List

SELECT distinct OWNER as SCHEMA_NAME

FROM  DBA_OBJECTS

ORDER BY OWNER;

Example Administrator (DBA) Schema List Results Screenshot

  • Example Administrator (DBA) Schema List Results Screenshot

Fortunately for the rest of us, there are All user tables, from which we can get a listing of the schemas to which we have access.

Example All Users Schema List

SELECT distinct OWNER as SCHEMA_NAME

FROM    ALL_OBJECTS

ORDER BY OWNER;

Example All Users Schema List Results Screenshot

  • Example All Users Schema List Results Screenshot

Related References

Oracle help Center > Database> Oracle > Oracle Database > Release 19

Oracle SQL*Plus Is still Around

Advertisements
  • Oracle SQL*Plus

It is funny how you cannot work with some for a while because of newer tools, and then rediscover them, so to speak.  The other day I was looking at my overflow bookshelf in the garage and saw an old book on Oracle SQL*Plus and was thinking, “do I still want or need that book?”. 

In recent years I have been using a variety of other tools when working with oracle. So, I really hadn’t thought about the once ubiquitous Oracle SQL*Plus command-line interface for Oracle databases, which around for thirty-five years or more.  However, I recently needed to do an Oracle 18C database install to enable some training and was pleasantly surprised Oracle SQL*Plus as a menu item. 

Now I have been purposely using Oracle SQL*Plus again to refresh my skills, and I will be keeping my Oracle SQL*Plus: The Definitive Guide, after all. 

How to Determine Your Oracle Database Name

Advertisements
  • Oracle

Oracle provides a few ways to determine which database you are working in.  Admittedly, I usually know which database I’m working in, but recently I did an Oracle Database Express Edition (XE) install which did not goes has expected and I had reason to confirm which database I was actually in when the SQL*Plus session opened.  So, this lead me to consider how one would prove exactly which database they were connected to.  As it happens, Oracle has a few ways to quickly display which database you are connected to and here are two easy ways to find out your Oracle database name in SQL*Plus:

  • V$DATABASE
  • GLOBAL_NAME

Checking the GLOBAL_NAME table

The First method is to run a quick-select against the GLOBAL_NAME table, which. is publicly available to logged-in users of the database

Example GLOBAL_NAME Select Statement

select * from global_name;

  • Example GLOBAL_NAME Select Statement

Checking the V$DATABASE Variable

The second method is to run a quick-select a V$database. However, not everyone will have access to the V$database variable.

Example V$database Select Statement

select name from V$database;

  • Example V$database Select Statement

Use and Advantages of Apache Derby DB

Advertisements


Developed by Apache Software Foundation, Apache Derby DB is a completely free, open-source relational database system developed purely with Java. It has multiple advantages that make it a popular choice for Java applications requiring small to medium-sized databases.

Reliable and Secure

With over 15 years in development, Derby DB had time to grow, add new and improve on the existing components. Even though it has an extremely small footprint – only 3.5MB of all JAR files – Derby is a full-featured ANSI SQL database, supporting all the latest SQL standards, transactions, and security factors.

The small footprint adds to its versatility and portability – Derby can easily be embedded into Java applications with almost no performance impact. It’s extremely easy to install and configure, requiring almost no administration afterward. Once implemented, there is no need to further modify or set up the database at the end user’s computer. Alongside the embedded framework, Derby can also be used in a more familiar server mode.

All documentation containing different manuals for specific versions of Derby can be found on their official website, at :

Cross-Platform Support

Java is compatible with almost all the different platforms, including Windows, Linux, and MacOS. Since Derby DB is implemented completely in Java, it can be easily transferred without the need for different distribution downloads. It can use all types of Java Virtual Machines as long as they’re properly certified. Apache’s Derby includes the Derby code without any modification to the elemental source code.

Derby supports transactions, which are executed for quick and secure data retrieval from the database as well as referential integrity. Even though the stored procedures are made in Java, in the client/server mode Derby can bind to PHP, Python and Perl programming languages. 

All data is encrypted, with support for database triggers to maintain the integrity of the information. Alongside that, custom made functions can be created with any Java library so the users can manipulate the data however they want. 

Embedded and Server Modes

Derby’s embedded mode is usually recommended as a beginner-friendly option. The main differences are in who manages the database along with how it’s stored. 

When Derby is integrated as a whole and becomes a part of the main program, it acts as a persistent data store and the database is managed through the application. It also runs within the Java Virtual Machine of the application. In this mode, no other user is able to access the database – only the app that it is integrated into. As a result of these limits, the embedded mode is most useful for single-user apps.

If it’s run in server mode, the user starts a Derby network server which is tasked with responding to database requests. Derby runs in a Java Virtual Machine that hosts the server. The database is loaded onto the server, waiting for client applications to connect to it. This is the most typical architecture used by most of the other bigger databases, such as MySQL. Server mode is highly beneficial when more than one user needs to have access to the database across the network.

Downloading Derby

Derby has to be downloaded and extracted from the .zip package before being used. Downloads can be found at the Apache’s official website:

Numerous download options are presented on there, depending on the Java version that the package is going to be used with. 

Using Derby requires having Java Development Kit (JDK) pre-installed on the system and then configuring the environment to use the JDBC driver. Official tutorials can be found at:

Running and Manipulating Derby DB

Interacting with Derby is done through the use of ‘ij’ tool, which is an interactive JDBC scripting program. It can be used for running interactive queries and scripts against a Derby database. The ij tool is run through the command shell.

The initial Derby connection command differs depending on whether it’s going to be run in embedded or server mode.

For a tutorial on how to use the connect commands, check out https://www.vogella.com/tutorials/ApacheDerby/article.html.

Some Useful Derby DB Documentation

Derby Reference Manual‎: ‎

Derby Server and Administration Guide‎: ‎

API Reference‎:

Derby Tools and Utilities Guide‎: ‎

ij Basics

In conclusion, Derby DB is a lightweight yet efficient tool that can be integrated into various types of Java applications with ease.

OLTP vs Data Warehousing

Advertisements

OLTP Versus Data Warehousing

I’ve tried to explain the difference between OLTP systems and a Data Warehouse to my managers many times, as I’ve worked at a hospital as a Data Warehouse Manager/data analyst for many years. Why was the list that came from the operational applications different than the one that came from the Data Warehouse? Why couldn’t I just get a list of patients that were laying in the hospital right now from the Data Warehouse? So I explained, and explained again, and explained to another manager, and another. You get the picture.
In this article I will explain this very same thing to you. So you know  how to explain this to your manager. Or, if you are a manager, you might understand what your data analyst can and cannot give you.

OLTP

OLTP stands for On Line Transactional Processing. With other words: getting your data directly from the operational systems to make reports. An operational system is a system that is used for the day to day processes.
For example: When a patient checks in, his or her information gets entered into a Patient Information System. The doctor put scheduled tests, a diagnoses and a treatment plan in there as well. Doctors, nurses and other people working with patients use this system on a daily basis to enter and get detailed information on their patients.
The way the data is stored within operational systems is so the data can be used efficiently by the people working directly on the product, or with the patient in this case.

Data Warehousing

A Data Warehouse is a big database that fills itself with data from operational systems. It is used solely for reporting and analytical purposes. No one uses this data for day to day operations. The beauty of a Data Warehouse is, among others, that you can combine the data from the different operational systems. You can actually combine the number of patients in a department with the number of nurses for example. You can see how far a doctor is behind schedule and find the cause of that by looking at the patients. Does he run late with elderly patients? Is there a particular diagnoses that takes more time? Or does he just oversleep a lot? You can use this information to look at the past, see trends, so you can plan for the future.

The difference between OLTP and Data Warehousing

This is how a Data Warehouse works:

The data gets entered into the operational systems. Then the ETL processes Extract this data from these systems, Transforms the data so it will fit neatly into the Data Warehouse, and then Loads it into the Data Warehouse. After that reports are formed with a reporting tool, from the data that lies in the Data Warehouse.

This is how OLTP works:

Reports are directly made from the data inside the database of the operational systems. Some operational systems come with their own reporting tool, but you can always use a standalone reporting tool to make reports form the operational databases.

Pro’s and Con’s

Data Warehousing

Pro’s:

  • There is no strain on the operational systems during business hours
    • As you can schedule the ETL processes to run during the hours the least amount of people are using the operational system, you won’t disturb the operational processes. And when you need to run a large query, the operational systems won’t be affected, as you are working directly on the Data Warehouse database.
  • Data from different systems can be combined
    • It is possible to combine finance and productivity data for example. As the ETL process transforms the data so it can be combined.
  • Data is optimized for making queries and reports
    • You use different data in reports than you use on a day to day base. A Data Warehouse is built for this. For instance: most Data Warehouses have a separate date table where the weekday, day, month and year is saved. You can make a query to derive the weekday from a date, but that takes processing time. By using a separate table like this you’ll save time and decrease the strain on the database.
  • Data is saved longer than in the source systems
    • The source systems need to have their old records deleted when they are no longer used in the day to day operations. So they get deleted to gain performance.

Con’s:

  • You always look at the past
    • A Data Warehouse is updated once a night, or even just once a week. That means that you never have the latest data. Staying with the hospital example: you never knew how many patients are in the hospital are right now. Or what surgeon didn’t show up on time this morning.
  • You don’t have all the data
    • A Data Warehouse is built for discovering trends, showing the big picture. The little details, the ones not used in trends, get discarded during the ETL process.
  • Data isn’t the same as the data in the source systems
    • Because the data is older than those of the source systems it will always be a little different. But also because of the Transformation step in the ETL process, data will be a little different. It doesn’t mean one or the other is wrong. It’s just a different way of looking at the data. For example: the Data Warehouse at the hospital excluded all transactions that were marked as cancelled. If you try to get the same reports from both systems, and don’t exclude the cancelled transactions in the source system, you’ll get different results.

online transactional processing (OLTP)

Pro’s

  • You get real time data
    • If someone is entering a new record now, you’ll see it right away in your report. No delays.
  • You’ve got all the details
    • You have access to all the details that the employees have entered into the system. No grouping, no skipping records, just all the raw data that’s available.

Con’s

  • You are putting strain on an application during business hours.
    • When you are making a large query, you can take processing space that would otherwise be available to the people that need to work with this system for their day to day operations. And if you make an error, by for instance forgetting to put a date filter on your query, you could even bring the system down so no one can use it anymore.
  • You can’t compare the data with data from other sources.
    • Even when the systems are similar. Like an HR system and a payroll system that use each other to work. Data is always going to be different because it is granulated on a different level, or not all data is relevant for both systems.
  • You don’t have access to old data
    • To keep the applications at peak performance, old data, that’s irrelevant to day to day operations is deleted.
  • Data is optimized to suit day to day operations
    • And not for report making. This means you’ll have to get creative with your queries to get the data you need.

So what method should you use?

That all depends on what you need at that moment. If you need detailed information about things that are happening now, you should use OLTP.
If you are looking for trends, or insights on a higher level, you should use a Data Warehouse.

 Related References

Netezza / PureData – Table Describe SQL

Advertisements

Netezza / Puredata Table Describe SQL

If you want to describe a PureData / Netezza table in SQL, it can be done, but Netezza doesn’t have a describe command.  Here is a quick SQL, which will give the basic structure of a table or a view.  Honestly, if you have Aginity Generating the DDL is fast and more informative, at least to me.  If you have permissions to access NZSQL you can also use the slash commands (e.g. d).

Example Netezza Table Describe SQL

select  name as Table_name,

owner as Table_Owner,

Createdate as Table_Created_Date,

type as Table_Type,

Database as Database_Name,

schema as Database_Schema,

attnum as Field_Order,

attname as Field_Name,

format_type as Field_Type,

attnotnull as Field_Not_Null_Indicator,

attlen as Field_Length

from _v_relation_column

where

name='<<Table Name Here>>’

Order by attnum;

 

Related References

IBM Knowledge Center, PureData System for Analytics, Version 7.2.1

IBM Netezza database user documentation, Command-line options for nzsql, Internal slash options

IBM Knowledge Center, PureData System for Analytics, Version 7.2.1

IBM Netezza getting started tips, About the Netezza data warehouse appliance, Commands and queries, Basic Netezza SQL information, Commonly used nzsql internal slash commands

IBM Knowledge Center, PureData System for Analytics, Version 7.2.1

IBM Netezza database user documentation, Netezza SQL introduction, The nzsql command options, Slash options

 

 

Netezza / PureData – Substring Function Example

Advertisements

The function Substring (SUBSTR) in Netezza PureData provides the capability to parse character type fields based on position within a character string.

Substring Functions Basic Syntax

SUBSTRING Function Syntax

SUBSTRING(<<CharacterField>>,<< StartingPosition integer>>, <<for Number of characters Integer–optional>>)

SUBSTR Function Syntax

SUBSTR((<>,<< StartingPosition integer>>, <>)

Example Substring SQL

Netezza / PureData Substring Example

Substring SQL Used In Example

SELECT  LOCATIONTEXT

— From the Left Of the String

—

— Using SUBSTRING Function

,’==SUBSTRING From the Left==’ as Divider1

,SUBSTRING(LOCATIONTEXT,1,5) as Beggining_Using_SUBSTRING_LFT

,SUBSTRING(LOCATIONTEXT,7,6) as Middle_Using_SUBSTRING_LFT

,SUBSTRING(LOCATIONTEXT,15) as End_Using_SUBSTRING_LFT

,’==SUBSTR From the Left==’ as Divider2

—Using SUBSTR Function

,SUBSTR(LOCATIONTEXT,1,5) as Beggining_Using_SUBSTR_LFT

,SUBSTR(LOCATIONTEXT,7,6) as Middle_Using_SUBSTR_LFT

,SUBSTR(LOCATIONTEXT,15) as End_Using_SUBSTR_LFT

—

— From the right of the String

—

,’==SUBSTRING From the Right==’ as Divider3

,SUBSTRING(LOCATIONTEXT,LENGTH(LOCATIONTEXT)-18, 8) as Beggining_Using_SUBSTRING_RGT

,SUBSTRING(LOCATIONTEXT,LENGTH(LOCATIONTEXT)-9, 6) as Middle_Using_SUBSTRING_RGT

,SUBSTRING(LOCATIONTEXT,LENGTH(LOCATIONTEXT)-1) as End_Using_SUBSTRING_RGT

,’==SUBSTR From the right==’ as Divider4

,SUBSTR(LOCATIONTEXT,LENGTH(LOCATIONTEXT)-18, 8) as Beggining_Using_SUBSTR_RGT

,SUBSTR(LOCATIONTEXT,LENGTH(LOCATIONTEXT)-9, 6) as Middle_Using_SUBSTR_RGT

,SUBSTR(LOCATIONTEXT,LENGTH(LOCATIONTEXT)-1) as End_Using_SUBSTR_RGT

FROM BLOG.D_ZIPCODE

where STATE = ‘PR’

AND CITY = ‘REPTO ROBLES’;

IBM Knowledge Center, PureData System for Analytics, Version 7.2.1

IBM Netezza database user documentation, Netezza SQL basics, Netezza SQL extensions, Character string functions

IBM Knowledge Center, PureData System for Analytics, Version 7.1.0

IBM Netezza Database User’s Guide, Netezza SQL basics, Functions and operators, Functions, Standard string functions

IBM Knowledge Center, PureData System for Analytics, Version 7.2.1

IBM Netezza database user documentation, Netezza SQL command reference, Functions

Netezza / PureData – Substring Function On Specific Delimiter

Advertisements

Netezza / PureData – Substring Function On Specific Delimiter

The function Substring (SUBSTR) in Netezza PureData provides the capability parse character type fields based on position within a character string.  However, it is possible, with a little creativity, to substring based on the position of a character in the string. This approach give more flexibility to the substring function and makes the substring more useful in many cases. This approach works fine with either the substring or substr functions.  In this example, I used the position example provide the numbers for the string command.

 

Example Substring SQL

Netezza PureData Substring Function On Specific Character In String

 

Substring SQL Used In Example

select LOCATIONTEXT

,position(‘,’ in LOCATIONTEXT) as Comma_Postion_In_String

—without Adjustment

,SUBSTRING(LOCATIONTEXT,position(‘,’ in LOCATIONTEXT)) as Substring_On_Comma

—Adjusted to account for extra space

,SUBSTRING(LOCATIONTEXT,position(‘,’ in LOCATIONTEXT)+2) as Substring_On_Comma_Ajusted

,’==Breaking_Up_The_Sting==’ as Divider

— breaking up the string

,SUBSTRING(LOCATIONTEXT,1, position(‘ ‘ in LOCATIONTEXT)-1) as Beggining_of_String

,SUBSTRING(LOCATIONTEXT,position(‘ ‘ in LOCATIONTEXT)+1, position(‘ ‘ in LOCATIONTEXT)-1) as Middle_Of_String

,SUBSTRING(LOCATIONTEXT,position(‘,’ in LOCATIONTEXT)+2) as End_Of_String

 

FROM Blog.D_ZIPCODE

where STATE = ‘PR’

AND CITY = ‘REPTO ROBLES’

Related References

IBM Knowledge Center, PureData System for Analytics, Version 7.2.1

IBM Netezza database user documentation, Netezza SQL basics, Netezza SQL extensions, Character string functions

IBM Knowledge Center, PureData System for Analytics, Version 7.1.0

IBM Netezza Database User’s Guide, Netezza SQL basics, Functions and operators, Functions, Standard string functions

IBM Knowledge Center, PureData System for Analytics, Version 7.2.1

IBM Netezza database user documentation, Netezza SQL command reference, Functions

Netezza / PureData – Position Function

Advertisements

Netezza / PureData Position Function

 

The position function in Netezza is a simple enough function, it just returns the number of a specified character within a string (char, varchar, nvarchar, etc.) or zero if the character not found. The real power of this command is when you imbed it with character function, which require a numeric response, but the character may be inconsistent from row to row in a field.

The Position Function’s Basic Syntax

position(<<character or Character String>> in <<CharacterFieldName>>)

 

Example Position Function SQL

Netezza PureData Position Function

 

Position Function SQL Used in Example

select LOCATIONTEXT, CITY

,’==Postion Funtion Return Values==’ as Divider

,position(‘,’ in LOCATIONTEXT) as Postion_In_Nbr_String

,position(‘-‘ in LOCATIONTEXT) as Postion_Value_Not_Found

,’==Postion Combined with Substring Function==’ as Divider2

,SUBSTRING(LOCATIONTEXT,position(‘,’ in LOCATIONTEXT)+2) as Position_Used_in_Substring_Function

FROM Blog.D_ZIPCODE  where STATE = ‘MN’ AND CITY = ‘RED WING’ limit 1;

 

 

Related References

IBM Knowledge Center, PureData System for Analytics, Version 7.1.0

IBM Netezza Database User’s Guide, Netezza SQL basics, Functions and operators, Functions, Standard string functions

IBM Knowledge Center, PureData System for Analytics, Version 7.2.1

IBM Netezza database user documentation, Netezza SQL command reference, Functions

 

Data Modeling – Column Data Classification

Advertisements
  • Data Modeling

Column Data Classification

When analyzing individual column data, at its most foundational level, column data can be classified by their fundamental use/characteristics.  Granted, when you start rolling up the structure into multiple columns, table structure and table relationship, then other classifications/behaviors, such as keys (primary and foreign), indexes, and distribution come into play.  However, many times when working with existing data sets it is essential to understand the nature the existing data to begin the modeling and information governance process.

Column Data Classification

Generally, individual columns can be classified into the classifications:

  • Identifier — A column/field which is unique to a row and/or can identify related data (e.g., Person ID, National identifier, ). Basically, think primary key and/or foreign key.
  • Indicator — A column/field, often called a Flag, that has a binary condition (e.g., True or False, Yes or No, Female or Male, Active or Inactive). Frequently used to identify compliance with complex with a specific business rule.
  • Code — A column/field that has a distinct and defined set of values, often abbreviated (e.g., State Code, Currency Code)
  • Temporal — A column/field that contains some type date, timestamp, time, interval, or numeric duration data
  • Quantity — A column/field that contains a numeric value (decimals, integers, etc.) and is not classified as an Identifier or Code (e.g., Price, Amount, Asset Value, Count)
  • Text — A column/field that contains alphanumeric values, possibly long text, and is not classified as an Identifier or Code (e.g., Name, Address, Long Description, Short Description)
  • Large Object (LOB)– A column/field that contains data traditional long text fields or binary data like graphics. The large objects can be broadly classified as Character Large Objects (CLOBs), Binary Large Objects (BLOBs), and Double-Byte Character Large Object (DBCLOB or NCLOB).

What is a Common Data Model (CDM)?

Advertisements
  • Data Model

What is a Common Data Model (CDM)?

A Common Data Model (CDM) is a share data structure designed to provide well-formed and standardized data structures within an industry (e.g. medical, Insurance, etc.) or business channel (e.g. Human resource management, Asset Management, etc.), which can be applied to provide organizations a consistent unified view of business information.   These common models can be leveraged as accelerators by organizations form the foundation for their information, including SOA interchanges, Mashup, data vitalization, Enterprise Data Model (EDM), business intelligence (BI), and/or to standardize their data models to improve meta data management and data integration practices.

Related references

IBM, IBM Analytics

IBM Analytics, Technology, Database Management, Data Warehousing, Industry Models

github.com

Observational Health Data Sciences and Informatics (OHDSI)/Common Data Model

Oracle

Oracle Technology Network, Database, More Key Features, Utilities Data Model

Oracle

Industries, Communications, Service Providers, Products, Data Mode, Oracle Communications Data Model

Oracle

Oracle Technology Network, Database, More Key Features, Airline data Model

PureData / Netezza – What date/time ranges are supported by Netezza?

Advertisements

Date/Time ranges supported by Netezza

Here is a synopsis of the temporal ranges ( date, time, and timestamp), which Netezza / PureData supports.

Temporal Type

Supported Ranges

Size In Bytes

Date

 A month, day, and year. Values range from January 1, 0001, to December 31, 9999. 4 bytes

Time

 An hour, minute, and second to six decimal places (microseconds). Values range from 00:00:00.000000 to 23:59:59.999999. 8 bytes

Related References

Temporal data types

PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza database user documentation, Netezza SQL basics, Data types, Temporal data types

Netezza date/time data type representations

PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza user-defined functions, Data type helper API reference, Temporal data type helper functions, Netezza date/time data type representations

Date/time functions

PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza database user documentation, Netezza SQL basics, Netezza SQL extensions, Date/time functions

Netezza / PureData – How to add a Foreign Key

Advertisements
DDL (Data Definition Language)

Adding a forging key to tables in Netezza / PureData is a best practice; especially, when working with dimensionally modeled data warehouse structures and with modern governance, integration (including virtualization), presentation semantics (including reporting, business intelligence and analytics).

Foreign Key (FK) Guidelines

  • A primary key must be defined on the table and fields (or fields) to which you intend to link the foreign key
  • Avoid using distribution keys as foreign keys
  • Foreign Key field should not be nullable
  • Your foreign key link field(s) must be of the same format(s) (e.g. integer to integer, etc.)
  • Apply standard naming conventions to constraint name:
    • FK_<<Constraint_Name>>_<<Number>>
    • <<Constraint_Name>>_FK<<Number>>
  • Please note that foreign key constraints are not enforced in Netezza

Steps to add a Foreign Key

The process for adding foreign keys involves just a few steps:

  • Verify guidelines above
  • Alter table add constraint SQL command
  • Run statistics, which is optional, but strongly recommended

Basic Foreign Key SQL Command Structure

Here is the basic syntax for adding Foreign key:

ALTER TABLE <<Owner>>.<<NAME_OF_TABLE_BEING_ALTERED>>

ADD CONSTRAINT <<Constraint_Name>>_fk<Number>>

FOREIGN KEY (<<Field_Name or Field_Name List>>) REFERENCES <<Owner>>.<<target_FK_Table_Name>.(<<Field_Name or Field_Name List>>) <<On Update | On Delete>> action;

Example Foreign Key SQL Command

This is a simple one field example of the foreign key (FK)

ALTER TABLE Blog.job_stage_fact

ADD CONSTRAINT job_stage_fact_host_dim_fk1

FOREIGN KEY (hostid) REFERENCES Blog.host_dim(hostid) ON DELETE cascade ON UPDATE no action;

Related References

Alter Table

PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza database user documentation, Netezza SQL command reference, Alter Table, constraints

Databases – Database Isolation Level Cross Reference

Advertisements

Database And Tables

 

Here is a table quick reference of some common database and/or connection types, which use connection level isolation and the equivalent isolation levels. This quick reference may prove useful as a job aid reference, when working with and making decisions about isolation level usage.

Database isolation levels

Data sources

Most restrictive isolation level

More restrictive isolation level

Less restrictive isolation level

Least restrictive isolation level

Amazon SimpleDB

 Serializable Repeatable read Read committed Read Uncommitted

dashDB

 Repeatable read Read stability Cursor stability Uncommitted read

DB2® family of products

 Repeatable read Read stability* Cursor stability Uncommitted read

Informix®

 Repeatable read Repeatable read Cursor stability Dirty read

JDBC

 Serializable Repeatable read Read committed Read Uncommitted

MariaDB

 Serializable Repeatable read Read committed Read Uncommitted

Microsoft SQL Server

 Serializable Repeatable read Read committed Read Uncommitted

MySQL

 Serializable Repeatable read Read committed Read Uncommitted

ODBC

 Serializable Repeatable read Read committed Read Uncommitted

Oracle

 Serializable Serializable Read committed Read committed

PostgreSQL

 Serializable Repeatable read Read committed Read committed

Sybase

 Level 3 Level 3 Level 1 Level 0

 

Related References

 

Database – What is a foreign key?

Advertisements
Acronyms, Abbreviations, Terms, And Definitions

Definition of a Foreign Key

  • A foreign Key (FK) is a constraint that references the unique primary key (PK) of another table.

Facts About Foreign Keys

  • Foreign Keys act as a cross-reference between tables linking the foreign key (Child record) to the Primary key (parent record) of another table, which establishing a link/relationship between the table keys
  • Foreign keys are not enforced by all RDBMS
  • The concept of referential integrity is derived from foreign key theory
  • Because Foreign keys involve more than one table relationship, their implementation can be more complex than primary keys
  • A foreign-key constraint implicitly defines an index on the foreign-key column(s) in the child table, however, manually defining a matching index may improve join performance in some database
  • The SQL, normally, provides the following referential integrity actions for deletions, when enforcing foreign-keys

Cascade

  • The deletion of a parent (primary key) record may cause the deletion of corresponding foreign-key records.

No Action

  • Forbids the deletion of a parent (primary key) record, if there are dependent foreign-key records.   No Action does not mean to suppress the foreign-key constraint.

Set null

  • The deletion of a parent (primary key) record causes the corresponding foreign-key to be set to null.

Set default

  • The deletion of a record causes the corresponding foreign-keys be set to a default value instead of null upon deletion of a parent (primary key) record

Related References

Netezza / PureData – How to rebuild a Netezza view in Aginity

Advertisements
  • How To Generate View or table DDL in Aginity For Netezza

Rebuilding Netezza view sometimes becomes necessary when the view’s source table have changed underneath the view.  Rebuilding a view can be done on Netezza or in Aginity. In Aginity, it is a simple process, assume your user has permissions to create or replace a view.  The process breaks down into just a few steps:

Generate the create / replace view SQL of the original view into the query window, if you don’t have it already

In the object browser:

  • Navigate to the Database and view you wish to rebuild
  • Select the view and right click
  • Select ‘Scripts’, then ‘DDL to Query window’

Make may updates to create / replace View SQL

  • This step is not always necessary, sometimes the changes which invalided the view did not actually impact the code of the view. If changes are necessary, make may updates to the SQL code.

Execute The code

  • This I usually do by choosing the ‘Execute as a single batch’ option.  Make sure the code executes successfully.

Verify the view

  • To verify the simply execute a select statement and make it executes without errors and/or warning.

Netezza / PureData Date – Difference in Days SQL

Advertisements

Netezza /PureData Date Difference in Days

Since Netezza does not have a datediff function, the ‘old school’ of calculating the difference, in days, between dates must be used.

Subtracting Inclusive Dates

To subtract to day and include end date, as a day, in calculation (1 day is added)

select (date(‘2015-12-31’) – date(‘2015-01-01’))+1 as Inclusive_dates

From _v_dual;

 

Subtracting Non-inclusive dates

To subtract dates non-inclusive simply subtract the dates

select date(‘2015-12-31’) – date(‘2015-01-01’) as Non_Inclusive_dates

From _v_dual;

 

Example SQL From Graphic

select (date(‘2015-12-31’) – date(‘2015-01-01’))+1 as Inclusive_dates,

date(‘2015-12-31’) – date(‘2015-01-01’) as Non_Inclusive_dates

From _v_dual;

 

Related References

What are the types of Database Management Systems (DBMS)?

Advertisements

Database Management Systems (DBMS)

It is important to understand the differences between Database Management Systems (DBMS) types, since the structure of each type will influence integrations approaches, functionality, overall speed, and scalability.

The Five Types of Database Management Systems (DBMS)?

The five basic types of databases are:

  • Hierarchical
  • Object-Orientated
  • Network
  • Relational
  • Flat File

Hierarchical Database (HDB)

  • A hierarchical database (HDB or HDBMS)is a design that uses a one-to-many relationship for data elements. Hierarchical database models use a tree structure that links several disparate elements to one “owner,” or “parent,” primary record.

Object-Oriented Database (OODB)

  • Object-Orientated databases (OODB or OODBM) integrate object orientation with database capabilities. Object orientation allows a more direct representation and modeling of real-world problems, and database functionality is needed to ensure persistence and concurrent sharing of information in applications.

Network Database (NDB)

  • Network databases (NDB or NDBMS) are quite like hierarchical databases, except it allows multiple records to be linked to the same owner file. The model can be seen as an upside down tree where the branches are the member information linked to the owner, which is the bottom of the tree. The multiple linkages which this information allows the network database model to be very flexible. In addition, the relationship that the information has in the network database model is defined as many-to-many relationship because one owner file can be linked to many member files and vice versa.

Relational Database (RDB)

  • In simplest terms, a relational database (RDB or RDBMS)is one that presents information in formally described tables with rows and columns. A table is referred to as a relation in the sense that it is a collection of objects of the same type (rows). Data in a table can be related per common keys or concepts, and the ability to retrieve related data from a table, which is the basis for the term relational database. Data can be accessed or reassembled in many ways without having to reorganize the database tables structure.

Flat File Database (FFDB)

  •  A flat file database (FFDB or FFDBM) describes any of various means to encode a database model as a single file or collection of files, which can be a plain text file or a binary file. There are usually no structural relationships between the records. Each line of the text file holds one record, with fields separated by delimiters, such as commas or tabs. This is a very old database approach, but can still be found in use to, often with some relation capability enhancements,.  Some example of current flat file databases are: GRAV, Jekyll, Kerby, and Monstra.

Related References

 

Database – What is a Composite Primary Key?

Advertisements
Database Table

What is a Composite Primary Key?

A Composite Primary key is Primary key What a primary key, which is defined by having multiple fields (columns) in it.  Like a Primary Key what a composite Primary Key is depends on the database.  Essentially a Composite Primary Key:

  • Is a combination of Fields (columns) which uniquely identifies every row.
  • Is an index in database systems which use indexes for optimization
  • Is a type of table constraint
  • Is applied with a data definition language (DDL) alter command
  • And may define parent-Child relationship between tables

Related References

Database – What is a Primary Key?

Advertisements
Database Table

What is a primary Key?

What a primary key is depends, somewhat, on the database.  However, in its simplest form a primary key:

  • Is a field (Column) or combination of Fields (columns) which uniquely identifies every row.
  • Is an index in database systems which use indexes for optimization
  • Is a type of table constraint
  • Is applied with a data definition language (DDL) alter command
  • And, depending on the data model can, define parent-Child relationship between tables

Related References