As developers of web applications for Oracle databases and for any developer who works with applications connected to a relational database, it is very important to know and understand what a database schema is and how it is used.
Every organization has information to store and manage to meet their own requirements. For example, a corporation might require the collection and maintenance of employee data within the Human Resources area and allow such information to be accessed by the rest of the organization.
An information system is a formal system (manual or automated) for storing and processing information. We can think that a manual information system, for this case, like a file cabinet full of folders with the files of each employee organized alphabetically.
Figure 1: Files organized alphabetically.
However, most companies today use a database to automate their information systems. A database is an organized collection of structured information stored electronically. The purpose of a database is to collect, store, and retrieve related information for application usage in the database. In short, a database allows us to store a large amount of information in an organized manner for future consultation and data entry and manipulation, as well as carrying out searches and many other operations that the application would require.
A database is usually managed by a database management system (DBMS) which is a software that controls the storage, organization, and retrieval of data.
On the other hand, a database application is a software that interacts with a database to access and manipulate data. It is important not to confuse a web application with a web page. While the web application interacts with the database by displaying and manipulating data from the database, the web page created in HTML, for example, simply displays information without interacting with a database.
Before the database is created, we as developers need to plan and design components to include in that database as well as figure out how those components will be related to one another. Good planning will help us ensure that our database contains all the necessary components to meet the needs of our project.
And this is where database schemas come into play, because they will help us see how a database should be configured.
So now that we know what a database is, let us answer the following question.
What is a database schema?
A database schema is defined in the Oracle documentation as a collection of logical data structures or schema objects. A database user owns a database schema, which has the same name as the username.
Database schema objectsare user-created structures that directly refer to data in the database. The database supports many types of schema objects, the most important of which are tables and indexes. These contain data and other objects, such as views or synonyms that consist of only one definition.
In thisarticle you can learn how to navigate through all the objects in a database schema using Toad for Oracle.
Other types of objects are also stored in the database and can be created and manipulated with SQL statements, but they are not contained in a schema. These objects include database user accounts, roles, contexts, and dictionary objects.
The database schema defines how the data is organized and how the relationships between them are associated. In addition, it determines all the restrictions that will be applied to the data.
A database schema is basically like any other database object. It is like a container for other objects that makes it easy to manage sets of objects in complex databases, which could help database administrators grant roles and permissions establishing the security that is so necessary when working with data in a database.
A database user account has a specific database password and privileges. Each user account has a unique schema which, as we mentioned before, has the same name as the user. The database schema contains the data of the user who owns the schema. For example, the HR user account owns the HR schema which contains schema objects such as the Employees (EMP) table.
Figure 2: HR Schema
In short, we can think of a database as a box that contains objects of type schemas, tables, indexes, etc. And a schema is the container for all the objects of a user account.
Figure 3: Database and Schemas
In a production database, the schema owner generally represents a database application rather than a person.
The size and complexity of a database schema depends on the size of the project.
In many companies, database design and DBMS responsibilities generally fall to the role of the database administrator (DBA). DBAs are responsible for ensuring that information is easily accessible by users and data analysts. In addition, they work closely with developers to meet application requirements.
How to create a database schema in Oracle
To create a database schema in Oracle, simply create a user and its associated schema with the same name will be created.
The most basic way to do it is with the following SQL query:
SQL> create user <user_name> identifed by <password>;
To know more about the creation of users in Oracle you can visit the following link here.
It may seem a bit confusing since we can interpret the user and the schema to be synonymous, but for Oracle, they are different objects. The user is the name of the account and the database schema is the set of all tables and other objects owned by that user. A user account is not the same as a database schema, even though the schema is created by using the CREATE USER statement and the schema has the same name as the user. For this reason, when the user is created, the schema will be created and it will contain all the objects such as tables, indexes, views, procedures and PL/SQL functions, and anything else that is owned by the user.
Database schema object storage
The Oracle database stores a schema object logically within a tablespace. There is no relationship between database schemas and a tablespace: a tablespace can contain objects from different schemas, and objects from a database schema can be contained in different tablespaces. The data for each object is physically contained in one or more data files.
How to name database schema objects
Each database object belongs to a database schema and has a unique name within that schema. Multiple database objects can share the same name if they are in different database schemas. We can use the schema name to refer to objects unambiguously. For example, hr.employees refers to the table named employees in the hr schema. (The employees table is owned by hr.)
When we create a database schema object, we must make sure to create it in the desired schema. One method to achieve this is to log into the database as the user who owns the database schema and then create the object. Generally, we put all the objects that belong to a single application in the same database schema.
A database schema object name must adhere to certain rules. In addition to being unique within a database schema, the name of a database schema object cannot be longer than 30 bytes and must begin with a letter. If we try to create an object with a name that violates any of these rules, the database will throw an error.
Conclusion
By knowing what database schemas are, developers can plan a database schema in advance to learn what components are required for their application and how these components will connect to each other. The importance of database schemas is that they help administrators and developers understand how a database is structured, and this allows a database to be built and managed much more effectively.
Related information
Blog: Getting to Know the Toad for Oracle Schema Browser
Blog: How to compare two database schemas in Oracle using Toad®
Blog: How to compare multiple database schemas in Oracle using Toad.
Quest blog: Using Database Schemas in SQL Server
Video: Toad for Oracle – Compare DB environments with multiple schemas
Blog: Compare and Sync Multiple Tables with Toad
Blog:How to compare database schemas with Toad® DevOps Toolkit 1.3
Have questions, comments or an idea for a new Toad feature?
Head over to the Toad for Oracle forum on Toad World®! Chat with the Toad developers, and lots of experienced users.
Help your colleagues
If you think your colleagues would benefit from this blog, share it now on social media with the buttons located at the top of this blog post. Thanks!
Start the discussion at forums.toadworld.com