Questions and Answers of Database Processing Fundamentals

What is an SQL injection attack and how can it be prevented?
Explain the difference between optimistic and pessimistic locking.
Explain the benefits of marking transaction boundaries, declaring lock characteristics, and letting the DBMS place locks.
What is SQL Transaction Control Language (TCL)? Explain the use of SQL BEGIN TRANSACTION, COMMIT TRANSACTION, and ROLLBACK TRANSACTION statements. Why does MySQL also use the SQL START TRANSACTION
Explain the meaning of the expression ACID transaction.
Describe statement-level consistency.
Describe transaction-level consistency. What disadvantage can exist with it?
What is the purpose of transaction isolation levels?
Explain the read uncommitted isolation level. Give an example of its use.
Explain the read committed isolation level. Give an example of its use.
Explain the repeatable read isolation level. Give an example of its use.
Explain the serializable isolation level. Give an example of its use.
Explain the term SQL cursor.
Explain why a transaction may have many cursors. Also, how is it possible that a transaction may have more than one cursor on a given table?
What is the advantage of using different types of cursors?
Explain forward only cursors. Give an example of their use.
Explain static cursors. Give an example of their use.
Explain keyset cursors. Give an example of their use.
Explain dynamic cursors. Give an example of their use.
What happens if you do not declare the transaction isolation level and the cursor type to the DBMS? Is this good or bad?
What is SQL Data Control Language (DCL)? Explain the necessity of defining processing rights and responsibilities. How are such responsibilities enforced, and what is the role of SQL DCL in enforcing
Explain the relationships among USER, GROUP, PERMISSION, and OBJECT for a generic database security system.
Should the DBA assume a firewall when planning security?
What should be done with unused DBMS features and functions?
Explain how to protect the computer that runs the DBMS.
With regard to security, what actions should the DBA take on user accounts and passwords?
List two elements of a database security plan.
Describe the advantages and disadvantages of DBMS-provided and application-provided security.
Explain how a database could be recovered via reprocessing. Why is this generally not feasible?
Define rollback and rollforward.
Why is it important to write to the log before changing the database values?
What is the advantage of taking frequent checkpoints of a database?
Describe the rollforward process. Under what conditions should it be used?
Describe the rollback process. Under what conditions should it be used?
Summarize the DBA’s responsibilities for managing the DBMS.
What is a data repository? A passive data repository? An active data repository?
Explain why a data repository is important. What is likely to happen if one is not available?
What is a surrogate key?
Where does the value of a surrogate key come from?
When would you use a surrogate key?
What is a foreign key? Explain the significance of the referential integrity constraint to a foreign key.
The term domestic key is not used. If it were used, however, what do you think it would mean?
What is a normal form?
Illustrate deletion, modification, and insertion anomalies on the STUDENT_ACTIVITY relation in Figure 3-24.
Explain why duplicated data leads to data integrity problems.
What relations are in 1NF?
Which normal forms are concerned with functional dependencies?
What conditions are required for a relation to be in 2NF?
What conditions are required for a relation to be in 3NF?
What conditions are required for a relation to be in BCNF?
If a relation is in BCNF, what can we say about it with regard to 2NF and 3NF?
What normal form is concerned with multivalued dependencies?
What is the premise of Fagin’s work on DK/NF?
Summarize the three categories of normalization theory.
In general, how can you transform a relation not in BCNF into ones that are in BCNF?
What is a referential integrity constraint? Define the term, and give an example of its use. Are null values allowed in foreign key columns with a referential integrity constrain? How does the
Explain the role of referential integrity constraints in normalization.
Why is an un-normalized relation like a paragraph with multiple themes?
In normalization example 3, why is the EXTENDED_PRICE relation silly?
In normalization example 4, under what conditions is(StudentID, Activity) → ActivityFeemore accurate than Activity → ActivityFee
If a determinant is part of a candidate key, is that good enough for BCNF?
In normalization example 5, why are the following two tables not correct?DEPARTMENT (Department, DeptBudgetCode, Buyer)SKU_DATA_4 (SKU, SKU_Description, Department)
How does a multivalued dependency differ from a functional dependency?
Consider the relation:PERSON (Name, Sibling, ShoeSize)Assume the following functional dependencies exist:Name →→ SiblingName → ShoeSizeDescribe deletion, modification, and insertion anomalies
Place the PERSON relation in Review Question 3.52 into 4NF.
Consider the relation:PERSON_2 (Name, Sibling, ShoeSize, Hobby)Assume that the following functional dependencies exist:Name → → SiblingName → ShoeSizeName → → HobbyDescribe
Place the PERSON_2 relation in Review Question 3.54 into 4NF.Data from in Review Question 3.54Assume that the following functional dependencies exist: Name → → Sibling Name → → ShoeSize Name
What is 5NF?
How do the conditions for DK/NF correspond to the conditions for BCNF?
Summarize the premise of this chapter.
When you receive a set of tables, what steps should you take to assess their structure and content?
Show SQL statements to count the number of rows and to list the top 15 rows of the RETAIL_ORDER table.
Suppose you receive the following two tables:DEPARTMENT (DepartmentName, BudgetCode)EMPLOYEE (EmployeeNumber, EmployeeLastName, EmployeeFirstName, Email, DepartmentName)and you conclude that
Summarize how database design principles differ with regards to the design of updateable databases and the design of read-only databases.
Describe two advantages of normalized tables.
Describe two disadvantages of normalized tables.
What is the best test for determining whether a determinant is unique?
What is a primary key? Explain the significance of the entity integrity constraint to a primary key.
What is a candidate key?
What is a composite key?
Under what conditions will a determinant be unique in a relation?
If it is true that:PartNumber → PartWeightDoes that mean that PartNumber will be unique in a relation?
For the SKU_DATA table in Figure 3-1, explain why Buyer determines Department, but Department does not determine Buyer.
If A → (B, C), then can we also say that A → B?
If (A, B) → C, then can we also say that A → C?
What is a composite determinant?
Explain the meaning of the expression:(FirstName, LastName) → Phone
Explain the following statement: "The only reason for having relations is to store instances of functional dependencies."
Intuitively, what is the meaning of the functional dependency:PartNumber → PartWeight
Explain the three different sets of terms used to describe tables, columns, and rows.
Must all the values in the same column of a relation have the same length?
Suppose that two columns in two different tables have the same column name. What convention is used to give each a unique name?
Give an example of two tables that are not relations.
Describe the characteristics of a table that make it a relation. Define the term domain and explain the significance of the domain integrity constraint to a relation.
What is the basic premise of this and the next chapter?
Name three sources for databases.
Write an SQL statement to show a unique SKU and SKU_Description for all products having a description that includes the word 'Climb'.
Write an SQL statement to show a unique SKU and SKU_Description for all products having a ‘d’ in the third position from the left in SKU_Description.
Explain the difference between the SQL built-in functions COUNT and SUM.
Write an SQL statement to display the WarehouseID and the sum of QuantityOnHand, grouped by WarehouseID. Name the sum TotalItemsOnHand and display the results in descending order of TotalItemsOnHand.

Showing 600 - 700 of 777