General handling of databases

Database basics

A database is an organized collection of data that is typically stored in an electronic format. In a database, data can be quickly input, managed, organized and retrieved.

Traditional databases are organized by datasets (rows) and fields (columns), which are located in tables stored in the database files. Each row within a table corresponds to a single dataset and contains several attributes that describe the dataset.

The following figure shows the general structure in database management systems.

Data types and default data type categories

Databases use the following data type categories and data types by default.

• Exact numeric (bigint, bit, decimal, int, money, numeric, smallint)

• Approximate numeric (float, real)

• Date and time (date, datetime2, datetime, datetimeoffset, time)

• Character strings (char, varchar, text)

• Unicode character strings (nchar, ntext, nvarchar)

• Binary strings (binary, varbinary, image)

• Other data types (cursor, time stamp, unique identifier, table)

• Large value data types (varchar(max), nvarchar(max))

• Large object data types (text, ntext, image, xml)

Relational databases

A relational database is a collection of data tables that are formally described and organized according to the relational model. Each table must identify a column or group of columns using the primary key in order to uniquely identify each row.

ibaAnalyzer creates the following types of tables, see Database format options.

Referential integrity (RI) is a database concept used to ensure that the relationships between database tables remain synchronized during data changes and that the data is consistent. RI may also be helpful in optimizing the database environment and can assist in the early detection of errors.

A combination of primary key and foreign key constraints can be used to help enforce referential integrity of the database. In addition to a foreign key referring to a primary key constraint, a foreign key can also refer to a UNIQUE constraint to help maintain referential integrity.

Normalizing a database

Normalization is the process of organizing data in a database that includes creating tables and establishing relationships between the tables. The following normalization forms are used to help eliminate redundant data.

Five normalization forms (NFs)

• 1NF: Eliminate repeating groups

• 2NF: Eliminate redundant data

• 3NF: Eliminate columns not dependent on key

• 4NF: Isolate independent multiple relationships

• 5NF: Isolate semantically related multiple relationships