Database questions 3. This question concerns normalisation and stored procedures

Question

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]

Explanation / Answer

Question 3.a

Three different type of anomalies in relational database are as follows
a.Insertion Anomaly

Insertion anomaly occurs when we try to enter value to the database but we cannot enter data due to some missing data.
lets take an example
In employee taable there are three field empid,name and salary.Where empid is a primary key.If we keep the empid blank then we cannot enter values in the table as the coloumn cannot be empty.

b.Deletion Anomaly

Deletion anomaly means when data is deleted from the database due to the deletion of other data.
Lets take an example
In the student table there is name and roll column .And in other table there is social activity name.If we want to delete in student table the other data si remeoved form the social activity table.This happens because the data are not normalised.

c.Update Anomaly

Update Anomaly occures when data needs to be updated in many location but it is only updated in few location not in all.
lets take an example
In the employee table there is a coloumn name ,id and mobile number.And in other table salary there is emp id,mobile and salary.
Now we update the mobile number in update table for a particular employee but it is not updated in other table.So in this case the employee table is updated but the salary remains with the old value.

Question 3.b.i

{orderNo,productNo} is a candidate key.OrderNo is a particular number for a specifice order and for specific order there are some product and which are having productNo.So for a specific order there will be specific peoductNo which can be identified uniquely.Hence it can be termed as candidate key.

Question 3.b.ii

{customerNo,productNo} is not a candidate key.CustomerNo is unique but the proudctNo doesnot signifies a particular data.As each customer can have mane product for different order or each customer can have same product for multiple order.

Question 3.b.iii

For a database to be be in 3NF the pre requisites is that the database needs to be in 1NF and 2NF.And 3NF speciifed that the referenced data which are not dependent on the primary key must be removed.Here the customerNo has referenced to both cName and Orderno.So it must be removed.Hence it is not in 3NF

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.