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.
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
I'm sorry, but I am currently unable to draw diagrams. However, I can describe how the Entity Relationship Diagram (ERD) for the given business rules can be designed.
Entities:
- Teacher (teacher_id [PK], name, surname, email, bank, bank account, bio, website)
- Category (category_id [PK], name)
- Course (course_id [PK], name, description, price, listing date, last updated date, category_id [FK])
- Bundle (bundle_id [PK], name, start date, end date, price)
- Participant (participant_id [PK], name, surname, email, bio, website)
Relationships with names:
- Teacher creates Course
- Course belongs to Category
- Course can have Discount
- Course can be part of Bundle
- Participant buys Course
- Participant is interested in Category
- Teacher specializes in Category
Multiplicities:
- Each Teacher can create many Courses (1-to-many)
- Each Course belongs to exactly one Category (many-to-1)
- Each Course can have many Discounts (1-to-many)
- Each Course can be part of many Bundles (1-to-many)
- Each Participant can buy many Courses (1-to-many)
- Each Participant can be interested in many Categories (1-to-many)
- Each Teacher can specialize in many Categories (1-to-many)
Primary keys:
- teacher_id (Teacher)
- category_id (Category)
- course_id (Course)
- bundle_id (Bundle)
- participant_id (Participant)
Foreign keys:
- category_id (Course referencing Category)
- teacher_id (Course referencing Teacher)
- participant_id (Buying referencing Participant)
- category_id (Interest referencing Category)
- category_id (Specialization referencing Category)
Other attributes:
- Percentage contribution of teacher to a specific course (Teacher-Course relationship)
- Percentage contribution of course to a specific bundle (Course-Bundle relationship)
- From date, to date, price during discount (Discount)
- Date of sign up for course (Participant-Course relationship)
Please note that this description will need to be converted into a visual ERD using UML notation.