Question

Question 4   4 Marks

Relational model and Normalisation                                     

The following table identifies the information that the funding managers trade the stocks. But this table has been wrongly designed. It has been assumed that the combination of ManagerID, StockCode and TradeDate uniquely identifies each tuple in this relation.

StockTrade

ManagerID	ManagerName	StockCode	CompanyName	Action	Quantity	Price	TradeDate
M101	James Cook	ORG	Origin Energy	Buy	10,000	10.10	12-1-2019
M101	James Cook	RIO	Rio Tinto	Sell	20,000	88.50	15-1-2019
M102	Shirley Lin	ORG	Origin Energy	Sell	10,000	11.50	15-1-2019
M102	Shirley Lin	BHP	BHP Billiton	Buy	50,000	31.20	18-1-2019
M103	David Johnson	AGL	AGL Energy	Sell	30,000	17.90	19-2-2019
M104	Linda Coles	RIO	Rio Tinto	Buy	70,000	75.90	19-2-2019

1. On the above table, if we want to add a record related to the stock - TLS (Telstra), what kind of anomaly will happen? Justify your answer. (1 mark)
   
2. What is the highest normal form that this relation satisfies and why?      (1 mark)
   
3. Normalise the relation into a set of relations that satisfy 3NF using the format as follows:

Customer (CustomerID, CustomerName)

Order (OrderID, Amount, Date, CustomerID)

         Foreign key (CustomerID) references Customer

                                                                                                                                  

Answer 1

Solution)

Q1.

Adding TSL stock might cause inserion anamoloy since any manager might not be available to manage it.

Q2 and Q3

I HOPE THIS ANSWER WILL HELP YOU PLEASE DO UP VOTE THANK YOU.