46) Any database needs to go through a normalization process to make sure that data is represented only once. This will eliminate problems with creating or destroying data in the database. The normalization process is done usually in three steps which results in first, second and third normal forms. Which best describes the process to obtain the third normal form? (Check one that applies the best)
A. Each table should have related columns.
B. Each separate table should have a primary key.
C. We have a table with multi-valued key. All columns that are dependent on only one or on some of the keys should be moved in a different table.
D. If a table has columns not dependent on the primary keys, they need to be moved in a separate table.
E. E. Primary key is always UNIQUE and NOT NULL.
A) D. All columns in a table should be dependent on the primary key. This will eliminate transitive dependencies in
which A depends on B, and B depends on C, but we're not sure how C depends on A.
Q) SQL can be embedded in a host program that uses a relational database as a persistent data repository. Some of
the most important pre-defined structures for this mechanism are SQLDA ("SQL Descriptor Area") and
SQLCA ("SQL Communications Area") SQLCA contains two structures - SQLCODE and SQLSTATE.
SQLSTATE is a standard set of error messages and warnings in which the first two characters defines the class
and the last three defines the subclass of the error. Which of the following SQLSTATE codes is interpreted as
"No data returned"?(Check one that applies the best)
A) C. 00 - is successful completion, 01 - warnings, 22 - is data exception and 2A is syntax error. The SQLSTATE code
format returned for "No data returned" is "02xxx".
48) What are common SQL abend codes? (e.g. : 0,100 etc.,)
A) -818 time stamp mismatch
-180 wrong data moved into date field
49) What is meant by dynamic SQL?
A) Dynamic SQL are SQL statements that are prepared and executed within a program while the program is executing.
The SQL source is contained in host variables rather than being hard coded into the program. The SQL statement may
change from execution to execution.
50) What is meant by embedded SQL?
A) They are SQL statements that are embedded with in application program and are prepared during the program
preparation process before the program is executed. After it is prepared, the statement itself does not change(although
values of host variables specified within the statement might change).
51) What is meant by entity integrity?
A) Entity integrity is when the primary key is in fact unique and not null.
52) What will EXPLAIN do?
A) EXPLAIN obtains information (which indexes are used, whether sorting is necessary, which level of locking is
applied) about how SQL statements in the DBRM will be executed, inserting this information into the
?X?.PLAN.TABLE where the ?X? is the authorization ID of the owner of the plan.
53) What is the foreign key?
A) A foreign key is a column (or combination of columns) in a table whose values are required to match those of the
primary key in some other table.
54) What will GRANT option do?
A) It will grant privileges to a list of one or more users. If the GRANT option is used in conjunction with the ?PUBLIC?
option, then all users will be granted privileges. Also you can grant privileges by objects and types.
55) What does the term ?grant privileges? mean?
A) Grant privileges means giving access/authority to DB2 users.
56) What is an image copy?
A) It is an exact reproduction of all or part of a tablespace. DB2 provides utility programs to make full-image copies (to
copy the entire tablespace) or incremental image copies to copy only those pages that have been modified since the last
57) What is meant by an index?
A) An index is a set of row identifiers (RIDs) or pointers that are logically ordered by the values of a column that has
been specified as being an index. Indexes provide faster access to data and can enforce uniqueness on the row in a
58) What is an index key?
A) It is a column or set of columns in a table used to determine the order of index entries.
59) What is a join?
A) A join is a relational operation that allows retrieval of data from two or more tables based on matching columns
60) What is meant by locking?
A) Locking is a process that is used to ensure data integrity. It also prevents concurrent users from accessing inconsistent
data. The data (row) is locked until a commit is executed to release the updated data.