Relational Model

Created on Jan. 18, 2013, 6:13 p.m. by Hevok & updated by Hevok on May 2, 2013, 5:06 p.m.

The relational model has a long history and is the foundation of the Database Management Systems. It is a very simple model which is one of its benefits.

Further, it can be queried, which means one can ask questions of databases in the model using High Level Languages. High Level Languages are simple, yet extremely efficient implementations of the relational model and of the query query languages on that model.

  • Used by all database systems
  • Very simple model
  • Query with high-level languages: simple yet expressive
  • Efficient implementations

The primary construct in the relational model is in fact the Relation. A database consists of a set of Relations (refereed to as "Tables"), each of it has a name.

Table Relations shall be named using singular, rather than plural names (just for the sake of conventions).

Every Relation in a relational database has a predefined set of of columns or attributes each of which has a name or at least an unique ID.

The actual data is stored in tuples (or the rows) in the tables.

  • Database = set of names Relations (or Tables)
  • Each Relation has a set of names Attributes (or Columns)
  • Each Tuple (or Row) has a value for each Attribute
  • Each attribute has a Type (or Domain)

In each column in a relational database, typically each attribute or column has a type (sometimes referred to as a domain). For instance, the ID might be an integer, the name might be a string, a number might be float and photo might be a jpeg file.

There is also the concept of enumerated domain. For example country or state might be a enumerated domain for the abbreviations for countries/states.

It is typical to have just atomic types in relational database, but many database systems do also support structured types inside attributes.

A schema of a database is the structure of the Relations. In such the schema includes the name of the relation and the attributes of the relation and the types of those attributes, where the instance is the actual contents of the table at a given point in time. Typically the schema is set up in advance, then the instances of the data will Change over time.

Besides that most columns have types, there is also a special value that is Null. Nulls are very important in relational databases. Null values are used to denote that a particular value is maybe unknown undefined.

It need to be carefully when queries are run over Relations that have null values. Greater than and smaller than queries do exclude rows with Null as value if it queried on exactly on this very attribute.

Key is an important key concept in relational databases. An key is an attribute in relation where every value for that attribute is unique. IDs are typically used as keys. In other cases combination of multiple attributes are used as keys. Thus the combination has to be unique. It is important to have attributes that are identified as keys. Keys are used to identify specific tuples.

If one want to run a query to get a specific tuple out of the database one would do that by asking for that tuple by its key. Related to that database systems for efficiency tend to build special index structures or store the database in a particular way. This it is very fast to find a tuple based on its key.

If one relation in a relational database wants to refer to tuples of another. There is no concept of pointer in relational databases. Therefore, the first relation will typically refer to a tuple in the second relation by its unique key.

  • Schema - structural description of Relations in database
  • Instance - actual contents at given point in time
  • NULL - special value for "unknown" or "undefined"
  • Key - attribute whose value is unique in each tuple (or set of attributes whose combined values are unique)

Creating a table in SQL language is very simple. One just says "CREATE TABLE", give the name of the relation and a list of attributes as well as types for the attributes if desired. Its attribute name is followed by its type.

CREATE TABLE Entry(ID, title, text, image)

CREATE TABLE Institute(name string, country char(2), labs integer)

Tags: coding, programming, database, data
Categories: Tutorial, News, reST
Parent: DBs
Children: Querying Relational Databases, Steps in Creating and Using a Relational Database

Update entry (Admin) | See changes

Comment on This Data Unit