Create table with both foreign keys and a composite primary key

Question

ProblemStatement:-

Below are my tables-

Employee (EmployeeId, EmployeeName, EmployeeCountry)
Training   (TrainingCode, TrainingName, TrainingType, TrainingTeacher)
Outcome  (EmployeeId, TrainingCode, Grade)

The TrainingType field specifies the training type as, e.g. CASSANDRA, SQL etc.

Below are the tables I have created for the above use case. I am not sure how to create third table - Outcome? Does EmployeeId, TrainingCode together are a primary key here?

CREATE TABLE employee
(
EmployeeId int,
EmployeeName varchar(255),
EmployeeCountry varchar(255),
PRIMARY KEY (EmployeeId)
);


CREATE TABLE Training
(
TrainingCode int,
TrainingName varchar(255),
TrainingType varchar(255),
TrainingTeacher varchar(255),
PRIMARY KEY (TrainingCode)
);

Answer 1

the compound key may be used to create a PK for the outcomes table. You will need to define foreign key relations on the Employee and Training tables.

Ideally you would set the outcome field on the outcome table as a not null field.

CREATE TABLE Outcome
(
   EmployeeId INT REFERENCES Employee,  -- fk to table employee
   TrainingCode INT REFERENCES Training, -- fk to table training
   Outcome VARCHAR(255) not null,
   PRIMARY KEY(EmployeeID, TrainingCode) -- compound PK
);

Answer 2

Put succinctly, yes: the combination of EmployeeID and TrainingCode in the Outcome table are the primary key. Each of those columns is also, individually, a foreign key to one of the other two tables. Hence:

CREATE TABLE Outcome
(
    EmployeeId INT REFERENCES Employee,
    TrainingCode INT REFERENCES Training,
    Outcome VARCHAR(255),
    PRIMARY KEY(EmployeeID, TrainingCode)
);

You may need some other words around for your specific DBMS, but the gist should be clear.

---

Which employees have taken every IT training course or every Business training course?

As discussed in the chat session, you need to use TDQD — Test-Driven Query Design — to build up the answer a step at a time. I'm going to assume that there is at least one IT training course and at least one Business training course. It gets really tricky if you have to ensure that every employee is selected if there are zero IT courses, for example (because if there are zero courses, every employee has taken all of the available IT courses). I am also assuming that the 'or' in the question is an inclusive OR, not an exclusive XOR.

1. Find the number of IT training courses.

   SELECT COUNT(*) AS ITClassesAvailable
     FROM Training
    WHERE TrainingType = 'IT'
   

2. Find the number of Business training courses.

   SELECT COUNT(*) AS BusinessClassesAvailable
     FROM Training
    WHERE TrainingType = 'Business'
   

3. Find the number of IT training courses taken by each employee who has taken at least one IT training course.

   SELECT O.EmployeeID, COUNT(*) AS NumberOfITCoursesTaken
     FROM Outcome AS O
     JOIN Training AS T ON O.TrainingCode = T.TrainingCode
    WHERE T.TrainingType = 'IT'
   

4. Find the number of Business training courses taken by each employee who has taken at least one Business training course.

   SELECT O.EmployeeID, COUNT(*) AS NumberOfBusinessCoursesTaken
     FROM Outcome AS O
     JOIN Training AS T ON O.TrainingCode = T.TrainingCode
    WHERE T.TrainingType = 'Business'
   

5. Find the employees who have taken every IT training course.

   SELECT X.EmployeeID
     FROM (SELECT O.EmployeeID, COUNT(*) AS NumberOfITCoursesTaken
             FROM Outcome AS O
             JOIN Training AS T ON O.TrainingCode = T.TrainingCode
            WHERE T.TrainingType = 'IT'
          ) AS X
     JOIN (SELECT COUNT(*) AS ITClassesAvailable
             FROM Training
            WHERE TrainingType = 'IT'
          ) AS Y
       ON X.NumberOfITCoursesTaken = Y.ITClassesAvailable
   

6. Find the employees who have taken every Business training course.

   SELECT X.EmployeeID
     FROM (SELECT O.EmployeeID, COUNT(*) AS NumberOfBusinessCoursesTaken
             FROM Outcome AS O
             JOIN Training AS T ON O.TrainingCode = T.TrainingCode
            WHERE T.TrainingType = 'Business'
          ) AS X
     JOIN (SELECT COUNT(*) AS BusinessClassesAvailable
             FROM Training
            WHERE TrainingType = 'Business'
          ) AS Y
       ON X.NumberOfBusinessCoursesTaken = Y.BusinessClassesAvailable
   

7. The list of employees who have take either all the IT courses or all the Business courses, therefore, is the UNION of these two queries, or:

   SELECT X.EmployeeID
     FROM (SELECT O.EmployeeID, COUNT(*) AS NumberOfITCoursesTaken
             FROM Outcome AS O
             JOIN Training AS T ON O.TrainingCode = T.TrainingCode
            WHERE T.TrainingType = 'IT'
          ) AS X
     JOIN (SELECT COUNT(*) AS ITClassesAvailable
             FROM Training
            WHERE TrainingType = 'IT'
          ) AS Y
       ON X.NumberOfITCoursesTaken = Y.ITClassesAvailable
   UNION
   SELECT X.EmployeeID
     FROM (SELECT O.EmployeeID, COUNT(*) AS NumberOfBusinessCoursesTaken
             FROM Outcome AS O
             JOIN Training AS T ON O.TrainingCode = T.TrainingCode
            WHERE T.TrainingType = 'Business'
          ) AS X
     JOIN (SELECT COUNT(*) AS BusinessClassesAvailable
             FROM Training
            WHERE TrainingType = 'Business'
          ) AS Y
       ON X.NumberOfBusinessCoursesTaken = Y.BusinessClassesAvailable
   

   The list of employees who have both taken all the IT courses and all the Business courses is the intersection of the two queries (use INTERSECT in place of UNION, or use a join of the two halves of the UNION query).

I don't go out and write a query like that in one step; I wouldn't usually get it right first time. But you can see the steps I've taken, and test each of the component queries, and you can check that they (a) a syntactically correct and (b) produce the correct answer on your test data, and from there you can see how the component queries were combined to make the more complex queries, and the final query. And, if necessary, it is easy to make corrections as you go. That's the benefit of TDQD.