Database and everything in between

Database

    A 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

    A 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".

    Computer scientists may classify database-management systems according to the database models that they support. Relational databases became dominant in the 1980s. These model data as rows and columns in a series of tables, and the vast majority use SQL for writing and querying data. In the 2000s, non-relational databases became popular, referred to as NoSQL because they use different query languages.

Table/file

    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.

Figure 1

Record

    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. A record for Log ID 1201242 is highlighted in Figure 2.

Figure 2

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. Data from the Log ID field for five records is highlighted in the Figure 3.

Figure 3

Report

    A report presents data in an attractive format and is especially suitable for printing. Reports can display data from tables or queries. All or selected fields can be included in a report. Data can be grouped or sorted and arranged in a variety of ways.

Figure 4 shows a report containing data from the Complaints table. The data is grouped by resolved and unresolved incidents. The Log ID field is sorted in ascending order. Compare the format of the data in this report to the database table shown in Figure 5.

Figure 4↑         Figure 5↓

Form

    A database form shows all or selected fields for one record. Forms show field names and data in an attractive and easy-to-read format. Record 1 of the database is shown in Figure 5.

You can enter data in fields in a form just as you can in a table. Data has been entered in three fields in the form shown in Figure 7. Navigation buttons for moving from record to record are displayed at the bottom of the form.

Query

    A query finds records in a database according to criteria you specify. Sometimes working with all the data in a large database is not practical. You might wish to work with just a small part or subset of the data. For example, you might want to find records for unresolved customer support incidents. A query can be used to display these records. You could construct the query to display only selected fields, such as Log ID and Resolved. A query displays only the data you request.

The table in Figure 8 shows all data that has been entered in the database. An empty checkbox in the Resolved field indicates the incident has not been resolved.

Figure 9 shows the results of a query. Only the Log ID and Resolved fields for unresolved incidents are displayed.

Primary key

    primary key of a relational table uniquely identifies each record in the table. Databases use keys to compare, sort, and store records, and to create relationships between records.

Choosing the primary key in a database is one of the most important steps in the process. It can be a normal attribute that is guaranteed to be unique such as Social Security number on a table with no more than one record per person or – preferably – it can be generated by the database management system such as a globally unique identifier, or GUID, in Microsoft SQL Server. Primary keys may consist of a single attribute or multiple attributes in combination.

Primary keys are the unique links to related information in other tables where the primary key is used. It must be entered when a record is created, and it should never be changed. Each table in the database has a column or two specifically for the primary key.

Foreign key

    a foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table or the same table.^]In simpler words, the foreign key is defined in a second table, but it refers to the primary key or a unique key in the first table. For example, a table called Employees has a primary key called employee_id. Another table called Employee Details has a foreign key which references employee_id in order to uniquely identify the relationship between the two tables.