Schema Diagram

1. What is a Schema Diagram?

A schema diagram illustrates the logical structure of a database. It helps users understand how tables are connected and how data flows.

• Represents tables and relationships
• Shows primary and foreign keys
• Helps in database design and maintenance

2. Components of Schema Diagram

• Tables (Relations)
• Attributes (Columns)
• Primary Keys (PK)
• Foreign Keys (FK)
• Relationships

3. Representation of a Table

STUDENT
---------------------
RollNo (PK)
Name
CourseID (FK)

4. Schema Diagram Example

STUDENT
---------------------
RollNo (PK)
Name
CourseID (FK)
        |
        |
        ▼
COURSE
---------------------
CourseID (PK)
CourseName

Here, CourseID in STUDENT is a foreign key that references CourseID in COURSE.

5. Relationship Types in Schema Diagram

• One-to-One (1:1)
• One-to-Many (1:M)
• Many-to-Many (M:N)

1:M → COURSE ─── STUDENT
M:N → STUDENT ─── COURSE (using ENROLL table)

6. Schema Diagram for Many-to-Many Relationship

STUDENT               ENROLL                COURSE
---------             -----------           --------
RollNo (PK)  ←──┐     RollNo (FK)     ┌──→  CourseID (PK)
Name               CourseID (FK)      |      CourseName
                   (PK: RollNo, CourseID)

7. Advantages of Schema Diagram

• Easy visualization of database structure
• Helps in identifying relationships
• Improves database documentation
• Useful for developers and DBAs

8. ER Diagram vs Schema Diagram

ER Diagram                     Schema Diagram
----------------------------   -----------------------------
Conceptual design              Logical design
Entities & relationships       Tables & keys
High-level                     Implementation-level

Practice Questions

1. What is a schema diagram?
2. List components of schema diagram.
3. Draw schema diagram for Student–Course system.
4. Differentiate ER diagram and schema diagram.
5. How is M:N relationship represented in schema diagram?

Practice Task