Our social:

Thursday, October 6, 2016

Database Management System-Tutorial

To overcome all drawbacks of file processing system, a new system called database system is developed. All the files in the file processing system are known as tables in the database. The contents of files form records of the table. In database, each of column values are known as attribute and each row of information is known as record. There no difference in the data being stored. But it is different from the file system by the way data is stored and accessed in the database.
In the database, each set of information is stored in the form of rows and columns. We define a unique key column for each record known as primary key. Using primary key, we can access the data much faster than file system. We can also define a mapping between any two related tables. This helps in reducing unnecessary data storage and faster retrieval of data.
Below is the example how the data is stored in the database tables logically.
           
How do we access these data from the database? For this purpose, database defines a collection of programs that allows the users to insert, delete, update and retrieve the data from the database based on user’s requirement. These set of programs are known as database management system – DBMS. Apart from basic transactions, it allows the users to perform complex transaction; it helps to maintain consistency, atomicity, security, and controls concurrency.   Oracle, MySQL, SQL Servers, DB2, FoxPro, dBase etc are some of the DBMS available in the market. Though they are manufactured by different manufacturer, basic functionalities of all them are same.

Advantages of DBMS

DBMS is developed to cover the drawbacks of the traditional file system.
  1. Data Mapping and Access: - DBMS defines the way to map any two related tables by means of primary key –foreign key relationship. Primary key is the column in the table which responsible for uniquely identifying each record in the table. Foreign key is the column in the table which is a primary key in other table and with which the entries in the current table are related to other table.
    For example, in the new database system, we can Student and Student_Report table as follows.
    STUDENT_ID is the unique using which we can identify each student and hence it forms a primary key in STUDENT table. Similarly, REPORT_ID is the primary key in the STUDENT_REPORT table. STUDENT_ID in the STUDENT_REPORT table is the foreign key. It links STUDENT and STUDENT_REPORT tables.
    Because of such mapping, it becomes each for the programmer to search for related tables, join them, and fire the query as per the requirement. This reduces the time consumed while searching and mapping these tables. Even when there is large amount of data, the time consumed to retrieve, insert, update or delete is very less. Hence there is no data isolation in the system.
    Note: Mapping of tables is usually done when they are created.
  2. Data Redundancy: - By the introduction of primary key in the table, data redundancy problem is reduced to great extent. As we saw, primary key is the unique column for each record, when there is a re-entry of same record, it does not allow saving such records.
    DBMS has strong designing techniques like normalization which makes sure the same copy of data is not stored in same table or in multiple tables. It makes sure all the informations are stored only once in the database tables.
    We can see the difference in the way data is being stored in the file and database system. Primary key, foreign keys are defined; unnecessary columns are removed from the STUDENT_REPORT table in the database system. These are missing in the file processing system.
  3. Data Independence and Consistency: - DBMS defines a standard to represent the data in the form of rows and columns. It also stores the information about the tables, columns, keys, storage space, used space, available space etc separately from the logical data. Hence they totally independent of the way they are stored and the data being stored. Any changes to the physical storage (like disks, tapes etc) or structure, does not harm the data being stored. Since DBMS defines each columns and rows at the beginning itself and controls the way data being entered, there is no affect on the programs or any other tables or data. Hence the consistency of the data also maintained.
    If there is a change in the address of any student, we just have to update it in the Student table. There is no other place his information is being stored. Hence it maintains the consistent data in the database.
    Suppose there is a new column addition to STUDENT table, say DOB. This will change the metadata to reflect additional column in the table structure. It will hardly affect the application unless until there is a new requirement to have transaction with DOB. Hence data independence is also assured in the database.
  4. Security: - DBMS allows different levels of access to different users based on their roles. In the school database, individual students will have access to their data alone, while their teachers will have access to all the students whom they are teaching and for the subjects that they are teaching. Class teacher will be able to see the reports of all the students in that class, but not other classes.
    Similarly, in a banking system, individual account holder will have Read-Only access to their account. While accountant can update, individual account details for each of their transaction.
    All these levels of security and access are not allowed in file system.
  5. Integrity: - DBMS allows having restrictions on individual columns. It would be defined while designing the table itself. If we want to enter salary of an employee within the range 10000 to 40000, we can impose this while designing the table by using CHECK constraint. When salary is entered, it will automatically check for the range specified.
    CREATE TABLE EMPLOYEE ……….. CONSTRAINT chk_salary CHECK (salary>10000 AND salary <40000)
  6. Atomicity: - DBMS makes sure either the transaction is fully complete or it is rolled back to the previous committed state. It does not allow the system to be in a partially committed state. In our example above, DBMS commits marks change transaction before calculating the total. If there is any crash or shutdown of the system, before committing the marks, then updated marks will be rolled back to the original marks. Hence it makes sure atomicity of the transaction is achieved.
  7. Concurrent Access: - DBMS provide access to multiple users to access the database at the same time. It has its own mechanism to have concurrency accesses and hence avoid any incorrect data in the system.

Disadvantages of DBMS

  1. It is bit complex. Since it supports multiple functionality to give the user the best, the underlying software has become complex. The designers and developers should have thorough knowledge about the software to get the most out of it.
  2. Because of its complexity and functionality, it uses large amount of memory. It also needs large memory to run efficiently.
  3. DBMS system works on the centralized system, i.e.; all the users from all over the world access this database. Hence any failure of the DBMS, will impact all the users.
  4. DBMS is generalized software, i.e.; it is written work on the entire systems rather specific one. Hence some of the application will run slow.

               

 Database Schema

0 comments:

Post a Comment

Hello Every One thanks For Visit My site You Can Suggest any Idea About site And Related Education Information Provide me Its Best Content For you am Include in this Site And feel free contact And email now I hope This site very help full you