Relational Algebra, Optimization, and more

Question 1

Selection condition

Answer 1

sigma, allows us to specify the subset of tuples we’re interested in (similar to SQL WHERE)

Question 2

Project condition

Answer 2

pi, select a subset of attributes to be returned (similar to SQL SELECT)

Question 3

Relation expressions

Answer 3

allow us to combine selection and projection together

Question 4

Union

Answer 4

U, finds the set of tuples that belong to one set, the other, or both

Question 5

Set difference

Answer 5

-, remove a set of tubles from a second set

Question 6

Union-compatible relations

Answer 6

two relations must have the same number of attributes, same names of the attributes, and same data type for each matching attribute

Question 7

Cartesian product

Answer 7

x, produces the set of all tuples by concatenating each tuple (same as cross product), computationally the most expensive operator

Question 8

Joins

Answer 8

bowtie, join two tables based on some conditions

Question 9

View

Answer 9

psuedo-table used to hold a result set

Question 10

Stored Procedures

Answer 10

query you can store in database to be executed, similar to a function

Question 11

SQL Injection

Answer 11

common attack by entering malicious data via user input

Question 12

Query optimizer

Answer 12

chooses the most efficient way to execute a query

Question 13

Logical optimization

Answer 13

rearranges the query via relational algebra (performed first), want to reduce the number of tuples as quickly as possible

Question 14

Physical optimization

Answer 14

chooses indexes/types of joins (performed second)

Question 15

Database index

Answer 15

data structure stored separately in memory for improving query operations

Question 16

Covering index

Answer 16

index that contains all search terms as well as the output attributes

Question 17

Non-covering index

Answer 17

index that points you to information, but does not store it

Question 18

Distributed database

Answer 18

databases spread across multiple machines, in either the same or separate locations, approached via replication or duplication

Question 19

Replication

Answer 19

looks for changes in any database and pushed changes as necessary (NoSQL approach)

Question 20

Duplication

Answer 20

assigns one database as the master and duplicates that database on the the others any time changes are made to it (RDBMS approach)

Question 21

CAP

Answer 21

Consistency, Availability, Partition tolerance

Question 22

CAP - Consistency

Answer 22

all clients see the same data at the same time

Question 23

CAP - Availability

Answer 23

any client making a request for data gets a response, even if one or more nodes are down

Question 24

CAP - Partition tolerance

Answer 24

the cluster must continue to work despite any number of communication breakdowns (partitions) between nodes in the system