Questions and Answers of Database Processing Fundamentals

Describe the general characteristics of Oracle Database 12c and the Oracle Database 12c product suite. Explain why these characteristics mean there is considerable complexity to master.
Explain the problems inherent in using sequences for surrogate key columns.
In Figure 10A-58, why is the SELECT statement that begins SELECT @CustomerID necessary?
In Figure 10C-66, why is the SELECT statement that begins SELECT COUNT(*) INTO varRowCount necessary?
Show SQL statements for creating a relationship between table T2 and table T3. Assume that T3 has a foreign key column named FK1 that relates to T2 and that deletions in T2 should force deletions in
Answer question 10B.17, but do not force deletions.
Explain the purpose of the transaction shown in Figure 10A-60.
Explain the purpose of the procedure shown in Figure 10C-69.
Explain how to use the To-Date function.
What happens if an incorrect value of Copy is input to the stored procedure in Figure 10A-60?
What happens if an incorrect value of Copy is input to the stored procedure in Figure 10C-69?
Explain how you would use SQL Developer to create an index on the salary field of an employee table. What sorts of queries will benefit from this, and how?
In Figure 10A-60, what happens if the ROLLBACK statement is executed?
In Figure 10C-69, what happens if the ROLLBACK statement is executed?
Create a database named MEDIA in Oracle Database.
In Figure 10A-60, why is SUM used instead of AVG?
In Figure 10C-73, why is SUM used instead of AVG?
In the SQL Developer folder structure in your Documents folder, create a folder named DBP-e14-Media-Database. Use this folder to save and store *.sql scripts containing the SQL statements that you
What are the three primary factors that influence SQL Server locking behavior?
What is the primary factor that influences MySQL locking behavior?
Create a connection named Media-Database in SQL Developer, and use it to connect to the MEDIA database. Open a new tabbed SQL Worksheet window, and save it as MEDIA-CH10B-RQ-Solutions.sql in the
Explain why the strategy for storing CHECK constraint values in a separate table is better than implementing them in a table-based constraint. How can this strategy be used to implement the
Explain why the strategy for storing CHECK constraint values in a separate table is better than implementing them in a table-based constraint. How can this strategy be used to implement the
Explain why the view CustomerInterestsView in Figure 10A-36 is not updateable. Describe the logic of the INSTEAD OF UPDATE trigger in Figure 10A-66.
Explain why the lack of an INSTEAD OF trigger disallows updating views in MySQL.
Explain what limitation must be enforced for the trigger in Figure 10A-66 to be effective.
Explain the meaning of each of the transaction isolation levels under Options shown in Figure 10C-82. Туре Scope Options Transaction isolation level GLOBAL READ UNCOMMITTED SESSION READ COMMITTED
Explain the meaning of each of the transaction isolation levels under Options shown in Figure 10A-72.
How are backups performed using MySQL Administrator?
How are recoveries performed using MySQL Administrator?
What is the purpose of locking hints?
What is the difference between complete and differential backups? Under what conditions are complete backups preferred? Under what conditions are differential backups preferred?
Explain the differences between simple, full, and bulk-logged recovery models. Under what conditions would you choose each one?
Run an SQL SELECT query to demonstrate that the view PopularShowsView was constructed correctly.
Use the SQL Developer GUI tools to determine that the PopularShowsView was constructed properly. Modify this view to include PICTURE.PictureDescription and PICTURE.PictureFileName. Clicking on the
Can the SQL DELETE statement be used with the PopularShowsView View? Why or why not?
Under what circumstances can the PopularShowsView be used for inserts and modifications?
For the View Ridge Gallery database discussed in this chapter, construct a view that contains a customer’s LastName, FirstName, City, and State. Name your view CustomerBasicView.
For the View Ridge Gallery database, construct a view that has the full customer name and artist name for all art that the customer has purchased.
For the View Ridge Gallery database, construct a view that has full customer name and full artist name for all artists in which the customer is interested. Explain the difference between this view
Can you combine the views in questions 10B.36 and 10B.37 into one view? Why or why not?
How can you update an SQL view using Oracle Database?
In PL/SQL, what is the purpose of the RETURN keyword?
What must be done to be able to see the output generated by the Oracle Database DBMS_OUTPUT package? What limits exist on such output?
Explain how the PL/SQL statement FOR variable IN cursorname works.
Where in SQL Developer will you see error messages when compiling stored procedures and triggers?
What is the syntax of the BEGIN TRANSACTION statement in PL/SQL? How is a transaction started?
In the stored procedure in Figure 10B-82, how are the values of the variables varTID and varAID used if there are no suitable TRANS rows in the database? How are they used if there is just one
Explain the purpose of BEFORE, AFTER, and INSTEAD OF triggers.
When an update is in progress, how can the trigger code obtain the value of a column, say C1, before the update began? How can the trigger code obtain the value that the column is being set to?
Explain why INSTEAD OF triggers are needed for join views.
Explain a limitation on the use of AFTER triggers.
What three levels of transaction isolation are supported by Oracle Database?
Explain how Oracle Database uses the system change number (SCN) to read data that are current at a particular point in time.
Under what circumstances does Oracle Database read dirty data?
Explain how conflicting locks are handled by Oracle Database when a transaction is operating in read-committed isolation mode.
Show the SQL statement necessary to set the transaction isolation level to serializable for an entire session.
What happens when a transaction in serializable mode tries to update data that have been updated by a different transaction? Assume that the SCN is less than the transaction’s SCN. Assume the SCN
Describe three circumstances under which a transaction could receive the “Cannot serialize” exception.
Explain how Oracle Database processes the read-only transaction isolation level.
What three types of files are important for Oracle Database backup and recovery processing?
What is the difference between the OnLine ReDo logs and the OffLine or Archive ReDo logs? How is each type used?
What does multiplexing mean in the context of Oracle Database recovery?
Explain how Oracle Database recovers from application failure.
What is instance failure, and how does Oracle Database recover from it?
What is media failure, and how does Oracle Database recover from it?
What is a binary relationship?
Explain how a data model is like a building blueprint. What is the advantage of making changes during the data modeling stage?
Explain the following statement: “The multivalue, multicolumn problem is just another form of multivalued dependency.” Show how this is so.
Show how the tables in your answer to question 4.25 solve the problems you identified in question 4.24.
Show how to represent the relation in your answer to question 4.23 with two tables.
Give an example of a multivalue, multicolumn table other than one discussed in this chapter.
List four common design problems when creating databases from existing data.
Why are customized duplicated tables not used for updateable databases?
Summarize the reasons for creating customized duplicated tables.
Suppose you are given the DEPARTMENT and EMPLOYEE tables in question 4.10 and asked to denormalize them into the EMPLOYEE_DEPARTMENT relation. Show the design of the EMPLOYEE_DEPARTMENT relation.
For read-only databases, how persuasive is the argument that normalization reduces file space?
How does the fact that a read-only database is never updated influence the reasons for normalization?
Describe three uses for a read-only database.
Compare the difficulty of writing subqueries and joins with the difficulty of dealing with anomalies caused by multivalued dependencies.
According to this text, under what situations should you choose not to remove multivalued dependencies from a relation?
Describe a situation, other than the one for zip codes, in which one would choose not to place tables into BCNF. Justify your decision not to use BCNF.
Summarize the reasons explained in this chapter for not placing ZIP code values into BCNF.
Suppose you are given the table:EMPLOYEE_DEPARTMENT (EmployeeNumber, EmployeeLastName, EmployeeFirstName, Email, DepartmentName, BudgetCode)and you wish to transform this table into the two
If data duplication is only reduced, how can we say that the possibility of data inconsistencies has been eliminated?
Why do we say that data duplication is only reduced? Why is it not eliminated?
For the SKU_DATA table in Figure 3-1, explain why:SKU_Description → (SKU, Department, Buyer)
Define each of the terms listed in Figure 3-3.
Write an SQL statement to show the SKU, SKU_Description, WarehouseID for all items stored in a warehouse managed by ‘Lucille Smith’. Use a subquery.
Write an SQL statement to display SKU and SKU_Description.
Write an SQL statement to display the SKU, SKU_Description, and Department of all SKUs that appear in only the Cape Codd 2013 Catalog (either in the printed catalog or on the Web site) and not in the
Write an SQL statement to display the SKU, SKU_Description, and Department of all SKUs that appear in both the Cape Codd 2013 Catalog (only in the printed catalog itself) and the Cape Codd 2014
Write an SQL statement to display the SKU, SKU_Description, and Department of all SKUs that appear in both the Cape Codd 2013 Catalog (either in the printed catalog or on the Web site) and the Cape
Write an SQL statement to display the SKU, SKU_Description, and Department of all SKUs that appear in either the Cape Codd 2013 Catalog (only in the printed catalog itself) or the Cape Codd 2014
Write an SQL statement to display the SKU, SKU_Description, and Department of all SKUs that appear in either the Cape Codd 2013 Catalog (either in the printed catalog or on the Web site) or the Cape
Write an SQL statement to join WAREHOUSE and INVENTORY and include all rows of WAREHOUSE in your answer, regardless of whether they have any INVENTORY. Run this statement.
Write an SQL statement to display the WarehouseID, the sum of QuantityOnOrder and sum of QuantityOnHand, grouped by WarehouseID and QuantityOnOrder. Name the sum of QuantityOnOrder as
Write an SQL statement to show the WarehouseID, WarehouseCity, WarehouseState, Manager, SKU, SKU_Description, and QuantityOnHand of all items with a Manager of ‘Lucille Smith’. Use a join using
Write an SQL statement to show the WarehouseID and average QuantityOnHand of all items stored in a warehouse managed by ‘Lucille Smith’. Use a join using JOIN ON syntax.
Write an SQL statement to show the WarehouseID and average QuantityOnHand of all items stored in a warehouse managed by ‘Lucille Smith’. Use a join, but do not use JOIN ON syntax.

Showing 200 - 300 of 777