Answered step by step
Verified Expert Solution
Question
1 Approved Answer
This is my data base assignment. Please help me to solve this. Thank you. The table below is an Excel-based sheet representing a purchase history
This is my data base assignment. Please help me to solve this. Thank you.
The table below is an "Excel-based" sheet representing a purchase history of products bought by projects of a company Project ID Project Name Dept. ID Dept. Name Budget Product ID Product Name Unit Price Qty Total A001 Office Renewal 1001 Planning 100000 501A LCD 30000 2 60000 A002 PC replacement 1002 Development 1000000 501A LCD 30000 5 1220000 801B PC 150000 5 1001 Planning 400000 801B PC 150000 2 702A Camera 5000 4 This table shows that, for example, the project "Office Renewal" with the project ID "A001" allocated 100,000 JPY budget to the department "Planning", and purchased two units of the product "LCD" of the unit price 30,000 JPY with the product ID "501A", leading to the total price 60,000 JPY. As another example, the project "PC replacement" with the project ID "A002" allocated 1,000,000 JPY to the department "Development" and 400,000 JPY to the department "Planning", and these departments purchased "LCD", "PC" and "Camera", leading to the total price 1,220,000 JPY. A relational schema to represent this purchase history can be as follows (keys are not indicated). Purchase History(prj_id, prj_name, dept_id, dept_name, budget, prd_id, prd_name, unit_price, quantity, total_price) Regarding this relation, answer the following questions. (1) Explain what is "update anomaly" by showing an example of updating one of the values in the table. (2) List up all functional dependencies with describing their reasons. (3) Identify the candidate key(s). (4) Identify the best normal form that this relation satisfies. (5) Decompose this relation into BCNF. (6) For the decomposed relations, which type of index should be prepared in which attribute of which relation, in order to process the following query in a good performance? "Get the name of departments which purchased products whose price was over 100,000 JPY." (7) Describe your thought on how the "Total" attribute should be dealt withStep by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started