What is a database schema?

What is a database schema?

A database schema is a way of organizing and describing the data in a relational database. A database schema defines the structure of the data, such as the tables, fields, data types, constraints, and relationships between them. A database schema also provides information about how to access and manipulate the data, such as the queries, views, indexes, and procedures.

A database schema is like a blueprint of a database that shows how the data is stored and connected. A database schema helps developers and users understand the data and its relationships, and also ensures consistency and integrity of the data. A database schema can be represented visually using diagrams or textually using code or syntax.

Types of database schemas

There are three main types of database schemas:

• Physical schema: This type of schema describes how the data is physically stored in the storage system, such as the files, blocks, sectors, and indexes. A physical schema specifies the technical details of the data storage, such as the format, encoding, compression, encryption, and partitioning. A physical schema is usually written in a specific language or syntax that is understood by the database management system (DBMS).
• Logical schema: This type of schema describes the logical structure and organization of the data, such as the tables, columns, data types, primary keys, foreign keys, and constraints. A logical schema defines the rules and restrictions that apply to the data, such as the integrity constraints, validation rules, and triggers. A logical schema is usually written in a standard language or syntax that is independent of the DBMS.
• View schema: This type of schema describes how the data is presented to the users or applications, such as the views, queries, reports, and dashboards. A view schema defines what data is visible and accessible to different users or roles, and how the data is formatted and aggregated. A view schema can be written in a variety of languages or syntaxes that are supported by the DBMS or the application.

Benefits of database schemas

Database schemas have many benefits for both developers and users of databases. Some of these benefits are:

• Data quality: Database schemas ensure that the data is consistent, accurate, complete, and reliable. Database schemas enforce integrity constraints that prevent invalid or duplicate data from being entered or stored in the database. Database schemas also provide validation rules that check the data for errors or anomalies before inserting or updating it in the database.
• Data security: Database schemas protect the data from unauthorized access or modification. Database schemas define different levels of access and permissions for different users or roles. Database schemas also support encryption and authentication mechanisms that secure the data from external threats or attacks.
• Data efficiency: Database schemas optimize the performance and scalability of the database. Database schemas enable faster and easier retrieval and manipulation of the data by using indexes, partitions, views, and procedures. Database schemas also reduce the storage space and bandwidth required by using compression and encoding techniques.
• Data integration: Database schemas facilitate the exchange and sharing of data between different databases or systems. Database schemas provide a common language and format for describing and communicating the data. Database schemas also support interoperability and compatibility standards that enable different databases or systems to work together.
• Data analysis: Database schemas enable better understanding and insight into the data and its relationships. Database schemas provide visual representations and documentation of the data using diagrams or text. Database schemas also support various analytical tools and techniques that enable users to query, explore, visualize, and report on the data.

Examples of database schemas

To illustrate how database schemas work in practice, let us look at some examples of database schemas for different scenarios.

Example 1: Online bookstore

Suppose we want to design a database for an online bookstore that sells books to customers. The database should store information about books, authors, publishers, customers, orders, payments, reviews, etc.

A possible physical schema for this database could look something like this:

CREATE TABLE books (
  book_id INT PRIMARY KEY,
  title VARCHAR(100) NOT NULL,
  author_id INT NOT NULL,
  publisher_id INT NOT NULL,
  genre VARCHAR(20) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  stock INT NOT NULL,
  rating FLOAT NOT NULL,
  FOREIGN KEY (author_id) REFERENCES authors(author_id),
  FOREIGN KEY (publisher_id) REFERENCES publishers(publisher_id)
);

CREATE TABLE authors (
  author_id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  bio TEXT NOT NULL
);

CREATE TABLE publishers (
  publisher_id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  address VARCHAR(100) NOT NULL
);

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(50) NOT NULL UNIQUE,
  password VARCHAR(50) NOT NULL,
  address VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT NOT NULL,
  date DATE NOT NULL,
  total DECIMAL(10,2) NOT NULL,
  status VARCHAR(10) NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE order_details (
  order_id INT NOT NULL,
  book_id INT NOT NULL,
  quantity INT NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (order_id, book_id),
  FOREIGN KEY (order_id) REFERENCES orders(order_id),
  FOREIGN KEY (book_id) REFERENCES books(book_id)
);

CREATE TABLE payments (
  payment_id INT PRIMARY KEY,
  order_id INT NOT NULL UNIQUE,
  method VARCHAR(10) NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  date DATE NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

CREATE TABLE reviews (
  review_id INT PRIMARY KEY,
  book_id INT NOT NULL,
  customer_id INT NOT NULL,
  rating INT NOT NULL,
  comment TEXT NOT NULL,
  date DATE NOT NULL,
  FOREIGN KEY (book_id) REFERENCES books(book_id),
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

A possible logical schema for this database could look something like this:

A possible view schema for this database could look something like this:

CREATE VIEW best_sellers AS
SELECT b.title, b.genre, b.price, b.rating, a.name AS author, p.name AS publisher
FROM books b
JOIN authors a ON b.author_id = a.author_id
JOIN publishers p ON b.publisher_id = p.publisher_id
WHERE b.rating >= 4.5
ORDER BY b.rating DESC;

CREATE VIEW order_history AS
SELECT o.order_id, o.date, o.total, o.status, p.method AS payment_method, p.amount AS payment_amount, p.date AS payment_date
FROM orders o
JOIN payments p ON o.order_id = p.order_id;

CREATE VIEW customer_reviews AS
SELECT c.name AS customer_name, c.email AS customer_email, r.rating, r.comment, r.date
FROM reviews r
JOIN customers c ON r.customer_id = c.customer_id;

Tweet

Read More..