Notes RDBMS
Notes RDBMS
Notes RDBMS
It consists of
1 ata in such a way that it provides a clear distinction between the physical data format
d
both ‘end-user data’, which includes raw facts of interest and ‘metadata’, which includes and logical data format.
information about data using which end-user data are managed and integrated. It is 4. Security Management This function provides a security system, which enforces data
usually so large that it has to be stored on the secondary storage devices like disks or privacy and user security rules. These rules specify the users that can access the
tapes. Such data can be maintained as a collection of operating system files and stored database, the data operations that can be performed by the user and the data items that
in a database structure called Database Management System (DBMS). can be accessed by the users. Thus, security rules are very much important in multiuser
A Database Management System (DBMS) is a software that defines a database, stores database systems where several users access the database concurrently. The DBMS
the data, supports a query language, produces reports and creates data entry screens. provides an authenticated username and password or a biometric authentication like a
Typically, it is a collection of programs that controls and manages the access made to fingerprint scan to every user so that he/she can access the database components like
the data in the database. It acts as an intermediary between the end-users and queries and reports.
application programs. The end-users send the application requests to the DBMS, which 5. Multi-user Access Control This function refers to the management of multiple users
in turn converts those requests into complex operations and thus provides data to the that access the database simultaneously without the loss of data consistency and data
users. The internal complexity of the database is hidden from both end-users and integrity. This can be achieved by DBMS which uses complex and sophisticated
application programs. This is done basically by the DBMS. The DBMS utility programs algorithms.
may create application programs, which are written in languages like C++, COBOL 6. Backup and Recovery Management This function refers to the management of backup
(Common Business-Oriented Language) or Visual Basic. and recovery of data provided by the DBMS so as to provide an assurance about the data
Functions of DBMS safety and integrity. Some special routines, backup and restore procedures are
DBMS is a system that is responsible for performing several functions based upon which performed by the DBA. When a failure occurs, then the recovery of the database can be
the integrity and consistency of data can be ensured. The basic functions performed by done by the recovery management system.
DBMS are, 1. Data Dictionary Management 7. Data Integrity Management This function refers to the process of promoting and
This function refers to managing the data dictionary in which definitions of several data providing the integrity rules so as to increase the data consistency and to reduce the
elements and their corresponding relationships are stored by DBMS. The DBMS helps in data redundancy.The assurance of data integrity is very crucial in the
executing the programs which are used for accessing the data within the database transaction-oriented database systems and this integrity can be enforced in the
environment. The user doesn’t need to code the complex relationships in every program databases using the data relationships stored in the data dictionary.
because it identifies the data elements and their relationships by using the data 8. Database Access Languages and Application Programming Interfaces The data can be
dictionary. If changes are made to the data in the database structure, then these changes accessed by a query language, which refers to a non procedural language. It does not
are automatically stored in the data dictionary. This in turn removes the necessity of specify the way to execute the queries, but only specifies information about them. The
updating each program that accesses the changed structure. Consequently, it can be data can also be accessed by the programmers using procedural languages like C, C++,
said that data abstraction is provided by the DBMS, which in turn eliminates the PASCAL, COBOL, etc. The DBA uses some administrative utility softwares that are
structural and data dependency issues from the system. provided by the DBMS. The DBMS also provides a database designer for creating,
2. Data Storage Management This function refers to management and creation of maintaining, monitoring and implementing the database. The query language that is used
complex data structures, which are stored within the database structure. Thus, it avoids by most of the DBMS vendors is Structured Query Language (SQL). This language refers
the need to define and organize the physical data characteristics. Advanced Data Storage to a data access standard language and also a de facto query language.
Management provides the storage structure for storing data, reporting definitions, 9. Database Communication Interfaces This function refers to the communication of end
screening definitions, defining structures to handle video and picture formats, defining users with several network environments. It is provided by the current DBMSs, that
data validation rules, etc. It helps in the database performance tuning, which is a set of accept the requests sent by the end users through multiple network environments. For
activities using which the storage and access speed of the database increases instance, the user can request for accessing the database through the use of web
significantly. From the user’s perspective, the database is viewed as a single data browsers like Micorsofit Internet Explorer or Mozilla Firefox. The database
storage component. But, actually the database is stored in several physical data files by communication thus, can be achieved in the following three ways, (i) By filling screen
the DBMS. These files are stored on several storage devices using which the DBMS can forms through the suggested web browsers, the answers to the queries can be achieved.
access the database requests without waiting for a disk request to complete. This means (ii) By automatically promoting predefined reports on the web site. (iii) By connecting to
it can respond to the database requests in a concurrent manner. the third-part systems so that information can be distributed with the use of e-mail or
3. Data Transformation and Presentation This function is used to transform the data in productivity applications.
such a way that it is conformed to the required data structures. It also represents the
2 Explain the three level logical architecture of DBMS. r epresenting internal model. This representation is done using different database
nswer : The three level logical architecture ofDBMS can be described in terms oflevels
A constraints that are supported by the selected DBMS. Ifrelational Database is selected,
of data abstraction. Levels of Data Abstraction Data abstraction is a mechanism that then internal schema must match the characteristics ofconceptual model with that
enables the designers in providing only an abstract view of data environment to the end ofrelational model constructs. This means that the entities defined in conceptual model
user i.e, the implementation complexities are hidden from the users. ANSI/SPARC are mapped to tables in the relational model. The internal schema in relational database
architecture defines three levels of data abstraction using which it is very easy for a must be expressed using a Standard Query Language (i.e., SQL). The primary advantage
designer to integrate multiple views of data present at different levels of organization. of representing a relational database using internalmodel isthat, less specification details
The three levels of data abstraction are as follows, are required when compared to hierarchical and network model. Thisis
(i) External level (Model) (ii) Conceptual level (Model) (iii) Internal level (Model). because,relational model is capable ofhandling data access paths transparently (i.e.,
Later, ANS/SPARC expanded their architecture to include another level of abstraction database designers need not maintain details regarding data access paths). However,
calledphysical level(model) which specifies the physical level implementation details of similarto hierarchical and networkmodel, relational database software also requires a
the internal model. The lowest level of abstraction is the physical level which specifies data storage location specification. Internal Model is software dependent, which implies
the way ofstoring data on storage devices. Basically, a physical model that operates at a that changes made in DBMS software must also be reflected in the internal model. On the
physical level requires the information about the physical storage devices and also the other hand, the model is hardware independent because changes made in storage
access methods. The storage structures which are used for storing the data are generally devices doesn’t have any impact on the internal model. Moreover, changes in internal
dependent on the software and also on the type of storage devices. In order to accurately model doesn’t have any affect on conceptual model thereby ensuring logical
define a physical model, it is necessary for a database designer to have detailed independence.
knowledge regarding the hardware and Software that are used for implementing the
database design. What is entity relationship model? Explain with example.
3
(i) External Level (Model) External level is the highest and the outermost level of data Answer : Entity Relationship Model (ER-Model) The entity relationship model is one of
environment which represents the end user’s view, who usually use the application the most widely used data model while designing the database. This model was
program for manipulating data and generating information. It is possible that different developed in 1976 by Peter Chan and is also referred to as high-level conceptual data
external views can use the same conceptual view in order to satisfy the requirements of model. The main purpose of developing E-R model is to support the user’s view about
various users. External level of abstraction is operated using external model. Generally, the data and to hide the implementation details associated with database design.
end users perform their operations within an environment that consists of an application ER-models are represented in ER-diagram, which are basically the graphical
which concentrates on a particular business unit (sales, finance, marketing). Every representations of entire logical structure of database. The following are the basic
business unit works in accordance to certain constraints and requirements. These units components ofER model,
utilize only a part of data in the organization, thereby enabling the end user to view only (i) . Entities (ii) Attributes (iii) Relationships.
their respective part of data, which is external to other business units in the organization. (i) Entities An Entity is a real-world object that is distinguishable from other objects. In
External view of data (being modeled) can be represented using ER-diagrams. This ER-diagram, an entity is represented by a rectangle (entity box). The name ofthe entity is
representation of external view is referred to as “External schema”, which includes a noun and its written in the centre of rectangle. Whenever, ERdiagram is applied to
appropriate building blocks ofdata models imposed by every business unit relational model, an entity is mapped to relational table wherein each row represents an
(ii) Conceptual Level (Model) Conceptual view represents the global (enterprise view) of entity instance.
data environment wherein all external views are integrated in order to form a single Example STUDENT— CUSTOMER —DEPARTMENT
global view of data. This view is graphically represented using ER-diagram and the (ii) Attributes A set ofattributes associated with an entity describes the characteristic
resulting representation is known as conceptual schema. This level of abstraction is feature of respective entity. Attributes can also be defined as the qualifier that provides
used by DBA, who makes a decision regarding what information is to be kept in the additional information about the entity. Generally, attribute is an atomic unit of
database. A database consists ofonly one conceptual schema, which is connected to information associated with the named entity. This information helps in uniquely defining
both external schema and internal schema an entity. In ER-diagram, attributes are represented by ellipse and the name ofthe
(iii) Internal Level (Model) The Internal level is the level of abstraction present between attributes are written inside the ellipse. Each ofthe attribute is linked with the respective
the conceptual level and physical level. Internal model is used for mapping the entity.
conceptual model with the selected DBMS. This model represents the DBMS view of data Example The attributes associated with ‘student’ entity include Roll-no, name, age,
environment. It enables the designer to match the characteristics of conceptual model address, phone-no. These attributes are represented as:
and constraints with that ofselected implementation model. Internal schema is used for
( iii) Relationships Relationships describe associations between different entities. In eterminant is a key i.e., for every functional dependency A -> B , A is a key. Basically,
d
ER-diagram, relationships are represented by diamond-shaped symbol and the name of determinant is an attribute whose value determines the value of other attributes.
relationship is written inside the diamond. The two sides of diamond are connected to Rule-4: Boyce Codd Normal Form A relation schema R is said to be in BCNF ifthe
theirrespective related entities. The different types ofrelationships that are defined in ER following statements are true for each FD P — > Q in a set F ofFDs that hold over R .
model includes, one-to-many, many-to-many and one-to-one. Here, P is the subset of attributes ofR and Q is a single attribute ofR . (i) The given FD is a
These relationships are represented by using any one ofthe following notations, (a) Chen trivial, i.e., QEP (ii) P is a superkey.
notation (b) Crow’s foot notation.
(a) Chen Notation In this type of notation, the relationship is written next to the respective Explain the following commands in SQL with examples, (a) Create (b) Insert (c) Alter (d)
5
entity box. Update (e) Delete.
(b) Crow’s Foot Notation In Crow’s Foot Notation, a three-pronged symbol is used for Answer : (a) Create The tables (or relations) in SQL are created using the CREATE TABLE
representing ‘many’ sides of relation and short-line segments for representing ‘one’ side command
of relation. Syntax CREATE TABLE TABLE NAME (column 1 name (column2_name datatype datatype
Advantages of ER-Model 1. The model results in exceptional conceptual simplicity [NULL | NOT NULL [WITH DEFAULT] | UNION], [NULL | NOT NULL [WITH DEFAULT] |
because ofvisual modelling. 2. The model integrates with dominant relational model. 3. UNION],(a) « (columnNname [Primarykeydefinition], [Alternate_key_definitions],
The model is an effective communication tool because ofvisual representation. [Foreignkeydefinitions]);
Disadvantages of ER-Model 1. The model supports limited constraint, relationship In the definition of CREATE TABLE command column name is the name ofthe column,
representation. 2. The model doesn’t support data manipulation language. 3. The model datatype is the type of data that the column will hold. The keywords NULL and NOT NULL
results in information loss when attributes are deleted from entities. are optional. These keywords specify the conditions that must be checked while
inserting data into the table. Ifa column is specified as NULLthen a NULL value will be
What is normalization? Explain various rules of normalization.
4 inserted in that column ifthe user does not insert value for that column. If a column is
Answer : Normalization Normalization is a concept that isrelated to relational database NOT NULL then the user must specify value for that column otherwise the system does
managementsystem design. It is a process ofdesigning the database structure such that not accept that record and returns an error message. With NOT NULL two other
it minimizes the data redundancy and also avoids data anomalies (insertion, deletion and keywords WITH DEFAULT or UNION can also be specified. Both the keywords are
update anomalies). During normalization, tables or relations that contain anomalies are optional. If a column is NOT NULL WITH DEFAULT then the system uses the default value
decomposed into smaller and well structured relations. The process of normalization for that column ifthe user does not specify the value. For example, the default value for
includes a series ofstages known as normal forms, namely, numeric data type is 0 and for character data type is a space. If a column is specified as
First Normal Form (INF), Second Normal Form (2NF) and third normal form (3NF). NOT NULL UNIQUE then the system does not allow the duplicate values for that column.
Although there exist other higher normal forms like 4NF and 5NF, relations in 3NF are The last three lines in the CREATE TABLE definition specify the primary key, alternate
often described as “normalized” because they are free from redundancy and data key and the foreign key for the new table.
anomalies. Rules in Normalization During normalization, relations are said to be in INF, Example The following statement creates a new table named customer, CREATE Table
5NF, 3NF etc., based on the following rules, Customer (cid INTEGER NOT NULL, f CHAR(20), INTEGER, CHAR(30) NULL, REALNOT
Rule-1: First Normal Form A relation schema is said to be in first normal form ifthe NULL WITH DEFAULT, Primary key(cid));
attribute values in the relation are atomic i.e., there should be no repeated values in a Alter ALTER TABLE command is used to modify the structure ofthe table or view.
particular column. Rule-2: Second Normal Form A relation is said to be in 2NF, ifthe Syntax
following two conditions are satisfied, (i) A relation must be in INF (ii) A non-key attribute ALTER TABLE Tablename ADD [COLUMN] Column_name datatype DROP [COLUMN]
must be fully functionally dependent on primary key. In other words, it can be said that a Columnname RESTRICT/CASCADE ADD [CONSTRAINT] Constraint name DROP
relation is in 2NF, ifit does not contain any partial dependencies. The second condition [CONSTRAINT] Constraint name RESTRICT/CASCADE [ALTER [COLUMN] SET DEFAULT
needs to be verified only if a relation consists of a primary key that is composed ofmany Default option] ALTER [COLUMN] DROP DEFAULT]
attributes i.e., if a relation consists of a primary key with only one attribute then that table With ALTER command following modifications can be done.
is in 2NF only ifit is in INF. (a) Addition of column (b) Deletion of column (c) Addition of constraint (d) Deletion of
Rule-3: Third Normal Form A relation is said to be in third normal form ifthe following two constraint (e) Setting a default value for a column (f) Unsetting a default value for a
conditions are satisfied, (i) A relation must be in 2NF (ii) A relation must not have any column.
transitive dependencies. In other words, it can be said that a relation is in 3NF, ifevery Example ALTER TABLE Employee U-- ■ ADD Project Number INTEGER ALTER TABLE
Employee ALTER sex SEX SET DEFAULT ‘M’
xample DROP TABLE Department RESTRICT This command deletes the department
E
Explain DDL commands with syntaxes.
6 table if some view or integrity constraint is referring to it otherwise, this command fails.
Answer : Data Definition Language (DDL) The data definition language refers to a DROP TABLE Department CASCADE In this case all views and integrity constraint
language that consists of SQL commands for the creation of databases objects like referring to the relation ‘department’ are deleted along with the table.
tables views indexes etc. It also contem commands used to specify access eights to
those database objects, The Data Definition Language (DDL) creates, alters and deletes Write about data manipulation language commands in relational data model.
7
relations in a database. The DDL commands and perform these operations are, 1. Answer : DML (Data Manipulation Language) DML refers to a language that consists of
CREATE 2. ALTER 3. DROP. SQL commands used to manipulate data in table. They include data operations like
a) Create The tables (or relations) in SQL are created using the CREATE TABJJE insert, delete, update and retrieve ii the database tables. The following are the different
command. Syntax CREATE TABLE TABLE NAME (column 1 name (column2_name dau manipulation commands in SQL:
datatype datatype [NULL | NOT NULL [WITH DEFAULT] | UNION], [NULL | NOT NULL 1. INSERT Command This command is used to add information or colunu values to a row
[WITH DEFAULT] |UNION], in a table. Its syntax is as follows,
(columnNname[Primarykeydefinition],[Alternate_key_definitions],[Foreignkeydefinitions]) (a) Partial Insert Statement INSERT INTO table_name (coll, col2.....) VALUES (value 1,
; value2.... );
In the definition of CREATE TABLE command column name is the name ofthe column, (b) Simple Insert Statement INSERT INTO table name VALUES (valuel, value2.....);
datatype is the type of data that the column will hold. The keywords NULL and NOT NULL (c) Interactive Insert Statement SQL> INSERT INTO (coll, col2.... coin) VALUES (R valuel,
are optional. These keywords specify the conditions that must be checked while & value2.....);
inserting data into the table. If column is specified as NULLthen a NULL value will be SELECT Command This command is mostly used in SQL queries. It is considered as the
inserted in that column if the user does not insert value for that column. If a column is basic SQL command which is used to retrieve information or to select specific columns
NOT NULL then the user must specify value for that column otherwise the system does from the tables. Every SQL query starts with the SELECT keyword and is followed by the
not accept that record and returns an error message. With NOT NULL two other list ofcolumns that form the resulting relation. The syntax for SELECT command is as
keywords WITH DEFAULT or UNION can also be specified. Both the keywords are follows, SELECT coll, col2..... FROM table 1, table2..... WHERE condition;
optional. If a column is NOT NULL WITH DEFAULT then the system uses the default value Example To retrieve all the information about the employees from the EMPLOYEE table,
for that column ifthe user does not specify the value. For example, the default value for Consider the following query, SELECT * FROM EMPLOYEE;
numeric data type is 0 and for character data type is a space. If a column is specified as 3. WHERE Clause This clause is used to specify a condition. All the rows/tuples which
NOT NULL UNIQUE then the system does not allow the duplicate values for that column. satisfy the condition are selected.
The last three lines in the CREATE TABLE definition specify the primary key, alternate Example To retrieve EMP NAME and EMP ID from the EMPLOYEE table whose EMP SAL
key and the foreign key for the new table. Example The following statement creates a is greater than 20000, consider the following query, SELECT EMP NAME, EMP ID FROM
new table named customer, CREATE Table Customer (cid INTEGER NOT NULL, cname EMPLOYEE < WHERE EMP SAL > 20000;
aceno fname amt datatype [NULL | NOT NULL [WITH DEFAULT] | UNION], f CHAR(20), 4. GROUP BY Clause GROUP BY clause is used to group the data of a table in a single
INTEGER, CHAR(30) NULL, REALNOT NULL WITH DEFAULT, Primary key(cid)); row or column based on the given grouping condition. It applies some aggregate
(c) Alter ALTER TABLE command is used to modify the structure of the table or view. functions like MAX, MIN, AVG, SUM and COUNT on groups.
Syntax ALTER TABLE Tablename ADD [COLUMN] Column_name datatype DROP 5. ORDER BY Clause ORDER BY clause is used to retrieve the data in a particular order
[COLUMN] Columnname RESTRICT/CASCADE ADD [CONSTRAINT] Constraint name i.e., either ascending or descending order.
DROP [CONSTRAINT] Constraint name RESTRICT/CASCADE [ALTER [COLUMN] SET 6. HAVING Clause HAVING clause is similar to the WHERE clause. It is used to pose the
DEFAULT Default option] ALTER [COLUMN] DROP DEFAULT] conditions on groups. With WHERE clause, conditions are set on rows and with HAVING
With ALTER command following modifications can be done. (a) Addition of column (b) clause, conditions are set on groups.
Deletion of column (c) Addition of constraint (d) Deletion of constraint (e) Setting a 7. UPDATE Command * This command is used to change or modify the existing column
default value for a column (f) Unsetting a default value for a column. values of a row in a table. The syntax is as follows, UPDATE table name ♦ SET colname =
Example ALTER TABLE Employee ADD Project Number INTEGER ALTER TABLE scalar_expression WHERE condition;
Employee ALTER sex SEX SET DEFAULT ‘M’
Drop Destroying or deleting a table can be done with the help of DROP command. What is a lock? What are the different types of locks?
8
Syntax DROP TABLE table name RESTRICT/CASCADE The keywords RESTRICT or Answer : Lock A lock is a variable associated with a database object i.e., the data item.
CASCADE must always be used with DROP command. Locking is a mechanism of manipulating the data value of a lock. Locks are used when
ultiple users want to access a database object concurrently. The main purpose of using
m lock on other data item before releasing the already acquired lock. There are many
locks is to block other transactions from accessing the data items which are locked protocols that can be used for avoiding deadlocks. If deadlocks are present then
currently. The locks are granted to the transaction by a component of database inconsistent state (that occurred when locking mechanism is not used) can be avoided.
management system called lock manager. Types of Lock The following are the two Since, deadlocks issues are handled by simply rolling back the transaction. They are
different types oflock, desirable over inconsistent state issue which basically result in real world problems that
1. Binary locks are very difficult to be handled by a database system.
2. Shared/exclusive locks Techniques for Controlling Deadlock The following are three different techniques used
1. Binary Locks Binary locks are the locks that consist oftwo states, locked and for controlling the occurrence of deadlock, 1. Deadlock prevention 2. Deadlock detection
unlocked. Binary values ‘1’ and ‘0’ are used for representing locked state and unlocked 3. Deadlock avoidance.
state respectively. If a data item is in lock state, then it implies that it cannot be accessed The selection of these techniques is basically dependent on the database environment.
by any requested database operation. If The data item is in unlock state, then any 1. Deadlock Prevention This technique prevents the occurrence of deadlock by avoiding
database operation that have sent a request for accessing the data item can access it. the execution ofthe conditions that may generate deadlock. This is done by examining
Whenever a transaction sends a request for accessing data item (say x) then database whether to grant a lock permission to a transaction results in a deadlock or not. If it
system must ensure whether ‘x’ is in unlocked state. Ifit is in unlocked state, then DBMS results in deadlock, then the requested transaction is aborted and all the changes
grants the lock to the requested transaction, otherwise, the transaction have to wait until performed by that transaction is rolled back. In addition to this, all the locks acquired by
the required data item is unlocked. the aborted transaction are released and then the transaction is reschedule for
2. Shared/Exclusive Locks (i) Shared Lock (S) A transaction that acquired shared lock on execution. This technique is generally preferred when the probability forthe occurrence
data item P , can execute read instruction but not the write instruction i.e., these locks ofdeadlock is very high.
support only read integrity. Using these locks multiple users can read the data 2. Deadlock Detection This technique detects the transaction whose execution may lead
concurrently, but cannot change/write the data value. The intention of this lock is to to the occurrence of deadlock. This is done by examining the database for deadlocks
ensure that data items are not modified while they are locked using shared locking mode. regularly. While examining if a deadlock is found, then the transaction that resulted in
(ii) Exclusive Lock (X) A transaction that acquired an exclusive lock on data item P , can deadlock is aborted. However, the remaining transactions ofthe database is not affected
execute both read as well as write instruction i.e., these locks support read and write by the aborted transaction and therefore can continue with their execution. The changes
integrity. Only a single transaction can update the data at a time. The intention of this ofaborted transaction is rolled back and the transaction is rescheduled for execution.
lock is to provide exclusive use of the data item to one transaction i.e.^if a transaction T This technique is generally preferred when the probabilities of deadlock occurrence is
A holds an exclusive lock on data item P, then no other transaction can read/update that low.
data item until T A releases the lock. 3. Deadlock Avoidance This technique avoids the occurrence of deadlock by allowing the
transaction to initially (prior to execution) acquire all the locks that are required for
Define deadlock. Discuss several techniques to control deadlock.
9 performing the execution. Using this technique, a transaction acquires the lock in
Answer : Deadlock Deadlock is a situation where one transaction is waiting for another succession, due to which the roll back of conflicting transactions can be avoided.
transaction to release a lock, which itself is waiting for another transaction to release a However, such way of acquiring lock in sequential fashion increases the response times
lock before it can proceed with its normal execution. of . execution. This technique is preferred in the situations where in the response time is
Example Suppose, a transaction 1 \ holds an exclusive lock on some data item x and less for the transactions listed on the system
transaction 7\ holds an exclusive lock on data item y. Now, T \ requests an exclusive lock
on y and T 2 requests an exclusive lock on x. Both these transactions are waiting for one 0 What is meant by distributed database? Why it is needed?
1
another for releasing their locks (i.e., i\ is waiting for T2 to release the lock and T, is Answer: Distributed Database A distributed database is a single logical database that is
waiting for T, to release the lock). Thus, a state is arrived, where none of these spread physically across computers in multiple locations. These computers are
transaction can proceed with their normal execution. Such a state is called a deadlock connected through a data communication network. Such database requires multiple
state. database management systems, running at each remote site. The different types of
If a deadlock is detected, it can be handled by the system by rolling back any one of the distributed database environment can be distinguished based on,
transaction. If a transaction is rolled back, then all the locks acquired by that transaction 1. The degree to which these different DBMSs cooperate or work in partnership.
can be released. Now, other transactions can access the unlocked data item and can 2. Whether there is a master site that coordinates request involving data from multiple
proceed with their execution. DBMS is responsible for detecting and preventing the sites. Reasons/Need of Distributed Database There are several reasons for developing
deadlock. Another reason for the occurrence of deadlock is that, a transaction request a distributed databases. Some ofthem includes the following,
. Sharing Data The major advantage in building a distributed database system is the
1 f ails at one location, then the other components present at different locations remains
provision of an environment where users at one site may be able to access the data unaffected i.e., only those applications are affected that are linked with the data ofthe
residing at other sites. For instance, in a distributed banking system, where each branch failed location. Also, the failure ofentire system is infrequent in the distributed databases.
stores data present at another branch. Without this capability, a user wishing to transfer
funds from one branch to another would have to resort to some external mechanism that 1 Define client server architecture. Discuss the advantages and disadvantages of client
1
connect existing systems. servei architecture
2. Organizational and Economic Motivation Distributed organizational structure plays a Answer : Client/server Architecture An architecture that provides a well-defined way with
vital role while designing decentralized organizations. A distributed database consumes which the computers i.e., clients (user (or) requestor of resources) and servers (provider
less cost when compared to the large and centralized databases. Hence, organizations ofresources) can interact with each other is known as a client/server architecture. In
and economyof-scale are the basic factors considered while developing distributed distributed database, TP is considered as a client and DP is considered as a server when
databases. TP/client sends a request and sends back the result to TP on its request.
3. Connecting Existing Databases with Each Other C If a large number of databases exist Advantages of Client/Server Architecture 1. The solutions provided by the client/server
in a particular organization, then a distributed database approach is required. Because applications with respect to the start-up requirements ofthe infrastructure are less
ofthese databases, global applications need to be performed. In this situation, the expensive when compared with the solutions provided by mainframe (or) minicomputer.
distributed database is developed by employing bottom-up approach and initiating from 2. It helps in improving the functionality and simplicity by allowing the use ofGraphical
the already available local databases. This process needs less amount oflocal UserInterface (GUI) associated with microcomputer. Example ofsuch solution is the use
restructuring, when compared to the construction of new centralized databases. ofjava and .Net frameworks in ubiquitous web browsers. 3. Most ofthe students are
4. Smooth Incremental Growth Smooth incremental growth is supported by distributed opting to learn because market is preferring to recurrent candidates who have are skilled
databases when new, autonomous units are added so as to expand the organization. in java and .Net programming. 4. To provide support for the interaction with many DBMSs
This growth, however, doesn’t have much impact on the previously existing the client/server applications provide many query and data analysis tools. 5. PC in the
organizational units. But, in the centralized databasesthe growth has a large impact both workplace is well established. 6. With use ofpowerful PCs, the cost associated with the
on the new applications and also on the already existing units. The initial dimensions application development can be reduced.
ofthe system are responsible for managing the future expansions, which would Disadvantages of Client/server Architecture 1. Security problems arise with increase of
otherwise be difficult to predict and at the same time would be expensive to implement. users and processing sites. 2. Difficulties in the management ofplatforms like operating
5. Decreased Communication Overhead Distributed databases consist of more systems, LANs etc., because ofthe complexities in the client/server environment. 3. Need
communication overhead when compared to centralized databases. The communication for the broad knowledge increases the maintenance cost ofthe environment because
overhead in distributed databases can be reduced by localizing the applications (i.e., ofthe need to provide training to the users.
every application can access its own database). Therefore, the main goal while designing
a distributed database is to maximize the level of localization.
6. Enhanced System Performance With a high degree of parallelism, several independent
processors can enhance the performance level of the system. This performance criteria
is not only considered by the distributed databases, but also by the multiprocessor
systems. In the distributed databases, the data is decomposed into smaller components,
due to which, (i) The applications are made dependent (ii) The applications locality is
increased (iii) The interference existing among several different processors is reduced
(iv) The load is shared among several processors and (v) The issues arising due to
communication network or common services of system are prevented.
7. Higher Reliability and Availability A system with higher reliability and availability can
be developed by using a distributed database approach along with redundant data and
some techniques (that are not completely known). The processing capability of the
autonomous processors present at different locations doesn’t ensure a higher reliability
ofthe system. Instead, it ensures a “graceful degradation” ofthe distributed system in
which failures are more frequent when compared to the centralized database. This is
because, the components are large in number. In distribution databases, if a component