Q4. Consider a database schema recording data for suppliers that supply parts for projects. We store...

 

 

Transcribed Image Text:

Q4. Consider a database schema recording data for suppliers that supply parts for projects. We store the following attributes: SupplierNo, SupplierName, PartNo, PartName, ProjectNo, ProjectName, Quantity. Four functional dependencies are known for this set of attributes, as marked in the figure below. SupplierNo SupplierName PartNo PartName Project No ProjectName Quantity A database designer uses the following two tables to store the data. Answer the following questions about the proposed design (in each question, show the reason clearly). SUPPLIER-PARTS( SupplierNo, SupplierName, PartNo, PartName, Qty) PROJECT-PART( ProjNo, ProjName, PartNo) (a) Suggest a primary key for the table SUPPLIER-PARTS (b) Suggest a primary key for table PROJECT-PARTS (c) Write some possible values (that are consistent with the database requirements) in the following instance of SUPPLIER-PARTS and demonstrate an insertion anomaly. Supplier No SupplierName PartNo PartName Quantity (d) Is it possible for PROJECT-PARTS to have update anomalies (give reason)? (e) We want to find out how much quantity of a specified part was used by a specified project. Write clearly (step-by-step) how you will use the two tables to find out this information. (f) Based on your answer of part (e) only, would you say that this design using the two tables is a good design (give reason)? Q4. Consider a database schema recording data for suppliers that supply parts for projects. We store the following attributes: SupplierNo, SupplierName, PartNo, PartName, ProjectNo, ProjectName, Quantity. Four functional dependencies are known for this set of attributes, as marked in the figure below. SupplierNo SupplierName PartNo PartName Project No ProjectName Quantity A database designer uses the following two tables to store the data. Answer the following questions about the proposed design (in each question, show the reason clearly). SUPPLIER-PARTS( SupplierNo, SupplierName, PartNo, PartName, Qty) PROJECT-PART( ProjNo, ProjName, PartNo) (a) Suggest a primary key for the table SUPPLIER-PARTS (b) Suggest a primary key for table PROJECT-PARTS (c) Write some possible values (that are consistent with the database requirements) in the following instance of SUPPLIER-PARTS and demonstrate an insertion anomaly. Supplier No SupplierName PartNo PartName Quantity (d) Is it possible for PROJECT-PARTS to have update anomalies (give reason)? (e) We want to find out how much quantity of a specified part was used by a specified project. Write clearly (step-by-step) how you will use the two tables to find out this information. (f) Based on your answer of part (e) only, would you say that this design using the two tables is a good design (give reason)?