-1

A table in First Normal Form: enter image description here

While converting it to Second Normal Form should I use the same Employee ID in 1 table as Primary and use it as a Foreign key in the other table like shown in the picture?

enter image description here

Or do I need to make a Primary Key in the 2nd table called City ID beside the Foreign Key Employee ID to convert it to Third Normal Form? Or can I just continue with a Foreign key? And how many tables should be in the Third Normal Form?

philipxy
  • 14,867
  • 6
  • 39
  • 83
Ethan Brown
  • 107
  • 6
  • 1. empId,name,surname,city is employee table, 2, create table major-> majorid, major, facility 3. add mador id to employe as foregn key – Taurus Silver Nov 09 '22 at 04:14
  • [Why should I not upload images of code/data/errors when asking a question?](https://meta.stackoverflow.com/q/285551/3404097) – philipxy Nov 09 '22 at 04:33
  • What is your 1 specific researched non-duplicate question re how/why you are 1st stuck in what published presentation of what design method/process given what? Right now you are essentially asking us to (re)write a textbook with bespoke tutorial with no details on what you misunderstand or do or don't understand. [ask] [Help] Basic questions are faqs. [research effort](https://meta.stackoverflow.com/q/261592/3404097) PS It is unhelpful & unclear to ask 'A or B' when A & B are not mutually exclusive and/or not the only options. (And you may think they are when they are not.) Ask 1 question. – philipxy Nov 09 '22 at 04:38
  • DB normalization to higher NFs does not explicitly output FKs. So it's not clear what you think normalization is or even what you are trying to accomplish. Since normalization outputs projections of inputs, any column set that is a CK in an output merits a FK from every other table with that column set. Also, a SQL PK or UNIQUE NOT NULL declaration declares the analogue of a relational superkey & not necessarily a PK, and similarly a SQL FK is the analogue of a reference to a superkey, not to a CK necessarily. Since you tag with SQL it's not clear what you mean by PK & FK. – philipxy Nov 09 '22 at 04:44
  • 2NF is never a target NF. You wouldn't be declaring CKs, PKs & FKs until you get to your desired NF in all tables & have the tables you want. Before you get there FKs don't matter, they don't matter to DB normalization. PS [Does an empty SQL table have a superkey? Does every SQL table?](https://stackoverflow.com/a/46029100/3404097) [Is it possible to have more than one foreign key in a normalised database schema?](https://stackoverflow.com/a/48937853/3404097) – philipxy Nov 09 '22 at 04:47
  • [Foreign keys in first normal form?](https://stackoverflow.com/a/60133657/3404097) [Understanding foreign keys](https://stackoverflow.com/a/55895220/3404097) [Can you move compound keys and/or foreign keys to other tables when normalizing to 3NF (third normal form)](https://stackoverflow.com/a/45072451/3404097) – philipxy Nov 09 '22 at 05:09
  • Using common sense for the table meaning & business rules, the original table is already in 5NF. – philipxy Nov 10 '22 at 00:48
  • @TaurusSilver Normalization to higher NFs does not involve adding new columns. – philipxy Nov 10 '22 at 00:49
  • @philipxy Yeah i know, but if we look at his 1NF it is weird, no matter how you turn into db schame except create an new field. Except his facility become a candidate key but who want to create key with such a long string like that. – Taurus Silver Nov 10 '22 at 03:53
  • @TaurusSilver As a normalization exercise, there's no practicality issues with what the types are. But anyway under everyday expectations no string is part of a CK here. (And if ids are introduced it isn't from normalizing.) Bye. – philipxy Nov 10 '22 at 05:51

1 Answers1

0

2NF (Second Normal Form) Rules

Rule 1- Be in 1NF Rule 2- Single Column Primary Key that does not functionally dependant on any subset of candidate key relation. The point is that at this 2NF normalization stage, the table must be broken down based on the primary key.

EmployeeID in second table must be foreign ke because it refer from Employee table.

If still confuse you can visit this link --> https://www.guru99.com/database-normalization.html

dderizk
  • 41
  • 3
  • This is extremely unclear, and the things it seems to be trying to say are wrong. The linked site is a worthless & misleading pile of misconceptions. PS The question post is not clear & asks multiple questions, so it should be closed until clear & on-topic, not answered. [ask] [answer] [Help] – philipxy Nov 10 '22 at 05:59
  • 1
    As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Nov 13 '22 at 20:43