i. The functional dependencies regarding the relational database of shopping basket are:
A shopping basket has a variety of features. We investigate what other attributes may be obtained from the given attribute based on the description provided regarding the different attributes.
custNum – it is a unique id given to each customer. So, using this we can find out custName, custAddress, custContact. So, we can evaluate or define that custName –> custName, custAddress, custContact
Custname was not able to uniquely find the other attributes that is connected to the order or delivery person or even the card details. This is due to the customer can have multiple orders. Different service people would be allocated to different orders. In addition, customers can choose separate cards for each order.
ordNum – It is the unique number that is assigned to order. This might be uniquely identify order date(ordDate) and total order price(totordPrice) ordNum –>ordDate,totordPrice
The order doesn’t uniquely identify the items in it. OrdLineNum is a special identifier for an item that we can use to identify its description (itemDesc). However, in order to know how much quantity(quantity) of item was ordered and total price to (ItemPrice) of item there will need both item number(OrdLineNum ) and order number(ordNum ) both. Since each and every item in order is assigned or allocated to a deliveryPerson, by using the order number and item number we can identify the delivery person, expected delivery date and deliveryPersoncontact. So, from this we have the following functional dependencies.
OrdLineNum –> itemDesc deliveryPersonContact –> deliveryPerson
OrdLineNum, ordNum –> totItemPrice, quantity, deliveryPerson, deliveryPersonContact, expectedDeliveryDate
A customer may have several credit cards in his or her possession. We’ll still need the order number to figure out which card was used for which order.
custNum, ordNum –> creditCardNum, totOrdPrice
ii.
From the above shown dependencies, there is clearly seen the combination of custNum, ordNum, ordLineNum can be derived all the other attributes of this relations. Therefore, the super minimal key of the relation is: custNum, ordNum, ordLineNum.
iii.
If all of the simple (not composite) and single-valued attributes in a reference schema are 1NF, it is said to be in 1NF.Any relational schema is in 1NF by nature.
In the case this, relational schema should be on the 2NF, there should not be on partial key dependence I.e., non-prime attribute / non key attribute should be dependent on partial key or even the part of key. In the above relation or dependence from the above table, there is seen a lot of the partial dependence I.e., custNum, ordNum –> creditCardNum, totOrdPrice. Therefore, this is not in 2NF. So, the highest normalize form of this is in 1NF.
iv.
In the Case of a relational schema to be in BCNF, the rule is for all functional dependencies of the form X–>Y, X should be superkey. So that it will be decomposed relation shoppingBasket according to rule mentioned into following relations.
OrderPayments:
custNum, ordNum, ordLineNum, creditCardNum, TotOrdPrice
Customer: custNum, custName, custAddress,
custContact Item: OrdLineNum, itemDesc
Order: ordNum, ordDate, TotordPrice
OrderDetails: OrdLineNum, ordNum,
totItemPrice, quantity, deliveryPerson, deliveryPersonContact
,
expectedDeliveryDate DeliveryAgents:
DeliveryPersonContact
, DeliveryPerson
