Database

August 28, 2018

Database

Definition

Database is an organized collection of data, stored and accessed electronically. Database designers typically organize the data to model aspects of reality in a way that supports processes requiring information, such as (for example) modelling the availability of rooms in hotels in a way that supports finding a hotel with vacancies.

Database management system (DBMS)

Database management system (DBMS) is a software application that interacts with end users, other applications, and the database itself to capture and analyze data. A general-purpose DBMS allows the definition, creation, querying, update, and administration of databases. A database is generally stored in a DBMS-specific format which is not portable, but different DBMSs can share data by using standards such as SQL and ODBC or JDBC. Sometimes a DBMS is loosely referred to as a "database".

Table/File (database)

A database table is composed of records and fields that hold data. Tables are also called datasheets. Each table in a database holds data about a different, but related, subject.

Record (database)

Data is stored in records. A record is composed of fields and contains all the data about one particular person, company, or item in a database. In this database, a record contains the data for one customer support incident report. Records appear as rows in the database table.

Field

A field is part of a record and contains a single piece of data for the subject of the record. In the database table illustrated in Figure 4, each record contains four fields:

Log ID -	A number assigned to this customer support incident for identification purposes
Operator -	The code for the customer support operator who handled this incident
Resolved -	A check box to indicate whether the incident was resolved
Duration -	The time in seconds the operator spent on this incident

Fields appear as columns in a database table.

Report

A database report is the formatted result of database queries and contains useful data for decision-making and analysis.
Most good business applications contain a built-in reporting tool; this is simply a front-end interface that calls or runs back-end database queries that are formatted for easy application usage. For example, a banking software application may contain specifically defined reports on all customers with large deposits or reports on monthly loan summaries for all customers.
To extract data, a query must be run with various tools that call at least one query language. Structured Query Language (SQL) is the most popular and well-known query language. Other query languages include:

Hyper Text Structured Query Language (HTSQL): This language translates hypertext transfer protocol (HTTP) queries to SQL.
Poliqarp Query Language: This language searches annotated text.
SPARQL (a recursive acronym for SPARQL Protocol and RDF Query Language): This language is for graphing applications.

Another standard reporting feature is the ability to create output parameters or restrictions. For example, when a user runs a monthly loans summary report, the user first enters the specific month or account type associated with the requested report. Specialized reporting tools not typically available via simple queries may be connected to a database to facilitate additional reporting capabilities. These often offer greater data insight and highlight trends and patterns, and are frequently labeled under the buzzword "business intelligence" (BI).

Query

A query is a request for data or information from a database table or combination of tables. This data may be generated as results returned by Structured Query Language (SQL) or as pictorials, graphs or complex results, e.g., trend analyses from data-mining tools.
One of several different query languages may be used to perform a range of simple to complex database queries. SQL, the most well-known and widely-used query language, is familiar to most database administrators (DBAs).
The query database feature is equal in necessity to data storage capability. Thus, a number of query languages have been developed for different database engines and purposes, but SQL is by far the most ubiquitous and well-known. In fact, rookie database administrators often are surprised when they learn about the existence of other query languages, somewhat akin to how native English-speaking children are perplexed when hearing a foreign language for the first time. The element of surprise in both scenarios leads to a better understanding of other languages.
Query languages generate different data types according to function. For example, SQL returns data in neat rows and columns and is very similar to Microsoft Excel in appearance.
Other query languages generate data as graphs or other complex data manipulations, e.g., data mining, which is the deep analysis of information that uncovers previously-unknown trends and relationships between distinct or divergent data. For example, a SQL manufacturing company query may reveal that monthly sales peak in June and July, or that female sales representatives continually outperform male counterparts during holiday months.

Primary Key

A primary key is a special relational database table column (or combination of columns) designated to uniquely identify all table records.

A primary key’s main features are:

It must contain a unique value for each row of data.
It cannot contain null values.

A primary key is either an existing table column or a column that is specifically generated by the database according to a defined sequence.

Tips for Choosing Good Primary Keys

Keep it short. Because the primary key is used for lookups and comparisons, a short primary key means the database management system can process it more quickly than a long primary key.
Use a number for the primary key whenever possible. SQL Server or other database management systems process number data types faster than character data types.
Keep it simple. Don't use any special characters, embedded spaces, or a mix of upper and lower capitalization.
Never change the primary key after you assign it.

Foreign Key

A foreign key is a column or columns of data in one table that connects to the primary key data in the original table.
To ensure the links between foreign key and primary key tables aren't broken, foreign key constraints can be created to prevent actions that would damage the links between tables and prevent erroneous data from being added to the foreign key column.