Tuesday, May 22, 2012
0
1. What are data and information, and how are they related in a database?
Ans:- Data is recorded facts and figures, and information is knowledge derived from data. A database stores data in such a way that information can be created.
2. What is Enterprise Resource Planning (ERP), and what kind of a database is used in an ERP application?
Ans:- Enterprise Resource Planning (ERP) is an information system used in manufacturing companies and includes sales, inventory, production planning, purchasing and other business functions. An ERP system typically uses a multiuser database.
3. What is a DBMS?
Ans:- DBMS stands for Database Management System. A DBMS receives requests from applications and translates those requests into actions on a specific database. A DBMS processes SQL statements or uses other functionality to create, process and administer databases.
4. Why is a database considered to be "self-describing"?
Ans:- In addition to the users' data, a database contains a description of its own structure. This descriptive data is called "metadata."
5. Who is E.F. Codd, and why is he significant in the development of modern database systems?
Ans:- While working at IBM, E.F. Codd created the relational database model. A paper he published in 1970 presented his ideas to the world at large. His work is the foundation for most of the DBMSs currently in use, and thus forms the basis for database systems as we know and use them today.
6. What is SQL, and why is it important?
Ans:- SQL stands for Structured Query Language, and is the most important data processing language in use today. It is not a complete programming language like Java or C#, but a data sublanguage used for creating and processing database data and metadata. All DBMS products today use SQL.
7. Write an SQL SELECT statement to display all the columns of the STUDENT table but only those rows where the Grade column is greater than or equal to 90.
Ans:- SELECT * FROM STUDENT WHERE Grade >= 90;
8. Name and briefly describe the five SQL built-in functions.
Ans:- COUNT: computes the number of rows in a table. SUM: totals numeric columns. AVG: computes the average value. MAX: obtains the maximum value of a column in a table. MIN: obtains the minimum value of a column in a table.
9. Write an SQL SELECT statement to count the number of rows in STUDENT table and display the result with the label NumStudents.
Ans:- SELECT COUNT(*) AS NumStudents FROM STUDENT;
10. What is an SQL subquery?
Ans:- An SQL subquery is a means of querying two or more tables at the same time. The subquery itself is an SQL SELECT statement contained within the WHERE clause of another SQL SELECT statement, and separated by being enclosed in parenthesis. Some subqueries have equivalent SQL join structures, but correlated subqueries cannot be duplicated by a join..
11. Discuss the alternative terminology that is used in the relational model.
Ans:- Relations are also called tables, and sometimes by the older data processing term files. A row is known as a tuple in the relational model, but may also be referred to as a record. Finally, relational model attributes are known as table columns and sometimes as fields.
12. Why are functional dependencies not equations?
Ans:- Equations deal with numerical relationships. A functional dependency deals with the existence of a determinant relationship between attributes, regardless of whether or not there is a numerical relationship between them. Thus, if we know that there is no hot water every Wednesday, No-Hot-Water is functionally dependent on Wednesday. So, if we know it is Wednesday, then we know we will have No-Hot-Water. This is a functional dependency, but not an equation.
13. What is a foreign key, and what is it used for?
Ans:- A foreign key is used to establish relationships among relations in the relational model. Technically, a foreign key is a column (or columns) appearing in one relation that is (are) the primary key of another table. Although there may be exceptions, the values in the foreign key columns usually must correspond to values existing in the set of primary key values. This correspondence requirement is created in a database using a referential integrity constraint on the foreign key.
14. What are insertion and deletion anomalies?
Ans:- A deletion anomaly occurs when, by deleting the facts about one entity, we inadvertently delete facts about another entity; with one deletion, we lose facts about two entities. For example, if we delete the tuple for Student 001289 from a table, we may lose not only the fact that Student 001289 is in Pierce Hall, but also the fact that he has $200 left in his security deposit. An insertion anomaly happens when we encounter the restriction that we cannot insert a fact about one entity until we have an additional fact about another entity. For example, we want to store the fact that the security deposit for Pierce Hall is $300, but we cannot enter this data into the Student relation until a student registers for Pierce Hall.
15. What does it mean when we say that a relation is in Boyce-Codd Normal Form (BCNF)?
Ans:- A relation is in BCNF when every determinant in the relation is a candidate key. This means that any possible primary key can determine all other attributes in the relation. Attributes may not be determined by non-candidate key attributes or part of a composite candidate key. Thus it is said "I swear to construct my tables so that all nonkey columns are dependent on the key, the whole key and nothing but the key, so help me Codd!"
16. You have been given a set of tables with data and asked to create a new database to store them. When you examine the data values in the tables, what are you looking for?
Ans:-
(1) Multivalued dependencies,
(2) Functional dependencies,
(3) Candidate keys,
(4) Primary keys and
(5) Foreign keys.
17. Why do normalized tables require more complex SQL when SQL statements are used in application programs?
Ans:- Tables that are normalized contain data that has been distributed among the tables, but which may need to be recombined to answer queries from an application. To recombine the data, the programmer will have to use subqueries and/or joins. These SQL structures are more complex to write than a simple SELECT statement.
18. What is the multivalue, multicolumn problem? Include an example not used in the text.
Ans:- The multivalue, multicolumn problem occurs when a table is designed to include multiple columns that hold variations of one type of attribute data. One example is where boat owners have the names of their boats stored as BOAT_01, BOAT_02 and BOAT_03.
19. Why is the multivalue, multicolumn problem another form of the multivalued dependency problem?
Ans:- Both problems try to store multiple values on an attribute in a table. In the multivalue, multiplecolumn problem, the multiple values are stored in different columns. In the mutlivalued dependency problem the multiple values are stored in different rows. In both cases, the solution is the same: store the multiple values in a separate table.
20. What is the inconsistent values problem? Include an example not used in the text.
Ans:- The inconsistent values problem occurs when different users or data sources use slightly different forms of the same data value. One example is where automobiles are specified as "Ford, 2-door, Red" in one cell and "Red Ford 2-door" in another.
Database Management System Interview Questions
Database Management System Interview Questions
Following are the Database Management System Interview Questions presenting here are collected from various students, professors, websites.
Following are the Database Management System Interview Questions presenting here are collected from various students, professors, websites.
1. What are data and information, and how are they related in a database?
Ans:- Data is recorded facts and figures, and information is knowledge derived from data. A database stores data in such a way that information can be created.
2. What is Enterprise Resource Planning (ERP), and what kind of a database is used in an ERP application?
Ans:- Enterprise Resource Planning (ERP) is an information system used in manufacturing companies and includes sales, inventory, production planning, purchasing and other business functions. An ERP system typically uses a multiuser database.
3. What is a DBMS?
Ans:- DBMS stands for Database Management System. A DBMS receives requests from applications and translates those requests into actions on a specific database. A DBMS processes SQL statements or uses other functionality to create, process and administer databases.
4. Why is a database considered to be "self-describing"?
Ans:- In addition to the users' data, a database contains a description of its own structure. This descriptive data is called "metadata."
5. Who is E.F. Codd, and why is he significant in the development of modern database systems?
Ans:- While working at IBM, E.F. Codd created the relational database model. A paper he published in 1970 presented his ideas to the world at large. His work is the foundation for most of the DBMSs currently in use, and thus forms the basis for database systems as we know and use them today.
6. What is SQL, and why is it important?
Ans:- SQL stands for Structured Query Language, and is the most important data processing language in use today. It is not a complete programming language like Java or C#, but a data sublanguage used for creating and processing database data and metadata. All DBMS products today use SQL.
7. Write an SQL SELECT statement to display all the columns of the STUDENT table but only those rows where the Grade column is greater than or equal to 90.
Ans:- SELECT * FROM STUDENT WHERE Grade >= 90;
8. Name and briefly describe the five SQL built-in functions.
Ans:- COUNT: computes the number of rows in a table. SUM: totals numeric columns. AVG: computes the average value. MAX: obtains the maximum value of a column in a table. MIN: obtains the minimum value of a column in a table.
9. Write an SQL SELECT statement to count the number of rows in STUDENT table and display the result with the label NumStudents.
Ans:- SELECT COUNT(*) AS NumStudents FROM STUDENT;
10. What is an SQL subquery?
Ans:- An SQL subquery is a means of querying two or more tables at the same time. The subquery itself is an SQL SELECT statement contained within the WHERE clause of another SQL SELECT statement, and separated by being enclosed in parenthesis. Some subqueries have equivalent SQL join structures, but correlated subqueries cannot be duplicated by a join..
11. Discuss the alternative terminology that is used in the relational model.
Ans:- Relations are also called tables, and sometimes by the older data processing term files. A row is known as a tuple in the relational model, but may also be referred to as a record. Finally, relational model attributes are known as table columns and sometimes as fields.
12. Why are functional dependencies not equations?
Ans:- Equations deal with numerical relationships. A functional dependency deals with the existence of a determinant relationship between attributes, regardless of whether or not there is a numerical relationship between them. Thus, if we know that there is no hot water every Wednesday, No-Hot-Water is functionally dependent on Wednesday. So, if we know it is Wednesday, then we know we will have No-Hot-Water. This is a functional dependency, but not an equation.
13. What is a foreign key, and what is it used for?
Ans:- A foreign key is used to establish relationships among relations in the relational model. Technically, a foreign key is a column (or columns) appearing in one relation that is (are) the primary key of another table. Although there may be exceptions, the values in the foreign key columns usually must correspond to values existing in the set of primary key values. This correspondence requirement is created in a database using a referential integrity constraint on the foreign key.
14. What are insertion and deletion anomalies?
Ans:- A deletion anomaly occurs when, by deleting the facts about one entity, we inadvertently delete facts about another entity; with one deletion, we lose facts about two entities. For example, if we delete the tuple for Student 001289 from a table, we may lose not only the fact that Student 001289 is in Pierce Hall, but also the fact that he has $200 left in his security deposit. An insertion anomaly happens when we encounter the restriction that we cannot insert a fact about one entity until we have an additional fact about another entity. For example, we want to store the fact that the security deposit for Pierce Hall is $300, but we cannot enter this data into the Student relation until a student registers for Pierce Hall.
15. What does it mean when we say that a relation is in Boyce-Codd Normal Form (BCNF)?
Ans:- A relation is in BCNF when every determinant in the relation is a candidate key. This means that any possible primary key can determine all other attributes in the relation. Attributes may not be determined by non-candidate key attributes or part of a composite candidate key. Thus it is said "I swear to construct my tables so that all nonkey columns are dependent on the key, the whole key and nothing but the key, so help me Codd!"
16. You have been given a set of tables with data and asked to create a new database to store them. When you examine the data values in the tables, what are you looking for?
Ans:-
(1) Multivalued dependencies,
(2) Functional dependencies,
(3) Candidate keys,
(4) Primary keys and
(5) Foreign keys.
17. Why do normalized tables require more complex SQL when SQL statements are used in application programs?
Ans:- Tables that are normalized contain data that has been distributed among the tables, but which may need to be recombined to answer queries from an application. To recombine the data, the programmer will have to use subqueries and/or joins. These SQL structures are more complex to write than a simple SELECT statement.
18. What is the multivalue, multicolumn problem? Include an example not used in the text.
Ans:- The multivalue, multicolumn problem occurs when a table is designed to include multiple columns that hold variations of one type of attribute data. One example is where boat owners have the names of their boats stored as BOAT_01, BOAT_02 and BOAT_03.
19. Why is the multivalue, multicolumn problem another form of the multivalued dependency problem?
Ans:- Both problems try to store multiple values on an attribute in a table. In the multivalue, multiplecolumn problem, the multiple values are stored in different columns. In the mutlivalued dependency problem the multiple values are stored in different rows. In both cases, the solution is the same: store the multiple values in a separate table.
20. What is the inconsistent values problem? Include an example not used in the text.
Ans:- The inconsistent values problem occurs when different users or data sources use slightly different forms of the same data value. One example is where automobiles are specified as "Ford, 2-door, Red" in one cell and "Red Ford 2-door" in another.
Subscribe to:
Post Comments (Atom)
0 Responses to “Database Management System Interview Questions”
Post a Comment