Notes RDBMS

Download as pdf or txt
Download as pdf or txt
You are on page 1of 3

‭ Answer : Database A database is an integrated, shared collection of data.

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 l‭ock 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‬

You might also like