0

I decomposed the single table below into 3 tables in order to get it into 3NF based on the following dependencies and composite key (FirstName, LastName, Career):

First normal form:

FirstName, LastName, Address, Career, Pay, Managerial

Dependencies:

FirstName, LastName -> Address
Career -> Pay, Managerial

Third normal form:

People (FirstName, LastName, Career)
Addresses (FirstName, LastName, Address)
Careers (Career, Pay, Managerial)

For the purposes of this example we can assume that (FirstName, LastName) is unique as well as (Career) to avoid creating IDs for these in the Addresses and Careers tables.

Am I correct in my thinking that there are no foreign keys in this schema? The keys in Addresses and Careers only partially make up the key in People. Or does People actually have 2 foreign keys: FK (FirstName, LastName) and FK (Career) and 1 primary key: PK (FirstName, LastName, Career)?

Pierre Delecto
  • 455
  • 1
  • 7
  • 26
  • 1
    We can't tell you whether FKs hold unless you tell us to assume what all component CKs or FDs are & to assume that your decomposition is correct. (Even then you could be giving us contradictory information.) In other words the FKs that hold depend on the definition of FK & not anything unique to normalization decomposition. PS In SQL "FK" does not mean FK. It more or less means foreign superkey. PS What does tag SQL have to do with this in any way not implied by [tag:database-normalization]? – philipxy Apr 28 '19 at 21:47
  • 1
    One needs CKs to decompose & that requires a FD cover but you don't say that's one. 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. Please see [ask], hits googling 'stackexchange homework' & the voting arrow mouseover texts. PS Normalization to higherf NFs by decomposition does not involve introducing new columns. – philipxy Apr 28 '19 at 21:54
  • 1
    "(FirstName, LastName) is unique as well as (Career)" in what table(s)? – philipxy Apr 28 '19 at 22:45

3 Answers3

2

Normalization to higher NFs by decomposition replaces a table by others that natural join back to it that are projections of it. So two components have the same set of subrow values for any shared columns, because they are both projections of the original. So there is a FK (foreign key) from a column set & table to another column set & table when/iff the latter column set forms a CK (candidate key) in the latter table.

Here if your original FDs form a cover: You have People CKs {{FirstName, LastName, Career}}, Addresses CKs {{FirstName, LastName}} & Careers CKs {{Career}}. So the FKs are People FKs {FirstName, LastName} to Addresses & {Career} to Careers.

PS Frequently when normalizing we realize that we don't want projections of an original table but instead we want tables with headings like some components but that hold more rows. (Sometimes this is misrepresented as part of normalization.) (We detect & redesign for deletion & insertion anomalies although normalization only addresses update anomalies.) Then these different tables that we choose don't in general have the same CKs or subrow value sets as the normalization components.

PS Normalization to higher NFs does not introduce new columns. That includes id columns.

PS Relationally "FK" usually involves a column set. But it can mean a column list referencing another. Or a column list where each name appears once referencing another. But SQL "FK" doesn't mean any of those relational notions of FK--it more or less means foreign superkey. Similarly SQL "PK" more or less means primary superkey. See this answer.

philipxy
  • 14,867
  • 6
  • 39
  • 83
1

You are incorrect. I don't agree with your schema, but Person.Career is a foreign key reference to Careers.Career.

Similarly, there should probably be an address id and a person id somewhere in the schema.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Edited to add "For the purposes of this example we can assume that (FirstName, LastName) is unique as well as (Career) to avoid creating IDs for these in the Addresses and Careers tables." – Pierre Delecto Apr 28 '19 at 17:35
-2

Actually you have many-to-many relationships. Thus, one address can be taken by many persons. One person can have many addresses. In this case you will have duplicates in persons table and dupicates in addresses table. Foreign keys will be different, but other parts will repeat. It is common to have tables:

PersonsAdresses (person_id, address_id, occulied_from, occupied_till)

PersonCareers (person_id, career_id, employee_from, employee_till)

Other part is that there is no RDBMS that supply complex keys as you mentioned, as it is described in relation db theory

Van Ng
  • 773
  • 1
  • 7
  • 17
  • 1
    The question's original table does not have those columns & it specifically says FirstName, LastName -> Address. Also normalization to higher NFs does not introduce new columns. You are mentioning a bunch of things irrelevant to & contradictory to the question. – philipxy Apr 28 '19 at 22:01
  • These things are relevant to real life solutions, due to my expirience. So I have mentioned them as a _common_ solution. Not thinked about it as science examination. – Van Ng Apr 28 '19 at 22:19
  • 1
    This is not an answer to the question. – philipxy Apr 28 '19 at 22:37