将表格标准化为第三个正常形式

问题描述

这个问题显然是一个家庭作业问题。我不明白我的教授，不知道他在选举期间说什么。我需要逐步的指示，以便将下面的表格标准化为1NF，然后是2NF，然后是3NF。

我感谢任何帮助和指示。

解决方案

好的，我希望我记得所有人都正确，让我们开始...

规则

为了使它们非常短（不是非常精确，只是给你一个第一个想法是什么）：

• NF1 ：表格单元格不能包含多个值。


• NF2 ：NF1
NF3 ：NF2和非主键列可能不依赖于每个主键列其他。

说明

• NF1 ：查找包含多个值的表格单元格，将它们分成不同的列。


NF3 ：查找依赖于其他非主要列的列，并将列放入另一个仅包含他们真正依赖的主键列的表中。键列，此外还取决于主键。 h3>



一列 state 具有类似WA，Washington的值。 NF1被违反，因为这是两个值，缩写和名称。





解决方案：要完成NF1，请创建两个列 STATE_ABBREVIATION 和 STATE_NAME 。





NF2





假设您有一个包含这四列的表格，车型名称：






• COUNTRY_ID （数字，主键） >
  
• CAR_MODEL_ID （数字，主键）


• COUNTRY_NAME （varchar）


• CAR_MODEL_NAME （varchar）






表格可能有以下两个数据行：






• 第1行：COUNTRY_ID = 1，CAR_MODEL_ID = 5，COUNTRY_NAME = USA，CAR_MODEL_NAME = Fox


• 第2列：COUNTRY_ID = 2，CAR_MODEL_ID = 5，COUNTRY_NAME =德国，CAR_MODEL_NAME = Polo






这就是说，在美国，Fox模型被称为Fox，但在德国，同样的汽车模型被称为Polo（不记得那是真的）。​​





NF2被违反，因为国家名称不依赖于车型ID和国家ID，而只取决于国家ID。





解决方案：要实现NF2，请将 COUNTRY_NAME 移到单独的表COUNTRY > COUNTRY_ID （主键）和 COUNTRY_NAME 。要获取包含国家/地区名称的结果集，您需要使用JOIN连接这两个表。





NF3





假设您有一个包含这些列的表格，表示状态的气候条件： p>





• STATE_ID （varchar，主键）


• CLIME_ID （外键，沙漠，雨林等气候区域的ID）


• IS_MOSTLY_DRY （bool）






违反NF3，因为IS_MOSTLY_DRY仅取决于CLIME_ID





解决方案：要满足NF3，请将列 MOSTLY_DRY 进入气候区表。





---





这里有一些关于练习中给出的实际表的想法： / p>



我应用上述NF规则，而不挑战主键列。






• NF1没有被违反，每个单元格只保留一个值。


• EMP_NM和所有电话号码违反了NF2，因为所有这些列都不依赖于完整的主键。它们都依赖于EMP_ID（PK），但不在DEPT_CD（PK）上。我假设电话号码在员工移动到其他部门时保持不变。


• DEPT_NM也违反了NF2，因为DEPT_NM不依赖于完整的主键。


• 所有技能列都违反了NF2，因为它们不是部门，而只是针对员工的。


• NF3被SKILL_NM违反，因为技能名称只取决于技能代码，甚至不是复合主键的一部分。


• SKILL_YRS违反NF3，在主键成员（EMP_ID）和非主键成员（SKILL_CD）上。因此，它部分取决于非主键属性。






因此，如果删除所有违反NF2或NF3的列，主键保留（EMP_ID和DEPT_CD）。剩余部分违反了给定的业务规则：此结构将允许员工在多个部门同时工作。





让我们从远处回顾一下。您的数据模型是关于员工，部门，技能和这些实体之间的关系。如果你把它标准化，你会得到一个表的雇员（包含DEPT_CD作为外键），一个部门，一个技能，另一个为员工和技能之间的关系，持有技能年（EMP_ID和SKILL_CD的每个元组）（我的老师会将后者称为关联实体）。




This questions is obviously a homework question. I can't understand my professor and have no idea what he said during the election. I need to make step by step instructions to normalize the following table first into 1NF, then 2NF, then 3NF.

I appreciate any help and instruction.

解决方案

Okay, I hope I remember all of them correctly, let's start...

Rules

To make them very short (and not very precise, just to give you a first idea of what it's all about):

• NF1: A table cell must not contain more than one value.
• NF2: NF1, plus all non-primary-key columns must depend on all primary key columns.
• NF3: NF2, plus non-primary key columns may not depend on each other.

Instructions

• NF1: find table cells containing more than one value, put those into separate columns.
• NF2: find columns depending on less then all primary key columns, put them into another table which has only those primary key columns they really depend on.
• NF3: find columns which depend on other non-primary-key columns, in addition to depending on the primary key. Put the dependent columns into another table.

Examples

NF1

a column "state" has values like "WA, Washington". NF1 is violated, because that's two values, abbreviation and name.

Solution: To fulfill NF1, create two columns, STATE_ABBREVIATION and STATE_NAME.

NF2

Imagine you've got a table with these 4 columns, expressing international names of car models:

• COUNTRY_ID (numeric, primary key)
• CAR_MODEL_ID (numeric, primary key)
• COUNTRY_NAME (varchar)
• CAR_MODEL_NAME (varchar)

The table may have these two data rows:

• Row 1: COUNTRY_ID=1, CAR_MODEL_ID=5, COUNTRY_NAME=USA, CAR_MODEL_NAME=Fox
• Row 2: COUNTRY_ID=2, CAR_MODEL_ID=5, COUNTRY_NAME=Germany, CAR_MODEL_NAME=Polo

That says, model "Fox" is called "Fox" in USA, but the same car model is called "Polo" in Germany (don't remember if that's actually true).

NF2 is violated, because the country name does not depend on both car model ID and country ID, but only on the country ID.

Solution: To fulfill NF2, move COUNTRY_NAME into a separate table "COUNTRY" with columns COUNTRY_ID (primary key) and COUNTRY_NAME. To get a result set including the country name, you'll need to connect the two tables using a JOIN.

NF3

Say you've got a table with these columns, expressing climatic conditions of states:

• STATE_ID (varchar, primary key)
• CLIME_ID (foreign key, ID of a climate zone like "desert", "rainforest", etc.)
• IS_MOSTLY_DRY (bool)

NF3 is violated, because IS_MOSTLY_DRY only depends on the CLIME_ID (let's at least assume that), but not on the STATE_ID (primary key).

Solution: to fulfill NF3, put the column MOSTLY_DRY into the climate zone table.

---

Here are some thoughts regarding the actual table given in the exercise:

I apply the above mentioned NF rules without to challenge the primary key columns. But they actually don't make sense, as we will see later.

• NF1 isn't violated, each cell holds just one value.
• NF2 is violated by EMP_NM and all the phone numbers, because all of these columns don't depend on the full primary key. They all depend on EMP_ID (PK), but not on DEPT_CD (PK). I assume that phone numbers stay the same when an employee moves to another department.
• NF2 is also violated by DEPT_NM, because DEPT_NM does not depend on the full primary key. It depends on DEPT_CD, but not on EMP_ID.
• NF2 is also violated by all the skill columns, because they are not department- but only employee-specific.
• NF3 is violated by SKILL_NM, because the skill name only depends on the skill code, which is not even part of the composite primary key.
• SKILL_YRS violates NF3, because it depends on a primary key member (EMP_ID) and a non-primary key member (SKILL_CD). So it is partly dependent on a non-primary-key attribute.

So if you remove all columns which violate NF2 or NF3, only the primary key remains (EMP_ID and DEPT_CD). That remaining part violates the given business rules: this structure would allow an employee to work in multiple departments at the same time.

Let's review it from a distance. Your data model is about employees, departments, skills and the relationships between these entities. If you normalize that, you'll end up with one table for the employees (containing DEPT_CD as a foreign key), one for the departments, one for the skills, and another one for the relationship between employees and skills, holding the "skill years" for each tuple of EMP_ID and SKILL_CD (my teacher would have called the latter an "associative entity").

这篇关于将表格标准化为第三个正常形式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！