- Indicate against each of the following statements, True or False :
- Every relation has at least one super key by default, which is the combination of all its attributes. (T)
- Data transformation is called Information. (T)
- Referential integrity constraint arises because of relationships between various entities. (T)
- The complete absence of WHERE clause in SELECT statement implies that no tuples of a relation shall be selected. (F)
- ER model is an example of representational data model. (F)
- Fill in the blanks, an appropriate word(s)
- A ……….. does not have key attributes of its own. (Weak entity)
- The ……….. for binary relationship specifies the number of relationship instances that an entity can participate in. (Cardinality Ratios)
- Each simple attribute of an entity type is associated with a value set called ……….. of values. (Domain)
- When structure of AIS is based on both human and computer resources, it is called ……….. AIS. (liveware)
- An ……….. is a collection of all entities of a particular entity type. (Entity Set)
- A weak entity type always has a ……….. constraint with respect to its identifying relationship. (total participation constraint (existence dependency))
- When a relation has more than one attribute with unique values, each such attribute is called ………… (candidate key)
- Relational model: It represents the data as relations.
- Network model: It represents the data as record types.
- Hierarchical model: It represents the data as hierarchical tree structure.
- Collecting
- Sorting
- Relating
- Interpreting
- and computing data items
in such a manner as to provide useful and meaningful information for decision-making. This is achieved through the following steps.
- Data capturing
- Inputing
- Processing
- and generating information for the user.
In computerised accounting, this is achieved through
- identifying
- storing
- and retrieving of data content
of an accounting transaction. The machanism should allow the storage so as to permit easy and convenient retrieval of data as and when required. To achieve this, a suitable database is designed for accounting related activities. This database consists of inter-related data tables. The design is structured to ensure data consistency and integrity. Moreover, the data processing cycle is more efficient and reliable and available round the clock. In this way, the computers are very useful in processing of the accounting data.
It goes through the following stages through which it is finally transformed for being presented as information in financial statements.
- Source Documents: The accounting data is captured in the vouchers that are designed to allow recording of this data in a systematic manner.
- Input of Data: The data is input into the Data Entry Form that is designed to appear similar to the vourcher.
- Data Storage: In order to store the data, a data storage structure or data table is created during the structuring of the database for accounting.
- Manipulation of data: The stored data is manipulated to transform it to generate final reports. This data can be directly presented in the form of a report or it can be stored separately for generating the reports later on.
- Output of Data: The transformed data can be accessed in the form of accounting reports such as ledger, trial balance etc and viewed in a pre-designed format.
- Integrated property: This implies that distinct data tables have been logically organised. This ensures that the redundancy (or duplicity) is reduced or eliminated. It also facilitates better data access.
- Shared property: This implies that only those users that are authorized to use the data/information have access to the relevant data.
On the other hand, DBMS is a collection of programs that enable users to create and maintain a database. It can be defined as the general-purpose software system that facilitates the processes of
- defining
- constructing
- and manipulating (or processing)
databases for various applications.
An example of entity type is as described below.
Entity Type: Accounts
Intension (or structure) of entity type
Code | Name | Type |
---|
An entity set is a collection of all entity instances of a specific entity type.
An example of entity set is as described below.
Entity Set: Collection of entity instances of an entity type “Accounts”
Intension (or structure) of entity type
111111 | Capital Account | 4 |
222222 | Jain & Co | 3 |
333333 | Jayram Bros | 2 |
Relationship instance refers to the association between two entities from two different entity types.
Relationship Set is the collection of all the associations .
A set of attributes of the weak entity type can uniquely identify the weak entities that are related to the same owner entity. This set of attributes are called as partial key. If this is not the case, the composite attribute of all the weak entity’s attributes will be parial key.
document authoriserand the Voucher plays the role
document authorised.
Participation constraint specifies whether an entity type exists depending on whether it is related to another entity type or not. The following are the two types of participating constraints:
- Total participation: In this, entity of an entity type can exist only if it participates in a specific relationship with another entity type. It is also called as existence dependency. Example is the participation of ACCOUNTS in CLASSIFY relationship as every account must refer to atleast one of the account types. In the ER diagram, it is represented by a double line connecting the participating entity type to the relationship.
- Partial participation: In this, entity of an entity type can exist even if it does not participates in a specific relationship with another entity type. Example is the participation of EMPLOYEE in PREPARED BY relationship as there might be few employees who do not prepare the vouchers but still exist. In the ER diagram, it is represented by a single line connecting the participating entity type to the relationship.
Example: Consider the case where in the relation Voucher (Vno, Sno, Vdate, Debit, Amount, Credit, Amount, Prep_by, Auth_by, Narration) references the two other relations Accounts(Code, Name, Type) and Employees(EmpId, Fname, Minit, Lname, Address, PhoneNo, SuperId) as specified below.
Vno | Sno | Vdate | Debit | Amount | Credit | Amount | Prep_by | Auth_by | Narration |
Code | Name | Type |
EmpId | Fname | Minit | Lname | Address | PhoneNo | SuperId |
In the above, the Vouchers relation uses its Debit and Credit attributes to refer to the Accounts relation. Here Debit and Credit attributes are Foreign keys. They refer to the tuples of Accounts relation through its primary key named Code.
Also, the Vouhchers relation uses its Prep_By and Auth_By attributes to refer to the Employees relation. Here Prep_By and Auth_By attributes are foreign keys. They refer to the tuples of Employees relation through its primary key named EmpId.
The following are the reasons that lead to their occurrence in database relations.
- When a particular attribute does not apply to an entity.
- Value of an attribute is not known at the moment, even though it exists.
- Value of an attribute is not known because it does not exist.
By default, every relation will have atleast one key which is the combinaion of all its attributes. This is called super-key by default. Therefore, any such super-key specifies uniqueness constraint. Presence of duplicate tuples implies that there are two super-keys which are same. This goes against the basic princile of unqueness constraint. Due to this reason, duplicate tuples are not allowed in a relation.
- Both the relations should have the same number of attributes.
- The domain of the similar attributes should be the same for all the attributes.
The set operations i.e. Union, Intersection, Difference and Cartesian product, require the union compatibility. Union compatibility is required for these operations because these set operations are binary (an operation that combines two elements to give a third element) and for the result of a binary operation to be a set they should have the same number of attributes and their domains should be the same. In otherwords, the two participatint sets should be union compatible.
- The possibility of duplicate or redundant data items is reduced or eliminated.
- Improve the storage efficiency by avoiding the storage of redundant data.
- Achieve scalability
- To achieve a flexible database structure
- To achieve more efficient database structure
- Ease of maintenance
- Entities: An entity is anything in the real world that has the indepenent existence. It is an object that has
- physical existence. For example a person, a building or a bike etc.
- 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.
- Accounts
- Vouchers
- Employees
- Accounting Type
- Support Documents
- 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.
- 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.
- 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.
- 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.
- Null Values:The NULL values indicate the absence of data.
- Complex attributes: The composite and multi-valued attributes may be nested or grouped to form complex attributes.
- 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.
- Value Sets of Attributes: The set of all possible values that may be assigned to a specific attribute.
- 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.
- 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.
- Types of relationships: A relationship type is constituted when entities from different entity types are related to one another in a specific manner.
- Degree: Degree of relationship type is the number of participating entity types in a relation.
- Roll Names: Each entity type participating in a relationship type plays a particular role in the relationship.
- Structural Constraints: The reality imposes certain constraints or restrictions to limit the possible combination of entities participating in a relationship set
- Cardinality Ratio: This specifies the number of relationship instances that can participate in a binary relationship.
- 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.
- Domain: It requires that the value of each attribute must be
- indivisible value.
- drawn out of possible values associated with its domain.
- conforming to the data type associated with the domain
- 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.
Flow chart to reach a minimal super key - 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.
- 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.
- Domain
- Key constraints and NULL values
- Entity integrity constraint
- 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.
- Insert: This operation is performed to add a new tuple in a relation. The insert operation might violate any of the above four constraints.
- 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.
- 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.
- 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) - 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) - 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) - 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.
ER Diagram showing relationships between vouchers and accounts in the
context of credit vouchers, with one debit and several credit entries
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)
You might also want to refer the following pages.