The Business Intelligence Guide
   BI Strategy | BI Program | BI Projects | BI Data | BI Infrastructure | BI User Tools | BI Vendors | Resource Guides | Articles | BI Blog | BIG Bookstore

Get a FREE Sample of the
The Total BI Guide
THE LOGICAL ORGANIZATION

and receive the
TLO NEWSLETTER
Just enter your details below

Email:
Name:
 
HOME
 
Business Intelligence
BI Strategy
BI Program Guide
BI Tools
- Dashboards
- Scorecards
- Operational BI
- Analytics
BI Software Solutions
Data Management
Databases
DataWarehouses
Decision Support
Marketing Tools
Industry Solutions
Case Studies
BI Surveys & Awards
RESOURCES
ARTICLES
BI EVENTS
BI NEWS
Sitemap

About the Author

View Gail La Grouw's profile on LinkedIn

Google+ Gail La Grouw

Bookmark and Share

Relational Database Models


Three key terms are used extensively in relational database models are:

  • Relation - a table with columns and rows.
  • Attributes – the named columns of the relation
  • Domain - the set of values the attributes are allowed to take.

The basic data structure of the relational model is the Table, where information about a particular entity [employee] is represented in columns and rows [tuples].

The Columns organise the various attributes of the entity [the employee's name, address, phone number].

A Row is an actual instance of the entity [a specific employee] that is represented by the relation. As a result, each tuple of the employee table represents various attributes of a single employee.

All relations [tables] in a relational database have to adhere to some basic rules to qualify as relations:

  1. The ordering of columns is immaterial in a table.
  2. There cannot be identical tuples or rows in a table.
  3. Each tuple will contain a single value for each of its attributes.

A relational database contains multiple tables, each similar to the one in the "flat" database model.

 

Constraints

One of the strengths of the relational model is that, in principle, any value occurring in two different records (belonging to the same table or to different tables), implies a relationship among those two records. To enforce explicit integrity constraints, relationships between records in tables can also be defined explicitly, by identifying or non-identifying parent-child relationships characterized by assigning cardinality (1:1, (0)1:M, M:M).

Tables can also have a designated single attribute or a set of attributes that can act as a "key", which can be used to uniquely identify each tuple in the table.

 

Keys

A key that can be used to uniquely identify a row in a table is called a primary key. Keys are commonly used to join or combine data from two or more tables. For example, an Employee table may contain a column named Location which contains a value that matches the key of a Location table. Keys are also critical in the creation of indices, which facilitate fast retrieval of data from large tables. Any column can be a key, or multiple columns can be grouped together into a compound key. It is not necessary to define all the keys in advance; a column can be used as a key even if it was not originally intended to be one.

 

Relational Operations

Users [or programs] request data from a relational database by sending it a query [written in SQL]. For instance, dynamic web sites perform SQL queries to an SQL database of content to generate pages.

In response to a query, the database returns a result set; a list of rows containing the result of the query. Often, data from multiple tables are combined into one [a join]. There are a number of relational operations in addition to join.

True Relational DBMS use the Structured Query Language (SQL) to extract and update data and conform to relational rules of normalisation. Oracle, Sybase, Informix etc are examples.

Work best when the data structures have been "normalised" to eliminate data and field duplication.

Data is organised within "Tables" (files) and relationships expressed between tables and data elements.

Note: a system using a Relational DBMS, does not mean that the data structures have been properly defined in the first place. SQL is now the industry standard for data querying and updating of databases. Relational DBMS lend themselves very well to the library concepts of authority files.

 

Advantages Of Relational Models

The main advantages of a relational database model include:

  • The most popular type of DBMS in use and as a result technical development effort ensures that advances e.g. object orientation, web serving etc appear quickly and reliably.
  • There are many, many third party tools such as report writers that are tuned to work with the popular Relational DBMS via standards such as Open Database Connectivity (ODBC).
  • Offer distributed database and distributed processing options which might be advantageous for some large consortium libraries.
  • Extremely well developed management tools and security with automatic data logging and recovery.
  • Have Referential integrity controls ensure data consistency.
    Have Transactional integrity features to ensure that incomplete transactions do not occur.

 

Disadvantages

The main disadvantages of a relational database model include:

  • Older Relational DBMS were slow; newer versions do not have performance problems.
  • Some restrictions in field lengths. Field lengths are usually defined with a maximum. This can lead to occasional practical problems e.g. a publisher with a 300 character name - they are rare but it can happen!
  • SQL does not provide an efficient way to browse alphabetically through an index. Thus some systems cannot provide a simple title A-Z browse.

See Microsoft SQL Server

 

NEXT: Object Database Models

 

Back To Top

For The World's Leading Guide To BI Strategy, Program & Technology


Database Index | Relational Model | Object Model | Other Models | DBMS | Contextual Types | Microsoft SQL | Oracle OODBMS | Data Warehouse | Teradata ADW | Data Mart | MS vs Teradata | Development Platforms | ODBC | JDBC | SMP | MPP | SN | Glossary

Bookmark and Share
 


SALES ANALYTICS GUIDE

SELL MORE & HAVE YOUR CUSTOMERS LOVE YOU FOR IT

Power up your pipeline profit and manage sales performance in less time with less stress.
Sell More & Have Your Customers Love You For It

Now Also Available in
EBOOK VERSION

Find out more