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.

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.

Database Systems: Design, Implementation, & Management

12th Edition

ISBN:9781305627482

Author:Carlos Coronel, Steven Morris

Publisher:Carlos Coronel, Steven Morris

Chapter8: Advanced Sql

Section: Chapter Questions

Problem 18RQ

See similar textbooks

Similar questions

Given the database schema R(A, B, C), and a relation r on the schema R, write an SQL query to test whether the functional dependency B → C holds on relation r. Also write an SQL assertion that enforces the functional dependency. Assume that no null values are present. (Although part of the SQL standard, such assertions are not supported by any database implementation currently.)
In SQL, foreign key constraints can reference only the primary key attributes ofthe referenced relation or other attributes declared to be a superkey using theunique constraint. As a result, total participation constraints on a many-to-manyrelationship set (or on the “one” side of a one-to-many relationship set) cannotbe enforced on the relations created from the relationship set, using primarykey, foreign key, and not null constraints on the relations. Explain why
In SQL, foreign key constraints can reference only the primary key attributes ofthe referenced relation or other attributes declared to be a superkey using theunique constraint. As a result, total participation constraints on a many-to-manyrelationship set (or on the “one” side of a one-to-many relationship set) cannotbe enforced on the relations created from the relationship set, using primarykey, foreign key, and not null constraints on the relations. Explain how to enforce total participation constraints using complexcheck constraints or assertions (see Section 4.4.8). (Unfortunately, thesefeatures are not supported on any widely used database currently.)
Write an SQL Assertion statement to enforce the functional dependency Title → Salary for the relation EMPLOYEE(EmpID, Name, DeptID, Title, Salary). You may assume that no tuple has null values. Discuss any potential issue with this table design.
Consider the following SQL query on the university schema: select avg(salary)-(sum(salary) / count(*)) from instructorWe might expect that the result of this query is zero since the average of a set of numbers is defined to be the sum of the numbers divided by the number of numbers. Indeed this is true for the example instructor relation in Figure 2.1. However, there are other possible instances of that relation for which the result would not be zero. Give one such instance, and explain why the result would not be zero.
Given the following relational schema in the image attached. For the following query, prepare the initial (canonical) query tree, then show how the query tree is optimized by the use of heuristic optimization. (Provide all the relational algebra expressions for the query trees). ‘For every project located in ‘Stanford’, list the project number, the controlling department number, and the department manager’s lastname, address and birthdate’
Consider the E-R diagram in Figure 8.7 showing entity set instructor. Give an SQL schema definition corresponding to the E-R diagram, treating phone number as an array of 10 elements, using Oracle or PostgreSQL syntax.
If there are more than one possible candidate keys, provide some instances of relations that have more than one.
Give an SQL schema definition for the employee database of Figure 3.19.Choose an appropriate domain for each attribute and an appropriate primarykey for each relation schema. Include any foreign-key constraints that might beappropriate.
Consider the bank database of Figure 16.9, where the primary keys are underlined. Construct the following SQL queries for this relational database. Write a nested query on the relation account to find, for each branch with name starting with B, all accounts with the maximum balance at the branch.
Consider a many-to-one relationship R between entity sets A and B. Supposethe relation created from R is combined with the relation created from A. InSQL, attributes participating in a foreign key constraint can be null. Explainhow a constraint on total participation of A in R can be enforced using not nullconstraints in SQL.
For a relational schema R(ABCDEFG), Following dependencies are given to you i)Find out the prime and non-prime attributes.ii) Find out the highest normalization form of R