DATABASE





TABLE=A table is a collection of related data held in a structured format within a database. It consists of columns, and rows.

In relational databases, and flat file databases, a table is a set of data elements (values) using a model of vertical columns (identifiable by name) and horizontal rows, the cell being the unit where a row and column intersect.^[1] A table has a specified number of columns, but can have any number of rows.^[2] Each row is identified by one or more values appearing in a particular column subset. A specific choice of columns which uniquely identify rows is called the primary key.

"Table" is another term for "relation"; although there is the difference in that a table is usually a multiset (bag) of rows where a relation is a set and does not allow duplicates. Besides the actual data rows, tables generally have associated with them some metadata, such as constraints on the table or on the values within particular columns.

The data in a table does not have to be physically stored in the database. Views also function as relational tables, but their data are calculated at query time. External tables (in Informix^[3] or Oracle,^[4][5] for example) can also be thought of as views.

E.g.=.

SOURCE

1.https://en.wikipedia.org/wiki/Table_(database)

2.https://www.cengage.com/school/corpview/RegularFeatures/DatabaseTutorial/db_elements/db_elements2.htm

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 3.

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 4.

Figure 4 Fields appear as columns in a database table.

SOURCE

1.https://www.cengage.com/school/corpview/RegularFeatures/DatabaseTutorial/db_elements/db_elements2.htm

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).

SOURCE

1.https://www.techopedia.com/definition/5736/query

efinition - What does Primary Key mean?

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.

Techopedia explains Primary Key

The primary key concept is critical to an efficient relational database. Without the primary key and closely related foreign key concepts, relational databases would not work.

Almost all individuals deal with primary keys frequently but unknowingly in everyday life. For example, students are routinely assigned unique identification (ID) numbers, and all U.S. citizens have government-assigned and uniquely identifiable Social Security numbers.

For example, a database must hold all of the data stored by a commercial bank. Two of the database tables include the CUSTOMER_MASTER, which stores basic and static customer data (name, date of birth, address, Social Security number, etc.) and the ACCOUNTS_MASTER, which stores various bank account data (account creation date, account type, withdrawal limits or corresponding account information, etc.).

To uniquely identify customers, a column or combination of columns is selected to guarantee that two customers never have the same unique value. Thus, certain columns are immediately eliminated, e.g., surname and date of birth. A good primary key candidate is the column that is designated to hold Social Security numbers. However, some account holders may not have Social Security numbers, so this column’s candidacy is eliminated. The next logical option is to use a combination of columns, such as adding the surname to the date of birth to the email address, resulting in a long and cumbersome primary key.

The best option is to create a separate primary key in a new column named CUSTOMER_ID. Then, the database automatically generates a unique number each time a customer is added, guaranteeing unique identification. As this key is created, the column is designated as the primary key within the SQL script that creates the table, and all null values are automatically rejected.

The account number associated with each CUSTOMER_ID allows for the secure handling of customer queries and also demonstrates why primary keys offer the fastest method of data searching within tables. For example, a customer may be asked to provide his surname when conducting a bank query. A common surname (such as Smith) query is likely to return multiple results. When querying data, utilizing the primary key uniqueness feature guarantees one result.

SOURCE

1.https://www.techopedia.com/definition/5547/primary-key

Definition - What does Foreign Key mean?

A foreign key is a column or group of columns in a relational database table that provides a link between data in two tables. It acts as a cross-reference between tables because it references the primary key of another table, thereby establishing a link between them.

The majority of tables in a relational database system adhere to the foreign key concept. In complex databases and data warehouses, data in a domain must be added across multiple tables, thus maintaining a relationship between them. The concept of referential integrity is derived from foreign key theory.

Foreign keys and their implementation are more complex than primary keys.

Techopedia explains Foreign Key

For any column acting as a foreign key, a corresponding value should exist in the link table. Special care must be taken while inserting data and removing data from the foreign key column, as a careless deletion or insertion might destroy the relationship between the two tables.

For instance, if there are two tables, customer and order, a relationship can be created between them by introducing a foreign key into the order table that refers to the customer ID in the customer table. The customer ID column exists in both customer and order tables. The customer ID in the order table becomes the foreign key, referring to the primary key in the customer table. To insert an entry into the order table, the foreign key constraint must be satisfied. An attempt to enter a customer ID that is not present in the customer table fails, thus maintaining the table's referential integrity.

Some referential actions associated with a foreign key action include the following:

• Cascade: When rows in the parent table are deleted, the matching foreign key columns in the child table are also deleted, creating a cascading delete.
• Set Null: When a referenced row in the parent table is deleted or updated, the foreign key values in the referencing row are set to null to maintain the referential integrity.
• Triggers: Referential actions are normally implemented as triggers. In many ways foreign key actions are similar to user-defined triggers. To ensure proper execution, ordered referential actions are sometimes replaced with their equivalent user-defined triggers.
• Set Default: This referential action is similar to "set null." The foreign key values in the child table are set to the default column value when the referenced row in the parent table is deleted or updated.
• Restrict: This is the normal referential action associated with a foreign key. A value in the parent table cannot be deleted or updated as long as it is referred to by a foreign key in another table.
• No Action: This referential action is similar in function to the "restrict" action except that a no-action check is performed only after trying to alter the table.

SOURCE

1.https://www.techopedia.com/definition/7272/foreign-key