Notes

Relational Database A relational database is an entity consisting of logical units known as tables (also called relations). A relational database is based on the relational model of data proposed by E. F. Codd in 1970. Data in a relational database systems can be accessed using SQL (Structured Query Language). An RDBMS (Relational Database Management System) is a software system used to run and maintain relational databases.

RDBMS Key features of RDBMS:

• Stores data into tables.
• Allows the creation of new databases and their data structures.
• Allows data query and modification using an appropriate programming language (SQL).
• Allows the storage of vast amounts of data over a long period of time.
• Enables database recovery in times of failure, error or intentional misuse.
• Controls data access from many users at once.

Tables A table is defined by the following metadata:

• A Name
• One or more columns (or fields)
• A primary key, which uniquely identifies a row (or record)

Fields & Records Columns or fields

• Fields are the building blocks of a table.
• They define the data structure of the table.
• A field is made of:
  • A Name
  • A Data Type (string, number, date, boolean, binary)
  • A NULL constraint. It's an attribute that specifies if the column can contain NULL values or not.

Records or tuples

• A table row is also called record or tuple.
• A record holds data for a single "entity" (i.e., a customer, a product, a supplier).
• A record contains a value for each field (for a single entity).

Data Types

String

• CHAR(n) = fixed length n.
• VARCHAR(n) = variable length, maximum n.

Date

• DATE
• DATETIME
• TIME

Numbers

• INT

Floating point

• FLOAT

Fixed Decimals

• DECIMAL (precision, scale). Ex: DECIMAL(5,2) = 5 digits with 2 decimals.

Primary Key

The primary key constraint guarantees the record uniqueness. When we put the primary key constraint on a column, its values must be unique. If we try and insert a duplicate value the RDBMS rejects it and returns an error:

Creating a table using SQL We use the CREATE TABLE command, followed by the columns list (name, data type and NULL constraint).

Foreign Key As the primary key uniquely represents a record in a table, we can use the primary key value to make a reference to that record. For example: let's say that we have the sales table and we want to keep track of each safe, we could create the table like this:

We don't repeat all the data for each customer, but we just use the customer_id field (the primary key) to reference a customer. But what happens if we put a non existing customer_id into the sales table? We loose consistency, because we would have a reference to a customer that doesn't exist!! To avoid this situation we can create a foreign key constraint on the sales table. The foreign key links the customer_id column of the sales table to the customer_id column in the Customers table and doesn't allow the insertion of a non existing customer_id.

Data Integrity The primary key constraint guarantees the entity (table) integrity, which means no duplicate rows (or no duplicate keys). The foreign key protects the database against the violation of the referential integrity. In fact a field with a foreign key constraint:

• Can be NULL
• Or MUST contain a value that matches one value taken from the linked primary key.

The data type of a column and the NULL constraint specify and protect the domain integrity: all the values of the same column belong to the same domain. In addition the user can define other constraint, called CHECK CONSTRAINT (for example: quantity > 0).

Full SQL Example

Dropping tables We can delete a table using the DROP TABLE command:

Other Objects A Database contains many object types along with the tables:

• Indexes, an index is a data structure that speeds up the data retrieval process. Just like an index in a book, the database index makes it possible to get data in a faster way.

Without indexes the RDBMS must scan the entire table even if we request a single record!

Database Normalization Database Normalization is a technique of organizing the data in the database. Normalization is a systematic approach of decomposing tables to eliminate data redundancy and possible anomalies in insert, update and delete operations. The normalization rules are divided into the following normal forms:

• First normal form
• Second normal form
• Third normal form
• Other normal forms (we won't see them in these lectures...)
  • Boyce-Codd Normal form
  • Fourth Normal form

Denormalized Tables Customer Table

Employee Table

Problems In the Customer table we have:

• A duplicate record.
• The name and address fields that are not atomic: many values are store inside those fields:
  • First and Last Name in the Name Column.
  • Street, City and Country in the Address Column.

In this case it's hard to use the address data (for example, getting the customers that live in NY). In the Employee table we have:

• Data Repetition for Dep.Name and Dep.Phone.

In this case there could be update anomalies: for example if the department phone changes we must update it for all the employees that belong to that department. If an employee goes to another department, we'd have to update all the dep. Columns (code, name and phone). It's really easy to make a mistake...

First Normal Form A table should only have single (atomic) valued columns. Values stored in a column should be of the same domain (same data type). All the columns in a table should have unique names. Records should be unique. A primary key constraint should be set in order to guarantee the record uniqueness.

Third Normal Form The table shouldn't have Transitive Dependencies. We have Transitive Dependency when a non-primary key attribute depends on other non-primary key attributes rather than depending upon the primary key.

OLTP On Line Transaction Processing. Transaction Oriented databases. They are fully normalized:

• Many tables
• Each table has few columns
• No data redundancy! This is the model for operational databases. PROS: The normalization prevents errors in insert/update/delete operations. CONS: But... the normalization makes it difficult to query the database: the analyst must put together many tables in order to create a report.

OLTP Example

Data Warehouse The data warehouse is an analysis oriented database. The purpose of DWH is to provide a simple data model for the analyst. The DWH is made of two types of tables:

• The dimensions, which are denormalized (1^st^ Normal form).
• The fact tables which are normalized (3^rd^ Normal form).

Dimensions Dimensions contains all the attributes of a business entity. The dimension key in the DWH is called surrogate key. It's a progressive number created by the ETL process. The original key (coming from the operational source) is called business key and it's stored in the dimension table. Examples of dimensions (or business entities):

• Customer
• Supplier
• Product
• Accounts
• Branches
• Departments

Fact Tables Fact tables contain:

• The references (surrogate key) to the applicable dimensions.
• Examples:
  • Sales fact table
  • Purchase fact table
  • Bank movements fact table

More on DWH The DWH is fed with data coming from many operational databases. The process that feeds the DWH is called ETL (Extract Transform and Load):

• It extracts data from the data sources.
• It transforms the data according to the business rules.
• It integrates etherogeneous data sources.
• It checks the data quality.
• It generates surrogate keys.
• It takes care of the data integrity in the data warehouse (Generally we have no foreign keys in the DWH and even no primary keys!)

Data Warehouse Example

SQL Language Intro SQL (Structured Query Language) allows us to interact with databases. Technically SQL is:

• A domain-specific language: it only applies to databases.
• A declarative language: it expresses the logic of a computation/data retrieval/data modification without describing its control flow or algorithm. With SQL we write a query, but we don't tell the RDBMS how to actually implement it. RDMBS takes care of that, using a component called optimizer, which chooses the best way to retrieve (or modify) data.
• A procedural language, because all the SQL dialects (implementation inside different RDBMS) contain procedural instructions (like IF, WHILE, ...)

Why SQL for big data? Most of the Big Data and NoSQL tools have an SQL interface:

• Hadoop
  • Hive works with a language called HiveQL which is an ANSI version of SQL + some Hadoop specific commands.
• Spark
  • SparkSQL is a component in the Spark platform.
• NoSQL
  • Native tools
    • Cassandra have the CQL language which is a dialect of SQL.
    • Hbase
  • ODBC Drivers

SQL consists of many types of statements, that can be grouped into 4 sublanguages:

• Data Query Language (DQL) used to retrieve data from the database.
• Data Definition Language (DDL) used to create tables and other objects (views, procedures, etc.)
• Data Control Language (DCL) used to grant access to the database objects. It's used to manage users' permissions on the db objects.
• Data Manipulation Language (DML) used to insert, update and delete data.

Some definitions:

• A Query retrieves data from one or more tables. It begins with the keyword SELECT.
• A Statement modifies data, the table schema (columns and data types) or controls the program flow. Keyword examples:
  • INSERT, UPDATE, DELETE.
  • CREATE TABLE / DROP TABLE
  • BEGIN, END, IF, WHILE
• A Clause is a part of a query or statement. We have:
  • The WHERE clause, that filters the records.
  • The SET clause in the UPDATE statement.
• An Expression returns scalar values or tabular values (rows and columns).
  • Example: Where city = 'Milan' ('Milan' is the expression)
• A Predicate is a logical condition used in the WHERE clause.

Some SQL Dialects