Relational Data Model:-Brief History, Terminology in Relational Data Structure, Relations, Properties of Relations, Keys, Domains, Integrity Constraints over Relations

What is Relational Model?

Relational Model represents the database as a collection of relations. A relation is nothing but a table of values. Every row in the table represents a collection of related data values. These rows in the table denote a real-world entity or relationship.

The table name and column names are helpful to interpret the meaning of values in each row. The data are represented as a set of relations. In the relational model, data are stored as tables. However, the physical storage of the data is independent of the way the data are logically organized.

The History of the Relational Model

The relational model for database introduced by a gentleman named E.F. Codd. Codd developed the model in a 1970 research paper. His concepts were put into practice in the late 70's in an IBM product called System R. Subsequently, the Interactive Graphics Retrieval System (INGRES) was put in place at Berkeley University.

The main appeal to the relational model is its simplicity. Prior models (flat files, hierarchical, and network structures) were difficult to use and query. Codd's proposal won him a Turing Award in 1981 and it completely revolutionized data management. Relational databases are currently the most prevalent in industry today.

Terminology

Attribute: Each column in a Table. Attributes are the properties which define a relation. e.g., Student_Rollno, NAME,etc.
Tables – In the Relational model the, relations are saved in the table format. It is stored along with its entities. A table has two properties rows and columns. Rows represent records and columns represent attributes.
Tuple – It is nothing but a single row of a table, which contains a single record.
Relation Schema: A relation schema represents the name of the relation with its attributes.
Degree: The total number of attributes which in the relation is called the degree of the relation.
Cardinality: Total number of rows present in the Table.
Column: The column represents the set of values for a specific attribute.
Relation instance – Relation instance is a finite set of tuples in the RDBMS system. Relation instances never have duplicate tuples.
Relation key - Every row has one, two or multiple attributes, which is called relation key.
Attribute domain – Every attribute has some pre-defined value and scope which is known as attribute domain

Properties of Relations

A relation has the following properties:

The relation has a name that is distinct from all other relation names in the relational schema
Each cell of the relation contains exactly one atomic (single) value
Each attribute has a distinct name; n the values of an attribute are all from the same domain
Each tuple is distinct; there are no duplicate tuples
The order of attributes has no significance
The order of tuples has no significance, theoretically. (However, in practice, the order may affect the efficiency of accessing tuples.)

Relational Keys

Super key

An attribute, or set of attributes, that uniquely identifies a tuple within a relation.

A super key uniquely identifies each tuple within a relation. However, a super key may contain additional attributes that are not necessary for unique identification, and we are interested in identifying super keys that contain only the minimum number of attributes necessary for unique identification.

Candidate Key

A super key such that no proper subset is a super key within the relation.

A candidate key, K, for a relation R has two properties:

Uniqueness – in each tuple of R, the values of K uniquely identify that tuple .

Irreducibility – no proper subset of K has the uniqueness property.

There may be several candidate keys for a relation. When a key consists of more than one attribute, we call it a composite key .

The instance of a relation cannot be used to prove that an attribute or combination of attributes is a candidate key. The fact that there are no duplicates for the values that appear at a particular moment in time does not guarantee that duplicates are not possible.

However, the presence of duplicates in an instance can be used to show that some attribute combination is not a candidate key. Identifying a candidate key requires that we know the ‘real world’ meaning of the attribute(s) involved so that we can decide whether duplicates are possible. Only by using this semantic information can we be certain that an attribute combination is a candidate key

Primary Key

The candidate key that is selected to identify tuples uniquely within the relation.

Since a relation has no duplicate tuples, it is always possible to identify each row uniquely. This means that a relation always has a primary key. In the worst case, the entire set of attributes could serve as the primary key, but usually some smaller subset is sufficient to distinguish the tuples.

The candidate keys that are not selected to be the primary key are called alternate keys.

Foreign Key

An attribute, or set of attributes, within one relation that matches the candidate key of some (possibly the same) relation.

When an attribute appears in more than one relation, its appearance usually represents a relationship between tuples of the two relations.

Relational Integrity Constraints

Relational Integrity constraints in DBMS are referred to conditions which must be present for a valid relation. These Relational constraints in DBMS are derived from the rules in the mini-world that the database represents.
There are many types of Integrity Constraints in DBMS. Constraints on the Relational database management system is mostly divided into three main categories are:

Domain Constraints
Key Constraints
Referential Integrity Constraints

Domain Constraints

Domain constraints can be violated if an attribute value is not appearing in the corresponding domain or it is not of the appropriate data type.
Domain constraints specify that within each tuple, and the value of each attribute must be unique. This is specified as data types which include standard data types integers, real numbers, characters, Booleans, variable length strings, etc.

Example:

Create DOMAIN CustomerName
CHECK (value not NULL)

The example shown demonstrates creating a domain constraint such that CustomerName is not NULL

Key Constraints

An attribute that can uniquely identify a tuple in a relation is called the key of the table. The value of the attribute for different tuples in the relation has to be unique.

Example:

In the given table, CustomerID is a key attribute of Customer Table. It is most likely to have a single key for one customer, CustomerID =1 is only for the CustomerName =" Google".

CustomerID	CustomerName	Status
1	Google	Active
2	Amazon	Active
3	Apple	Inactive

Referential Integrity Constraints

Referential Integrity constraints in DBMS are based on the concept of Foreign Keys. A foreign key is an important attribute of a relation which should be referred to in other relationships. Referential integrity constraint state happens where relation refers to a key attribute of a different or same relation. However, that key element must exist in the table.

Example:

In the above example, we have 2 relations, Customer and Billing.

Tuple for CustomerID =1 is referenced twice in the relation Billing. So we know CustomerName=Google has billing amount $300

Nulls

Null Represents a value for an attribute that is currently unknown or is not applicable for this tuple.

A null can be taken to mean the logical value ‘unknown’. It can mean that a value is not applicable to a particular tuple, or it could merely mean that no value has yet been supplied. Nulls are a way to deal with incomplete or exceptional data. However, a null is not the same as a zero numeric value or a text string filled with spaces; zeros and spaces are values, but a null represents the absence of a value.

Therefore, nulls should be treated differently from other values. Some authors use the term ‘null value’, however as a null is not a value but represents the absence of a value, the term ‘null value’ is deprecated.

Views

In the relational model, the word ‘view’ has a slightly different meaning. Rather than being the entire external model of a user’s view, a view is a virtual or derived relation: a relation that does not necessarily exist in its own right, but may be dynamically derived from one or more base relations.

Thus, an external model can consist of both base (conceptual-level) relations and views derived from the base relations .

Purpose of Views

It provides a powerful and flexible security mechanism by hiding parts of the database from certain users. Users are not aware of the existence of any attributes or tuples that are missing from the view.

It permits users to access data in a way that is customized to their needs, so that the same data can be seen by different users in different ways, at the same time.

It can simplify complex operations on the base relations. For example, if a view is defined as a combination (join) of two relations , users may now perform more simple operations on the view, which will be translated by the DBMS into equivalent operations on the join.

A view should be designed to support the external model that the user finds familiar. For example:

A user might need Branch tuples that contain the names of managers as well as the other attributes already in Branch. This view is created by combining the Branch relation with a restricted form of the Staff relation where the staff position is ‘Manager’.

Some members of staff should see Staff tuples without the salary attribute.

Attributes may be renamed or the order of attributes changed. For example, the user accustomed to calling the branchNo attribute of branches by the full name Branch Number may see that column heading.

Some members of staff should see only property records for those properties that they manage.

Updating Views

All updates to a base relation should be immediately reflected in all views that reference that base relation. Similarly, if a view is updated, then the underlying base relation should reflect the change.

However, there are restrictions on the types of modification that can be made through views. We summarize below the conditions under which most systems determine whether an update is allowed through a view:

Updates are allowed through a view defined using a simple query involving a single base relation and containing either the primary key or a candidate key of the base relation.

Updates are not allowed through views involving multiple base relations.

Updates are not allowed through views involving aggregation or grouping operations

Search This Blog

DBMS Tutorials