EP.1: SQL Database Modeling

Objective

A well-structured Entity-Relationship Diagram (ERD) is essential for designing an efficient database system. In this document, we explore how to model a School System using ERD principles. The goal is to develop a relational schema that supports key functionalities such as student enrollment, course management, assessments, and payment tracking.

By analyzing the system’s requirements, we identify entities and their relationships, then translate them into a PlantUML-based ERD. This approach provides a clear, visual representation of the database structure, making it easier for developers, data analysts, and system architects to understand and implement.

Entities:

To design an effective Entity-Relationship Diagram (ERD) for a School System, we first need to identify the key entities and their roles within the system. The entities derived from the system’s requirements include:

• Students – Individuals enrolled in courses.
• Instructors – Teachers who conduct courses.
• Courses – Learning programs that students enroll in.
• Modules – Sections within a course that help structure learning.
• Assessments – Evaluations assigned within modules to test student understanding.
• Submissions – Student responses to assessments.
• Subscriptions – Student access to courses through paid plans.
• Reviews – Feedback given by students on courses.
• PaymentRecords – Records of student payments.
• Enrollment – Tracks which students have registered for which courses.

The Product Manager’s Queries focus on:

• Enrollment statistics.
• Course performance (ratings, completions).
• Revenue tracking.
• Student activity and engagement.
• Instructor performance.

To answer these queries, our database design must support efficient querying and reporting of the above information.

Step 2: Identifying Relationships

Now that we understand the key entities, we identify how they interact:

1. Students ↔ Enrollment ↔ Courses (Many-to-Many) – A student can enroll in multiple courses, and each course can have many students.
2. Students ↔ Subscriptions ↔ Courses (Many-to-Many) – A student can subscribe to multiple courses, and a course can have multiple subscribers.
3. Students ↔ Reviews ↔ Courses (One-to-Many) – Each student can review multiple courses, but each review belongs to a single student.
4. Students ↔ PaymentRecords (One-to-Many) – Each student can have multiple payment records.
5. Courses ↔ Instructors (Many-to-Many) – A course can have multiple instructors, and each instructor can teach multiple courses.
6. Courses ↔ Modules (One-to-Many) – A course is divided into multiple modules.
7. Modules ↔ Assessments (One-to-Many) – Each module contains multiple assessments.
8. Students ↔ Submissions ↔ Assessments (Many-to-Many) – Students can submit multiple assessments, and each assessment can be attempted by many students.

These relationships allow for effective data retrieval and logical structuring of the database.

Step 3: ERD Design in PlantUML Notation

To visualize the database structure, we use PlantUML, a widely used tool for creating diagrams in a text-based format. PlantUML is particularly useful for ERD modeling as it allows easy updates and integration into documentation.

Understanding PlantUML Syntax

PlantUML follows a simple and intuitive syntax where:

• entity defines a database table or entity.
• Fields within the entity are listed inside {}.
• + denotes a primary key (PK).
• -- separates metadata from field definitions.
• Relationships between entities are represented using connectors like ||--o{ to show cardinality:
  • Courses ↔ Instructors (Many-to-Many)
  • Students ↔ Submissions ↔ Assessments (Many-to-Many)
    • Now correctly represented as Students ||--|| Submissions and Assessments ||--|| Submissions.
  • Courses ↔ Modules (One-to-Many)
    • Kept as One-to-Many (Courses ||--o{ Modules) because a course contains multiple modules.
  • Modules ↔ Assessments (One-to-Many)

PlantUML uses a domain-specific language (DSL) specifically designed for diagram generation. The syntax is text-based, allowing users to define entities, relationships, and structures in a way that can be easily converted into a visual diagram.

It is not a general-purpose programming language but a markup-like language for defining diagrams, similar to how Markdown is used for text formatting.

When creating an Entity-Relationship Diagram (ERD) in PlantUML, you define entities using the entity keyword, relationships using connectors like ||--o{, and metadata using comments (' for single-line comments).

@startuml
!define primary_key(x) <b>x</b>
!define foreign_key(x) <u>x</u>

entity "Students" as Students {
    primary_key(student_id): INT
    name: VARCHAR(255)
    email: VARCHAR(255)
    registration_date: DATE
    subscription_type: VARCHAR(50)
}

entity "Instructors" as Instructors {
    primary_key(instructor_id): INT
    name: VARCHAR(255)
    email: VARCHAR(255)
    total_courses: INT
}

entity "Courses" as Courses {
    primary_key(course_id): INT
    title: VARCHAR(255)
    category: VARCHAR(100)
}

entity "CourseInstructors" as CourseInstructors {
    foreign_key(course_id): INT
    foreign_key(instructor_id): INT
    PRIMARY KEY (course_id, instructor_id)
}

entity "Modules" as Modules {
    primary_key(module_id): INT
    foreign_key(course_id): INT
    module_title: VARCHAR(255)
    order_number: INT
}

entity "Assessments" as Assessments {
    primary_key(assessment_id): INT
    foreign_key(module_id): INT
    title: VARCHAR(255)
    due_date: DATE
    max_score: INT
}

entity "Submissions" as Submissions {
    primary_key(submission_id): INT
    foreign_key(assessment_id): INT
    foreign_key(student_id): INT
    score: INT
    submitted_on: DATE
}

entity "Enrollment" as Enrollment {
    primary_key(enrollment_id): INT
    foreign_key(student_id): INT
    foreign_key(course_id): INT
    enrolled_on: DATE
    completed: BOOLEAN
}

entity "Subscriptions" as Subscriptions {
    primary_key(subscription_id): INT
    foreign_key(student_id): INT
    start_date: DATE
    end_date: DATE
    subscription_type: VARCHAR(50)
    active: BOOLEAN
}

entity "PaymentRecords" as PaymentRecords {
    primary_key(payment_id): INT
    foreign_key(student_id): INT
    payment_date: DATE
    amount: DECIMAL(10,2)
    payment_method: VARCHAR(50)
}

entity "Reviews" as Reviews {
    primary_key(review_id): INT
    foreign_key(student_id): INT
    foreign_key(course_id): INT
    rating: INT
    comments: TEXT
    review_date: DATE
}

' Relationships
Students ||--o{ Enrollment : enrolls
Courses ||--o{ Enrollment : has
Students ||--o{ Subscriptions : subscribes
Students ||--o{ PaymentRecords : makes
Students ||--o{ Reviews : gives
Courses ||--o{ Reviews : receives

' Many-to-Many (Courses ↔ Instructors) via Junction Table
Courses ||--|| CourseInstructors : has
Instructors ||--|| CourseInstructors : teaches

' One-to-Many (Courses ↔ Modules)
Courses ||--o{ Modules : contains
Modules ||--o{ Assessments : includes

' Many-to-Many (Students ↔ Submissions ↔ Assessments)
Students ||--|| Submissions : submits
Assessments ||--|| Submissions : receives
@enduml

Step 4: Implementing the ERD in SQL

To bring this ERD into reality, we define tables using PostgreSQL SQL syntax. Each table follows the relationships outlined above.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    registration_date DATE NOT NULL,
    subscription_type VARCHAR(50)
);

CREATE TABLE instructors (
    instructor_id INT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    total_courses INT DEFAULT 0
);

-- (Other table definitions follow as per the ERD above)

This ERD ensures efficient database design for a School System, supporting crucial functionalities like:

1. Tracking student enrollments across different courses.
2. Monitoring instructor performance through course assignments.
3. Handling payments and subscriptions efficiently.
4. Recording student submissions for assessments.
5. Providing insightful reports for product managers on revenue, engagement, and performance.

Using PlantUML, we can create diagrams that are easy to read, edit, and integrate into documentation, making it a useful tool for database design and project planning.

Next Episode Preview

In the next article, we will focus on creating database tables and inserting sample data based on this ERD, ensuring a practical implementation of our design