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.
1. Change: Add a many-to-many relationship between Product (A) and Component (B) with a junction table (C) to store the quantity of each component used for a specific product.
Reason: This change is necessary to accurately represent the relationship between products and components as each product can have multiple components and each component can be used in multiple products.
2. Change: Add a Primary Key attribute to the Product (A) entity.
Reason: Every entity in a database must have a Primary Key attribute to uniquely identify each record. This is essential for data integrity and efficient querying of the database.
3. Change: Add a Primary Key attribute to the Component (B) entity.
Reason: Similar to the Product entity, the Component entity also needs a Primary Key attribute for uniqueness and data integrity.
4. Change: Add a many-to-many relationship between Component (B) and Supplier (D) with a junction table (E) to represent the supplier-component relationship.
Reason: This change is necessary to accurately represent the relationship between components and suppliers, as each supplier can supply many different components and each component can be supplied by multiple suppliers.
5. Change: Add attributes for name and address to the Supplier (D) entity.
Reason: The business rule specifies that the name and address of each supplier must be stored in the database. Therefore, these attributes must be included in the Supplier entity.
6. Change: Add a Primary Key attribute to the Supplier (D) entity.
Reason: Just like the other entities, the Supplier entity must have a Primary Key attribute for uniqueness and data integrity.
7. Change: Add a Production Line (F) entity with a Primary Key attribute.
Reason: The business rule states that each production line manufactures exactly one type of product at a time, so it is essential to include a Production Line entity to represent this relationship.
8. Change: Add an attribute for colour to the Production Line (F) entity.
Reason: The business rule specifies that a colour is allocated to each production line for easy distinction. Therefore, a colour attribute must be included in the Production Line entity.
9. Change: Add a one-to-many relationship between Production Line (F) and Product (A) to represent the fact that each production line manufactures one type of product at a time.
Reason: This relationship is needed to enforce the rule that each production line is dedicated to manufacturing a single type of product at a time.
10. Change: Ensure all relationships are clearly labelled with cardinality and optionality to indicate the nature of the relationships between entities.
Reason: Clear labelling of relationships is crucial for understanding the data model and ensuring that it accurately reflects the business rules. This information is essential for database design and implementation in a relational database system.