Different Integrity Rules in Relational Model:

Relational database integrity rules are very important to good database design. Many (but by no means all) RDBMSs enforce integrity rules automatically. However, it is much safer to make sure that your application design conforms to the entity and referential integrity rules.

Those rules are summarized as follows

Entity Integrity:

Requirement: All primary key entries are unique, and no part of a primary key may be null.

Purpose: Each row will have a unique identity, and foreign key values can properly reference primary key values.

Example: No invoice can have a duplicate number, nor can it be null. In short, all invoices are uniquely identified by their invoice number.

Read more ...

Relational Set Operators:

Relational algebra defines the theoretical way of manipulating table contents using the eight relational operators: SELECT, PROJECT, JOIN, INTERSECT, UNION, DIFFERENCE, PRODUCT, and DIVIDE.

The relational operators have the property of closure; that is, the use of relational algebra operators on existing relations (tables) produces new relations.

1. SELECT, also known as RESTRICT, yields values for all rows found in a table that satisfy a given condition. SELECT can be used to list all of the row values, or it can yield only those row values that match a specified criterion. In other words, SELECT yields a horizontal subset of a table.

2. PROJECT yields all values for selected attributes. In other words, PROJECT yields a vertical subset of a table.

Read more ...

Codd’s Relational Database Rules:

In 1985, Dr. E. F. Codd published a list of 12 rules to define a relational database system.2 The reason Dr. Codd published the list was his concern that many vendors were marketing products as “relational” even though those products did not meet minimum relational standards. Dr. Codd’s list, shown in the following Table 3.8, serves as a frame of   reference for what a truly relational database should be. Bear in mind that even the dominant database vendors do not fully support all 12 rules.

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.

Read more ...

Types of Entities in Relational Data Model:

Entity:

An Entity is an object of interest to the end user. An entity actually refers to the entity set and not to a single entity occurrence. In other words, the word entity in the ERM corresponds to a table—not to a row—in the relational environment. The ERM refers to a table row as an entity instance or entity occurrence. In both the Chen and Crow’s Foot notations, an entity is represented by a rectangle containing the entity’s name. The entity name, a noun, is usually written in all capital letters. Different types of Entities are as follows.

Read more ...

Attributes and Types Of Attributes

Attributes are characteristics of entities. For example, the STUDENT entity includes, among many others, the attributes STU_LNAME, STU_FNAME, and STU_INITIAL. In the original Chen notation, attributes are represented by ovals and are connected to the entity rectangle with a line. Each oval contains the name of the attribute it represents. In the Crow’s Foot notation, the attributes are written in the attribute box below the entity rectangle.

Required and Optional Attributes

Read more ...

Attributes and Types of Attributes

Attributes are characteristics of entities. For example, the STUDENT entity includes, among many others, the attributes STU_LNAME, STU_FNAME, and STU_INITIAL. In the original Chen notation, attributes are represented by ovals and are connected to the entity rectangle with a line. Each oval contains the name of the attribute it represents. In the Crow’s Foot notation, the attributes are written in the attribute box below the entity rectangle.

Required and Optional Attributes

A required attribute is an attribute that must have a value; in other words, it cannot be left empty. As shown in the above Figure, there are two boldfaced attributes in the Crow’s Foot notation. This indicates that a data entry will be required.
In this example, STU_LNAME and STU_FNAME require data entries because of the assumption that all students have a last name and a first name.

Read more ...

Anomalies in DBMS

"What does anomaly mean?". The dictionary defines anomaly as “an abnormality.”

Ideally, a field value change should be made in only a single place.

Data redundancy, however, fosters an abnormal condition by forcing field value changes in many different locations.

Anomalies in DBMS develops when not all of the required changes in the redundant data are made successfully.

The Different Anomalies in DBMS:

1. Anomalies in DBMS: Update Anomalies

If agent Leah F. Hahn has a new phone number, that number must be entered in each of the CUSTOMER file records in which Ms. Hahn’s phone number is shown. In this case, only three changes must be made. In a large file system, such a change might occur in hundreds or even thousands of records. Clearly, the potential for data inconsistencies is great.

2. Anomalies in DBMS: Insertion Anomalies

If only the CUSTOMER file existed, to add a new agent, you would also add a dummy customer data entry to reflect the new agent’s addition. Again, the potential for creating data inconsistencies would be great.

3. Anomalies in DBMS: Deletion Anomalies

If you delete the customers Amy B. O’Brian, George Williams, and Olette K. Smith, you will also delete John T. Okon’s agent data. Clearly, this is not desirable.

Other Important DBMS Questions

Join and types of Joins

JOIN allows information to be combined from two or more tables. JOIN is the real power behind the relational database, allowing the use of independent tables linked by common attributes. The CUSTOMER and AGENT tables shown in the following Figure will be used to illustrate several types of joins.

Read more ...