Database Schema

1. What is Database Schema?

A database schema represents the structure of a database at a particular level. It defines how data is stored and how different data elements are related.

• Defines database structure
• Does not store actual data
• Acts as a blueprint

2. Schema vs Instance

Schema                         Instance
----------------------------   ----------------------------
Logical structure              Actual data
Rarely changes                 Changes frequently
Design-time                    Run-time

3. Types of Database Schema

• Physical Schema
• Logical Schema
• View Schema

4. Physical Schema

Physical schema describes how data is actually stored in storage devices.

• File organization
• Indexes
• Storage allocation

Disk → Files → Records → Fields

5. Logical Schema

Logical schema describes what data is stored and relationships among data. It is independent of physical storage.

STUDENT (RollNo, Name, CourseID)
COURSE  (CourseID, CourseName)

• Designed by database designers
• Uses tables and constraints

6. View Schema

View schema defines different views for different users. It provides security and abstraction.

Admin View    → Full access
Teacher View  → Student details only
Student View  → Own record only

7. Schema Diagram

STUDENT ────────┐
  | RollNo (PK) │
  | Name        │
  | CourseID ───┼──────► COURSE
                 |         | CourseID (PK)
                 |         | CourseName

8. Importance of Database Schema

• Ensures organized data storage
• Improves data integrity
• Supports security and access control
• Helps in database maintenance

Practice Questions

1. What is database schema?
2. Differentiate between schema and instance.
3. Explain types of database schema.
4. What is view schema?
5. Why database schema is important?

Practice Task