Explore
For Enterprise
Join for Free
Log In

Artes e Humanas
Negócios
Ciência da Computação
Ciência de Dados
Tecnologia da informação
da saúdesaúde
Matemática e Lógica
Desenvolvimento Pessoal
Ciência e Engenharia Física
Ciências Sociais
Línguas
Notas
Certificados

Explore todo o Coursera

Navegar
Pesquisar
For Enterprise
Entrar
Inscreva-se gratuitamente

Relational Algebra Operators: Cross Product cont'd, Join

Loading...

Manipulação de dados em escala: sistemas e algoritimos

Universidade de Washington

4.3 (687 classificações) | 49K alunos inscritos

Curso 1 de 4 no Ciência de dados em larga escalaCiência de Dados em Larga Escala Programa de cursos integrados

Data analysis has replaced data acquisition as the bottleneck to evidence-based decision making --- we are drowning in it. Extracting knowledge from large, heterogeneous, and noisy datasets requires not only powerful computing resources, but the programming abstractions to use them effectively. The abstractions that emerged in the last decade blend ideas from parallel databases, distributed systems, and programming languages to create a new class of scalable data analytics platforms that form the foundation for data science at realistic scales. In this course, you will learn the landscape of relevant systems, the principles on which they rely, their tradeoffs, and how to evaluate their utility against your requirements. You will learn how practical systems were derived from the frontier of research in computer science and what systems are coming on the horizon. Cloud computing, SQL and NoSQL databases, MapReduce and the ecosystem it spawned, Spark and its contemporaries, and specialized systems for graphs and arrays will be covered. You will also learn the history and context of data science, the skills, challenges, and methodologies the term implies, and how to structure a data science project. At the end of this course, you will be able to: Learning Goals: 1. Describe common patterns, challenges, and approaches associated with data science projects, and what makes them different from projects in related fields. 2. Identify and use the programming models associated with scalable data manipulation, including relational algebra, mapreduce, and other data flow models. 3. Use database technology adapted for large-scale analytics, including the concepts driving parallel databases, parallel query processing, and in-database analytics 4. Evaluate key-value stores and NoSQL systems, describe their tradeoffs with comparable systems, the details of important examples in the space, and future trends. 5. “Think” in MapReduce to effectively write algorithms for systems including Hadoop and Spark. You will understand their limitations, design details, their relationship to databases, and their associated ecosystem of algorithms, extensions, and languages. write programs in Spark 6. Describe the landscape of specialized Big Data systems for graphs, arrays, and streams

Visualizar o programa do curso

Habilidades que você aprenderá

Relational Algebra, Python Programming, Mapreduce, SQL

Avaliações

4.3 (687 classificações)

5 stars
386 ratings
4 stars
183 ratings
3 stars
67 ratings
2 stars
33 ratings
1 star
18 ratings

DK

Jan 24, 2016

Good! I like the final (optional) project on running on a large dataset through EC2. The lectures aren't as polished and compact as they could be but certainly a very valuable course.

LG

Jun 20, 2017

Comprehensive and clear explanation of theory and interlinks of the up-to-date tools, languages, tendencies. Kudos and thanks to Bill Howe.\n\nHighly recommended.

Na lição

Relational Databases and the Relational Algebra

Relational Databases are the workhouse of large-scale data management. Although originally motivated by problems in enterprise operations, they have proven remarkably capable for analytics as well. But most importantly, the principles underlying relational databases are universal in managing, manipulating, and analyzing data at scale. Even as the landscape of large-scale data systems has expanded dramatically in the last decade, relational models and languages have remained a unifying concept. For working with large-scale data, there is no more important programming model to learn.

Algebraic Optimization Overview6:33

Relational Algebra Overview4:18

Relational Algebra Operators: Union, Difference, Selection6:07

Relational Algebra Operators: Projection, Cross Product4:46

Relational Algebra Operators: Cross Product cont'd, Join6:00

Relational Algebra Operators: Outer Join4:08

Relational Algebra Operators: Theta-Join4:00

Ministrado por

Bill Howe
Director of Research

Transcrição

So what does the cross product look like?

Well imagine you had table employee with these two columns and

a table dependent with these two columns,

and we do a, oops there's a, there we do a cross product of employee with dependent.

That's all possible combinations of employees with all possible

combinations of dependents.

So we know there's going to be four in the output.

And you can check to see that John is here twice, once for

every instance in dependent and so on.

All right.

So now let's talk about join, and

I probably maybe should have put a slide in here just about join in general, first.

So really, we're talking about join.

The most common instance of join that you're going to run into.

And in fact, if we don't qualify it, this is what we mean,

is what I'll call equi-join.

And so equi-join is a join with an equality condition right here.

Ok, so what does join do?

Join says, for every record in R1

find me a corresponding record in R2 that satisfies some condition.

And, in general, especially for those of you familiar with databases,

you're going to be thinking of that in terms of equi-join, this would be for

every course in finding student IDs that appear in particular courses.

Okay.

So joins on primary key and foreign key if you don't mind the jargon,

are instances of equi-joins.

They need not be.

This is one point I want to make.

So we're not talking too much about schemas.

In this course, I'm more interested in teaching about the relational algebra and

showing how it comes up rather than teaching about how to

design a database in the first place.

And the reason for that is that you often don't have the luxury,

I made this point before, we don't have the luxury of a engineered schema.

You don't have time to build one.

You don't have- there isn't one handed to you in the first place.

There perhaps isn't much need for one in that you're going to get your

answer to a couple of questions and then you might sorta move on to something else.

So there's not really a way to amortized the cost of developing the scheme in

the first place and so on.

So If you work with databases,

most of the joins you're doing are going to be along these predefined relationships

that are called foreign keys, but those need not exist in order to apply a join.

Okay.

And then I'll point out just this sort of syntactic note.

You could write this two different ways in SQL.

You'll sometimes see select star from R1 join R2 on some join condition.

And other times you'll just say select star from R1,

R2 where this condition is met.

Now if I literally translated this into a brain-dead relational algebra plan.

And we will talk about how to do this a little more mechanically in a bit.

This is actually saying sort of saying well hey look,

first build the cross product of R1 and R2, right?

And then filter that cross product so that this condition is met.

Okay.

And this one is saying no, no, no, don't do that.

Actually use the join operator.

Rather than generating the cause product, but

the optimizer, the databases are not that stupid.

Right? They're smart enough to figure out that

even in this case the right way to do this is to express a join.

And so in practice there's no difference between these two different ways of

spelling the same query.

Okay. And in fact, to do a first approximation,

two equivalent queries, different syntax but

same semantics in SQL, there's not gonna be any difference between them.

The optimizer doesn't care how you write your SQL.

It's going to optimize the thing anyway, it's gonna turn into relational plan and

manipulate that plan to guess the best way to evaluate that query, okay.

And so I say to it first approximation because there are such things as query

hints and other ways you can sort of

tell the optimizer how you'd like the query to be evaluated.

We're not going to talk about that because they're rarely important.

And the other thing, it's not impossible to have two different queries that do

the same thing that the optimizer can't figure out are actually equivalent.

It's not impossible to get two different plans.

But typically that won't be the case.

And in this example, that doesn't matter at all.

Okay.

Join versus the where clause.

I'll tend to write queries this way even when they come up, okay.

Fine. So that's the most common and

the simplest instance of this join operator.

Okay, in fact I think I did us a disservice here by doing this on left and

right.

This actually kind of a nice example.

This is the SQL equivalent of writing join, and this is the relational,

sort of SQL equivalent of writing cross product followed by a selection.

Right?

They're the same.

In fact, the only reason why in the algebra, you remember,

the algebra is a formalism.

Maybe you don't care so much about deriving new operators as long as you can

express a thing it doesn't matter.

There's so much work and there's so many good algorithms for

implementing join that it deserves its place as a specific operation.

We don't wanna have to write cross product followed by select.

When we're actually talking about the efficient join algorithms okay.

[MUSIC]

Explore nosso catálogo

Registre-se gratuitamente e obtenha recomendações, atualizações e ofertas personalizadas.

O Coursera proporciona acesso universal à melhor educação do mundo fazendo parcerias com as melhores universidades e organizações para oferecer cursos on-line.

© 2019 Coursera Inc. Todos os direitos reservados.

Baixar na App Store

Baixar no Google Play

Coursera
Sobre
Liderança
Carreiras
Lista de cursos
Certificados
Certificados MasterTrack™
Notas
For Enterprise
For Government

Comunidade
Learners
Partners
Developers
Beta Testers
Translators

Conectar
Blog
Facebook
LinkedIn
Twitter
Instagram
YouTube
Tech Blog

Mais
Termos
Privacidade
Ajuda
Acessibilidade
Imprensa
Contato
Diretório
Afiliados