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

OPLMS-create-DDL.sql​

DML script for inserting data into database tables

OPLMS--DML.sql​

Relational database diagram generated using DBeaver