Relational Databases

Relational Databases

Structure

• In its model, the data is organized into tables. These tables are called relations.
• A row in a table represents a relationship among a set of values.
• Rows of relations are called tuples and the columns are called attributes.

Components of a table

1. Byte : A byte is a collection of eight bits and used to store a character.
2. Data Item : A data item is the smallest unit of named data. May consist of any number of bits or bytes. A data item represents one type of information and is often called field or data element.
3. Record : A record is a named collection of data items which represents a complete unit of information.
4. Table : A table is a named collection of all occurrences of a given type of record.

Some examples of common Relational Database Management Systems include Oracle database, Microsoft SQL Server, MYSQL, IBM DB2, IBM DB2 etc.

Out of all these, MySQL is Open Source implementations.

Properties of relation :

1. In any column of a table, all items are of the same kind whereas items in different columns may not be the same kind.
2. For a row, each column must have an atomic (indivisible) value and also for a row, a column cannot have more than one value.
3. All rows of a relation are distinct. That is, a relation does not contain two rows which are identical in every column.
4. There is no order maintained for rows inside a relation. That is , we cannot retrieve anything by saying that from row number x, column name is to be accessed.
5. The columns of a relation are assigned distinct names and the ordering of these columns is immaterial.

Terminology associated with RDBMS

1. Degree – The number of attributes in a relation determine the degree of a relation.
2. Cardinality – The number of tuples in a relation is called the cardinality of the relation.
3. Views – A view is a kind of table whose contents are taken from other tables depending upon a condition.

Keys

1. Primary key – Within a given relation, a set of one or more attributes having values that are unique within the relation and thus are able to uniquely identify that tuple, is known as the primary key of the relation
2. Candidate key — A candidate key is a column, or set of columns, in a table that can uniquely identify any database record without referring to any other data.
3. Alternate key – If two or more than two candidate keys are present, only one of them serves as the primary key. The rest of them are alternate keys.
4. Foreign Key – A foreign key is an attribute whose value is derived from the primary key of other table

Referential Integrity

It’s  a system of rules that a DBMS uses to ensure that relationships between records in related tables are valid, and that users don’t accidentally delete or change related data.

Referential Integrity is set only when all of the following are met :

1. The matching field from the primary table must be the primary or has a unique index.
2. The related fields must have the same data type.
3. Both tables  belong to the same database. If the tables are linked tables, they must be of same DBMS format, and you must open the database in which they are stored to set referential integrity.