Databases and Data Warehouses Lecture BigData Analytics Julian M. Kunkel julian.kunkel@googlemail.com University of Hamburg / German Climate Computing Center (DKRZ) 2016-11-04 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, 2016 2 / 50
Relational Model Databases and SQL Advanced Features for Analytics Data Warehouses Summary 1 Relational Model 2 Databases and SQL 3 Advanced Features for Analytics 4 Data Warehouses 5 Summary Julian M. Kunkel Lecture BigData Analytics, 2016 3 / 50
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, 2016 4 / 50
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 ID Name Matrikel Name Birthday 1 Big Data Analytics 242 Hans 22.04.1955 2 Hochleistungsrechnen 245 Fritz 24.05.1995 Lecture table Student table Matrikel LectureID 242 1 242 2 245 2 Attends table representing a relation Julian M. Kunkel Lecture BigData Analytics, 2016 5 / 50
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 Many-to-many: One-to-many in both directions One-to-one: One entity of type A with at most one entity of type B Relationships can be expressed with additional columns Packing data of entities together in the table Alternatively: provide a “reference” to other tables Matrikel Name Birthday Lecture ID Lecture Name 242 Hans 22.04.1955 1 Big Data Analytics 242 Hans 22.04.1955 2 Hochleistungsrechnen 245 Fritz 24.05.1995 2 Hochleistungsrechnen Student table with attended lecture information embedded Julian M. Kunkel Lecture BigData Analytics, 2016 6 / 50
Relational Model Databases and SQL Advanced Features for Analytics Data Warehouses Summary Entity Relationship Diagrams Illustrate the relational model and partly the database schema Elements: Entity, relations, attributes Additional information about them, e.g., cardinality, data types * * Student attends Lecture Name Birthday ID Name Matrikel A student/lecture example in modified Chen notation * is the cardinality and means any number is fine Julian M. Kunkel Lecture BigData Analytics, 2016 7 / 50
Relational Model Databases and SQL Advanced Features for Analytics Data Warehouses Summary Keys [16, 17, 18] A Superkey1 allows addressing specific tuples in a table Superkey: Set of attributes that identify each tuple in a table There can be at most one tuple for each possible key value A superkey does not have to be minimal e.g., all columns together are a Superkey of any table After removing an attribute, it can still be a key Simple key: key is only one attribute Compound key: consists of at least two attributes Candidate key: a minimal key, i.e., no attribute can be removed Primary key: the selected candidate key for a table Foreign key: inherited key of another table Natural key: key that naturally is unique, e.g., matrikel Surrogate key: artificial key, e.g., numeric ID for a row 1Often it is just called key Julian M. Kunkel Lecture BigData Analytics, 2016 8 / 50
Relational Model Databases and SQL Advanced Features for Analytics Data Warehouses Summary Example Keys Student table Lecture table Matrikel Name Birthday ... ID Name Semester 242 Hans 22.04.1955 1 Big Data Analytics SS15 245 Fritz 24.05.1995 2 Hochleistungsrechnen WS1516 Attends table representing a relation Matrikel LectureID 242 1 242 2 245 2 Student table Candidate keys: Matrikel, (name, birthday, city), social insurance ID Primary key: Matrikel (also a natural key) Lecture table Candidate keys: ID, (Name, Semester) Primary key: ID (also a Surrogate Key) Attends table Candidate key: (Matrikel, Lecture ID) Primary key: (Matrikel, Lecture ID) Julian M. Kunkel Lecture BigData Analytics, 2016 9 / 50
Relational Model Databases and SQL Advanced Features for Analytics Data Warehouses Summary Normalization [10]: My Simplified Perspective Normalization is the process of organizing the columns and tables to minimize redundancy [19] Reduces dependencies Prevents inconsistency across replicated information Normally, reduces required storage space and speeds up updates There are different normal forms with increasing requirements 1NF: It follows our notion of a table. No collections in the table. A primary key exists. 2NF: No redundancy of data i.e., entities of many-to-many relations are stored in separate tables Every column must depend on each candidate key and not a subset 3NF: Columns are not functional dependent to sth. else than a candidate key 4NF: Do not store multiple relationships in one table 4NF is a good choice2 for transactional data processing but not big data 2It has been shown that 4NF can always be achieved for relational data Julian M. Kunkel Lecture BigData Analytics, 2016 10 / 50
Relational Model Databases and SQL Advanced Features for Analytics Data Warehouses Summary Example for Unnormalized Data Matrikel Name Birthday Name 242 Hans 22.04.1955 [Big Data Analytics, Hochleistungsrechnen] 245 Fritz 24.05.1995 Hochleistungsrechnen Not normalized Student and lecture table/relation, contains identical column names and collections. Problematic if we want to update the name of an lecture. Matrikel Name Birthday Lecture Name 242 Hans 22.04.1955 Big Data Analytics 242 Hans 22.04.1955 Hochleistungsrechnen 245 Fritz 24.05.1995 Hochleistungsrechnen Student and lecture table/relation in 1NF, it contains a many-to-many relation. Changing lecture name requires still to touch multiple rows. Julian M. Kunkel Lecture BigData Analytics, 2016 11 / 50
Relational Model Databases and SQL Advanced Features for Analytics Data Warehouses Summary Example for Unnormalized Data Matrikel Name Birthday Age 242 Hans 22.04.1955 40 245 Fritz 24.05.1995 20 In 2NF but not 3NF: Age is functional depending on birthday Matrikel Attended lecture Attended seminar 242 BDA SIW 242 HR SIW 242 BDA NTH 242 HR NTH In 3NF but not 4NF: Candidate key depends on all three columns Julian M. Kunkel Lecture BigData Analytics, 2016 12 / 50
Relational Model Databases and SQL Advanced Features for Analytics Data Warehouses Summary 1 Relational Model 2 Databases and SQL 3 Advanced Features for Analytics 4 Data Warehouses 5 Summary Julian M. Kunkel Lecture BigData Analytics, 2016 13 / 50
Relational Model Databases and SQL Advanced Features for Analytics Data Warehouses Summary Databases [29] Database: an organized collection of data Includes layout (schemes), queries, views Database models: Relational, graph, document, ... Database management system (DBMS): software application that interacts with the user, other applications and the database itself to capture and analyze data [29] Definition, creation, update, querying and administration of databases DBMS functions for managing databases Data definition: Creation, modification of definitions for data organization Update: Insertion, modification and deletion of data Query/Retrieval: retrieving stored and computing derived data Administration: users, security, monitoring, data integrity, recovery Julian M. Kunkel Lecture BigData Analytics, 2016 14 / 50
Relational Model Databases and SQL Advanced Features for Analytics Data Warehouses Summary Structured Query Language (SQL) [20] Declarative language: specify what to achieve and not how Evolving standard with growing feature set Language elements Statement: instructions to perform, terminate by ; Query: alternative name; usually retrieves/computes data Clause: components of statements Predicates: conditions limiting the affected rows/columns Expressions: produce scalar values or tables Operators: compare values, change column names Functions: transform/compute values Julian M. Kunkel Lecture BigData Analytics, 2016 15 / 50
Relational Model Databases and SQL Advanced Features for Analytics Data Warehouses Summary PostgreSQL [10] A popular database implementation Semantics: ACID support for transactions A transaction is a batch of operations It either fails or succeeds Implements majority of SQL:2011 standard Syntax may differ from SQL standard and extensions are provided Interactive shell via psql Excerpt of features Materialized views (create virtual tables from logical tables) Fulltext search Regular expression Statistics and histograms User defined objects (functions, operators) Triggers: events upon insert or update statements; may invoke functions New versions support semi-structed data in arrays, XML, JSON3 3See http://www.postgresql.org/docs/9.4/static/arrays.html and .../functions-json.html Julian M. Kunkel Lecture BigData Analytics, 2016 16 / 50
Relational Model Databases and SQL Advanced Features for Analytics Data Warehouses Summary Schemas (in Postgres) Creation of a database and table 1 CREATE ROLE "bigdata" NOSUPERUSER LOGIN PASSWORD ’mybigdata’; 2 CREATE DATABASE bigdata OWNER "bigdata"; To connect to the database use psql -W -U bigdata bigdata Create our tables 1 CREATE TABLE students (matrikel INT, name VARCHAR, birthday DATE, PRIMARY KEY(matrikel)); 2 CREATE TABLE lectures (id SERIAL, name VARCHAR, PRIMARY KEY(id)); 3 CREATE TABLE attends (matrikel INT, lid INT, 4 FOREIGN KEY (matrikel) REFERENCES students(matrikel), 5 FOREIGN KEY (lid) REFERENCES lectures(id)); 6 --\d <TABLE> prints the schema Additional constraints 1 -- minimum length of the name shall be 5 2 ALTER TABLE students ADD CONSTRAINT length CHECK (char_length(name) > 3); 3 -- to remove the constraint later: ALTER TABLE students DROP CONSTRAINT length ; 4 -- minimum age of students should be 10 years 5 ALTER TABLE students ADD CONSTRAINT age CHECK (extract(’year’ from age(birthday)) > 10); 6 -- disallow NULL values in students 7 ALTER TABLE students ALTER COLUMN birthday SET NOT NULL; -- during CREATE with "birthday DATE NOT NULL" 8 ALTER TABLE students ALTER COLUMN name SET NOT NULL; Julian M. Kunkel Lecture BigData Analytics, 2016 17 / 50
Relational Model Databases and SQL Advanced Features for Analytics Data Warehouses Summary Populating the Tables 1 -- Explicit specification of columns, not defined values are NULL 2 INSERT INTO students (matrikel, name, birthday) 3 VALUES (242, ’Hans’, ’22.04.1955’); 4 -- Insertation of the same name twice could be prevented using a constraint 5 INSERT INTO students (matrikel, name) VALUES (246, ’Hans’); 6 -- Order is expected to match the columns in the table 7 INSERT INTO students VALUES (245, ’Fritz’, ’24.05.1995’); 8 INSERT INTO lectures VALUES (1, ’Big Data Analytics’); 9 INSERT INTO lectures VALUES (2, ’Hochleistungsrechnen’); 10 11 -- Populate relation 12 INSERT into attends VALUES(242, 1); 13 INSERT into attends VALUES(242, 2); 14 INSERT into attends VALUES(245, 2); 15 16 -- Insertations that will fail due to table constraints: 17 INSERT INTO students (matrikel, name) VALUES (250, ’Hans’); 18 -- ERROR: null value in column "birthday" violates not-null constraint 19 INSERT INTO students VALUES (250, ’Hans’, ’22.04.2009’); 20 -- ERROR: new row for relation "students" violates check constraint "age" 21 INSERT INTO students VALUES (245, ’Fritz’, ’24.05.1995’); 22 -- ERROR: duplicate key value violates unique constraint "students_pkey" 23 -- DETAIL: Key (matrikel)=(245) already exists. Julian M. Kunkel Lecture BigData Analytics, 2016 18 / 50
Relational Model Databases and SQL Advanced Features for Analytics Data Warehouses Summary Queries [20] A query retrieves/computes a (sub)table from tables It does not change/mutate any content of existing tables Statement: SELECT < column1 >, < column2 >, ... Subqueries: nesting of queries is possible Supported clauses FROM: specify the table(s) to retrieve data WHERE: filter rows returned GROUP BY: group rows together that match conditions HAVING: filters grouped rows ORDER BY: sort the columns 1 SELECT Matrikel, Name FROM students WHERE Birthday=’22.04.1955’; 2 -- Returns a table with one row: 3 -- matrikel | name 4 -- ----------+------ 5 -- 242 | Hans Julian M. Kunkel Lecture BigData Analytics, 2016 19 / 50
Relational Model Databases and SQL Advanced Features for Analytics Data Warehouses Summary More Queries Ordering of results 1 -- Example comment, alternatively /* */ 2 select * from students 3 where (name != ’fritz’ and name != ’nena’) -- two constraints 4 order by name desc; -- descending sorting order Aggregation functions 1 -- There are several aggregate functions such as max, min, sum, avg 2 select max(birthday) from students; 3 -- 1995-05-24 4 5 -- It is not valid to combine reductions with non-reduced columns e.g. 6 select matrikel, max(birthday) from students; -- ERROR! Counting the number of students 1 -- Number of students in the table and rename the column to number 2 SELECT count(*) AS number FROM students; 3 -- number 4 -- 2 Julian M. Kunkel Lecture BigData Analytics, 2016 20 / 50
显示前 20 页。下载资料查看完整内容。
暂无评论。快来抢沙发吧！
评论 (0)
暂无评论。快来抢沙发吧！