CSC 675 Review 4 - Relational Algebra and SQL

Question 1

What is the purpose of the relational algebra select operation?

Answer 1

Restriction - The SELECT operation is used to choose a subset of the tuples from a relation that satisfies a selection condition.

Question 2

What is the purpose of the relation algebra project operation?

Answer 2

Selects certain columns from the table and discards the other columns.

Question 3

What is the purpose of the relational algebra join operation?

Answer 3

Is used to combine related tuples from two relations into single “longer” tuples.

Question 4

What is the purpose of the relation algebra union operation?

Answer 4

A collection of tuples is the set of all distinct elements.

Question 5

What is the purpose of the relational algebra difference operation?

Answer 5

Produces a relation that includes all the tuples in R1 that are not in R2; R1 and R2 must be union compatible.

Question 6

What is the purpose of the relation algebra intersection operation?

Answer 6

Produces a relation that includes all the tuples in both R1 and R2; R1 and R2 must be union compatible.

Question 7

What is the purpose of the relational algebra division operation?

Answer 7

Division identifies the attribute values from a relation that are found to be paired with all of the values from another relation.

Question 8

What is the purpose of the relational algebra cartesian product operation?

Answer 8

All ordered pairs of tuples from the two relations.

Question 9

Explain how join can be expressed using the other relational operation.

Answer 9

a join can be expressed as multiple selects

Question 10

Explain how division can be expressed using the relational operation.

Answer 10

division can be expressed as multiple selects

Question 11

What is union compatibility? *

Answer 11

• Relations have the same number of
attributes
• Corresponding attributes have the same domains

Question 12

Why do the union, intersection and difference operations require that the relations on which they are applied be union compatible? *

Answer 12

Union, intersection, and difference require union compatibility because these operation require direct comparison of the individual tuples.

Question 13

what is the standard definition of a natural join?

Answer 13

Produces all the combinations of tuples from R1 and R2 that satisfy a join condition with only equality comparisons. Without the duplicate columns.

Question 14

What is the standard definition of an equijoin?

Answer 14

Produces all the combinations of tuples from R1 and R2 that satisfy a join condition with only equality comparisons.

Question 15

Under what circumstances are outer joins useful? *

Answer 15

We wan to use outer joins when we want to know all the information from a join. When you do an outer join all the tuples not contained within the join are completed with null values. This is the reason why outer joins are not considered part of the relational algebra. In “pure” relation algebra, there are no null values.

Question 16

Why are they sometimes not considered to be part of the relational algebra?

Answer 16

?

Question 17

What is a query tree? *

Answer 17

Binary tree representation of fully parenthesized relational algebra expression
• relations correspond to leaves!
• relational operators correspond to internal
nodes
• unary operators have one child
• binary operators have two children
• Order of evaluation corresponds to a postorder traversal of the query tree, however most systems use iterators and pipelining whenever possible

Question 18

How is a query tree different from a relational algebra expression? *

Answer 18

It’s a visualization of the query.

Question 19

How are query trees used in relational query optimization and processing?

Answer 19

To visualize the query and rearrange it to be more efficeint. slide 224 ?

Question 20

You should be able to write relational algebra expressions to answer simple SPJ queries about a given collection of data.

Answer 20

?

Question 21

How do the relations (tables) in SQL differ from the relations defined formally for the relational algebra?

Answer 21

?

Question 22

Why does SQL allow duplicate tuples in a table or in a query result?

Answer 22

tables in SQL are not sets–duplicate tuples are allowed unless specifically eliminated from the result (select distinct attlist) or disallowed by a declared primary key (this does not apply to the result of queries).!

Question 23

How are primary key and foreign key constrains expressed in SQL?

Answer 23

PRIMARY KEY (P_Id)

P_Id int FOREIGN KEY REFERENCES Persons(P_Id)

Question 24

What is a view in SQL?

Answer 24

A view is a virtual relation defined as the result of any legal SQL query. The view behaves as if it is not materialized until a query against it is executed, and consequently is always up to date with respect to the underlying data values in the database

Question 25

How is it defined?

Answer 25

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

Question 26

Why is it sometimes impossible to update a view? *

Answer 26

It is sometimes not possible to update a view when the view is over more than one table. It is never possible to update a view that has aggregates in it.

Question 27

Under what circumstances is it possible?

Answer 27

It is possible to update a view when the view is over only one table and their are not aggregates used in it.

Question 28

What is a cursor?

Answer 28

is a control structure that enables traversal over the records in a database.

Question 29

How are cursors used in embedded SQL?

Answer 29

when open command executes. All tuples are materialized and cursor placed before first tuple. Each call to fetch advances cursor to next tuple and returns those values. close releases all resources.

Question 30

You should be able to write SQL programs to answer simple SPJ queries about a given collection of data.

Answer 30

?

Question 31

You should also be able to give an equivalent relational algebra expression for SQL SPJ queries.

Answer 31

?