Databases and Data Warehouses Lecture BigData Analytics Julian M. Kunkel julian.kunkel@googlemail.com University of Hamburg / German Climate Computing Center (DKRZ) 2017-11-03 Disclaimer: Big Data software is constantly updated, code samples may be outdated. Relational Model Databases and SQL Advanced Features for Analytics Data Warehouses Summary Outline 1 Relational Model 2 Databases and SQL 3 Advanced Features for Analytics 4 Data Warehouses 5 Summary Julian M. Kunkel Lecture BigData Analytics, WiSe 17/18 2 / 49 Relational Model Databases and SQL Advanced Features for Analytics Data Warehouses Summary Relational Model [10] Database model based on first-order predicate logic Theoretic foundations: relational algebra and calculus Data is represented as tuples Relation/Table: groups similar tuples Table consists of rows and named columns (attributes) No duplicates of complete rows allowed In a pure form, no support for collections in tuples Schema: specify structure of tables Datatypes (domain of attributes) Organization and optimizations Consistency via constraints Source: Relational model concepts [11] Julian M. Kunkel Lecture BigData Analytics, WiSe 17/18 3 / 49 Relational Model Databases and SQL Advanced Features for Analytics Data Warehouses Summary Example Schema for our Students Data Description Database for information about students and lectures Relational model Matrikel 242 245 Name Hans Fritz Birthday 22.04.1955 24.05.1995 ID 1 2 Name Big Data Analytics Hochleistungsrechnen Lecture table Student table Matrikel 242 242 245 LectureID 1 2 2 Attends table representing a relation Julian M. Kunkel Lecture BigData Analytics, WiSe 17/18 4 / 49 Relational Model Databases and SQL Advanced Features for Analytics Data Warehouses Summary Relationships Model relationships between data entities Cardinality defines how many entities are related One-to-many: One entity of type A with many entities of type B Man