I'm trying to set up 3NF tables and insert data, but keep getting 'UNIQUE constraint failed' error. Maybe I've done the 1NF-3NF conversion incorrectly, or incorrectly identified foreign/primary keys.

Insert method:

INSERT INTO Employee VALUES ('16', 'Smith'), ('33', 'Smith'),('30', 'Jenny'), ('16', 'Smith')

What I hope Employee table to look like (16-SMITH is supposed to duplicate):

My tables:

CREATE TABLE "Employee" (
    "EMP_ID"    INTEGER,
    "EMP_FNAME"    TEXT,
    PRIMARY KEY("EMP_ID")
);

CREATE TABLE "Project" (
    "PROJ_ID"    INTEGER,
    "JOB_TYPE"    INTEGER,
    "EMP_ID"    INTEGER,
    PRIMARY KEY("PROJ_ID","EMP_ID")
);

CREATE TABLE "HOURLY PAY" (
    "JOB_TYPE"    INTEGER,
    "HOUR_RATE"    INTEGER,
    PRIMARY KEY("JOB_TYPE"),
    FOREIGN KEY("JOB_TYPE") REFERENCES "Project"("JOB_TYPE")
);

Table I was provided with that I had to convert from 1NF->3NF:

CodePudding user response：

INSERT INTO Employee VALUES ('16', 'Smith'), ('33', 'Smith'),('30', 'Jenny'), ('16', 'Smith')

You have two Smith, and both of their ids are 16.

CodePudding user response：

Since EMP_ID is the primary key. It must be unique. The primary key allows you to uniquely identify one record. Assuming that you could insert the Id 16 twice and later wanted to delete one of these two records, how would you accomplish it? DELETE FROM Employee WHERE EMP_ID = 16 would delete both of them.

This is the very point of normalization. Every entity must be unique and information must not be repeated.

You need one more table I think. You need a Project table with a unique Project ID and a project name. Then you need a junction table between Project and Employee. We could call it "Assignment". It must have the structure of the table you are calling "Project" now.

CREATE TABLE Employee (
    EMP_ID     INTEGER,
    EMP_FNAME  TEXT,
    PRIMARY KEY (EMP_ID)
);

CREATE TABLE Project (
    PROJ_ID      INTEGER,
    PROJ_NAME    TEXT,
    PRIMARY KEY (PROJ_ID)
);

CREATE TABLE JobType (
    JOB_TYPE     TEXT,
    HOUR_RATE    INTEGER,
    PRIMARY KEY (JOB_TYPE)
);

CREATE TABLE Assignment (
    PROJ_ID      INTEGER,
    EMP_ID       INTEGER,
    JOB_TYPE     TEXT,
    PRIMARY KEY (PROJ_ID, EMP_ID),
    FOREIGN KEY (PROJ_ID) REFERENCES Project (PROJ_ID),
    FOREIGN KEY (EMP_ID) REFERENCES Employee (EMP_ID),
    FOREIGN KEY (JOB_TYPE) REFERENCES JobType (JOB_TYPE)
);

Employees, Projects and JobTypes are unique. In "Assignment" Employee/Project pairings are unique.

INSERT INTO Employee (EMP_ID, EMP_FNAME) VALUES
  (16, 'Smith'),
  (30, 'Jenny'),
  (33, 'Smith');

INSERT INTO Project (PROJ_ID, PROJ_NAME) VALUES
  (1135, 'Zulu'),
  (1188, 'Voyager');

INSERT INTO JobType (JOB_TYPE, HOUR_RATE) VALUES
  ('H25', 20),
  ('H26', 30),
  ('H27', 20);

INSERT INTO Assignment (PROJ_ID, EMP_ID, JOB_TYPE) VALUES
  (1135, 16, 'H25'),   <== Smith 16
  (1135, 33, 'H27'),
  (1188, 30, 'H26'),
  (1188, 16, 'H25');   <== Smith 16 again, but assigned to a different project.

One would have to consider whether it would not be better to assign a neutral numeric primary key to the job type. The job code (like 'H25') would be a separate column. Meaningful primary keys can be problematic. If someone wants to introduce another naming scheme for the job types, this is difficult to realize when the name is the primary key.

You can then re-create the information as contained in the original table with a query

SELECT
   a.PROJ_ID,
   p.PROJ_NAME,
   a.EMP_ID,
   e.EMP_FNAME,
   a.JOB_TYPE,
   j.HOUR_RATE
FROM
    Assignemnt a
    INNER JOIN Employee e ON a.EMP_ID = e.EMP_ID
    INNER JOIN Project p ON a.PROJ_ID = p.PROJ_ID
    INNER JOIN JobType j ON a.JOB_TYPE = j.JOB_TYPE
ORDER BY
    a.PROJ_ID,
    e.EMP_FNAME

See: http://sqlfiddle.com/#!5/3dcdb/3/0