DATABASE

In this blog we are going to learn about the basic concept related to the database, definition types & facts of the Database management system Relationship that will enable us to cultivate a better understanding of MS Access, the Relational Database Management System (RDBMS) from the Microsoft Office suite.

Information is an important factor in managing our routine activities, and so is its maintenance and management to plan well for the future. From a child to an old man,  a professional to a homemaker, everyone needs information.

Information is needed on various vital matters, such as addresses, telephone numbers of our associates, details of customers, and clients, railways, air travel schedules, and numerous other aspects. so it is very important to know how we all easily manage this huge data by understanding the database in this blog.

 

Database | Definition | Types & facts | Database Management system | Database relationship | SQL



However, data in its raw and unprocessed form is meaningless, but when such data is covered in information, it can be easily interpreted. Hence, it is essential to provide a structure to the available data and organized it, which can be achieved with the help of a database. It enables us to edit, store, manage, and retrieve information easily and quickly as and when required. So we are going to discuss briefly about Database | Definition | Types & facts | Database Management system | Database relationship | SQL

DATABASE

It is an organized collection of information that can easily be accessed , managed, and updated. The overall purpose of a database is to record and maintain information that is relevant to the organization and necessary for making decisions. A database is an integral part of our daily life. Each one of you like to maintain your personal address dairy, wherein you record your friend's addresses and telephone numbers. Have you ever noticed that how do you maintain your address dairy? Well, you maintain the information alphabetically and whenever any information is needed, you browse and extract the information quickly. From time to time, you keep on adding the addresses of your new friends, sometimes you even delete and update the addresses. All these above operations, like adding, updating, sorting, editing, deleting, etc are the part of a database. A telephone directory, a dictionary, a list of school children and their addresses, a list of grocery items, a catalog in a library, index at the back of a textbook, list of different customers in a bank, etc. are the examples of database.

Earlier, it was difficult to maintain in a database manually that consists of hundreds and thousands of records. Moreover, when a piece of information was to be updated in this system, it had to be done separately in all related files. These issues created the need for designing an efficient DBMS which could easily handle vast amount of data and provide centralized control over it.

DATABASE MANAGEMENT SYSTEM

A DBMS is a collection of program that enables one or more users to create, manage, and retrieve information from a database. It acts as an interface between the database and application programs.

Let us take a small example of a railway reservation system. In a single day, thousand of trains in India follow their schedules routes and timing. For reservation and enquiry purposes, you can check the availability of seat, with respect to distance, fare, travelling class, etc. of any train. What manages such a huge real-time data?

In yet another examples, SBI (State Bank of India) has more than 10,000 branches all over the India. It does not matter in which branche of SBI you have your account, you can withdraw cash from any ATM or branch across the world.

Let us discuss an examples at the school level. Every year the CBSE/ICSE/State Board results are declared in the month of May/June. On the examination portal, you simply provide your Roll Number and you get your Name, Father's Name, Detailed Marks, Percentage, and Division. How is this all possible without any mistakes? How does a computer manage to remember so much data and display it in the correct format?

You are now very much clear that there is no human intervention in the information retrieval processes and no-one except the computer can work so fast and accurate for millions of users, bank account holders or students. There is powerful software which works at a lightning pace. This software is known as Database Management System (DBMS).

WHY DBMS?

In today's world, people can access to a huge amount of data, either through the Internet or customized software. There is always a need for an efficient database system which should be capable of handling vast amount of data. A DBMS must have the following goals:-

  • To provide retrieval flexibility. It should be easy to link data from different files.
  • To facilitate reduction of data duplication and elimination of multiple copies of a master file.Data redundancy control helps in overcoming updating problems and promotes data integrity .
  • To ensure a high level of data independence.The data is hidden from the programming language, operating system and processing environment.it should be upto the DBMS to convert the stored data into a form which can be used in any programming language.

Let us discuss the need of a DBMS with respect to:

1.Data storing     2. Data sorting     3.Data summarizing   4. Data classifying    5.Data retrieving 

DATA STORING

You can store large amount of data using an efficient DBMS. Let us take the example of a bank database. A bank has thousands of customers whose complete data is stored ina database. From details of Account Numbers to Customer Names, Permanent Address to PIN Numbers, Photographs to Signatures, Account Balance to Financial transactions, everything is managed by the DBMS. It proves that you can store and maintain different categories and vast amount of data using a proficient DBMS.

DATA SORTING

Sorting refers to the arrangement of data in a proper sequence. A collection of data is useless until it is managed in an organized way. If you do not arrange the data in a proper manner the retrieval of data becomes very difficult. Imagine, if telephone directories do not sort the data in an alphabetic order, it would be extremely difficult to find a particular telephone number. A DBMS sorts the data in a particular order which makes information retrieval very fast.

DATA SUMMARIZING

Summarizing is a process which is used to get the synopsis of data on the basis of some criteria. For example, thousands of bills are generated and processed everyday in a shopping mall. The DBMS which stores this data provides us the facility of getting a summary of complete data, such as the Total units received, Total units sold, and Stock in hand and thus can be helpful in inventory management. Hence, you can say that a DBMS generates reports that give useful summaries of the updated data.

DATA CLASSIFICATION

Classification of data means to categorize data on the basis of some conditions. For examples, a bank has millions of accounts, but all the accounts are not same. They fall under different categories, such as Saving, Current, Salary, and students accounts. A bank provides different services for each type of account. Hence, a DBMS can categorize the data in different sections and handle it accordingly.

DATA RETRIEVING

By using an efficient DBMS, you can instantly retrieve any data from the database as the data is stored in an organized manner. Let us continue with the example of a bank. whenever you and your parents use their debit cards at an ATM, it provides them the details of their account within seconds. Despite having records of millions of customers, it retrieves the data at lightning speed. Hence, a DBMS not only helps in storing large amounts of data but also helps in quick retrieval of the same.  Now letus see the Types of DATABASE

TYPES OF DATABASE

A Database can be categorized into two types.

1. Flat File Database   2. Relational Database

FLAT FILE DATABASE: It is relatively a simple database system which stores all the data in a single table. A flat file refers to data files that contains records which have a small, fixed number of fields, without any structured relationship. E.g. MS Excel.

RELATIONAL DATABASE: A relational database is a type of database that stores data in several tables. These tables are related to one another in such a way that whenever there is a need, the data can be joined and information can be accessed from the at once. The relationship between different tables can be defined by their common fields. The main advantages of using a relational database is that we are not required to enter repetitive information in tables again and again, which inturn saves time. E.g. Oracle, Base, MS Access, Sybase, MS SQL.

COMPONENTS OF DATABASE

1. TABLES: Tables are the building blocks of database. They store the complete data in a structured manner. Tables store data in the form of rows and columns. Every tables has finite number of columns but it can have infinite rows. The Elements of a Tables are:

  • FIELDS: All the columns in a table are called Fields. A fields describes a particular attribute of all the records in a table. For examples, the fields Roll No in Students table will store the roll numbers of all the students and will be a common attribute of every student.
  • RECORDS: The rows in a table are called Records. A records contains the values for all the fields that belongs to a single person or an object. For examples, when we enter the data for all the fields, such as Roll No, Name, Marks, etc., for given a students, it creates a new row in a table which is called a record.
  • DATA: A set of characters that represents a valid values is known as Data. For examples 3, Dipti, 90, 88, and 70 are the data value for the specific fields of the table given in the figure:-


2. QUERIES: As the name suggest, queries are used to ask questions from the database. A database stores vast amount of data but queries help to retrieve the filtered data based upon some conditions. Queries are also used to perform actions, such as insert, delete, update etc. on the data depending upon the criteria specified by the user.

3. FORMS: Forms are the user friendly interfaces that facilitate the process of editing (insert, update, delete) data in tables and queries. A form has an attraactive interface that accepts data from the user and forwards it to the corresponding table or query.

4. REPORTS: Reports are used to display selected data in a printable format. Reports collect the summarized data from one or more tables/queries and organize in a printable form.

DESIGNING A DATABASE

1. Determine the purpose of database: It is essential to know the purpose, which helps us to decide what data we want to store and what facts about the data are required in the database.

2. Determine the tables needed: After knowing the purpose, we can divide our information into separate topics,. It is important to note that a tables should not contain duplicate data and there should be no redundant information between different tables. For examples, if we consider an entry in the field 'student DOB' then we must ensure that it is present only once in the same table.

3. Determine the Fields needed: At this step, we should list what information we want to store in each table and then eliminate the category which is not required for the problem solution. Each category of the topic is know as field and is displayed as a column in the table. For examples, the fields in a students table ca be student _Name, Student Id, student Address etc.

4. Identify Fields wit Unique values: A primary key is used to uniquely identify each record in a database table. It can either be an actual attribute of the record or an artificial key generated by a DBMS. It should never contain duplicate and Null values. It can be composed of one or more fields. If more than one attribute form the primary keys,it is called a composite key. It must be note that A particular table can contain only one primary key.

5. Determine the Relationship between Tables: Observe each table and analyze how the in one table is related to the data in other tables. Accordingly, add new fields to tables or create new tables t explain the relationship.

6.Refine The Design: In this step of design process, we evaluate the design of the database. we need to identify any design flaws that could result in data errors. This can be achieved by creating a few tables and inserting some sample data so as to check whether we are getting the desired results from the database or not.

7. Enter Data and Create other database objects: If you are satisfied that the tables in your database meet the design requirements, we can proceed to all the relevant data to the tables. After completing the tables, you can generate reports, create forms, queries, etc, of your choice.

ADVANTAGES OF A DBMS

1. To reduce data Redundancy and Inconsistency: A DBMS helps in controlling data redundancy which in turn minimizes data inconsistency. Data redundancy occurs when data is duplicated unnecessary.

2.Data Concurrency: Concurrency control can be defined as a feature in a DBMS which allows multiple transactions to access and modify shared data at the same time, without violating data integrity. However, this ability is not exhibited by traditional file processing system where multiple access to the same data causes data inconsistencies.

3. Enforcement of Data Standards: In a DBMS, data is stored in a standard format. Standards may relate to the naming of data, format of data, structure of data etc. Standardized data facilitates in data interchanging or migration between system.

4. Database Can Ensure Data Security: Sometimes a database may contain confidential data that need to be protected from unauthorized access. A DBMS provides security to sensitive data by performing authorization checks and enforcing selective access to users.

5. Integrity Can be Improved: Since data of an organization using a database approach is centralized and would be used by a number of users at a time, it is essential to enforce integrity constraints.

6. Backup and Recovery Management: Backup and recovery refers to the various strategies and procedures used in protecting a database against data loss, and the process of reconstructing the data in case a disk crash occurs.

WHAT IS RELATIONSHIP?

Relationships are links that associate a field in one table with a field in another table. In Access, you store data in multiple tables, which are related to one another in such a way that if there is a need, the data can be joined from these tables and information can be retrieved.

A relationship works by matching a field with the same name in both tables. In most cases, these matching fields are the primary key from one table that uniquely identifies each record in the second table. The advantage of using a relational database is that you do not have to enter repetitive information again and again and this saves time.