DBMS

MARWARI COLLEGE, RANCHI

(AN AUTONOMOUS UNIT OF RANCHI UNIVERSITY FROM 2009)

- Prakash Kumar, Dept. of CA

-Raju Manjhi, Dept. of CA

__________________________________________________________________________________ 

Database is a collection of related data and data is a collection of facts and figures that can be processed to produce information.

Mostly data represents recordable facts. Data aids in producing information, which is based on facts. For example, if we have data about marks obtained by all students, we can then conclude about toppers and average marks.

A database management system is a software system that allows access to data contained in a database.

             The objective of the DBMS is to provide a convenient and effective method of defining, storing and retrieving the information contained in a database.

Terms to know:

Entities: - Entities are the basic unit used in modeling classes of concrete or abstract object.

Entity Set: - It is a group of similar object of concern to an organization for which it maintains data.

E.g.:- Transaction, Employees, etc.

Attributes: - The properties that characterized an entity set are called its attribute. It is also referred by data item, data element, item, etc.

Domain: - Each attribute of an entity set has a particular value. The set of possible values that a given attribute can have is called domain.

Keys: - A key is a single attribute or combination of two or more attribute of an entity set that is used to identify one or more instances of the set.

Primary key: - A unique entity identifier is referred to as a primary key.

Super key: - If we add additional attribute to a primary key it is called super key.

Candidate Key: - When combination of attributes uniquely identifies an instance of an entity, it is called candidate key.

                 The remaining candidate key is considered as “Alternate key”.

Secondary key: - A secondary key is an attribute or combination of attribute that may not be a candidate key but they classify the entity set on a particular characteristic.

          Foreign key: - A non-key attribute of a relation that works as a primary key in another              relation is called foreign key.

Metadata: - Data about data is called metadata.

Relationship: - It is used in data modeling to represent an association between entity set.

3-Level Architecture of DBMS:-

Scheme: - A scheme is an outline or a plane that describes the records and relationships existing in view.

                  The word scheme is interchangeably used in the database literature with the word “Schema”.

The architecture is divided into:-

1.   External or user view:- It is the highest level of database abstraction where only those portion of the database of concern to the user are included.

The external view is described by external schema. It consists of the definition of logical records and their relationship in the external view.

2.   Conceptual view: - At this level of database abstraction all the database entities and relationship between them are included.   

          “One conceptual view represents the entire database”.

The conceptual view is defined by the conceptual schema. It also describes the records and relationship included in the conceptual view.

There is only one conceptual schema per database.

The description of data at this level is in a format independent of its physical  representation.

3.   Internal view: - This is the lowest level of database abstraction, closest to physical storage method used.

It describes the data structure and access methods to be used by the database. Internal view is expressed by the internal schema.

Data Independence:-

Data independence is of two types:-

1.   Logical Data Independence: - Logical data independence indicates that the conceptual schema can be change without affecting the existing external schema. It indicates that the application programs need not be change if fields are added to an existing record or if fields not used by application program are debuted.

2.   Physical Data Independence:- It allows changes in the physical storage devices, without requiring changes in conceptual view or any of the external views. Thus the files may migrate from one type of physical media to another.

Database Administrator (DBA):- Centralized control of the database is exerted by a person or a group of persons under the supervision of high level administrator. This person or group is referred to as DBA.

Jobs of DBA:-

1.   Mapping between internal and conceptual level.

2.   Granting permission to user.

3.   Defines procedure to recover database.

DBMS Facilities:-

The facilities provided by DBMS are:-

1. Data Definition Language

2. Data Manipulation Language

3. Data Control Language

1. Data Definition Language (DDL):- It can be used to define conceptual schema and also give some details about how to implement this scheme in the physical devices used to store data. This definition includes all the entity sets and their associated attributes as well as the relationships among the entity sets.

E.g.:- Create, Alter, Drop table.

2. Data Manipulation Language (DML):- The language used to manipulate data in the database is called DML. It involves:- 

1.   Retrieval of data.

2.   Insertion of new data.

3.   Deletion or modification of existing data.

It provides command to select and retrieve data from database.

E.g.:- Insert, Update, Delete table.

3. Data Control Language (DCL):- DCL consists of commands that control the user access to the database objects. It is mainly related to security issues i.e. determining who has access to database objects and what operations that can perform.

E.g.:- Grant, Revoke.

Structure of DBMS:-

1.    DDL Compiler: - It converts the data definition statement into a set of tables.

2.  Data Manager: - The data manager is a central software component of DBMS. It converts operations in the users queries commit directly via the query processor.

3.    File Manager: - It is responsible for the structure of file and managing the file space. It locates the block containing the required record, requesting the block from the disk manager and transmits the required record to the data managing.

4.  Disk Manager: - The disk manager is a part of the operating system of the host computer. It transfers the block or page requested by the file manager.

5.   Query Processor: - The query processor is used to interpret the users query and convert it into an efficient series of operations in a form capable of being set to the data manager for execution.

              It uses the data dictionary to find the structure of the relevant portion of the database.

6.   Data Dictionary: - The information pertaining to the structure and uses of data contained in the database; the “Metadata” is maintained in a data dictionary. It documents the data.

                It stores information concerning the external, conceptual and internal level of database.

Advantages OF DBMS:-

1. Reduction of redundancies: - Centralized control of data while the DBA avoids unnecessarily reduces the total amount of data storage required.

2.   Shared Data: - Database allows the sharing of data under its control by any number of application program or users.

3.   Integrity: - It means that the data contained in the database is both accurate and consistent.

4.  Security: - DBMS ensures that proper access procedures are followed, including proper authentication scheme for access to the DBMS and additional checks before permitting access to sensitive.

5.  Conflict Resolution: - The DBA resolves the conflicting requirement of various users and applications.

6.  Aggregation: - It is process of compiling information on an object there by abstracting a higher level object.

Abstraction: - It is the simplification mechanism used to high super flows details of a set of objects; it allows one to concentrate on the properties that are of interest to the application.

1.   Generalization: - It is the abstracting process of viewing set of objects as a single general class by concentrating on the general characteristic of the constituent sets while suppressing or ignoring their differences. It is the union of a number of lower level entity types for the purpose of producing a higher level entity type.

2.  Specialization:-It is the abstracting process of introducing new characteristics to an existing class of objects to create one or more new classes of objects.

This involves taking a higher level entity and using additional characteristics generating lower level entities.

Note: - Generalization is also known as IS_A relationship.

Data Models:

Relational Data Model: - In this model the only construct required to represent the associations among the attributes of entity as well as the relationship among different entities. The relation is the only data structure used in the relational data model to represent both entities and the relationship between them.

Note: - Rows of a relation are referred to as its attributes. Each attribute in a column are drawn from a set of value known as domains.

The main highlights of this model are:

  Data is stored in tables called relations.

  Relations can be normalized.

  In normalized relations, values saved are atomic values.

  Each row in a relation contains a unique value.

  Each column in a relation contains values from a same domain.

Network Data Model (DBTG) {Database Task Group}:- The DBTG model uses two different data structures to represent the database entities and relationship between the entities, namely record type and set type. It is made-up of a number of data items that represent the attributes of entity.

A set type is used to represent a directed relationship between two record types:-

1.   Owner Record Type

2.   Member Record Type

               The set type is named and specified that there is a 1: M relationship.

Hierarchical Model: - This model uses tree structures to represent relationship among records.

The nodes of the tree represent record types. Each tree effectively represents a root record type and all its dependent records types.

                                         A hierarchical model can represent a 1: M relationship between two entities where the two are respectively parent and child. However, to represent a many: many (M: M) relationship requires duplication of one of the record types corresponding to one of the entities involves in this relationship.

Entity-Relationship 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.

ER Model is based on:

 Entities and their attributes.

 Relationships among entities.

These concepts are explained below.

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 is considered 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 entities in various ways. Mapping cardinalities define the number of association between two entities.

Mapping cardinalities:

o one to one

o one to many

o many to one

o many to many