determining foreign, primary keys, 1nf, 2nf, 3nf given table and functional dependencies

Question

first and foremost i would like to say that this is for a past homework assignment that i could not figure out and have come here to ask for clarification. I am having trouble with normalization for this specific question.

given

1.PetStore(storeBranchName, storeAddr, storeManager,(customerName, customerAddr, customerPhone,(petName, petBreed, petSex, price) ) )

FDs

storeBranchName → storeAddr, storeManager
customerName → customerAddr, customerPhone
customerName, petName →  petBreed, petSex
customerName,storeBranchName → petName 
petBreed → price

a. Is this relation in 1NF? If not, why isn't it? Then put it in 1NF.

b. Is this relation in 2NF? If not, why isn't it? Then put it in 2NF.

c. Is this relation in 3NF? If not, why isn't it? Then put it in 3NF.

d. identify the primary keys(underline) and the foreign keys(italicize) for the relation.

my question now is how would i determine what form this is in? my attempt at a solution.

a. the table is not in 1NF because each value is not at atomic value

1NF

PetStore(storeBranchName, storeAddr, storeManager,customerName, customerAddr, customerPhone,petName, petBreed, petSex, price )

****here is where i start having problems****

b. the relation cannot be in 2NF because it was not in 1NF

2NF

store(storeBranchName, storeAddr, storeManager)
customer(customerName, customerAddr, customerPhone)
pet(petName,petbreed,petsex)

c.?

3NF

store(storeBranchName, storeAddr, storeManager)
customer(customerName, customerAddr, customerPhone)
pet(petName, petBreed, petSex)
petCust(customerName,storeBranchName, petName)
petPrice(petBreed, price)

d. i really have trouble deciding what the primary keys would be here and dont really understand the cocnept of foreign keys. if anyone could give me any hints or clues i really do prefer to not receive a straight answer unless its by correcting something i may have done wrong. any help would be appreciated.

Answer 1

The assignment isn't structured well. Questions a, b and c refer to "this relation", and if it's interpreted as referring to the original given relation, the answer to b and c will start with "because it's not in 1NF". It would be a better test of a student's understanding if b and c referred to the answer to the preceding question. In addition, question d should be applied to each step.

I also question the given FD customerName,storeBranchName → petName. It offends common sense (a customer can only buy one pet per store), and if it was the case, the original nested relation would have no need to nest (petName, petBreed, petSex, price). Perhaps it was added to an existing question to complicate it.

Your answer to a is correct, but I would like to see set-, tuple- or relation-valued attributes identified in the original relation, or a mention of the traditional "repeating groups". As I said above, I would also like to see keys identified in each step. From the given FDs, we can determine that customerName,storeBranchName is a candidate key for the 1NF relation - demonstrate this by deriving the closure of the FDs for this set of attributes.

For question b, explain why your answer to question a isn't in 2NF by demonstrating a partial dependency. Your 2NF relations don't follow from normalization of the 1NF answer. You should have 3 relations, keyed by customerName, storeBranchName and customerName,storeBranchName respectively.

For question c, explain why the answer to question b isn't in 3NF by demonstrating a transitive dependency. Your 3NF relations don't follow from your 2NF answer, but they're close to the correct answer. The correct pet relation is pet (customerName PK, petName PK, petBreed, petSex).

Primary keys should be maintained/derived through the normalization process, not "decided" after the fact. You only need to identify a primary key once, for 1NF, the others should follow from the normalization.

The concept of foreign keys means different things in the relational model and the old network model. In the relational model, a foreign key constraint is just a subset integrity constraint, e.g. petCust.customerName ⊆ customer.customerName. In the network model, it represents a relationship between petCust and customer records. I recommend studying and comparing the two models.

Hope this helps, feel free to ask questions in the comments.