Basic Terminologies

Data: Facts, figures, statistics etc. having no particular meaning (e.g. 1, ABC, 19 etc).

Record: Collection of related data items, e.g. in the above example the three data items had no meaning. But if we organise them in the following way, then they collectively represent meaningful information.

Roll	Name	Age
1	ABC	19

Table or Relation: Collection of related records.

Roll	Name	Age
1	ABC	19
2	DEF	22
3	XYZ	28

The columns of this relation are called Fields, Attributes or Domains. The rows are called Tuples or Records.

Database: Collection of related relations. Consider the following collection of tables:

T1

Roll	Name	Age
1	ABC	19
2	DEF	22
3	XYZ	28

T2

Roll	Address
1	KOL
2	DEL
3	MUM

T3

Roll	Year
1	I
2	II
3	I

T4

Year	Hostel
I	H1
II	H2

We now have a collection of 4 tables. They can be called a “related collection” because we can clearly find out that there are some common attributes existing in a selected pair of tables. Because of these common attributes we may combine the data of two or more tables together to find out the complete details of a student. Questions like “Which hostel does the youngest student live in?” can be answered now, although Age and Hostelattributes are in different tables.

In a database, data is organized strictly in row and column format. The rows are calledTuple or Record. The data items within one row may belong to different data types. On the other hand, the columns are often called Domain or Attribute. All the data items within a single attribute are of the same data type.

What is Management System?

A management system is a set of rules and procedures which help us to create organize and manipulate the database. It also helps us to add, modify delete data items in the database. The management system can be either manual or computerized.

The management system is important because without the existence of some kind of rules and regulations it is not possible to maintain the database. We have to select the particular attributes which should be included in a particular table; the common attributes to create relationship between two tables; if a new record has to be inserted or deleted then which tables should have to be handled etc. These issues must be resolved by having some kind of rules to follow in order to maintain the integrity of the database.

Summary:

DBMS stands for Database Management System. We can break it like this DBMS = Database + Management System. Database is a collection of data and Management System is a set of programs to store and retrieve those data. Based on this we can define DBMS like this: DBMS is a collection of inter-related data and set of programs to store & access those data in an easy and effective manner.

What is the need of DBMS?
Database systems are basically developed for large amount of data. When dealing with huge amount of data, there are two things that require optimization: Storage of data and retrieval of data.

Storage: According to the principles of database systems, the data is stored in such a way that it acquires lot less space as the redundant data (duplicate data) has been removed before storage. Let’s take a layman example to understand this:
In a banking system, suppose a customer is having two accounts, one is saving account and another is salary account. Let’s say bank stores saving account data at one place (these places are called tables we will learn them later) and salary account data at another place, in that case if the customer information such as customer name, address etc. are stored at both places then this is just a wastage of storage (redundancy/ duplication of data), to organize the data in a better way the information should be stored at one place and both the accounts should be linked to that information somehow. The same thing we achieve in DBMS.

Fast Retrieval of data: Along with storing the data in an optimized and systematic manner, it is also important that we retrieve the data quickly when needed. Database systems ensure that the data is retrieved as quickly as possible.

Three Schema Approach

We know that the same thing, if viewed from different angles produces difference sights. Likewise, the database that we have created already can have different aspects to reveal if seen from different levels of abstraction. The term Abstraction is very important here. Generally it means the amount of detail you want to hide. Any entity can be seen from different perspectives and levels of complexity to make it a reveal its current amount of abstraction. Let us illustrate by a simple example.

A computer reveals the minimum of its internal details, when seen from outside. We do not know what parts it is built with. This is the highest level of abstraction, meaning very few details are visible. If we open the computer case and look inside at the hard disc, motherboard, CD drive, CPU and RAM, we are in middle level of abstraction. If we move on to open the hard disc and examine its tracks, sectors and read-write heads, we are at the lowest level of abstraction, where no details are invisible.

In the same manner, the database can also be viewed from different levels of abstraction to reveal different levels of details. From a bottom-up manner, we may find that there are three levels of abstraction or views in the database. We discuss them here.

The word schema means arrangement – how we want to arrange things that we have to store. The diagram above shows the three different schemas used in DBMS, seen from different levels of abstraction.

The lowest level, called the Internal or Physical schema, deals with the description of how raw data items (like 1, ABC, KOL, H2 etc.) are stored in the physical storage (Hard Disc, CD, Tape Drive etc.). It also describes the data type of these data items, the size of the items in the storage media, the location (physical address) of the items in the storage device and so on. This schema is useful for database application developers and database administrator.

The middle level is known as the Conceptual or Logical Schema, and deals with the structure of the entire database. Please note that at this level we are not interested with the raw data items anymore, we are interested with the structure of the database. This means we want to know the information about the attributes of each table, the common attributes in different tables that help them to be combined, what kind of data can be input into these attributes, and so on. Conceptual or Logical schema is very useful for database administrators whose responsibility is to maintain the entire database.

The highest level of abstraction is the External or View Schema. This is targeted for the end users. Now, an end user does not need to know everything about the structure of the entire database, rather than the amount of details he/she needs to work with. We may not want the end user to become confused with astounding amount of details by allowing him/her to have a look at the entire database, or we may also not allow this for the purpose of security, where sensitive information must remain hidden from unwanted persons. The database administrator may want to create custom made tables, keeping in mind the specific kind of need for each user. These tables are also known as virtual tables, because they have no separate physical existence. They are crated dynamically for the users at runtime. Say for example, in our sample database we have created earlier, we have a special officer whose responsibility is to keep in touch with the parents of any under aged student living in the hostels. That officer does not need to know every detail except the Roll, Name, Addresss and Age. The database administrator may create a virtual table with only these four attributes, only for the use of this officer.

Summary:

Data and Related Structures

Data are actually stored as bits, or numbers and strings, but it is difficult to work with data at this level.

It is necessary to view data at different levels of abstraction.

Schema:

• Description of data at some level. Each level has its own schema.

We will be concerned with three forms of schemas:

• physical,
• conceptual, and
• external.

---

Physical Data Level

The physical schema describes details of how data is stored: files, indices, etc. on the random access disk system.  It also typically describes the record layout of files and type of files (hash, b-tree, flat).

Early applications worked at this level - explicitly dealt with details. E.g., minimizing physical distances between related data and organizing the data structures within the file (blocked records, linked lists of blocks, etc.)

Problem:

• Routines are hardcoded to deal with physical representation.
• Changes to data structures are difficult to make.
• Application code becomes complex since it must deal with details.
• Rapid implementation of new features very difficult.

---

Conceptual Data Level

Also referred to as the Logical level

Hides details of the physical level.

• In the relational model, the conceptual schema presents data as a set of tables.

The DBMS maps data access between the conceptual to physical schemas automatically.

• Physical schema can be changed without changing application:
• DBMS must change mapping from conceptual to physical.
• Referred to as physical data independence.

---

External Data Level

In the relational model, the external schema also presents data as a set of relations. An external schema specifies a view of the data in terms of the conceptual level. It is tailored to the needs of a particular category of users. Portions of stored data should not be seen by some users and begins to implement a level of security and simplifies the view for these users

Examples:

• Students should not see faculty salaries.
• Faculty should not see billing or payment data.

Information that can be derived from stored data might be viewed as if it were stored.

• GPA not stored, calculated when needed.

Applications are written in terms of an external schema. The external view is computed when accessed.  It is not stored. Different external schemas can be provided to different categories of users. Translation from external level to conceptual level is done automatically by DBMS at run time. The conceptual schema can be changed without changing application:

• Mapping from external to conceptual must be changed.
• Referred to as conceptual data independence.

Structure of DBMS

At very high level, a database is considered as shown in below diagram.  Let us see them in detail below.

• Applications: - It can be considered as a user friendly web page where the user enters the requests. Here he simply enters the details that he needs and presses buttons to get the data.
• End User: - They are the real users of the database. They can be developers, designers, administrator or the actual users of the database.
• DDL: - Data Definition Language (DDL) is a query fired to create database, schema, tables, mappings etc in the database. These are the commands used to create the objects like tables, indexes in the database for the first time. In other words, they create structure of the database.
• DDL Compiler: - This part of database is responsible for processing the DDL commands. That means these compiler actually breaks down the command into machine understandable codes. It is also responsible for storing the metadata information like table name, space used by it, number of columns in it, mapping information etc.
• DML Compiler: - When the user inserts, deletes, updates or retrieves the record from the database, he will be sending request which he understands by pressing some buttons. But for the database to work/understand the request, it should be broken down to object code. This is done by this compiler. One can imagine this as when a person is asked some question, how this is broken down into waves to reach the brain!
• Query Optimizer: - When user fires some request, he is least bothered how it will be fired on the database. He is not all aware of database or its way of performance. But whatever be the request, it should be efficient enough to fetch, insert, update or delete the data from the database. The query optimizer decides the best way to execute the user request which is received from the DML compiler. It is similar to selecting the best nerve to carry the waves to brain!
• Stored Data Manager: - This is also known as Database Control System. It is one the main central system of the database. It is responsible for various tasks
• It converts the requests received from query optimizer to machine understandable form.  It makes actual request inside the database. It is like fetching the exact part of the brain to answer.
• It helps to maintain consistency and integrity by applying the constraints.  That means, it does not allow inserting / updating / deleting any data if it has child entry. Similarly it does not allow entering any duplicate value into database tables.
• It controls concurrent access. If there is multiple users accessing the database at the same time, it makes sure, all of them see correct data. It guarantees that there is no data loss or data mismatch happens between the transactions of multiple users.
• It helps to backup the database and recover data whenever required. Since it is a huge database and when there is any unexpected exploit of transaction, and reverting the changes are not easy. It maintains the backup of all data, so that it can be recovered.
• Data Files: - It has the real data stored in it. It can be stored as magnetic tapes, magnetic disks or optical disks.
• Compiled DML: - Some of the processed DML statements (insert, update, delete) are stored in it so that if there is similar requests, it will be re-used.
• Data Dictionary: - It contains all the information about the database. As the name suggests, it is the dictionary of all the data items. It contains description of all the tables, view, materialized views, constraints, indexes, triggers etc.

Database Users and Administrators

A primary goal of a database system is to retrieve information from and store new information in the database. People who work with a database can be categorized as database users or database administrators.

1) Database Users and User Interfaces

There are four different types of database-system users, differentiated by the way they expect to interact with the system. Different types of user interfaces have been designed for the different types of users:

• Naive users are unsophisticated users who interact with the system by invoking one of the application programs that have been written previously. For example, a bank teller who needs to transfer $50 from account Ato account B invokes a program calledtransfer. This program asks the teller for the amount of money to be transferred, the account from which the money is to be transferred, and the account to which the money is to be transferred.

As another example, consider a user who wishes to find her account balance over the World Wide Web. Such a user may access a form, where she enters her account number. An application program at the Web server then retrieves the account balance, using the given account number, and passes

this information back to the user.

The typical user interface for naive users is a forms interface, where the user can fill in appropriate fields of the form. Naive users may also simply read reports generated from the database.

•Application programmers are computer professionals who write application programs. Application programmers can choose from many tools to develop user interfaces.Rapid application development (RAD)tools are tools that enable an application programmer to construct forms and reports without writing a program. There are also special types of programming languages that combine imperative control structures (for example, for loops, while loops and if-then-else statements) with statements of the data manipulation language. These languages, sometimes calledfourth-generation languages,often

include special features to facilitate the generation of forms and the display of data on the screen. Most major commercial database systems include a fourth generation language.

•Sophisticated users interact with the system without writing programs. Instead, they form their requests in a database query language. They submit each such query to a query processor, whose function is to break down DML statements into instructions that the storage manager understands. Analysts who submit queries to explore data in the database fall in this category.

Online analytical processing (OLAP)tools simplify analysts’ tasks by letting them view summaries of data in different ways. For instance, an analyst can see total sales by region (for example, North, South, East, and West), or by product, or by a combination of region and product (that is, total sales of each product in each region). The tools also permit the analyst to select specific regions, look at data in more detail (for example, sales by city within a region) or look at the data in less detail (for example, aggregate products together by category).

Another class of tools for analysts is data mining tools, which help them find certain kinds of patterns in data.

•Specialized users are sophisticated users who write specialized database applications that do not fit into the traditional data-processing framework. Among these applications are computer-aided design systems, knowledge base and expert systems, systems that store data with complex data types (for

example, graphics data and audio data), and environment-modeling systems.

2 Database Administrator

One of the main reasons for using DBMSs is to have central control of both the data and the programs that access those data. A person who has such central control over the system is called a database administrator(DBA). The functions of a DBA include:

•Schema definition. The DBA creates the original database schema by executing a set of data definition statements in theDDL.

•Storage structure and access-method definition.

•Schema and physical-organization modification.The DBA carries out changes to the schema and physical organization to reflect the changing needs of the organization, or to alter the physical organization to improve performance.

•Granting of authorization for data access. By granting different types of authorization, the database administrator can regulate which parts of the database various users can access. The authorization information is kept in a special system structure that the database system consults whenever someone attempts to access the data in the system.

•Routine maintenance. Examples of the database administrator’s routine maintenance activities are:

a) Periodically backing up the database, either onto tapes or onto remote servers, to prevent loss of data in case of disasters such as flooding.

b) Ensuring that enough free disk space is available for normal operations, and upgrading disk space as required.

c) Monitoring jobs running on the database and ensuring that performance is not degraded by very expensive tasks submitted by some users.

Summary:

DBA Responsibilities

• Installation, configuration and upgrading of Microsoft SQL Server/MySQL/Oracle server software and related products.
• Evaluate MSSQL/MySQL/Oracle features and MSSQL/MySQL/Oracle related products.
• Establish and maintain sound backup and recovery policies and procedures.
• Take care of the Database design and implementation.
• Implement and maintain database security (create and maintain users and roles, assign privileges).
• Database tuning and performance monitoring.
• Application tuning and performance monitoring.
• Setup and maintain documentation and standards.
• Plan growth and changes (capacity planning).
• Work as part of a team and provide 7×24 supports when required.
• Do general technical trouble shooting and give consultation to development teams.
• Interface with MSSQL/MySQL/Oracle for technical support.
• ITIL Skill set requirement (Problem Management/Incident Management/Chain Management etc)

Types of DBA

1. Administrative DBA – Work on maintaining the server and keeping it running. Concerned with backups, security, patches, replication, etc. Things that concern the actual server software.
2. Development DBA – works on building queries, stored procedures, etc. that meet business needs. This is the equivalent of the programmer. You primarily write T-SQL.
3. Architect – Design schemas. Build tables, FKs, PKs, etc. Work to build a structure that meets the business needs in general. The design is then used by developers and development DBAs to implement the actual application.
4. Data Warehouse DBA – Newer role, but responsible for merging data from multiple sources into a data warehouse. May have to design warehouse, but cleans, standardizes, and scrubs data before loading. In SQL Server, this DBA would use DTS heavily.
5. OLAP DBA – Builds multi-dimensional cubes for decision support or OLAP systems. The primary language in SQL Server is MDX, not SQL here

Application DBA- Application DBAs straddle the fence between the DBMS and the application software and are responsible for ensuring that the application is fully optimized for the database and vice versa. They usually manage all the application components that interact with the database and carry out activities such as application installation and patching, application upgrades, database cloning, building and running data cleanup routines, data load process management, etc.

Architecture of Database

Database architecture can be 2-tier or 3 tier architecture based on how users are connected to the database to get their request done. They can either directly connect to the database or their request is received by intermediary layer, which synthesizes the request and then it sends to database.

2-tier Architecture

In 2-tier architecture, application program directly interacts with the database. There will not be any user interface or the user involved with database interaction. Imagine a front end application of School, where we need to display the reports of all the students who are opted for different subjects. In this case, the application will directly interact with the database and retreive all required data. Here no inputs from the user are required. This involves 2-tier architecture of the database.

Let us consider another example of two tier architecture. Consider a railway ticket reservation system. How does this work? Imagine a person is reserving the ticket from Delhi to Goa on particular day. At the same time another person in some other place of Delhi is also reserving the ticket to Goa on the same day for the same train. Now there is a requirement for two tickets, but for different persons. What will reservation system do? It takes the request from both of them, and queues the requests entered by each of them. Here the request entered to application layer and request is sent to database layer. Once the request is processed in database, the result is sent back to application layer for the user.

• Easy to understand as it directly communicates with the database.
• Requested data can be retrieved very quickly, when there is less number of users.
• Easy to modify – any changes required, directly requests can be sent to database
• Easy to maintain – When there are multiple requests, it will be handled in a queue and there will not be any chaos.

Disadvantages of 2-tier architecture:

• It would be time consuming, when there is huge number of users. All the requests will be queued and handed one after another. Hence it will not respond to multiple users at the same time.
• This architecture would little cost effective.

3-tier Architecture

3-tier architecture is the most widely used database architecture. It can be viewed as below.

• Presentation layer / User layer is the layer where user uses the database. He does not have any knowledge about underlying database. He simply interacts with the database as though he has all data in front of him. You can imagine this layer as a registration form where you will be inputting your details.  Did you ever guessed, after pressing ‘submit’ button where the data goes? No right? You just know that your details are saved. This is the presentation layer where all the details from the user are taken, sent to the next layer for processing.
• Application layer is the underlying program which is responsible for saving the details that you have entered, and retrieving your details to show up in the page. This layer has all the business logics like validation, calculations and manipulations of data, and then sends the requests to database to get the actual data. If this layer sees that the request is invalid, it sends back the message to presentation layer.  It will not hit the database layer at all.
• Data layer or Database layer is the layer where actual database resides. In this layer, all the tables, their mappings and the actual data present. When you save you details from the front end, it will be inserted into the respective tables in the database layer, by using the programs in the application layer. When you want to view your details in the web browser, a request is sent to database layer by application layer. The database layer fires queries and gets the data. These data are then transferred to the browser (presentation layer) by the programs in the application layer.

Advantages of 3-tier architecture:

• Easy to maintain and modify. Any changes requested will not affect any other data in the database. Application layer will do all the validations.
• Improved security. Since there is no direct access to the database, data security is increased. There is no fear of mishandling the data. Application layer filters out all the malicious actions.
• Good performance. Since this architecture cache the data once retrieved, there is no need to hit the database for each request. This reduces the time consumed for multiple requests and hence enables the system to respond at the same time.

Disadvantages of 3-tier architecture are that it is little more complex and little more effort is required in terms of hitting the database.

Characteristics of a Good DBMS

1. Should be able to store all kinds of data that exists in this real world. Since we need to work with all kinds of data and requirements, database should be strong enough to store all kinds of data that is present around us.
2. Should be able to relate the entities / tables in the database by means of a relation. i.e.; any two tables should be related. Let us say, an employee works for a department. This implies that Employee is related to a particular department. We should be able to define such a relationship between any two entities in the database. There should not be any table lying without any mapping.
3. Data and application should be isolated. Because database is a system which gives the platform to store the data, and the data is the one which allows the database to work. Hence there should be clear differentiation between them.
4. There should not be any duplication of data in the database. Data should be stored in such a way that it should not be repeated in multiple tables. If repeated, it would be unnecessary waste of DB space and maintaining such data becomes chaos.
5. DBMS has a strong query language. Once the database is designed, this helps the user to retrieve and manipulate the data. If a particular user wants to see any specific data, he can apply as many filtering conditions that he wants and pull the data that he needs.
6. Multiple users should be able to access the same database, without affecting the other user. i.e.; if teachers want to update a student’s marks in Results table at the same time, then they should be allowed to update the marks for their subjects, without modifying other subject marks. A good database should support this feature.
7. It supports multiple views to the user, depending on his role. In a school database, Students will able to see only their reports and their access would be read only. At the same time teachers will have access to all the students with the modification rights. But the database is the same. Hence a single database provides different views to different users.
8. Database should also provide security, i.e.; when there are multiple users are accessing the database, each user will have their own levels of rights to see the database.  Some of them will   be allowed to see whole database, and some will have only partial rights. For example, instructor who is teaching Physics will have access to see and update marks of his subject. He will not have access for other subjects. But the HOD will have full access on all the subjects.
9. Database should also support ACID property. i.e.; while performing any transactions like insert, update and delete, database makes sure that the real purpose of the data is not lost. For example, if a student’s address is updated, then it should make sure that there is no duplicate data is created nor there is any data mismatch for that student.

As we now know what is a database, who would be the users of database? Of course the developers will be using this database to design and develop.  Who else? There would be an administrator, who keeps watching the database for its usages, who is accessing it, giving access to other users, limiting the security for the users, and any other maintenance work of the database. And there is one more end users. These end users are the real group of people who really uses the database and takes the advantages of database. In School database, teachers, students are the end users, who really uses the database in their daily needs.

Where all are these database used? Everywhere!! Now a day, database is used in each and every place. We can see the use of database in supermarkets, stock exchange, college, library, ATMs, offices, banks, hospitals etc.

Summary:

Characteristics

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.

Various Objectives of Database Management System

Mass Storage

DBMS can store a lot of data in it. So for all the big firms, DBMS is really ideal technology to use. It can store thousands of records in it and one can fetch all that data whenever it is needed.

Removes Duplicity

If you have lots of data then data duplicity will occur for sure at any instance. DBMS guarantee it that there will be no data duplicity among all the records. While storing new records, DBMS makes sure that same data was not inserted before.

Multiple Users Access

No one handles the whole database alone. There are lots of users who are able to access database. So this situation may happen that two or more users are accessing database. They can change whatever they want, at that time DBMS makes it sure that they can work concurrently.

Data Protection

Information such as bank details, employee’s salary details and sale purchase details should always be kept secured. Also all the companies need their data secured from unauthorized use. DBMS gives a master level security to their data. No one can alter or modify the information without the privilege of using that data.

Data Back up and recovery

Sometimes database failure occurs so there is no option like one can say that all the data has been lost. There should be a backup of database so that on database failure it can be recovered. DBMS has the ability to backup and recover all the data in database.

Everyone can work on DBMS

There is no need to be a master of programming language if you want to work on DBMS. Any accountant who is having less technical knowledge can work on DBMS. All the definitions and descriptions are given in it so that even a non-technical background w=person can work on it.

Integrity

Integrity means your data is authentic and consistent. DBMS has various validity checks that make your data completely accurate and consistence.

Platform Independent

One can run dbms at any platform. No particular platform is required to work on database management system.

So it was all about Various Objectives of Database Management System. If you have any question regarding this topic then please comment below.