Given the relational schema PATIENTS = (PatientName, Doctor, DoctorSpecialty, PatientGender, insurance) where all the attributes are atomic, (PatientName, Doctor, PatientGender) is the only candidate key of PATIENTS, and Doctor DoctorSpecialty. Answer the two following questions (a) and (b): a) Explain why PATIENTS does not satisfy the third normal form (3NF) with respect to the set of functional dependencies that hold on PATIENTS. Show your work. b) Use the decomposition algorithm discussed in class to decompose PATIENTS into a set of schemas such that each resulting schema is in 3NF; show your work. For each resulting schema, give its candidate keys and its complete set of non-trivial functional dependencies. You must explain in detail why EACH of your resulting

Given the relational schema PATIENTS = (PatientName, Doctor, DoctorSpecialty, PatientGender, insurance) where all the attributes are atomic, (PatientName, Doctor, PatientGender) is the only candidate key of PATIENTS, and Doctor DoctorSpecialty. Answer the two following questions (a) and (b): a) Explain why PATIENTS does not satisfy the third normal form (3NF) with respect to the set of functional dependencies that hold on PATIENTS. Show your work. b) Use the decomposition algorithm discussed in class to decompose PATIENTS into a set of schemas such that each resulting schema is in 3NF; show your work. For each resulting schema, give its candidate keys and its complete set of non-trivial functional dependencies. You must explain in detail why EACH of your resulting

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

Where do entity supertype stored at.
In the arc implementation of the super- and sub-type entities, which one of the following is false?A) The primary key of each table for each category of object will be its own UID, if one exists.B) The primary key in the table for the super type will be its UID, if one exists.There is a duplicate primary key in the super-type table for each sub-type table, as well as a duplicate primary key in the super-table for each sub-table
Note: Please make sure that all the points/rules must be implemented.Draw the ERD Diagram to create relational model corresponding to the described Scenario. The loan office in a bank receives from various parties, requests to investigate the credit status of aCustomers. Each credit request is identified by a Request ID and is described by a Request Date andRequesting party Name. The loan office also received results of credit checks. A credit check is identifiedBy a credit check ID and is described by the credit check Date and the credit rating. The loan officeMatches credit requests with credit check results. A credit request may be recorded before its resultArrives; a particular credit result may be used in support of several credit requests. Draw ERD for thisSituation. Requirements:ER Diagram of system implemented in tool such as Sqlyog & Draw.io (Available online).
The text states that you can argue that “the only reason for having relations is to store instances of functional dependencies.” Explain, in your own words, what this means
For each of the following pairs of expressions, give instances of relations thatshow the expressions are not equivalent. ΠA(r − s) and ΠA(r) − ΠA(s).
A phone company has set up the following database in order to be able to send detailed bills to its customers: CUSTOMER(Phone#,FirstName, LastName, City), CALL(CallerPhone#, CalledPhone#, Date, Time, Duration, Cost), The keys of the relations are underlined. Answer must be written in relational algebra On which days did all the customers make at least one call? Is there a way to write this in relational algebra without using count? Subject: MYSQL
If we have a set of attributes as R : (A, B, C, D, E, F) and functional dependencies are: AB -> C B -> AE C -> D What are the candidate key(s)?
Given the reation R (A, B, C, D , E), with functional dependencies: A, B -> C D, E -> C B -> D Could you explain why this is not functional dependency preserving? Also could you work out the BCNF for this?
You are given a relation R with the following attributes R(A,B,C,D,E). Assume that you were given the following functional dependencies: • FD1= AB -> E • FD2: = D -> C (a) What is a valid key for this relation? justify your response. (b) Is this relation in 3NF (Third Normal Form)? Justify your response. If it is not in 3NF, normalize the relation and demonstrate all steps of normalization.
Testing 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.
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.
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?