如何在标准化中标准化日期 [英] How to normalize dates in Normalization
问题描述
这是我的练习问题.我删除了图片，并将表格添加为HTML代码格式.
This is my practice question. I removed the images and added the table into HTML code format.
下面的代码是未规范化的.
This below code is of Un Normalized formed.
<table>
<thead>
<tr>
<th>ENo </th>
<th>BNo </th>
<th>Branch </th>
<th>Name </th>
<th>Designation </th>
<th>Salary </th>
<th>DeptNo </th>
<th>DeptName </th>
<th>FromDate </th>
<th>ToDate </th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>2</td>
<td>Deer Road</td>
<td>John</td>
<td>Manager</td>
<td>30000</td>
<td>1</td>
<td>HR</td>
<td>05-04-2001</td>
<td>31-12-2006</td>
</tr>
<tr>
<td>1</td>
<td>2</td>
<td>Deer Road</td>
<td>John</td>
<td>Manager</td>
<td>30000</td>
<td>2</td>
<td>Finance</td>
<td>01-01-2007</td>
<td>--</td>
</tr>
<tr>
<td>1</td>
<td>3</td>
<td>Argyll street</td>
<td>Ann</td>
<td>Assistant</td>
<td>8000</td>
<td>1</td>
<td>HR</td>
<td>01-09-2005</td>
<td>--</td>
</tr>
<tr>
<td>2</td>
<td>1</td>
<td>Main Road</td>
<td>David</td>
<td>Supervisor</td>
<td>15000</td>
<td>3</td>
<td>IT</td>
<td>01-07-2002</td>
<td>--</td>
</tr>
</tbody>
</table>我通过简单地添加另一个Employee ID(EId)列将UNF转换为1NF，因为当前这些列不能唯一标识.
I converted UNF to 1NF by simply adding another Employee ID (EId) column because currently columns are not uniquely identifying.
下面的代码为1NF形式:
This below code is of 1NF Form:
.tg {border-collapse:collapse;border-spacing:0;}
.tg td{border-color:black;border-style:solid;border-width:1px;font-family:Arial, sans-serif;font-size:14px;
overflow:hidden;padding:10px 5px;word-break:normal;}
.tg th{border-color:black;border-style:solid;border-width:1px;font-family:Arial, sans-serif;font-size:14px;
font-weight:normal;overflow:hidden;padding:10px 5px;word-break:normal;}
.tg .tg-cly1{text-align:left;vertical-align:middle}
.tg .tg-9qwi{background-color:#fd6864;border-color:inherit;font-weight:bold;text-align:center;vertical-align:middle}
.tg .tg-yla0{font-weight:bold;text-align:left;vertical-align:middle}
.tg .tg-vwbk{background-color:#fd6864;text-align:left;vertical-align:bottom}<table class="tg">
<thead>
<tr>
<th class="tg-9qwi">Eid</th>
<th class="tg-yla0">ENo </th>
<th class="tg-yla0">BNo </th>
<th class="tg-yla0">Branch </th>
<th class="tg-yla0">Name </th>
<th class="tg-yla0">Designation </th>
<th class="tg-yla0">Salary </th>
<th class="tg-yla0">DeptNo </th>
<th class="tg-yla0">DeptName </th>
<th class="tg-yla0">FromDate </th>
<th class="tg-yla0">ToDate </th>
</tr>
</thead>
<tbody>
<tr>
<td class="tg-vwbk">1</td>
<td class="tg-cly1">1</td>
<td class="tg-cly1">2</td>
<td class="tg-cly1">Deer Road</td>
<td class="tg-cly1">John</td>
<td class="tg-cly1">Manager</td>
<td class="tg-cly1">30,000</td>
<td class="tg-cly1">1</td>
<td class="tg-cly1">HR</td>
<td class="tg-cly1">05-04-2001</td>
<td class="tg-cly1">31-12-2006</td>
</tr>
<tr>
<td class="tg-vwbk">1</td>
<td class="tg-cly1">1</td>
<td class="tg-cly1">2</td>
<td class="tg-cly1">Deer Road</td>
<td class="tg-cly1">John</td>
<td class="tg-cly1">Manager</td>
<td class="tg-cly1">30,000</td>
<td class="tg-cly1">2</td>
<td class="tg-cly1">Finance</td>
<td class="tg-cly1">01-01-2007</td>
<td class="tg-cly1">--</td>
</tr>
<tr>
<td class="tg-vwbk">2</td>
<td class="tg-cly1">1</td>
<td class="tg-cly1">3</td>
<td class="tg-cly1">Argyll street</td>
<td class="tg-cly1">Ann</td>
<td class="tg-cly1">Assistant</td>
<td class="tg-cly1">8,000</td>
<td class="tg-cly1">1</td>
<td class="tg-cly1">HR</td>
<td class="tg-cly1">01-09-2005</td>
<td class="tg-cly1">--</td>
</tr>
<tr>
<td class="tg-vwbk">3</td>
<td class="tg-cly1">2</td>
<td class="tg-cly1">1</td>
<td class="tg-cly1">Main Road</td>
<td class="tg-cly1">David</td>
<td class="tg-cly1">Supervisor</td>
<td class="tg-cly1">15,000</td>
<td class="tg-cly1">3</td>
<td class="tg-cly1">IT</td>
<td class="tg-cly1">01-07-2002</td>
<td class="tg-cly1">--</td>
</tr>
</tbody>
</table>通过将表分为三个子表，我从1NF转换为2NF.
I converted from 1NF to 2NF by breaking the table into three subtables.
这是2NF子表代码:
<style type="text/css">
.tg {border-collapse:collapse;border-spacing:0;}
.tg td{border-color:black;border-style:solid;border-width:1px;font-family:Arial, sans-serif;font-size:14px;
overflow:hidden;padding:10px 5px;word-break:normal;}
.tg th{border-color:black;border-style:solid;border-width:1px;font-family:Arial, sans-serif;font-size:14px;
font-weight:normal;overflow:hidden;padding:10px 5px;word-break:normal;}
.tg .tg-9ger{background-color:#fcff2f;text-align:left;vertical-align:bottom}
.tg .tg-ctz4{background-color:#3166ff;text-align:left;vertical-align:bottom}
.tg .tg-u24d{background-color:#fcff2f;text-align:left;vertical-align:middle}
.tg .tg-za14{border-color:inherit;text-align:left;vertical-align:bottom}
.tg .tg-7zrl{text-align:left;vertical-align:bottom}
.tg .tg-yla0{font-weight:bold;text-align:left;vertical-align:middle}
.tg .tg-16v0{background-color:#fcff2f;font-weight:bold;text-align:left;vertical-align:bottom}
.tg .tg-exyj{background-color:#34ff34;font-weight:bold;text-align:left;vertical-align:bottom}
.tg .tg-4aos{background-color:#34ff34;text-align:left;vertical-align:bottom}
.tg .tg-xtan{background-color:#3166ff;font-weight:bold;text-align:left;vertical-align:bottom}
.tg .tg-gl5e{background-color:#fcff2f;font-weight:bold;text-align:left;vertical-align:middle}
</style>
<table class="tg">
<thead>
<tr>
<th class="tg-za14"></th>
<th class="tg-7zrl"></th>
<th class="tg-7zrl"></th>
<th class="tg-7zrl"></th>
<th class="tg-7zrl"></th>
<th class="tg-7zrl"></th>
<th class="tg-yla0">2NF</th>
<th class="tg-7zrl"></th>
<th class="tg-7zrl"></th>
<th class="tg-7zrl"></th>
<th class="tg-7zrl"></th>
<th class="tg-7zrl"></th>
</tr>
</thead>
<tbody>
<tr>
<td class="tg-9ger"></td>
<td class="tg-9ger"></td>
<td class="tg-16v0">Employee Table</td>
<td class="tg-9ger"></td>
<td class="tg-u24d"></td>
<td class="tg-7zrl"></td>
<td class="tg-7zrl"></td>
<td class="tg-exyj">Branch Table</td>
<td class="tg-4aos"></td>
<td class="tg-7zrl"></td>
<td class="tg-xtan" colspan="2">Department Table</td>
</tr>
<tr>
<td class="tg-9ger"></td>
<td class="tg-9ger"></td>
<td class="tg-9ger"></td>
<td class="tg-9ger"></td>
<td class="tg-u24d"></td>
<td class="tg-7zrl"></td>
<td class="tg-7zrl"></td>
<td class="tg-4aos"></td>
<td class="tg-4aos"></td>
<td class="tg-7zrl"></td>
<td class="tg-ctz4"></td>
<td class="tg-ctz4"></td>
</tr>
<tr>
<td class="tg-16v0">EId</td>
<td class="tg-16v0">ENo</td>
<td class="tg-16v0">Name</td>
<td class="tg-gl5e">Designation</td>
<td class="tg-gl5e">Salary</td>
<td class="tg-7zrl"></td>
<td class="tg-7zrl"></td>
<td class="tg-exyj">BNo</td>
<td class="tg-exyj">Branch</td>
<td class="tg-7zrl"></td>
<td class="tg-xtan">DeptNo</td>
<td class="tg-xtan">DeptName</td>
</tr>
<tr>
<td class="tg-9ger">1</td>
<td class="tg-9ger">1</td>
<td class="tg-9ger">John</td>
<td class="tg-u24d">Manager</td>
<td class="tg-9ger">30,000</td>
<td class="tg-7zrl"></td>
<td class="tg-7zrl"></td>
<td class="tg-4aos">1</td>
<td class="tg-4aos">Main Road</td>
<td class="tg-7zrl"></td>
<td class="tg-ctz4">1</td>
<td class="tg-ctz4">HR</td>
</tr>
<tr>
<td class="tg-9ger">2</td>
<td class="tg-9ger">1</td>
<td class="tg-9ger">Ann</td>
<td class="tg-9ger">Assistant</td>
<td class="tg-9ger">8,000</td>
<td class="tg-7zrl"></td>
<td class="tg-7zrl"></td>
<td class="tg-4aos">2</td>
<td class="tg-4aos">Deer Road</td>
<td class="tg-7zrl"></td>
<td class="tg-ctz4">2</td>
<td class="tg-ctz4">Finance</td>
</tr>
<tr>
<td class="tg-9ger">3</td>
<td class="tg-9ger">2</td>
<td class="tg-9ger">David</td>
<td class="tg-9ger">Supervisor</td>
<td class="tg-9ger">15,000</td>
<td class="tg-7zrl"></td>
<td class="tg-7zrl"></td>
<td class="tg-4aos">3</td>
<td class="tg-4aos">Argyll Street</td>
<td class="tg-7zrl"></td>
<td class="tg-ctz4">3</td>
<td class="tg-ctz4">IT</td>
</tr>
</tbody>
</table>在那之后，我停留在如何从日期和日期"列对它进行规范化.
after that, I stuck on how to normalized this from the date and to date column.
我需要一些建议，我必须在哪个表中添加此日期和日期?
I need suggestions with which table I have to add this from and to date column?
推荐答案
规范化永远不会引入新的属性.您介绍了Eid.
Normalization never introduces new attributes. You introduced Eid.
这是一个实践问题，而不是实际的数据库设计.例如，员工在此设计中没有姓氏.那不是真实世界".但这是对练习题的合理简化.
This is a practice question, not a real-world database design. For example, employees don't have last names in this design. That's not "real world". But it's a defensible simplification for a practice question.
在练习题的上下文中，"Eno"表示建议将该列理解为雇员编号".我们通常会考虑员工编号"在这样的表中是唯一的，但约翰和安拥有相同的埃诺(Eno).那为您举起了红旗，那是一件好事.但是在这种情况下，我本来应该先问数据(问您的教授)，因为这对我来说似乎是一个错字.在该实践问题的上下文中，您应考虑名称".可以识别.
In the context of a practice question, "Eno" suggests that column should be understood as "Employee number". We'd usually consider "Employee number" in a table like this to be unique, but John and Ann have the same Eno. That raised a red flag for you, and that's a good thing. But in this context, I would have questioned the data first (ask your professor), because that looks like a typo to me. In the context of this practice question, you should consider "Name" to be identifying.
不能通过将表分成子表"来进行规范化.稍微简化一下，归一化通过基于功能依赖性将表分解成该表的投影来进行.
Normalization doesn't proceed by "breaking a table into subtables". Simplifying a little, normalization proceeds by decomposing a table into projections of that table, based on functional dependencies.
您没有足够的信息来知道如何处理FromDate和ToDate属性，因为您不知道这些列名的含义.这表明"FromDate"不正确.和"ToDate"这些列是不好的名字.使用更具描述性的名称，这样人们就不必猜测.
You don't have enough information to know what to do with the attributes FromDate and ToDate, because you don't know what those column names mean. That suggests that "FromDate" and "ToDate" are bad names for these columns. Use more descriptive names, so people don't have to guess.
发件人日期"和"ToDate"可能与
"FromDate" and "ToDate" might have to do with
- 名称(John从FromDate一直担任ToDate的经理)
- 分支(John从FromDate到Todate被分配到Branch)
- 部门(John从FromDate到Todate被分配到Department)
- 薪水(约翰从FromDate到ToDate提取薪水)
- 以此类推.
这是一个功能依赖项，表明它们是必不可少的.
This is one place functional dependencies show they're essential.
这篇关于如何在标准化中标准化日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
