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.

Suggested Videos

 

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 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 database schema shall apply to every database state of that schema.

Learn more about the Entity-Relationship Model here in detail.

Image Source: stackoverflow

Browse more Topics Under Structuring Database For Accounting

• Data Processing Cycle and Database for Accounting
• Designing Relational Database Schema and Interaction with Databases
• Entity Relationship (ER) Model
• Relational Databases and Schemas
• Illustration of Accounting Database

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 characteristic 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 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.

Solved Example For You

Explain the operations and constraint violations?

Ans. 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.