Analysis Of Relational Schemas And Normalization

  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

Share this post

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn
Share on whatsapp
WhatsApp

Related posts

Keep in touch with the trends