Consider the relational schemas given below and the respective sets of functional dependencies valid in the schemas. R(A,B, C, D, E, H) Functional Dependency: A → B, BC → D,E → C,D → A Find all the minimal super key of the relational table R. List the derivations of all minimal keys. (ii) Identify the highest normal form of the relational R. Remember that the identification of a normal form requires analysis of the valid functional dependencies. (i) Decompose the relational table R into minimal number of normalized jii) relational tables in BCNF. Remember to indicate the primary key and foreign keys (if any).

Consider the relational schemas given below and the respective sets of functional dependencies valid in the schemas. R(A,B, C, D, E, H) Functional Dependency: A → B, BC → D,E → C,D → A Find all the minimal super key of the relational table R. List the derivations of all minimal keys. (ii) Identify the highest normal form of the relational R. Remember that the identification of a normal form requires analysis of the valid functional dependencies. (i) Decompose the relational table R into minimal number of normalized jii) relational tables in BCNF. Remember to indicate the primary key and foreign keys (if any).

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.)
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.
Let R and S be two relations with the following schema: R (P. Q. R1, R2, R3) S (P. Q S1, S2) Where (P, Q] is the key for both schemas. Which of the following queries are equivalent? Ilp (R S) lp (R) Ilp (S) Ilp (lpo (R) N lp,g (S)) Пр (Про (R) - (Пре (R) Про (S))) I. II. III. IV.
Convert this Table into BCNF . pls show the result of each step in the normalization process. Books( book_id, title, publisher_id, ISBN, edition, price, num_page, publication_date) Functional Dependencybook_id -->title, publisher_idISBN -->title , edition, price, num_page, publication_dateedition -->publication_date
Consider the following relation: r (A1, A2, A3). For all the states of the relation, the result of Query #1 is always the same as that of Query #2. Specify the primary key of the relation r (30 points); Explain why it must be the primary key.Query #1: sr1.A2=r2.A2 Ù r1.A1=r2.A1 (rr1 (r) ´ rr2 (r))Query #2: select *from r as r1, r as r2where r1.A1=r2.A1
Let R and S be two relations with the following schemas R (P.Q.R₁,R2,R3) and S (P.Q,S1,S₂), where {P, Q} is the primary key for both schemas. Which of the following relational algebra expressions are equivalent? I. 11, (RS) II. 11, (R) 11, (S) III. (11, (R), (S)) P.Q IV. II, (1, (R)-(11 (R)-11, (S))) P/Q I, II, and III I and III IV only I and II I, III, and IV
Suppose you have a Relation that has an atomic Primary Key and noRepeating Groups. All non-key columns are functionally dependent on the Primary Key, but it has a Transitive Functional Dependency. How would you resolve this Transitive Functional Dependency? you would create a 1:M relationship between the initial table and a new table and place the columns that are Transitively Functionally Dependent on the many side. You would move the columns that are Transitively Functionally Dependent to a parent table. The column they were Transitively Functionally Dependent on serves as the Foreign Key to the parent table Transitive Functional Dependencies can only exist with a Composite Primary Key and the, therefore, can;t exist in the table as it has an atomic Primary Key. You would move the column that are Transitively FUnctionally Dependent to a child table. The column they were Transitively Functionally Dependent on serves as the Primary Key to the child Table
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’
Explain the concept of transitive dependency in the context of the Second Normal Form (2NF) and how to resolve it.
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.
For developing our queries, we will be using the following relational schema: Student(SID, name, username, major) Course(cname, description, department, weight) Offering(OID, cname, year, term, duration) Enrolled(SID, OID, grade) A course relation is the generic description of a course which is offered by a department. The offering relation is the specific instance of a course being taught in a given term, and is the entity which will be associated with the student enrollment. Students are enrolled in these offerings and achieve a mark. Express each of following queries in Relational Algebra: | Write a relational algebra query which will list the cname and description of all the courses which are being offering in the Fall 2018 term. 2 Write a relational algebra query which will list the name of all the students who have earned 60% in both 'COSC2P12' and 'COSC2P13'.
Consider the relational schemas given below and the respective sets of functional dependencies valid in the schemas. R(A, B, C, D, E, H) Functional Dependency: AB, BCD,E-C,D →A (i) Find all the minimal super key of the relational table R. List the derivations of all minimal keys. (ii) Identify the highest normal form of the relational R. Remember that the identification of a normal form requires analysis of the valid functional dependencies. Decompose the relational table R into minimal number of normalized relational tables in BCNF. Remember to indicate the primary key and foreign keys (if any).

SEE MORE QUESTIONS

Recommended textbooks for you

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781305627482

Author:

Carlos Coronel, Steven Morris

Publisher:

Cengage Learning

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781285196145

Author:

Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:

Cengage Learning

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781305627482

Author:

Carlos Coronel, Steven Morris

Publisher:

Cengage Learning

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781285196145

Author:

Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:

Cengage Learning

SEE MORE TEXTBOOKS