Chapter 9 Exercises 9 47, 9 61, 9 73 The questions are below please this is data base management system SQL please help 9 47 Define rollback and rollforward 9 61 Explain what problems can occur in a distributed database that is partitioned but not replicated 9 73 Answer the following questions for the View Ridge Gallery database discussed in Chapter 7 with the tables shown below A Suppose that you are developing a stored procedure to record an artist who has never been in the gallery before, a work for that artist, and a row in the TRANS table to record the date acquired and the acquisition price How will you declare the boundaries of the transaction What transaction isolation level will you use B Suppose that you are writing a stored procedure to change values in the CUSTOMER table What transaction isolation level will you use C Suppose that you are writing a stored procedure to record a customers purchase Assume that the customers data are new How will you declare the boundaries of the transaction What isolation level will you use D Suppose that you are writing a stored procedure to check the validity of the intersection table Specifically, for each customer, your procedure should read the customers transaction and determine the artist of that work Given the artist, your procedure should then check to ensure that an interest has been declared for that artist in the intersection table If there is no such intersection row, your procedure should create one How will you set the boundaries of your transaction What isolation level will you use What cursor types (if any) will you use Chapter 10A Exercises 10A 36, A F Only Project Question 10 36 is based on the View Ridge Gallery database discussed in this chapter 10 36 If you have not already installed SQL Server 2008 R2 (or do not otherwise have it available to you), you need to install a version of it at this point Write SQL statements to accomplish the following tasks and submit them to SQL Server 2008 R2 via the Microsoft SQL Server Management Studio A In the SQL Server Management Studio folder structure in your My Documents folder, create a folder named DBP e12 VRG CH10 PQ Database in the Projects folder Use this folder to save and store sql scripts containing the SQL statements that you are asked to create in the remaining questions in this section B Create an SQL Server 2008 R2 database named VRG CH10 PQ C In the VRG CH10 PQ database, create the VRG database tables shown in Figure below, except do notcreate the NationalityValues constraint D Populate your database with the sample data from Figure below E Create all the VRG views discussed in the Chapter 7 section on SQL views ADDITIONAL INFORMATIOn FOR THE ABOVE QUESTION 7 SQL for Database Construction and Application Processing Chapter Objectives To create and manage table structures using SQL statements To understand how referential integrity actions are implemented in SQL statements To create and execute SQL constraints To understand several uses for SQL views To use SQL statements to create and use views To understand how SQL is used in application programming To understand SQL Persistent Stored Modules (SQL PSM) To understand how to create and use triggers To understand how to create and use stored procedures In Chapter 2, we introduced SQL and classified SQL statements into three categories data definition language (DDL) statements, which are used for creating tables, relationships, and other database structures data manipulation language (DML) statements, which are used for querying, inserting, updating, and deleting data SQL Persistent stored modules (SQL PSM) statements, which extend SQL by adding procedural programming capabilities, such as variables and flow of control statements, that provide some programmability within the SQL framework In Chapter 2, we discussed only DML query statements This chapter describes and illustrates SQL DDL statements for constructing databases, SQL DML statements for inserting, modifying, and deleting data, and SQL statements to create and use SQL Views We also discuss how to embed SQL statements into application programs and SQL PSM, and how to use SQL PSM to create triggers and stored procedures The knowledge in this chapter is important whether you become a database administrator or an application programmer Even if you will not construct SQL triggers or stored procedures yourself, it is important that you know what they are, how they work, and how they influence database processing The View Ridge Gallery Database In Chapter 6, we introduced the View Ridge Gallery, a small art gallery that sells contemporary North American and European fine art and provides art framing services We also developed a data model and database design for a database for the View Ridge Gallery Our final database design for View Ridge is shown in Figure 7 1 In this chapter, we will use SQL to build the View Ridge database based on that design SQL DDL, DML, and a New Type of Join Figure 7 1 Final View Ridge Gallery Database Design F Write a stored procedure named PrintArtistData to read the ARTIST table and display the artist data using the Transact SQL PRINT command Create test data and demonstrate that your stored procedure works THIS IS IT SIR MADAM, PLEASE HELP ME WITH THIS, THANKS A BUNCH IN ADVANCE

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Sep 05, 2024

Chapter 9 Exercises: #9.47, 9.61, 9.73 The questions are below please this is data base management system SQL please help 9.47 Define rollback and rollforward.

Chapter 9 Exercises: #9.47, 9.61, 9.73

The questions are below please this is data base management system SQL please help

9.47

Define rollback and rollforward.

9.61

Explain what problems can occur in a distributed database that is partitioned but not replicated.

9.73

Answer the following questions for the View Ridge Gallery database discussed in Chapter 7 with the tables shown below.

image text in transcribed

A.Suppose that you are developing a stored procedure to record an artist who has never been in the gallery before, a work for that artist, and a row in the TRANS table to record the date acquired and the acquisition price. How will you declare the boundaries of the transaction? What transaction isolation level will you use?
B.Suppose that you are writing a stored procedure to change values in the CUSTOMER table. What transaction isolation level will you use?
C.Suppose that you are writing a stored procedure to record a customers purchase. Assume that the customers data are new. How will you declare the boundaries of the transaction? What isolation level will you use?
D.Suppose that you are writing a stored procedure to check the validity of the intersection table. Specifically, for each customer, your procedure should read the customers transaction and determine the artist of that work. Given the artist, your procedure should then check to ensure that an interest has been declared for that artist in the intersection table. If there is no such intersection row, your procedure should create one. How will you set the boundaries of your transaction? What isolation level will you use? What cursor types (if any) will you use?

Chapter 10A Exercises: 10A.36, #A - #F Only

Project Question 10.36 is based on the View Ridge Gallery database discussed in this chapter.

10.36

If you have not already installed SQL Server 2008 R2 (or do not otherwise have it available to you), you need to install a version of it at this point. Write SQL statements to accomplish the following tasks and submit them to SQL Server 2008 R2 via the Microsoft SQL Server Management Studio.

A.In the SQL Server Management Studio folder structure in your My Documents folder, create a folder named DBP-e12-VRG-CH10-PQ-Database in the Projects folder. Use this folder to save and store *.sql scripts containing the SQL statements that you are asked to create in the remaining questions in this section.
B.Create an SQL Server 2008 R2 database named VRG-CH10-PQ.
C.In the VRG-CH10-PQ database, create the VRG database tables shown in Figure below, except do notcreate the NationalityValues constraint.

image text in transcribed

D.Populate your database with the sample data from Figure below.

image text in transcribed

E.Create all the VRG views discussed in the Chapter 7 section on SQL views.

ADDITIONAL INFORMATIOn FOR THE ABOVE QUESTION-----

7 SQL for Database Construction and Application Processing

Chapter Objectives

To create and manage table structures using SQL statements
To understand how referential integrity actions are implemented in SQL statements
To create and execute SQL constraints
To understand several uses for SQL views
To use SQL statements to create and use views
To understand how SQL is used in application programming
To understand SQL/Persistent Stored Modules (SQL/PSM)
To understand how to create and use triggers
To understand how to create and use stored procedures

In Chapter 2, we introduced SQL and classified SQL statements into three categories:

data definition language (DDL) statements, which are used for creating tables, relationships, and other database structures.
data manipulation language (DML) statements, which are used for querying, inserting, updating, and deleting data.
SQL/Persistent stored modules (SQL/PSM) statements, which extend SQL by adding procedural programming capabilities, such as variables and flow-of-control statements, that provide some programmability within the SQL framework.

In Chapter 2, we discussed only DML query statements. This chapter describes and illustrates SQL DDL statements for constructing databases, SQL DML statements for inserting, modifying, and deleting data, and SQL statements to create and use SQL Views. We also discuss how to embed SQL statements into application programs and SQL/PSM, and how to use SQL/PSM to create triggers and stored procedures.

The knowledge in this chapter is important whether you become a database administrator or an application programmer. Even if you will not construct SQL triggers or stored procedures yourself, it is important that you know what they are, how they work, and how they influence database processing.

The View Ridge Gallery Database

In Chapter 6, we introduced the View Ridge Gallery, a small art gallery that sells contemporary North American and European fine art and provides art framing services. We also developed a data model and database design for a database for the View Ridge Gallery. Our final database design for View Ridge is shown in Figure 7-1. In this chapter, we will use SQL to build the View Ridge database based on that design.

SQL DDL, DML, and a New Type of Join

image text in transcribed

Figure 7-1 Final View Ridge Gallery Database Design

F. Write a stored procedure named PrintArtistData to read the ARTIST table and display the artist data using the Transact-SQL PRINT command. Create test data and demonstrate that your stored procedure works.

THIS IS IT SIR/MADAM, PLEASE HELP ME WITH THIS, THANKS A BUNCH IN ADVANCE.