= Relational Design for OPLMS=

== Notation ==

* Primary keys are bolded and underlined.
* Foreign keys are marked with `*` at the end of their name and the referenced entity is written in parentheses.
* Complex attributes are bolded, and their containing attributes are following the, made italic.
* Multivalued attributes have their own table

== Tables ==

1. **USER**(__'''User_ID'''__, '''First_Name''', '''Last_Name''', '''Email''', '''Password''')

2. **Instructor **(__'''Instructor_ID'''__, '''Bio''', '''Expertise''')

3. **Administrator **(__'''Admin_ID'''__, '''Admin_Level''')   

4. **Category **(__'''Category_ID'''__, '''Name''', '''Description''')

5. **Course **(__'''Course_ID'''__, '''Name''', '''Price''', '''Status''', __Category_ID__*(Category), __Instructor_ID__*(Instructor))

6. **Module **(__'''Module_id'''__, '''Title''', '''Description''', __Course_ID*(Course))

7. **Lesson **(__'''lesson_ID'''__, '''TItle''', '''Material''', __Module_id*(Module))

8. **Quiz **(__'''Quiz_ID'''__, '''Total_points''', '''Passing_score''', __lesson_ID*(Lesson))

9. **Enrollment **(__'''Enrollment_ID'''__, '''Enroll_date''', '''Completion_Status''', '''Progress_percentage''',__User_id*(User),__Course_ID*(Course))

10. **Certificate **(__'''Certificate_ID'''__, '''Issue_date''', '''Certificate_code''', '''Status''', __Enrollment_id*(Enrollment))

11. **SupportTicket **(__'''Ticket_ID'''__, '''Subject, Description''', '''Status''', '''Created_at''', __User_ID*(User), __Admin_ID*(Administrator))

12. **SubscriptionPlan **(__'''Plan_ID'''__, '''Name''', '''Price''', '''Duration_months''', '''Description''', '''Access_type'''

13. **UserSubscription**(__'''Subscription_ID'''__, '''Start_date''', '''End_date''', '''Status''', __User_ID*(User), __Plan_ID**(SubscriptionPlan))

14. **Payment **(__'''Payment_ID'''__, '''Amount''', __User_ID*(User), __Subscription_ID**(UserSubscription))

15. **QuizAttempt **(__'''Аttempt_ID'''__, '''Attempt_date''', '''Score''',__User_id*(User), __Quiz_ID*(Quiz))

=== DDL script for creating and dropping database tables ===
'''[attachment:OPLMS-create-DDL.sql​]'''


=== DML script for inserting data into database tables ===
'''[attachment:OPLMS--DML.sql]'''

=== Relational database diagram generated using DBeaver ===
[[Image("OPLMS.png",80%)]]