Structuring Database for Accounting

1. The possibility of duplicate or redundant data items is reduced or eliminated.
2. Improve the storage efficiency by avoiding the storage of redundant data.
3. Achieve scalability
4. To achieve a flexible database structure
5. To achieve more efficient database structure
6. Ease of maintenance

1. Entities: An entity is anything in the real world that has the indepenent existence. It is an object that has
   1. physical existence. For example a person, a building or a bike etc.
   2. conceptual existence. For example an account, a voucher, a university course, a job, a company etc.

   The following five entities exist in the context of a typical accounting reality. These entities are used to capture the accounting data.

   1. Accounts
   2. Vouchers
   3. Employees
   4. Accounting Type
   5. Support Documents
2. Atributes: Attributes are some properties of interest (or characteristics) that are used to further describe the entities. For example, the height, birth place and date of birth of a person or code, name of an Account. Each attribute of an attribute will have a value. These values are stored in the database. The following are different types of attributes.
   1. Simple or Atomic Attributes vs Composite Attributes: Simple or atomic attributes can not be further sub-divided. Composite attributes can be further sub-divided into smaller sub-parts.
   2. Single valued vs multi-valued attribtues: Single value attribute of an entity has only one value. Multi-valued attribute of an entity has multiple values.
   3. Stored vs Derived attributes: A basic attribute is stored attribute. Derived attributes depend on the basic attribute. Example: Date of Birth is a stored attribute. Age can be derived from the date of birth.
   4. Null Values:The NULL values indicate the absence of data.
   5. Complex attributes: The composite and multi-valued attributes may be nested or grouped to form complex attributes.
   6. Entity Types and Entity Sets: Entity type is a collection of entities that share a common definition of attributes. Entity Set is a collection of all the entity instances of an entity type.
   7. Value Sets of Attributes: The set of all possible values that may be assigned to a specific attribute.
3. Identifiers: The attribute of an entity type that has unique value is called as identifier. It is used for identifying the entity instance. Sometimes two or more attributes combined together (called as composite key) constitute such distinct value. The entity types that do not have a key attribute are called as weak entities.
4. Relationships: Relationships exist between two or more entity types. They represent the interaction among the entities of the entity types. A relation exists when an attribute of an entity type refers to another entity types. In the ER model, these references are represented as explicit relationships instead of attributes.
   1. Types of relationships: A relationship type is constituted when entities from different entity types are related to one another in a specific manner.
   2. Degree: Degree of relationship type is the number of participating entity types in a relation.
   3. Roll Names: Each entity type participating in a relationship type plays a particular role in the relationship.
   4. Structural Constraints: The reality imposes certain constraints or restrictions to limit the possible combination of entities participating in a relationship set
      1. Cardinality Ratio: This specifies the number of relationship instances that can participate in a binary relationship.
      2. Participation Constraint: It specifies the whether an entity type exists based on its relation with another entity based on the relationship type. When it is required that every entity of an entity type must relate to another entity type, it is called total participation. On the contrary, when some entities of an entity type need not be related to another entity type, it is called as partial participation.

These elements are used to express a reality for which a database is to be designed. The figure below gives a depicts the ER Model through the ER symbols. These symbols are used to represent different types of entities, attributes, identifiers and relationships.

1. Domain: It requires that the value of each attribute must be
   1. indivisible value.
   2. drawn out of possible values associated with its domain.
   3. conforming to the data type associated with the domain
2. Key Constraints and NULL values: As per the definition, a relation is a set of tuples that are distinct. So, in a relation, each data record that corresponds to a tuple should be unique. This implies that no two tuples (or rows) in a relation (or table) should have same combination values for all their data items. Also, every relation, by default, will have the combination of all the attributes as the key. This is also called as super-key. This super key specifies the uniqueness constraint. Such a super key may have redundant attributes. So, a key that does not have a redundancy will be more useful.
   Minimal super-key, also called as Key, is a part of the super-key constituting the least possible number of attributes that are unique. The value of this minimal super-key is used to identity each tuple in a relation. This key should continue to be unique even after adding a new tuple.
   When a relation has more than one key, such key is called as candidate key. One of these candidate keys is called as primary key. The circumstances of the mini-world would decide which of the available candidate keys can be a primary key.
   

   

3. Entity integrity constraint: As per this constraint, a primary key value can not be NULL because the primary key is used to identity individual tuple in a relation. If a primary key has a NULL value it implies that such tuples can not be identified or it identifies multiple tuples as unique. In otherwords, this means that there are duplicate tuples.
4. Referential integrity constraint: Referential integrity constraint is specified on two relations to maintain consistency among the tuples of these relations. As per this, a tuple in a relation must refer to an existing tuple in another relation through a foreign key.

1. Domain
2. Key constraints and NULL values
3. Entity integrity constraint
4. Referential integrity constraint.

The following are the different types of update operations in relation to the above integrity constraints which must be satisfied in a relational database model.

1. Insert: This operation is performed to add a new tuple in a relation. The insert operation might violate any of the above four constraints.
2. Delete: This operation is performed to delete a tuple from a relation. Though the delete operation, a specific data record from a table can removed. The delete operation might violate referential integrity constraint. This happens when a tuple being removed is referenced by foreign key from other tuples.
3. Modify:The operation is performed to modify the values of some attributes in existing tuples. This operation is helpful to change values of an existing record in a table. This operation might violate the integrity constraint only when the operation tries to modify either primary key or foreign key. Otherwise, it does not violate any of the constraints.

1. Create a relation for every strong entity: For every strong entity type that has the primary key in the ER schema, a separate relation is created. This relation includes all the simple attributes of that entity. The primary key can be either one of the key attributes of this entity or a set of simple attributes that uniquely indentity this entity.
   For example, Employee entity is strong because it has one of its unique attribute EmpId as the primary key. So, we’ll create a separate relation for the Employee as specified below. Note that the primary key is underlined.
   Employee(EmpId, Fname, Minit, Lname, Address, PhoneNo, SuperId)
   Similarly, other relations are created for the below strong entities.
   Accounts(Code, Name, Type)
   Vouchers(VNo, vDate, Amount, Description)
   Account Type(CatId, Category)
2. Create a separate relation for each weak entity type: Every weak entity has a owner entity. The weak entity type is identified through the identifying relationship. For every weak entity type, a separate relation that includes all its attributes is created. The primary key of this new relation is the combination of its unique attribute(s) for a particular tuple of the owner relation along with the primary key attribute of the owner relation. In addition, the primary key of owner entity is included as foreign key in such a relation key of the owner entity. This foreign key acts as the relation key between the owner and the weak entity type. The weak entity has the composite key which is combination of the foreign key and the partial key of the weak entity.
   If we take the example of Vourchers and Support Documents entities, the Vourchers entity is the owner entity and Support documents entity is the weak entity. The Support Documents entity does not have a primary key of its own. It has the composite key which is combination of the primary key of Vouchers i.e. vNo along with SNo. It is given below.
   Support Documents(vNo, SNo, dName, sDate)
3. Identify entity types participating in binary 1:N relationship type: Identity the first relation on the n-side of the relationship and second on 1-side of the relationship. The primary key of the second relation should be included as a foreign key in the first relation.
   For example, an employee can authorize a number vouchers. So, in the Auth_By relation, the Vouchers participates on the n-side and the Employees participate on the 1-side. So, the primary key of the Emploees i.e. EmpId should be included as foreign key in the Vouchers relation. Similarly the Vouchers and Employees participate in the Prep_By relation. The mapping of both of these relations require that the EmpId should be included twice, but with different roles. As the relation cannot have two attributes with the same name, we include their role names as attribute names to designate the foreign keys. The Vouchers relation is modified accordingly and provided below:
   Vochers(VNo, vDate, Amount, Description, Auth_By, Prep_By)
   Similarly, two relationships exist between Vouchers and Accounts. One is Debit and one is Credit. Here Vouchers include the Code attribute of Accounts as foreign key. As the relation cannot have two attributes with the same name (i.e. Code and Code), we include their role names (Debit and Credit) as attribute names to designate the foreign keys. The Vouchers relation is modified accordingly and provided below:
   Vochers(VNo, vDate, Debit, Credit, Amount, Description, Auth_By, Prep_By)
4. Identify entity types participating in binary M:N relationship type: For each binary M:N relationship type, create a new relation to represent such relationship. The primary keys of the relations that represent the participating entity types should be included as foreign keys in this new relation.
   For example, consider the case of credit voucher which has one debit and multiple credit accounts as depicted below.
   

   

   
   In this case, the relationship Credit has cardinality ratio of M:N between vouchers and accounts(multiple vouchers are related to multiple accounts) where as the relationship Debit has cardinality ratio of N:1 (Multiple vouchers are related to one account). In addition to that the credit relationship has the attributes Sno, amount and Description. So, we’ll create a new relation as
   Credit(vNo, Sno, Code, Amount, Description)
   In the above relation credit Code is foreign key to represent the primary key of accounts relation. Vno is foreign key to represent the primary key of Vouchers relation.
   On similar note, the relation for the Debit voucher will be
   Debit(vNo, Sno, Code, Amount, Description)