Relational Databases

 

 A relational database organises information through a series of related tables.

 

Relational databases are the most commonly used type of database.  A relational database consists of a collection of tables that store particular sets of data. The invention of the relational database system has standardized the way that data is stored and processed. Most of the database management systems used today are based on the relational system. Related databases have the following features:

 

Every table is divided into a series of records, which are comprised of fields in the same way as a flat file database. Every table has one of the fields designated as a key field which is used to sort, index and link the data between the tables. Reduced data redundancy due to normalisation of the data Reduced time to update data due to normalisation of the data Consistent reports because of validation and integrity of data Centralised security. Instead of data being split between several files it is all in one centralised file which is consistently updated. The schema shows an overall view of the data and of the relationships between the tables. Data can be viewed for different purposes according to the search, queries or reports that are run.

 

 

 

 Parts of a Relational Database

 

Entity: is a “topic” or specific thing about which information is being collected. For instance if it is a school database the entities (topics) about which information might be collected are such things as students, teachers and classes. If it was a flat file database we would probably have a separate table for each one.

 

Attribute: is a defined property of an entity. In the school example an attribute of student (which is an entity) might be name or student ID. Attributes are the same thing as fields in a flat file database.

 

Key field:Any field that can be used to sort the data is a "key field" as opposed to the primary key which is a designated attribute (field) which is used to sort but also to index and link one table to another. A key is an entity in a table that distinguishes one row of data from another. The key may be a single column, or it may consist of a group of columns that uniquely identifies a record. Tables can contain primary keys which differentiates records from one another.

 

Primary Keys: Primary keys are unique fileds. Primary keys can be an individual attribute, or a combination of attributes. A field which is a primary key must not be null.

 

Foreign Key: is the primary key of another table. In the example of a school database student ID may well come under loans in a library/ loans table as well as in its own student table. Foreign keys relate tables in the database to one another. A foreign key in one table is a primary key in another. The foreign keys generally define parent-to-child relationships between tables.

 

Table:  information about an entity is stored in tables. A table contains columns and rows. A row in a table is called a tuple of an entity and represents a record. A column of a table is also called the attribute. In a flat file database it is referred to as a field. In a relational database it may be called an attribute or field. Tables can be related to each other in a variety of ways. Functional dependencies are formed when an attribute of one table relates to attributes of other tables. The simplest relationship is the one-to-one relationship, in which one record in a table is related to another record in a separate table. A one-to-many relationship is one in which one record in a table is related to multiple records in another table. A many-to-one relationship defines the reverse situation; more than one record in a single table relates to only one record in another table. Finally, in a many-to-many relationship, more than one record in a table relates to more than one record in another table.

 

Relationship: This refers to the link between the various tables and is usually made through a primary key in the primary table linking through to a field in the secondary table. The relation is the critical element of storage in a relational database, which is a two-dimensional table. A relational database can contain two or more of these tables. Each table consists of a unique set of rows and columns. A single record is stored in a table as a row, also known as a tuple. Attributes of the data are defined in columns, or fields, in the table. The characteristics of the data, or the column, relates one record to another. Each column has a unique name and the content within it must be of the same type.

 

 

There are four types of relationships.

1.      One-to-One: Occurs when the Primary key in one table is linked to the Primary key in another table. This means that the primary key in both tables is identical and that exactly one row in one table is related to exactly one row in another table.

2.       One-to-Many: The one-to-many relationship is used to relate one record in a table with many records in another. This allows a customer to make more than one order and is the most common type of relationship.

3.      Many-to-One: This involves a lookup table taking the place of the primary key. For instance a customer may choose a state from a lookup table and that state will relate to many customers in the next table. Many one to many relationships can be reversed into many-to-one. For example many pets can have the same owner so if we had a lookup table with the pets’ names then they could be related to the owner in the next table.

4.      Many-to-Many: Think of it as a pair of one to many relationships between two tables. A patient can go to a hospital on many different dates so that forms a one-to-many relationship but at the same time on each date, many people can be brought into the hospital. This is also a one-to-many relationship. So an individual patient may visit the hospital on many dates, and on a given date, many patients may visit the hospital. Thus a pair of separate, two-way, one-to-many relationships creates a many-to-many relationship.

 

Normalisation :

Normalisation of data is the process of the reduction of duplication of data in a database or table. One of the positive aspects of a relational database is that duplication of data is almost unnecessary and should be kept to a minimum. Duplicated data is called redundant data. The data that is stored in tables are organized logically based on a particular purpose that minimizes duplication, reduces data anomalies, and reinforces data integrity. The process by which data is organized logically is called normalization. Normalization simplifies the way data is defined and regulates its structure. There are five forms in the normalization process with each form meeting a more stringent condition The first normal form, 1NF, has the least data redundancy while the fifth normal form, or 5NF, structures the data with the least anomalies and least redundancy. There is a trade-off with multiple form normalisation (5NF) taking longer to query the database as such for faster response times for some queries the database may need to retain a higher level of data redundancy.

 

The History of Relational Databases

The history of the relational database started with Codd’s 1970 paper, "A Relational Model of Data for Large Shared Data Banks". This theory stated that data should be independent of any hardware or storage system and allowed automatic navigation between the data elements. This meant that data would be stored in tables with relationships (links) between the different data sets, or tables.

Oracle Corporation developed the first commercial relational database in 1979. IBM followed this in 1982 with the SQL Data System. Microsoft was slow to jump in with SQL Server 4.2 in 1992. Oracle and Microsoft have the majority market share of commercial database products in use.

 

Timeline Relational Databases
1971	Codd’s 1970 paper, "A Relational Model of Data for Large Shared Data Banks"
1979	Oracle Corporation created the first commercial relational database in 1979
1982	IBM developed SQL Data System
1992	Microsoft was the last major company to jump in with SQL Server 4.2 in 1992

 

Today

The relational database management system (RDBMS), is the most common database format.

 

Related Topics: DBMS, The 5 Types of Information Systems, Expert Systems, TPS, DSS, ES, MIS, OAS, Characteristics, computerised methods, non-computerised methods, flat file databases, relational databases, data modelling, URLs, storyboards, searching, HTML, backups, security methods, encryption, firewalls, virus' and virus scanners, passwords and security levels, social and ethical issues, data accuracy, data integrity, data validation, data bias, data reliability, privacy, ownership and control of data, access to data, data warehousing, data mining, freedom of information, acknowledgement of data sources, issues related to new trends, sequential access, direct access, distributed databases, storage media, querying, operators, wildcards, sorting, SQL, biometrics and ethical issues, face recognition , information systems trivia