Schema in DBMS
What is a Schema?
A schema in a Database Management System (DBMS) defines the logical structure of a database, including its tables, relationships, constraints, views, indexes, and permissions. It acts as a blueprint that dictates how data is organized and how different entities interact.
Key Characteristics of a Schema
✅ Logical structure – Defines how data is stored and related.
✅ Includes constraints – Specifies rules (e.g., primary keys, foreign keys).
✅ Does not store data – Acts as a framework for data storage.
✅ Can be modified – Using ALTER SCHEMA or ALTER TABLE.
Types of Database Schemas
A DBMS schema can be divided into different levels based on the ANSI-SPARC three-tier architecture:
- Physical Schema (Internal Level) – Storage details
- Logical Schema (Conceptual Level) – Data structure & relationships
- View Schema (External Level) – User-specific views
1. Physical Schema (Internal Schema)
🔹 Definition: Describes how data is physically stored on hardware (disks, SSDs).
🔹 Includes: File organization, indexing methods, access paths.
🔹 Users: Database administrators (DBAs) & system engineers.
💡 Example:
- Data is stored in B-trees, hash indexes, or tablespaces.
- Indexing is used to speed up retrieval.
2. Logical Schema (Conceptual Schema)
🔹 Definition: Defines the structure of the database (tables, attributes, relationships).
🔹 Includes:
- Tables & fields (
Employee(id, name, department)) - Relationships (
One-to-Many between Employee & Department) - Constraints (
Primary key, foreign key, NOT NULL)
🔹 Users: Database developers, architects.
Defines the Employee table structure with a foreign key linking it to the Department table.
3. View Schema (External Schema)
🔹 Definition: Defines how different users see the database.
🔹 Includes:
- User permissions (
HR sees salaries, but employees don’t). - Custom views for business intelligence and reporting.
🔹 Users: End-users, business analysts, security teams.
- Creates a custom view that only shows salary details for a specific department.
Other Types of Schemas in DBMS
| Schema Type | Description | Example |
|---|---|---|
| Star Schema | A single fact table connected to multiple dimension tables | Data warehouses, OLAP |
| Snowflake Schema | A normalized version of a star schema | Complex analytics |
| Fact Constellation | Multiple fact tables sharing common dimension tables | Large-scale BI applications |
| ER Model Schema | Uses Entity-Relationship diagrams to model relationships | Database design phase |
Comparison Table: Physical vs. Logical vs. View Schema
| Feature | Physical Schema | Logical Schema | View Schema |
|---|---|---|---|
| Focus | How data is stored | Data structure & relationships | User-specific views |
| Includes | Files, indexes, partitions | Tables, relationships, constraints | Custom reports, security filters |
| Users | DBAs, System Engineers | Developers, Architects | End-users, Analysts |
Schema Modification (ALTER SCHEMA Commands)
Modify Table Structure (Logical Schema Change)
Conclusion
- The Physical Schema handles storage details.
- The Logical Schema defines tables, relationships, and constraints.
- The View Schema provides user-specific data access.
- Different schemas like Star, Snowflake, and Fact Constellation are used in data warehouses.
About the Author
One thing is that when you are searching for a education loan you may find that you’ll need a co-signer. There are many situations where this is correct because you should find that you do not employ a past credit ranking so the bank will require you have someone cosign the credit for you. Good post.
Hey there just wanted to give you a quick heads up. The text in your article seem to be running off the screen in Ie. I’m not sure if this is a formatting issue or something to do with browser compatibility but I figured I’d post to let you know. The style and design look great though! Hope you get the issue resolved soon. Cheers
Howdy would you mind sharing which blog platform you’re working with? I’m going to start my own blog soon but I’m having a tough time selecting between BlogEngine/Wordpress/B2evolution and Drupal. The reason I ask is because your design and style seems different then most blogs and I’m looking for something completely unique. P.S Apologies for getting off-topic but I had to ask!
You made some nice points there. I looked on the internet for the subject and found most guys will consent with your website.
Valuable information. Fortunate me I found your site by chance, and I’m stunned why this twist of fate didn’t came about in advance! I bookmarked it.
I like the valuable information you provide to your articles. I抣l bookmark your blog and check once more here frequently. I’m slightly certain I will be told plenty of new stuff right right here! Best of luck for the following!
You’ve been great to me. Thank you! http://www.kayswell.com
I’ve been surfing online more than 3 hours today, yet I never found any interesting article like yours. It’s pretty worth enough for me. Personally, if all webmasters and bloggers made good content as you did, the web will be much more useful than ever before.