Data Management for Analytics Part 2

Northeastern University via Coursera

Go to class Write review

Details

Go to class

Provider

Coursera
Pricing

Paid Course
Languages

English
Certificate

Paid Certificate Available
Duration & workload

9 hours 40 minutes
Sessions

On-Demand
Level

Intermediate
Subtitles

English

Found in

Overview

Coursera Flash Sale

40% Off Coursera Plus for 3 Months!

Grab it

This course will offer you an opportunity to learn the fundamental concepts and emerging technologies in data storage and data governance. It presents a balanced theory-practice focus and covers Structured Query Language, and two flavors of NoSQL databases in MongoDB and Neo4j graph database. It also includes a brief introduction to big data management including hadoop, MapReduce, and Apache Spark. By the end of this part 2 course on data analytics, you will have a foundational understanding of the theory and applications of database management to support data analytics, data mining, machine learning, and artificial intelligence.

Syllabus

Structured Query Language, Part 1

This module first presents an overview of the structured query language (SQL) Data Definition Language (SQL DDL) to define a relational data model. It examines the schema creation, table creation, drop command, and alter command. Various syntaxes are illustrated with explicit examples. This module also discusses the SQL Data Manipulation Language (SQL DML) used to retrieve data, update data, insert new data, and delete existing data. The focus is on SQL INSERT statements for inserting data into tables and some simple SQL SELECT statements. More complex SQL SELECT statements will be discussed in later modules along with SQL DELETE and SQL UPDATE statements.

Structured Query Language, Part 2

This module continues the discussion of the SQL data manipulation language (DML) SELECT statement. It introduces various aggregate functions: COUNT, SUM, AVG, VARIANCE, MIN, and MAX, which are used to summarize information from database tuples. This is followed by the GROUP BY/HAVING clause, which allows the application of aggregate functions to subgroups. This module then discusses join queries that allow the user to combine or join data from multiple tables. The inner join queries feature a “where” clause that matches one or multiple columns from two tables. The left outer join, right outer join, and full outer join can be used to keep all the tuples of one or both tables in the result, regardless of whether or not they have matching tuples in the other table. All queries in this module use the Wine database in the online playground and can be executed there.

Structured Query Language, Part 3

This module presents more complex SQL queries. It introduces nested queries where a complete SELECT FROM block appears in the WHERE clause of another query. The subquery or inner block is nested in the outer block and there can be multi-level nesting. The query optimizer usually flattens the nested query into multiple queries and executes them sequentially from the innermost to the outermost level. This module also examines the correlated nested query, where the inner block uses one or more columns of the table defined in the outer block. In this case, the query cannot be flattened, and the inner block subquery must be evaluated for each tuple of the table (also used in the inner block). The usage of the operators >= ALL and > ANY is discussed. The former can be used to find the highest or largest values whereas the latter can be used to exclude the lowest or smallest values. All queries in this module use the Wine database in the online playground and can be executed there. Finally, this module examines the DELETE and UPDATE statements that can be used to delete or modify data. It concludes with a brief discussion of SQL views.

Extension to Relational Database Management Systems

This module introduces a couple of extensions to the Relational Database Management Systems (RDBMSs). We will start by reviewing the core components of the relational model and its limitations. Subsequently, the module explores methods for extending relational databases, starting with a thorough review of triggers and stored procedures as pivotal mechanisms for augmenting the activity of RDBMSs. The module concludes by delving into the intricacies of recursive queries, a powerful extension to the SQL language.

NoSQL Databases: MongoDB

This module presents an overview of the NoSQL movement and distributed systems. MongoDB NoSQL database is discussed at the introductory level. MongoDB is intended for storing documents such as resumes, legal documents, books, etc. It does not use any schema or data model, and stores documents as collections — which store a collection of attributes labeled and unordered that represent semi-structured items.

NoSQL Databases: Neo4j Graph Database

This module continues the discussion of the NoSQL database. The graph theory and Neo4j graph database are discussed at the introductory level. The Neo4j is a graph database that applies graph theory to information storage. It consists of nodes and edges, both of which can store information. Graph databases are particularly useful in modeling social networks such as X (formerly known as Twitter) and Facebook. In a way, a graph database is a hyper-relational database where join tables are replaced by more interesting and semantically meaningful relationships that can be navigated (graph traversal) and/or queried, based on graph pattern matching.