Normalizing redundant data (is this 1NF, 2NF or 3NF)
-
In the example below, columns 2 - 4 are dependent on the EmpId column. Columns 6 and 7 are dependent on the DeptName. So we have two types of data in this table. We have employee details and department details. The department details should be grouped out into a separate table, then create a primary key called DeptID for that table. In the table below, we would then insert a column for DeptID which would be a foreign key in the table below.
My questions are:
1. Regarding the act of breaking out the department details what is this called?
a. Removing repeating groups
b. Partial dependency
c. Transitive dependency
d. Something else2. When we breakout the department details into another table, is that done in 1NF, 2NF or 3NF?
- michael.leach2015 - Wednesday, February 6, 2019 12:04 PM
1. It's called normalization.
2. It depends on your application, but the goal is typically to achieve 3NF.Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
Sounds like some sort of test question for you. I think there is an answer for both 1 and 2, but what do you know or what have you done?
Not a fan of doing homework for you.
- Steve Jones - SSC Editor - Thursday, February 7, 2019 10:07 AM
Hi Steve,
That's very flattering of you to say. As it turns out, it's not a test question. I just believe that framing my questions like a test question makes it considerably easier for me to convey what I am asking. By providing multiple choices for an answer, I am hoping that somebody may give me the correct answer, instead of providing an answer that doesn't really answer my question. The idea is that people would answer by selecting one of my choices, and even though I have taken the time to clearly layout my question, many people still don't answer to the question. It's amazing.
Furthermore, I am studying SQL in pursuit of taking some certification exams, so this is another reason why I am trying to make absolutely sure I know this stuff. I am trying to anticipate what I might be asked on a test, so that if I am asked these questions, I will be able to answer them correctly.
Again, very flattering. Thank you.
-
In that case, what do you think this means for normalization? If I remove the department groups, what effect does that have on:
a) no repeating groups and atomic values
b) no partial dependencies
c) no transitive dependenciesI have my thoughts, though I'm not completely confident I am right here, but I'll share if you do.
-
You're trying to normalize your schema. The heuristic is to first look for entities. You have two of them based on your data; personnel (not employees! Use a collective noun for the name of a set) and departments (there's no good collective noun for this so use a plural).
Next, remember that the R in RDBMS stands for relations. What is the relationship between these entities? I'd use "job_assignments" and create a third table that references the primary keys of the tables involved in the relationship (employee_name, department_name).
Now I have to decide what kind of relationship I have. Is it one to one? One to many? Many to many? Can I have an employee that is not assigned to a department? Can I have an employee is assigned to multiple departments? This means I will need to do this in the DDL, which you fail to post.
When I try to split out the departments as their own table, you have screwed up! The department head is probably an employee. And not modeled as an attribute.
The relationship table can also have properties of its own. For example, a marriage is clearly relationship which has a presiding official, a license number, a date location, whether it was civil or religious, etc.
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply