Hello everyone,



I have to do a homework for my business informatics course at university.
We have to construct an ER-Model containing following information: A company pays their employees a salary depending on collective agreement and salary level (containing the level, gross salary per month and the validation).
The company also pays bonuses to some employees (containing bonus level, amount and validation). The bonus is based on the performance of the employees.

The first question is: what is the connection between those two things (salary and bonus). I thought about the collective agreement, therefore I've made a model with two entities:
1) Salary (attributes: salary level, collective agreement, gross salary per month and validation)
2) Bonus (attributs: bonus level, collective agreement, amount and validation)

The problem is now that I have to define two primary keys per table (collective agreement & salary level such as collective agreement & bonus level)...
I've used the following codes to create those tables:

CREATE TABLE salary (
salary level VARCHAR,
collective agreement VARCHAR,
gross salary per month CURRENCY,
validation DATE,
PRIMARY KEY (salary level, collective agreement));

CREATE TABLe bonus (
bonus level VARCHAR,
collective agreement VARCHAR,
amount CURRENCY,
validation DATE,
salary level VARCHAR,
PRIMARY KEY (bonus level, collective agreement),
FOREIGN KEY (salary level, collective agreement) REFERENCES salary (salary level, collective agreement));

The codes work.. BUT then I have the salary level also in my table of the bonus and that's the problem. Because I can't define a salary level for each bonus.
The bonus depends on bonus level and collective agreement, but not on salary level.