A data warehouse is a large collection of data that can be used to help an organisation make key business decisions.
Here’s a more precise definition of the term, as coined by Bill Inmon, (considered by many to be “the father of data warehousing”):
A data warehouse is a subject-oriented, integrated, nonvolatile, and time-variant collection of data in support of management’s decisions.
The term big data refers to the massive amounts of data – both structured and unstructured – that inundate organisations on a day-to-day basis.
Typically, big data is so large, and accumulates so fast, that traditional data storage and processing applications are inadequate.
The big data industry helps organisations capture and analyse their big data, so that those organisations can make more informed business decisions.
DBMS stands for Database Management System.
A database management system is an application that enables the creation and administration of databases. Database management system is a broad term that includes any system that performs that function.
The most common type of DBMS is an RDBMS (Relational Database Management System). RDBMSs allow you to create relational databases – databases that have multiple tables that contain related data.
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.