Structuring Database for Accounting — Account STD 11 Commerce — Question

A database schema implies the structure and design of database. Database schema is regarded as a collective term for various properties of database such as, tables, constraints, relations, etc. A relational database schema consists of a set of relational database schemas and set of integrity constraints.
There are mainly four different integrity constraints that are imposed on a relational database. These are mentioned as:

1. Domain Constraints: These constraints in the database schema state the conditions that each relationa linstance must satisfy. The value of each attribute of a relation must be an indivisible value and must be drawn from the domain associated with that attribute. Hence, the value of an attribute must confirm to the data type associated with the domain.
2. Key Constraints and NULL Value: These constraints imply the existence of candidate keys. As per the Key Constraints, in every instance of a relational database schema, the tuples can be uniquely identified by their values for certain attributes. This implies that each data record corresponding to a tuple of relation in a table must be distinct. That is, in other words, no two tuples in a relation can have the same combination of values for all their attributes. Every relation has at least one key, which is the combination of all its attributes and is known as Super-Key. The Super-key specifies the uniqueness constraints.

However, a Super-key may contain some additional attributes that are not necessary for unique identification. Minimal super key is that part of super key from which removal of any attribute from the Super-key leaves a set of attributes that is not a Super-key. That is, if any attribute is removed, then the uniqueness constraint is disturbed. The Minimal Super-key is also known as Candidate key.

In case a relation has many Candidate keys, then out of them one is arbitrarily chosen and regarded as Primary key. The attributes of Primary key are represented as underlined in a schema. The other Candidate keys are regarded as Alternate or Unique keys.

3. Entity Integrity Constraints: A Primary key is used to identify individual tuple in a relation. As per the Entity Integrity Constraints, the value of Primary key cannot be null. If in case, the Primary key has null value, then it implies that we cannot identify such tuples as they are same. This implies that tuples are duplicated and the uniqueness constraints are violated.
4. Referential Integrity Constraints: Referential integrity constraint is specified between two or more relations to maintain consistency among the tuples of such relations. These are the constraints implies by the existence of foreign keys. Hence, a tuple in one relation that refers to another relation must refer to an existing tuple in that other relation.

The above mentioned integrity constraints are certain conditions that are imposed on a database schema. The importance of integrity constraints is that by imposing certain specifications, the integrity constraints restrict the data that is stored in a database instance. In case, the database instance satisfies all the integrity constraints, then it is regarded as a legal database instance and hence, can be stored in the database. In case, these constraints are violated, then it implies that database is not maintained in an efficient manner and the database contains illegal database instances.