Database
Database
Database is collection of interrelated data. Database stores data about the entities (objects); entity can be physical or abstract. Data in database is stored in the tabular format; rows of the table represent a record (record of an entity) and columns of the table represent attributes of the entity. Database management system stores data in structured format and allows ease access and modification of the data; includes language to create, access, and manipulate data stored in the database. This language is known as Structured Query Language (SQL).
Database management system (DBMS) is an important component of the Management Information System (MIS). MIS works and processes data and produces information. Data should be in correct format for MIS to process and produce information. There are several advantages of storing data in database over storing data other systems like file system:
- Database helps to reduce redundancy (repetition of data). Each record of the database table can be represented by a unique attribute; this protects duplication of the same record in the table.
- Data in the database tables can be related and data can be retrieved by joining the related tables.
- Database allows to specify constraints (rules) over the data stored in the database. These constraint make database consistent, that is database system will not allow to insert inconsistent data into the database tables.
- Data from database can be retrieved easily; data can be retrieved based on different conditions also.
- DBMS has Transaction management component. DBMS allows more than one transaction (transaction is a set of operations like updating data, inserting data, retrieving data) to execute at the same time.
MIS needs data, without data information can’t be produced. It is necessary to store data in the organized and well-managed form such that it can be retrieved and processed easily. Organizations take advantage with well managed data; database management system is the best option for storing data in the organized manner.
Here a simple example is given to shown how database management system helps managers in decision making process. Let’s consider a Library which provides books to its members; only members can borrow books from the library. The employees of the library can create membership for the users; they manage all the data of the library; insert/edit information of new books, members, transactions (borrow and return). The employees need several information for smooth operation of the library. The employees have to decide:
- when to order new books
- which new books are to be ordered
- in how many units books have to be ordered
- who are the most frequent users of the library
- what type of members (based on education, gender, age etc.) are there.
By storing information of books, members, transaction (borrows, returns, orders etc.) in database the employees of the library can get answer (information) to above queries easily and effectively. Books, members are entities of the library system and borrows, orders are relationship between the entities.
Three tables are created in the database to store data of books, members and issues. The books table will store data of the books only, member table will store data of the members only and issues will store data about the books borrowed by the members.
Table 1 Books
Table 2 Members
Table 3 Issues
Following SQL statements will produce information which will be of value for the employees in answering above questions:
- select fullname,count(*) as total from issues as i, member as m where i.memid = m.memid group by fullname
Table 4 Number of Books Issued by each member
- select bookname,count(*) as total from issues as i, books as b where i.bookid = b.bookid group by bookname
Table 5 Number of books issued
- select type,count(*) as total from issues as i, books as b where i.bookid = b.bookid group by type
Table 6 Number of different types of books issued
- select gender,count(*) as total from issues as i, member as m where i.memid = m.memid group by gender
Table 7 Number of male and female members
- select gender,type,count(*) as total from issues as i, member as m,books as b where i.memid = m.memid and i.bookid = b.bookid group by gender,type
Table 8 Number of books issued by each gender
Data about only one entity (object) should be stored in a table. It is not a good practice to store data of more than one entity in the single table; storing data of more than one entity in the single table creates different problems. Consider following table in which employee data and book data are stored in the single table to show who has borrowed which book; this table is able to provide information about the borrows made by the members but there are problems:
Table 9 Books Issued by members
Here, the given table shows who borrowed which book and when. In the give table there is repetition of same data; records of Mohan, Sheetal are repeated; similarly books information are also repeated. The repetition of records means more storage requirements. If we want to change address of Mohan, we have to update all the records of Mohan; missing to update one record of Mohan will make data inconsistent. These issues stand up because data of two different entities (member, book) are stored in the single table.
Above images of tables: book, member and issues are given. In those tables, there is no repetition of records; no waste of memory space; and updating member or book data can be done easily; updating address of Mohan in one place is sufficient. That design in which a table storing data of one entity only is preferred over the flat tables containing data of more than one entity
Database helps to make data consistent. Organizations have several rules on data like: age should be greater than 20 or gender must be among three values (male, female, or other) only. These kinds of rules can be easily imposed in the database. These rules are known as integrity constraints. The integrity constraints are specified at the time of designing table.
- Entity integrity constraint: Specifying Primary key in the table. The primary key will protect users from inserting duplicate records
- Referential integrity Constraint: An attribute ‘X’ of table ‘A’ will take only those values that appear for an attribute ‘Y’ of another table ‘B’. Here, table ‘A’ and ‘B’ are related with the concept of primary key and foreign key; attribute ‘X’ of table ‘A’ is foreign key and attribute ‘Y’ of table ‘B’ is primary key for table ‘B’.
- Domain Constraint: This constraint will protect attribute of table from having inconsistent values; for example, specifying data type of an attribute is a domain constraint. If an attribute of table is made integer, then that attribute will not take any other value than integer. The possible values for an attribute can be restricted also; like age must be integer and must be between 20 and 50.
- Functional dependency: If value of attribute X is known then the value of attribute Y can also be known; that is, attribute Y is dependent on attribute X. If post of an employee is known then his basic salary can be known. Functional dependency helps to make data consistent; the dependent attribute will not have more than one value.
- Cardinality constraint: This rule shows how many members of an entity set “A” is related with how many members of another entity set “B”. Let’s consider an example: person has car. Here person and car are entities. We can write rule like: a person can have only one car and a car can belong to only one person. This rule will make database consistent as in database we can’t find records in which a person is having more than one car or a car belonging to more than one person.