0

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?

Woden
  • 1,054
  • 2
  • 13
  • 26
  • 1
    Right now you are just asking for us to rewrite your textbook with a bespoke tutorial. Please see [ask], hits googling 'stackexchange homework' & the voting arrow mouseover texts. Show the steps of your work following your textbook with justification & ask 1 specific researched non-duplicate question re the first place you are stuck. Quote the definitions, theorems & algorithms you are relying on. All the steps are also SO faqs. PS Determining FKs is not part of normalization to higher NFs. Deteriming them is also in your textbook & a faq. – philipxy Jun 14 '20 at 00:04
  • 1
    Re FKs & normalization to higher NFs: [Can you move ... keys and/or foreign keys to other tables when normalizing ...](https://stackoverflow.com/a/45072451/3404097) [Is it possible to have more than one foreign key in a normalised database schema?](https://stackoverflow.com/a/48937853/3404097) – philipxy Jun 14 '20 at 00:23
  • Thank you, sir for the suggestion. I am really grateful for that, and I'll take your advice. Maybe I am not so good at asking the question, but I'll keep improving. Thank you, sir. – Woden Jun 14 '20 at 00:26
  • 1
    I hope you follow my comment & edit this post. PS You nest parentheses when you give SCAN. That is not standard notation.--But you don't explain it. Maybe you mean SCAN is not a relation.--But then, you don't explain what it is. Maybe you mean SCAN is a relation with an attribute with components. Regardless, productId is not a SCAN attribute so it can't be part of a SCAN PK. Also regardless, you are initially normalizing to 1NF. But ["1NF" has no single meaning](https://stackoverflow.com/a/40640962/3404097). So you need to tell us your definitions & preferably also textbook name & edition. – philipxy Jun 14 '20 at 00:40
  • Thank you, @philipxy, sir. I've edited the post and found where I was confused. – Woden Jun 14 '20 at 01:25
  • 1
    It is not clear what you are starting with or what you are trying to do with it or where you are 1st stuck or what your 1 specific question is. Please decide what the input is & what you are trying to do & tell us. Clearly give just what is necessary. Don't post the nested parentheses unless you say what they mean. (Ask yorur instructor.) "normalization" to higher NFs doesn't mean anything specific unless you give a NF. Please act on all my comments. PS "I can simply derive to the form below." is not "Show the steps of your work following your textbook with justification". Etc. – philipxy Jun 14 '20 at 01:37
  • 1
    Why does your post contain anything other than the last sentence, and what does your textbook say about it, and what does other research including googling give? (Including with 'site:stackoverflow.com'.) When to declare FKs is a faq. – philipxy Jun 14 '20 at 01:48
  • 1
    A relational FK is an attribute set whose subtuple values appear elsewhere as CK. An SQL FK is a column set whose null-free subrow values appear elsewhere as UNIQUE. Declare when not implied by other declarations. That is *after* we have determined tables. – philipxy Jun 14 '20 at 02:05
  • 1
    Thank you, sir, for your patience and kindness. I think I know how to do it right now. – Woden Jun 14 '20 at 02:07

1 Answers1

1

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.

Woden
  • 1,054
  • 2
  • 13
  • 26
  • This doesn't reflect anything about normalization, in either of its senses of to 1NF or to higher NFs. I hope you'll find out about it, good luck. – philipxy Jun 16 '20 at 04:37
  • 1
    My question is neither about reflecting anything about normalization nor about how detailed the normalization process should be. It is clear that the question is about referencing after normalization and how normalization can be done in a quicker way. Have a nice day. – Woden Jun 16 '20 at 04:50