Mysql – Relationship issue

It looks like you've used the option to create an Identifying relationship (this is the one with the unbroken line in the toolbox).

An Identifying relationship assumes that the primary key in the referenced table should be part of primary key of the referencing table.

Because Company_id is now part of Department's Primary Key, it must also be part of the foreign key of Employee, hence your problem #1.

What you're probably wanting to do is create a Non-identifying relationship. This can be done by either selecting the dashed line from the toolbox, or unticking "Identifying relationship" in the Foreign Key tab of the Relationship properties.

A Non-identifying relationship is a classic foreign key constraint, and simply ensures that any value in the referencing table exists in the referenced table.

Technically this should resolve your problems, but there are still potential issues with the underlying database design.

For example, there's nothing to stop multiple companies existing with the same name, or multiple departments with the same name, even within the same company.

This could be solved by adding unique key constraints, but another way to tackle the same problem would be to use the Company and Department names as primary keys.

If you were doing this, you'd actually want to use an Identifying relationship, so that the Company name becomes an integral part of the Department.

(You may wish to read up on database theory and database normalisation, as it will help you avoid a lot of traps that you're likely to come up against when building a database)

I'm not suggesting that this is a better database design, simply that this is one where an Identifying relationship is valid / useful.

For the simple case shown, with a business rule something like "Each company can be contacted at zero to many email addresses; each email address directs to exactly one company" it would be more usual to have the company id as a foreign key in the email table. What you have shown will work, but there will be a one-to-one relationship between Email and CompanyEmail, which is redundant.

For your linked question, where you actually have a Company, a Branch and an Employee, all of which can have many email addresses and any one email address could relate to any or all of Company, Branch and Employee, things are slightly different. In the logical data model I would be inclined to show Email and the intersection entity types explicitly to document the rules fully. In the physical table design, however, I would consider omiting the Email table. This is because its only columns are e_addr and e_id, so e_id is really just a synonym for e_addr and e_addr can replace e_id in the intersection tables CustomerEmail, BranchEmail and EmployeeEmail. You can still answer all the questions about one email address's re-use but won't have the actual table to maintain. It would mean an email address disappeared from your system when it stopped being used. That's probably OK in practice.