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

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. Save the script as WPC-Insert-DEPARTMENT-Data.sql for future use.]
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 the script as WPC-Insert-EMPLOYEE-Data.sql for future use.]
Write INSERT statements to add the data shown in Figure 2-43 to the PROJECT table. Run these statements to populate the PROJECT table. [Hint: Write and test an SQL script, and then run the script. Save the script as WPC-Insert-PROJECT-Data.sql for future use.]
Write INSERT statements to add the data shown in Figure 2-45 to the ASSIGNMENT table. Run these statements to populate the ASSIGNMENT table. [Write and test an SQL script, and then run the script. Save the script as WPC-Insert-ASSIGNMENT-Data.sql for future use.]
Why were the tables populated in the order shown in Review Questions 7.51 – 7.54?
Assume that you have a table named NEW_EMPLOYEE that has the columns Department, Email, FirstName, and LastName, in that order. Write an INSERT statement to add all of the rows from the table NEW_EMPLOYEE to EMPLOYEE. Do not attempt to run this statement!
Write an UPDATE statement to change the phone number of employee with EmployeeNumber 11 to 360-287-8810. Run this SQL statement.
Write an UPDATE statement to change the department of employee with EmployeeNumber 5 to Finance. Run this SQL statement.
Write an UPDATE statement to change the phone number of employee with EmployeeNumber 5 to 360-287-8420. Run this SQL statement.
Combine your answers to Review Questions 7.58 and 7.59 into one SQL statement. Run this statement.
Write an UPDATE statement to set HoursWorked to 60 for every row in ASSIGNMENT having the value 10 for EmployeeNumber. Run this statement.
Assume that you have a table named NEW_EMAIL that has new values of Email for some employees. NEW_EMAIL has two coluumns: EmployeeNumber and NewEmail. Write an UPDATE statement to change the values of Email in EMPLOYEE tothose in the NEW_EMAIL table. Do not run this statement.
Write one DELETE statement that will delete all data for project ‘2015 Q3 Product Plan’ and all of its rows in ASSIGNMENT. Do not run this statement.
Write a DELETE statement that will delete the row for the employee named “Smith”. Do not run this statement. What happens if this employee has rows in ASSIGNMENT?
What is an SQL view? What purposes do views serve?
What is the limitation on SELECT statements used in SQL Views?
Write an SQL statement to create a view named EmployeePhoneView that shows the values of EMPLOYEE.LastName as EmployeeLastName, EMPLOYEE.FirstName as EmployeeFirstName, and EMPLOYEE.Phone as EmployeePhone. Run this statement, and then test the view with an SQL SELECT statement.
Write an SQL statement to create a view named FinanceEmployeePhoneView that shows the values of EMPLOYEE.LastName as EmployeeLastName, EMPLOYEE.First-Name as EmployeeFirstName, and EMPLOYEE.Phone as EmployeePhone for employees who work in the Finance department. Run this statement, and then test
Write an SQL statement to create a view named CombinedNameEmployeePhoneView that shows the values of EMPLOYEE.LastName, EMPLOYEE.FirstName, and EMPLOYEE.Phone as EmployeePhone, but that combines EMPLOYEE.LastName and EMPLOYEE.FirstName into one column named EmployeeName that displays the employee
Write an SQL statement to create a view named EmployeeProjectAssignmentView that shows the values of EMPLOYEE.LastName as EmployeeLastName, EMPLOYEE.First-Name as EmployeeFirstName, EMPLOYEE.Phone as EmployeePhone, and PROJECT.Name as ProjectName. Run this statement, and then test the view with an
Write an SQL statement to create a view named DepartmentEmployeeProjectAssignmentView that shows the values of EMPLOYEE.LastName as EmployeeLastName, EMPLOYEE.FirstName as EmployeeFirstName, EMPLOYEE.Phone as EmployeePhone, DEPARTMENT.DepartmentName, Department.PHONE as DepartmentPhone, and
Write an SQL statement to create a view named ProjectHoursToDateView that shows the values of PROJECT.ProjectID, PROJECT.Name as ProjectName, PROJECT.MaxHours as ProjectMaxHour and the sum of ASSIGNMENT.HoursWorked as ProjectHoursWorkedToDate. Run this statement, and then test the view with an SQL
Describe how views are used to provide an alias for tables. Why is this useful?
Explain how views can be used to improve data security.
Explain how views can be used to provide additional trigger functionality.
Give an example of a view that is clearly updateable.
Give an example of a view that is clearly not updateable.
Summarize the general idea for determining whether a view is updateable.
If a view is missing required items, what action on the view is definitely not allowed?
Explain the paradigm mismatch between SQL and programming languages.
How is the mismatch in your answer to question 7.80 corrected?
Describe the SQL/PSM component of the SQL standard. What are PL/SQL and T-SQL? What is the MySQL equivalent?
What is a user-defined function?
Create and test a user-defined function named LastNameFirst that combines two parameters named FirstName and LastName into a concatenated name field formatted LastName, FirstName (including the comma and space).
Create and test a view called EmployeeDepartmentDataView that contains the employee name concatenated and formatted as LastName, FirstName in a field named EmployeeName, EMPLOYEE.Department, DEPARTMENT.OfficeNumber,DEPARTMENT.Phone as DepartmentPhone, and EMPLOYEE.Phone as EmployeePhone. Run this
What is a trigger?
What is the relationship between a trigger and a table or view?
Name nine possible trigger types.
Explain, in general terms, how new and old values are made available to a trigger.
Describe four uses for triggers.
Assume that the View Ridge Gallery will allow a work to be deleted from WORK if it has never been sold. Explain, in general terms, how to use a trigger to accomplish such a deletion.
Assume that the Wedgewood Pacific Corporation will allow a row to be deleted from EMPLOYEE if the employee has no project assignments. Explain, in general terms, how to use a trigger to accomplish such a deletion.
What is a stored procedure? How do they differ from triggers?
Summarize how to invoke a stored procedure.
Summarize the key advantages of stored procedures.
Review the three sources of database design and implementation.
Describe why database redesign is necessary.
Explain the following statement in your own words: “Information systems and organizations create each other.” How does this relate to database redesign?
Suppose that a table contains two nonkey columns: AdvisorName and AdvisorPhone. Further suppose that you suspect that AdvisorPhone → AdvisorName. Explain how to examine the data to determine if this supposition is true.
Write a subquery, other than one in this chapter, that is not a correlated subquery.
Explain the following statement: “The processing of correlated subqueries is nested, whereas that of regular subqueries is not.”
Write a correlated subquery, other than one in this chapter.
Explain how the query in your answer to Review Question 8.5 differs from the query in your answer to Review Question 8.7.
Explain what is wrong with the correlated subquery SQL-Query-CH08-03 on page 400.
Write a correlated subquery to determine whether the data support the supposition in question 8.4.
Explain the meaning of the SQL EXISTS comparison operator.
Explain the processing of SQL-Query-CH08-06 on page 402.
Using the View Ridge Gallery database, write a query that will display the names of any customers who are interested in all artists.
Explain how the query in your answer to question 8.15 works.
Why is it important to analyze the database before implementing database redesign tasks? What can happen if this is not done?
Explain the process of reverse engineering.
Why is it important to carefully evaluate the results of reverse engineering?
What is a dependency graph? What purpose does it serve?
Explain the dependencies for WORK in the graph in Figure 8-4. TRANS CheckSalesPrice ARTIST CUSTOMER WORK TRANS TRANS AskingPricolnitialValue ArtistWorkNotViow Table View Trigger ArtistWorkTotalNetView
What sources are used when creating a dependency graph?
Explain two different types of test databases that should be used when testing database redesign changes.
Describe the process of changing a table name.
Considering Figure 8-4, describe the tasks that need to be accomplished to change the name of the table WORK to WORK_VERSION2.
Explain how views can simplify the process of changing a table name.
Show an SQL statement to add an integer column C1 to the table T2. Assume that C1 is NULL.
Show an SQL statement to drop the column C1 from table T2.
Describe the process for dropping primary key C1 and making the new primary key C2.
Show an SQL statement to change a column C1 to Char(10) NOT NULL. What conditions must exist in the data for this change to be successful?
Explain how to change the minimum cardinality when a child that was required to have a parent is no longer required to have one.
Explain how to change the minimum cardinality when a child that was not required to have a parent is now required to have one. What condition must exist in the data for this change to work?
Explain how to change the minimum cardinality when a parent that was required to have a child is no longer required to have one.
Explain how to change the minimum cardinality when a parent that was not required to have a child is now required to have one.
Describe how to change the maximum cardinality from 1:1 to 1:N. Assume that the foreign key is on the side of the new child in the 1:N relationship.
Describe how to change the maximum cardinality from 1:1 to 1:N. Assume that the foreign key is on the side of the new parent in the 1:N relationship.
Assume that tables T1 and T2 have a 1:1 relationship. Assume that T2 has the foreign key. Show the SQL statements necessary to move the foreign key to T1. Make up your own names for primary and foreign keys.
Explain how to transform a 1:N relationship into an N:M relationship.
Suppose that tables T1 and T2 have a 1:N relationship. Show the SQL statements necessary to fill an intersection T1_T2_INT. Make up your own names for primary and foreign keys.
Explain how the reduction of maximum cardinalities causes data loss.
Using the tables in your answer to Review Question 8.44, show the SQL statements necessary to change the relationship back to 1:N. Assume that the first row in the qualifying rows of the intersection table is to provide the foreign key. Use the keys and foreign keys from your answer to Review
Using the results of your answer to Review Question 8.46, explain what must be done to convert this relationship to 1:1. Use the keys and foreign keys from your answer to question 8.46.
In general terms, what must be done to add a new relationship?
Suppose that tables T1 and T2 have a 1:N relationship, with T2 as the child. Show the SQL statements necessary to remove table T1. Make your own assumptions about the names of keys and foreign keys.
What are the risks and problems of forward engineering?
Briefly describe five diffiExplain the difference between a database administrator and a data administrator.cult problems for organizations that create and use multiuser databases.
Summarize the DBA’s responsibilities for managing database structure.
What is configuration control? Why is it necessary?
Explain the meaning of the word inappropriately in the phrase “one user's work does not inappropriately influence another user’s work.”
Define an atomic transaction and explain why atomicity is important.
Explain the difference between concurrent transactions and simultaneous transactions. How many CPUs are required for simultaneous transactions?
Give an example, other than the one in this text, of the lost update problem.
Explain the difference between an explicit and an implicit lock.
What is lock granularity?
Explain the difference between an exclusive lock and a shared lock.

Showing 500 - 600 of 781

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

Step by Step Answers