-
***Name the five fundamental operations of relational algebra.
- • Selection
- • Projection
- • Cartesian product
- • Union
- • Difference
- (These perform most of the data retrieval operations)
-
***What is the difference between a unary operation and a
binary operation?
- Unary = operates on single relation
- Binary=operates on two relations
-
***Name four types of join operations in relational algebra.
- • Theta-join
- • Equi-join
- • Natural join
- • Outer join
-
In algebraic terms, a selection operation is expressed by the following notation:
σc(R)
The symbol "σ" represents the selection operation, "c" is the condition to use when selecting rows, and "R" is the ___ being operated on.
relation
-
In theory, a join operation would be used instead of a restricted Cartesian product operation. A ___ is a binary operation that creates a new ___.
-
A ____ (notated as θ-join) is a binary operation that combines two relations where the combination of rows satisfies a predicate. Following is the algebraic notation for a ____. R >< F S The symbol ">< " represents the join operation.
-
In other words, a theta-join can be viewed in terms of a ____ Cartesian product operation.
restricted
-
...two occurrences of the c_id are present. A ___ join would prevent this ____ data. A ___ join is a binary operation that combines two relations over their common attributes, eliminating one occurrence of each common attribute. Following is the algebraic notation for a natural join. R >< SThe "><" symbol represents the join operation. In the notation, "R" is the first relation and"S" is the second relation. The algebraic notation for the expression that will eliminate the occurrence of the common attribute shown in Figure 9-20 is written as follows.
-
On occasion, when joining two relations based on matching values in a join column, you will want rows to appear in the result relation even though no matching values occur in the second relation. Outer join operations make this possible. Several types of outer joins exist. In a left outer join, a binary operation, rows from the first relation that do not have matching values in the shared column of the second relation are included in the result relation. The algebraic notation for this operation is as follows.R X| S The X|symbol (not exact, representative) represents the left outer join operation. In the notation, "R" is the first relation and "S" is the second relation. Suppose a relation is needed containing the customers who have orders, the orderinformation, as well as customers who have no orders. The algebraic notation for this operation is as follows. πc_id,last_name,address,city,state,zip (Customer) Orders
-
An ____ is a binary operation that creates a new relation containing all the rows that are in both the first and the second relations. The relations must be union-compatible to participate in an intersection operation.
Following is the algebraic notation for an intersection operation. R ∩ SThe "∩" symbol represents the ____ operation. In the notation, "R" is the first relation and "S" is the second relation.Using the Employee relations shown in Figure 9-12, an intersection operation against the two employee relations is written in SQL as follows.
SELECT *
FROM Employee1
INTERSECT
(SELECT *
FROM Employee2);
- intersection
- intersection
-
In this type of operation, ___ relations are involved. A ___ product operation multiplies two relations to create a new relation containing every possible pair...
-
The algebraic notation for the Cartesian product operation is as follows. R _ S The "_" symbol represents the Cartesian product operation. In the notation, "R" is the first relation and "S" is the second relation.
- X
- SELECT Employee.*, Customer.*
- FROM Employee, Customer;
-
A union is a binary operation that ____ all ___ into a single ___ while eliminating any duplicates. For the two relations to be involved in a union, they must be union-compatible. This means that they must have the same number of attributes with matching domains.
-
The algebraic notation for a ___ operation is as follows. R ∪ S The "∪" symbol represents the union operation. In the notation, "R" is the first relation and "S" is the second relation.
- union
- The SQL statement that would produce a union of the two relations is as follows.SELECT *
- FROM Employee1
- UNION
- SELECT *
- FROM Employee2;
-
A ____ (or set ____) is a binary operation that creates a relation including the rows that are in the first relation, but not in the second. The relations must be union compatible to participate in a difference operation
-
Following is the algebraic notation for a difference operation. R _ S The "_" symbol represents the difference operation. In the notation, "R" is the first relation and "S" is the second relation. Using the Employee relations shown in Figure 9-12, a difference operation against the two employee relations is written in SQL as follows.
SELECT *
FROM Employee1
EXCEPT
(SELECT e2_id
FROM Employee2);
-
***Briefly explain why intersections and joins are not considered fundamental operations of relational algebra.
Intersections and joins are not considered fundamental operations because it is possible to reproduce the results of an intersection or a join using combinations of the selection, projection, Cartesian product, union, and difference operations.
-
***What is the SQL equivalent of the following relational algebraic expression?σprod_description = 'guitar'(Products)
SELECT * FROM Products WHERE prod_description = 'guitar';
-
***What is the relational algebraic expression for the following SQL statement?
SELECT prod_no, prod_name, prod_price FROM Products WHERE prod_description = 'guitar';
π prod_no, prod_name, prod_price (σprod_description = 'guitar'(Products))
-
***What is the SQL equivalent of the following relational algebraic expression?σproducts.prod_no = distributors.prod_no (Products X Distributors)
- SELECT Products.*, Distributors.*
- FROM Products, Distributors
- WHERE Products.prod_no = Distributors.prod_no;
-
***What is the relational algebraic expression for the following SQL statement?
SELECT prod_name, prod_description
FROM Products
UNION
SELECT prod_name, prod_description
FROM NewProducts;
πprod_name, prod_description(Products) ∪ πprod_name,prod_description(NewProducts)
-
***What is the SQL statement for the following relational algebraic expression?
(πprod_no,prod_name,prod_price (Products))
|> <| Products.prod_no=Orders.prod_no (πprod_no,ord_no (Orders))
- SELECT Products.prod_no, prod_name, prod_price,
- Orders.prod_no, ord_no
- FROM Products, Orders
- WHERE Products.prod_no = Orders.prod_no;
-
**P The ACID property known as isolation refers to which of the following?
a. The transaction is a complete unit. Atomicity.
b. The transaction must change the database from one consistent state toanother consistent state.
c. The transaction is independent from other transactions.
d. The transaction's results are durable.
C
-
**P Which of the following describes the lost update anomaly?
a. A lost update can occur when a transaction is only partially committed.
b. A lost update can occur when simultaneous updates occur to a relation and one update overrides another.
c. A lost update can occur when a database query accesses only partially updated data.
d. A lost update can occur when one transaction is allowed to see the intermediate results of another transaction.
- B
- When simultaneous updates occur to a relation, one update may override another
-
**P Briefly discuss optimistic concurrency control.
- ...data access conflicts will rarely occur, so it is more efficient to permit the transactions to be carried out. Then, when a transaction isready to be committed, a check is performed to determine whether a conflict is present. If a conflict exists, the transaction is then rolled back and restarted. If transaction conflicts are rare, rollbacks should also be rare.
- 3 steps involved with optimistic concurrency control: read, validate (ensures no conflicts, rollback if conflict), write (commit items in DB).
-
***What is the purpose of optimistic concurrency control?
Optimistic concurrency methods are designed to reduce the extra processing required by locking and time stamping techniques.
-
***Define concurrency control.
Concurrency control is the management of transactions that occur simultaneously so that they do not conflict with one another.
-
***What is a transaction?
A transaction is a sequence of actions conducted by a single user against a database that retrieves or alters the data in that database.
-
***Name five security threats to an enterprise.
Security threats to an enterprise include: user impersonation; unauthorized copying of data; corruption or alteration of data access applications; illegal data access; theft, corruption, or denial of access by hackers; wire tapping; trapdoors; failure of security measures; insufficient personnel training; and personnel viewing or disclosing sensitive data.
|
|
