Database System Concepts
7th Edition
ISBN: 9780078022159
Author: Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher: McGraw-Hill Education
expand_more
expand_more
format_list_bulleted
Question
Chapter 7, Problem 43E
Program Plan Intro
Materialized view:
- Materialized view is a
database object that contains the result of a query. - It is mainly used for increasing application performance.
- It is used for replicating data and to cache expensive queries in a data warehouse environment.
</PROGRAM-PLAN-INNTRO>
Constraints on materialized views enforcing functional dependency:
- The constraints on materialized view helps for increasing execution speed.
- Materialized view is refreshed by the type of materialized view.
- The constraints under materialized view should be normalized.
- That means each dimension should be contained in one table.
- Also, the joins between tables must be normalized or partially normalized.
- Hence, there is a guarantee that each child side row joins with exactly one parent side row.
- Hence, constraints on materialized view can be used for enforcing functional dependency.
Expert Solution & Answer
Want to see the full answer?
Check out a sample textbook solutionStudents have asked these similar questions
So SQL does not suppor functional depency constraints. However, if the database system supports constraints on materialized views and materialized views are maintained immediately, is it possible to enforce the functional dependency constraints in SQL?
If it is possible, for a relation r(A,B,C), how can the constraints on materialized views be used to enforce a functional dependency, like B->A?
Consider a database containing two relations: R(A,B,C) and S(D,E,F). For each of three (your choice) of the 12 rewriting rules for relational algebra expression, give an example pair of relational algebra expressions involving R and S such that the rule rewrites one expression to the other expression, and give another example pair to show the rewriting rule cannot be used since some of the conditions for the rule arenot satisfied
Q7. Consider a database containing two relations: R(A,B,C) and S(D,E,F). For each of three (your choice) of the 12 rewriting rules for relational algebra expression, give an example pair of relational algebra expressions involving R and S such that the rule rewrites one expression to the other expression, and give another example pair to show the rewriting rule cannot be used since some of the conditions for the rule are not satisfied.
Chapter 7 Solutions
Database System Concepts
Ch. 7 - Prob. 1PECh. 7 - Prob. 2PECh. 7 - Explain how functional dependencies can be used to...Ch. 7 - Prob. 4PECh. 7 - Prob. 5PECh. 7 - Prob. 6PECh. 7 - Prob. 7PECh. 7 - Prob. 8PECh. 7 - Prob. 9PECh. 7 - Prob. 10PE
Ch. 7 - Prob. 11PECh. 7 - Prob. 12PECh. 7 - Prob. 13PECh. 7 - Prob. 14PECh. 7 - Prob. 15PECh. 7 - Prob. 16PECh. 7 - Prob. 17PECh. 7 - Prob. 18PECh. 7 - Prob. 19PECh. 7 - Prob. 20PECh. 7 - Prob. 21ECh. 7 - Prob. 22ECh. 7 -
Explain what is meant by repetition of...Ch. 7 -
Why are certain functional dependencies called...Ch. 7 - Prob. 25ECh. 7 - Prob. 26ECh. 7 - Prob. 27ECh. 7 - Prob. 28ECh. 7 - Prob. 29ECh. 7 - Prob. 30ECh. 7 - Prob. 32ECh. 7 - Prob. 33ECh. 7 - Prob. 35ECh. 7 - Prob. 36ECh. 7 - Prob. 37ECh. 7 - Prob. 38ECh. 7 - Prob. 39ECh. 7 - Prob. 40ECh. 7 - Prob. 41ECh. 7 - Prob. 42ECh. 7 - Prob. 43E
Knowledge Booster
Similar questions
- Although SQL does not support functional dependency constraints, if thedatabase system supports constraints on materialized views, and materialized views are maintained immediately, it is possible to enforce functional dependency constraints in SQL. Given a relation r(A, B, C), explain how constraints on materialized views can be used to enforce the functional dependency B → C.arrow_forwardTesting SQL queries: To test if a query specified in English has been correctlywritten in SQL, the SQL query is typically executed on multiple test databases,and a human checks if the SQL query result on each test database matches theintention of the specification in English. When creating test databases, it is important to create tuples with null values for foreign-key attributes, provided the attribute is nullable (SQL allows foreign-key attributes to take on null values, as long as they are not part of the primary key and have not been declared as not null). Explain why, using an example query on the university database.arrow_forwardConsider the relation schema R (A, B, C, D) with all possible functional dependencies. For each of the following situations, identify the highest normal form for this relation R. A, C -> B, D C -> D A, C -> B, D B -> D A, C -> B, Darrow_forward
- Given relations P and Q, both over attributes A and B, write a query in relational algebra under bag semantics that returns P if Q is empty and returns Q if Q is not empty.arrow_forwardFor each part of this problem you will need to construct a single SQL query which will check whether a certain condition holds on a specific instance of a relation, in the following way: your query should return an empty result if and only if the condition holds on the instance. (If the condition doesn't hold, your query should return something non-empty). Note our language here: the conditions that we specify cannot be proved to hold in general without knowing the externally-defined functional dependencies; so what we mean is, check whether they could hold in general for the relation, given a specific set of tuples. You may assume that there will be no NULL values in the tables, and you may assume that the relations are sets rather than multisets, but otherwise your query should work for general in- stances. 1. A is a superkey for a relation T (A, B, C, D). 2. The combinations of two attributes in the relation T (A, B, C, D) are each keys. 3. A tuple-generating dependency (TGD)…arrow_forwardDatabase Systems For each part of this problem you will need to construct a single SQL query which will check whether a certain condition holds on a specific instance of a relation, in the following way: your query should return an empty result if and only if the condition holds on the instance. (If the condition doesn’t hold, your query should return something non-empty). Note our language here: the conditions that we specify cannot be proved to hold in general without knowing the externally-defined functional dependencies; so what we mean is, check whether they could hold in general for the relation, given a specific set of tuples. You may assume that there will be no NULL values in the tables, and you may assume that the relations are sets rather than multisets, but otherwise your query should work for general in- stances. A is a superkey for a relation T (A, B, C, D). The combinations of two attributes in the relation T (A, B, C, D) are each keys. A tuple-generating…arrow_forward
- Relational Database A relation R is given which is R(A,B,C,D,E). It has the following functional dependencies: BC -> E, E -> A and B -> D. Answer the following based on this information. Find all the candidate keys. What type of dependencies are found in this relation? Which normal form does this relation satisfy? Is it in 3NF? If not, decompose it until 3NFarrow_forwardDATABASE SYSTEM A relation R is given which is R(A,B,C,D,E). It has the following functional dependencies: BC -> ADE , D -> B. Answer the following based on this information. Which normal forms does this relation satisfy? Which type of dependencies does this relation violate? Normalize the relationship so that it decomposes to 3NF.arrow_forwardList all nontrivial functional dependencies satisfied by the relation of Figure7.18.arrow_forward
- List all nontrivial functional dependencies satisfied by the relation of Figure 7.18.arrow_forwardGiven the following relation: R(A, B, C, D, E, F, G) The functional dependencies that apply onto R are: AB> G B>E,F G>A A> CD 1. What normal form is the relation in? Why? 2. How would you successively normalize it completely? State the reason behind each decomposition step by step.arrow_forwardConsider the Relation R4 = (A,C,B,D,E,F), with Functional Dependencies: A -> B, C -> D, E -> D. What is the Candidate Key for R4?arrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
- Database System ConceptsComputer ScienceISBN:9780078022159Author:Abraham Silberschatz Professor, Henry F. Korth, S. SudarshanPublisher:McGraw-Hill EducationStarting Out with Python (4th Edition)Computer ScienceISBN:9780134444321Author:Tony GaddisPublisher:PEARSONDigital Fundamentals (11th Edition)Computer ScienceISBN:9780132737968Author:Thomas L. FloydPublisher:PEARSON
- C How to Program (8th Edition)Computer ScienceISBN:9780133976892Author:Paul J. Deitel, Harvey DeitelPublisher:PEARSONDatabase Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781337627900Author:Carlos Coronel, Steven MorrisPublisher:Cengage LearningProgrammable Logic ControllersComputer ScienceISBN:9780073373843Author:Frank D. PetruzellaPublisher:McGraw-Hill Education
Database System Concepts
Computer Science
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:McGraw-Hill Education
Starting Out with Python (4th Edition)
Computer Science
ISBN:9780134444321
Author:Tony Gaddis
Publisher:PEARSON
Digital Fundamentals (11th Edition)
Computer Science
ISBN:9780132737968
Author:Thomas L. Floyd
Publisher:PEARSON
C How to Program (8th Edition)
Computer Science
ISBN:9780133976892
Author:Paul J. Deitel, Harvey Deitel
Publisher:PEARSON
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781337627900
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Programmable Logic Controllers
Computer Science
ISBN:9780073373843
Author:Frank D. Petruzella
Publisher:McGraw-Hill Education