Chapter Seven Using SQL in Applications

Chapter Seven Using SQL in Applications

ANSWERS TO GROUP I QUESTIONS

7.1For the data model in Figure 7-2, explain why it is appropriate to model WORK and TRANSACTION as ID-dependent entities.

Logically, an art work cannot exist unless some artist has created it.

7.2 Why is minimum cardinality on the relationship from ARTIST to WORK not one?

An artist may be recorded in the database even if none of his or her works have appeared in the gallery. This is done to record customer preferences for artists whose works might appear in the future. Thus, an artist may have from zero to many works.

7.3Why is the identifier of WORK the composite (Title, Copy)?

The identifier of WORK is the composite (Title, Copy) because, in the case of lithographs and photos, there may be many copies of a given title.

7.4 Justify the decision to make the relationship between CUSTOMER and TRANSACTION optional in both directions.

Some art is purchased from the Artist. In this case the Transaction would record the AcqusitionPrice but since no Customer has purchased the Work, the Transaction would not have a Customer. We may have Customers that are interested in an Artist but have not purchased a Work. In this case, the Customer would have no Transaction.

7.5 Make a list of three possible missing entities for the N:M relationship between CUSTOMER and ARTIST. Explain why (or why not) they should be included in the database design.

Inquiry: Someone asks about an artist. Should be included since an event, the inquiry, occurred. In all cases, you must know which Artist the Customer is interested in. Something must happen in order for you to know.

Visit: Assume you have a showing of an artist’s work and the customer comes to the viewing. Should be included since an event, the inquiry, occurred. In all cases, you must know which Artist the Customer is interested in. Something must happen in order for you to know.

Call: Assume a customer calls looking for a work from an artist. Should be included since an event, the inquiry occurred. In all cases, you must know which Artist the Customer is interested in. Something must happen in order for you to know.

7.6 Explain what is wrong with the database design in Figure 7-2(b).

The keys for WORK and TRANSACTION are huge and the key for CUSTOMER is doubtful; many customers may not have an e-mail address. Because of these problems, this design cries out for surrogate keys.

7.7 In the surrogate key data model in Figure 7-3(a), the WORK and TRANSACTION were changed from an ID-dependent entity to a strong entity. Why was this done?

This is done because their primary keys would be too large and complex. The primary key for Work would be Artist Name, Copy, and Title. The primary key for Transaction would be Artist Name, Date Acquired, Copy, and Title.

7.8 Explain the meaning of the expression IDENTITY (4000, 5).

Identity defines a surrogate key. The 4000 is the starting point for the surrogate key values and the 5 is the increment for each additional surrogate key. The surrogate key values will be 4000, 4005, 4010, 4015 and so forth.

7.9 Summarize and justify the referential integrity actions on relationship parents for the design in Figure 7-3(b).

For Customer to Customer_Artist_Int, both Updates and Deletes will be Cascaded. This is also true for the Artist to Customer_Artist_int. This is normal for intersection tables because there is no need for an intersection if it does not have both parents. From the Artist to the Work, the Delete action is to Restrict. You would not want to delete an Artist if you still have Works by that Artist. The Update action is to cascade. If the identifier for an Artist changed, you would still want to keep the Artist related to his/her Work. The same actions apply to the relationship between Work and Transaction. If a Work is deleted, cascade the delete to the Work’s Transactions and if the Work identifier changes, keep its Transactions related to the Work. This same rational applies to the Customer to Transaction relationship.

7.10 Describe the potential problem that exists when deleting a row in WORK or TRANSACTION.

Whenever a WORK is inserted, a related TRANSACTION row must be created for it to hold the DateAcquired and AcquisitionPrice. Therefore, all WORKs will have at least one TRANSACTION. Using the preceding deletion rule, however, no TRANSACTION can be deleted that has a WORK. Therefore, it will be impossible to delete a WORK, even if it has never been sold.

7.11 Summarize and justify the referential integrity actions on relationship children for the design in Figure 7-3(b).

All updates are restricted. For CUSTOMER_ARTIST_INT, WORK, and TRANSACTION.WorkID, there should never be a reason to change a foreign key value; and restricted means never allow an update to any of them. For TRANSACTION.CustomerID, there is a need to update CustomerID when the work is sold. After that, however, CustomerID should never be changed. Thus, if CustomerID is null, it can be updated; otherwise, it cannot be updated. This rule, too, will need to be implemented in a trigger.

Deletions of WORK and TRANSACTION from the child side are marked as restricted, but for different reasons. If TRANSACTION.CustomerID is not null, the row is never to be deleted. If it is null, it can be deleted only if TRANSACTION is the only transaction for a WORK and if WORK itself is being deleted. This pair of deletions will need to be done in a trigger, as mentioned previously. Finally, a WORK can be deleted if it is related to a single TRANSACTION having a null value of CustomerID. Rows in CUSTOMER_ARTIST_INT can be deleted either via cascading deletions or via application when a customer indicates he or she no longer has an interest in a given artist.

7.12Explain the unusual characteristic of the columns BirthDate and DeceasedDate for the database design in Figure 7-4.

The users at View Ridge Gallery did not care about the particular month, day, and year of an artist’s birth or death. They just wanted to know the year.

7.13Give an example of a range CHECK CONSTRAINT, other than one in this chapter.

Assume that credit hours for courses had to be between 1 and 5 credit hours. The CHECK constraint would be:

CONSTRAINT ValidHours CHECK CreditHours BETWEEN 1 AND 5,

7.14Give an example of an enumerated list CHECK CONSTRAINT, other than one in this chapter.

Assume you wanted to make sure that only valid grades were entered in a database that stored grades earned in a class. The CHECK constraint would be:

CONSTRAINT ValidGrades CHECK Grade IN (‘A’, ‘B’, ‘C’, ‘D’, ‘F’),

7.15Give an example of a CHECK CONSTRAINT that uses two or more columns, other than one in this chapter.

Assume a student has a HoursAttempted and a HoursEarned in semester record. The student cannot earn more credit hours than they attempt. The CHECK constraint would be:

CONSTRAINT HoursPass CHECK HoursEarned <= HoursAttempted,

7.16Give an example of a CHECK CONSTRAINT that specifies the format of PartNumber to consist of five decimal numbers (the first of which is 5, 6, or 7; and the last of which cannot be 0).

CONSTRAINT ValidPart CHECK (PartNumber LIKE '[5-7][0-9][0-9][0-9][1-9]'),

7.17Give an example of a CHECK CONSTRAINT that uses a subquery. Do not use the example in this chapter.

Assume any Advisor must be a Faculty member that has a Rank of Full Professor. Assume the AdvisorID is a foreign key in the Student table that references the FACULTY table. The CHECK would be:

CONSTRAINT ValidAdvisor CHECK STUDENT.AdvisorID IN

(SELECT FacultyID FROM FACULTY

WHERE FACULTY.Rank = ‘Full Professor’);

7.18What prohibition does SQL-92 make regarding CHECK CONSTRAINTS?

According to the SQL-92 standard, CHECK constraints may not include the system date.

7.19In general terms, describe a SQL view.

A SQL view is a virtual table that is constructed from other tables or views. A view has no data of its own, but obtains data from tables or other views.

7.20What is the limitation on SELECT statements used in SQL views?

The only limitation on such statements is that they may not contain an ORDER BY clause.

7.21Code a SQL statement to create a view that shows the values of CUSTOMER.State.

CREATE VIEW StateViewAS

SELECT CUSTOMER.State FROM CUSTOMER;

7.22Code a SQL statement to create a view that shows the unique values of CUSTOMER.State.

CREATE VIEW StateViewAS

SELECT DISTINCT CUSTOMER.State FROM CUSTOMER;

7.23Code a SQL statement to create a view that shows Name, City, and State of CUSTOMER.

CREATE VIEW AddressViewAS

SELECT Name, City, State FROM CUSTOMER;

7.24Code a SQL statement to create a view that shows Name, City, and State of CUSTOMER for customers in California.

CREATE VIEW CaliforniaViewAS

SELECT Name, City, State FROM CUSTOMER

WHERE State = ‘CA’;

7.25Code a SQL statement to create a view that shows CUSTOMER.Name and a computed attribute called Location that combines CUSTOMER.City and CUSTOMER.State in a format like ‘Chicago, IL’.

CREATE VIEW AddressViewAS

SELECT Name, City + ‘, ‘ + State AS CityState

FROM CUSTOMER;

7.26Code a SQL statement to create a view that displays the view you created in your answer to question 7.25, but shows only customers in California.

CREATE VIEW CAViewAS

SELECT * FROM AddressView

WHERE CitryState LIKE ‘%CA’;

7.27Code a SQL statement to create a view that shows ARTIST.Name, WORK.Title, and WORK.Description.

CREATE VIEW View7_27 AS

SELECT ARTIST.Name, WORK.Title, WORK.Description

FROM ARTIST, WORK

WHERE ARTIST.ArtistID = WORK.ArtistID;

7.28Code a SQL statement to create a view that shows CUSTOMER.Name, WORK.Title, and ARTIST.Name for all customer purchases.

CREATE VIEW View7_28 AS

SELECT CUSTOMER.Name, WORK.Title, ARTIST.Name

FROM CUSTOMER, TRANSACTION, WORK, ARTIST

WHERE CUSTOMER.CustomerID = TRANSACTION.CustomerID

AND TRANSACTION.WorkID = WORK.WorkID

AND WORK.ArtistID = ARTIST.ArtistID;

7.29Code a SQL statement to create a view that computes the NetPrice (the difference between SalesPrice and AskingPrice) for each customer purchase.

CREATE VIEW NetPriceViewAS

SELECT WorkID, CustomerID, AskingPrice – SalesPrice as NetPrice

FROM TRANSACTION;

7.30Code a SQL statement to create a view that computes the sum of NetPrice for each customer.

SELECT CustomerID, SUM (NetPrice)

FROM NetPriceView

GROUP BY CUSTOMER.CustomerID;

7.31Code a SQL statement to create a view that computes the sum of NetPrice for each combination of customer and artist.

SELECT NetPriceView.CustomerID, ARTIST.ArtistID, SUM (NetPriceView.NetPrice)

FROM NetPriceView, WORK, ARTIST

WHERE NetPriceView.WorkID = WORK.WorkID

AND WORK.ArtistID = ARTIST.ArtistID;

GROUP BY NetPriceView.CustomerID, ARTIST.ArtistID;

7.32Describe how views are used to provide a level of indirection between application programs and tables. Why might this be important?

If an application uses a view, then the source of the underlying data can be changed without impacting any of the application code. We simply need to redefine the view. This indirection can be important when the source of data may change and for other reasons as explained in the next chapter.

7.33Explain how views can be used to improve data security.

Sometimes views are constructed to give different sets of processing permissions to the same table. Two views could be defined for a table, one with read only permission and one with read and update permission.

7.34Explain how views can be used to provide additional trigger functionality.

Views can be used to enable different sets of triggers to be defined for a table. Triggers are assigned to a table or a view, so each view can have its own set of triggers. Thus, there might be different processing rules for the same data that is accessed via different views.

7.35Give an example of a view that is clearly updatable.

Any view that is an image of a base table is updatable. The view NewView created below would be updatable because all columns in the NewView are identical to the columns in the table CUSTOMER.

CREATE VIEW NewViewAS

SELECT * FROM CUSTOMER;

7.36Give an example of a view that is clearly not updatable.

Any view that has columns that are derived from existing columns. For example, NetPrice would not be updatable in the view below.

CREATE VIEW NetPriceViewAS

SELECT WorkID, CustomerID, AskingPrice – SalesPrice as NetPrice

FROM TRANSACTION;

7.37Summarize the general idea for determining whether a view is updatable.

In general, to update a view, the DBMS must be able to associate the column(s) to be updated with a particular row in a particular underlying table. If any required (NOT NULL) columns are missing from the view, the view cannot be used for inserts. Multi-table views may be updatable on the most subordinate table if the primary key or a candidate key for that table is in the view.

7.38If a view is missing required items, what action on the view is definitely not allowed?

Inserts

7.39Explain the difference between a SQL view and an external view, as described in Chapter 2. When are they the same? When are they different?

SQL views are a subset of external views. They can be used to represent some, but not all, external views. They are the same when the View created by SQL statements is identical to the way the user sees the data. They are different when the view created by the SQL statements does not reflect the world as the user sees it.

7.40Explain the paradigm mismatch between SQL and programming languages.

SQL is set-oriented; most SQL statements return a table or a set of rows. Programs, on the other hand, are element- or row-oriented.

7.41How is the mismatch in your answer to question 7.40 corrected?

The results of SQL statements are treated like pseudo-files. A SQL statement is executed and returns a set of rows. A cursor is placed on the first row and is processed. Then, the cursor is moved to the next row and that row is processed, iterating in this way until all rows have been processed.

7.42Define trigger.

A trigger is a stored program that is attached to a table or view. The trigger code is invoked by the DBMS when an insert, update, or delete request is issued on the table or view to which the trigger is attached.

7.43What are PL/SQL and T-SQL?

PL/SQL, or Programming Language for SQL is a proprietary language for the Oracle DBMS. Server triggers are written in a proprietary language called Transact-SQL, or T-SQL.

7.44What is the relationship between a trigger and a table or view?

Triggers are attached to tables. The trigger is fired when an update is made to the table the trigger is assigned to.

7.45Name nine possible trigger types.

BEFORE (Insert, Update, Delete)

INSTEAD OF (Insert, Update, Delete)

AFTER (Insert, Update, Delete)

7.46In general terms, explain how new and old values are made available to triggers.

New values are supplied by prefixing a column name with the expression :new.

Old values are supplied by prefixing a column name with the expression :old.

7.47Describe four possible uses for triggers.

Validity Checking

Assigning Default Values

Updating Views

Enforcing referential integrity actions

7.48Explain, in general terms, how to use a trigger to accomplish a deletion on WORK, given the referential integrity actions defined for the View Ridge Gallery.

This rule can be encoded in an INSTEAD OF DELETE trigger on WORK. Figure 7-18 shows generic code for such a trigger. There should never be a WORK with no related TRANSACTION rows, so if rowcount is zero, an error message is generated. Next, if rowcount is 1, it is necessary to determine if TRANSACTION.CustomerID is null. The SELECT statement is used to count the appropriate rows. If nullCount is 1, the rows will be deleted. The deletions must occur in the order shown. If they are reversed, the DBMS disallows the deletion of WORK because it has a dependent TRANSACTION row.

7.49What is a stored procedure? How does it differ from a trigger?

A stored procedure is a program that performs some common action on database data and that is stored in the database. Unlike triggers, which are attached to a given table or view, stored procedures are attached to the database. They can be executed by any process using the database that has permission to use the procedure.

7.50Summarize the way to invoke a stored procedure.

They can be executed from application programs written in languages such as Java, C#, C++, or VB.Net. They can also be invoked from Web pages using VBScript or JavaScript. Ad hoc users can run them from products such as SQL*Plus in Oracle or from Query Analyzer in SQL Server. Stored procedures can also be run for either Oracle or SQL Server from the Microsoft VisualStudio.NET environment.

7.51Summarize the key advantages of stored procedures.

• Greater security
• Decreased network traffic
• SQL can be optimized
• Code sharing
• Less work
• Standardized processing
• Specialization among developers

7.52Explain, in general terms, what an application program must do to materialize a form like that shown in Figure 7-20.

7.53Can the form in Figure 7-20 be represented by a single view? Why or why not?

ANSWERS TO GROUP II QUESTIONS

Use the data model in Figure 7-23 and the related database design in Figure 7-24 to answer the following questions:

7.54Alter the database design in Figure 7-24 to use surrogate keys. Is this design an improvement over the design in Figure 7-24? Why or why not?

The design would be basically the same except that the EmployeeID and the SerialNumber would be defined as Identities with a starting number and an Increment. The Assignment would also have a single attribute identifier as a surrogate key. This would probably not be an improvement to the design. The composite key for the Assignment has only two attributes so it is relatively simple.

7.55Using the design in Figure 7-24, and keeping in mind the relationship cardinalities in the data model, explain the restriction on EMPLOYEE deletions to the ASSIGNMENT relationship. What does restrict mean in this context?

An employee cannot be deleted if an Assignment row exists for the Employee. If this row exists in the Assignment table, the employee still has a Computer assigned to them. You would not want to delete the Employee until they turn in their Computer.