Consider a database with a single table R(q id,txns, total, failed), where q id is the...

  

Transcribed Image Text:

Consider a database with a single table R(q id,txns, total, failed), where q id is the primary key, and all attributes are the same fixed width. Suppose R has 20,000 tuples that fit into 100 pages, Ignore any additional storage overhead for the table (e.g., page headers, tuple headers). Additionally, you should make the following assumptions: The DBMS does not have any additional meta-data (e.g., sort order, zone maps). R does not have any indexes (including for primary key q id) • None of R's pages are already in the buffer pool. A) Suppose the DBMS uses the decomposition storage model (DSM) with implicit offsets What is the minimum number of pages that the DBMS will potentially have to read from disk to answer this query? B.) What is the maximum number of pages that the DBMS will potentially have to read from disk to answer this query? Question Two Explain why a file can have only one clustered index. Question Three Given a tuple-oriented page X where tuples are organized and stored on the page using the strawman approach discussed in class. What happens if we have a variable length storage on a tuple? What happens if we delete a tuple? Scoring system Exercise Points Max points 20 20 20 40 100 la 1.b 12 3 Total Functional Dependencies and Normalization Exercise Points 25 25 25 25 Total 100 1 2 3 4 Exercise 1 Consider the following relation: Stock(#prod, #dep, pname, quantity) Stock describes the name and quantity of products stocked in a particular department. • Determine the functional dependencies on Stock. . For each of them, explain what they mean in plain English. Is Stock 3NF? . • Is Stock BCNF? Exercise 2 Consider the following relation: Plane(#plane, type, manufacturer, capacity, owner) Plane describes the plane id and its type, constructor, capacity and owner. For example, b747 is a 4-seat large airliner, manufactured by Boeing, owned by American Airline. • Determine the functional dependencies on Plane. • For each of them, explain what they mean in plain English. ● • Is Plane 3NF? Is Plane BCNF? Exercise 2 Consider the following relation: Plane(#plane, type, manufacturer, capacity, owner) Plane describes the plane id and its type, constructor, capacity and owner. For example, b747 is a 4-seat large airliner, manufactured by Boeing, owned by American Airline. • Determine the functional dependencies on Plane. • For each of them, explain what they mean in plain English. • Is Plane 3NF? Is Plane BCNF? • Exercise 3 1. Compute the attribute closure of XZA with respect to the following set of dependencies: F = {XZ => ZYB, YA -> GC, C -> W, B -> G, XZ -> G} 2. Is the dependency XZA -> YB implied by F? Hint: Use the Armstrong axioms. Exercise 4 1. Provide the pseudo-code of the BCNF decomposition algorithm. 2. What are the properties of the BCNF decomposition algorithm? Explain lossless and dependency preservation with your own words. 3. Apply the decomposition algorithm on Stock. Consider a database with a single table R(q id,txns, total, failed), where q id is the primary key, and all attributes are the same fixed width. Suppose R has 20,000 tuples that fit into 100 pages, Ignore any additional storage overhead for the table (e.g., page headers, tuple headers). Additionally, you should make the following assumptions: The DBMS does not have any additional meta-data (e.g., sort order, zone maps). R does not have any indexes (including for primary key q id) • None of R's pages are already in the buffer pool. A) Suppose the DBMS uses the decomposition storage model (DSM) with implicit offsets What is the minimum number of pages that the DBMS will potentially have to read from disk to answer this query? B.) What is the maximum number of pages that the DBMS will potentially have to read from disk to answer this query? Question Two Explain why a file can have only one clustered index. Question Three Given a tuple-oriented page X where tuples are organized and stored on the page using the strawman approach discussed in class. What happens if we have a variable length storage on a tuple? What happens if we delete a tuple? Scoring system Exercise Points Max points 20 20 20 40 100 la 1.b 12 3 Total Functional Dependencies and Normalization Exercise Points 25 25 25 25 Total 100 1 2 3 4 Exercise 1 Consider the following relation: Stock(#prod, #dep, pname, quantity) Stock describes the name and quantity of products stocked in a particular department. • Determine the functional dependencies on Stock. . For each of them, explain what they mean in plain English. Is Stock 3NF? . • Is Stock BCNF? Exercise 2 Consider the following relation: Plane(#plane, type, manufacturer, capacity, owner) Plane describes the plane id and its type, constructor, capacity and owner. For example, b747 is a 4-seat large airliner, manufactured by Boeing, owned by American Airline. • Determine the functional dependencies on Plane. • For each of them, explain what they mean in plain English. ● • Is Plane 3NF? Is Plane BCNF? Exercise 2 Consider the following relation: Plane(#plane, type, manufacturer, capacity, owner) Plane describes the plane id and its type, constructor, capacity and owner. For example, b747 is a 4-seat large airliner, manufactured by Boeing, owned by American Airline. • Determine the functional dependencies on Plane. • For each of them, explain what they mean in plain English. • Is Plane 3NF? Is Plane BCNF? • Exercise 3 1. Compute the attribute closure of XZA with respect to the following set of dependencies: F = {XZ => ZYB, YA -> GC, C -> W, B -> G, XZ -> G} 2. Is the dependency XZA -> YB implied by F? Hint: Use the Armstrong axioms. Exercise 4 1. Provide the pseudo-code of the BCNF decomposition algorithm. 2. What are the properties of the BCNF decomposition algorithm? Explain lossless and dependency preservation with your own words. 3. Apply the decomposition algorithm on Stock.

Answer rating: 100% (QA)

Solution Lets tackle each part of your questions step by step Question One A Minimum Number of Pages to Read In the decomposition storage model DSM with implicit offsets the DBMS needs to read only th... View the full answer