Questions and Answers of Database Processing Fundamentals

Summarize the two sides in the argument about the importance of weak but not ID-dependent entities.
Give an example of the line-item pattern as it could be used to describe the contents of a shipment. Assume that the shipment includes the names and quantities of various items as well as each
What entity type should come to mind when you see the words “For use by” in a form?
Give examples of 1:1, 1:N, and N:M recursive relationships (other than those presented in this chapter).
Explain why the data modeling process must be iterative. Use the Highline University example.
Identify the three major tasks for transforming a data model into a database design.
What is the relationship between entities and tables? Between attributes and columns?
Why is the choice of the primary key important?
What are the three characteristics of an ideal primary key?
What is a surrogate key? What are its advantages
When should you use a surrogate key?
Describe two disadvantages of surrogate keys.
What does the notation LastName (AK2.2) mean?
Name four column properties.
Explain why primary keys may never be null, but alternate keys can be null.
List five generic data types.
Describe three ways that a default value can be assigned.
What is a domain constraint? Give an example.
What is a range constraint? Give an example.
What is an intrarelation constraint? Give an example.
What is an interrelation constraint? Give an example.
What tasks should be accomplished when verifying normalization of a database design?
Describe how to represent a 1:N strong entity relationship. Give an example other than one in this chapter.
Describe how to represent an N:M strong entity relationship. Give an example other than one in this chapter.
What is an intersection table? Why is it necessary?
What is the difference between the table that represents an ID-dependent association entity and an intersection table?
Describe how to represent an N:M recursive relationship. Give an example other than one in this chapter.
List four uses for ID-dependent entities.
Describe how to represent an association entity relationship. Give an example other than one in this chapter.
Describe how to represent a multivalued attribute entity relationship. Give an example other than one in this chapter.
Describe how to represent a archetype/instance entity relationship. Give an example other than one in this chapter.
What happens when an instance entity is given a non-ID-dependent identifier? How does this change affect relationship design?
What happens when the parent in an ID-dependent relationship is given a surrogate key? What should the key of the child become?
Describe how to represent a mixed entity relationship. Give an example other than one in this chapter.
Describe how to represent a supertype/subtype entity relationship. Give an example other than one in this chapter.
Describe two ways to represent a 1:1 recursive relationship. Give an example other than one in this chapter.
Describe how to represent a 1:N recursive relationship. Give an example other than one in this chapter.
In general, how are ternary relationships represented? Explain how a binary constraint may impact such a relationship.
Describe a MUST NOT constraint. Give an example other than one in this chapter.
Describe a MUST COVER constraint. Give an example other than one in this chapter.
Explain in general terms what needs to be done to enforce minimum cardinality.
State which of the actions in Figure 6-29 must be applied for M-O relationships, O-M relationships, and M-M relationships.
Explain what must be done for the DBMS to enforce required parents.
What design decisions must be made to enforce required parents?
Explain why the DBMS cannot be used to enforce required children.
What is a trigger? How can triggers be used to enforce required children?
Explain why the enforcement of M-M relationships is particularly difficult.
Explain the need for each of the design decisions in Figure 6-34. Design Decisions to Be Made Design Documentation Relationship Minimum Cardinality • Update cascade or prohibit? • Delete cascade
Explain the implications of each of the minimum cardinality specifications in Figure 6-40. Cardinality MAX MIN Relationship Parent Child Type Nonidentifying 1:N Nonidentifying 1:N Nonidentifying 1:N
Explain how the association pattern differs from the N:M strong entity pattern. What characteristic of the report in Figure 5-21 indicates that an association pattern is needed?
Explain the rationale for each of the entries in the table in Figure 6-42. TRANS Action on WORK Action on TRANS Is Required (Parent) (Child) Parent Will be created by INSERT trigger on WORK. Insert
What does DDL stand for? List the SQL DDL statements.
What does DML stand for? List the SQL DML statements.
Explain the meaning of the following expression: IDENTITY (4000, 5).
Create a database named CH07_RQ_TABLES.
If you are using Microsoft SQL Server, Oracle Database, or MySQL, create and save an SQL script named CH07-RQ-TABLES-Tables-Data-and-Views.sql to hold the answers to Review Questions 7.7–7.40. Use
Write and run an SQL CREATE TABLE statement to create the CUSTOMER_01 table.
Write and run an SQL CREATE TABLE statement to create the CUSTOMER_02 table.
Are there any significant differences between the CUSTOMER_01 and CUSTOMER_02 tables? If so, what are they?
Write and run an SQL CREATE TABLE statement to create the CUSTOMER_03 table.
Are there any significant differences between the CUSTOMER_02 and CUSTOMER_03 tables? If so, what are they?
Write and run an SQL CREATE TABLE statement to create the CUSTOMER_04 table.
Are there any significant differences between the CUSTOMER_03 and CUSTOMER_04 tables? If so, what are they?
Write and run an SQL CREATE TABLE statement to create the SALE_01 table. Note that the foreign key is EmailAddress, which references CUSTOMER_01. EmailAddress. In this database, CUSTOMER_01 and
In Review Question 7.14, how did you implement the ON UPDATE referential integrity action? Why?
Are there any significant differences between the CUSTOMER_01 and SALE_01 tables? If so, what are they?
Could we have created the SALE_01 table before creating the CUSTOMER_01 table? If not, why not?
Write and run an SQL CREATE TABLE statement to create the SALE_02 table. Note that the foreign key is CustomerID, which references CUSTOMER_04.CustomerID. In this database, CUSTOMER_04 and SALE_02
In Review Question 7.18, how did you implement the ON UPDATE referential integrity action? Why?
Are there any significant differences between the SALE_01 and SALE_02 tables? If so, what are they?
Could we have created the SALE_02 table before creating the CUSTOMER_04 table? If not, why not?
Write and run a set of SQL INSERT statements to populate the CUSTOMER_01 table.
Write and run a set of SQL INSERT statements to populate the CUSTOMER_02 table. Do not use a bulk INSERT command.
Are there any significant differences between the sets of SQL INSERT statements used to populate the CUSTOMER_01 and CUSTOMER_02 tables? If so, what are they?
Write and run an SQL INSERT statement to populate the CUSTOMER_03 table. Use a bulk INSERT command and the data in the CUSTOMER_01 table.
Are there any significant differences between the sets of SQL INSERT statements used to populate the CUSTOMER_02 and CUSTOMER_03 tables? If so, what are they?
Write and run a set of SQL INSERT statements to populate rows 1 through 3 in the CUSTOMER_04 table. Note that this question involves non-sequential surrogate key values and is based on techniques for
Are there any significant differences between the sets of SQL INSERT statements used to populate the CUSTOMER_02 table and rows 1-3 of the CUSTOMER_04 table? If so, what are they?
Write and run a set of SQL INSERT statements to populate rows 4 through 6 in the CUSTOMER_04 table. Note that this question involves sequential surrogate key values and is based on techniques for
Are there any significant differences between the sets of SQL INSERT statements used to populate the CUSTOMER_02 table and rows 4-6 of the CUSTOMER_04 table? If so, what are they?
Write and run a set of SQL INSERT statements to populate the SALE_01 table.
Are there any significant differences between the sets of SQL INSERT statements used to populate the CUSTOMER_01 table and the SALE_01 table? If so, what are they?
Could we have populated the SALE_01 table before populating the CUSTOMER_01 table? If not, why not?
Write and run a set of SQL INSERT statements to populate the SALE_02 table.
Are there any significant differences between the sets of SQL INSERT statements used to populate the SALE_01 table and the SALE_02 table? If so, what are they?
Could we have populated the SALE_02 table before populating the CUSTOMER_04 table? If not, why not?
Write and run an SQL INSERT statement to insert the following record into the SALE_02 table:What was the result of running this statement? Why did this result occur? DateOf Sale SalelD CustomerlD
Write an SQL statement to create a view named Customer01DataView based on the CUSTOMER_01 table. In the view, include the values of EmailAddress, LastName as CustomerLastName, and FirstName as
Write an SQL statement to create a view named CustomerSalesView based on the the CUSTOMER_04 and SALE_02 tables. In this view, include the values of Customer ID, LastName as CustomerLastName,
Write a CREATE TABLE statement for the DEPARTMENT table.
Write a CREATE TABLE statement for the EMPLOYEE table. Email is required and is an alternate key, and the default value of Department is Human Resources. Cascade updates but not deletions from
Write a CREATE TABLE statement for PROJECT table. The default value for MaxHours is 100. Cascade updates but not deletions from DEPARTMENT to EMPLOYEE.
Write a CREATE TABLE statement for the ASSIGNMENT table. Cascade only deletions from PROJECT to ASSIGNMENT; do not cascade either deletions or updates from EMPLOYEE to ASSIGNMENT.
Modify your answer to Review Question 7.7 to include the constraint that StartDate be prior to EndDate.
Write an ALTER statement to add the column AreaCode to EMPLOYEE. Assume that AreaCode is not required.
Write an ALTER statement to remove the column AreaCode from EMPLOYEE.
Write an ALTER statement to make Phone an alternate key in EMPLOYEE.
Write an ALTER statement to drop the constraint that Phone is an alternate key in EMPLOYEE.
Write INSERT statements to add the data shown in Figure 1-29 to the DEPARTMENT table. Run these statements to populate the DEPARTMENT table. [Write and test an SQL script, and then run the script.
Write INSERT statements to add the data shown in Figure 1-31 to the EMPLOYEE table. Run these statements to populate the EMPLOYEE table. [Write and test an SQL script, and then run the script. Save

Showing 400 - 500 of 777