Suppose we have three relations r(A, B), s(B, C), and t(B, D), with all attributes declared as not null. Consider the expressions • r natural left outer join (s natural left outer join t), and • ( natural left outer join s) natural left outer join t a. Give instances of relations r, s and t such that in the result of the second expression, attribute C has a null value but attribute D has a non-null value. b. Is the above pattern, with C null and D not null possible in the result of the first expression? Explain why or why not.

Suppose we have three relations r(A, B), s(B, C), and t(B, D), with all attributes declared as not null. Consider the expressions • r natural left outer join (s natural left outer join t), and • ( natural left outer join s) natural left outer join t a. Give instances of relations r, s and t such that in the result of the second expression, attribute C has a null value but attribute D has a non-null value. b. Is the above pattern, with C null and D not null possible in the result of the first expression? Explain why or why not.

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Similar questions

Suppose we have three relations r(A, B), s(B, C), and t(B, D), with all attributesdeclared as not null. Give instances of relations r, s, and t such that in the result of(r natural left outer join s) natural left outer join tattribute C has a null value but attribute D has a non-null value.
Consider the join R ▷◁ S where the join predicate is R.a = S.b, given the following metadata about R and S: Relation R contains 2,000 tuples and has 10 tuples per block Relation S contains 10,000 tuples and has 10 tuples per block Attribute a of relation R is the primary key for R, and every tuple in R matches 5 tuples in S There exists a primary index on S.b with height 3 There exists a secondary index on R.a with height 2 The buffer can hold 5 blocks For this problem, we use a simplified cost model where cost = number of I/O in terms of pages (i.e., the number of pages read/written). If R ▷◁R.a=S.b S is evaluated with a block nested loop join, which relation should be the outer relation? Justify your answer. What is the cost of the join in number of I/O’s?
Consider the join R ▷◁ S where the join predicate is R.a = S.b, given the following metadata about R and S: Relation R contains 2,000 tuples and has 10 tuples per block Relation S contains 10,000 tuples and has 10 tuples per block Attribute a of relation R is the primary key for R, and every tuple in R matches 5 tuples in S There exists a primary index on S.b with height 3 There exists a secondary index on R.a with height 2 The buffer can hold 5 blocks What is the cost of a plan that evaluates this query using sort-merge join. Show the details of your cost analysis. Please show detailed explaination.
Suppose R is a relation with attributes A1, A2, ..., An. As a function of n, tell how many superkeys R has, if The only keys are A1 and A2?
It is possible to construct a connection such as sec course from a group of relationships that are many to one. Does the relation's many-to-one cardinality restriction get enforced by the main and foreign key constraints? Why do you think that is?
Suppose RR is a relation with attributes A1,A2,…,AnA1,A2,…,An. How many superkeys will RR have if a) The only candidate key is A1A1. b) The only candidate keys are A1A2A1A2 and A1A3A1A3.
Consider a relation r(A, B, C), with an index on attribute A. Give an example of a query that can be answered by using the index only, without looking at the tuples in the relation. (Query plans that use only the index, without accessing the actual relation, are called index-only plans.)
Consider a relation with schema R(A,B,C,D,E,G) and functional dependencies (FDs) C→D; B→A; D→E; E→G ) What is the closure of {B,C}? Show steps of your solution. Find 2 nontrivial FDs that can be inferred from the given FDs set. Explain how you found them. Propose one key for a given schema and FDs. Explain how you found it.
Consider a relation R(A,B,C,D,E) with functional dependencies AB→C, A→D, and D→E. (a) What are the keys for R?(b)What is the closure A, that is A+ ? What is the closure AB, that is (AC)+ ?
Given a set A = {1, 2, 3, 4}, which of the following relations is a partial order relation andwhich is not? Justify your answer(a) { (1, 2), (2, 3), (1, 3), (4, 3) }(b) { (1, 2), (2, 3), (1, 3), (3, 4), (1, 1), (2, 2), (3, 3), (4, 4) }(c) { (1, 2), (2, 3), (1, 3), (4, 3), (1, 1), (2, 2), (3, 3), (4, 4) }
We studied different properties of relations like reflexive, symmetric, antisymmetric, transitive. Please provide one example relation each for each property of the relation. Try to come up with original examples which can be seen practically. (Example, Reflexive relation R = {(a, b) | a and b share same birthday}, Symmetric relation R = {(a, b) | a and b are friends}, Antisymmetric relation R = {(a, b) | a is instructor of b}, Transitive relation R = {(a, c) | a is sibling of b, b is sibling of c, then a is sibling of c}
Consider a relation schema R(A, B, C, D, E, G) and a set of functional dependencies F = {A → C, AD → CE, B → ACD, C → B}. (a) Show the steps of computing a canonical cover for F. (b) Compute the closure of AG and then determine whether or not AG is a candidate key. (c) Determine whether or not (A, E, G) is in BCNF and justify your answer using the transitive closure of a set of attributes. If (A, E, G) is not in BCNF, find a BCNF decomposition of it. (d) Assume that (A, E, G) is decomposed into (A, G) and (E, G). Given the above functional dependencies, is this decomposition always lossless? If so, prove this. Otherwise, explain it using an example (i.e., a case of decomposing a table containg rows and columns into two tables). (e) Assume that R is decomposed into R1(A, B, C, D) and R2(A, E, G). Is this decomposition dependency preserving? Justify your answer.