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.
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.
An orphaned record is a record whose foreign key value references a non-existent primary key value.
Orphaned records are a concept within database relationships. If a row in a related table references a non-existent row in the primary table, it is said to be an orphaned row. This is because it has no “parent” with which its data is associated with. The terms orphaned row and orphaned record tend to be used interchangeably, even though there’s a subtle difference between a row and a record.
Referential integrity refers to the accuracy and consistency of data within a relationship.
In relationships, data is linked between two or more tables. This is achieved by having the foreign key (in the associated table) reference a primary key value (in the primary – or parent – table). Because of this, we need to ensure that data on both sides of the relationship remain intact.
So, referential integrity requires that, whenever a foreign key value is used it must reference a valid, existing primary key in the parent table.
The term data integrity refers to the accuracy and consistency of data.
When creating databases, attention needs to be given to data integrity and how to maintain it. A good database will enforce data integrity whenever possible.
For example, a user could accidentally try to enter a phone number into a date field. If the system enforces data integrity, it will prevent the user from making these mistakes.
Maintaining data integrity means making sure the data remains intact and unchanged throughout its entire life cycle. This includes the capture of the data, storage, updates, transfers, backups, etc. Every time data is processed there’s a risk that it could get corrupted (whether accidentally or maliciously).
In database terminology, field is often used to refer to the individual cells within a row or column. However, it can also refer to the whole column itself.
When referring to an individual cell, we’re usually referring to the value within that cell. So a user might ask “what value is in the FirstName field?” when referring to an individual record.
When referring to the whole column, we’re usually referring to the name of the column, its data type, constraints, and any data contained within that column.
In relational databases, a record is a collection of fields that contain data about a given entity.
A record is typically stored as a row in a table. A record contains the smallest amount of data that can be inserted, updated or deleted from a table.
An example of a record could be a single row in a “Customers” table. This row could contain the customer’s first name and last name for example. Therefore, the record contains the customer’s first name and last name. It could also contain other fields as required – such as an ID field, the date the record was created, etc.
In a relational database, a column is a vertical group of cells within a table.
It’s also used to describe the vertical group of cells within a result set of a query, or other database objects, such as views, stored procedures, table-valued functions, etc.
In a table, each column is typically assigned a data type and other constraints which determine the type of value that can be stored in that column. For example, one column might email addresses, another might accept phone numbers.
In relational database terms, a row is a collection of fields that make up a record.
The cells in a row run horizontally, and together, contain all data for that record.
A row can contain as many fields as required, each one defined in a different column. There must be at least one column defined in a table before a row of data can be added. The row is the smallest unit of data that can be inserted into a table and deleted from a table.
In database terminology, a cell is a part of a table where a row and column intersect. A cell is designed to hold a specified portion of the data within a record. A cell is sometimes referred to as a field (although a column is also often referred to as a field).
A table row is made up of one or more cells running next to each other horizontally. A column is made up of one or more cells running below each other vertically.