Database Management System Viva Question

Data integrity : integrity constraints provide a way of ensuring that changesmade in the database by authorized users will not result in the loss of dataconsistency and correctness.
Data security: database management system has a separate systemfor securitywhich is responsible for protecting database against accidental or intentional loss, destruction and misuse of data.
Transaction control: the changes made in the database can be revertedbagwith the help of rollback command and safe successfully with the helpof commit data command
Concurrency control: the data in the database can be exist by multipleuser at the same time.Such operations are allowed by sharing of Same data betweenmultiple users.
Data recovery, backup and restore: database recovery is the process of resting database to original state after database failure.

5. What are the difference between file system and database system ?

Ans:

File system	Data base management system
i. It consist of collection of individual file accessed by application programs.	i. Computerized records keepingsystemm is used in the Databasemanagement system
ii. It is designed to allow pre- determined access to data.	ii. It allows flexible access to data.
iii. It co-ordinates only with the physical access to the data.	iii.It co-ordinates only with the physicaland logical access to the data.
iv. Unauthorized access cannot be restricted in the file system.	iv.Unauthorized access can be restrictedin the file system.
v. Data redundancy cannot be controlled in this system.	v. Data redundancy can be controlledinthis system.

6. What are the advantages of database management system(DBMS) ?

Ans:

Advantages of database management system:
a. Data redundancy can be reduced.
b. Inconsistent data can be avoided.
c. Data can be share between multiple user.
d. User can have centralised control of the data.
e. Data standard can be enforced.
f. Security and restriction can be applied to the data present in the database.
g. Integrity of the database can be maintained.
h. Data independency can be provided among the users. i. New application maybe develop using the existing database.

7. Name the different type of users present in the database ?

Ans:

Naive user is the one who interact with the system using application programsthat have been developed previously.
Application programmers are the one who developers or computer professionals who are responsible for writing application programs that usethedatabase.
Sophisticated users are the one who interact with the application without writing any programs by using a database query language.
Specialized user other ones who create the actual database and implementstechnical controls that is needed to enforce various policy decisions.

8. What is data independence ?

Ans:

Data independence is defined as the capacity to change one Level Schemawithout changing any schema at the next higher level.

9. Name different type of Data Independence.

Ans:

Logical data Independence : Logical data independence a capacity tochangethe conceptual Schema without having any changes to the external Schemaorapplication program.
Physical data independence : Physical data Independence is a capacitytochange the internal Sigma without having any changes to conceptual schema.

10. Define DBA( Database Administrator).

Ans:

A Database Administrator (DBA) is individual or person responsibleforcontrolling, maintenance, coordinating, and operation of database management system.

11. What are the roles of DBA or what are the roles of database administrator?

Ans:

Roles of DBA are;
a. Creating and maintaining database standards and policies.
b. Supporting database design, creation, and testing activities.
c. Managing the database availability and performance, including incident andproblem management.
d. Administering database objects to achieve optimum utilization.
e. Defining and implementing event triggers that will alert on potential databaseperformance or integrity issues.
f. Performing database housekeeping, such as tuning, indexing, etc.
g. Monitoring usage, transaction volumes, response times, concurrency levels, etc.

12. Define data abstraction.

Ans:

Database systems comprise complex data-structures. In order to makethesystem efficient in terms of retrieval of data, and reduce complexity in termsofusability of users, developers use abstraction

13. Name different level of data abstraction

Ans:

Physical Level : It is the lowest level of abstraction describe howthe dataareactually stored.
Conceptual Level : It describes how the database appears to the users conceptually and the relationships between various data tables.
View Level : Highest level of data abstraction. This level describes the user interaction with database system.

14. What is internal Schema, conceptual Schema and external Schema?

Ans:

Internal Schema : It defines various store data types and specify what typeofindices exist, how store fields are presented and so on.
Conceptual Schema : It is a representation of the entire content of the database. The conceptual level describes what data are to be stored inthedatabase and also describes what relationship exists among those data
External Schema : It consist of definitions of each of the various external datatypes in that external view.

15. What is 1 tired architecture ?

Ans:

In One-Tier Architecture the database is directly available to the user, theuser can directly sit on the DBMS and use it i.e.; the client, server and the Database all present on the same machine.

16. What is 2 tier architecture?

Ans:

The application on the client-side establishes a connection with the server-side in order to communicate with the DBMS.

17. What is 3 tier architecture?

Ans:

3-tier architecture divides the complete system into three inter-relatedbut independent modules as shown below:

Physical Level: At the physical level, the information about the location of database objects in the data store is kept.
Conceptual Level: At conceptual level, data is represented in the form of various database tables. Also referred as logical schema, it describes what kind of data is to be stored in the database.
External Level : An external level specifies a view of the data in terms of conceptual level tables. Each external level view is used to cater to theneedsof a particular category of users.

Entity-relation data model

1. Define your ER model.

Ans:

ER diagram is the first step of the database design to specify the desiredcomponent of the database system and the relationships among those components

2. What are the components of ER model?

Ans:

Components of ER model are entity, attributes and relationship

3. Define entity.

Ans:

Entity is anything in Real world object which may have physical or logical existence.

4. Define entity set

Ans:

Entity set is a collection of same type of data.

5. Define attribute.

Ans:

Each entity has its own property which describes that entity these suchproperties are called as attributes.

6. Define relationship.

Ans:

Relation ship is an association among two more than two entities.

7. Name different entity type.

Ans:

There are two type entities namely,

Strong entity : A strong entity set is an entity set that contains efficient attributes to uniquely identify any tuple
Weak entity : A weak entity set is an entity said that does not contains sufficient attributes to uniquely identify any tuple.

8. Name different type of attributes.

Ans:

Simple attribute : Attributes which cannot be divided into multiple attributesare called as simple attributes
Composite attribute : Attributes which can be divided into multiple attributesare called as composite attributes
Single-valued attribute : Attributes which cannot have more than one valueare called as single valued attributes.
Multi-valued attributes : Attributes which can have more than one valuearecalled as multivalued attributes.
Derived attributes : The attributes whose values can be derived fromthevalues of other attributes are called as derived attributes
Null attributes : Attributes that can take null values when no values areassignto the attributes are called as null attributes.

9. Define key

Ans:

Key is an attribute of an entity which must have a unique value by whichanyrow can be uniquely identify.

10. Name different type of key.

Ans:

Super key : A super key is a group of single or multiple keys which uniquelyidentifies rows in a table.
Candidate key : Candidate key is a set of attributes that uniquely identifiestuples in a table.
Primary key : Primary key is the attribute which uniquely identifies theeverytuple of the table.
Foreign key : When a primary of one table is used to uniquely identifythetuples of another tale then such key is called foreign key.
Alternate key : A candidate key which is not selected as primary keyis calledas alternate key.
Secondary key : Secondary key is the attribute set of attributes that is usedtoaccess a single tuple in entity.

11. Define relationship set.

Ans:

Relationship set is define as the collection of all relationship of same type.

12. Define degree of relation ship.

Ans:

Degree of relationship is the number of participating entity types in a particular relation.

13. Name different types of relationship

Ans:

Based on the mapping constraits relationship are of four types:

One to one relationship : In this relationship one tuple of one entity is relatedwith only one tuple of the another entity
One to many relationship : In this relationship one tuple of one entitycanberelated with many tuples of another entity.
Many to one relationship : In this relationship many tuples of one entitycanbe related with one tuple of another entity.
Many to many relationship : In this relationship many tuples of entitycanberelated with many tuples of another entity.

Based on the partition constraits relation are of two types:

Total partition relationship : Every object in the entity must take part intherelationship. This type of relation is represented by dark line or double linebetween entity and relation.
Partial partition relationship : More than one object in an entity mayparticipate in the relation. This type of relation is represented by single linebetween entity and relationship

14. Define EER model (Extended Entity Relationship) model.

Ans:

A diagrammatic technique for displaying the concept of specialisationgeneralization and aggregation when they arises in EER Schema the resultingSchema diagram is called as EER diagram

15. Define specialization, generalization, aggregation.

Ans:

Specialization: The process of designating subgrouping within entityset iscalled specialization.
Generalization : It is a process in which we differentiate among server entitytypes and identify their common features and generalizing themto a singlesuperclass of which original entity type are special class.
Aggregation : It allows us to treat a relationship set as an entity set for thepurpose of participating other relationship. Aggregation is meant to represent or relationship between a whole object and its component parts.

Relational database model

1. Define relation.

Ans:

Relation are a logical structure which is a collection of tables consistinghorizontal rows also called tuples and vertical columns also called as attributes. Tables are know as relations.

2. What are the characteristics of relation ?

Ans:

a. A table composed of rows and columns.
b. Each table in a database has its unique table name.
c. Each table rows represents a single entity occurrence within the entity set.
d. All values in a same column must conform to the same format of data.
e. Each table must have a single attribute or set of attributes that uniquelyidentifieseach row

3. Define attributes(column).

Ans:

Relation has its own properties which describes that relatiion such propertiesare know as attributes.

4. Define tuples(row).

Ans:

A single row in relational table which contains all the information about asingle entity is called as tuple.

5. Define domain.

Ans:

The intersection column and row in a relational table which represents dataofentity is called as domain.

6. What is relational database schema?

Ans:

Relational database schema consists of a number of relation schema associated with that database. It describes the structure of relational database

7. What is relational schema ?

Ans:

The intersection column and row in a relational table which represents dataofentity is called as domain.

8. Name different type of relational constraints.

Ans:

Constraint make sure that only authorized user will make modifications todatabase and changes should not lead to loss of data consistency and correctness.
Types of relational constraint are as follows:

Domain relational constraint : Domain constraint ensure that the datavalueentered for that particular column matches with the data type definedfor that column
Entity integrity constraint : Entity constraint allow us to tes whether thetuple inserted into the database are correct or not
Referential integrity : Referential integrity is a value appearing inaonerelation for a given set of attributes also appears for another set of attributesinanother relation.

9. Name the different type of domain constraint.

Ans:

a. Required data constraint/Nullness constraint : This constraint ensure that the attributes in the database are not allowed to contain null values or blanks.
b. Check constraint : The check constraint is used to ensure that attributevalue satisfies specific condition as specified by data requirements or user.
c. Default keyword : Default keyword is used to add a default specific value, if attribute value is not provided by user.

10. Name the different type of entity integrity constraint.

Ans:

Unique constraint/ Unique key : This attribute ensure that no two tuples canhave equal value for same attribute.
Primary key constraint : A primary key constraint depicts a key comprisingone or more columns that will help uniquely identify every tuple/recordinatable. The main difference between unique constraint and primary key constraint isthat in unique constraint allows one null value and primary key does not allownull value.

11. Define relational algebra.

Ans:

Relational Algebra is procedural query language, which takes Relation as input and generate relation as output.

12. Name the different operations in relational algebra

Ans:

Unary relational operations:

Project operation(ℼ) : This operator is used for selecting columns of atableto them displayed in the result
Select operation(σ) : This operator is used to select some rows from the table which satisfy particular selection condition given in selection operation.
Rename operation(δ) : This operator is used to rename the column that have same name.

Set theory operations:

Union operation(Ս) : This operator effectively appends the result of first query to the second query
Difference operation(-) : This operation finds the records that are present inone relation but not in other and display them in the result.
Intersection operation(Ո) : This operator is used to display common values from both the tables in the result

Binary operations:

Join operations() : This operation is used to retrieve data from multiple tables.
Cartesian operations(X) : This operation is used to perform relational product or cartesian product of two tables specified in the query
Division operations(%) : This operator is used to divide values of oneetable with the values of another table and display their result in the resultant table.

13. Name different types of join in relational algebra.

Ans:

Natural join operations() : This operator is used to returns all the rows by matching values in common columns having same name and data type of columns and that column should bee present in both the table
Inner join operations() : This operator is used to combines tuples from multiple relation if they satisfy the specified join condition. It preserve only matching tuples from both the table in the result.
Outer join operations() : This operator is used to combines tuples from multiple relation even though they do not satisfy the specified join condition. It preserve non-matching tuples also from both the table in the result.
Left outer join operations() : This operator is used to take all the tuples from the left relation that did not match with any tuple in the right relation.
Right outer join operations() : This operator is used to take all the tuples from the right relation that did not match with any tuple in the left relation.
Full outer join operations() : This operator is used to take all the tuples from both the relations although they did not match with each other.

SQL(Structure Query Language)

1. Define SQL.

Ans:

Software design deals with transforming the customer requirements, as SQL (Structure Query Language) is the computer language into store, manipulate and retrive data store in the relational data bases.

2. What is DDL command? Name the DDL commands.

Ans:

DDL (Data Definition Language) statements are used to build and modify the structure of the table and the other objects in the database.
The DDL commands are as follows:
i. CREATE: Used to create database object.
ii. ALTER: Used to modified database object.
iii. DROP: Used to drop the database object.
iv. RENAME: Used to renamed the database object.
v. TRUNCATE: Used to delete all the records from the table.

3. What is DML command? Name the DML commands..

Ans:

DML (Data Manipulation Language) used to modify or manage data inthedatabase.
The DML commands are as follows:
i. INSERT: Used to add new records in the table.
ii. UPDATE: Used to modify the existing data in the table.
iii. DELETE: Used to delete some or all records from the table.

4. What is DCL command? Name the DCL commands.

Ans:

Data control language (DCL) is used to control various user actions or privileges in database.
DCL commands are as follows:
i. GRANT: Give some privileges to user for performing task on database.
ii. REVOKE: Take back permission given from user

5. What is TCL command? Name the TCL commands.

Ans:

Transaction Control Language (TCL) is a set of special commands that deal with the transactions within the database.
TCL commands are as follows:
i. COMMIT: It is used to permanently save all the modifications done (all thetransactions) in the database.
ii. ROLLBACK: It is used to undo all the transactions / modifications that have done to the database.

6. Define joins.

Ans:

A SQL Join statement is used to combine data or rows from two or more tables based on a common field between them

7. Name different types of joins in sql.

Ans:

There are four types of, namely
i. INNER JOIN : The join operation that do not preserve the non-matching tuples in the result.
ii. LEFT OUTER JOIN : The join operation that preserve the tuples onlyfromthe table that is present to the left of the left outer join operator or named before left outer join operator.
iii. RIGHT OUTER JOIN : The join operation that preserve the tuples only from the table that is present to the right of the right outer join operator or named after right outer join operator.
iv. FULL OUTER JOIN : The FULL OUTER JOIN combines the results of both left and right outer joins and returns all matched or unmatched rows from the tables on both sides of the full outer join operator.

8. Define Triggers.

Ans:

A triggers is a procedure that is automatically invoked by the database management system in response to specific changes to the database or a table in database.

9. What are aggregate functions?

Ans:

Aggregate functions are the functions that can summarize the data of thetable.
Some aggregate functions in sql are as follows:
i. AVG() : This function is used to calculate average of all the values from all thetuples of the column.
ii. COUNT() : This function is used to calculate count of all the tuples of the column.
iii. SUM() : This function is used to calculate sum of all the values fromall the tuples of the column.
iv. MIN() : This function is used to give the minimum value in the column.
v. MAX() : This function is used to give the maximum value in the column.

10. What is the use of group by clause, having clause, WHERE clause in sql?

Ans:

i. A GROUP BY clause creates a set of data, containing several sets of records grouped together based on some conditions.
ii. HAVING is conditional clause which checks data for specific search condition. A HAVING clause is like a WHERE clause, but applicable only to groups as awhole, wheras the WHERE clause applies to individual rows.
iii. The WHERE clause is used to filter records. It is used to extract only those tuples from the table that fulfill a specified condition.

11. Define integrity constraint in sql.

Ans:

Integrity constraint are a set of rules that ensures that the data insertion, updating and other processes have to be perform in such a way that it does not thedata integrity.

12. What is domain constraint, entity integrity constraint, check constraint, referential integrity constraint and key constraint in sql?

Ans:

a. Domain constraint: It ensure that the data value entered for that particular column matches with the data type defined for that column.
b. Check constraint: It is used to ensure that attribute value satisfies specificuserdefined condition.
c. Referential integrity: It is a value appearing in a one relation for a given set of attributes also appears for another set of attributes in another relation. Referential integrity constraint is defined between two tables to maintain the consistency among tuples in the two relations.
d. Key constraints: In dbms these are set of rules that are defined for primary key or for foregin key in dbms.
e. Entity integrity constraint: It ensures that the primary key attribute in a relation, should not accept a null value

13. Define views in database.

Ans:

A view is defined as a database object that allows us to create a virtual table in the database whose contents are defined by a query or taken from one or more tables.

Relational database design

1. What are Pitfalls of relational database management system ?

Ans:

Pitfalls of relational database management system suggests the effects when database design is incorrect or invalid.

2. Define decomposition

Ans:

The process of breaking up or dividing a single relation into two or more subrelations is called as decomposition of a relation.

3. Define functional dependency.

Ans:

Functional dependency is a relationship that exists between two relations when one attribute uniquely determines another attribute.

4.Name different type of functional dependency.

Ans:

a. Full functional dependency b. Partial functional dependency c. Transitive functional dependency d. Trival functional dependency e. Multivalued functional dependency

5. What are the properties of functional dependency ?

Ans:

The properties of functional dependency are:

Reflectivity: It state the for a relation R having attributes X, Y, Z, W, if Yisthe subset of X then X → Y relation is true.
Append/Augmentation: It state the for a relation R having attributes X, Y, Z, W, if X → Y relation is valid then it is possible to append attribute Z to both sides of functional dependencies i.e XZ → YZ is valid.
Transitivity: It state the for a relation R having attributes X, Y, Z, W, if X→Y and Y→Z relations are valid then it is possible to use transitivity if attributes X, Y, Z are part of the same table i.e X→Z is valid.
Union: It state the for a relation R having attributes X, Y, Z, W, if two tables are have same primary key then we should consider them as one by putting them together i.e. X → Y and X→Z relations are valid then we can consider them as X→YZ this is also valid.
Decomposition: It state the for a relation R having attributes X, Y, Z, W, if two tables are determine by same primary key then we should consider them as individual by breaking them into two separate tables i.e. X→YZ relations arevalid then we can consider them as X → Y and X→Z this is also valid.
Pseudo transitivity: It state the for a relation R having attributes X, Y, Z, W, ifX → Y and YZ→W relations are valid then it is possible to use pseudo transitivity if attributes X, Y, Z are part of the same table i.e XZ→Wis valid.

6. What is normalisation?

Ans:

Normalisation is the process of step by step decomposition of complex records into simple records.

7.What is 1NF ?

Ans:

A relation in 1NF states that all attributes in the relation must have atomic(single) values and all attributes in a tuple(row) must have a single value.

8.What are the conditions for a relation to be in 1NF?

Ans:

Conditions for a relation to be 1NF are as follows:
a. Every value of each attributes must be atomic.
b. No composite values will be there.
c. All entities in any column must be of same kind.
d. All the values in the column must unique.
e. No two rows are identical.

9.What is 2NF ?

Ans:

A relation is said to be in 2NF if it is in 1NF and any non-prime attributes does not have any partial dependency on any prime attributes i.e. all non-prime attributes are have full functional dependency on prime attributes.

10. What is 3NF?

Ans:

A relation is said to be in 3NF if it is in 2NF and any non-prime attributes does not have any transitive dependency on any prime attributes i.e. all non-prime attributes are have full functional dependency on prime attributes.

11.What is BC NF ?

Ans:

A relation is said to be in BC NF if it is in 3NF and there should not be any overlapping among non-prime attributes.

12.What is 4NF ?

Ans:

A relation is said to be in 4NF if it is in BC NF and there should not be any multivalued dependencies among attributes.

13.What are the goals of decomposition?

Ans:

The goals of the decomposition are:

Loss less join decomposition.
Dependency decomposition.
No repetition of information.

Transaction management and concurrency

1. Define transactions.

Ans:

A transaction is a sequence of one or more SQL statements that combined together to form a single logical unit of work

2. What are the states of transaction?

Ans:

There are five states of transactions namely, Active state, Partially committedstate, Failed state, Committed state and Aborted State.
i. Active state: As soon as transaction execution starts it enters in the activestateand remain in this state till transaction finishes.
ii. Partially committed state: As soon as last operation in transaction is executedtransaction goes to partially committed state. At this condition the transactionhascompleted its execution and ready to commit on database server.
iii. Failed state: A transaction enters the failed state after the systemdeterminesthat the transaction can no longer proceed with its normal execution.
iv. Aborted State: Fail transaction enters in aborted state where it has beenrollback restoring into prior state and gets terminated.
v. Committed state: After successful completion of transaction it enters inthecommitted state and gets terminated

3. Define deadlock.

Ans:

In a database, a deadlock is an unwanted situation in which two or more transactions are waiting indefinitely for one another to give up locks.

4. How to detect a deadlock?

Ans:

A deadlock can we detected by using wait for graft method in which a graphis drawn based on the transaction and their lock on the resources if the graph is a close loop or a cycle then there is a deadlock otherwise not

5. Name the method to prevent the deadlock.

Ans:

Deadlock can be prevented by using two methods wait-die Schema andwound wait schema.
i. Wait-die schema: In this method if a transaction for a resources that is held by other transaction then database management system checks the time stamp of both the transaction and allows the older transaction to wait until the resources is available for execution.
ii. Wound-Wait Schema: In this method if an older transaction request for a resources hold by a younger transaction then an older transaction forces a younger transaction to kill the younger transaction.

6. What are acid properties?

Ans:

In order to maintain consistency in a database before and after transaction certain properties are followed. These properties are called ACID properties.
They are as follws:
i. Atomicity: This property states that either the entire transaction takes placeat once or doen’t happen at all.
ii. Consistency: It states that integrity constraits must be maintained so that the database is consistent before and after the transaction.
iii. Isolation: This property ensures that multiple transaction can occur concurrently without leading to the in consistency of database state.
iv. Durability: This property ensure that once the transaction has completed it’s execution, then update and modifications are stored in the database and written to disk and they persist even if a system failure occurs.

7. What is serializability?

Ans:

Serializability is the concept in a transaction that helps to identify which non-serial schedule is correct and will maintain the database consistency.
There are two type of serializability as follows:
i. Conflict serializability: A schedule is called conflict serializable if it can be transformed into a serial schedule by swapping non-conflicting operations.
ii. View serializability: A schedule is said to be View-Serializable if it is view equivalent to a Serial Schedule (where no interleaving of transactions is possible).

8. What is time stamp base protocol?

Ans:

Timestamp base protocol is a general method to achieve serializability order of transaction for execution in which execution can be decided in advance using its time at which transaction entered in system.

9. What is log based recovery?

Ans:

Lock is a mechanism which is important in a concurrent control. It controls concurrent access to a data item. It assures that one process should not retrieve or update a record which another process is updating. If the locking is not done properly, then it will display the inconsistent and corrupt data. It manages theorder between the conflicting pairs among transactions at the time of execution.

10. What is log base protocol?

Ans:

In log based protocol, any transaction cannot read or write data until it acquires an appropriate lock on it.

11. Name two type of locks.

Ans:

There are two type of locks, namely Shared lock and exclusive lock.
i. Shared lock: This type of locking is used by the DBMS when a transaction wants to only read data without performing modification to it fromthe database.
ii. Exclusive lock: This type of locking is used by the DBMS when a transaction wants to read or write (i.e. performing modification) data in the database

12. What is granting lock?

Ans:

Lock is granted only when no other conflicting type of lock on it, is held by other transaction.

13. What is rejecting lock?

Ans:

Lock is rejected when other conflicting type of lock is held by other transaction

14. What is Thomas write rule ?

Ans:

Thomas write rule is a concurrency control mechanism that is implemented to maintain and manage the concurrent transactions and processes in the database management system.