CODEWITHSUNDEEP
sqldatabasedatabase-designforeign-keysdatabase-normalization
Woden
Woden

Reputation: 1386

Normalization, correct referencing based on the question

I am practicing the database normalization, but sometimes I get confused about referencing. There is something I want to clarify first:

  1. The nested structure is given by the instructor, I don't know if it's correct or how it should be notated.
  2. I got the SCAN model, and the question specified the [PK] inside the SCAN, which are storeId, and the productId.

Here is the question statement:

Customers take their goods to the checkout and purchase them: we record this as one purchase, consisting of quantities of different products. We must record each item scanned at the checkout, and the method used to pay for the purchase. We record which store the goods were purchased at. We do not identify our customers.

SCAN (storeId, storeAddress, purchaseId, whenPurchased, paymentMethod, (productId, productName, quantityPurchased))

I can see the [PK] and its association with the attributes, and derive it as follows:

Table1(storeId [PK], storeAddress)
Table2(purchaseId [PK], whenPurchased, paymentMethod, quantityPurchased)
Table3(productId [PK], productName)

However, when I tried to reference the foreign key, I choose to do like below. The reason is the purchase is around the customer, which is the Table2. Therefore, when a customer purchases a product in a store, I need to reference the storeId and the productId as a foreign key, like the below:

Table1(storeId [PK], storeAddress)
Table2(purchaseId [PK], whenPurchased, paymentMethod, productId [FK], storeId [FK])
Table3(productId [PK], productName, quantityPurchased)

Sometimes I get confused about which Table should reference the other. For example, there are two table: department and emp. The emp should reference departmentId as a foreign key because each employee affiliated to at least one department, but the opposite is wrong. How can I determine the relationship between two entities and reference correctly?

Upvotes: 0

Views: 80

Answers (1)

Woden
Woden

Reputation: 1386

I've figured out an explanation of this question. The normalization is based on the primary key. Therefore, for this question, the form should be derived like this:

Store(storeId [PK], storeAddress)
Customer(purchaseId [PK], whenPurchased, paymentMethod)
Product(productId [PK], productName, quantityPurchased)

Supposed there is a customer purchase a product. Each purchase record is recorded known as a receipt. Therefore, for specifying which product in which store. We need to reference storeId and productId into the receipt. Thus, the normalized form will be :

 Store(storeId [PK], storeAddress)
 Customer(purchaseId [PK], whenPurchased, paymentMethod, storeId [FK], productId [FK])
 Product(productId [PK], productName, quantityPurchased)

The quantityPurchased should be with Product form because each product has its unique productId, but can have the same productName. For example, an iPhone 11 cellphone could have iphone 11 128GB black as a productName, but each iphone 11 128GB black will have different productId. If I want to count how many iphone 11 128GB black are sold, I can group by this productName and count(productName) to find the quantityPurchased.

Upvotes: 1

Related Questions