Database questions

3. This question concerns normalisation and stored procedures & triggers: (a) List the THREE different main types of anomalies in relational databases. For each case give a convincing example [9 marks] (b) Given the relation schema Order(orderNo, productNo, customerNo, cName, quantity, costPerUnit) with the following full functional dependencies orderNo customer-No, customerNo productNo costPerUnit { OrderNo, productNo } quantity cName, and thus also orderNo cName i. Explain briefly why { orderNo, productN} is a candidate key [3 marks] ii. Is { customerNo, productNo } a candidate key? Explain your answer [3 marks] ii. Explain whether or not the relation schema Order is in Third Normal [2 marks] iv. Normalise to Second Normal Form. Define relation schemas with primary key and, where necessary, foreign key declarations [7 marks] v. Normalise to Third Normal Form. Define relation schemas with pri mary and, where necessary, foreign key declarations. 3 marks] vi. What would it mean for an order if the following were a full functional briefly Form dependency? { customerNo, quantity } costPerUnit Explain in business terms without referring to functional dependen [5 marks] (c) List THREE advantages of using stored procedures in database appli [6 marks] (d) Consider the following Relational Database Model for a computer store cies cations ComputerModel (modelNo, producer, model, description) primary key modelNo DisplayItem (productNo, price, model, aisle) primary key productNo foreign key model references ComputerModel (modelNo) Write the required trigger(s) to enforce the following enterprise constraint for the computer store All the computers displayed on the same aisle are manufactured by the same producer [12 marks]