Relational Model

The relational database model was first introduced by E.F. Codd of IBM Research in 1970 in a classic paper, and attracted immediate attention due to its simplicity and foundation. The model uses tables as its basic building blocks. A table in the relational database model is also called relation and is made up of named attributes (columns) of data. The relation has a tuple (row) which contains one value per attribute.

     The relational model resembles a table of values or, to some extent a flat file of records. When a relation is thought of as table of values, each row in the table represents a collection of related data values.

 

A Logical View of Data

 Tables and their characteristics

The logical view of the relational database is facilitated by the creation of data relationships based on a logical construct known as tables. A table is perceived as a two-dimensional structure composed of rows and columns. The RDBMS requires only that the database be perceived by the user as tables.

In the relational model, relations are used to hold information about the objects to be represented in the database. In the relational, each row in the table represents a fact that typically corresponds to an entity or relationship. The table name and column names are used to help to interpret the meaning of values in each row.

 

A relation is represented as a two-dimensional table which the rows of the table correspond to individual records and the table columns correspond to attributes.  Attributes are named columns of a relation.

Domains are powerful feature of the relational model. Domain is the set of allowable values for one or more attributes. A domain is a set of atomic values. Atomic means that each value in the domain is inseparable. Specifying a data type is a common method in specifying a domain. Specifying a name for the domain is also useful to help interpret its values (e.g. Area_code. The set of four-digit numbers valid in the Philippines, Last_Name. The set of character strings that represent the last name of a person.)

 

The elements of a relation are those rows or tuples in the table. The degree of a relation is the number of attributes it contains. The cardinality of a relation is the number of tuples it contains.

Relational Model

Relational Model

 

Properties of Relations

  • The name of the relation is distinct from all other relation names in the relational schema;
  • Only one atomic (single) value the relation cell contains;
  • Each attribute has a distinct value;
  • The values of an attribute are all from the same domain;
  • There are no duplicate tuples;
  • The order of the tuples in the relation 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

A relational key consists of one or more attributes that determine other attributes.  Relational key uniquely identifies each tuple in the relation.

PRIMARY KEY

It uniquely identifies a tuple within the relation. A relation should not have a duplicate tuple therefore; it is easy to identify each row uniquely. This simply means that every relation should have a primary key.  In a relation which has two possible primary keys, for example Employee_Number and Postal_Code. If the selected primary key is the Employee_Number, then the remaining key which is the Postal_Code is no longer called a primary key but an alternate key. In choosing a primary key for a relation, the attribute which will have a unique value should always be considered.

 

FOREIGN KEY

When an attribute appears in more than one relation, its appearance usually represents a relationship between tuples of two relations. For example, the Employee relation and the Branch relation relate with each other through the use of a foreign key. Say, the Employee relation’s primary key is Employee_Number and the primary key of the Branch relation is the Branch_Number. If the Branch_Number key can be found in the Employee relation, then it can no longer be called a primary key for the Employee relation has its primary key already, instead it is called to be the foreign key of the employee relation.

 

 Integrity Rules

Relational database integrity rules are important to a good database design.

 1. Entity integrity exists when each primary key within a table has a value that is unique. this ensures that each row is uniquely identified by the primary key. One requirement for entity integrity is that a primary key cannot have a null value. The purpose of this integrity is to have each row to have a unique identity, and foreign key values can properly reference primary key values. For example, in creating a primary key, preventing the primary key from accepting null values ensures that you can distinguish one record from another. In the entity integrity, no attribute of a primary key can be null.

 

Null represents a value for an attribute that is currently unknown or is not applicable for this tuple. 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 way to deal with incomplete or exceptional data. A null is not the same as zero numeric value or a text string filled with spaces for they are considered as values, null represents the absence of a value.

 

2. Referential Integrity exists when a foreign key contains a value that value refers to an existing tuple/row in another relation. The purpose of referential integrity is to make it impossible to delete a row in one table whose primary key has mandatory matching foreign key values in another table.

 

Other Definitions from the Web

  • Referential integrity is a database constraint that ensures that references between data are indeed valid and intact. Referential integrity is a fundamental principle of database theory and arises from the notion that a database should not only store data, but should actively seek to ensure its quality.
  • Referential integrity in a relational database is consistency between coupled tables. Referential integrity is usually enforced by the combination of a primary key and a foreign key. For referential integrity to hold, any field in a table that is declared a foreign key can contain only values from a parent table’s primary key field.
  •  Referential integrity is a feature provided by relational database management systems (RDBMS’s) that prevents users or applications from entering inconsistent data. Most RDBMS’s have various referential integrity rules that you can apply when you create a relationship between two tables.
  • Referential integrity is a database management safeguard that ensures every foreign key matches a primary key. For example, customer numbers in a customer file are the primary keys, and customer numbers in the order file are the foreign keys. If a customer record is deleted, the order records must also be deleted; otherwise they are left without a primary reference. If the DBMS does not test for this, it must be programmed into the applications.

 

There are many benefits of defining referential integrity in a database.

  • Improved data quality. An obvious benefit is the boost to the quality of data that is stored in a database. There can still be errors, but at least data references are genuine and intact.
  • Faster development. Referential integrity is declared. This is much more productive (one or two orders of magnitude) than writing custom programming code.
  • Fewer bugs. The declarations of referential integrity are more concise than the equivalent programming code. In essence, such declarations reuse the tried and tested general-purpose code in a database engine, rather than redeveloping the same logic on a case-by-case basis.
  • Consistency across applications. Referential integrity ensures the quality of data references across the multiple application programs that may access a database.

 

Referential Integrity and RDBMSs

The SQL syntax for defining referential integrity looks essentially like the following. The words in capital letters denote keywords. The foreign key columns are in table1 and the primary key (or other unique combination of columns) is in table2.

ALTER TABLE tableName1

ADD CONSTRAINT constraintName

FOREIGN KEY (columnList)

REFERENCES tableName2 (columnList);

 

               In SQL a foreign key can refer to any unique combination of columns in the referenced table. If the referenced column list is omitted, the foreign key refers to the primary key. The SQL standard provides the following referential integrity actions for deletions.

  • Cascade. The deletion of a record may cause the deletion of corresponding foreign-key records. For example, if you delete a company, you might also want to delete the company’s history of addresses.
  • No action. Alternatively, you may forbid the deletion of a record if there are dependent foreign-key records. For example, if you have sold products to a company, you might want to prevent deletion of the company record.
  • Set null. The deletion of a record may cause the corresponding foreign keys to be set to null. For example, if there is an aircraft substitution on a flight, you may want to nullify some seat assignments. (These passengers must then request other seat assignments.)
  • Set default. You may set a foreign key to a default value instead of to null upon deletion of a record.

Referential integrity enforces the following three rules:

1. We may not add a record to the Employees table unless the ManagedBy attribute points to a valid record in the Managers table.

2. If the primary key for a record in the Managers table changes, all corresponding records in the Employees table must be modified using a cascading update.

3. If a record in the Managers table is deleted, all corresponding records in the Employees table must be deleted using a cascading delete.

RELATIONAL SET OPERATORS

Relational Set Operator uses relational algebra to manipulate contents in a database. All together there are eight different types of operators. These operators are SQL commands.

 1. UNION. It combines all of the rows in one table with all the rows in another table except for the duplicate tuples/rows. The tables are required to have the same attribute characteristics for the Union command to work. The tables must be union-compatible which means that two tables being used have the same amount of columns and the columns have the same names, and also need to share the same domain.

2. INTERSECT. It is the second SQL command that takes two tables and combines only the rows that appear in both tables. The tables must ne union-compatible to be able to use the Intersect command or else it won’t work.

3. DIFFERENCE. It is another SQL command that gets all rows in one table that are not found in the other table. Basically it subtracts one table from the other table to leave only the attributes that are not the same in both tables. For this command to work both table must be union-compatible.

4. PRODUCT. It is a command would show all possible pairs of rows from both tables being used. This command can be referred to as the Cartesian product.

5. SELECT. It is the command to show all rows in a table. It can be used to select only specific data from the table that meets certain criteria. This command is also referred to as the Restrict command.

6. PROJECT. It is the command that gives all values for certain attributes specified after the command. It shows a vertical view of the given table.

7. JOIN. It takes two or more tables and combines them into one table. This can be used in combination with other commands to get specific information. There are several types of Join command. The Natural Join, Equijoin, Theta Join, Left Outer Join and Right Outer Join.

8. DIVIDE. It has specific requirements of the table. One of the tables can only have one column and the other must have two columns only.

The Data Dictionary and the Set Catalog

A data dictionary is a collection of descriptions of the data objects or items in a data model for the benefit of programmers and others who need to refer to them. A first step in analyzing a system of objects  with which users interact is to identify each object and its relationship to other objects. This process is called data modeling and results in a picture of object relationships. After each data object or item is given a descriptive name, its relationship is described (or it becomes part of some structure that implicitly describes relationship), the type of data (such as text or image or binary value) is described, possible predefined values are listed, and a brief textual description is provided. This collection can be organized for reference into a book called a data dictionary.

When developing programs that use the data model, a data dictionary can be consulted to understand where a data item fits in the structure, what values it may contain, and basically what the data item means in real-world terms. For example, a bank or group of banks could model the data objects involved in consumer banking. They could then provide a data dictionary for a bank’s programmers. The data dictionary would describe each of the data items in its data model for consumer banking (for example, “Account holder” and “”Available credit”).

Relationships within the Relational Databases

  • The I:M Relationship

A one-to-many relationship is the most common type of relationship. In one-to-many relationship, a record in Table A can have matching records in Table B, but a record in Table B has one matching record in Table A. A one-to-many relationship is created if only one of the related fields is a primary key or has a unique index.

One to Many Relationship

One to Many Relationship

The I:I Relationship

In a one-to-one relationship, each record in Table A can have only one matching record in Table B and each record in Table B can have only one matching record in Table A. This type of relationship is not common, because most information related in this way would be in one table.

One to One Relationship

One to One Relationship

 

  • The M:N Relationship

In a many-to-many relationship, a record in Table A can have many matching records in Table B, and a record in Table B can have many matching records in Table A. This type of relationship is only possible be defining a third table (called a junction table) whose primary key consists of two fields – the foreign keys from both Tables A and B. A many-to-many relationship is really two one-to-many relationships with a third table.

Many to Many Relationship

Many to Many Relationship

 

Codds 12 Relational Database Rules

RULE RULE NAME DESCRIPTION
1 Information All information in a relational database must be logically represented as column values in rows within tables.
2 Guaranteed Access Every value in a table is guaranteed to be accessible through a combination of table name, primary key value, and column name.
3 Systematic Treatment of Nulls Nulls must be represented and treated in a systematic way, independent data type.
4 Dynamic On-line Catalog Based on the Relational Model The metadata must be stored and managed as ordinary data, that is, in tables within the database. Such data must be available to authorized users being the standard database relational language.
5 Comprehensive Data Sublanguage The relational database may support many languages. However it must support one well-defined declarative language with support for data definition, view definition, data manipulation (interactive and by program), integrity constraints, authorization, and transaction management (begin, commit, and rollback)
6 View Updating Any view that is theoretically updatable must be updatable through the system
7 High-level Insert, Update and Delete The database must support set-level inserts, updates, and deletes
8 Physical Data Independence Application programs and ad hoc facilities are logically unaffected when physical access methods or storage structures are changed.
9 Logical Data Independence Application programs and ad hoc facilities are logically unaffected when changes are made to the table structures that preserve the original table values (changing order of column or inserting columns)
10 Integrity Independence All relational integrity constraints must be definable in the relational language and stored in the system catalog, not at the application level
11 Distribution Independence The end users and application programs are unaware and unaffected by the data location (distributed vs. local databases)
12 Non-subversion If the system supports low-level access to the data, there must not be a way to bypass the integrity rules of the database
Rule Zero All preceding rules are based on the notion that in order for a database to be considered relational, it must use its relational facilities exclusively to manage the database.

 

Download Link: http://www.liezlabuzo.com/downloads/CHAPTER3_dbms.docx