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 3 – Entity Relationship Diagrams (Marks: 50)
Thato has given what data he needs for the course marketplace website a lot of thought. He has
come up with quite a comprehensive set of business rules.
Course Marketplace Business Rules
1. Each course can belong to exactly one category, and many courses can belong to the
same category.
2. The name of each category must be stored in the database.
3. The name, description, price, listing date and last updated date must be stored in the
database for each course.
4. A teacher can create many courses, and each course can be created by more than one
teacher.
5. The percentage that each teacher contributes to a specific course must be stored in
the database.
6. The following must be stored for each teacher: name, surname, email, bank, bank
account, bio, and website.
7. Each teacher can indicate that they specialise in multiple categories. And many
teachers can specialise in the same category.
8. A discount can be applied to a specific course. The same course can be discounted
many different times or never.
9. The from date and to date, as well as the price during the discount, needs to be
stored in the database.
10. Sometimes courses are sold as part of a bundle. Many courses can be part of the
same bundle, and the same course can be part of many different bundles.
11. The percentage contribution of each course to a specific bundle needs to be stored
in the database.
12. The name, start date, end date and price of each bundle must be stored in the
database.
13. A participant can (hopefully will!) buy many courses over time. And many participants
can buy the same course.
14. The date that a participant signs up for a course must be stored in the database.
15. If a discount is active when a course is bought, the fact that a discount was applied
to the purchase needs to be recorded in the database.
16. The following data must be stored for each participant: name, surname, email, bio,
and website.
17. Each participant can indicate that they are interested in multiple categories, and
many participants can be interested in the same category.
Draw an Entity Relationship Diagram (ERD) using Unified Modelling Language (UML) notation,
representing these business rules. Your design should be at the logical level – include surrogate
primary key and foreign key fields. Remember to remove any many-to-many relationships.
You only need to submit the final, complete diagram.
Tip: Make sure the diagram is readable in the document you submit!
Marks will be allocated as follows:
Entities 11 marks
Relationships with names 13 marks
Multiplicities 13 marks
Primary keys 5 marks
Foreign keys 5 marks
Other attributes 2 marks
Correct UML notation 1 mark
Here is the Entity Relationship Diagram (ERD) representing the business rules provided by Thato for the course marketplace website:
Entities:
- Category
- Course
- Teacher
- Bundle
- Participant
Relationships with names:
- Course belongs to Category
- Course is created by Teacher
- Teacher specializes in Category
- Course can be part of Bundle
- Participant buys Course
Multiplicities:
- Course (1) belongs to Category (1)
- Course (M) is created by Teacher (M)
- Teacher (M) specializes in Category (M)
- Course (M) can be part of Bundle (M)
- Participant (M) buys Course (M)
Primary keys:
- CategoryID (in Category entity)
- CourseID (in Course entity)
- TeacherID (in Teacher entity)
- BundleID (in Bundle entity)
- ParticipantID (in Participant entity)
Foreign keys:
- CategoryID (in Course entity)
- TeacherID (in Course entity)
- TeacherID (in Teacher entity)
- CategoryID (in Teacher entity)
- CourseID (in Bundle entity)
- CourseID (in Participant entity)
Other attributes:
- Name, Description, Price, Listing Date, Last Updated Date (in Course entity)
- Percentage Contribution (in Teacher entity and Bundle entity)
- Name, Surname, Email, Bank, Bank Account, Bio, Website (in Teacher entity)
- From Date, To Date (in Bundle entity)
- Name, Start Date, End Date, Price (in Bundle entity)
- Date Signed Up, Discount Applied (in Participant entity)
- Name, Surname, Email, Bio, Website (in Participant entity)