Transform the ER model below into a set of 3NF relations. Use the format shown here for relations: FACULTY (FacID, LastName, FirstName, Rank, DeptID) FK DeptID → DEPT DEPT (DeptID, DeptName, DeptPhone) You must underline all components of each primary key. Denote each foreign key directly beneath the relation that contains the foreign key, as shown above. Do not use the graphical notation from the textbook. The notation shown above is the notation you will be required to use on this exam. Be sure that all relations are in 3NF (if you follow the transformation rules taught in class, they will be). Supervises POLICY HOLDER AGENT PolicyNumber PolicyType HolderName(First, Last) PURCHASE Agent_ld ODate Name(First, Last) Time Salary MaritalStatus INSURANCE Insurance_ld POLICY DEPENDENT PolicyLength OFFICE Name(Flrst, Last) Premium Office_ld Dateof Birth PayoutAmount Location [Age] Insurance Type {Phone(CC,AC,LC} Email InsuranceType= "L" "C" LIFE CAR Term Deductible

Transform the ER model below into a set of 3NF relations. Use the format shown here for relations: FACULTY (FacID, LastName, FirstName, Rank, DeptID) FK DeptID → DEPT DEPT (DeptID, DeptName, DeptPhone) You must underline all components of each primary key. Denote each foreign key directly beneath the relation that contains the foreign key, as shown above. Do not use the graphical notation from the textbook. The notation shown above is the notation you will be required to use on this exam. Be sure that all relations are in 3NF (if you follow the transformation rules taught in class, they will be). Supervises POLICY HOLDER AGENT PolicyNumber PolicyType HolderName(First, Last) PURCHASE Agent_ld ODate Name(First, Last) Time Salary MaritalStatus INSURANCE Insurance_ld POLICY DEPENDENT PolicyLength OFFICE Name(Flrst, Last) Premium Office_ld Dateof Birth PayoutAmount Location [Age] Insurance Type {Phone(CC,AC,LC} Email InsuranceType= "L" "C" LIFE CAR Term Deductible

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

Transform the given ER model into a set of 3NF relations. Use the format shown below for relations. FACULTY (FacID, LastName, FirstName, Rank, DeptID) FK DeptID à DEPT DEPT (DeptID, DeptName, DeptPhone) You must underline all components of each primary key. Denote each foreign key directly beneath the relation that contains the foreign key, as shown above. Do not use the graphical notation from the textbook. The notation shown here is the notation you will be required to use on exams. Be sure that all relations are in 3NF.
Consider the relation r(A, B, C), where the index is the attribute A. Give an example of a query that can only be answered by looking at the index rather than the relationship's tuples. (Index-only plans are query plans that rely just on the index rather than the actual relation.)
Convert the ER diagram to relations in the normal manner to capture as many constraints as possible, and then identify which of the answers is a relation schema. A. Dependents(ID, name, bDate) B. Employees(ID, name, street, city, state, phones, sDate, lengthEmp) C. Coverage(ID, name) D. Employees(ID, name, street, city, state, sDate, lengthEmp)
Put into third Normal form where A,B, and C are prime attributes. Show the resulting 2NF and 3NF relations. Show work in relational notation R (A, B, C, D, E, F, G, H, I, J) FD1 B —> D, J FD2 (A, B, C) —> E FD3 F —> G, H FD4 C—> I FD5 A —> F
Consider the relation r(A, B, C) with the attribute A as the index. Give an example of a query that can be answered only by looking at the index, rather than the tuples in the relationship. (Index-only plans are query plans that just utilise the index and do not access the actual relation.)
Discuss the differences between the candidate keys and the primary key of a relation. Explain what is meant by a foreign key. How do foreign keys of relations relate to candidate keys? Give examples to illustrate your answer.
Consider the following relation: Invoice(Order_ID, Order_Date, Customer_ID, Customer_Name,Customer_Address,Product_ID, Product_Desc, Price, Order_Quantity) FD1 - Order_ID -> Order_Date, Customer_ID, Customer_Name, Customer_Address FD2 - Customer_ID -> Customer_Name, Customer_Address FD3 - Product_ID -> Product_Description, Price FD4 - Order_ID, Product_ID -> Order_Quantity What are the candidate keys of this relation? Give reasons to explain if this is in 1NF or not. If not, decompose it to 1NF. Give reasons to explain if this is in 2NF or not. If not, decompose it to 2NF. Give reasons to explain if this is in 3NF or not. If not, decompose it to 3NF.
note:please explain better Question 2 2.1) Define Transitive Dependency. Give a good Example. 2.3) Consider a relation T with seven attributes ABCDEFG where AB is a composite primary key for T. Assume T has the following functional dependencies: AB -> CDEFG A -> CEF F -> E In what normal form is relation T? Explain why? Is it possible to decompose T into a number of relations in order to achieve a higher normal form? Explain how and show tables.
Describe the meaning of a Functional Dependency between attributes in a single relation A1 → A2. Use an example such as the Employee relation’s SSN (Social Security Number) and the remaining attributes in the relation. Do not submit SSN as an answer. Provide an example of how functional dependencies are not symmetric i.e. if A1 → A2 it is not necessarily true that A2 → A1.
Use normalization to decompose a relation with anomalies into well-structured relations?
Question 6. Consider the following relation: Invoice(Order_ID, Order_Date, Customer_ID, Customer_Name,Customer_Address,Product_ID, Product_Desc, Price, Order_Quantity) FD1 - Order_ID -> Order_Date, Customer_ID, Customer_Name, Customer_Address FD2 - Customer_ID -> Customer_Name, Customer_Address FD3 - Product_ID -> Product_Description, Price FD4 - Order_ID, Product_ID -> Order_Quantity What are the candidate keys of this relation? Give reasons to explain if this is in 1NF or not. If not, decompose it to 1NF. Give reasons to explain if this is in 2NF or not. If not, decompose it to 2NF. Give reasons to explain if this is in 3NF or not. If not, decompose it to 3NF.
Consider the relation r(A, B, C), where the characteristic A serves as the index to the relationship. Give an example of a question that can only be answered by looking at the index, rather than the tuples in the relationship, and why this is so. (Index-only plans are query plans that solely make use of the index and do not touch the underlying relational database.)