How Should the following table be Normalized?

I have a table called Employee with ID,Fname,Lname,dateEmployed,Program, fieldOfStudy,Salary,jobTitle and JobDescription columns. I need to Normalize it but i am not sure how to do that. So how should i normalize it?

This is what i have tried

I created two tables Employee and Job as shown below and referenced the jobId column from Job table in Employee table.

here is Employee Table

CREATE TABLE EMPLOYEE(

    Id int primary key identity,

    FName nvarchar(50),

    LName nvarchar(50),

    dateEmployed Date,

    jobID int foreign key references job(jbId)

);

here is the Job Table

CREATE TABLE Job(

    jobId int primary key identity,

    Title nvarchar(200),

    program nvarchar(30),

    salary float,

    fieldOfStudy nvarchar(50)

    JobDescription nvarchar(max)

);

My Question is just about Normalization not on sql query

asked Jan 20 at 15:10

Qwerty

425

As per your requirement, you will have for 1 job there will be one or more employees. if that is the case, the above structure will work fine.

– sri harsha
Jan 20 at 15:20

add a comment |

CREATE TABLE EMPLOYEE(

    Id int primary key identity,

    FName nvarchar(50),

    LName nvarchar(50),

    dateEmployed Date,

    jobID int foreign key references job(jbId)

);

here is the Job Table

CREATE TABLE Job(

    jobId int primary key identity,

    Title nvarchar(200),

    program nvarchar(30),

    salary float,

    fieldOfStudy nvarchar(50)

    JobDescription nvarchar(max)

);

My Question is just about Normalization not on sql query

asked Jan 20 at 15:10

Qwerty

425

As per your requirement, you will have for 1 job there will be one or more employees. if that is the case, the above structure will work fine.

– sri harsha
Jan 20 at 15:20

add a comment |

CREATE TABLE EMPLOYEE(

    Id int primary key identity,

    FName nvarchar(50),

    LName nvarchar(50),

    dateEmployed Date,

    jobID int foreign key references job(jbId)

);

here is the Job Table

CREATE TABLE Job(

    jobId int primary key identity,

    Title nvarchar(200),

    program nvarchar(30),

    salary float,

    fieldOfStudy nvarchar(50)

    JobDescription nvarchar(max)

);

My Question is just about Normalization not on sql query

asked Jan 20 at 15:10

Qwerty

425

CREATE TABLE EMPLOYEE(

    Id int primary key identity,

    FName nvarchar(50),

    LName nvarchar(50),

    dateEmployed Date,

    jobID int foreign key references job(jbId)

);

here is the Job Table

CREATE TABLE Job(

    jobId int primary key identity,

    Title nvarchar(200),

    program nvarchar(30),

    salary float,

    fieldOfStudy nvarchar(50)

    JobDescription nvarchar(max)

);

My Question is just about Normalization not on sql query

sql database

asked Jan 20 at 15:10

Qwerty

425

asked Jan 20 at 15:10

Qwerty

425

asked Jan 20 at 15:10

Qwerty

425

asked Jan 20 at 15:10

Qwerty

425

asked Jan 20 at 15:10

Qwerty

425

As per your requirement, you will have for 1 job there will be one or more employees. if that is the case, the above structure will work fine.

– sri harsha
Jan 20 at 15:20

add a comment |

As per your requirement, you will have for 1 job there will be one or more employees. if that is the case, the above structure will work fine.

– sri harsha
Jan 20 at 15:20

As per your requirement, you will have for 1 job there will be one or more employees. if that is the case, the above structure will work fine.

– sri harsha
Jan 20 at 15:20

add a comment |

1 Answer
1

active

oldest

votes

Your question actually breaks down to : what is the relationship between jobs and employees (which you did not thoroughly explained). Possible cases are :

1-N : if each employee has one job and several employees can have the same job, the your setup with two tables and a foreign key in employee towards job is fine

1-1 : if each employee has one job, and each job belongs to a single employee, then you do not need two tables : you can just stuff everything in the employee table

N-N : if an employee can have several jobs, and each job can belong to several employees, then you would need to create a third table, a bridge table to represent that relationship, where each row would store one foreign key towards the jobs table and another towards the employees table

edited Jan 20 at 15:44

answered Jan 20 at 15:37

GMB

12.5k2824

i didn't explain the relationships because i have been given just the sql table and asked to normalize it.

– Qwerty
Jan 20 at 16:08

@Qwerty : ok, then you should ask for information about the relationship first, and then accordingly pick the relevant design, based on the decision tree I provided you with.

– GMB
Jan 20 at 16:12

ok thank you...

– Qwerty
Jan 20 at 16:22

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54277799%2fhow-should-the-following-table-be-normalized%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

Your question actually breaks down to : what is the relationship between jobs and employees (which you did not thoroughly explained). Possible cases are :

1-N : if each employee has one job and several employees can have the same job, the your setup with two tables and a foreign key in employee towards job is fine

1-1 : if each employee has one job, and each job belongs to a single employee, then you do not need two tables : you can just stuff everything in the employee table

N-N : if an employee can have several jobs, and each job can belong to several employees, then you would need to create a third table, a bridge table to represent that relationship, where each row would store one foreign key towards the jobs table and another towards the employees table

edited Jan 20 at 15:44

answered Jan 20 at 15:37

GMB

12.5k2824

i didn't explain the relationships because i have been given just the sql table and asked to normalize it.

– Qwerty
Jan 20 at 16:08

@Qwerty : ok, then you should ask for information about the relationship first, and then accordingly pick the relevant design, based on the decision tree I provided you with.

– GMB
Jan 20 at 16:12

ok thank you...

– Qwerty
Jan 20 at 16:22

add a comment |

Your question actually breaks down to : what is the relationship between jobs and employees (which you did not thoroughly explained). Possible cases are :

1-N : if each employee has one job and several employees can have the same job, the your setup with two tables and a foreign key in employee towards job is fine

1-1 : if each employee has one job, and each job belongs to a single employee, then you do not need two tables : you can just stuff everything in the employee table

N-N : if an employee can have several jobs, and each job can belong to several employees, then you would need to create a third table, a bridge table to represent that relationship, where each row would store one foreign key towards the jobs table and another towards the employees table

edited Jan 20 at 15:44

answered Jan 20 at 15:37

GMB

12.5k2824

i didn't explain the relationships because i have been given just the sql table and asked to normalize it.

– Qwerty
Jan 20 at 16:08

@Qwerty : ok, then you should ask for information about the relationship first, and then accordingly pick the relevant design, based on the decision tree I provided you with.

– GMB
Jan 20 at 16:12

ok thank you...

– Qwerty
Jan 20 at 16:22

add a comment |

Your question actually breaks down to : what is the relationship between jobs and employees (which you did not thoroughly explained). Possible cases are :

1-N : if each employee has one job and several employees can have the same job, the your setup with two tables and a foreign key in employee towards job is fine

1-1 : if each employee has one job, and each job belongs to a single employee, then you do not need two tables : you can just stuff everything in the employee table

N-N : if an employee can have several jobs, and each job can belong to several employees, then you would need to create a third table, a bridge table to represent that relationship, where each row would store one foreign key towards the jobs table and another towards the employees table

edited Jan 20 at 15:44

answered Jan 20 at 15:37

GMB

12.5k2824

Your question actually breaks down to : what is the relationship between jobs and employees (which you did not thoroughly explained). Possible cases are :

1-N : if each employee has one job and several employees can have the same job, the your setup with two tables and a foreign key in employee towards job is fine

1-1 : if each employee has one job, and each job belongs to a single employee, then you do not need two tables : you can just stuff everything in the employee table

N-N : if an employee can have several jobs, and each job can belong to several employees, then you would need to create a third table, a bridge table to represent that relationship, where each row would store one foreign key towards the jobs table and another towards the employees table

edited Jan 20 at 15:44

answered Jan 20 at 15:37

GMB

12.5k2824

edited Jan 20 at 15:44

answered Jan 20 at 15:37

GMB

12.5k2824

answered Jan 20 at 15:37

GMB

12.5k2824

answered Jan 20 at 15:37

GMB

12.5k2824

i didn't explain the relationships because i have been given just the sql table and asked to normalize it.

– Qwerty
Jan 20 at 16:08

@Qwerty : ok, then you should ask for information about the relationship first, and then accordingly pick the relevant design, based on the decision tree I provided you with.

– GMB
Jan 20 at 16:12

ok thank you...

– Qwerty
Jan 20 at 16:22

add a comment |

i didn't explain the relationships because i have been given just the sql table and asked to normalize it.

– Qwerty
Jan 20 at 16:08

@Qwerty : ok, then you should ask for information about the relationship first, and then accordingly pick the relevant design, based on the decision tree I provided you with.

– GMB
Jan 20 at 16:12

ok thank you...

– Qwerty
Jan 20 at 16:22

i didn't explain the relationships because i have been given just the sql table and asked to normalize it.

– Qwerty
Jan 20 at 16:08

@Qwerty : ok, then you should ask for information about the relationship first, and then accordingly pick the relevant design, based on the decision tree I provided you with.

– GMB
Jan 20 at 16:12

ok thank you...

– Qwerty
Jan 20 at 16:22

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Brtdku