Database Review for Test 2
Left Outer Join
--------------------Keeps every tuple in relation R1; if no match tuple is found in R2, then the attributes of R2 in the join result are filled with null values.
Right Outer Join Operation
--------------------Keeps every tuple in relation R2; if no match tuple is found in R1, then the attributes of R1 in the join result are filled with null values
Aggregate Functions
--------------------compute summary of information
Full Outer Join
-------------------Keeps all tuples in both relations R1 and R2; when no matching tuples are found, padding them with null values as needed.
Selectivity
A measure of how selective a condition is (i.e., the fraction of rows that satisfy a condition).
Search Methods
A number of search algorithms are possible for selecting records from a file, called file scans
Execution
An _______ of the query tree consists of executing internal node operations
Cost Formula
Calculated based on the number of disk I/O operations, CPU cycles, and memory usage.
Dm = min(nb-1, nr)
Degree of Merging
Projection
In Relational Algebra, the symbol π represents which operation?
Rename
In Relational Algebra, what does the ρ (rho) operator do?
FROM
In relational algebra, the JOIN operation is similar to which SQL keyword?
Union
In relational algebra, which operation is used to create a new relation that includes all rows from both relations, without eliminating duplicates?
Intersection
In relational algebra, which operation is used to find common elements between two relations and produce a new relation containing only those common elements?
nr = b/nb
Initial Num. of Runs
bo + bi * (bo/(nB-2))
Num. of Disk Block Accesses
logDm nr= ceil(log(nr )/log(dm))
Num. of Passes
Procedural
Relational Algebra is a ______ query language that takes two relations as input and produces another relation as an output of the query.
1) Sorting Phase 2) Merge Phase
Steps in the sort-merge algorithm
1) Not Optimized Query Tree 2) Move SELECT Operations Down 3) Apply more restrictive SELECT Operations 4) Replace CARTESIAN PRODUCT & SELECT w/ Natural Join 5) Move PROJECT operations down
Steps of Heuristic Optimization
Join
The ____ allows using independent tables linked by common attributes.
Set-Difference
The ___________ operation, denoted by −, allows us to find tuples that are in one relation but are not in another.
Difference
The ____________operator subtracts one table from the other.
Project
The _________operator yields a vertical subset of a table.
Cardinality
The number of tuples produced by each operation (e.g., selection, join).
Select
To be considered minimally relational, the DBMS must support the key relational operators _____, PROJECT, and JOIN.
1) Nested-Loop Join 2) Indexed Nested-Loop Join 3) Sort-Merge Join 4) Hash Join
Types of Join Operations
pFall2009(courseID)( 𝞂 (semester = 'Fall' ⌃ year= '2009' Sections) pSpring2010(courseID)( 𝞂 (semester = 'Spring' ⌃ year= '2010' Sections) pFall2009 ∩ Spring2010
Using relational algebra, find courses that ran in Fall 2009 and in Spring 2010 from the table below
pFall2009(courseID)( 𝞂 (semester = 'Fall' ⌃ year= '2009' Sections) pSpring2010(courseID)( 𝞂 (semester = 'Spring' ⌃ year= '2010' Sections) Fall2009 -- Spring2010
Using relational algebra, find courses that ran in Fall 2009 but not in Spring 2010 from the table below
pFall2009(courseID)( 𝞂 (semester = 'Fall' ⌃ year= '2009' Sections) pSpring2010(courseID)( 𝞂 (semester = 'Spring' ⌃ year= '2010' Sections) pFall2009 ⋃ Spring2010
Using relational algebra, find courses that ran in Fall 2009 or in Spring 2010 from the table below
deptName G AVG salary (Instructors)
Using relational algebra, find the average salary of all instructors in each department from the table below
G AVG salary (σ deptName = 'Physics' (Instructors))
Using relational algebra, find the average salary of instructors in the Physics department from the table below
G AVG salary (Instructors)
Using relational algebra, find the average value of the "salary" fields from the table below
σ deptName = "Physics" (Instructors)
Using relational algebra, select only the instructors who work in the department "Physics" from the table below
σ deptName = 'Physics' ⌃ lastName = 'Dunn' ∨ salary = 90000 (Instructors)
Using relational algebra, select only the instructors who works in the "Physics" AND their last name is "Dunn" OR instructors who make $90000.00" from the table below
σ deptName = 'Physics' ∨ salary = 90000 (Instructors)
Using relational algebra, select only the instructors who works in the "Physics" OR instructors who make $90000.00" from the table below
σ deptName = "Physics" ⌃ salary = 90000.00 (Instructors)
Using relational algebra, select only the instructors who works in the "Physics" with salary equal to $90000.00" from the table below
Selects a subset of columns from a relation
What does the PROJECT operation in relational algebra do?
A database query language
What is Relational Algebra in DBMS?
When a DBMS sees a query of the form SELECT * FROM R WHERE <condition>
What is a full table scan?
When a DBMS sees a query of the form SELECT * FROM person WHERE birthyear=1975!
What is a point query?
When a DBMS sees a query of the form SELECT * FROM person WHERE year(birthdate) BETWEEN 1975 and 1994;
What is a range query?
Merges the sorted runs in one or more merge passes, creating larger sorted sub-files
What is the merging phase in the sort-merge algorithm?
Selection
What is the purpose of the σ (sigma) operator in Relational Algebra?
M * N
What is the result of the Cartesian Product operation between two relations with 'm' and 'n' rows, respectively?
A relation containing attributes from A that are also present in B
What is the result of the division operation in relational algebra when applied to two relations A and B?
A new relation with selected attributes from an existing relation
What is the result of the projection operation in Relational Algebra?
Sorts main-memory sized sub-files called runs using internal sort algorithm and written back to disk as temporary sorted subfiles (runs)
What is the sorting phase in the sort-merge algorithm?
Join
Which Relational Algebra operation is used to combine rows from two relations that have a common attribute?
Equijoins
Which is a join condition that contains an equality operator:
Generalized selection
Which is a unary operation:
Creates a new relation by pairing every tuple from one relation with every tuple from another relation
Which of the following Relational Algebra operations is used to combine two relations into a single relation?
Concatenation
Which of the following is not a basic operation in Relational Algebra?
project
Which of the following is not a valid binary operation in the relational algebra
min
Which of the following is not unary operation in the relational algebra
Sigma (Greek)
Which of the following is used to denote the selection operation in relational algebra?
It finds all the tuples in one relation that are related to all the tuples in another relation
Which of the following statements about the division operation in Relational Algebra is true?
Select
Which operation in relational algebra selects a subset of rows from a relation based on a given condition?
Project
_____ returns only the attributes requested, in the order in which they are requested.
Select
_____, also known as RESTRICT, yields values for all rows found in a table that satisfy a given condition.
Leaf Nodes
_______ are the input relations for the query tree
Internal Nodes
_______ are the relational algebra operations for the query tree
