-3

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>

I converted UNF to 1NF by simply adding another Employee ID (EId) column because currently columns are not uniquely identifying.

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>

I converted from 1NF to 2NF by breaking the table into three subtables.

this is 2NF subtables code:

<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?

Saghar Francis
  • 47
  • 5
  • Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Use images only for what cannot be expressed as text or to augment text. Include a legend/key & explanation with an image. – philipxy Jun 08 '20 at 09:36
  • 2
    ["1NF" has no single meaning.](https://stackoverflow.com/a/40640962/3404097) Nor does "unnormalized" or "UNF" or for that matter "relation". So you need to tell us your definitions & preferably also textbook name & edition. "I need suggestions" is not a valid SO question. " I have to add this from and to date column" is not clear. Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. Are you trying to normalize to 3NF? Or what? Explain what you have tried & how you are stuck. [ask] – philipxy Jun 08 '20 at 09:38
  • 2
    Right now you are just asking for us to rewrite your textbook with a bespoke tutorial. Please see [ask], hits googling 'stackexchange homework' & the voting arrow mouseover texts. Show the steps of your work following your textbook with justification & ask 1 specific researched non-duplicate question re the first place you are stuck. Quote the definitions, theorems & algorithms you are relying on. All the steps are also SO faqs. – philipxy Jun 08 '20 at 09:43
  • 2
    You mean you replaced the images by images plus code that generates them. Please act on the comments. – philipxy Jun 09 '20 at 00:02

1 Answers1

1

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.

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.

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.

"FromDate" and "ToDate" might have to do with

  • Designation (John was a manager from FromDate until ToDate),
  • Branch (John was assigned to Branch from FromDate until Todate),
  • Department (John was assigned to Department from FromDate until Todate),
  • Salary (John drew Salary from FromDate until ToDate),
  • and so on.

This is one place functional dependencies show they're essential.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185