The objective of database schema is to recognize different tables and fields of each table. Schema also describes how the relationship is established between tables. This helps to identify constraints in the system.
The subschema is a subset of the schema and the subschema inherits the same property as the schema.
Schema can be divided into two parts
- Logical Schema
- Physical Schema
1). Logical Schema
The logical schema is all about the design. It doesn’t care where the data is stored; it just focuses on how it’s organized. Think of it as the architect’s plan for a house.
Key Points:
- Tables: These are like the rooms in our house plan. For example, one table might be for customer information, and another for orders.
- Columns: Imagine these as the different features each room can have, like a bed in a bedroom or a stove in a kitchen.
- Relationships: It’s how the rooms are connected. Maybe the living room leads to the kitchen.
Logical Schema Example
In our online bookstore, the logical schema focuses on how we organize and structure data without worrying about how it’s actually stored on disk. Here’s a simplified view:
- Tables:
- Books Table: Contains details about each book. Columns might include BookID, Title, Author, and Price.
- Customers Table: Holds information about customers. Columns might include CustomerID, Name, Email, and Address.
- Orders Table: Keeps track of all book orders. Columns might include OrderID, CustomerID, BookID, Quantity, and OrderDate.
- Relationships:
- Each order in the Orders Table is linked to a specific customer in the Customers Table by CustomerID.
- Each order also links to a specific book in the Books Table by BookID.
This logical schema designs our database to hold information about books, customers, and orders and shows how these entities are related.
2). Physical Schema
The physical schema takes the logical plan and puts it into action. It’s about how the database will use the computer’s storage.
Key Points:
- Data Storage: This is about where the data lives in the computer’s memory. Like deciding whether to put your bed against the wall or in the center of the room.
- Data Access: It also involves how quickly and efficiently data can be retrieved. It’s like putting the things you use most in easy-to-reach places.
Physical Schema Example
The physical schema translates our logical design into a structure that can be stored on the computer’s hard drive:
- Data Storage:
- Books Table: Stored in a file with a specific format, optimized for fast search based on BookID.
- Customers Table: Stored in a separate file, indexed by CustomerID to quickly find customer details.
- Orders Table: Also stored in its file, with indexes on both OrderID for order lookup and CustomerID to efficiently retrieve all orders by a specific customer.
- Data Access Paths:
- Index on Author in the Books Table: Allows quick searches of books by a specific author.
- Index on OrderDate in the Orders Table: Enables efficient querying of orders within certain date ranges.
Combining Logical and Physical Schemas
The logical and physical schemas work together. First, the logical schema designs the organization of data. Then, the physical schema implements this design in the most efficient way possible.
Imagine you’re planning a party. The logical schema is your guest list and the layout of the party space. The physical schema is about arranging the furniture, setting up the decorations, and making sure there’s a flow that allows guests to move easily.
In databases, this teamwork ensures that not only is the data well-organized, but it’s also stored and accessed in the best way possible. This dual approach lets databases support everything from small apps on your phone to huge websites like social media platforms, making sure they run smoothly and efficiently.