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.
If you have some tips/suggestion please mail me @ Anshul24mehta@gmail.com.
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..
If you have some tips/suggestion please mail me @ Anshul24mehta@gmail.com.
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.
If you have some tips/suggestion please mail me @ Anshul24mehta@gmail.com.
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.
If you have some tips/suggestion please mail me @ Anshul24mehta@gmail.com.
21. Explain the relationship between entity, entity class, and entity instance.
Ans: An entity is something that can be identified in the users' work environment, something that the users want to track. Entities of a given type are grouped into entity classes. An entity instance is the representation of a particular entity.
22. Explain the difference between attributes and identifiers.
Ans: 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.
23. Name and describe three types of binary relationships.
Ans:
· 1:1 - a single entity instance of one type is related to a single-entity instance of another type.
· 1:N - a single entity instance of one type is related to many-entity instances of another type.
· M:N - many-entity instances of one type relate to many-entity instances of another type.
24. What is the archtetype/instance pattern?
Ans: The archetype/instance pattern occurs when one entity tracks occurrences of another entity. A common example is the relationship between CLASS and SECTION, where the actual occurrence of a class happens when students register for a SECTION of that CLASS. The archetype/instance pattern is implemented as an ID-dependent relationship.
25. What is a recursive relationship? Give an example not used in the text.
Ans: A recursive relationship is a relationship between an entity and itself. For example, given the entity PERSON, a recursive relationship could be used to show a PERSON and his or her SIBLINGs (brothers and sisters).
If you have some tips/suggestion please mail me @ Anshul24mehta@gmail.com.
26. What are the steps for transforming an entity into a table?
Ans: The steps are:
(1) specify the primary key,
(2) specify candidate keys,
(3) specify column properties including null status, data type, default value (if any), and data constraints (if any), and
(4) verifying normalization.
27. Define a surrogate key, describe the ideal primary key and explain how surrogate keys meet this ideal.
Ans: The ideal primary key is short, numeric and fixed. A surrogate key is a unique, DBMS-supplied identifier intended to be used as the primary key of a table. Further, the DBMS will not allow the value of a surrogate key to be changed. The values of a surrogate key have no meaning to the users and are usually hidden on forms and reports. By design, they are short, numeric and fixed and thus meet the definition of the ideal primary key
28. Define and discuss data constraints.
Ans: Data constraints on a column are the limits put on the values the data can have. There are four types of data constraints:
(1) domain constraints, which define a limited set of values for the column,
(2) range constraints, which specify that the values must fall within a certain range,
(3) interrelation constraints, which define what values the column can have based on values of other columns in the same table, and
(4) interrelation constraints, which define values the column can have based on values of columns in other tables.
29. In general, how are recursive relationships handled in a database design?
Ans: A recursive relationship is a relationship among entities of the same class, and is represented in the same way as other relationships are. The rows of the tables can take two different roles, however. Some are parent rows, and others are child rows. Further, the table will contain both its own primary key and the foreign key that links back to the table itself. If a row has no parent, then the value of the foreign key column in that row will be null. If the row has a parent, then there must be a foreign key value in that row that corresponds to the primary key value of another row in the table.
30. What is a cascading update?
Ans: Referential integrity constraints require that foreign key values in one table correspond to primary key values in another. If the value of the primary key is changed -- that is, updated -- the value of the foreign key must immediately be changed to match it. Cascading updates will set this change to be done automatically by the DBMS whenever necessary.
If you have some tips/suggestion please mail me @ Anshul24mehta@gmail.com.
31. What is a SQL view? Briefly explain the use of views.
Ans: A SQL view is a virtual table built from other tables or views. Views are used to (1) hide columns or rows, (2) show the results of computed columns, (3) hide complicated SQL syntax, (4) layer built-in functions, (5) provide a level of indirection between application programs and tables, (6) assign different sets of processing permissions to tables, and (7) to assign different sets of triggers to the same table.
32. Explain the "paradigm mismatch" between SQL and application programming languages.
Ans: 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.
33. Name four applications for triggers.
Ans:
(1) providing default values,
(2) enforcing data constraints,
(3) updating views and
(4) enforcing referential integrity
34. What are stored procedures, and how do they differ from triggers?
Ans: A stored procedure is a program that is stored within the database and is compiled when used. They can receive input parameters and they can return results. Unlike triggers, their scope is database-wide; they can be used by any process that has permission to use the database stored procedure.
35. What are the advantages of using stored procedures?
Ans: The advantages of stored procedures are
(1) greater security,
(2) decreased network traffic,
(3) the fact that SQL can be optimized and
(4) code sharing which leads to less work, standardized processing, and specialization among developers.
If you have some tips/suggestion please mail me @ Anshul24mehta@gmail.com.
36. Why is database redesign necessary?
Ans: Database redesign is necessary for two reasons. First, redesign is necessary both to fix mistakes made during the initial database design. Second, redesign is necessary to adapt the database to changes in system requirements. Such changes are common because information systems and organizations do not just influence each other they create each other. Thus, new information systems cause changes in systems requirements.
37. What is the difference between a correlated subquery and a regular subquery?
Ans: A correlated subquery appears deceptively similar to a regular subquery. The difference is that a regular subquery can be processed from the bottom up. In a regular subquery, results from the lowest query can be determined and used to evaluate the upper-level query. In contrast, in a correlated subquery, the processing is nested; that is, a row from an upper query statement is used in comparison with rows in a lower level query. The key distinction of a correlated subquery is that the lower-level select statements use columns from upper-level statements.
38. What is a dependency graph?
Ans: A dependency graph is a diagram that is used to portray the connections between database elements. For example, a change in a table can potentially impact relationships, views, indexes, triggers, stored procedures, and application programs. These impacts need to be known and accounted for before making database changes.
39. Explain how to add a NOT NULL column to a table.
Ans: First, add the column as NULL. Then use UPDATE to add data to every row. Finally use an ALTER TABLE . . . ALTER COLUMN statement to change the column constraint to NOT NULL.
40. You have two tables, EMPLOYEE and COMPUTER that are in a one-to-one relationship. The foreign key is EmpNumber in COMPUTER which references EmpNumber as the primary key of EMPLOYEE. Explain what must be done to convert the one-to-one EMPLOYEE-COMPUTER relationship to a one-to-many relationship where one employee can have more than one computer.
Ans: In the one-to-one relationship, there will be a constraint on EmpNumber as a foreign key in COMPUTER stating that EmpNumber must be unique. To convert the relationship to a one-to-many relationship, just drop this constraint.
If you have some tips/suggestion please mail me @ Anshul24mehta@gmail.com.
41. Explain the difference between an exclusive lock and a shared lock.
Ans: 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.
42. Explain the difference between optimistic locking and pessimistic locking.
Ans: Optimistic locking assumes no transaction conflict will occur and deals with the consequences if it does. Pessimistic locking assumes that conflict will occur and so prevents it ahead of time with locks. In general, optimistic locking is preferred for the Internet and for many intranet applications.
43. What is deadlock? How can it be avoided? How can it be resolved once it occurs?
Ans: Deadlock occurs when two transactions are each waiting on a resource that the other transaction holds. Deadlock can be prevented by requiring transactions to acquire all locks at the same time; once it occurs, the only way to cure it is to abort one of the transactions and back out of partially completed work.
44. What are the major functions of the database administrator?
Ans: Managing database structure, controlling concurrent processing, managing processing rights and responsibilities, developing database security, providing for database recovery, managing the DBMS and maintaining the data repository.
45. Explain what we mean by an ACID transaction.
Ans: An ACID transaction is one that is atomic, consistent, isolated, and durable. Durable means that database changes are permanent. Consistency can mean either statement level or transaction level consistency. With transaction level consistency, a transaction may not see its own changes. There are four transaction isolation levels: read committed, read uncommitted, repeatable read and serialization. Atomic means it is performed as a unit.
If you have some tips/suggestion please mail me @ Anshul24mehta@gmail.com.
46. What are the ways in which an Oracle database can be created?
Ans: There are three (3) ways to create an Oracle database. You can create a database using the Database Configuration Assistant, using the Oracle-supplied database creation procedures or using the SQL CREATE DATABASE command.
47. What are sequences, and what are the possible problems when using them to create surrogate keys?
Ans: A sequence is an object that generates a sequential series of unique numbers. Sequences are most often used to provide values for surrogate keys. However, there are three problems with using sequences. First, a developer can use a defined sequence for any purpose; and if a sequence is used for purposes other than the surrogate key, some values will be missing. A second problem is that there is nothing in the schema that prevents someone from issuing an INSERT statement that adds data to the table but that does not use the sequence. Finally, it is possible to use the wrong sequence when putting data into a table.
48. Under what conditions should indexes be used?
Ans: Indexes can be created to enforce uniqueness, to facilitate sorting, and to enable fast retrieval by column values. A good candidate for an index is a column that is frequently used with equal conditions in WHERE clauses.
49. Explain the three levels of transaction isolation supported by Oracle.
Ans: Oracle supports read committed, serializable, and read-only transaction isolation levels. Because of the way Oracle System Change Number (SCN) values are processed, Oracle never reads dirty data. Serializable isolation is possible, but the application program must be written to process the "Cannot serialize" exception. Applications can place locks explicitly using SELECT FOR UPDATE commands but this is not recommended.
50. What are the types of files used in Oracle recovery?
Ans: Datafiles, control files and two types of ReDo log files: OnLine Redo and Offline ReDo (which is also known as Archive ReDo).
Click on more advance RDBMS reading -> RDBMS - Advance 2 and RDBMS - Advance 3
You can revise the basic here : RDBMS - BASIC 1 and RDBMS - BASIC 2