Examining Motivation and Basic Concepts

Objectives

After completing this lesson, you will be able to:

List the goals of the Relational Database Model and the three-level schema architecture.
Describe SQL and its relationship to the relational model.
Describe the database objects and components of database tables.

Terinology: Database Management System, Database, and Database System

The terms Database, Database System, and Database Management System are frequently used incorrectly and interchangeably.

"Which Database Management System do you use?"

"We are using SAP HANA as our Database Management System."

In everyday life, these terms are usually used incorrectly …

The terms in fact refer to slightly different concepts:

Database (DB): A database is a concrete structured collection of records.
Database Management System (DBMS): A database management system is software that manages databases. Every access to a database (create, read, insert, update, delete) goes through the DBMS. The DBMS exercises complete control over the databases it manages.
Database System: A database system is the combination of the implementation of a DBMS and the databases it manages.

Illustrating showing DBS equals DB plus DBMS.

Goals of the Relational Database Model

The way that data records are structured and how DBMSs are implemented are referred to as database models. The following are some of the database models that have been developed over the past decades:

Hierarchical database model
Network database model
Object-oriented database model
Object-relational database model
Relational database model
XML-based database model

Design Goals of the Relational Database Model

The Relational Database Model was invented in the late 1960s by Edgar F. Codd (1923–2003) while working at IBM Almaden Research Lab in San José, California. The Relational Database Model was designed to meet the following specific goals:

Provide a simple yet mathematically profound and precise database model.
Make monitoring data integrity largely a responsibility of the DBMS.
Separate the conceptual schema from the internal schema.
Make data storage and retrieval the sole responsibility of the DBMS.
- A descriptive data access language removes the need to traverse data links.
- Database optimizers choose the optimal execution strategy for a given query.
Provide a simple database access language with semantics described with mathematical precision.
- Easy to learn.
- Equivalence of different queries can be proved.

The goal behind separating the conceptual schema from the internal schema is to allow for a three-level architecture so that changes on a lower level do not impact higher levels.

Illustration showing the three-level schema architecture.

The External Schema defines how data is presented to the user:

(Partial) views of the data as required by applications or users
In the case of a relational database system, implemented via views

The Conceptual Schema defines what is stored:

Overall presentation of the data model at the logical, (if possible) DBMS, and application-independent level
For example, in a relational representation, or even higher level of abstraction (for example, E/R model)

The Internal Schema defines how and where it is stored:

Describes (DBMS-specific) the internal, physical representation of data
How and where exactly the data is stored, internal record format, access paths, and so on

Three Level Schema Architecture Overview

The address book should not display salary data	External Level (VIEW)
An Employee has a D-Number, name, and salary and is assigned to a department	Conceptual Level (TABLE)
The board mostly accesses employee data according to descending order of salary (which has to be very fast)	Internal Level (INDEX)

The Role of SQL

Languages for the Relational Database Model

The following languages are available to interact with a relational DBMS:

Relational Algebra	Formal basis for DBMS internal query optimization Six basic operations: Selection, Projection, Cartesian Product, Union, Difference, and Rename
Relational Calculus	Tuple variables and quantifiers
SQL	Standardized and used in practice

Why SQL?

In almost every business application scenario, the data is managed using database systems.

The most significant are database systems based on the relational data model and using SQL (Structured Query Language) as a database language.

SQL is a widely-established, powerful, standardized database language many application programmers have experience in.

There is (so far) no other database language that has all the advantages mentioned.

SAP HANA is a relational database management system.
SAP HANA supports SQL.

SQL and Relational Model

SQL is the most important database language for the relational database model. However, it deviates in important points from the purely relational model.

Because SQL is designed to work with data stored in tables (in other words with sets) it is multi-set oriented and not single record-based. Using a single SQL statement, you can read multiple table rows, modify, or delete them in one go.

SQL is Not Fully Relational

SQL deviates from the purely relational model in the following ways:

Results of SQL queries can contain duplicates (identical rows). This makes SQL multi-set oriented and not set oriented.
SQL allows for NULL values, leading to a three-valued logic.
SQL language is not closed.

ANSI SQL

SQL is a computer language that is used to define, manipulate, and control relational databases. It is defined by the American Standards Institute (ANSI).

SQL is designed as a descriptive rather than procedural language. Using SQL you can express what data you want, not exactly how to retrieve it. This design allows the DBMS to first parse each statement and then optimize it. In theory, the optimizer determines the best execution plan.

SAP HANA SQL and SQLScript

SAP HANA SQL, including SQLScript, is the computer language created by SAP and used in the SAP HANA platform. It is built on a foundation of standard ANSI SQL and it extends the standard considerably, in part to better support the column and in-memory store nature of SAP HANA.

SAP HANA SQL and SQLScript Elements

The following is a list of selected elements of SAP HANA SQL:

Standard (ANSI) SQL statements
SAP extensions to SQL statements for SAP HANA, for example:
- DML extensions, for example, SELECT INTO, UPSERT
- DDL extensions, for example to allow creating column and row store tables
- Functions
- Triggers
Declarative SQLScript statements, for example variable assignments
Imperative SQLScript statements (flow control statements)

Database Objects

Tables are the primary database object, but not the only one. Apart from tables, a database usually contains the following:

Views to simplify and limit data access
Indexes to speed up (certain) read accesses
Constraints to ensure data consistency
Stored procedures for more complex tasks
Triggers to selectively respond to particular events

Database Tables

A table consists of columns (structure) and rows (data instances).	A table can therefore be represented two-dimensionally, but the horizontal dimension represented by rows is optional; put another way, a table may have zero rows (no data instances) but may not have zero columns.
A (database) table represents a relation.	A table represents an (unordered) multi-set of points in n-dimensional space. Each of the n dimensions is equivalent to a table column.

Components of Database Tables

Keys

A key is a set of one or more columns which serves to uniquely identify any row in the table. The ability to uniquely identify rows must apply in principle (and not only for the rows existing at a certain point in time).

Example of a database table showing key.

Multi-Column Keys

A key can consist of multiple columns.

In this example, the key consists of seven columns. There is just one key, not seven.

Multiple Keys

There may be more than one key, if more than one column (or combination of columns) ensures uniqueness of row values.

Example of a database table showing multi keys.

Here there are seven keys (provided there are no joint accounts). Just one key is selected as the primary key; others that could serve as the primary but are not chosen are known as candidate keys.

Foreign Keys

A foreign key is a set of columns, whose values are found in a (primary) key in an(other) table:

The foreign key can refer to its own table.
It is not necessary for the columns to share the same names.
The foreign key can contain only those values that occur as a (primary) key value in another table (in addition and if applicable, NULL values are allowed).
The foreign key is usually not a candidate or primary key!

Database tables showing foreign-key relationship.

Multi-Column Foreign Keys

A foreign key can consist of multiple columns.

This example shows one foreign key built on two columns.

Multiple Foreign Keys

There can be multiple foreign keys.

Self-Referencing Foreign Keys

The foreign key can refer to its own table.

Next lesson