Relational Database Management Systems

Creating data models

Databases and Data Analysis

Databases, and data models, consists of several tables linked to each other via primary key / foreign key relationships. The primary key is represented by one or several columns which uniquely identify a table row. The foreign key is the primary key of a second table connected with the first one. The following simplified 2 table examples represent purchase orders, and suppliers of a book store:

  • OrderID is the primary key of the Purchase Orders table.
  • The Subtotal column in the Purchase Orders table is an example of calculated column, dynamically calculated by the system based on item prices, and quantities. For performance reasons these calculations typically take place on the server side, e.g. using a server programming language, e.g. PHP in case of Web Development, or using Database Management System languages such as SQL (Structured Query Language). For the example below we have used JavaScript to perform these calculations. JavaScript is a programming language, complementing "higher level" HTML code on the Web client side, that is, JavaScript calculations don't directly interact with the server, don't store or change information in the server database, cannot install viruses on your computer, etc. Python, Java, or PHP can do such mischief, because they operate on the server side.
  • The Purchase Orders and Suppliers tables are connected to each other through the SupplierID column.
  • SupplierID is the primary key of the Suppliers table, and acts as foreign key in the Purchase Orders table. The name of the column containing the supplier ID in both tables doesn't need to be the same. It is the IDs themselves that are linked to each other.
  • The Purchase Orders table is a typical example of Transactional Data, that is, data which changes very often (Bewegungsdaten in German).
  • The Suppliers table is a typical example of Master Data, that is, data which doesn't change very often (Stammdaten in German).
ERP, CRM, and Business Intelligence systems are often interested in aggregations of key figures (Kennzahlen) in transactional data, e.g. aggregated revenues per year, country, or product. These aggregations usually take place at the column level, and not at the row level of a table. That's why Business Intelligence systems, and SAP HANA, are optimized for column store calculations. Master Data analysis takes place at the row level, and is interested in characteristic combinations (Markmale) of fields, e.g. qualitative information about suppliers, customers, patients, etc. Database indexes help to efficiently find data in big tables, like the indexes of a book.

Purchase Orders

OrderID BookTitle SupplierID Price Quantity Subtotal Currency Year
10000010 Harry Potter (Book 1) 6010 EUR 2016
10000020 Harry Potter (Book 4) 6010 EUR 2016
10000030 Harry Potter (Book 7) 6010 EUR 2016
10000040 How to loose 10 kg in 10 weeks? 8500 EUR 2016
10000050 How to get rich in 300 days? 8500 EUR 2016
10000060 How to flirt effectively? 8500 EUR 2016
10000070 Hamlet 9200 EUR 2016
Totals: EUR


SupplierID Company Address City ZIP Country FirstName LastName Email
6010 Hogwarts Inc. FullMoon Street 77 SilverHill 177771 Farlands Morgan Dumbldee md@hogwarts.fl
8500 RatAreUs GmbH Große Straße 15 Mehrstadt 696969 Deutschland Marko Klein
9200 LoveClassics Org Thames 77 London BB 6697 KK UK Willy Shakesit