User talk:Ghytred/Relational database

A Relational database is a computer database whose organisation is based on the relational model of data as proposed by [[E.F.Codd] in 1970. The main type of database current at that time were based on the [Hierarchical database model|hierarchical model], but they were generally replaced by the more flexible relational database.

Within a Relational database complex objects, such as an invoice, are broken down into simpler ones, such as an Invoice Header (containing Invoice number, customer, invoice date, due date, invoice total) and Invoice Line (Product, quantity, individual price, discount). This process is repeated until the required level of simplicity and atomicity is reached according to a process called Normalisation. Each resulting object is called a Table (or Relation in the formal relational model); the data items in a table are called Columns (formally, an Attribute); and each individual entry in the table is a row (formally, a Tuple). Each row in a table will have a column or set of columns which uniquely identify it known as the Primary Key. Each Table which is linked to another one will have a reference to it so that an Invoice Line will include the Primary Key of the Invoice Header (the Invoice Number) as a column; this column is known as a Foreign Key.

Software Systems used to maintain relational databases and known as Relational Database Management Systems (RDBMs). Virtually all of them use Structured Query Language (SQL) to query and maintain their databases. There are a number of suppliers of RDBMs ranging from large corporations (Microsoft, Oracle) to Open Source.

The main competitor the relational databases currently (2015) are various NoSQL database systems.

Overview

Each database is a collection of tables, which are called relations. Each table is a representation of an entity or object that is in a tabular format consisting of columns and rows. Columns are the fields of a record or the attributes of an entity. The rows contain the values or data instances; these are also called records or tuples. Each table is named according to the data it contains, such as people or addresses.

The data in a database is maintained by a program or user sending it commands. Using SQL, there are four basic commands: INSERT, UPDATE, DELETE, and SELECT. These commands operate directly on the rows and columns of the database. Databases also contain stored procedures (SPs) which can be called by programs and users. Often procedures can be used to greatly reduce the amount of information transferred within and outside of a system. For increased security, the system design may also grant access to only the stored procedures and not directly to the tables. Fundamental stored procedures contain the logic needed to insert new data and update existing data. More complex procedures may be written to implement additional rules and logic related to processing or selecting the data. These procedures are written using SQL Commands plus scripting languages defined by the supplier of the RDBMs (T/SQL for SQL Server from Microsoft, PL/SQL for Oracle).

A database can also contain Triggers which the RDBMs will run when certain pre-defined events happen. A standard use of Triggers to log any changes to a table to a separate Audit table for traceability, or perform validation when certain data items are changed and prevent the change if the validation fails.

Tables

There are relationships between tables; these take three logical forms: one-to-one, one-to-many, or many-to-many:

• A Capital City may belong to only one Country and a Country has only one Capital City
• An Invoice may have many Invoice Lines and An Invoice Line can only belong to one Invoice
• A Book may have many Authors and a Author may have many Books

These restrictions are enforced by the RDBMs, and are set by the designer of the database using Foreign Keys and Indexes. Such restrictions are not necessarily matched in the real world: it is possible to imagine a situation in which two countries may claim the same city as their capital; this situation would not be capable of being entered into a database enforcing a one-to-one relationship. Database designers do not necessarily represent the real world in their databases: they include only what the system they are designing requires, and occasionally do not model rare circumstances due to the complexity (and hence cost) introduced by the circumstance outweighing the likelyhood of that circumstance.

Each table usually have a Primary Key (PK). This is a column (or fixed set of columns) which uniquely identifies each row. That is, if InvoiceNumber is the PK of table Invoice, then table Invoice will only allow one row to have InvoiceNumber 1234; if another row with the same invoice number is added then that attempt to add a row will fail.

References

Category:Database theory Category:Types of databases Category:English inventions Category:1969 introductions