In SQL Server, “schema-bound” refers to a specific feature that ensures a SQL object, such as a view or function, is tightly linked to the schema of the underlying tables or other database objects it references. When a view or function is schema-bound, the underlying tables or objects cannot be altered in a way that would break the view or function’s dependencies.
Continue readingTag: database schema
How to Move a Table to Another Schema in SQL Server
In SQL Server, if you ever need to move one or more tables to another schema, you can use the T-SQL ALTER SCHEMA command.
Get the Underlying Columns of a View Based on its Result Set
One of the great things about database views, is that they allow you to run complex queries without needing to know the underlying database schema.
Yes, it’s true that you need to know the underlying schema when you create the view, but you only need to do that once. Once you’ve created it, you can query that view all day long without needing to remember all the table and column names, etc.
Views typically combine data from multiple tables into a single, virtual table, which makes it kind of like a “black box”. As long as it works as designed, you don’t need to concern yourself with the hidden details.
But what if you do want to check a view for its underlying tables and columns?
Continue reading3 Ways to Get the Schema of a Result Set in SQL Server
Sometimes when you run a query in SQL Server, you might want to know what the underlying data type of each column is, its precision, length, whether or not its nullable, etc.
If you’re querying just one table, you can get this sort of data with procedures such as sp_columns. But if your query runs across many tables, this could get unwieldy very quickly.
Fortunately there are several ways you can go about getting such metadata for a result set in SQL Server.
Continue readingWhat is Normalization?
Normalization is the process of organizing a database to reduce redundancy and improve data integrity.
Normalization also simplifies the database design so that it achieves the optimal structure composed of atomic elements (i.e. elements that cannot be broken down into smaller parts).
Also referred to as database normalization or data normalization, normalization is an important part of relational database design, as it helps with the speed, accuracy, and efficiency of the database.
Schema Definitions by DBMS
Different database management systems define schema in their own way. This can make it difficult for database developers to work out exactly what a schema is – especially when switching between different DBMSs.
This article provides definitions used by the three leading database systems.
What is a Database Schema?
In database terms, a schema (pronounced “skee-muh” or “skee-mah”) is the organisation and structure of a database. Both schemas and schemata can be used as plural forms.
A schema contains schema objects, which could be tables, columns, data types, views, stored procedures, relationships, primary keys, foreign keys, etc.
A database schema can be represented in a visual diagram, which shows the database objects and their relationship with each other.
Above is a simple example of a schema diagram. It shows three tables, along with their data types, relationships between the tables, as well as their primary keys and foreign keys.
How to Reverse Engineer a Database in MySQL Workbench
To reverse engineer a database in MySQL Workbench:
- Select Database > Reverse Engineer from the top menu of MySQL Workbench
- Set/review parameters for connecting to the DBMS then click Continue
- Enter password if required, then click OK
- The wizard will connect to the DBMS, fetch a list of databases, and check for any issues. Click Continue
- Select the database/s you would like to reverse engineer, then click Continue
- The wizard will retrieve all objects from the selected schema/s and check the results. Click Continue
- Select the database objects you’d like to have reverse engineered, then click Execute
- The wizard will now reverse engineer all selected objects and generate the EER diagram (behind the scenes). Click Continue
- A summary is displayed. Click Close
The EER diagram is now displayed on the screen.