Question

Question 1 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

Quesation 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.

Quesation 2 : What is the highest normal form that this relation satisfies and why?     (1 mark)

Quesation 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

Q1.

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

Q2, 3

Stock The Trade Date Stock Code → normal form FD, the Since the FD above are primary key of given relation is ㅓ Managerlo, The FD possible Manager ID Manager Name Company Name partial highest given relation is in is INF. Normalizing it to 3NF > compory (Stockcode company Name) Manager (ManagerID, Manager Name) R Stock Manager, Action, Quantity, Price, Trade Date) company Stock Code, company Name) Manager ( ManagerID, Manager Name) Orcks Stockcode, Managerid, Action, Quality Price, Trade Date) Foreign Key (Stock code) references company Foreign Key (Manager ID) references Manager.

If you have any questions comment down. Please don't simply downvote and leave. If you are satisfied with answer, please? upvote thanks