Thato, being ever curious and wanting to learn more about anything and everything, decided to
try his hand at creating a UML ERD for a relational database too. You made it look so easy after all,
so he was confident he could do it too. So, he wrote some new rules about a fictional
manufacturing company and created the below ERD based on some web pages he consulted.
Each product requires different components during the manufacturing process. A
component can be used in multiple different products.
The quantity of each component that is used for a specific product must be stored in
the database.
The name of each product and each component must be stored in the database.
Each component is bought from a specific supplier, and a supplier can supply many
different components.
The name and address of each supplier must be stored in the database.
Each production line manufactures exactly one type of product at a time.
A colour is allocated to each production line to make them easy to distinguish.
Comment on his diagram, explaining which 10 changes you recommend to improve the diagram
to align with the business rules. Explain why you would make each change too, so he can learn
from his mistakes. Also, include improvements that would make it possible to implement the
design in a relational database.
Hint: Include the diagram and add letters to the diagram that you can refer to in your comments..Also explain in how the new ERD is improved
Here are 10 changes that I recommend to improve the ERD to better align with the business rules and make it possible to implement in a relational database:
1. Change A: Add a "Products" entity that stores the name of each product.
- This change will ensure that the names of products are stored in the database as per the business rule.
2. Change B: Add a "Components" entity that stores the name of each component.
- This change will ensure that the names of components are stored in the database as per the business rule.
3. Change C: Add a "ProductComponents" entity that keeps track of the quantity of each component used for a specific product.
- This change will store the quantity of components used for each product, as required by the business rules.
4. Change D: Connect the "Suppliers" entity to the "Components" entity with a many-to-many relationship.
- This change will indicate that a supplier can supply many different components, as per the business rule.
5. Change E: Add attributes for "Name" and "Address" to the "Suppliers" entity.
- This change will store the name and address of each supplier in the database as per the business rule.
6. Change F: Add a "ProductionLines" entity that represents each production line in the manufacturing company.
- This change will ensure that each production line is represented in the database, as required by the business rules.
7. Change G: Add a "ProductsManufactured" attribute to the "ProductionLines" entity to specify the type of product being manufactured.
- This change will ensure that each production line manufactures exactly one type of product at a time, as per the business rule.
8. Change H: Add a "Color" attribute to the "ProductionLines" entity to differentiate between production lines.
- This change will allocate a color to each production line, making them easy to distinguish as per the business rule.
9. Change I: Ensure proper relationships (such as one-to-many or many-to-many) between the entities to represent the connections accurately.
- This change will ensure data integrity and consistency in the database design.
10. Change J: Add primary keys to each entity to uniquely identify each record in the database.
- This change will establish a primary key for each entity, allowing for efficient querying and data retrieval.
By implementing these changes, the ERD will better reflect the business rules and be structured in a way that is optimized for a relational database implementation. This will provide a solid foundation for storing and managing data effectively for the fictional manufacturing company.