relational database

Databases have formatted records that allow indexing and searching. Databases {relational database} can have different-format records and relations among formats.

tables

Relational databases store data in two-dimensional tables. For example, table can be about people in a group. Rows are about one object or event, such as person. Columns are fields, properties, classes, or data categories, such as person names and weights.

tables: columns

Columns have value formats. Column values can be numbers, character strings, bitmaps, times, dates, or monetary amounts. Column values can have fixed or variable number of characters. Columns can be empty or require values. Column values can be unique or the same. Column values can be another-column values. Column values can be any sequence value, such as 1 to n.

tables: key column

Tables typically have main column {primary key}. For example, in Person table, primary key is person name or ID. Several columns can link to make primary key.

tables: index

Columns can have indexes. Indexes assign table-row number to column value. Columns can require that each value occur only once or allow values to repeat. For example, person names occur only once, but the same weight can apply to several people. It takes time to make indexes, but indexing speeds searches. Indexing is more efficient if table updates are infrequent.

tables: relations

Two tables cross-reference each other, if they both have same column. For example, Person table can have PersonID column and WeightCategory column. Weight table can have WeightCategory column and WeightAmount category. Both tables share WeightCategory column. Database queries that use both tables can yield WeightAmount for PersonID. WeightCategory column in weight table is reference {foreign key} for WeightCategory column in Person table. Database can allow only weight categories listed in weight table to be values for WeightCategory column in Person table.

trigger

Inserting, deleting, or updating table values can cause {trigger, database} actions on database tables. For example, if Person table lists all people and their groups and if group table lists group members, adding person and his or her group to Person table can automatically add person to Group table.

normal forms

Theory governs table structure in relational databases. Five principles {normal form, database} improve performance speeds and prevent update problems, data redundancies, and data inconsistencies. Tables and records typically have normal forms {normalization, database}, unless performance considerations require non-normalization.

normal forms: First Normal Form

Table records have same number of non-repeating fields. For example, people in Person table have names, weights, and heights, even if some people have no known height values.

normal forms: Second Normal Form

Tables have one primary key. In Person table, name field is primary key, because it identifies person uniquely. Other fields state facts about primary key, such as weight and height, and are not unique.

normal forms: Third Normal Form

Columns are independent of other columns. There is only one column about each property. For example, in Person table, there are not two columns about weight, one for kilograms and one for pounds, or one for weight numbers and one for weight categories. For weights, there can be a Weight table. Non-key fields cannot be subsets of, or have another relation to, primary keys. For example, Person table has name column and does not have nickname column. For nicknames, there can be a nickname table.

normal forms: Fourth Normal Form

Row and column cells have one value, not multiple values. In Person table, name column has only best name, not alternative names, and language column has only preferred language, not all languages spoken. For nicknames, there can be a nickname table. For languages, there can be a language table. In Person table, each column is only about one property, not multiple properties. For example, Person table does not have a name-language column. For names and languages, there can be a language table.

normal forms: Fifth Normal Form

If two columns relate, and relation does not have direction {symmetric constraint}, use several tables with as few fields as possible, rather than fewer tables with more fields. For example, to relate person, weight amount, and weight category, Person table has name and weight category columns, and weight table has weight amount and weight category. Person table does not have person, weight amount, and weight category columns. For asymmetric constraints, database must have two tables.

tablespace

Single files {tablespace} can store related tables. Tablespaces represent shared columns only once, so memory size and computer processes decrease and processing speed increases.

Tablespace tables {cluster} {data cluster} can share columns. Clusters place related information in one physical location and allow overlapping indexes. Clustering works well for adding data to tables but is not good for tables that have many deletions or updates.

Whole Section in One File

3-Computer Science-Software-Database

Drawings

Technical Information

Date Modified: 2022.0224