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.
22; 23; 24 2024
© The Independent Institute of Education (Pty) Ltd 2024
Page 6 of 7
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.
Based on the business rules provided, the following Entity Relationship Diagram (ERD) can be created:
```
+---------------------+
| Category |
+---------------------+
| PK categoryId |
| categoryName |
+---------------------+
|
|
|
+---------------------+
| Course |
+---------------------+
| PK courseId |
| name |
| description |
| price |
| listingDate |
| lastUpdatedDate |
| FK categoryId |
+---------------------+
| |
| |
/|\ /|\
/ |
| |
+---------------------+
| Teacher |
+---------------------+
| PK teacherId |
| name |
| surname |
| email |
| bank |
| bankAccount |
| bio |
| website |
+---------------------+
| |
| |
| |
| |
/|\ /|\
/ |
| |
+---------------------+
| Bundle |
+---------------------+
| PK bundleId |
| name |
| startDate |
| endDate |
| price |
+---------------------+
| |
| |
/|\ /|\
/ |
| |
+---------------------+
| Participant |
+---------------------+
| PK participantId |
| name |
| surname |
| email |
| bio |
| website |
+---------------------+
```
In this ERD:
- Category and Course have a one-to-many relationship (one category can have many courses).
- Course and Teacher have a many-to-many relationship, therefore an intersection entity/table should be created to resolve this (not included in the ERD provided).
- Teacher and Bundle, Bundle and Course, Participant and Course have a many-to-many relationship, therefore intersection entities should be added to resolve these (not included in the ERD provided).
- Participant can buy many courses over time, indicating a many-to-many relationship between Participant and Course (resolved through an intersection entity).
- Category and Teacher, Category and Participant have a many-to-many relationship, therefore intersection entities should be added to resolve these (not included in the ERD provided).