Background
Thato has always been interested in learning different things about the world. Besides his studies
at college to become an environmental lawyer, he has done various short courses on multiple
platforms. He started with a massive open online course (MOOC) on astronomy presented by a
well-known university in the United States of America. He decided not to opt for the official
certificate, so the study did not cost anything. However, it was still quite academic and not what
he hoped to do for fun. So, his search continued.
Then he found an international course marketplace, where the instructors posted their courses
online, and anybody could enrol to watch the video material at any time. He has since learned
how to paint watercolour flowers from an artist in Spain. Additionally, he learned how to repair
broken ceramics from a wonderfully creative person in Japan and even how to create animated
characters from somebody in Italy. These online courses cost him less than a good dinner since he
patiently awaited special offers.
After a while, he started wondering what he could learn from South African people. We have such
talented creative people. Indeed, there must be great content out there. However, he could not
find what he was looking for. Plenty of serious, academic short courses are usually available, with
equally high prices too. There just was not a space for creatives to post their material.
Thato wanted to fill this gap with our South African course marketplace website.
Question 4 – Entity Relationship Diagram Review (Marks: 20)
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
In order to align the ERD with the business rules and make it possible to implement the design in a relational database, the following 10 changes are recommended:
1. Add a table for Products: Create a table for products to store the name of each product. This will allow for the association of products with components and suppliers.
2. Add a table for Components: Create a table for components to store the name of each component. This will allow for the association of components with products and suppliers.
3. Add a table for Suppliers: Create a table for suppliers to store the name and address of each supplier. This will allow for the association of suppliers with components.
4. Add a table for Production Lines: Create a table for production lines to store the information about each production line. This will allow for the association of production lines with products and colors.
5. Include a Quantity field in the Component table: Add a field in the Component table to store the quantity of each component used for a specific product. This will allow for tracking the quantity of components used in the manufacturing process.
6. Add a component_product junction table: Create a junction table to manage the many-to-many relationship between components and products. This table will store the quantity of each component used for a specific product.
7. Add a component_supplier junction table: Create a junction table to manage the many-to-many relationship between components and suppliers. This table will associate components with suppliers and store additional information about the relationship.
8. Add a product_productionline junction table: Create a junction table to manage the one-to-many relationship between products and production lines. This table will assign each production line to manufacture exactly one type of product at a time.
9. Include a Color field in the Production Lines table: Add a field in the Production Lines table to store the color allocated to each production line. This will allow for easy distinction between production lines.
10. Ensure proper normalization: Normalize the tables to eliminate redundancy and improve data integrity. This will help in managing the relationships between entities and ensure efficient database operations.
By making these changes, Thato's ERD will be improved to align with the business rules and make it possible to implement the design in a relational database. These changes will also help in maintaining data consistency and integrity within the database.