CSC 553 Chapter 8
8.35 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?
Before the change can be successful, all rows must contain a value in column C1. ALTER TABLE T2 ALTER COLUMN C1 Char(10) NOT NULL;
8.4 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.
Check the table to determine if each AdvisorPhone has only one value of AdvisorName. If the AdvisorPhone appears in the table more than once and the AdvisorName is not the same, AdvisorPhone does not determine AdvisorName. You can use a correlated subquery to determine this.
8.24 Explain the problems that can occur when changing the name of a table.
Constraints that define the relationships must be altered, views must be redefined and triggers must be rewritten to use the new name.
8.34 Which data type changes are the most risky?
Converting char or varchar back to date, money, or numeric is risky; and it may or may not be possible.
8.1 Explain, one more time, the three ways that databases arise.
Databases are created (1) from existing data, (2) from the development of new information systems, and (3) from the redesign of existing databases.
8.11 Explain the meaning of the keyword EXISTS.
EXISTS is a logical operator that is evaluated as true or false depending upon the presence or absence of rows that fit a qualifying condition.
8.37 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?
First, all child rows must contain a value in the column that is defined as the foreign key. To make the change, simply use the ALTER command and change the foreign key column's constraint to NOT NULL.
8.36 Explain how to change the minimum cardinality when a child that was required to have a parent is no longer required to have one.
First, drop the foreign key constraint. Second, alter the column definition for the column that is the foreign key so that the column can be NULL. Finally, add the foreign key constraint back to the schema. Many DBMS will allow making the change without dropping the foreign key constraint and adding it back.
8.8 Explain how the query in your answer to Review Question 8.5 differs from the query in your answer to Review Question 8.7.
First, the subquery in 8.5 uses two different tables, the subquery in 8.7 uses the same table twice. Second, in 8.5, the CUSTOMER_ARTIST_INT table is queried one time and the result is used to satisfy the WHERE clause in the outside query. In 8.7, each time a row in the outside query is tested the inside query must be executed.
8.33 Which data type changes are the least risky?
Generally, converting numeric to char or varchar is not risky. Converting date or money or other more specific data type to char or varchar will usually succeed.
8.27 Explain how views can simplify the process of changing a table name.
If an application uses program logic that always references views, then the only application change that is needed is to rewrite the views to reference the new table name.
8.40 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.
In this case, simply use the ALTER command to remove the unique constraint on the column defined as the foreign key.
8.38 Explain how to change the minimum cardinality when a parent that was required to have a child is no longer required to have one.
In this case, you would simply drop the trigger that enforces the constraint.
8.19 Why is it important to carefully evaluate the results of reverse engineering?
It is important to carefully evaluate the results of reverse engineering because the model produced will not be a truly logical model. For example, there will be entities created for every intersection table in the database, which should be modeled as many-to-many relationships in the data model.
8.2 Describe why database redesign is necessary.
It is not so easy to build a database correctly the first time. Even if we can obtain all of the users' requirements, the tasks of building a correct data model and of transforming that data model into a correct database design are difficult. During those stages, some aspects of the database will need to be redesigned. Also, inevitably, mistakes are made that must be corrected. When a new information system is installed, the users can behave in new ways. As the users behave in those new ways, they will want changes to the information system to accommodate their new behaviors. As those changes are made, the users will have more new behaviors, they will request more changes to the information system, and so forth in a never-ending cycle.
8.29 Show an SQL statement to add an integer column C1 to the table T2. Assume that C1 is NULL.
ALTER TABLE T2 ADD COLUMN C1 Int NULL;
8.30 Extend your answer to question 8.29 to add C1 when C1 is to be NOT NULL.
ALTER TABLE T2 ADD COLUMN C1 Int NULL; UPDATE T2 SET C1 = 0; ALTER TABLE T2 ALTER COLUMN C1 NOT NULL;
8.31 Show an SQL statement to drop the column C1 from table T2.
ALTER TABLE T2 DROP COLUMN C1;
8.45 Explain how the reduction of maximum cardinalities causes data loss.
Anytime you reduce maximum cardinalities you will lose instances of relationships. This causes data loss. For example an N:M relationship between Student and Class was reduced to a 1:N relationship, a Student could register for only one class. The relationship (intersection records) would be lost for all but one record.
8.6 Explain the following statement: "The processing of correlated subqueries is nested, whereas that of regular subqueries is not."
Regular subqueries can be processed from the bottom up. That is, the bottom or inside subquery can be executed then the next subquery can be processed. There is no need to jump back-and-forth between the SELECT statements. Correlated subqueries are nested because the inner query must jump back to the outer query to see if a value exists in both queries.
8.18 Explain the process of reverse engineering.
Reverse engineering is the process of reading a database schema and producing a data model from that schema. You are in essence working backwards, from implementation to design.
8.10 Write a correlated subquery to determine whether the data support the supposition in question 8.4.
SELECT A1.AdvisorName, A1.AdvisorPhone FROM ADVISOR A1 WHERE A1.AdvisorName IN (SELECT A2.AdvisorName FROM ADVISOR A2 WHERE A1.AdvisorName = A2.AdvisorName AND A1.AdvisorPhone <> A2.AdvisorPhone);
8.12 Answer question 8.10, but use EXISTS.
SELECT A1.AdvisorName, A1.AdvisorPhone FROM ADVISOR A1 WHERE EXISTS (SELECT * FROM ADVISOR A2 WHERE A1.AdvisorName = A2.AdvisorName AND A1.AdvisorPhone <> A2.AdvisorPhone);
8.5 Write a subquery, other than one in this chapter, that is not a correlated subquery.
SELECT C.CustomerID, C.LastName, C.FirstName FROM CUSTOMER AS C WHERE C.CustomerID IN (SELECT CAI.CustomerID FROM CUSTOMER_ARTIST_INT AS CAI WHERE CAI.ArtistID = 17);
8.7 Write a correlated subquery, other than one in this chapter.
SELECT C1.CustomerID, C1.Email FROM CUSTOMER AS C1 WHERE C1.Email IN (SELECT C2.Email FROM CUSTOMER AS C2 WHERE C1.Email = C2.Email AND C1.CustomerID <> C2.CustomerID);
8.22 What sources are used when creating a dependency graph?
Tables, Views, Relationships, Constraints, Triggers and Stored Procedures
8.13 Explain how any and all pertain to EXISTS and NOT EXISTS.
The EXISTS keyword will be true if any row in the subquery meets the condition. The NOT EXISTS keyword will be true only if all rows in the subquery fail the condition. Consequently, the double use of NOT EXISTS can be used to find rows that have some specified condition to every row of a table.
8.28 Under what conditions is the following SQL statement valid? INSERT INTO T1 (A, B) SELECT (C, D)FROMT2;
The statement is valid when T2 is an existing table with populated columns T2.C and T2.D, when T1 is a new table with columns T1.A and T1.B where the data type of T1.A matches T2.C and the datatype of T1.B matches T2.D.
8.48 In general terms, what must be done to add a new relationship?
To add a relationship, simply create a foreign key constraint. You will probably need to add a new column to a table to serve as the foreign key first.
8.39 Explain how to change the minimum cardinality when a parent that was not required to have a child is now required to have one.
To change the minimum cardinality from zero to one, it is necessary to write the appropriate triggers.
8.3 Explain the following statement in your own words: "Information systems and organizations create each other." How does this relate to database redesign?
When a new information system is installed, the users can behave in new ways. As the users behave in those new ways, they will want changes to the information system to accommodate their new behaviors. As those changes are made, the users will have more new behaviors, they will request more changes to the information system, and so forth in a never-ending cycle.
8.17 Why is it important to analyze the database before implementing database redesign tasks? What can happen if this is not done?
You must analyze the database because you must know the impact of any changes you may make. You must also know where any change may force other changes to occur. If you do not know this, a change you make during redesign may create a problem somewhere else and you may not know about this problem for days or even months after the redesign change is made. This problem may be severe enough that no processing can occur, in essence, closing the business during repairs.
8.20 What is a dependency graph? What purpose does it serve?
they are diagrams that consist of nodes and arcs (or lines) that connect those nodes. A dependency graph shows the relationships between tables, triggers, and views. It helps us determine the impact of and those things impacted by a change.
8.50 What are the risks and problems of forward engineering?
Much depends on the nature of the changes to be made and the quality of the forward engineering features of the data modeling product. Some products show the SQL they are generating, some do not. Changes to a database need to be carefully considered before they are implemented.
8.23 Explain two different types of test databases that should be used when testing database redesign changes.
One is a small test database that can be used for initial testing. The second is a large test database, which may even be a full copy of the operational database, which is used for secondary testing.
8.41 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.
(1) Add a column to the table that is the many side of the relationship. This column will be the foreign key that references the parent table. This foreign key is not unique. (2) Write an UPDATE command to place the value of the primary key in the new foreign key. (3) Create a constraint to define the new column as a foreign key. (4) Drop the original foreign key constraint in the parent table. (5) Drop the original foreign key column in the parent table.
8.25 Describe the process of changing a table name.
(1) Create a new table with the new name. (2) Alter any constraints, triggers, or views that may be affected. (3) Copy all data from the old table to the new table. (4) Create any needed foreign key constraints. (5) Drop the old table.
8.43 Explain how to transform a 1:N relationship into an N:M relationship.
(1) Create the intersection table. Foreign key constraints can be done while creating the table or as a separate step. (2) Copy the values of primary keys from the tables for rows in which there is an existing foreign key match between the tables. (3) Drop the original foreign key constraint and foreign key column for the original child table.
8.32 Describe the process for dropping primary key C1 and making the new primary key C2.
(1) Drop any foreign key constraints that reference the C1 primary key. (2) Drop the constraint that makes C1 the primary key. (3) Create a new constraint that makes C2 the primary key. (4) Create any required foreign key constraints for C2. (5) Drop column C1.
8.49 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.
(1) Drop the foreign key constraint T2.FK2. (2) Drop Table T1 ALTER TABLE T2 DROP CONSTRAINT FK2; ALTER TABLE T2 DROP COLUMN FK2; DROP TABLE T1;
