Relational Database Schemas: Types and Examples

The compilation of these Structuring Database for Accounting Notes makes students exam preparation simpler and organised.

Relational Databases and Schemas

A database is a collection of interrelated data files or structures. It is designed to meet the various information needs of the organization. Also, it is integrated and shared. Thus, a relational database schema is an arrangement of relation states in such a manner that every relational database state fulfills the integrity constraints set on a relational database schema.

Relational Database and Schemas

As we know that a relational database schema is an arrangement of integrity constraints. Thus, in the context of relational database schema following points deserve a particular consideration:

1. A specific characteristic, that bears the same real-world concept, may appear in more than one relationship with the same or a different name. For example, in the Employees relation, Employee Id (EmpId) is represented in Vouchers as AuthBy and PrepBy.

2. The specific real-world concept that appears more than once in a relationship should be represented by different names. For example, an employee is represented as subordinate or junior by using EmpId and as a superior or senior by using SuperId, in the employee’s relation.

3. The integrity constraints that are specified on the database schema shall apply to every database state of that schema.

Relational Databases and Schemas

Constraints and Database Schemas

Domain constraint, Key constraint, Entity integrity constraint, and Referential integrity constraint are the four different constraints of the relational databases. Let us now discuss them in detail.

1. Domain constraint: The value of each characteristic of a relationship needs to be an indivisible value and we need to draw it out of the possible values corresponding to its domain. Thus, the value of a character needs to adjust to the data type corresponding to the domain.

2. Key constraints and Null values: Each data record that relates to a tuple of a relation in a table needs to be distinct. Thus, this implies that no two rows or tuples in relation or table can have the same combination of values for their entire data item. Every relation has a super key by default and depicts uniqueness constraints. It is a combination of all the characteristics. Sometimes a relation can have more than just one key. Each such key is a candidate key. Out of these, we need to define one of the keys as the Primary Key.

3. Entity integrity constraint: This constraint states that the primary key value cannot be null as it defines the individual tuple in a relation. A null value indicates the failure to identify such tuples and thus it means that they are duplicates.

4. Referential integrity constraint: It is specified to maintain the consistency among the tuples of two or more relations.

Example:

Question:
Explain the operations and constraint violations?
Answer:
Updates and retrieval are the two categories of operations on the relational model. The basic types of updates are:

  1. Insert: We use this operation in order to add a new tuple in a relation. It is capable of violating any of the four constraints.
  2. Delete: We perform this operation in order to remove or delete a tuple in a relation. Under this operation, we can remove a particular data record from a table. It can only violate the referential integrity constraint.
  3. Modify: This operation causes a change in the values of some characteristic of existing tuples or accounting data tables.

Retrieval constraints do not cause a violation of integrity constraints.