A relational database program is a standard tool for storing and analyzing data. Examples include Microsoft SQL Server, PostgreSQL, MySQL and various products from companies such as Oracle and IBM. A relational schema outlines the database relationships and structure in a relational database program. It can be displayed graphically or written in the Structured Query Language (SQL) used to build tables in a relational database.
Understanding Relational Databases
Video of the Day
A relational database is a tool for digitally storing and analyzing data. Relational databases are the backbone of many servers on the internet and are used to generate web content for everything from news websites to banks.
Video of the Day
A variety of relational database programs exists, including some proprietary tools from big-name companies like Microsoft and Oracle as well as free open-source systems. Most of these database systems are controlled and accessed through SQL, a specialized programming language designed for communicating with databases. Each database system has a slightly different variation of SQL, but they're compatible enough that programmers can quickly learn to use one if they've used another.
Relational databases typically store data in tables as records with predefined columns, similar to the way many people use spreadsheets. For example, a table storing customer information might have a text column for the customer's first name, a text column for the customer's last name and a decimal data column for the customer's total spending over time.
Tables often include a unique identifier in each row known as a primary key. It may be denoted by a primary key symbol such as an image of a physical key in many graphical systems. Tables with the same primary key can be joined to pull related information, such as joining a table of customer information to one containing orders.
Understanding a Relational Schema
A relational schema for a database is an outline of how data is organized. It can be a graphic illustration or another kind of chart used by programmers to understand how each table is laid out, including the columns and the types of data they hold and how tables connect. It can also be written in SQL code.
A database schema usually specifies which columns are primary keys in tables and which other columns have special constraints such as being required to have unique values in each record. It also usually specifies which columns in which tables contain references to data in other tables, often by including primary keys from other table records so that rows can be easily joined. These are called foreign key columns. For example, a customer order table may contain a customer number column that is a foreign key referencing the primary key of the customer table.
CREATE Statements and Database Schemas
A database schema is ultimately implemented in SQL through CREATE statements. These are commands to the database program to build (or create) tables with certain specifications. They specify which column constitutes a primary key, what type of data each column stores, and which are foreign keys referencing other tables.
They also often indicate what indexes should be built on tables to allow them to be easily queried and analyzed without needing to go through each record to find data matching particular constraints.
You usually access the CREATE statements or another representation of the schema from within the database program, although the command to do so varies from program to program.
Some web development tools, such as Django in Python and Rails in Ruby, allow you to build database schemas and layouts using their languages. The schemas are then translated into the appropriate dialect of SQL to create the underlying database.