-2

I've designed complex databases (self-taught), but taking my first official database course in school; recently I've been assigned to design a database schema that is 2NF.

I've been working with a table formatted as follows:

enter image description here

Where STDNTNMBR must be unique for all entries AND SSN must be unique for all entries (not necessarily the combination of both). Essentially, both STDNTNMBR and SSN are both primary keys for the table.

At first glance, this table doesn't appear to be in 2NF because a non-prime attribute FIRSTNAME is partial dependent, because it can depend on STDNTNMBR or depend on SSN.

My question is: how can I convert this table into 2NF?

It wouldn't make sense to have another table that maps STDNTNMBR to SSN, such as:

enter image description here

This type of relationship would have to be one-to-one, where StudentSsn.SSN is the primary key, and the StudentSsn.STDNTNMBR is a foreign key to Student.STDNTNBMR.

Although this proposed solution isn't exactly clean, is it technically 2NF?

Or am I completely off-track?

Any help is much appreciated!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
G M
  • 311
  • 1
  • 5
  • 11
  • Please ask 1 (specific researched non-duplicate) question. PS "is partial dependent, because it can depend on STDNTNMBR or depend on SSN" That "because" is wrong. Write your reasoning out in full to see, including the definitions of the technical terms. PS Your "my question is" question is a faq. Please before considering posting read the manual/reference & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect your research. [ask] [Help] – philipxy Jan 21 '22 at 22:23
  • Your "I have these FDs" (& CKs) doesn't make sense. "These are all the FDs that hold"?--Not possible. "These are all the non-trivial FDs that hold"?--Not possible. "These are some FDs that hold"?--Question can't be answered. Find out what a *cover* is & what the exact conditions are to apply a particular definition/rule/algorithm. To determine CKs & NFs we must be given FDs that form a cover. Sometimes a minimal cover. And the set of all attributes must be given. [See this answer.](https://stackoverflow.com/a/53386492/3404097) PS PK means different things in the relational model & SQL. – philipxy Jan 21 '22 at 22:35
  • [Partial Dependency (Databases)](https://stackoverflow.com/a/25827210/3404097) – philipxy Jan 21 '22 at 22:50

1 Answers1

1

It does satisfy 2NF already.

FirstName cannot be partially dependent on the candidate keys, because both candidate keys (StndtNmbr and SSN) are only a single column each.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • This reasoning is incomplete, since you don't mention that you rely on {} not being a determinant. (Dependence on a one-attribute CK is paritial for an attribute determined by {}, ie an attribute that is the same value in every tuple.) Anyway the question has the usual problem that it gives some FDs & other constraints, but doesn't actually say that the only FDs are the ones that are implied by the ones they give. – philipxy Jan 21 '22 at 22:30