Chapter 4

Introduction to DBMS

Data vs. Information

Data are simply facts or figures — bits of information, but not information itself. When data areprocessed, interpreted, organized, structured or presented so as to make them meaningful or useful,they are called information. Information provides context for data.

Data and information are interrelated. Data usually refers to raw data, or unprocessed data. It is thebasic form of data, data that hasn’t been analyzed or processed in any manner. Once the data isanalyzed, it is considered as information. Information is "knowledge communicated or receivedconcerning a particular fact or circumstance." Information is a sequence of symbols that can beinterpreted as a message. It provides knowledge or insight about a certain matter.

Some differences between data and information:

Data is used as input for the computer system. Information is the output of data.

Data is unprocessed facts figures. Information is processed data.

Data doesn’t depend on Information. Information depends on data.

Data is not specific. Information is specific.

Data is a single unit. A group of data which carries news and meaning is called Information.

Data doesn’t carry a meaning. Information must carry a logical meaning.

Data is the raw material. Information is the product.

Data Hierarchy

What Is Data Management?

• Data management is the development and execution of processes,architectures, policies, practices and procedures in order to manage theinformation generated by an organization.

• The effective management of data within any organization has grown inimportance in recent years as organizations are subject to an increasingnumber of compliance regulations, large increases in storage informationstorage capacity and the sheer amount of data and documents beinggenerated by organizations.

• This rate of growth is not expected to slow down as IDC(International DataCorporation) predicts the amount of information generated will increase 29fold by 2020. These large sums of data from ERP systems, CRM systems andgeneral business documents if often referred to as big data.

Database

• A database is a collection of information that is organized so that itcan be easily accessed, managed and updated.

• Data is organized into rows, columns and tables, and it is indexed tomake it easier to find relevant information. Data gets updated,expanded and deleted as new information is added. Databasesprocess workloads to create and update themselves, querying thedata they contain and running applications against it.

Database management system(DBMS) A database management system is the software system that allows users to define, create and maintain a

database and provides controlled access to the data. A Database Management System (DBMS) is basically acollection of programs that enables users to store, modify, and extract information from a database as perthe requirements. DBMS is an intermediate layer between programs and the data. Programs access theDBMS, which then accesses the data. There are different types of DBMS ranging from small systems that runon personal computers to huge systems that run on mainframes.

Examples are dbase, FoxPro, IMS and Oracle, postgres, MySQL, SQL Servers and DB2 etc.

Advantages of DBMS:• Controls Redundancy

• Integrity can be enforced

• Inconsistency can be avoided

• Data can be shared

• Standards can be enforced

• Restricts unauthorized access

• Solves Enterprise Requirement than Individual Requirement

• Provides Backup and Recovery

• Concurrency Control

Disadvantages of DBMS• Complexity

• Size

• Performance

• Higher impact of a failure

• Cost of DBMS

• Additional Hardware costs

• Cost of Conversion

File management system(FMS)/Flat File system

• A file management system is a type of software that manages

data files in a computer system. It has limited capabilities and

is designed to manage individual or group files, such as special

office documents and records. It may display report details,

like owner, creation date, state of completion and similar features

useful in an office environment.

• A file management system is also known as a file manager.

Characteristics of File Processing System:

• It is a group of files storing data of an organization.• Each file is independent from one another.• Each file is called a flat file.• Each file contained and processed information for one specific function,such as accounting or inventory.• Files are designed by using programs written in programming languagessuch as COBOL, C, C++.• The physical implementation and access procedures are written intodatabase application; therefore, physical changes resulted in intensiverework on the part of the programmer.• As systems became more complex, file processing systems offered littleflexibility, presented many limitations, and were difficult to maintain.

Limitations of file system

• Separated and Isolated Data• Duplication of data• Data Dependence• Difficulty in representing data from the user’s view• Data Inflexibility• Incompatible file formats• Data Security• Transactional Problems(Atomicity Problems)• Concurrency problems• Poor data modeling of real world

Advantages

• Easy to understand.

• Easy to implement.

• Low Initial investment .

• Less hardware and software requirements.

• Less Skills set are required to handle flat database systems.

• Best for small databases.

• Low overhead cost

• Not Required Dedicated staff

When flat file systems are suitable

• Simple and easy system development

• Manage few data file(2-3 data files)

• Security is not major concern

• Concurrent access is not needed

Application areas of Database Systems

• Airlines and railways

• Banking• Education

• Telecommunications

• Credit card transactions• E-commerce

• Health care information system & electronic patient record

• Digital libraries and digital publishing

• Finance

• Sales

• Human resources

View of Data in DBMS

• Abstraction is one of the main features of database systems. Hiding irrelevant details from user and providing abstract view of data to users, helps in easy and efficient user-database interaction.

• To understand the view of data, you must have a basic knowledge of data abstraction and instance & schema.

• Data abstraction

• Instance and schema

Data Abstraction in DBMS

• Database systems are made-up of complex data structures. To easethe user interaction with database, the developers hide internalirrelevant details from users. This process of hiding irrelevant detailsfrom user is called data abstraction.

Fig: Three levels of data abstractions

• Physical level: This is the lowest level of data abstraction. It describes howdata is actually stored in database. You can get the complex data structuredetails at this level.

• Logical level: This is the middle level of 3-level data abstractionarchitecture. It describes what data is stored in database.

• View level: Highest level of data abstraction. This level describes the user interaction with database system.

• Example: Let’s say we are storing customer information in a customertable. At physical level these records can be described as blocks of storage(bytes, gigabytes, terabytes etc.) in memory. These details are often hiddenfrom the programmers.

• At the logical level these records can be described as fields and attributesalong with their data types, their relationship among each other can belogically implemented. The programmers generally work at this levelbecause they are aware of such things about database systems.

• At view level, user just interact with system with the help of GUI and enterthe details at the screen, they are not aware of how the data is stored andwhat data is stored; such details are hidden from them.

Database instance and schemasDefinition of schema: Design of a database is called the schema. Schema is of threetypes: Physical schema, logical schema and view schema.

• The design of a database at physical level is called physical schema, how the data stored in blocks of storage is described at this level.

• Design of database at logical level is called logical schema, programmers anddatabase administrators work at this level, at this level data can be described ascertain types of data records gets stored in data structures, however the internaldetails such as implementation of data structure is hidden at this level (availableat physical level).

• Design of database at view level is called view schema. This generally describesend user interaction with database systems.

Definition of instance: The data stored in database at a particular moment of timeis called instance of database. Database schema defines the variable declarations intables that belong to a particular database; the value of these variables at amoment of time is called the instance of that database.

Data Independence

• The capacity to change the database schema without affecting applicationprogram is called data independence. A database system normally containsa lot of data in addition to users’ data. For example, it stores data aboutdata, known as metadata, to locate and retrieve data easily. It is ratherdifficult to modify or update a set of metadata once it is stored in thedatabase. But as a DBMS expands, it needs to change over time to satisfythe requirements of the users. If the entire data is dependent, it wouldbecome a tedious and highly complex job.

Logical Data IndependenceLogical data is data about database, that is, it stores information about how data is managedinside. For example, a table (relation) stored in the database and all its constraints, applied on thatrelation.Logical data independence is a kind of mechanism, which liberalizes itself from actual data storedon the disk. If we do some changes on table format, it should not change the data residing on thedisk.

Physical Data IndependenceAll the schemas are logical, and the actual data is stored in bit format on the disk. Physical dataindependence is the power to change the physical data without impacting the schema or logicaldata.For example, in case we want to change or upgrade the storage system itself − suppose we wantto replace hard-disks with SSD − it should not have any impact on the logical data or schemas.

Database models

• Data models define how the logical structure of a database is modeled. DataModels are fundamental entities to introduce abstraction in a DBMS. Datamodels define how data is connected to each other and how they are processedand stored inside the system.

• A Data Model is a logical structure of Database. It describes the design ofdatabase to reflect entities, attributes, relationship among data, constrains etc.

• The very first data model could be flat data-models, where all the data used areto be kept in the same plane. Earlier data models were not so scientific, hencethey were prone to introduce lots of duplication and update anomalies.

Types of database models

• Hierarchical Model

• Network Model

• Entity-Relational Model

• Relational Model

• Object Oriented Model

• Object-Relational Model

Hierarchical Model• In hierarchical model, data is organized into a tree like structure with each record

is having one parent record and many children. The main drawback of this model is that, it can have only one to many relationships between nodes.

Course Table:

Advantages of Hierarchical Model

Easy to understand

Performance is better than relational data model

Disadvantages of Hierarchical Model

Difficult to access values at lower level

This model may not be flexible to accommodate the dynamic needs of

an organization

Deletion of parent node result in deletion of child node forcefully

Extra space is required for the storage of pointers

Entity-Relational Model• Entity-Relationship (ER) Model is based on the notion of real-world entities

and relationships among them. While formulating real-world scenario into the database model, the ER Model creates entity set, relationship set, general attributes and constraints.

• ER Model is best used for the conceptual design of a database.

Here are the geometric shapes and their meaning in an E-R Diagram :-

• Rectangle: Representes Entity sets.

• Ellipses/oval: Attribues

• Diamonds: Relationship Set

• Lines: They link attributes to Entity Sets and Entity sets to Relationship Set

• Double Ellipses: Multivalued Attributes

• Dashed Ellipses: Derived Attributes

• Double Rectangles: Weak Entity Sets

• Double Lines: Total participation of an entity in a relationship set

•Entity − An entity in an ER Model is a real-world entity having properties called attributes.•Every attribute is defined by its set of values called domain. For example, in a school database, a student isconsidered as an entity. Student has various attributes like name, age, class, etc.•Relationship − The logical association among entities is called relationship. Relationships are mapped with entitiesin various ways. Mapping cardinalities define the number of association between two entities.•Mapping cardinalities −

• one to one• one to many• many to one• many to many

Multivalued Attributes: An attribute that can hold multiple values is known as multivalued attribute. We represent it with double ellipses

in an E-R Diagram. E.g. A person can have more than one phone numbers so the phone number attribute is multivalued.

Derived Attribute: A derived attribute is one whose value is dynamic and derived from another attribute. It is represented by dashed

ellipses in an E-R Diagram. E.g. Person age is a derived attribute as it changes over time and can be derived from another attribute

(Date of birth).

Relational model• In relational model, the data and relationships are represented by collection of inter-

related tables. Each table is a group of column and rows, where column representsattribute of an entity and rows represents records.

• Relational data model is the primary data model, which is used widely around the worldfor data storage and processing. This model is simple and it has all the properties andcapabilities required to process data with storage efficiency.

Basic Concepts• Tables − In relational data model, relations are saved in the format of Tables. This format

stores the relation among entities. A table has rows and columns, where rows represents records and columns represent the attributes.

• Tuple − A single row of a table, which contains a single record for that relation is called a tuple.

• Relation instance − A finite set of tuples in the relational database system represents relation instance. Relation instances do not have duplicate tuples.

• Relation schema − A relation schema describes the relation name (table name), attributes, and their names.

• Relation key − Each row has one or more attributes, known as relation key, which can identify the row in the relation (table) uniquely.

• Attribute domain − Every attribute has some pre-defined value scope, known as attribute domain.

Network Model• A network database model is a database model that allows multiple records to be

linked to the same owner file.

• It is an extension of hierarchical database model.

• The model can be seen as an upside down tree where the branches are the member information linked to the owner, which is the bottom of the tree.

• The multiple linkages which this information allows the network database model to be very flexible.

• In addition, the relationship that the information has in the network database model is defined as many-to-many relationship because one owner file can be linked to many member files and vice versa.

• It describes data and relations between data by using graph rather than tree like structure.

ADVANTAGES OF NETWORK MODEL1.) Conceptual simplicity-Just like the hierarchical model, the network model is also conceptually simple and easy to design.

2.) Capability to handle more relationship types-The network model can handle the one to many and many to many relationships which is real help in modeling the real life situations.

3.) Ease of data access-The data access is easier and flexible than the hierarchical model.

4.) Data integrity- The network model does not allow a member to exist without an owner.

5.) Data independence- The network model is better than the hierarchical model in isolating the programs from the complex physical storage details.

6.) Database standards

DISADVANTAGE OF NETWORK MODEL

1.) System complexity- All the records are maintained using pointers and hence the whole database structure becomes very complex.

2.) Operational Anomalies- The insertion, deletion and updating operations of any record require large number of pointers adjustments.

3.) Absence of structural independence-structural changes to the database is very difficult.

Object oriented data model• An object database is a database management system in which

information is represented in the form of objects as used in object-oriented programming. Object databases are different from relationaldatabases which are table-oriented.

• Object-oriented database management systems (OODBMSs) alsocalled ODBMS (Object Database Management System) combinesdatabase capabilities with object-oriented programming languagecapabilities. OODBMSs allow object-oriented programmers todevelop the product, store them as objects, and replicate or modifyexisting objects to make new objects within the OODBMS.

A core object-oriented data model consists of the following basic object-oriented concepts:

(1) object and object identifier: Any real world entity is uniformly modeled as an object (associated with a unique id: used to pinpoint an object to retrieve).

(2) attributes and methods: every object has a state (the set of values for theattributes of the object) and a behavior (the set of methods - program code - whichoperate on the state of the object). The state and behavior encapsulated in anobject are accessed or invoked from outside the object only through explicitmessage passing.

(3) class: a means of grouping all the objects which share the same set of attributesand methods. An object must belong to only one class as an instance of that class(instance-of relationship). A class is similar to an abstract data type. A class mayalso be primitive (no attributes), e.g., integer, string, Boolean.

(4) Class hierarchy and inheritance: derive a new class (subclass) from an existingclass (superclass). The subclass inherits all the attributes and methods of theexisting class and may have additional attributes and methods.

Object relational modelCombines the advantages of relational database plus object oriented

database.

Database and objects is manipulated collectively with queries.

A programming API(Application Program Interface) for storing and retrieving objects

Database Languages: DDL, DML, DCLLanguage that are used to interact with database management

system are known as database Language.

A DBMS must provide appropriate languages and interfaces for eachcategory of users to express database queries and updates.

Database Languages are used to create and maintain databaseon computer.

Types of database languages are Data Definition Language (DDL),Data Manipulation Language (DML) and Data Control Language (DCL).

Data Definition Language (DDL)• DDL is used for specifying the database structure or schema(Eg.

Create,delete or modify)

• It is a type of language that allows the DBA or user to depict and name those entities, attributes, and relationships that are required for the application along with any associated integrity and security constraints

• Here are the lists of tasks that come under DDL:CREATE - used to create objects in the database

ALTER - used to alters the structure of the database

DROP - used to delete objects from the database

TRUNCATE - used to remove all records from a table, including all spaces allocated for the records are removed

COMMENT - used to add comments to the data dictionary

RENAME - used to rename an object

Example of DDL statement

Syntax for create queryCREATE TABLE table_name (

column1 datatype,column2 datatype,column3 datatype,....

);

Data Manipulation Language(DML)

• It is a language that provides a set of operations to support the basic data manipulation operations on the data held in the databases.

• It allows users to insert, update, delete and retrieve data from the database.

• DML is used for accessing and manipulating data in a database

• Basic DML Functions are:To read records from table(s) – SELECT

To insert record(s) into the table(s) – INSERT

Update the data in table(s) – UPDATE

Delete all the records from the table – DELETE

Example of DML statementSyntaxINSERT INTO table_nameVALUES (value1, value2, value3, ...);

Example

INSERT INTO Customers VALUES (‘Cardinal’, ’Stavanger’, ’Norway’);

SyntaxINSERT INTO table_name (column1, column2, column3, ...)VALUES (value1, value2, value3, ...);

Example

INSERT INTO Customers (CustomerName, City, Country)VALUES ('Cardinal', 'Stavanger', 'Norway');

Types of DML• Procedural DMLs: a user specifies what data are required and how to

get those data. these are normally low level data manipulation languages. Relational algebra is an example of procedural DML.

• Nonprocedural DMLs: a user specifies what data are needed without specifying how to get those data. these are normally high level DML. SQL is an example of nonprocedural DML.

• SELECT * from student

WHERE roll_no=4;

Data Control Language (DCL)

• DCL statements control access to data and the database using statements such as GRANT and REVOKE.

• A privilege can either be granted to a User with the help of GRANT statement. The privileges assigned can be SELECT, ALTER, DELETE, EXECUTE, INSERT, INDEX etc. In addition to granting of privileges, you can also revoke (taken back) it by using REVOKE command.

To grant access to user – GRANT

To revoke access from user – REVOKE

• In practical data definition language, data manipulation language and data control languages are not separate language; rather they are the parts of a single database language such as SQL.

• GRANT privilege_nameON object_nameTO {user_name |PUBLIC |role_name}[WITH GRANT OPTION];

• privilege_name is the access right or privilege granted to the user. Some of the access rights are ALL, EXECUTE, and SELECT.

• object_name is the name of an database object like TABLE, VIEW, STORED PROC and SEQUENCE.

• user_name is the name of the user to whom an access right is being granted.

• user_name is the name of the user to whom an access right is being granted.

• PUBLIC is used to grant access rights to all users.

• ROLES are a set of privileges grouped together.

• WITH GRANT OPTION - allows a user to grant access rights to other users.

• GRANT SELECT ON employee TO user1; This command grants a SELECTpermission on employee table to user1.You should use the WITH GRANT optioncarefully because for example if you GRANT SELECT privilege on employee tableto user1 using the WITH GRANT option, then user1 can GRANT SELECT privilegeon employee table to another user, such as user2 etc. Later, if you REVOKE theSELECT privilege on employee from user1, still user2 will have SELECT privilege onemployee table.

Database users and administrators• A primary goal of a database system is to retrieve information from

and store new information into the database. People who work with a database can be categorized as database users or database administrators.

• Database users are the one who really use and take the benefits of database. There will be different types of users depending on their need and way of accessing the database.

1.Application Programmers - They are the developers who interact with thedatabase by means of DML queries. These DML queries are written in theapplication programs like C, C++, JAVA, Pascal etc. These queries are converted intoobject code to communicate with the database. For example, writing a C programto generate the report of employees who are working in particular department willinvolve a query to fetch the data from database. It will include a embedded SQLquery in the C Program.

2. Sophisticated Users - They are database developers, who write SQL queries toselect/insert/delete/update data. They do not use any application or programs torequest the database. They directly interact with the database by means of querylanguage like SQL. These users will be scientists, engineers, analysts who thoroughlystudy SQL and DBMS to apply the concepts in their requirement. In short, we can saythis category includes designers and developers of DBMS and SQL.

3. Specialized Users - These are also sophisticated users, but they write specialdatabase application programs. They are the developers who develop the complexprograms to the requirement.

4. Stand-alone Users - These users will have stand –alone database for their personaluse. These kinds of database will have readymade database packages which will havemenus and graphical interfaces.

5. Native Users - these are the users who use the existing application to interact withthe database. For example, online library system, ticket booking systems, ATMs etcwhich has existing application and users use them to interact with the database to fulfilltheir requests.

Database Administrator• The person who has the central control over a database system is called Database Administrator (DBA).

• A database administrator (DBA) directs or performs all activities related to maintaining asuccessful database environment. Responsibilities include designing, implementing, and maintaining thedatabase system; establishing policies and procedures pertaining to the management, security,maintenance, and use of the database management system; and training employees in databasemanagement and use

The database administrator has the following functions in a database system.

• Schema Definition: The database administrator creates the original database schema by executing a set of data definition statements in DDL.

• Storage structure an access method definition.

• Schema and physical or organization modification: The database administrator performs the changes to the schema according to the needs of organizations or physical needs to improve the database performance.

• Provide the granting of authorization to access data: The database administrator can decide the which parts of the database can be accessed by a user, by using the different types of authorization methods.

• Database maintenance: The database maintenance includes the following processes.

Regular backing up of the database.

Ensuring the disk space for performing the required operations.

Monitoring the jobs running on the database.

• Installing and upgrading the DBMS Servers: - DBA is responsible for installing a new DBMS server for the new projects. He is also responsible for upgrading these servers as there are new versions comes in the market or requirement. If there is any failure in upgradation of the existing servers, he should be able revert the new changes back to the older version, thus maintaining the DBMS working. He is also responsible for updating the service packs/ hot fixes/ patches to the DBMS servers.

• Design and implementation: - Designing the database and implementing is also DBA’s responsibility. He should be able to decide proper memory management, file organizations, error handling, log maintenance etc. for the database.

• Performance tuning: - Since database is huge and it will have lots of tables, data, constraints and indices, there will be variations in the performance from time to time. Also, because of some designing issues or data growth, the database will not work as expected. It is responsibility of the DBA to tune the database performance. He is responsible to make sure all the queries and programs works in fraction of seconds.

• Migrate database servers: - Sometimes, users using oracle would like to shift to SQL server or Netezza. It is the responsibility of DBA to make sure that migration happens without any failure, and there is no data loss.

• Backup and Recovery: - Proper backup and recovery programs needs to be developed by DBA and has to be maintained him. This is one of the main responsibilities of DBA. Data/objects should be backed up regularly so that if there is any crash, itshould be recovered without much effort and data loss.

• Security: - DBA is responsible for creating various database users and roles, and giving them different levels of access rights.

• Documentation: - DBA should be properly documenting all his activities so that if he quits or any new DBA comes in, he should be able to understand the database without any effort. He should basically maintain all his installation, backup, recovery, security methods. He should keep various reports about database performance.

Transaction Management• A transaction is an event which occurs on the database. Generally a transaction reads a value from the

database or writes a value to the database.

• Although a transaction can both read and write on the database, there are some fundamental differencesbetween these two classes of operations. A read operation does not change the image of the database inany way. But a write operation, whether performed with the intention of inserting, updating or deleting datafrom the database, changes the image of the database. That is, we may say that these transactions bring thedatabase from an image which existed before the transaction occurred (called the Before Image or BFIM) toan image which exists after the transaction occurred (called the After Image or AFIM).

• A transaction is a logical unit of work that contains one or more SQL statements. A transaction isan atomic unit. The effects of all the SQL statements in a transaction can be eitherall committed (applied to the database) or all rolled back (undone from the database).

• A transaction is a set of logically related operations. For example, you are transferring money from your bank account to your friend’s account, the set of operations would be like this:

• Simple Transaction Example

1. Read your account balance2. Deduct the amount from your balance3. Write the remaining balance to your account4. Read your friend’s account balance5. Add the amount to his account balance6. Write the new updated balance to his account

Example• For example, we have two accounts A and B, each containing Rs

1000/-. We now start a transaction to deposit Rs 100/- from account A to Account B.

Read A;A = A – 100;Write A;Read B;B = B + 100;Write B;

ACID Properties• A transaction is a very small unit of a program and it may contain several low level

tasks. A transaction in a database system mustmaintain Atomicity, Consistency, Isolation, and Durability − commonly known asACID properties − in order to ensure accuracy, completeness, and data integrity.

1. Atomicity − This property states that a transaction must be treated as an atomicunit, that is, either all of its operations are executed or none. There must be nostate in a database where a transaction is left partially completed. States should bedefined either before the execution of the transaction or after theexecution/abortion/failure of the transaction.

2. Consistency − The database must remain in a consistent state after anytransaction. No transaction should have any adverse effect on the data residing inthe database. If the database was in a consistent state before the execution of atransaction, it must remain consistent after the execution of the transaction aswell.

3. Durability − The database should be durable enough to hold all itslatest updates even if the system fails or restarts. If a transactionupdates a chunk of data in a database and commits, then the databasewill hold the modified data. If a transaction commits but the systemfails before the data could be written on to the disk, then that data willbe updated once the system springs back into action.

4. Isolation − In a database system where more than one transactionare being executed simultaneously and in parallel, the property ofisolation states that all the transactions will be carried out andexecuted as if it is the only transaction in the system. No transactionwill affect the existence of any other transaction.

Database System Structure• A Database system is partitioned into modules that deal with each of the responsibilities of the overall system. The

functional components of a database system can be broadly divided into the storage manager and queryprocessor components.

1. Storage Manager: A storage manager is a program module that provides the interface between the lowlevel data stored in the database and the application programs and queries submitted to the system. The storagemanager is responsible for the interaction with the file manager. The raw data are stored on the disk using the filesystem, which is usually provided by a conventional operating system. The storage manager translates the variousDML statements into low level file system commands. Thus, the storage manager is responsible for storing, retrievingand updating data in the database.

The storage manager components include:

• Authorization and integrity manager: Which tests for the satisfaction of integrity constraints and checks the authority of users to access data.

• Transaction manager: Which ensures that the database remains in a consistent correct) state despite system failures, and that concurrent transaction executions proceed without conflicting.

• File Manager: Which manages the allocation of space on disk storage and the data structures used to represent information stored on disk.

• Buffer manager: Which is responsible for fetching data from disk storage into main memory, and deciding what data to cache in main memory. The buffer manager is a critical part of the database system, since it enables the database to handle data sizes that are much larger than the size of main memory.

The storage manager implements several data structures as part of the physical system implementation:

• Data files, which store the database itself.

• Data dictionary, which stores metadata about the structure of the database, in particular the schema of the database.

• Indices, which provide fast access to data items that hold particular values.

2. The Query Processor: It translates statements in a query language into low-level instructions the database manager understands.

The query processor components include

• DDL interpreter, which interprets DDL statements and records the definitions in the data dictionary.

• DML compiler, which translates DML statements in a query language into an evaluation plan consisting of low level instructions that the query evaluation engine understands.

A query can usually be translated into any of a number of alternative evaluation plans that all give the same result. The DML compiler also performs

query optimization, that is it picks the lowest cost evaluation plan from among the alternatives.

Query evaluation engine, which executes low level instructions generated by the DML compiler.

Database system architecture

Database Application architectures• A Database Management system is not always directly available for

users and applications to access and store data in it. A DatabaseManagement system can be centralised(all the data stored at onelocation), decentralised(multiple copies of database at differentlocations) or hierarchical, depending upon its architecture.

•Centralized Architecture• A centralized database (sometimes abbreviated CDB) is a database that is

located, stored, and maintained in a single location. This location is most often acentral computer or database system, for example a desktop or server CPU, or amainframe computer. In most cases, a centralized database would be used by anorganization (e.g. a business company) or an institution (e.g. a university.) Usersaccess a centralized database through a computer network which is able to givethem access to the central CPU, which in turn maintains to the database itself.

Advantages• Data integrity is maximized and data redundancy is minimized, as the single storing place of all

the data also implies that a given set of data only has one primary record. This aids in the maintaining of data as accurate and as consistent as possible and enhances data reliability.

• Generally bigger data security, as the single data storage location implies only a one possible place from which the database can be attacked and sets of data can be stolen or tampered with.

• Better data preservation than other types of databases due to often-included fault-tolerant setup.

• Easier for using by the end-user due to the simplicity of having a single database design.

• Generally easier data portability and database administration.

• More cost effective than other types of database systems as labor, power supply and maintenance costs are all minimized.

• Data kept in the same location is easier to be changed, re-organized, mirrored, or analyzed.

• All the information can be accessed at the same time from the same location.

• Updates to any given set of data are immediately received by every end-user.

Disadvantages• Centralized databases are highly dependent on network connectivity. The slower

the internet connection is, the longer the database access time needed will be.

• Bottlenecks can occur as a result of high traffic.

• Limited access by more than one person to the same set of data as there is onlyone copy of it and it is maintained in a single location. This can lead to majordecreases in the general efficiency of the system.

• If there is no fault-tolerant setup and hardware failure occurs, all the data within the database will be lost.

• Since there is minimal to no data redundancy, if a set of data is unexpectedly lost it is very hard to retrieve it back, in most cases it would have to be done manually.

Client/Server Database Systems • Client/Server architecture of database system has two logical components namely client, and

server.

• Clients are generally personal computers or workstations whereas server is large workstations,mini range computer system or a mainframe computer system.

• The applications and tools of DBMS run on one or more client platforms, while the DBMSsoftware's reside on the server.

• The server computer is called backend and the client’s computer is called front end.

• These server and client computers are connected into a network.

• The applications and tools act as clients of the DBMS, making requests for its services. The DBMS,in turn, processes these requests and returns the results to the client(s).

• Client/Server architecture handles the Graphical User Interface (GUI) and does computations andother programming of interest to the end user.

• The server handles parts of the job that are common to many clients, for example, databaseaccess and updates.

• In 1-tier architecture, the DBMS is the only entity where the

user directly sits on the DBMS and uses it. Any changes done

here will directly be done on the DBMS itself. It does not

provide handy tools for end-users. Database designers and

programmers normally prefer to use single-tier architecture.

• Generally such a setup is used for local application

development, where programmers communicate directly with

the database for quick response.

1-tier DBMS

2-tier DBMS Architecture• 2-tier DBMS architecture includes an Application layer between the user and the DBMS, which is

responsible to communicate the user's request to the database management system and then send theresponse from the DBMS to the user.

• An application interface known as ODBC(Open Database Connectivity) provides an API that allow clientside program to call the DBMS. Most DBMS vendors provide ODBC drivers for their DBMS.

• Such an architecture provides the DBMS extra security as it is not exposed to the End User directly. Also,security can be improved by adding security and authentication checks in the Application layer too.

3-tier Architecture• 3-tier DBMS architecture is the most commonly used architecture for web applications.it adds

intermediate layer known as application server(or web server) between the client and databaseserver.

• A 3-tier architecture separates its tiers from each other based on the complexity of the users andhow they use the data present in the database. It is the most widely used architecture to design aDBMS.

•Database (Data) Tier − At this tier, the databaseresides along with its query processing languages. Wealso have the relations that define the data and theirconstraints at this level.•Application (Middle) Tier − At this tier reside theapplication server and the programs that access thedatabase. For a user, this application tier presents anabstracted view of the database. End-users areunaware of any existence of the database beyond theapplication. At the other end, the database tier is notaware of any other user beyond the application tier.Hence, the application layer sits in the middle and actsas a mediator between the end-user and the database.•User (Presentation) Tier − End-users operate onthis tier and they know nothing about any existence ofthe database beyond this layer. At this layer, multipleviews of the database can be provided by theapplication. All views are generated by applicationsthat reside in the application tier.

Database language

• SQL (Structured Query Language) is a special-purposeprogramming language designed for managing dataheld in a relational database managementsystem (RDBMS). SQL is a standard language foraccessing and manipulating databases.

Types of SQLData Definition Language (DDL)

Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:

• CREATE - to create objects in the database

• ALTER - alters the structure of the database

• DROP - delete objects from the database

• TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed

• COMMENT - add comments to the data dictionary

• RENAME - rename an object

Example:

Create table student (regno int,

Name varchar(25),

Class int,

Shift varchar(15);

Data Manipulation Language (DML)A data manipulation language (DML) is language that enables users to access data as organized by appropriate data model, i.e. DML is used to update the database by adding new data or modify or delete the existing data.

The retrieval of information stored in database

The insertion of new information into database

The deletion of information from database

The modification of information stored in the database

Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:

SELECT - retrieve data from the a database

INSERT - insert data into a table

UPDATE - updates existing data within a table

DELETE - deletes all records from a table, the space for the records remain

Example:

INSERT INTO employee VALUES

(101, ‘RAHUL’, ‘PATAN’, 447881);

DELETE FROM employee

WHERE ADDRESS = ‘PATAN’; etc.

Data Control Language (DCL)A data Control language (DCL) is language that Use to

control/ configure database configurations and access control.

Examples of DCL commands include:

• GRANT to allow specified users to perform specified tasks.

• REVOKE to cancel previously granted or denied permissions.

• DENY Deny user access

Data dictionaryA data dictionary, or metadata (data about

data) repository, as defined in the IBM Dictionary ofComputing, is a "centralized repository of informationabout data such as meaning, relationships to other data,origin, usage, and format."The term can have one ofseveral closely related meanings pertainingto databases and database management systems (DBMS):

A document describing a database or collection of databases

An integral component of a DBMS that is required to determine its structure

A piece of middleware that extends the native data dictionary of a DBMS

Data Warehouse A data warehouse is a database, which is kept separate

from the organization's operational database.

There is no frequent updating done in a data warehouse.

It possesses consolidated historical data, which helps the organization to analyze its business.

A data warehouse helps executives to organize, understand, and use their data to take strategic decisions.

Data warehouse systems help in the integration of diversity of application systems.

A data warehouse system helps in consolidated historical data analysis.

Data Warehouse Features• Subject Oriented - A data warehouse is subject oriented because it

provides information around a subject rather than the organization'songoing operations. These subjects can be product, customers,suppliers, sales, revenue, etc. A data warehouse does not focus on theongoing operations, rather it focuses on modeling and analysis ofdata for decision making.

• Integrated - A data warehouse is constructed by integrating datafrom heterogeneous sources such as relational databases, flat files,etc. This integration enhances the effective analysis of data.

• Time Variant - The data collected in a data warehouse is identifiedwith a particular time period. The data in a data warehouse providesinformation from the historical point of view.

• Non-volatile - Non-volatile means the previous data is not erasedwhen new data is added to it. A data warehouse is kept separate fromthe operational database and therefore frequent changes inoperational database is not reflected in the data warehouse.

Note: A data warehouse does not require transaction processing,recovery, and concurrency controls, because it is physically storedand separate from the operational database.

Data mining• Data mining, the extraction of hidden predictive information from

large databases, is a powerful new technology with great potential tohelp companies focus on the most important information in their datawarehouses. Data mining tools predict future trends and behaviors,allowing businesses to make proactive, knowledge-driven decisions.The automated, prospective analyses offered by data mining movebeyond the analyses of past events provided by retrospective toolstypical of decision support systems. Data mining tools can answerbusiness questions that traditionally were too time consuming toresolve. They scour databases for hidden patterns, finding predictiveinformation that experts may miss because it lies outside theirexpectations.

• Most companies already collect and refine massive quantities of data.Data mining techniques can be implemented rapidly on existingsoftware and hardware platforms to enhance the value of existinginformation resources, and can be integrated with new products andsystems as they are brought on-line

• Data mining techniques are used in a many research areas, includingmathematics, cybernetics, genetics and marketing. Web mining, atype of data mining used in customer relationship management(CRM), takes advantage of the huge amount of information gatheredby a Web site to look for patterns in user behavior.

Database Security

Introduction• Database of an enterprises consist of huge amount of data collected from

different business activities in a course of time. The data hence collectedcarries a significant importance to the enterprise. Those data should becarefully stored so that the database or certain part of the database doesnot get lost, altered, or accessed by unauthorized person. Therefore toprotect the database from such events database security plays a vital role.

• Database security concerns the use of a broad range of informationsecurity controls to protect databases against compromises of theirconfidentiality, integrity and availability. Database security helps to controlthe access of the database objects, formulate the rules regardingmodification of data, auditing of the data, ensure the privacy, etc. Fordesigning a good database security, we need to consider three mainobjectives:

SecrecyIntegrityAvailability

Secrecy: The data should only be accessed by the authorized user. The access todata by unauthorized user should always be denied. Sometime a user may bedenied of the whole data and activity in a given database or only a part of data andcertain activities associated with the database.

E.g.: A Bank customer should not be allowed to check the details of other customers but can have authority to check their own detail.

Integrity: Modification of data may be allowed to only authorize user. Theaccidental modification or intentional modification should always be protected. Theuser should be allowed to modify only those part of data that they need.

E.g.: A customers should not be allowed to change the balance of his/her accountthrough database application.

Availability: Security should not restrict the authorized user to perform theiractions on the part of the database available for them i.e. authorized user shouldnot be denied access.

E.g.: All the authorized employee should be able to change the data andinformation about the customer in their bank.

Role of DBA in Database Security • Database Administrator is a person who has the central authority of the database

system. They are also responsible to maintain the overall security of thedatabase. They have to think of every possible way through which the database’ssecurity might be compromised and the way to handle such security issues. Thetwo main responsibility of a DBA regarding the security of database are:

1. Managing User Accounts: • Database administrator has a super user privilege (i.e. root user) so he/she has

the authority to create another database user as well as to drop the existing user.DBA can also grant or revoke the privileges (such as INSERT, UPDATE, DELETE,SELECT, CREATE, DROP, ALTER privileges) to another users. Therefore it is DBA’sresponsibility to use those authority and manage the security concern of thedatabase by providing only necessary privileges to other database users.

2. Performing Database Audits:

• Auditing in database means monitoring and recording the actions performed bythe database users and sometimes DBA himself/herself as well. It can be basedon certain action such as the type of SQL statement executed or on thecombination of factors such as username, application, and time and so on.Auditing is performed so that the operation performed in database and databaseobjects could be tracked in future. It helps us to investigate on suspiciousdatabase access and database operation as well.

Why do we need Database Security?

Database consists of all the information that is generated by an enterprise. Thedata in database are very important asset for any organization, it might beconfidential and sensitive. In order to protect such data from intruders and frombeing modified or deleted accidentally database security is very essential. We havea belief that most of the security breaches are caused by the hackers but in fact80% of the data loss is caused by insider, this is also a reason why we needdatabase security and provided only necessary access to each user within anorganization.

Some of general reasons for need of database security are:

i. To maintain data consistency when large number of user are accessing the same data in shared data environment.

ii. To control and restrict the intentional or accidental modification of the sensitive data by the insider.

iii. To protect data from hackers who tries to intrude the database through internet. iv. Hackers have developed specialized software to illegally enter the system and extract the financial data. Therefore database security is needed to secure the monetary transactions performed through credit card using internet.

Database Security System • DBA is a person responsible to analyze every possible security threat that can arise in a database

and then think of a protective measure to secure the database from those threats. DBA createsvarious type of authorization rules to implement security in the database. Creating authorizationrules involves authenticating the valid user, allowing the user to perform certain operation,allowing user to access on some part of database or whole database based on the role of thedatabase user, etc. Authorization rule can be enforced to a single user or even a group of users.

Once those authorization rules are created theyare stored in database security system withinthe DBMS. All the users who want to access thedatabase has to go through the databasesecurity system each time they perform certainoperation or access the database. Databasesecurity system check the authentication andprivileges of the user based on theauthorization rule created by the DBA and onlyallows those user who satisfy the conditionincluded in the authorization rule.

Classification of Database Security Database security concerns the use of a broad range of information security controls to protect databases (potentially including the data, thedatabase applications or stored functions, the database systems, the database servers and the associated network links) against compromises oftheir confidentiality, integrity and availability. It involves various types or categories of controls, such as technical, procedural/administrativeand physical. Database security is a specialist topic within the broader realms of computer security, information security and riskmanagement.

Physical Security

The physical environment has enormous influence on the security of a computer system. Proper consideration of factors like location andbuilding design and construction is vital, especially at the planning stage. It is much easier to eliminate potential security problems orminimize their effects at this stage. An existing building may have insolvable security problems.

Logical Security

A logical breach affects the data and software without physically affecting the hardware. The damage is often invisible until someone tries to

process or display the data. Fraud and unauthorized access are examples of malicious logical breach, and though it may have been introduced

accidentally onto a personal computer(PC), its original conception was malicious.

Logical security comprises the following

•Viruses

•Untested

•User error

•Operator error

•Computer misuse

•Computer fraud

•Student unauthorized access

•External unauthorized access

Database Security Level

Authorization in SQL (Privileges in SQL)

• Authorization is the process of granting of privileges to the database useron certain portion of the database that are necessary for them to performtheir job. Authorization also decides what operation can be performed bythe user on the authorized portion of the database. DBA is responsible togrant and revoke the authorization to/from the database users. However,the database user can also grant access to the database objects such asrelation owned by themself to other users.

• The information about the authorization is maintained in a table calledaccess matrix within the database. Each time the database user tries toaccess the database object and perform some operation the DBMS consultsthe access matrix table to check if the access is legal or not.

1. Read Authorization: It allows user to only read data from the database.

2. Insert Authorization: It allows only to insert the new record in database.

3. Update Authorization: It allows only to modify the existing data in database.

4. Delete Authorization: It allows only to delete the records from the database.

5. Index Authorization: It allows user to create or delete the index in the database. Indexes are useful for faster database access.

6. Resource Authorization: It allows user to creation of new relations, databases ,index etc. But cannot allow inserting new data ,modification or deletion of data.

7. Alteration Authorization: It allows the user to add, remove and modify the schema of the existing relation in database.

8. Drop Authorization: It allows the user to delete the entire relation and view from the database.

9. Reference Authorization: It allows the user to create a foreign key and establish relationship between two or more relations.

Different type of authorization that can be granted to the database users are:

• A DBA can provide all or some of the authorization to the database user using the SQLcommand GRANT and he/she can also cancel the authorization by using the SQLcommand REVOKE. GRANT and REVOKE are the part of SQL DCL (Data Control Language)statement.

• Authorization in SQL can be granted either to the user or to the role. DBA has aresponsibility to create a database user and delete the database user as well. The generalSQL syntax to create user in database is:

Syntax:

CREATE USER ‘<username>’@’<host address>’

IDENTIFIED BY ‘<password>’;

Example:

CREATE USER ‘Binod’@ ‘localhost’

IDENTIFIED BY ‘P@ssword’;

A user can be delete using SQL command as:

Syntax:

DROP USER ‘<username>’;

Example:

DROP USER ‘Binod’;

Granting PrivilegesPrivilege of various database object can be provided to the database user or role. While granting privilege, DBAhas to mention what type of SQL operations a user can perform on which relations or views. This operation isdone by using GRANT statement:

General Syntax for granting privilege:

GRANT <privilege list> | ALL PRIVILEGES

ON <relation names | view names>

TO <username list | role list> | PUBLIC [WITH GRANT OPTION];

Example 1:

GRANT SELECT,INSERT,UPDATE

ON Student, Course

TO Pratik;

Above SQL statement will grant the SELECT, INSERT and UPDATE privilege on Student and Course relation to the database user with username Pratik.

Example 2:

GRANT ALL PRIVILEGES

ON Student

TO Pratik;

Above SQL statement will grant all the privileges on Student relation to the database user with username Pratik.

Example 3:

GRANT ALL PRIVILEGES

ON Student, Teacher

TO PUBLIC;

Above SQL statement will grant all the privileges on Student and Teacher relation to all the database users.

Example 4:

GRANT ALL PRIVILEGES

ON Student, Teacher, Course

TO Pratik

WITH GRANT OPTION;

Above SQL statement will grant all the privileges on Student, Teacher andCourse relation to user Pratik. WITH GRANT OPTION in above statementmeans that now user Pratik can grant the privileges that he has to any otherusers. If WITH GRANT OPTION is not specified then Pratik cannot grant theprivileges to another user

Example 5:

GRANT REFERENCES (dept_no)

ON Department

TO Pratik;

Above SQL statement will allow user Pratik to create a relation which can reference to the dept_no attribute of Department relation.

Example 6:

GRANT UPDATE

ON Student(Sname, Class), Teacher

TO Pratik;

Above SQL statement will allow user to UPDATE all the field of relation Teacher but only the Sname and Class field of the relation Student.

Example 7:

GRANT DELETE

ON Employee(Salary), Orders TO Pratik;

Above SQL statement grants user Pratik delete authorization on salary column of Employees relations and ,Orders relation.

Authorization Graph

A privilege can be granted by the DBA to the database user. A database user who has privilege withGRANT OPTION can also pass the authorization to another user without the involvement of DBA. Allthis action of passage of authorization from one user to another can be represented in graphicalform which is called authorization graph. In this graph nodes represents users, root represents thedatabase administrator and edge from Ui to Uj (Ui Uj) represents that user Ui has grantedauthorization (Privilege ) to Uj.

Example:

Database Roles A database roles are created by the DBA to group together privileges orother roles. Role is efficient way of granting multiple number of privileges toa group of user. Thus we can say that roles are a collection of privileges oraccess rights. When there are many users in a database it becomes difficultto grant or revoke privileges to users. Therefore, if we define roles, we cangrant or revoke role to users, thereby automatically granting or revokingprivileges.Some of the benefits of using role are: • Reducing the number of grants and thereby making it easier to manage

security. • Dynamically changing the privileges for many users with a single grant or

remove• Selectively enabling or disabling depending on the application.

General Syntax to create role: CREATE ROLE ‘<role name>’@ ‘<hostname>’; Example: CREATE ROLE ‘Accountant’@‘localhost’;

General Syntax to GRANT Privileges to Role:

GRANT <privilege list>|ALL PRIVILEGES

ON <relation list>

TO <role list>;

Example

GRANT SELECT, INSERT

ON Salary, Employee

TO Accountant;

General Syntax to GRANT Role to users:

GRANT <role list>

TO <user list>;

Example:

GRANT Accountant

TO Sanjeev, Sameer, Priya;

Syntax to drop the role:

DROP ROLE <role name>;

Revoking of the privileges

The DBA or any USER who had issued the GRANT privileges to other user can revoke the privileges by using the REVOKE syntax.

General Syntax to Revoke the Privilege is:

REVOKE <list of privileges> | ALL PRIVILEGES

ON < table-name or view-name>

FROM <list of users> | PUBLIC [CASCADE | RESTRICT]

Example:

REVOKE UPDATE ON Teacher FROM Pratik;

This query will revoke the UPDATE privilege previously given to user Pratik on relation Teacher.

If a User A has granted the privileges to User B and now DBA want to Revoke theprivilege from the User A then in this case the DBA has to use the CASCADE clauseso that it will Revoke the privilege from the User B as well.

If DBA uses RESTRICT or do not mention anything (which by default meansRESTICT) then the REVOKE query will produce the error and none of the privilegesare Revoked from either User A or User B.

CASCADE clause in the REVOKE statement can only be used if the original privilegeincluded WITH GRANT OPTION. Whereas RESTRICT clause can only be used if theoriginal privilege does not include WITH GRANT OPTION or if the user who wasprovide the privilege of WITH GRANT OPTION has not passed the privilege toanother user otherwise the use of RESTIRICT clause will produce error.

Limitations of SQL Authorization SQL Authorization can be used in many ways to secure the database up to large extent. However there are some limitations associated with SQL Authorization.

• DBA can grant the SQL Authorization to the database user on a particular table and onparticular column of a table however SQL does not support authorization at a tuple level.

• E.g. We cannot grant access of database to a database user such that the user only getthe access of certain tuples of a relation. By using SQL authorization we can give usereither the access of whole relation or the access to certain column of the relation but wecannot give the access on tuple level.

• Another limitation is that, it becomes impossible to create user for each and individualperson when there is huge number of people accessing the database from source suchas website. For the users who access the database through the web, authorizationcannot be implemented by the DBMS and the task of authorization falls to theapplication program resulting in:

Tuple level authorization can be maintained through application program.

Authorization must be done in application hence increasing the complexity.

The authorization through application code may be dispersed all over the application program, hence it becomes difficult to find the authorization loophole.

Encryption and Authentication

Database authorization technique provides a certain level of databasesecurity but still it might be insufficient to protect highly sensitive data so inorder to enhance the level of security we can use encryption andauthentication techniques.

Encryption

Encryption is a task of changing the plain text to the cipher (code) text by using a secret key. Simple example of encryption can be as follow:

Plain text: Hello Ram

Key=+2 (i.e. every letter in the plain text is replaced by the letter 2 steps ahead of it)

Cipher text: Jgnnq Tco

• Different types of encryption standards are available that we can use to encryptthe data. Some of the popular encryption scheme are: DES (Data EncryptionStandard), AES (Advance Encryption Standard) and Public Key Encryption.

• DES and AES are based on symmetric key approach i.e. the encryption anddecryption is done using same key. Whereas Public key Encryption is based onasymmetric key approach i.e. the encryption is done through one key (known aspublic key) and decryption is done through another key (known as private key).

• While using symmetric key approach to encrypt the data we need to be carefulwhile providing the key to the valid receiver otherwise the key might becompromised and the unauthorized person may access the data. Whereas whileusing asymmetric key approach we need to share only the public key which canbe used by any person to send the encrypted message but in order to decrypt themessage the receiver need to use their own valid private key.

Authentication Authentication is the process by which the system verified the identity of the user.This is the first level of security. An authenticated user goes through the secondlevel of security, i.e. authorization. Various methods can be used to authenticatethe user such as secret password, some physical characteristics of the user, a smartcard or a key given to the user:

Password Authentication:

It is the simplest and most commonly used authentication scheme. Here the user enters the user name and the secretpassword in the login interface. Then the DBMS verified the combination of the username and the password enteredby the users and allows the access only if the user is identified.

Smart Card Authentication:

It is a better authentication technique compared to the traditional password authentication. Smart card is a pocketsized card that consist microchip which contain the information about the card holder. Smart card along with the PINcode is used to verify the authenticity of the user. A Smart card can be blocked by the user if he/she loses thepossession of the card or he/she is suspicious about the transaction being conducted.

Biometric Authentication:

It is the process of verifying the identity of the user through biological characters such as finger print, retina structure,facial structure, DNA, etc. The system allows the access to those user whose biometric information is recognized.

Digital Signature:

It is used to verify the authentication of the data. It uses the public key cryptography in reverse way. i.e. the datacreator encrypts the data using their private key and then the receiver retrieves the data by decrypting it using thepublic key of the data creator. Digital Signature is useful to ensure non-repudiation i.e. Once the data has be createdusing digital signature the data creator cannot deny that he/she did not created the data.

Digital Certificate:

Digital Certificate is way through which we can assure the authenticity of the web site or database in the internet.Digital certificate is based on public key of the web site or database. The public key of the website or database issigned by the certifying authority (often be government body). The user confirms if they are visiting or using theauthenticated website by verifying the digital certificate of that website.

END