0

I am very confused about how to identify the primary key in the first normal form

In the first example: I can understand the reason that SR_ID and Cus_No are the primary keys. But why Mngr_ID is not a primary key? Why Mngr_ID is depended on SR_ID and Cus_No enter image description here

In another example: Why staff_No is not a primary key? Because in the lecture, the first step that my professor examine is start from finding PK. But I am not sure how to do this? In the this example, all other attributes can depend on property_no and IDate, so they are the pk. But I don't understand why staff_no is depended on property_no and IDate

enter image description here enter image description here

Shin Yu Wu
  • 1,129
  • 4
  • 14
  • 23
  • What exactly is your question? Are you asking about identifying CKs--based on FDs--or about which CK to pick as PK? A PK is just some CK you picked to call the PK. A table can have many CKs but it can only have one PK. What did your research show for identifying CKs? What did your research show for picking a CK as PK? PS The NF of a table is irrelevant to identifying its CKs & choosing a PK. A table's CKs must be known before one can determine the NFs it satisfies. PKs are irrelevant to NFs. – philipxy Apr 28 '19 at 09:35
  • Re "is this right": Show the steps of your work following your reference/textbook, with justification--you will likely find mistakes that make your question unnecessary & we want to check your work but not redo it & we need your choices when an algorithm allows them & otherwise we can't tell you where you went wrong & right. Give your textbook name & edition. Please see [ask], hits googling 'stackexchange homework' & the voting arrow mouseover texts. – philipxy Apr 28 '19 at 09:36
  • No. My question Is there a way to directly search pk in the first normal form? Because in the lecture, the first step that my professor examine is start from finding PK. But I am not sure how to do this? In the second example, all other attributes can depend on property_no and IDate, so they are the pk. But I don't understand why staff_no is depended on property_no and IDate – Shin Yu Wu Apr 28 '19 at 10:11
  • PKs are irrelevant. CKs matter. First we find all the FDs that hold. Finding FDs & CKs are easily found faqs. It is also in your textbook. You are just asking us to rewrite it--don't. Tell us where you are stuck in some particular presentation. PS Attribute staff_no is functionally dependent on set {property_no, IDate} in the bottom schema when--by definition of "functionally dependent"--in every DB state at most one staff_no value appears in the table value with a given {property_no, IDate} subrow value. Ie when at most one staff inspects a given property on a given date. – philipxy Apr 28 '19 at 18:42
  • [Are Determinants and Candidate Keys same or different things?](https://stackoverflow.com/a/34191604/3404097) – philipxy Apr 28 '19 at 18:50
  • Please ask one single clear non-duplicate researched question, with everything that can be text as text. Don't ask a bunch of questions. Don't give images for text. Clarify via edits, not comments. If you don't understand what you are supposed to do then there is no point in listing a bunch of questions that you don't know the answer to. – philipxy Apr 28 '19 at 20:56
  • @ShinYuWu I understand your confusion. The problem statement does not specifically answer the question: Could 2 people inspect a property together? It does say that the property can only be inspected once a day (which make property & date part of the key), but it is not sufficiently clear on how it must be inspected. If only 1 person can inspect property, then property and date are enough to uniquely id any record in the table (PK), if 2 people can inspect together, then searching by property and date could bring up more than 1 record (one per inspector), thus the staff must added to the key – camba1 May 02 '19 at 16:27

1 Answers1

-1

Well the primary key is the column or combination of columns that make the row in the table unique. In the first example, mgr Id is not part of the key because the manager is directly assigned to a user, thus having only one manager per user. So if you know the user, the manager is irrelevant as it is derived from the user. In the other hand, if a user could have more than one manager, then you would pull the manager Id from this table and create a manager sales rep table to keep that relationship. Either way, the manager is indeed not needed to make the sales rep - Customer relationship unique.

The second example is more confusing as it depends on interpretation of the (not very good) issue description. If only one person can inspect the house per day, then yes staff is no needed as part of the primary key. However, since the problem says that there could be more than one people sharing the car, it may be possible that two people could inspect the house together in one day. That would mean that the staff should be part of the key in that case.

In the end, like everything in software development, it all depends on what you are trying to accomplish with your application.

camba1
  • 1,795
  • 2
  • 12
  • 18
  • This is a bunch of fragments not tied to correct definitions. PS A "(not very good) issue description" merits a comment asking for edits for clarity, not a guess as an answer post. – philipxy Apr 29 '19 at 05:16