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.
Firstly, it's great that Thato attempted to create an ERD for the manufacturing company, but there are several improvements that can be made to align the diagram with the business rules and make it more suitable for implementation in a relational database.
1. **Add a Primary Key**: Each table in the ERD should have a primary key to uniquely identify each record. Add PKs to tables A, B, D, and E.
2. **Add a table for Components**: Create a new table for components (Table F) to store information about each component, including its name. This will allow for a proper many-to-many relationship between products and components.
3. **Add a Quantity field**: In the Product_Component table (Table C), include a Quantity field to store the quantity of each component used in a specific product.
4. **Include Product-Supplier relationship**: Add a relationship between products and suppliers (Table G) to indicate which supplier provides components for each product.
5. **Add a table for Suppliers**: Create a Suppliers table (Table H) to store information about each supplier, including their name and address.
6. **Link Suppliers to Components**: Add a relationship between suppliers and components to show which supplier provides each component.
7. **Include a Production Line table**: Create a Production Line table (Table I) to store information about each production line, including its allocated color.
8. **Link Production Line to Products**: Add a relationship between production lines and products to show which product is manufactured on each production line.
9. **Adjust relationships**: Ensure that relationships are properly defined with appropriate cardinality and participation constraints to accurately reflect the business rules.
10. **Add Foreign Key constraints**: Include foreign key constraints in the tables to enforce referential integrity in the database design.
By making these changes, Thato's ERD will better represent the relationships between products, components, suppliers, and production lines in the manufacturing company, and it will be more aligned with the business rules. Additionally, these improvements will make the design more feasible for implementation in a relational database.
Diagram with recommended changes:
```
A. Product
-------------
Product_ID (PK)
Name
B. Component
-------------
Component_ID (PK)
Name
C. Product_Component
--------------------
Product_ID (PK, FK)
Component_ID (PK, FK)
Quantity
D. Supplier
----------------
Supplier_ID (PK)
Name
Address
E. Supplier_Component
--------------------
Supplier_ID (PK, FK)
Component_ID (PK, FK)
F. Component_Supplier
--------------------
Component_ID (PK, FK)
Supplier_ID (PK, FK)
G. Product_Supplier
--------------------
Product_ID (PK, FK)
Supplier_ID (PK, FK)
H. Production_Line
---------------------
Production_Line_ID (PK)
Color
I. Production_Line_Product
---------------------------
Production_Line_ID (PK, FK)
Product_ID (PK, FK)
```