Consider two relations Order and Product with the schema OrderDid, Odate, Pid, qty) and Product (Pid, manufacturer_id, unit_price). rke Answer the following: i) Can the Oid attribute in the relation Order be a candidate key of the relation? Justify your answer. ii) Can the Pid attribute in the relation Product be a candidate key of the relation? Justify your answer. ii) Find the candidate key for the relation Product. iv) Using the primary keys identified for the above relations write a SQL query that retrieves all tuples of the form (Oid, Odate,Pid,unit_price).

Consider two relations Order and Product with the schema OrderDid, Odate, Pid, qty) and Product (Pid, manufacturer_id, unit_price). rke Answer the following: i) Can the Oid attribute in the relation Order be a candidate key of the relation? Justify your answer. ii) Can the Pid attribute in the relation Product be a candidate key of the relation? Justify your answer. ii) Find the candidate key for the relation Product. iv) Using the primary keys identified for the above relations write a SQL query that retrieves all tuples of the form (Oid, Odate,Pid,unit_price).

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

Consider two relations Order and Product with the schema Order (Oid, Odate, Pid, qty) and Product (Pid, manufacturer_id, unit_price). i) Can the Oid attribute in the relation Order be a candidate key of the relation? Justify your answer. ii) Can the Pid attribute in the relation Product be a candidate key of the relation? Justify your answer. iii) Find the candidate key for the relation Product. iv) Using the primary keys identified for the above relations write a SQL query that retrieves all tuples of the form (Oid, Odate, Pid, unit_price).
Consider the relation schema Appointment={Patient, GP, Date, Time, Clinic, Room} and the followingset Σ of FDs:• Patient, Clinic, Date → Time• Patient → GP• GP → Clinic• Clinic, Date, Time, Room → Patient• Patient, Date, Time → Clinic, RoomIs the above relation schema Appointment in BCNF? If not, identify a BCNF decomposition for Appointment. You need to include the main steps used for identifying the BCNF decomposition. Check ifthis BCNF decomposition is dependency preserving
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.)
Consider the unnormalized relation R with six attributes ABCDEF and the following functional dependencies:AB ->CDEB -> FC -> Da) What is the key(s) for the relation?b) What is the normal form of this relation? Explain it.c) Decompose R into 3NF relations step by step if it is not in 3NF.
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)
If possible provide answer using dummy values. Person (PID, first_name, last_name, age, gender) School (school_ID, school_name, type, city, state) Education (PID, school_ID, degree, major, year) Company (company_ID, company_name, total_asset, headquarter) Branch (company_ID, branch_num, city, state) WorksAt (PID, company_ID, branch_num, start, end) Connection (PID, friend_ID) For each relation, the attribute(s) of the primary key is(are) underlined. In addition the following foreign key constraints hold: In Education, PID is a foreign key that references Person(PID), and school_ID is a foreign key that references School(school_ID) In Branch, company_ID is a foreign key that references Company(company_ID) In WorksAt, PID is a foreign key that references Person(PID); (company_ID, branch_num) is a foreign key that references Branch(company_ID, branch_num) In Connection, PID is a foreign key that references Person(PID); friend_ID is a foreign key that references…
Show that if a relation has no attribute that is functionally determined by all the other attributes, then the relation has no nontrivial FD’s at all.
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.
Explain three conditions that suggest a surrogate key should be created for the primary key of a relation.
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.)
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.
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.

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