Homework Help Question & Answers
Question 1 Relational model and Normalisation
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
