Database

Q.      What is the Concept of a Database?

            Database and database management system have become an essential part of our everyday life nowadays, we encounter several activities that involve some interaction with a database almost daily. The examples include preparation of census record, preparation of result sheet , deposit and withdrawal form a ban, airline reservation, accessing computerized library, purchase items from supermarkets etc, In all these cases a database is accessed . Database has been used knowingly or unknowingly in our daily life. Computerized database enables the user to find the specific information very fast and helps him her to take right decision at right time. An organized collection of related data where data is the known facts figures about a person, place , event or thing which can be recorded, A file composed of records, each containing fields together with a set of operations for searching sorting recombining and other functions For example phone diary, result sheet customer records price list  etc. The database contains only data not information so it should be analyzed or processed to get required information. We can define database as a collection of tables views index and other object to serve a specific purpose such as searching, sorting, recombining data, etc. in the computer system.

 

Q.      Write Advantage of the database over flat files or file-based system.

Or what is the benefit of using the Database management system

Reduction of Redundancies

            In a file processing system, each user group maintains its own files resulting in a considerable amount of redundancy of the stored data. This results in wastage of storage space but more importantly may result in data inconsistencies. Also, the same data has to be updated more than once resulting in duplication of effort. The files that represent the same data may become inconsistent as some may be updated whereas others may not be. In database approach data can be stored at a single place or with controlled redundancy under DBMS, which saves space and does not permit inconsistency.

Shared Data

            A DBMS allows the sharing of database under its control by any number of application programs or users. A database belongs to the entire organization and is shared by all authorized users (may not be the complete data, why?). This scheme can be best explained with the help of a logical diagram (Figure 2). New applications can be built and added to the current system and data not currently stored can be stored.

Data Independence

            In the file-based system, the descriptions of data and logic for accessing the data are built into each application program making the program more dependent on data. A change in the structure of data may require alterations to programs. Database Management systems separates data descriptions from data. Hence it is not affected by changes. This is called Data Independence, where details of data are not exposed. DBMS provides an abstract view and hides details. For example, logically we can say that the interface or window to data provided by DBMS to a user may still be the same although the internal structure of the data may be changed. (Refer to Figure 2).

Improved Integrity

            Data Integrity refers to validity and consistency of data. Data Integrity means that the data should be accurate and consistent. This is done by providing some checks or constraints. These are consistency rules that the database is not permitted to violate. Constraints may apply to data items within a record or relationships between records. For example, the age of an employee can be between 18 and 70 years only. While entering the data for the age of an employee, the database should check this. However, if Grades of any student are entered, the data can be erroneously entered as Grade C for Grade A. In this case, DBMS will not be able to provide any check as both A and C are of the same data type and are valid values.

Efficient Data Access

            DBMS utilizes techniques to store and retrieve the data efficiently at least for unforeseen queries. A complex DBMS should be able to provide services to end users, where they can efficiently retrieve the data almost immediately.

Multiple User Interfaces

            Since many users having varying levels of technical knowledge use a database, a DBMS should be able to provide a variety of interfaces. This includes ─ 

a.         query language for casual users,

b.         programming language interfaces for application programmers,

c.         forms and codes for parametric users,

d.         menu-driven interfaces, and

e.         natural language interfaces for standalone users, these interfaces are still not available in standard form with a commercial database.

Improved Security

            Data is vital to any organisation and also confidential. In a shared system where multiple users share the data, all information should not be shared by all users. For example, the salary of the employees should not be visible to anyone other than the department dealing in this. Hence, database should be protected from unauthorized users. This is done by Database Administrator (DBA) by providing the usernames and passwords only to authorised users as well as granting privileges or the type of operation allowed. This is done by using security and authorisation subsystem. Only authorized users may use the database and their access types can be restricted to only retrieval, insert, update or delete or any of these. For example, the Branch Manager of any company may have access to all data whereas the Sales Assistant may not have access to salary details.

Improved Backup and Recovery

            A file-based system may fail to provide measures to protect data from system failures. This lies solely on the user by taking backups periodically. DBMS provides facilities for recovering the hardware and software failures. A backup and recovery subsystem is responsible for this. In case a program fails, it restores the database to a state in which it was before the execution of the program.

Support for concurrent transactions

            A transaction is defined as the unit of work. For example, a bank may be involved in a transaction where an amount of Rs.5000/- is transferred from account X to account Y. A DBMS also allows multiple transactions to occur simultaneously

Q.      Write down any six components of Database Management System.

Major Six components of Database Management System.

(i)           Data:- Data stored in a database include numerical data which may be integers(whole numbers only) or floating point numbers(decimal),and non-numerical data such as characters(alphabetic and numeric characters), date or logical(true or false). More advanced systems may include more complicated data entities such as pictures and images as data types.

(ii)         Standard Operation:  Standard operations are provided by most DBMS, these operations provide the user basic capabilities for data manipulation . Examples of these standard operations are sorting, deleting and selecting records.

(iii)        Data definition language (DDL):- DDL is the language used to describe the contents of the database. It is used  to describe, for example, attribute names(field names). Data types, location in the database etc.

(iv)        Data Manipulation and query language:- Normally  a query language is supported by a DBMS to form commands for input, edit, analysis, output reformatting, etc, Same degree of standardization has been achieved with SQL(structured Query Language).

(v)         Programming tools:- Besides commands and queries , the database should be accessible directly form application programs through function called(subroutine calls) in conventional programming languages.

(vi)        File Structure:- Every DBMS has its own internal structures used to organize the data although some common data models are used by most DBMS.

Q.      Write the difference between DBMS and RDBMS.

	DBMS	RDBMS
1.	DBMS applications store data as file.	RDBMS applications store data in a tabular form.
2.	In DBMS, data is generally stored in either a hierarchical form or a navigational form.	In RDBMS , the tables have an identifier called primary key and the data  values are stored in the form of tables.
3.	Normalization is not present in DBMS.	Normalisation is present in RDBMS
4.	DBMS does not apply any security with regards to data manipulation	RDBMS defines the integrity constraint for the purpose of ACID( Atomocity, consistency, Isolation and Durability) property.
5.	DBMS uses file system to store data, so there will be no relation between the tables.	In RDBMS , data values are stored in the form of tables, so a relationship between these data values will be stored in the form of a table as well.
6.	DBMS has to provide some uniform methods to access the stored information.	RDBMS system supports a tabular structure of the data and a relationship between them to access the stored information.
7.	DBMS  does not support distributed database.	RDBMS supports distributed database.
8.	DBMS is meant to be for small organization and deal with small data. It supports single user.	RDBMS is designed to handle large amount of data. It supports multiple users.
9.	Example of DBMS are Fox fro, Dbase	MS Access, Sal server, oracle etc.

Define DBMS(2073,2074)

Database Management System or DBMS in short refers to the technology of storing and retrieving users’ data with utmost efficiency along with appropriate security measures. This tutorial explains the basics of DBMS such as its architecture, data models, data schemas, data independence, E-R model, relation model, relational database design, and storage and file structure and much more.

 

Q. Write down the various types of database model(2074)

Hierarchical Database Model

            It is one of the oldest database models, This model arranges the files used in the database in a top- down structures which is similar to an upside – down tree

Advantages

i.             It is the easiest model of database.

ii.           Searching is fast and easy if parent is known

iii.          This model is very efficient in handling one – to many relationship

Disadvantages

i.             It is old and outdated database model

ii.           Modification and addition of the child node is very hard. Hence, it is non- flexible database model.

iii.          It can't handle many- to- many relationship

B)        Network Model

            The network database model looks similar to the hierarchical model but it is more flexible  to access the data. Unlike the hierarchical model, each child can be linked with more than one parents, so the record can be accessed from more than one parents, which are linked. But model is more flexible and has multidimensional connections.

Advantages

I.             More flexible than hierarchical model

II.           Reduced data redundancy because similar data is not stored in more than one file

III.         Searching is faster because of multidimensional pointers

Disadvantages

i.             It is very complex to design.

ii.           Needs long program to handle the relationship

iii.          Pointers , needed in the database, model increases overhead of database storage .

iv.          Less security model parents because data can be accessed from any

C)       Relational Model

            It is the best and the most common database model developed by E. F. Codd.The relational database model basically defines the structure or organization of data and a set of operations on that data, it is a simple model in which database is represented as a collections of 'relation' where relation is represented by a two dimensional tables. Because of its simplicity , it becomes commonly used database model.

Advantages

i.             Since one table is linked to other tables with some common fields, rules implemented on one table can easily be implemented to another table.

ii.           Referential integrity can be easily implemented.

iii.          The Database has very less data redundancy

iv.          Normalization of the database is possible.

v.            Rapid database processing and searching is possible

Disadvantages

            It is more complex than other models due to relationship with other tables. Too many rules make the database not very user- friendly

Q. Explain the Characteristics of Database.

            Traditionally, data was organized in file formats. DBMS was a new concept then, and all the research was done to make it overcome the deficiencies in traditional style of data management. A modern DBMS has the following characteristics −

·             Real-world entity − A modern DBMS is more realistic and uses real-world entities to design its architecture. It uses the behavior and attributes too. For example, a school database may use students as an entity and their age as an attribute.

·             Relation-based tables − DBMS allows entities and relations among them to form tables. A user can understand the architecture of a database just by looking at the table names.

·             Isolation of data and application − A database system is entirely different than its data. A database is an active entity, whereas data is said to be passive, on which the database works and organizes. DBMS also stores metadata, which is data about data, to ease its own process.

·             Less redundancy − DBMS follows the rules of normalization, which splits a relation when any of its attributes is having redundancy in values. Normalization is a mathematically rich and scientific process that reduces data redundancy.

·             Consistency − Consistency is a state where every relation in a database remains consistent. There exist methods and techniques, which can detect attempt of leaving database in inconsistent state. A DBMS can provide greater consistency as compared to earlier forms of data storing applications like file-processing systems.

·             Query Language − DBMS is equipped with query language, which makes it more efficient to retrieve and manipulate data. A user can apply as many and as different filtering options as required to retrieve a set of data. Traditionally it was not possible where file-processing system was used.

·             ACID Properties − DBMS follows the concepts of Atomicity, Consistency, Isolation, and Durability (normally shortened as ACID). These concepts are applied on transactions, which manipulate data in a database. ACID properties help the database stay healthy in multi-transactional environments and in case of failure.

·             Multiuser and Concurrent Access − DBMS supports multi-user environment and allows them to access and manipulate data in parallel. Though there are restrictions on transactions when users attempt to handle the same data item, but users are always unaware of them.

·             Multiple views − DBMS offers multiple views for different users. A user who is in the Sales department will have a different view of database than a person working in the Production department. This feature enables the users to have a concentrate view of the database according to their requirements.

·             Security − Features like multiple views offer security to some extent where users are unable to access data of other users and departments. DBMS offers methods to impose constraints while entering data into the database and retrieving the same at a later stage. DBMS offers many different levels of security features, which enables multiple users to have different views with different features. For example, a user in the Sales department cannot see the data that belongs to the Purchase department. Additionally, it can also be managed how much data of the Sales department should be displayed to the user. Since a DBMS is not saved on the disk as traditional file systems, it is very hard for miscreants to break the code.

Q. Explain the Types of Databases.

1.           Relational Databases

            This is the most common of all the different types of databases. In this, the data in a relational database is stored in various data tables. Each table has a key field which is used to connect it to other tables. Hence all the tables are related to each other through several key fields. These databases are extensively used in various industries and will be the one you are most likely to come across when working in IT.

Examples of relational databases are Oracle, Sybase and Microsoft SQL Server and they are often key parts of the process of software development. Hence you should ensure you include any work required on the database as part of your project when creating a project plan and estimating project costs.

2.            Operational Databases

            In its day to day operation, an organization generates a huge amount of data. Think of things such as inventory management, purchases, transactions and financials. All this data is collected in a database which is often known by several names such as operational/ production database, subject-area database (SADB) or transaction databases.

            An operational database is usually hugely important to Organisations as they include the customer database, personal database and inventory database ie the details of how much of a product the company has as well as information on the customers who buy them. The data stored in operational databases can be changed and manipulated depending on what the company requires.

 

3.            Database Warehouses

            Organisations are required to keep all relevant data for several years. In the UK it can be as long as 6 years. This data is also an important source of information for analysing and comparing the current year data with that of the past years which also makes it easier to determine key trends taking place. All this data from previous years are stored in a database warehouse. Since the data stored has gone through all kinds of screening, editing and integration it does not need any further editing or alteration.

With this database ensure that the software requirements specification (SRS) is formally approved as part of the project quality plan.

4.            Distributed Databases

            Many organisations have several office locations, manufacturing plants, regional offices, branch offices and a head office at different geographic locations. Each of these work groups may have their own database which together will form the main database of the company. This is known as a distributed database.

5.            End-User Databases

            There is a variety of data available at the workstation of all the end users of any organisation. Each workstation is like a small database in itself which includes data in spreadsheets, presentations, word files, note pads and downloaded files. All such small databases form a different type of database called the end-user database.

6.            External Database

            There is a sea of information available outside world which is required by an organisation. They are privately-owned data for which one can have conditional and limited access for a fortune. This data is meant for commercial usage. All such databases outside the organisation which are of use and limited access are together called external database.

7.           Hypermedia Database

            Most websites have various interconnected multimedia pages which might include text, video clips, audio clips, photographs and graphics. These all need to be stored and “called” from somewhere when the webpage if created. All of them together form the hypermedia database.

Please note that if you are creating such a database from scratch to be generous when creating a project plan, detailed when defining the business requirements documentation (BRD) and meticulous in your project cost controls. I have seen too many projects where the creation of one of these databases has caused scope creep and an out of control budget for a project.

8.            Navigational Database

            Navigational database has all the items which are references from other objects. In this, one has to navigate from one reference to other or one object to other. It might be using modern systems like XPath. One of its applications is the air flight management systems.

9.            In-Memory Database

            An in-memory databases stores data in a computer’s main memory instead of using a disk-based storage system. It is faster and more reliable than that in a disk. They find their application in telecommunications network equipments.

10.         Document-Oriented Database

            A document oriented database is a different type of database which is used in applications which are document oriented. The data is stored in the form of text records instead of being stored in a data table as usually happens.

11.         Real-Time Database

            A real-time database handles data which constantly keep on changing. An example of this is a stock market database where the value of shares change every minute and need to be updated in the real-time database. This type of database is also used in medical and scientific analysis, banking, accounting, process control, reservation systems etc. Essentially anything which requires access to fast moving and constantly changing information?

            Assume that this will require much more time than a normal relational database when it comes to the software testing life cycle, as these are much more complicated to efficiently test within normal timeframes.

12.        Analytical Database

            An analytical database is used to store information from different types of databases such as selected operational databases and external databases. Other names given to analytical databases are information databases, management databases or multi-dimensional databases. The data stored in an analytical database is used by the management for analysis purposes, hence the name. The data in an analytical database cannot be changed or manipulated.

Same as railway reservation system, airline also needs DBMS to keep records of flights arrival, departure and delay status.

So in short, one can say the DBMS is used everywhere around us and we cannot rely without DBMS.