New Semester
Started
Get
50% OFF
Study Help!
--h --m --s
Claim Now
Question Answers
Textbooks
Find textbooks, questions and answers
Oops, something went wrong!
Change your search query and then try again
S
Books
FREE
Study Help
Expert Questions
Accounting
General Management
Mathematics
Finance
Organizational Behaviour
Law
Physics
Operating System
Management Leadership
Sociology
Programming
Marketing
Database
Computer Network
Economics
Textbooks Solutions
Accounting
Managerial Accounting
Management Leadership
Cost Accounting
Statistics
Business Law
Corporate Finance
Finance
Economics
Auditing
Tutors
Online Tutors
Find a Tutor
Hire a Tutor
Become a Tutor
AI Tutor
AI Study Planner
NEW
Sell Books
Search
Search
Sign In
Register
study help
computer sciences
databases
Database Systems Design Implementation And Management 11th Edition Carlos Coronel, Steven Morris - Solutions
Write a query to display the vendor ID, vendor name, brand name, and number of products of each brand supplied by each vendor. Sort the output by vendor name and then by brandname.
What is a CROSS JOIN? Give an example of its syntax.
What three join types are included in the OUTER JOIN classification?
What is a subquery, and what are its basic characteristics?
What is a correlated subquery? Give an example.
Explain the difference between a regular subquery and a correlated subquery.
The relational set operators UNION, INTERSECT, and MINUS work properly only if the relations are union-compatible. What does union-compatible mean, and how would you check for this condition?
What is the difference between UNION and UNION ALL? Write the syntax for each.
Why does the order of the operands (tables) matter in a MINUS query but not in a UNION query?
What is an Oracle sequence? Write its syntax.
What is a trigger, and what is its purpose? Give an example.
What is a stored procedure, and why is it particularly useful? Give an example.
What is embedded SQL, and how is it used?
What is dynamic SQL, and how does it differ from static SQL?
Using tables named T1 and T2, write a query example for each of the three join types you described in Question 2. Assume that T1 and T2 share a common column named C1.
Given the employee information in Question 1, what is the query output for the MINUS query? (List the query output.)
What Oracle function should you use to calculate the number of days between the current date and January 25, 1999?
Suppose that a PRODUCT table contains two attributes, PROD_CODE and VEND_CODE. Those two attributes have values of ABC, 125, DEF, 124, GHI, 124, and JKL, 123, respectively. The VENDOR table contains a single attribute, VEND_CODE, with values 123, 124, 125, and 126, respectively. (The VEND_CODE
Create the tables. (Use the MS Access example shown in Figure P8.1 to see what table names and attributes touse.)
Insert the data into the tables you created in Problem1.
Write the query that will show only the duplicate customer records.
Write the query that will generate only the records that are unique to the CUSTOMER_2 table.
Write the query that will show the invoice number, the invoice amount, the average invoice amount, and the difference between the average invoice amount and the actual invoice amount.
Modify the CUSTOMER table to included two new attributes: CUST_DOB and CUST_AGE. Customer 1000 was born on March 15, 1979, and customer 1001 was born on December 22, 1988.
Assuming you completed problem 10, write the query that will list the names and ages of your customers.
Write the trigger to update the CUST_BALANCE in the CUSTOMER table when a new invoice record is entered. (Assume that the sale is a credit sale.) Test the trigger using the following new INVOICE record:8005, 1001, ’27-APR-14’, 225.40
Write a procedure to add a new customer to the CUSTOMER table. Use the following values in the new record:1002, ‘Rauthor’, ‘Peter’, 0.00
Write a procedure to add a new invoice record to the INVOICE table. Use the following values in the new record:8006, 1000, ’30-APR-14’, 301.72
Write a procedure to delete an invoice given the invoice number as a parameter. Name the procedure prc_inv_delete. Test the procedure by deleting invoices 8005 and 8006.
Write a query to display the invoice number, line numbers, product SKUs, product descriptions, and brand ID for sales of sealer and top coat products of the same brand on the sameinvoice.
The Binder Prime Company wants to recognize the employee who sold the most of their products during a specified period. Write a query to display the employee number, employee first name, employee last name, e-mail address, and total units sold for the employee who sold the most Binder Prime brand
Write a query to display the customer code, first name, and last name of all customers who have had at least one invoice completed by employee 83649 and at least one invoice completed by employee 83677. Sort the output by customer last name and then firstname.
LargeCo is planning a new promotion in Alabama (AL) and wants to know about the largest purchases made by customers in that state. Write a query to display the customer code, customer first name, last name, full address, invoice date, and invoice total of the largest purchase made by each customer
One of the purchasing managers is interested in the impact of product prices on the sale of products of each brand. Write a query to display the brand name, brand type, average price of products of each brand, and total units sold of products of each brand. Even if a product has been sold more than
The purchasing manager is still concerned about the impact of price on sales. Write a query to display the brand name, brand type, product SKU, product description, and price of any products that are not a premium brand, but that cost more than the most expensive premium brandproducts.
Create a stored procedure named prc_inv_amounts to update the INV_SUBTOTAL, INV_TAX, and INV_TOTAL. The procedure takes the invoice number as a parameter. The INV_SUBTOTAL is the sum of the LINE_TOTAL amounts for the invoice, the INV_TAX is the product of the INV_SUBTOTAL and the tax rate (8%), and
Create a procedure named prc_cus_balance_update that will take the invoice number as a parameter and update the customer balance.
Create a trigger named trg_char_hours that will automatically update the AIRCRAFT table when a new CHARTER row is added. Use the CHARTER table’s CHAR_HOURS_FLOWN to update the AIRCRAFT table’s AC_TTAF, AC_TTEL, and AC_TTER values.
Create a trigger named trg_pic_hours that will automatically update the PILOT table when a new CREW row is added and the CREW table uses a ‘pilot’ CREW_JOB entry. Use the CHARTER table’s CHAR_HOURS_FLOWN to update the PILOT table’s PIL_PIC_HRS only when the CREW table uses a ‘pilot’
Create a stored procedure named prc_new_rental to insert new rows in the RENTAL table. The procedure should satisfy the following conditions.a. The membership number will be provided as a parameter.b. Use a Count() function to verify that the membership number exists in the MEMBERSHIP table. If
Create a stored procedure named prc_return_video enter data about the return of videos that had been rented. The procedure should satisfy the following requirements.a. The video number will be provided as a parameter.b. Verify the video number exists in the VIDEO table. If it does not exist,
Create a stored procedure named prc_new_detail to insert new rows in the DETAILRENTAL table. The procedure should satisfy the following requirements.a. The video number will be provided as a parameter.b. Verify the video number exists in the VIDEO table. If it does not exist, then display a
Create a trigger named trg_mem_balance that will maintain the correct value in the membership balance in the MEMBERSHIP table when videos are returned late. The trigger should execute as an AFTER trigger when the due date or return date attributes are updated in the DETAILRENTAL table. The trigger
What is an information system? What is its purpose?
How do systems analysis and systems development fit into a discussion about information systems?
What does the acronym DBLC mean, and what does a DBLC portray?
Discuss the distinction between centralized and decentralized conceptual database design.
Discuss the distinction between top-down and bottom-up approaches to database design.
What are business rules? Why are they important to a database designer?
What is the data dictionary's function in database design?
What steps are required in the development of an ER diagram?
List and briefly explain the activities involved in the verification of an ER model.
What factors are important in a DBMS software selection?
List and briefly explain the four steps performed during the logical design stage.
List and briefly explain the three steps performed during the physical design stage.
What three levels of backup may be used in database recovery management? Briefly describe what each of those three backup levels does.
What does the acronym SDLC mean, and what does an SDLC portray?
What is the minimal data rule in conceptual design? Why is it important?
The ABC Car Service & Repair Centers are owned by the SILENT car dealer; ABC services and repairs only SILENT cars. Three ABC Car Service & Repair Centers provide service and repair for the entire state.Each of the three centers is independently managed and operated by a shop manager, a
Suppose you have been asked to create an information system for a manufacturing plant that produces nuts and bolts of many shapes, sizes, and functions. What questions would you ask, and how would the answers to those questions affect the database design?a. What do you envision the SDLC to be?b.
Suppose you perform the same functions noted in Problem 2 for a larger warehousing operation. How are the two sets of procedures similar? How and why are they different?
Using the same procedures and concepts employed in Problem 1, how would you create an information system for the Tiny College example in Chapter 4?
Write the proper sequence of activities in the design of a video rental database. (The initial ERD was shown in Figure 9.9.) The design must support all rental activities, customer payment tracking, and employee work schedules, as well as track which employees checked out the videos to the
Explain the following statement: a transaction is a logical unit of work.
What is a consistent database state, and how is it achieved?
The DBMS does not guarantee that the semantic meaning of the transaction truly represents the real-world event. What are the possible consequences of that limitation? Give an example.
List and discuss the five transaction properties.
What is a transaction log, and what is its function?
What is a scheduler, what does it do, and why is its activity important to concurrency control?
What is a lock, and how, in general, does it work?
What are the different levels of lock granuality?
Why might a page-level lock be preferred over a field-level lock?
What is concurrency control, and what is its objective?
What is an exclusive lock, and under what circumstances is it granted?
What is a deadlock, and how can it be avoided? Discuss several strategies for dealing with deadlocks.
Why might it take a long time to complete transactions when an optimistic approach to concurrency control is used?
What are the three types of database critical events that can trigger the database recovery process? Give some examples for each one.
What are the four ANSI transaction isolation levels? What type of reads does each level allow?
What are some disadvantages of time-stamping methods for concurrency control?
Suppose you are a manufacturer of product ABC, which is composed of parts A, B, and C. Each time a new product is created, it must be added to the product inventory, using the PROD_QOH in a table named PRODUCT. And each time the product ABC is created, the parts inventory, using PART_QOH in a table
Suppose that your database system has failed. Describe the database recovery process and the use of deferred-write and write-through techniques.
ABC Markets sell products to customers. The relational diagram shown in Figure represents the main entities for ABC’s database. The following important characteristics:A customer may make many purchases, each one represented by an invoice.The CUS_BALANCE is updated with each credit purchase or
Create a simple transaction log (using the format shown in Table) to represent the actions of the two previous transactions.
Assuming that pessimistic locking is being used, but the two-phase locking protocol is not, create a chronological list of the locking, unlocking, and data manipulation activities that would occur during the complete processing of the transaction described in Problem.
Assuming that pessimistic locking with the two-phase locking protocol is being used, create a chronological list of the locking, unlocking, and data manipulation activities that would occur during the complete processing of the transaction described in Problem.
Assuming that pessimistic locking is being used, but the two-phase locking protocol is not, create a chronological list of the locking, unlocking, and data manipulation activities that would occur during the complete processing of the transaction described in Problem. Discuss.
Assuming that pessimistic locking with the two-phase locking protocol is being used create a chronological list of the locking, unlocking, and data manipulation activities that would occur during the complete processing of the transaction described in Problem.
Write the SQL statements that might be used in transaction management and explain how they work.
Starting with a consistent database state, trace the activities that are required to execute a set of transactions to produce an updated consistent database state.
Write an example of a database request.
Trace the use of the transaction log in database recovery.
Suppose you are asked to evaluate a DBMS in terms of lock granularity and the different locking levels. Create a simple database environment in which these features would be important.
What DBMS component is responsible for concurrency control? How is this feature used to resolve conflicts?
What are database statistics, and why are they important?
How are database statistics obtained?
What database statistics measurements are typical of tables, indexes, and resources?
In simple terms, the DBMS processes queries in three phases. What are those phases, and what is accomplished in each phase?
If indexes are so important, why not index every column in every table? (Include a brief discussion of the role played by data sparsity.)
What is the difference between a rule-based optimizer and a cost-based optimizer?
What are optimizer hints and how are they used?
Showing 700 - 800
of 3228
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Last
Step by Step Answers
Get 50% OFF
Claim Now
.png)
.png)
.png)
.png)
.png)
.png)
.png)
.png)
.png)
.png)
