{
"iq": [{"id": "1",
"q": " What is database?",
"answer":"A database is a collection of information that is organized.So that it can easily be accessed,managed,and updated."},
{"id": "2", "q": "What is DBMS?", "answer":"DBMS stands for Database Management System.It is a collection of programs that enables user to create and maintain a database."
},
{
"id": "3",
"q": " What is a Database system?",
"answer": "The database and DBMS software together is called as Database system."
},
{
"id": "4",
"q": " What are the advantages of DBMS?",
"answer": "I. Redundancy is controlled.\nII. Providing multiple user interfaces.\n III.Providing backup and recovery\n IV.Unauthorized access is restricted.\n V. Enforcing integrity constraints."
},
{
"id": "5",
"q": "What is normalization?",
"answer": "It is a process of analysing the given relation schemas based on their Functional Dependencies(FDs) and primary key to achieve the properties(1).Minimizing redundancy,\n(2).Minimizing insertion,\ndeletion and update anomalies."
},
{
"id": "6",
"q": " What is Data Model?",
"answer": " A collection of conceptual tools for describing data, data relationships data semantics and constraints."
},
{
"id": "7",
"q": "What is E-R model?",
"answer": "This data model is based on real world that consists of basic objects called entities and of relationship among these objects. Entities are described in a database by a set of attributes. "
},
{
"id": "8",
"q": "What is Object Oriented model?",
"answer": "This model is based on collection of objects.An object contains values stored in instance variables with in the object.An object also contains bodies of code that operate on the object.These bodies of code are called methods.Objects that contain same types of values and the same methods are grouped together into classes."
},
{
"id": "9",
"q": "What is an Entity?",
"answer": "An entity is a thing or object of importance about which data must be captured."
},
{
"id": "10",
"q": "What is DDL (Data Definition Language)?",
"answer": "A data base schema is specifies by a set of definitions expressed by a special language called DDL."
},
{
"id": "11",
"q": "What is DML (Data Manipulation Language)?",
"answer": "This language that enable user to access or manipulate data as organised by appropriate data model.Procedural DML or Low level: DML requires a user to specify what data are needed and how to get those data.Non - Procedural DML or High level: DML requires a user to specify what data are needed without specifying how to get those data"
},
{
"id": "12",
"q": " What is DML Compiler?",
"answer": "It translates DML statements in a query language into low-level instruction that the query evaluation engine can understand. "
},
{
"id": "13",
"q": "What is Query evaluation engine?",
"answer": "It executes low-level instruction generated by compiler."
},
{
"id": "14",
"q": "What is Functional Dependency?",
"answer": "Functional Dependency is the starting point of normalization.\nFunctional Dependency exists when a relation between two attributes allows you to uniquely determine the corresponding attribute’ s value."
},
{
"id": "15",
"q": "15. What is OLTP (Online Transaction Processing)?",
"answer": "ANSWER: In OLTP - online transaction processing systems relational database design use the discipline of data modeling and\ngenerally follow the Codd rules of data normalization in order to ensure absolute data integrity.Using these rules\ncomplex information is broken down into its most simple structures(a table) where all of the individual atomic level\nelements relate to each other and satisfy the normalization rules."
},
{
"id": "16",
"q": "16. What is PRIMARY KEY?",
"answer": "ANSWER: A PRIMARY KEY constraint is a unique identifier for a row within a database table. Every table should have a primary keyconstraint to uniquely identify each row and only one primary key constraint can be createdfor each table.The primarykey constraints are used to enforce entity integrity."
},
{
"id": "17",
"q": "17. What does SQL Stands for?",
"answer": "SQL stands for Structured Query Language. For more info visit What is SQL?"
},
{
"id": "18",
"q": "18.What is the difference between JOIN and UNION?",
"answer": "ANSWER: SQL JOIN allows us to “lookup” records on other table based on the given conditions between two tables. For example, if we have the department ID of each employee, then we can use this department ID of the employee table to join with the department ID of department table to lookup department names.\nUNION operation allows us to add 2 similar data sets to create resulting data set that contains all the data from the source data sets.Union does not require any condition\nfor joining.For example,\nif you have 2 employee tables with same structure,\nyou can UNION them to create one result set that will contain all the employees from both of the tables.\nSELECT * FROM EMP1\nUNION\nSELECT * FROM EMP2;"
},
{
"id": "19",
"q": "19. What are different normalization forms?",
"answer": "ANSWER: 1NF: Eliminate Repeating Groups Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain.\n2 NF: Eliminate Redundant Data If an attribute depends on only part of a multi - valued key,\nremove it to a separate table.\n3 NF: Eliminate Columns Not Dependent On Key If attributes do not contribute to a description of the key, remove them to a separate table.All attributes must be directly dependent on the primary key.\nBCNF: Boyce - Codd Normal Form If there are non - trivial dependencies between candidate key attributes,\nseparate them out into distinct tables.\n4 NF: Isolate Independent Multiple Relationships No table may contain two or more 1: n or n: m relationships that are not directly related.\n5 NF: Isolate Semantically Related Multiple Relationships There may be practical constrains on information that justify separating logically related many - to - many relationships.\nONF: Optimal Normal Form A model limited to only simple(elemental) facts,\nas expressed in Object Role Model notation.\nDKNF: Domain - Key Normal Form A model free from all modification anomalies is said to be in DKNF.\nRemember,\nthese normalization guidelines are cumulative.For a database to be in 3 NF,\nit must first fulfill all the criteria of a 2 NF and 1 NF database."
},
{
"id": "20",
"q": "20. What is sub-query? Explain properties of sub-query? ",
"answer": "ANSWER: Sub-queries are often referred to as sub-selects, as they allow a SELECT statement to be executed arbitrarily within the body of another SQL statement. A sub-query is executed by enclosing it in a set of parentheses. Sub-queries are generally used to return a single row as an atomic value, though they may be used to compare values against multiple rows with the IN keyword.\nA subquery is a SELECT statement that is nested within another T - SQL statement.A subquery SELECT statement\nif executed independently of the T - SQL statement,\nin which it is nested,\nwill\nreturn a resultset.Meaning a subquery SELECT statement can standalone and is not depended on the statement in which it is nested.A subquery SELECT statement can\nreturn any number of values,\nand can be found in ,\nthe column list of a SELECT statement,\na FROM,\nGROUP BY,\nHAVING,\nand / or ORDER BY clauses of a T - SQL statement.A Subquery can also be used as a parameter to a\nfunction call.Basically a subquery can be used anywhere an expression can be used."
},
{
"id": "21",
"q": "21. What's the difference between a primary key and a unique key?",
"answer": "ANSWER: Both primary key and unique key enforces uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only."
},
{
"id": "22",
"q": "22.What is UNIQUE KEY constraint? ",
"answer": "ANSWER: Sub-queries are often referred to as sub-selects, as they allow a SELECT statement to be executed arbitrarily within the body of another SQL statement. A sub-query is executed by enclosing it in a set of parentheses. Sub-queries are generally used to return a single row as an atomic value, though they may be used to compare values against multiple rows with the IN keyword.\nA subquery is a SELECT statement that is nested within another T - SQL statement.A subquery SELECT statement\nif executed independently of the T - SQL statement,\nin which it is nested,\nwill\nreturn a resultset.Meaning a subquery SELECT statement can standalone and is not depended on the statement in which it is nested.A subquery SELECT statement can\nreturn any number of values,\nand can be found in ,\nthe column list of a SELECT statement,\na FROM,\nGROUP BY,\nHAVING,\nand / or ORDER BY clauses of a T - SQL statement.A Subquery can also be used as a parameter to a\nfunction call.Basically a subquery can be used anywhere an expression can be used."
},
{
"id": "23",
"q": "What is 1 NF (Normal Form)?",
"answer": "The first normal form or 1NF is the first and the simplest type of normalization that can be implemented in a database.\nThe main aims of 1 NF are to:\n1. Eliminate duplicative columns from the same table.\n2. Create separate tables\nfor each group of related data and identify each row with a unique column(the primary key)."
}, {
"id": "24",
"q": "What is 2NF? ",
"answer": "A relation schema R is in 2 NF\nif it is in 1 NF and every non - prime attribute A in R is fully functionally dependent on primary key."
}, {
"id": "25",
"q": " What is 3NF? ",
"answer": "A relation is in third normal form\nif it is in Second Normal Form and there are no functional(transitive) dependencies between two(or more) non - primary key attributes."
}, {
"id": "26",
"q": " What is BCNF (Boyce-Codd Normal Form)?",
"answer": "A table is in Boyce-Codd normal form (BCNF) if and only if it is in 3NF and every determinant is a candidate key. "
}, {
"id": "27",
"q": "What is 4NF?",
"answer": "Fourth normal form requires that a table be BCNF and contain no multi-valued dependencies. "
}, {
"id": "28",
"q": "What is 5NF? ",
"answer": "A table is in fifth normal form (5NF) or Project-Join Normal Form (PJNF) if it is in 4NF and it cannot have a lossless decomposition into any number of smaller tables."
}, {
"id": "29",
"q": "What is a query?",
"answer": "A query with respect to DBMS relates to user commands that are used to interact with a data base."
}, {
"id": "30",
"q": "What is meant by query optimization? ",
"answer": "The phase that identifies an efficient execution plan for evaluating a query that has the least estimated cost is referred to as query optimization."
}, {
"id": "31",
"q": "What is an attribute? ",
"answer": "It is a particular property, which describes the entity."
}, {
"id": "32",
"q": "What is RDBMS?",
"answer": "Relational Data Base Management Systems(RDBMS) are database management systems that maintain data records and indices in tables."
}, {
"id": "33",
"q": "What’s difference between DBMS and RDBMS?",
"answer": "DBMS provides a systematic and organized way of storing, managing and retrieving from collection of logically related information.\nRDBMS also provides what DBMS provides but above that it provides relationship integrity."
}, {
"id": "34",
"q": "What is SQL?",
"answer": "SQL stands for Structured Query Language.\nSQL is an ANSI(American National Standards Institute) standard computer language\nfor accessing and manipulating database systems.\nSQL statements are used to retrieve and update data in a database."
}, {
"id": "35",
"q": " What is Stored Procedure? ",
"answer": "A stored procedure is a named group of SQL statements that have been previously created and stored in the server database."
}, {
"id": "36",
"q": "What is a view?",
"answer": "A view may be a subset of the database or it may contain virtual data that is derived from the database files but is not explicitly stored."
}, {
"id": "37",
"q": " What is Trigger?",
"answer": "A trigger is a SQL procedure that initiates an action when an event(INSERT, DELETE or UPDATE) occurs."
}, {
"id": "38",
"q": " What is Index?",
"answer": "An index is a physical structure containing pointers to the data. "
}, {
"id": "39",
"q": "What is extension and intension? ",
"answer": "Extension - It is the number of tuples present in a table at any instance.This is time dependent.\nIntension - It is a constant value that gives the name,\nstructure of table and the constraints laid on it."
}, {
"id": "40",
"q": "What is RDBMS KERNEL? ",
"answer": "Two important pieces of RDBMS architecture are the kernel,which is the software,and the data dictionary,which consists of the system - level data structures used by the kernel to manage the database."
}, {
"id": "41",
"q": " Name the sub-systems of a RDBMS? ",
"answer": "I / O,\nSecurity,\nLanguage Processing,\nProcess Control,\nStorage Management,\nLogging and Recovery,\nDistribution Control,\nTransaction Control,\nMemory Management,\nLock Management."
}, {
"id": "42",
"q": " How do you communicate with an RDBMS?",
"answer": "You communicate with an RDBMS using Structured Query Language (SQL) "
}, {
"id": "43",
"q": "Disadvantage in File Processing System?· ",
"answer": " Data redundancy & inconsistency.\nDifficult in accessing data.\nData isolation.\nData integrity.\nConcurrent access is not possible.\nSecurity Problems."
}, {
"id": "44",
"q": "What is VDL (View Definition Language)?",
"answer": "It specifies user views and their mappings to the conceptual schema."
}, {
"id": "45",
"q": " What is SDL (Storage Definition Language)?",
"answer": "This language is to specify the internal schema.This language may Specify the mapping between two schemas."
}, {
"id": "46",
"q": " Describe concurrency control?",
"answer": "Concurrency control is the process managing simultaneous operations against a database so that database integrity is no compromised.\nThere are two approaches to concurrency control.\nThe pessimistic approach involves locking and the optimistic approach involves versioning."
}, {
"id": "47",
"q": "What do you mean by atomicity and aggregation?",
"answer": "Atomicity-Atomicity states that database modifications must follow an “all or nothing” rule. Each transaction is said to be “atomic.” If one part of the transaction fails, the entire transaction fails.\nAggregation - A feature of the entity relationship model that allows a relationship set to participate in another relationship set.This is indicated on an ER diagram by drawing a dashed box around the aggregation."
}, {
"id": "47",
"q": " Describe the difference between homogeneous and heterogeneous distributed database? ",
"answer": "\nA homogenous database is one that uses the same DBMS at each node.A heterogeneous database is one that may have a different DBMS at each node."
}, {
"id": "48",
"q": "What is a distributed database?",
"answer": "\nA distributed database is a single logical database that is spread across more than one node or locations that are all connected via some communication link."
}, {
"id": "49",
"q": "Explain the difference between two and three-tier architectures? ",
"answer": "\nThree - tier architecture includes a client and two server layers.\nThe application code is stored on the application server and the database is stored on the database server.A two - tier architecture includes a client and one server layer.The database is stored on the database server."
}, {
"id": "50",
"q": "Briefly describe the three types of SQL commands? ",
"answer": "Data definition language commands are used to create,alter,and drop tables.Data manipulation commands are used to insert,modify,update,and query data in the database.Data control language commands help the DBA to control the database."
}, {
"id": "51",
"q": "List some of the properties of a relation?",
"answer": "Relations in a database have a unique name and no multivalued attributes exist.Each row is unique and each attribute within a relation has a unique name.The sequence of both columns and rows is irrelevant."
}, {
"id": "52",
"q": "Explain the differences between an intranet and an extranet?",
"answer": "An Internet database is accessible by everyone who has access to a Web site. An intranet database limits access to only people within a given organization."
}, {
"id": "53",
"q": "What is SQL Deadlock?",
"answer": "Deadlock is a unique situation in a multi user system that causes two or more users to wait indefinitely\nfor a locked resource."
}, {
"id": "54",
"q": "What is a Catalog?",
"answer": "A catalog is a table that contains the information such as structure of each file, the type and storage format of each data item and various constraints on the data .The information stored in the catalog is called Metadata. "
}, {
"id": "55",
"q": "What is data ware housing & OLAP? ",
"answer": "Data warehousing and OLAP(online analytical processing) systems are the techniques used in many companies to extract and analyze useful information from very large databasesfor decision making."
}, {
"id": "56",
"q": "Describe the three levels of data abstraction?",
"answer": "Physical level: The lowest level of abstraction describes how data are stored.\nLogical level: The next higher level of abstraction,\ndescribes what data are stored in database and what relationship among those data.\nView level: The highest level of abstraction describes only part of entire database."
}, {
"id": "57",
"q": "What is Data Independence? ",
"answer": "Data independence means that the application is independent of the storage structure and access strategy of data."
}, {
"id": "58",
"q": "How many types of relationship exist in database designing? ",
"answer": "There are three major relationship models: -\nOne - to - one\nOne - to - many\nMany - to - many"
}, {
"id": "59",
"q": "What is order by clause? ",
"answer": "ORDER BY clause helps to sort the data in either ascending order to descending"
}, {
"id": "60",
"q": "What is the use of DBCC commands? ",
"answer": "DBCC stands\nfor database consistency checker.We use these commands to check the consistency of the databases,\ni.e.,maintenance,\nvalidation task and status checks."
}, {
"id": "61",
"q": "What is Collation? ",
"answer": "Collation refers to a set of rules that determine how data is sorted and compared."
},
{
"id": "62",
"q": " What is difference between DELETE & TRUNCATE commands?",
"answer": "Delete command removes the rows from a table based on the condition that we provide with a WHERE clause.Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command."
},
{
"id": "63",
"q": " What is a transaction? ",
"answer": "A transaction is a logical unit of database processing that includes one or more database access operations."
},
{
"id": "64",
"q": "What is “transparent dbms”?",
"answer": "It is one,which keeps its physical structure hidden from user."
},
{
"id": "65",
"q": " What are the primitive operations common to all record management System?",
"answer": "Addition,\ndeletion and modification."
},
{
"id": "66",
"q": "Explain the differences between structured data and unstructured data.",
"answer": "Structured data are facts concerning objects and events.The most important structured data are numeric,\ncharacter,\nand dates.\nStructured data are stored in tabular form.Unstructured data are multimedia data such as documents,\nphotographs,\nmaps,\nimages,\nsound,\nand video clips.Unstructured data are most commonly found on Web servers and Web - enabled databases."
},
{
"id": "67",
"q": "What are the major functions of the database administrator?",
"answer": "Managing database structure,\ncontrolling concurrent processing,\nmanaging processing rights and responsibilities,\ndeveloping database security,\nproviding\nfor database recovery,\nmanaging the DBMS and maintaining the data repository."
},
{
"id": "68",
"q": " What is a dependency graph?",
"answer": "A dependency graph is a diagram that is used to portray the connections between database elements. "
},
{
"id": "69",
"q": " Explain the difference between an exclusive lock and a shared lock?",
"answer": "An exclusive lock prohibits other users from reading the locked resource; a shared lock allows other users to read the locked resource, but they cannot update it. "
},
{
"id": "70",
"q": " Explain the paradigm mismatch between SQL and application programming languages.",
"answer": "SQL statements return a set of rows, while an application program works on one row at a time. To resolve this mismatch the results of SQL statements are processed as pseudofiles, using a cursor or pointer to specify which row is being processed. "
},
{
"id": "71",
"q": "Name four applications for triggers.?",
"answer": "(1) Providing default values,\n(2) enforcing data constraints,\n(3) Updating views and(4) enforcing referential integrity"
},
{
"id": "72",
"q": "What are the advantages of using stored procedures?",
"answer": " The advantages of stored procedures are \n(1) greater security, \n(2) decreased network traffic, \n(3) the fact that SQL can be optimized and\n(4) code sharing which leads to less work,standardized processing,and specialization among developers."
},
{
"id": "73",
"q": "Explain the difference between attributes and identifiers.",
"answer": "Entities have attributes. Attributes are properties that describe the entity's characteristics. Entity instances have identifiers.Identifiers are attributes that name,or identify,entity instances."
},
{
"id": "74",
"q": "What is Enterprise Resource Planning (ERP), and what kind of a database is used in an ERP application?",
"answer": "Enterprise Resource Planning (ERP) is an information system used in manufacturing companies and includes sales, inventory, production planning, purchasingand other business functions.An ERP system typically uses a multiuser database."
},
{
"id": "75",
"q": " Describe the difference between embedded and dynamic SQL?",
"answer": "Embedded SQL is the process of including hard coded SQL statements.\nThese statements do not change unless the source code is modified.\nDynamic SQL is the process of generating SQL on the fly.\nThe statements generated do not have to be the same each time."
},
{
"id": "76",
"q": "Explain a join between tables\nA join allows tables to be linked to other tables when a relationship between the tables exists.",
"answer": "The relationships are established by using a common column in the tables and often uses the primary/foreign key relationship."
},
{
"id": "77",
"q": " Describe a subquery.",
"answer": "A subquery is a query that is composed of two queries.\nThe first query(inner query) is within the WHERE clause of the other query (outer query)."
},
{
"id": "78",
"q": "Compare a hierarchical and network database model?",
"answer": "The hierarchical model is a top-down structure where each parent may have many children but each child can have only one parent.\nThis model supports one - to - one and one - to - many relationships.\nThe network model can be much more flexible than the hierarchical model since each parent can have multiple children but each child can also have multiple parents.\nThis model supports one - to - one,\none - to - many,\nand many - to - many relationships."
},
{
"id": "79",
"q": "Explain the difference between a dynamic and materialized view.",
"answer": "A dynamic view may be created every time that a specific view is requested by a user.\nA materialized view is created and or updated infrequently and it must be synchronized with its associated base table(s)."
},
{
"id": "80",
"q": "Explain what needs to happen to convert a relation to third normal form.",
"answer": "First you must verify that a relation is in both first normal form and second normal form.If the relation is not,\nyou must convert into second normal form.After a relation is in second normal form,\nyou must remove all transitive dependencies."
}, {
"id": "81",
"q": "A unique primary index is unique and is used to find and store a row.",
"answer": "A nonunique primary index is not unique and is used to find a row but also where to store a row(based on its unique primary index).\nA unique secondary index is unique\nfor each row and used to find table rows.A nonunique secondary index is not unique and used to find table rows."
}, {
"id": "82",
"q": " Explain minimum and maximum cardinality?",
"answer": "Minimum cardinality is the minimum number of instances of an entity that can be associated with each instance of another entity. Maximum cardinality is the maximum number of instances of an entity that can be associated with each instance of another entity."
}, {
"id": "83",
"q": " What is deadlock? How can it be avoided?How can it be resolved once it occurs??",
"answer": "Deadlock occurs when two transactions are each waiting on a resource that the other transaction holds.\nDeadlock can be prevented by requiring transactions to acquire all locks at the same time;\nonce it occurs,\nthe only way to cure it is to abort one of the transactions and back out of partially completed work."
}, {
"id": "84",
"q": "Explain what we mean by an ACID transaction.",
"answer": "An ACID transaction is one that is atomic, consistent, isolated, and durable. Durable means that database changes are permanent.\nConsistency can mean either statement level or transaction level consistency.\nWith transaction level consistency,\na transaction may not see its own changes.Atomic means it is performed as a unit."
}, {
"id": "85",
"q": "Under what conditions should indexes be used?",
"answer": "Indexes can be created to enforce uniqueness, to facilitate sorting, and to enable fast retrieval by column values.\nA good candidate\nfor an index is a column that is frequently used with equal conditions in WHERE clauses."
}, {
"id": "86",
"q": "What is difference between SQL and SQL SERVER?",
"answer": "SQL is a language that provides an interface to RDBMS,\ndeveloped by IBM.SQL SERVER is a RDBMS just like Oracle,\nDB2."
}, {
"id": "87",
"q": "What is Specialization?",
"answer": "It is the process of defining a set of subclasses of an entity type where each subclass contain all\nthe attributes and relationships of the parent entity and may have additional attributes and relationships which are specific to itself."
}, {
"id": "88",
"q": " What is generalization?",
"answer": "Proactive Update: The updates that are applied to database before it becomes effective in real world.\nRetroactive Update: The updates that are applied to database after it becomes effective in real world.\nSimultaneous Update: The updates that are applied to database at the same time when it becomes effective in real world."
}, {
"id": "89",
"q": "What is a Phantom Deadlock?",
"answer": "In distributed deadlock detection,the delay in propagating local information might cause the deadlock detection algorithms to identify deadlocks that do not really exist.Such situations are called phantom deadlocks and they lead to unnecessary aborts."
}
]
}
"iq": [{"id": "1",
"q": " What is database?",
"answer":"A database is a collection of information that is organized.So that it can easily be accessed,managed,and updated."},
{"id": "2", "q": "What is DBMS?", "answer":"DBMS stands for Database Management System.It is a collection of programs that enables user to create and maintain a database."
},
{
"id": "3",
"q": " What is a Database system?",
"answer": "The database and DBMS software together is called as Database system."
},
{
"id": "4",
"q": " What are the advantages of DBMS?",
"answer": "I. Redundancy is controlled.\nII. Providing multiple user interfaces.\n III.Providing backup and recovery\n IV.Unauthorized access is restricted.\n V. Enforcing integrity constraints."
},
{
"id": "5",
"q": "What is normalization?",
"answer": "It is a process of analysing the given relation schemas based on their Functional Dependencies(FDs) and primary key to achieve the properties(1).Minimizing redundancy,\n(2).Minimizing insertion,\ndeletion and update anomalies."
},
{
"id": "6",
"q": " What is Data Model?",
"answer": " A collection of conceptual tools for describing data, data relationships data semantics and constraints."
},
{
"id": "7",
"q": "What is E-R model?",
"answer": "This data model is based on real world that consists of basic objects called entities and of relationship among these objects. Entities are described in a database by a set of attributes. "
},
{
"id": "8",
"q": "What is Object Oriented model?",
"answer": "This model is based on collection of objects.An object contains values stored in instance variables with in the object.An object also contains bodies of code that operate on the object.These bodies of code are called methods.Objects that contain same types of values and the same methods are grouped together into classes."
},
{
"id": "9",
"q": "What is an Entity?",
"answer": "An entity is a thing or object of importance about which data must be captured."
},
{
"id": "10",
"q": "What is DDL (Data Definition Language)?",
"answer": "A data base schema is specifies by a set of definitions expressed by a special language called DDL."
},
{
"id": "11",
"q": "What is DML (Data Manipulation Language)?",
"answer": "This language that enable user to access or manipulate data as organised by appropriate data model.Procedural DML or Low level: DML requires a user to specify what data are needed and how to get those data.Non - Procedural DML or High level: DML requires a user to specify what data are needed without specifying how to get those data"
},
{
"id": "12",
"q": " What is DML Compiler?",
"answer": "It translates DML statements in a query language into low-level instruction that the query evaluation engine can understand. "
},
{
"id": "13",
"q": "What is Query evaluation engine?",
"answer": "It executes low-level instruction generated by compiler."
},
{
"id": "14",
"q": "What is Functional Dependency?",
"answer": "Functional Dependency is the starting point of normalization.\nFunctional Dependency exists when a relation between two attributes allows you to uniquely determine the corresponding attribute’ s value."
},
{
"id": "15",
"q": "15. What is OLTP (Online Transaction Processing)?",
"answer": "ANSWER: In OLTP - online transaction processing systems relational database design use the discipline of data modeling and\ngenerally follow the Codd rules of data normalization in order to ensure absolute data integrity.Using these rules\ncomplex information is broken down into its most simple structures(a table) where all of the individual atomic level\nelements relate to each other and satisfy the normalization rules."
},
{
"id": "16",
"q": "16. What is PRIMARY KEY?",
"answer": "ANSWER: A PRIMARY KEY constraint is a unique identifier for a row within a database table. Every table should have a primary keyconstraint to uniquely identify each row and only one primary key constraint can be createdfor each table.The primarykey constraints are used to enforce entity integrity."
},
{
"id": "17",
"q": "17. What does SQL Stands for?",
"answer": "SQL stands for Structured Query Language. For more info visit What is SQL?"
},
{
"id": "18",
"q": "18.What is the difference between JOIN and UNION?",
"answer": "ANSWER: SQL JOIN allows us to “lookup” records on other table based on the given conditions between two tables. For example, if we have the department ID of each employee, then we can use this department ID of the employee table to join with the department ID of department table to lookup department names.\nUNION operation allows us to add 2 similar data sets to create resulting data set that contains all the data from the source data sets.Union does not require any condition\nfor joining.For example,\nif you have 2 employee tables with same structure,\nyou can UNION them to create one result set that will contain all the employees from both of the tables.\nSELECT * FROM EMP1\nUNION\nSELECT * FROM EMP2;"
},
{
"id": "19",
"q": "19. What are different normalization forms?",
"answer": "ANSWER: 1NF: Eliminate Repeating Groups Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain.\n2 NF: Eliminate Redundant Data If an attribute depends on only part of a multi - valued key,\nremove it to a separate table.\n3 NF: Eliminate Columns Not Dependent On Key If attributes do not contribute to a description of the key, remove them to a separate table.All attributes must be directly dependent on the primary key.\nBCNF: Boyce - Codd Normal Form If there are non - trivial dependencies between candidate key attributes,\nseparate them out into distinct tables.\n4 NF: Isolate Independent Multiple Relationships No table may contain two or more 1: n or n: m relationships that are not directly related.\n5 NF: Isolate Semantically Related Multiple Relationships There may be practical constrains on information that justify separating logically related many - to - many relationships.\nONF: Optimal Normal Form A model limited to only simple(elemental) facts,\nas expressed in Object Role Model notation.\nDKNF: Domain - Key Normal Form A model free from all modification anomalies is said to be in DKNF.\nRemember,\nthese normalization guidelines are cumulative.For a database to be in 3 NF,\nit must first fulfill all the criteria of a 2 NF and 1 NF database."
},
{
"id": "20",
"q": "20. What is sub-query? Explain properties of sub-query? ",
"answer": "ANSWER: Sub-queries are often referred to as sub-selects, as they allow a SELECT statement to be executed arbitrarily within the body of another SQL statement. A sub-query is executed by enclosing it in a set of parentheses. Sub-queries are generally used to return a single row as an atomic value, though they may be used to compare values against multiple rows with the IN keyword.\nA subquery is a SELECT statement that is nested within another T - SQL statement.A subquery SELECT statement\nif executed independently of the T - SQL statement,\nin which it is nested,\nwill\nreturn a resultset.Meaning a subquery SELECT statement can standalone and is not depended on the statement in which it is nested.A subquery SELECT statement can\nreturn any number of values,\nand can be found in ,\nthe column list of a SELECT statement,\na FROM,\nGROUP BY,\nHAVING,\nand / or ORDER BY clauses of a T - SQL statement.A Subquery can also be used as a parameter to a\nfunction call.Basically a subquery can be used anywhere an expression can be used."
},
{
"id": "21",
"q": "21. What's the difference between a primary key and a unique key?",
"answer": "ANSWER: Both primary key and unique key enforces uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only."
},
{
"id": "22",
"q": "22.What is UNIQUE KEY constraint? ",
"answer": "ANSWER: Sub-queries are often referred to as sub-selects, as they allow a SELECT statement to be executed arbitrarily within the body of another SQL statement. A sub-query is executed by enclosing it in a set of parentheses. Sub-queries are generally used to return a single row as an atomic value, though they may be used to compare values against multiple rows with the IN keyword.\nA subquery is a SELECT statement that is nested within another T - SQL statement.A subquery SELECT statement\nif executed independently of the T - SQL statement,\nin which it is nested,\nwill\nreturn a resultset.Meaning a subquery SELECT statement can standalone and is not depended on the statement in which it is nested.A subquery SELECT statement can\nreturn any number of values,\nand can be found in ,\nthe column list of a SELECT statement,\na FROM,\nGROUP BY,\nHAVING,\nand / or ORDER BY clauses of a T - SQL statement.A Subquery can also be used as a parameter to a\nfunction call.Basically a subquery can be used anywhere an expression can be used."
},
{
"id": "23",
"q": "What is 1 NF (Normal Form)?",
"answer": "The first normal form or 1NF is the first and the simplest type of normalization that can be implemented in a database.\nThe main aims of 1 NF are to:\n1. Eliminate duplicative columns from the same table.\n2. Create separate tables\nfor each group of related data and identify each row with a unique column(the primary key)."
}, {
"id": "24",
"q": "What is 2NF? ",
"answer": "A relation schema R is in 2 NF\nif it is in 1 NF and every non - prime attribute A in R is fully functionally dependent on primary key."
}, {
"id": "25",
"q": " What is 3NF? ",
"answer": "A relation is in third normal form\nif it is in Second Normal Form and there are no functional(transitive) dependencies between two(or more) non - primary key attributes."
}, {
"id": "26",
"q": " What is BCNF (Boyce-Codd Normal Form)?",
"answer": "A table is in Boyce-Codd normal form (BCNF) if and only if it is in 3NF and every determinant is a candidate key. "
}, {
"id": "27",
"q": "What is 4NF?",
"answer": "Fourth normal form requires that a table be BCNF and contain no multi-valued dependencies. "
}, {
"id": "28",
"q": "What is 5NF? ",
"answer": "A table is in fifth normal form (5NF) or Project-Join Normal Form (PJNF) if it is in 4NF and it cannot have a lossless decomposition into any number of smaller tables."
}, {
"id": "29",
"q": "What is a query?",
"answer": "A query with respect to DBMS relates to user commands that are used to interact with a data base."
}, {
"id": "30",
"q": "What is meant by query optimization? ",
"answer": "The phase that identifies an efficient execution plan for evaluating a query that has the least estimated cost is referred to as query optimization."
}, {
"id": "31",
"q": "What is an attribute? ",
"answer": "It is a particular property, which describes the entity."
}, {
"id": "32",
"q": "What is RDBMS?",
"answer": "Relational Data Base Management Systems(RDBMS) are database management systems that maintain data records and indices in tables."
}, {
"id": "33",
"q": "What’s difference between DBMS and RDBMS?",
"answer": "DBMS provides a systematic and organized way of storing, managing and retrieving from collection of logically related information.\nRDBMS also provides what DBMS provides but above that it provides relationship integrity."
}, {
"id": "34",
"q": "What is SQL?",
"answer": "SQL stands for Structured Query Language.\nSQL is an ANSI(American National Standards Institute) standard computer language\nfor accessing and manipulating database systems.\nSQL statements are used to retrieve and update data in a database."
}, {
"id": "35",
"q": " What is Stored Procedure? ",
"answer": "A stored procedure is a named group of SQL statements that have been previously created and stored in the server database."
}, {
"id": "36",
"q": "What is a view?",
"answer": "A view may be a subset of the database or it may contain virtual data that is derived from the database files but is not explicitly stored."
}, {
"id": "37",
"q": " What is Trigger?",
"answer": "A trigger is a SQL procedure that initiates an action when an event(INSERT, DELETE or UPDATE) occurs."
}, {
"id": "38",
"q": " What is Index?",
"answer": "An index is a physical structure containing pointers to the data. "
}, {
"id": "39",
"q": "What is extension and intension? ",
"answer": "Extension - It is the number of tuples present in a table at any instance.This is time dependent.\nIntension - It is a constant value that gives the name,\nstructure of table and the constraints laid on it."
}, {
"id": "40",
"q": "What is RDBMS KERNEL? ",
"answer": "Two important pieces of RDBMS architecture are the kernel,which is the software,and the data dictionary,which consists of the system - level data structures used by the kernel to manage the database."
}, {
"id": "41",
"q": " Name the sub-systems of a RDBMS? ",
"answer": "I / O,\nSecurity,\nLanguage Processing,\nProcess Control,\nStorage Management,\nLogging and Recovery,\nDistribution Control,\nTransaction Control,\nMemory Management,\nLock Management."
}, {
"id": "42",
"q": " How do you communicate with an RDBMS?",
"answer": "You communicate with an RDBMS using Structured Query Language (SQL) "
}, {
"id": "43",
"q": "Disadvantage in File Processing System?· ",
"answer": " Data redundancy & inconsistency.\nDifficult in accessing data.\nData isolation.\nData integrity.\nConcurrent access is not possible.\nSecurity Problems."
}, {
"id": "44",
"q": "What is VDL (View Definition Language)?",
"answer": "It specifies user views and their mappings to the conceptual schema."
}, {
"id": "45",
"q": " What is SDL (Storage Definition Language)?",
"answer": "This language is to specify the internal schema.This language may Specify the mapping between two schemas."
}, {
"id": "46",
"q": " Describe concurrency control?",
"answer": "Concurrency control is the process managing simultaneous operations against a database so that database integrity is no compromised.\nThere are two approaches to concurrency control.\nThe pessimistic approach involves locking and the optimistic approach involves versioning."
}, {
"id": "47",
"q": "What do you mean by atomicity and aggregation?",
"answer": "Atomicity-Atomicity states that database modifications must follow an “all or nothing” rule. Each transaction is said to be “atomic.” If one part of the transaction fails, the entire transaction fails.\nAggregation - A feature of the entity relationship model that allows a relationship set to participate in another relationship set.This is indicated on an ER diagram by drawing a dashed box around the aggregation."
}, {
"id": "47",
"q": " Describe the difference between homogeneous and heterogeneous distributed database? ",
"answer": "\nA homogenous database is one that uses the same DBMS at each node.A heterogeneous database is one that may have a different DBMS at each node."
}, {
"id": "48",
"q": "What is a distributed database?",
"answer": "\nA distributed database is a single logical database that is spread across more than one node or locations that are all connected via some communication link."
}, {
"id": "49",
"q": "Explain the difference between two and three-tier architectures? ",
"answer": "\nThree - tier architecture includes a client and two server layers.\nThe application code is stored on the application server and the database is stored on the database server.A two - tier architecture includes a client and one server layer.The database is stored on the database server."
}, {
"id": "50",
"q": "Briefly describe the three types of SQL commands? ",
"answer": "Data definition language commands are used to create,alter,and drop tables.Data manipulation commands are used to insert,modify,update,and query data in the database.Data control language commands help the DBA to control the database."
}, {
"id": "51",
"q": "List some of the properties of a relation?",
"answer": "Relations in a database have a unique name and no multivalued attributes exist.Each row is unique and each attribute within a relation has a unique name.The sequence of both columns and rows is irrelevant."
}, {
"id": "52",
"q": "Explain the differences between an intranet and an extranet?",
"answer": "An Internet database is accessible by everyone who has access to a Web site. An intranet database limits access to only people within a given organization."
}, {
"id": "53",
"q": "What is SQL Deadlock?",
"answer": "Deadlock is a unique situation in a multi user system that causes two or more users to wait indefinitely\nfor a locked resource."
}, {
"id": "54",
"q": "What is a Catalog?",
"answer": "A catalog is a table that contains the information such as structure of each file, the type and storage format of each data item and various constraints on the data .The information stored in the catalog is called Metadata. "
}, {
"id": "55",
"q": "What is data ware housing & OLAP? ",
"answer": "Data warehousing and OLAP(online analytical processing) systems are the techniques used in many companies to extract and analyze useful information from very large databasesfor decision making."
}, {
"id": "56",
"q": "Describe the three levels of data abstraction?",
"answer": "Physical level: The lowest level of abstraction describes how data are stored.\nLogical level: The next higher level of abstraction,\ndescribes what data are stored in database and what relationship among those data.\nView level: The highest level of abstraction describes only part of entire database."
}, {
"id": "57",
"q": "What is Data Independence? ",
"answer": "Data independence means that the application is independent of the storage structure and access strategy of data."
}, {
"id": "58",
"q": "How many types of relationship exist in database designing? ",
"answer": "There are three major relationship models: -\nOne - to - one\nOne - to - many\nMany - to - many"
}, {
"id": "59",
"q": "What is order by clause? ",
"answer": "ORDER BY clause helps to sort the data in either ascending order to descending"
}, {
"id": "60",
"q": "What is the use of DBCC commands? ",
"answer": "DBCC stands\nfor database consistency checker.We use these commands to check the consistency of the databases,\ni.e.,maintenance,\nvalidation task and status checks."
}, {
"id": "61",
"q": "What is Collation? ",
"answer": "Collation refers to a set of rules that determine how data is sorted and compared."
},
{
"id": "62",
"q": " What is difference between DELETE & TRUNCATE commands?",
"answer": "Delete command removes the rows from a table based on the condition that we provide with a WHERE clause.Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command."
},
{
"id": "63",
"q": " What is a transaction? ",
"answer": "A transaction is a logical unit of database processing that includes one or more database access operations."
},
{
"id": "64",
"q": "What is “transparent dbms”?",
"answer": "It is one,which keeps its physical structure hidden from user."
},
{
"id": "65",
"q": " What are the primitive operations common to all record management System?",
"answer": "Addition,\ndeletion and modification."
},
{
"id": "66",
"q": "Explain the differences between structured data and unstructured data.",
"answer": "Structured data are facts concerning objects and events.The most important structured data are numeric,\ncharacter,\nand dates.\nStructured data are stored in tabular form.Unstructured data are multimedia data such as documents,\nphotographs,\nmaps,\nimages,\nsound,\nand video clips.Unstructured data are most commonly found on Web servers and Web - enabled databases."
},
{
"id": "67",
"q": "What are the major functions of the database administrator?",
"answer": "Managing database structure,\ncontrolling concurrent processing,\nmanaging processing rights and responsibilities,\ndeveloping database security,\nproviding\nfor database recovery,\nmanaging the DBMS and maintaining the data repository."
},
{
"id": "68",
"q": " What is a dependency graph?",
"answer": "A dependency graph is a diagram that is used to portray the connections between database elements. "
},
{
"id": "69",
"q": " Explain the difference between an exclusive lock and a shared lock?",
"answer": "An exclusive lock prohibits other users from reading the locked resource; a shared lock allows other users to read the locked resource, but they cannot update it. "
},
{
"id": "70",
"q": " Explain the paradigm mismatch between SQL and application programming languages.",
"answer": "SQL statements return a set of rows, while an application program works on one row at a time. To resolve this mismatch the results of SQL statements are processed as pseudofiles, using a cursor or pointer to specify which row is being processed. "
},
{
"id": "71",
"q": "Name four applications for triggers.?",
"answer": "(1) Providing default values,\n(2) enforcing data constraints,\n(3) Updating views and(4) enforcing referential integrity"
},
{
"id": "72",
"q": "What are the advantages of using stored procedures?",
"answer": " The advantages of stored procedures are \n(1) greater security, \n(2) decreased network traffic, \n(3) the fact that SQL can be optimized and\n(4) code sharing which leads to less work,standardized processing,and specialization among developers."
},
{
"id": "73",
"q": "Explain the difference between attributes and identifiers.",
"answer": "Entities have attributes. Attributes are properties that describe the entity's characteristics. Entity instances have identifiers.Identifiers are attributes that name,or identify,entity instances."
},
{
"id": "74",
"q": "What is Enterprise Resource Planning (ERP), and what kind of a database is used in an ERP application?",
"answer": "Enterprise Resource Planning (ERP) is an information system used in manufacturing companies and includes sales, inventory, production planning, purchasingand other business functions.An ERP system typically uses a multiuser database."
},
{
"id": "75",
"q": " Describe the difference between embedded and dynamic SQL?",
"answer": "Embedded SQL is the process of including hard coded SQL statements.\nThese statements do not change unless the source code is modified.\nDynamic SQL is the process of generating SQL on the fly.\nThe statements generated do not have to be the same each time."
},
{
"id": "76",
"q": "Explain a join between tables\nA join allows tables to be linked to other tables when a relationship between the tables exists.",
"answer": "The relationships are established by using a common column in the tables and often uses the primary/foreign key relationship."
},
{
"id": "77",
"q": " Describe a subquery.",
"answer": "A subquery is a query that is composed of two queries.\nThe first query(inner query) is within the WHERE clause of the other query (outer query)."
},
{
"id": "78",
"q": "Compare a hierarchical and network database model?",
"answer": "The hierarchical model is a top-down structure where each parent may have many children but each child can have only one parent.\nThis model supports one - to - one and one - to - many relationships.\nThe network model can be much more flexible than the hierarchical model since each parent can have multiple children but each child can also have multiple parents.\nThis model supports one - to - one,\none - to - many,\nand many - to - many relationships."
},
{
"id": "79",
"q": "Explain the difference between a dynamic and materialized view.",
"answer": "A dynamic view may be created every time that a specific view is requested by a user.\nA materialized view is created and or updated infrequently and it must be synchronized with its associated base table(s)."
},
{
"id": "80",
"q": "Explain what needs to happen to convert a relation to third normal form.",
"answer": "First you must verify that a relation is in both first normal form and second normal form.If the relation is not,\nyou must convert into second normal form.After a relation is in second normal form,\nyou must remove all transitive dependencies."
}, {
"id": "81",
"q": "A unique primary index is unique and is used to find and store a row.",
"answer": "A nonunique primary index is not unique and is used to find a row but also where to store a row(based on its unique primary index).\nA unique secondary index is unique\nfor each row and used to find table rows.A nonunique secondary index is not unique and used to find table rows."
}, {
"id": "82",
"q": " Explain minimum and maximum cardinality?",
"answer": "Minimum cardinality is the minimum number of instances of an entity that can be associated with each instance of another entity. Maximum cardinality is the maximum number of instances of an entity that can be associated with each instance of another entity."
}, {
"id": "83",
"q": " What is deadlock? How can it be avoided?How can it be resolved once it occurs??",
"answer": "Deadlock occurs when two transactions are each waiting on a resource that the other transaction holds.\nDeadlock can be prevented by requiring transactions to acquire all locks at the same time;\nonce it occurs,\nthe only way to cure it is to abort one of the transactions and back out of partially completed work."
}, {
"id": "84",
"q": "Explain what we mean by an ACID transaction.",
"answer": "An ACID transaction is one that is atomic, consistent, isolated, and durable. Durable means that database changes are permanent.\nConsistency can mean either statement level or transaction level consistency.\nWith transaction level consistency,\na transaction may not see its own changes.Atomic means it is performed as a unit."
}, {
"id": "85",
"q": "Under what conditions should indexes be used?",
"answer": "Indexes can be created to enforce uniqueness, to facilitate sorting, and to enable fast retrieval by column values.\nA good candidate\nfor an index is a column that is frequently used with equal conditions in WHERE clauses."
}, {
"id": "86",
"q": "What is difference between SQL and SQL SERVER?",
"answer": "SQL is a language that provides an interface to RDBMS,\ndeveloped by IBM.SQL SERVER is a RDBMS just like Oracle,\nDB2."
}, {
"id": "87",
"q": "What is Specialization?",
"answer": "It is the process of defining a set of subclasses of an entity type where each subclass contain all\nthe attributes and relationships of the parent entity and may have additional attributes and relationships which are specific to itself."
}, {
"id": "88",
"q": " What is generalization?",
"answer": "Proactive Update: The updates that are applied to database before it becomes effective in real world.\nRetroactive Update: The updates that are applied to database after it becomes effective in real world.\nSimultaneous Update: The updates that are applied to database at the same time when it becomes effective in real world."
}, {
"id": "89",
"q": "What is a Phantom Deadlock?",
"answer": "In distributed deadlock detection,the delay in propagating local information might cause the deadlock detection algorithms to identify deadlocks that do not really exist.Such situations are called phantom deadlocks and they lead to unnecessary aborts."
}
]
}