You can download the physical ERD here:
Notes on the changes to the database design:
In your logical design, you broke the equipment up into four tables. In Visio, you made a mistake that ended up labeling the equipid on the equipment table as a pK/fk. It is only a PK.
You then, in the other four three tables, have the equipid as the pk/fk. This is not done accurately. If attributes rely solely a primary key, then those attributes should be within the table where that primary key lives. What you have done is create three identifying relationships to simply-key strong entities. Do so does not increase the normal form. It simply increases the number of tables. It does not follow the concepts of a relational database and actually poses a significant efficiency issue to a database if this were the real world.
All of the fields in the three other tables can be consolidated into the equipment table. Since these are not weak entities, there is absolutely no reason to put them in separate tables. You may be thinking that they are in different tables because not all equipment will have an EquipBin. However, that is not the correct way to design such a paradigm. If there are fields that rely solely on the primary key of the equip table (as is the case here), and the equipment may not have that field, then you make the field nullable. You do not put it in a separate table.
With all these fields consolidated into the table, it is still in third normal form, does not violate any rules, and is extremely more efficient. Now, if you wanted to a list of all equipment that has an EquipBin, you could simply do:
<i>SELECT equipment_id FROM Equipment WHERE equipment_bin IS NOT NULL;</i>
That is an extremely fast and efficient query. The way that you had it, you would need to do:
<i>SELECT e.equipment_id FROM Equipment e JOIN Untracked_Equip u ON e.equipment_id = u.equipment_id;</i>
This query is at least 100% less efficient. And, as I have mentioned, it in no way increases or follow the rules of normal form. In a good database design, these extra tables would not exist. The design I am sending you is in at least Third Normal Form (3NF), without creating any arbitrary tables or superfluous identifying relationships.
Next you will notice that I changed some of the design around Employees and Skills. You had it so that an employee could have exactly one skill, yet equipment could be used by many different skill sets. This really does not make any logical sense. A person can have many different skills. In fact, a human being can have more skills than a piece of equipment has. Therefore, I created a weak entity to facilitate a many-to-many relationship between employee and skills.
Finally, I changed some of the logic around your purchase requests and purchase orders. The way you had it, a purchase request and order could be created for a single piece of equipment. If an order needs to be placed for five different types/pieces of equipment, it would need five different purchase orders.
I've dealt with many purchase orders in the real world, and I have never seen one that permits you to input only a single type of equipment. Yes, the database should track each equipment ID. But many different equipment IDs should be permitted within a request/order.
To accomplish this, you will see that there are new tables for request items and purchase order items. This allows individuals to request/order many different equipment within a single request. The actual purchase order may not contain the exact same items or quantity as the purchase, or it may. For instance, an individual may request three wrenches and five hammers. The actual purchase order may be exactly the same, or may be two wrenches and five hammers. It depends on the situation and the quantity on hand.
Finally, I wanted to mention the weak entities. Equipment_skill an EmployeeSkill are both weak entities. A weak entity is one that facilitates a many-to-many relationship. These types of entities have identifying relationships. This means that they have a compound primary key, where the partial keys are foreign keys to the primary keys of the strong entities to this entity is facilitate the many-to-many relationship for.
Identifying relationships are signified by solid relationship lines instead of dashed. You will notice that such relationships only really exist when you are dealing with weak entities. You were actually creating an identifying relationship between equipment and those tables that you didn't need. Creating an identifying relationship between two strong entities (what you were doing) is always indicative of a design issue.
Let me know if you have any questions. I'm here to help.
If you ever need assistance in the future, please feel free to request me directly by starting your questions with **For TheDoctor**.
Please remember to rate my answer. Thank you so much and have a wonderful day!