Database Processing Fundamentals, Design, and Implementation 14th edition David M. Kroenke, David J. Auer - Solutions

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 TotalItemsOnOrder and the sum of QuantityOnHand as TotalItemsOnHand. Use only the INVENTORY table in your SQL
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 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 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.
Write an SQL statement to show the WarehouseID and average QuantityOnHand of all items stored in a warehouse managed by ‘Lucille Smith’. Use a subquery.
Write an SQL statement to show the SKU, SKU_Description, WarehouseID for all items stored in a warehouse managed by ‘Lucille Smith’. Use a join using JOIN ON syntax.
Write an SQL statement to show the SKU, SKU_Description, and WarehouseID for all items stored in a warehouse managed by ‘Lucille Smith’. Use a join, but do not use JOIN ON syntax.
Write an SQL statement to produce a single column called ItemLocation that combines the SKU_Description, the phrase “is located in”, and WarehouseCity. Do not be concerned with removing leading or trailing blanks.
Write an SQL statement to display the SKU, SKU_Description, WarehouseID, WarehouseCity, and WarehouseState of all items not stored in the Atlanta, Bangor, or Chicago warehouse. Use the NOT IN keyword.
Write an SQL statement to display the SKU, SKU_Description, WarehouseID, WarehouseCity, and WarehouseState for all items stored in the Atlanta, Bangor, or Chicago warehouse. Use the IN keyword.
Write an SQL statement that uses all of the SQL built-in functions on the QuantityOn-Hand column. Include meaningful column names in the result.
Write an SQL statement to show a unique SKU and SKU_Description for all products having an SKU description starting with ‘Half-dome’.
Write an SQL statement to display the SKU, SKU_Description, WarehouseID, and QuantityOnHand for all products having a QuantityOnHand greater than 1 and less than 10. Use the BETWEEN keyword.
Write an SQL statement to display the SKU, SKU_Description, WarehouseID, and QuantityOnHand for all products having a QuantityOnHand greater than 1 and less than 10. Do not use the BETWEEN keyword.
Write an SQL statement to display SKU, SKU_Description, and WarehouseID for all products that have a QuantityOnHand equal to 0 or a QuantityOnOrder equal to 0. Sort the results in descending order by WarehouseID and in ascending order by SKU.
Write an SQL statement to display SKU, SKU_Description, and WarehouseID for all products that have a QuantityOnHand equal to 0 and a QuantityOnOrder greater than 0. Sort the results in descending order by WarehouseID and in ascending order by SKU.
Write an SQL statement to display the SKU, SKU_Description, and WarehouseID for products having QuantityOnHand greater than 0. Sort the results in descending order by WarehouseID and ascending order by SKU.
Write an SQL statement to display the SKU, SKU_Description, and WarehouseID for products having QuantityOnHand equal to 0. Sort the results in ascending order by WarehouseID.
Write an SQL statement to display all data on products having a QuantityOnHand greater than 0.
Write an SQL statement to display the SKU and SKU_Description for products having QuantityOnHand equal to 0.
Write an SQL statement to display all of the columns using the SQL asterisk (*) wildcard character.
Write an SQL statement to display unique WarehouseIDs.
Write an SQL statement to display all of the columns without using the SQL asterisk (*) wildcard character.
Write an SQL statement to display WarehouseID.
Write an SQL statement to display SKU_Description and SKU.
List several consequences of a poorly designed database.
Name the five DBMS products described in this chapter, and compare them in terms of power, features, and ease of use.
Give an example of a database application that has hundreds of users and a very large and complicated database. Use an example other than one in Figure 1-7.
Show your two tables from question 1.6 without the columns that represent the relationships. Explain how the value of your two tables is diminished without the relationships.
For the tables you created in Review Question 1.6., what are the primary keys of each table? Do you think that any of these primary keys could be surrogate keys? Are any of these keys composite keys?
Give an example of two related tables other than one in this book. Use the STUDENT and GRADE tables in Figure 1-5 as an example pattern for your tables. Name the tables and columns using the conventions in this book.
Give examples of a single-user database application and a multiuser database application other than ones shown in Figure 1-7.
What problem can occur when a database is processed by more than one user?
Explain the problems that can occur when changing the name of a table.
Answer Review Question 8.10, but use the SQL EXISTS comparison operator.
Explain ways in which inconsistent values arise.
Give an example in which the general-purpose remarks column makes it difficult to obtain values for a foreign key.
Explain why the form and report in Figure 5-19 indicate that the underlying relationship is N:M.
Explain why two entities are needed to model multivalued attributes.
What is the difference between an alternate key and a candidate key?
Describe two ways to represent a 1:1 strong entity relationship. Give an example other than one in this chapter.
Describe a MUST constraint. Give an example other than one in this chapter.AGENT (AgentID, {other attributes}CLIENT (ClientID, AgentID{other attributes}PROPERTY (PropertyID, {other attributes}AGENT_CLIENT_PROPERTY (AgentID, ClientID, PropertyID, {other attributes}
Explain the need for each of the actions in Figure 6-29(a). Parent Required Action on Parent Action on Child Insert None. Get a parent. Prohibit. Change children's foreign Modify key or foreign key OK, if new foreign key value matches key values to match new value (cascade update). existing parent.
Explain the need for each of the actions in Figure 6-29(b).Actions When Child Is Required Child Required Action on Parent Get a child. Prohibit. Update the foreign key of (at least one) child. Prohibit. None. Action on Child None. Insert Modify key or foreign key If not last child, OK. If last
Write an SQL statement to create a view named Customer04DataView based on the CUSTOMER_04 table. In the view, include the values of Customer ID, LastName as CustomerLastName, FirstName as CustomerFirstName, and EmailAddress in that order. Run this statement to create the view, and then test the
Explain how any and all pertain to EXISTS and NOT EXISTS.
Under what conditions is the following SQL statement valid?
Extend your answer to Review Question 8.29 to add C1 when C1 is to be NOT NULL.
Which data type changes are the least risky?
Which data type changes are the most risky?
Explain the difference between a database administrator and a data administrator.
Explain the trade-off that exists in concurrency control.
List seven important DBA tasks.
Why are inconsistent values in foreign keys particularly troublesome?
Describe two ways to identify inconsistent values. Are these techniques certain to find all inconsistent values? What other step can be taken?
How does a null value differ from a blank value?
What are three interpretations of null values? Use an example in your answer.
Show SQL for determining the number of null values in the column EmployeeFirstName of the table EMPLOYEE.
Describe the general-purpose remarks column problem.
Give an example in which the general-purpose remarks column causes difficulties when multiple values are stored in the same column. How is this problem solved?
Why should one be wary of general-purpose remarks columns?
Describe the two phases in designing databases that arise from the development of new information systems.
In general terms, explain how a data model could be used to design a database for a small video rental store.
is the author of the entity-relationship data model?
Define entity. Give an example of an entity (other than one presented in this chapter).
Explain the difference between an entity class and an entity instance.
Define attribute. Give an example attribute for the entity in your answer to Review Question 5.5.
Define identifier. Give an example identifier for the entity in your answer to Review Question 5.5.
Give an example of a composite identifier.
Define relationship. Give an example of a relationship (other than one presented in this chapter). Name your relationship.
Explain the difference between a relationship class and a relationship instance.
What is the degree of a relationship? Give an example of a relationship of degree three (other than one presented in this chapter).
Explain the difference between an entity and a table. Why is this difference important?
What does cardinality mean?
Define the terms maximum cardinality and minimum cardinality.
Give examples of 1:1, 1:N, and N:M relationships (other than those presented in this chapter). Draw two E-R diagrams for each of your examples: one using the traditional diamond notation and one using IE Crow€™s Foot notation. AGENT 1:1 CAR AGENT_VEHICLE AGENT 1:N CLIENT AGENT_CLIENT
Give an example for which the maximum cardinality must be an exact number (other than those presented in this chapter).
Give examples of M-M, M-O, O-M, and O-O relationships (other than those presented in this chapter). Draw two E-R diagrams for each of your examples: one using the traditional diamond notation and one using IE Crow’s Foot notation.
Explain, in general terms, how the traditional E-R model, the IE Crow’s Foot version, the IDEF1X version, and the UML version differ. Which version is used primarily in this text?
What is an ID-dependent entity? Give an example of an ID-dependent entity (other than one presented in this chapter).
Explain how to determine the minimum cardinality of both sides of an ID-dependent relationship.
What rules exist when creating an instance of an ID-dependent entity? What rules exist when deleting the parent of an ID-dependent entity?
What is an identifying relationship? How is it used?
Explain why the relationship between BUILDING and APARTMENT discussed on page 206 is an identifying relationship.
What is a weak entity? How do weak entities relate to ID-dependent entities?
What distinguishes a weak entity from a strong entity that has a required relationship to another entity?
Define subtype and supertype. Give an example of a subtype–supertype relationship (other than one presented in this chapter).
Explain the difference between exclusive subtypes and inclusive subtypes. Give an example of each.
What is a discriminator?
Explain the difference between IS-A and HAS-A relationships.
What is the most important reason for using subtypes in a data model?
Describe the relationship between the structure of forms and reports and the data model.
Explain two ways forms and reports are used for data modeling.
Explain why the form and report in Figure 5-15 indicate that the underlying relationship is 1:1.
The form and report in Figure 5-15 show specific instances, but not the full range of possible relationship values between MEMBER and LOCKER. For example, unassigned LOCKERs may not show up on the report.
Describe two tests for determining if an entity is a strong entity.
Why does the form in Figure 5-17 not indicate that the underlying relationship is 1:N? What additional information is required to make that assertion?
Explain why two forms or reports are usually needed to infer maximum cardinality.
How can you assess minimum cardinality for the entities in the form in Figure 5-17?
Name three patterns that use ID-dependent relationships.

Showing 300 - 400 of 781

Database Processing Fundamentals, Design, and Implementation 14th edition David M. Kroenke, David J. Auer - Solutions

Step by Step Answers