Deck 10: Application Development With Views

Question 1

In a hierarchical form,the fixed part is called the parent form and the variable part is called the child form.

Answer 1

False
Explanation: In a hierarchical form, the fixed part is called the main form and the variable part is called the subform.

Question 2

SQL:1999 specifies that a view can be used in any query.

Answer 2

True

Question 3

Typically in a hierarchical form,the data from the two 1-M tables shown on the main form and the subform would be the result of executing a join query on the two tables for a specific value of the foreign key.

Answer 3

True

Question 4

Although there are many advantages to using views,the main drawback is that performance can sometimes be poor in processing some complex views.

Question 5

The same rules of updatability apply to 1-M queries with more than two tables,but you must apply the rules to each join in the query.

Question 6

Using the materialization strategy,when a user submits a query using a view,the query defining the view is first executed creating a temporary table,and then the query using the view is executed on the temporary table.

Question 7

You can rename some of the columns,all of the columns,or none of the columns in a view using parentheses after the view name.

Question 8

The motivations for using views are that they support data independence,simplify query formulation,and provide flexible security.

Question 9

A single-table view is not updatable if all of the columns of the base table are not included in the view.

Question 10

A modification query may cause rows in a view to disappear if a column in the WHERE clause of the view definition is changed by an UPDATE statement.

Question 11

In a 1-M updatable query,you cannot insert a row into the parent table without also inserting a corresponding row into the child table.

Question 12

In a 1-M updatable query,to insert a row into the child table,you would provide only the values needed to insert a row into the child table.

Question 13

In a data warehouse environment,views are materialized on demand,i.e.when the view query is submitted.

Question 14

Modifications to views,such as deleting rows or changing the primary key column are subject to the integrity rules of the underlying base table.

Question 15

Only single-table views are updatable with SQL modification statements.

Question 16

The WITH RESTRICTIONS OPTION clause of the CREATE VIEW statement prevents side effects from occurring when a view is updated.

Question 17

If a table name used in a view is changed,the view definition does not have to be changed but the applications using the view must be changed.

Question 18

In a 1-M updatable query,update operations are supported on the parent table even if the query does not contain the primary key of the parent table.

Question 19

In a hierarchical form involving a 1-M relationship,only columns from the two tables involved in the relationship can be displayed in the form.

Question 20

In a 1-M updatable query,to insert a row into both the parent and child tables,you must supply the primary keys and all the required columns of both the parent and child tables.

Question 21

In the "three schema architecture",views belong to:

A) The internal level
B) The conceptual level
C) The external level
D) The client-server level

Question 22

Figuer:
CUSTOMER (primary key = CID) $\begin{array}{|l|l|l|l|l|}\hline \text { CID } & \text { CNAME } & \text { AGE } & \text { RESID\_CITY } & \text { Cregion } \\\hline 10 & \text { BLACK } & 40 & \text { SD } & \text { SD } \\\hline 20 & \text { GREEN } & 25 & \text { SD } & \text { SD } \\\hline 30 & \text { JONES } & 30 & \text { La Mesa } & \text { SD } \\\hline 40 & \text { MARTIN } & 35 & \text { LA } & \text { LA } \\\hline 50 & \text { SIMON } & 22 & \text { Pomona } & \text { LA } \\\hline 60 & \text { VERNON } & 60 & \text { Poway } & \text { SD } \\\hline\end{array}$ RENTALS (primary key = RTN) $$\begin{array} { | l | l | l | l | l | l | l | l | } \hline \text { CID } & \text { MAKE } & \text { DATE\_OUT } & \text { PICKUP } & \text { RETURN } & \text { RTN } & \text { VIN } & \text { DATE-IN } \\\hline 10 & \text { FORD } & \text { 10-Oct-00 } & \text { SD } & \text { SD } & 1 & \text { F1 } & \text { 01-Dec-00 } \\\hline 10 & \text { GM } & 01 - \text { Jan-01 } & \text { SD } & \text { LA } & 2 & & \\\hline 10 & \text { FORD } & 01 - \text { Feb-01 } & \text { LA } & \text { SD } & 3 & & \\\hline 20 & \text { NISSAN } & 07 - J u l - 00 & \text { SD } & & 4 & \text { N1 } & \\\hline 30 & \text { FORD } & 01 - J u l - 00 & \text { SD } & \text { SD } & 5 & \text { F2 } & 30 \text {-Nov-00 } \\\hline 30 & \text { GM } & 01 - \text {-Dec-00 } & \text { SD } & \text { SD } & 6 & \text { G1 } & \\\hline 40 & \text { FORD } & 01 - \text { Aug-00 } & \text { LA } & \text { LA } & 7 & \text { F3 } & \\\hline 50 & \text { GM } & 01 - \text { Sep-00 } & \text { LA } & \text { SD } & 8 & \text { G2 } & \\\hline 60 & \text { NISSAN } & 02 \text {-Jan-01 } & \text { SD } & \text { LA } & 9 & & \\\hline\end{array}$$ In the table RENTALS, RTN provides the rental number (it is the primary key), CID refers to the CID in CUSTOMER, PICKUP is the city where the car was (or will be) picked up and Return is the city where the car was (or will be) returned, VIN is a foreign key that references the table CAR. The rental company has two branches, one in San Diego (SD) and one in Los Angeles (LA). Cars are picked up and returned by the customers at these two locations.
RENTCOST $$\begin{array} { | l | l | } \hline \text { MAKE } & \text { COST } \\\hline \text { FORD } & 30 \\\hline \text { GM } & 40 \\\hline \text { NISSAN } & 30 \\\hline \text { TOYOTA } & 20 \\\hline \text { VOLVO } & 50 \\\hline\end{array}$$ In RENTCOST, COST shows the base cost of renting a given MAKE for one day.
CAR $$\begin{array} { | l | l | l | l | } \hline \text { VIN } & \text { MAKE } & \text { RENTED } & \text { AT } \\\hline \text { F1 } & \text { FORD } & \text { NO } & \text { SD } \\\hline \text { F2 } & \text { FORD } & \text { NO } & \text { SD } \\\hline \text { F3 } & \text { FORD } & \text { YES } & \text { IR } \\\hline \text { F4 } & \text { FORD } & \text { NO } & \text { LA } \\\hline \text { G1 } & \text { GM } & \text { YES } & \text { SD } \\\hline \text { G2 } & \text { GM } & \text { YES } & \text { SD } \\\hline \text { N1 } & \text { NISSAN } & \text { YES } & \text { LA } \\\hline \text { N2 } & \text { NISSAN } & \text { NO } & \text { LA } \\\hline \text { T1 } & \text { TOYOTA } & \text { NO } & \text { IR } \\\hline \text { T2 } & \text { TOYOTA } & \text { NO } & \text { IR } \\\hline \text { V1 } & \text { VOLVO } & \text { NO } & \text { LA } \\\hline \text { V2 } & \text { VOLVO } & \text { NO } & \text { LA } \\\hline\end{array}$$ The table CAR (primary key = VIN) provides information about each car, in particular if it is currently rented, and where its usual storage location is (attribute AT).
CREATE VIEW CUST_SD AS
SELECT * FROM CUSTOMER
WHERE Cregion = 'SD'
CREATE VIEW CUST_40D AS
SELECT CNAME, RENTALS.MAKE, RENTCOST.COST
FROM CUSTOMER, RENTALS, RENTCOST
WHERE CUSTOMER.CID = RENTALS.CID
AND RENTALS.MAKE = RENTCOST.MAKE
AND RENTCOST < 40
CREATE VIEW Make_View (Make, NumRentals, Cregion, Storageat) AS
SELECT RENTALS.MAKE, Count (RTN), Cregion, AT
FROM CUSTOMER, RENTALS, CAR
WHERE CUSTOMER.CID = RENTALS.CID
AND RENTALS.VIN = CAR.VIN
GROUP BY RENTALS.MAKE, Cregion, At

-The number of rows in the view Make-View is:

A) 6
B) 5
C) 4
D) 3

Question 23

When creating a hierarchical report,the groups are formulated using the GROUP BY clause in the SQL query formulation.

Question 24

The following SQL2 command is executed: CREATE VIEW CUST_SD2 AS
SELECT * FROM CUSTOMER
WHERE Cregion = 'SD'
WITH CHECK OPTION
There is a request to execute the following SQL2 command:
UPDATE CUST_SD2
SET AGE = AGE +1,Cregion = 'LA' WHERE CNAME = 'BLACK'
Select the most appropriate statement:

A) After execution of this query, SELECT * from CUST_SD2 will show a value of 41 for BLACK
B) After execution of this query, SELECT * from CUSTOMER will show a value of 41 for BLACK
C) Both of the above
D) No update took place

Question 25

The use of views in application development can provide the following benefit:

A) data independence
B) simplification
C) security
D) all of the above

Question 26

To process queries that reference a view,the DBMS can use either a _________ or __________ strategy.

A) direct, indirect
B) materialization, modification
C) materialization, indirect
D) realistic, virtual

Question 27

Like a form,a report can change the underlying data in the base tables.

Question 28

When defining a view using SQL:

A) The SQL does not have to contain a select statement that defines the virtual or derived table
B) The select statement in the SQL must contain a reference to only one table
C) The column names of the virtual or derived table can be renamed in the SQL.
D) The column names of the virtual or derived table will always match the column names in the select statement

Question 29

Figuer:
CUSTOMER (primary key = CID) $\begin{array}{|l|l|l|l|l|}\hline \text { CID } & \text { CNAME } & \text { AGE } & \text { RESID\_CITY } & \text { Cregion } \\\hline 10 & \text { BLACK } & 40 & \text { SD } & \text { SD } \\\hline 20 & \text { GREEN } & 25 & \text { SD } & \text { SD } \\\hline 30 & \text { JONES } & 30 & \text { La Mesa } & \text { SD } \\\hline 40 & \text { MARTIN } & 35 & \text { LA } & \text { LA } \\\hline 50 & \text { SIMON } & 22 & \text { Pomona } & \text { LA } \\\hline 60 & \text { VERNON } & 60 & \text { Poway } & \text { SD } \\\hline\end{array}$ RENTALS (primary key = RTN) $$\begin{array} { | l | l | l | l | l | l | l | l | } \hline \text { CID } & \text { MAKE } & \text { DATE\_OUT } & \text { PICKUP } & \text { RETURN } & \text { RTN } & \text { VIN } & \text { DATE-IN } \\\hline 10 & \text { FORD } & \text { 10-Oct-00 } & \text { SD } & \text { SD } & 1 & \text { F1 } & \text { 01-Dec-00 } \\\hline 10 & \text { GM } & 01 - \text { Jan-01 } & \text { SD } & \text { LA } & 2 & & \\\hline 10 & \text { FORD } & 01 - \text { Feb-01 } & \text { LA } & \text { SD } & 3 & & \\\hline 20 & \text { NISSAN } & 07 - J u l - 00 & \text { SD } & & 4 & \text { N1 } & \\\hline 30 & \text { FORD } & 01 - J u l - 00 & \text { SD } & \text { SD } & 5 & \text { F2 } & 30 \text {-Nov-00 } \\\hline 30 & \text { GM } & 01 - \text {-Dec-00 } & \text { SD } & \text { SD } & 6 & \text { G1 } & \\\hline 40 & \text { FORD } & 01 - \text { Aug-00 } & \text { LA } & \text { LA } & 7 & \text { F3 } & \\\hline 50 & \text { GM } & 01 - \text { Sep-00 } & \text { LA } & \text { SD } & 8 & \text { G2 } & \\\hline 60 & \text { NISSAN } & 02 \text {-Jan-01 } & \text { SD } & \text { LA } & 9 & & \\\hline\end{array}$$ In the table RENTALS, RTN provides the rental number (it is the primary key), CID refers to the CID in CUSTOMER, PICKUP is the city where the car was (or will be) picked up and Return is the city where the car was (or will be) returned, VIN is a foreign key that references the table CAR. The rental company has two branches, one in San Diego (SD) and one in Los Angeles (LA). Cars are picked up and returned by the customers at these two locations.
RENTCOST $$\begin{array} { | l | l | } \hline \text { MAKE } & \text { COST } \\\hline \text { FORD } & 30 \\\hline \text { GM } & 40 \\\hline \text { NISSAN } & 30 \\\hline \text { TOYOTA } & 20 \\\hline \text { VOLVO } & 50 \\\hline\end{array}$$ In RENTCOST, COST shows the base cost of renting a given MAKE for one day.
CAR $$\begin{array} { | l | l | l | l | } \hline \text { VIN } & \text { MAKE } & \text { RENTED } & \text { AT } \\\hline \text { F1 } & \text { FORD } & \text { NO } & \text { SD } \\\hline \text { F2 } & \text { FORD } & \text { NO } & \text { SD } \\\hline \text { F3 } & \text { FORD } & \text { YES } & \text { IR } \\\hline \text { F4 } & \text { FORD } & \text { NO } & \text { LA } \\\hline \text { G1 } & \text { GM } & \text { YES } & \text { SD } \\\hline \text { G2 } & \text { GM } & \text { YES } & \text { SD } \\\hline \text { N1 } & \text { NISSAN } & \text { YES } & \text { LA } \\\hline \text { N2 } & \text { NISSAN } & \text { NO } & \text { LA } \\\hline \text { T1 } & \text { TOYOTA } & \text { NO } & \text { IR } \\\hline \text { T2 } & \text { TOYOTA } & \text { NO } & \text { IR } \\\hline \text { V1 } & \text { VOLVO } & \text { NO } & \text { LA } \\\hline \text { V2 } & \text { VOLVO } & \text { NO } & \text { LA } \\\hline\end{array}$$ The table CAR (primary key = VIN) provides information about each car, in particular if it is currently rented, and where its usual storage location is (attribute AT).
CREATE VIEW CUST_SD AS
SELECT * FROM CUSTOMER
WHERE Cregion = 'SD'
CREATE VIEW CUST_40D AS
SELECT CNAME, RENTALS.MAKE, RENTCOST.COST
FROM CUSTOMER, RENTALS, RENTCOST
WHERE CUSTOMER.CID = RENTALS.CID
AND RENTALS.MAKE = RENTCOST.MAKE
AND RENTCOST < 40
CREATE VIEW Make_View (Make, NumRentals, Cregion, Storageat) AS
SELECT RENTALS.MAKE, Count (RTN), Cregion, AT
FROM CUSTOMER, RENTALS, CAR
WHERE CUSTOMER.CID = RENTALS.CID
AND RENTALS.VIN = CAR.VIN
GROUP BY RENTALS.MAKE, Cregion, At

-UPDATE CUSTOMER SET AGE = AGE + 1
This command updates the following number of rows in the view CUST_SD:

A) 3
B) 4
C) 6
D) 0

Question 30

Views are the "building blocks" of both data entry forms and reports.

Question 31

Figuer:
CUSTOMER (primary key = CID) $\begin{array}{|l|l|l|l|l|}\hline \text { CID } & \text { CNAME } & \text { AGE } & \text { RESID\_CITY } & \text { Cregion } \\\hline 10 & \text { BLACK } & 40 & \text { SD } & \text { SD } \\\hline 20 & \text { GREEN } & 25 & \text { SD } & \text { SD } \\\hline 30 & \text { JONES } & 30 & \text { La Mesa } & \text { SD } \\\hline 40 & \text { MARTIN } & 35 & \text { LA } & \text { LA } \\\hline 50 & \text { SIMON } & 22 & \text { Pomona } & \text { LA } \\\hline 60 & \text { VERNON } & 60 & \text { Poway } & \text { SD } \\\hline\end{array}$ RENTALS (primary key = RTN) $$\begin{array} { | l | l | l | l | l | l | l | l | } \hline \text { CID } & \text { MAKE } & \text { DATE\_OUT } & \text { PICKUP } & \text { RETURN } & \text { RTN } & \text { VIN } & \text { DATE-IN } \\\hline 10 & \text { FORD } & \text { 10-Oct-00 } & \text { SD } & \text { SD } & 1 & \text { F1 } & \text { 01-Dec-00 } \\\hline 10 & \text { GM } & 01 - \text { Jan-01 } & \text { SD } & \text { LA } & 2 & & \\\hline 10 & \text { FORD } & 01 - \text { Feb-01 } & \text { LA } & \text { SD } & 3 & & \\\hline 20 & \text { NISSAN } & 07 - J u l - 00 & \text { SD } & & 4 & \text { N1 } & \\\hline 30 & \text { FORD } & 01 - J u l - 00 & \text { SD } & \text { SD } & 5 & \text { F2 } & 30 \text {-Nov-00 } \\\hline 30 & \text { GM } & 01 - \text {-Dec-00 } & \text { SD } & \text { SD } & 6 & \text { G1 } & \\\hline 40 & \text { FORD } & 01 - \text { Aug-00 } & \text { LA } & \text { LA } & 7 & \text { F3 } & \\\hline 50 & \text { GM } & 01 - \text { Sep-00 } & \text { LA } & \text { SD } & 8 & \text { G2 } & \\\hline 60 & \text { NISSAN } & 02 \text {-Jan-01 } & \text { SD } & \text { LA } & 9 & & \\\hline\end{array}$$ In the table RENTALS, RTN provides the rental number (it is the primary key), CID refers to the CID in CUSTOMER, PICKUP is the city where the car was (or will be) picked up and Return is the city where the car was (or will be) returned, VIN is a foreign key that references the table CAR. The rental company has two branches, one in San Diego (SD) and one in Los Angeles (LA). Cars are picked up and returned by the customers at these two locations.
RENTCOST $$\begin{array} { | l | l | } \hline \text { MAKE } & \text { COST } \\\hline \text { FORD } & 30 \\\hline \text { GM } & 40 \\\hline \text { NISSAN } & 30 \\\hline \text { TOYOTA } & 20 \\\hline \text { VOLVO } & 50 \\\hline\end{array}$$ In RENTCOST, COST shows the base cost of renting a given MAKE for one day.
CAR $$\begin{array} { | l | l | l | l | } \hline \text { VIN } & \text { MAKE } & \text { RENTED } & \text { AT } \\\hline \text { F1 } & \text { FORD } & \text { NO } & \text { SD } \\\hline \text { F2 } & \text { FORD } & \text { NO } & \text { SD } \\\hline \text { F3 } & \text { FORD } & \text { YES } & \text { IR } \\\hline \text { F4 } & \text { FORD } & \text { NO } & \text { LA } \\\hline \text { G1 } & \text { GM } & \text { YES } & \text { SD } \\\hline \text { G2 } & \text { GM } & \text { YES } & \text { SD } \\\hline \text { N1 } & \text { NISSAN } & \text { YES } & \text { LA } \\\hline \text { N2 } & \text { NISSAN } & \text { NO } & \text { LA } \\\hline \text { T1 } & \text { TOYOTA } & \text { NO } & \text { IR } \\\hline \text { T2 } & \text { TOYOTA } & \text { NO } & \text { IR } \\\hline \text { V1 } & \text { VOLVO } & \text { NO } & \text { LA } \\\hline \text { V2 } & \text { VOLVO } & \text { NO } & \text { LA } \\\hline\end{array}$$ The table CAR (primary key = VIN) provides information about each car, in particular if it is currently rented, and where its usual storage location is (attribute AT).
CREATE VIEW CUST_SD AS
SELECT * FROM CUSTOMER
WHERE Cregion = 'SD'
CREATE VIEW CUST_40D AS
SELECT CNAME, RENTALS.MAKE, RENTCOST.COST
FROM CUSTOMER, RENTALS, RENTCOST
WHERE CUSTOMER.CID = RENTALS.CID
AND RENTALS.MAKE = RENTCOST.MAKE
AND RENTCOST < 40
CREATE VIEW Make_View (Make, NumRentals, Cregion, Storageat) AS
SELECT RENTALS.MAKE, Count (RTN), Cregion, AT
FROM CUSTOMER, RENTALS, CAR
WHERE CUSTOMER.CID = RENTALS.CID
AND RENTALS.VIN = CAR.VIN
GROUP BY RENTALS.MAKE, Cregion, At

-The number of rows in the view CUST_SD is:

A) 6
B) 5
C) 4
D) 0

Question 32

Figuer:
CUSTOMER (primary key = CID) $\begin{array}{|l|l|l|l|l|}\hline \text { CID } & \text { CNAME } & \text { AGE } & \text { RESID\_CITY } & \text { Cregion } \\\hline 10 & \text { BLACK } & 40 & \text { SD } & \text { SD } \\\hline 20 & \text { GREEN } & 25 & \text { SD } & \text { SD } \\\hline 30 & \text { JONES } & 30 & \text { La Mesa } & \text { SD } \\\hline 40 & \text { MARTIN } & 35 & \text { LA } & \text { LA } \\\hline 50 & \text { SIMON } & 22 & \text { Pomona } & \text { LA } \\\hline 60 & \text { VERNON } & 60 & \text { Poway } & \text { SD } \\\hline\end{array}$ RENTALS (primary key = RTN) $$\begin{array} { | l | l | l | l | l | l | l | l | } \hline \text { CID } & \text { MAKE } & \text { DATE\_OUT } & \text { PICKUP } & \text { RETURN } & \text { RTN } & \text { VIN } & \text { DATE-IN } \\\hline 10 & \text { FORD } & \text { 10-Oct-00 } & \text { SD } & \text { SD } & 1 & \text { F1 } & \text { 01-Dec-00 } \\\hline 10 & \text { GM } & 01 - \text { Jan-01 } & \text { SD } & \text { LA } & 2 & & \\\hline 10 & \text { FORD } & 01 - \text { Feb-01 } & \text { LA } & \text { SD } & 3 & & \\\hline 20 & \text { NISSAN } & 07 - J u l - 00 & \text { SD } & & 4 & \text { N1 } & \\\hline 30 & \text { FORD } & 01 - J u l - 00 & \text { SD } & \text { SD } & 5 & \text { F2 } & 30 \text {-Nov-00 } \\\hline 30 & \text { GM } & 01 - \text {-Dec-00 } & \text { SD } & \text { SD } & 6 & \text { G1 } & \\\hline 40 & \text { FORD } & 01 - \text { Aug-00 } & \text { LA } & \text { LA } & 7 & \text { F3 } & \\\hline 50 & \text { GM } & 01 - \text { Sep-00 } & \text { LA } & \text { SD } & 8 & \text { G2 } & \\\hline 60 & \text { NISSAN } & 02 \text {-Jan-01 } & \text { SD } & \text { LA } & 9 & & \\\hline\end{array}$$ In the table RENTALS, RTN provides the rental number (it is the primary key), CID refers to the CID in CUSTOMER, PICKUP is the city where the car was (or will be) picked up and Return is the city where the car was (or will be) returned, VIN is a foreign key that references the table CAR. The rental company has two branches, one in San Diego (SD) and one in Los Angeles (LA). Cars are picked up and returned by the customers at these two locations.
RENTCOST $$\begin{array} { | l | l | } \hline \text { MAKE } & \text { COST } \\\hline \text { FORD } & 30 \\\hline \text { GM } & 40 \\\hline \text { NISSAN } & 30 \\\hline \text { TOYOTA } & 20 \\\hline \text { VOLVO } & 50 \\\hline\end{array}$$ In RENTCOST, COST shows the base cost of renting a given MAKE for one day.
CAR $$\begin{array} { | l | l | l | l | } \hline \text { VIN } & \text { MAKE } & \text { RENTED } & \text { AT } \\\hline \text { F1 } & \text { FORD } & \text { NO } & \text { SD } \\\hline \text { F2 } & \text { FORD } & \text { NO } & \text { SD } \\\hline \text { F3 } & \text { FORD } & \text { YES } & \text { IR } \\\hline \text { F4 } & \text { FORD } & \text { NO } & \text { LA } \\\hline \text { G1 } & \text { GM } & \text { YES } & \text { SD } \\\hline \text { G2 } & \text { GM } & \text { YES } & \text { SD } \\\hline \text { N1 } & \text { NISSAN } & \text { YES } & \text { LA } \\\hline \text { N2 } & \text { NISSAN } & \text { NO } & \text { LA } \\\hline \text { T1 } & \text { TOYOTA } & \text { NO } & \text { IR } \\\hline \text { T2 } & \text { TOYOTA } & \text { NO } & \text { IR } \\\hline \text { V1 } & \text { VOLVO } & \text { NO } & \text { LA } \\\hline \text { V2 } & \text { VOLVO } & \text { NO } & \text { LA } \\\hline\end{array}$$ The table CAR (primary key = VIN) provides information about each car, in particular if it is currently rented, and where its usual storage location is (attribute AT).
CREATE VIEW CUST_SD AS
SELECT * FROM CUSTOMER
WHERE Cregion = 'SD'
CREATE VIEW CUST_40D AS
SELECT CNAME, RENTALS.MAKE, RENTCOST.COST
FROM CUSTOMER, RENTALS, RENTCOST
WHERE CUSTOMER.CID = RENTALS.CID
AND RENTALS.MAKE = RENTCOST.MAKE
AND RENTCOST < 40
CREATE VIEW Make_View (Make, NumRentals, Cregion, Storageat) AS
SELECT RENTALS.MAKE, Count (RTN), Cregion, AT
FROM CUSTOMER, RENTALS, CAR
WHERE CUSTOMER.CID = RENTALS.CID
AND RENTALS.VIN = CAR.VIN
GROUP BY RENTALS.MAKE, Cregion, At

-The number of columns in the view CUST_SD is:

A) 5
B) 4
C) 3
D) 2

Question 33

For each Cregion,you want to show each customer (CNAME),and for each customer,each rental ordered by Make,showing PICKUP,RETURN and DATE-OUT.Which of the following would be a group in a hierarchical report?

A) MAKE
B) PICKUP
C) RETURN
D) RENTALS

Question 34

Request to execute the following SQL2 command: DELETE FROM CUST_SD
WHERE RESID_CITY ='SD'
Which of the following statements is not true?

A) After execution of this query, SELECT * from CUST_SD will show that two rows were deleted from CUST_SD
B) After execution of this query, SELECT * from CUST_SD will show that two rows were deleted from CUSTOMER
C) Both of the above
D) The DELETE statement cannot execute on CUST_SD

Question 35

Figuer:
CUSTOMER (primary key = CID) $\begin{array}{|l|l|l|l|l|}\hline \text { CID } & \text { CNAME } & \text { AGE } & \text { RESID\_CITY } & \text { Cregion } \\\hline 10 & \text { BLACK } & 40 & \text { SD } & \text { SD } \\\hline 20 & \text { GREEN } & 25 & \text { SD } & \text { SD } \\\hline 30 & \text { JONES } & 30 & \text { La Mesa } & \text { SD } \\\hline 40 & \text { MARTIN } & 35 & \text { LA } & \text { LA } \\\hline 50 & \text { SIMON } & 22 & \text { Pomona } & \text { LA } \\\hline 60 & \text { VERNON } & 60 & \text { Poway } & \text { SD } \\\hline\end{array}$ RENTALS (primary key = RTN) $$\begin{array} { | l | l | l | l | l | l | l | l | } \hline \text { CID } & \text { MAKE } & \text { DATE\_OUT } & \text { PICKUP } & \text { RETURN } & \text { RTN } & \text { VIN } & \text { DATE-IN } \\\hline 10 & \text { FORD } & \text { 10-Oct-00 } & \text { SD } & \text { SD } & 1 & \text { F1 } & \text { 01-Dec-00 } \\\hline 10 & \text { GM } & 01 - \text { Jan-01 } & \text { SD } & \text { LA } & 2 & & \\\hline 10 & \text { FORD } & 01 - \text { Feb-01 } & \text { LA } & \text { SD } & 3 & & \\\hline 20 & \text { NISSAN } & 07 - J u l - 00 & \text { SD } & & 4 & \text { N1 } & \\\hline 30 & \text { FORD } & 01 - J u l - 00 & \text { SD } & \text { SD } & 5 & \text { F2 } & 30 \text {-Nov-00 } \\\hline 30 & \text { GM } & 01 - \text {-Dec-00 } & \text { SD } & \text { SD } & 6 & \text { G1 } & \\\hline 40 & \text { FORD } & 01 - \text { Aug-00 } & \text { LA } & \text { LA } & 7 & \text { F3 } & \\\hline 50 & \text { GM } & 01 - \text { Sep-00 } & \text { LA } & \text { SD } & 8 & \text { G2 } & \\\hline 60 & \text { NISSAN } & 02 \text {-Jan-01 } & \text { SD } & \text { LA } & 9 & & \\\hline\end{array}$$ In the table RENTALS, RTN provides the rental number (it is the primary key), CID refers to the CID in CUSTOMER, PICKUP is the city where the car was (or will be) picked up and Return is the city where the car was (or will be) returned, VIN is a foreign key that references the table CAR. The rental company has two branches, one in San Diego (SD) and one in Los Angeles (LA). Cars are picked up and returned by the customers at these two locations.
RENTCOST $$\begin{array} { | l | l | } \hline \text { MAKE } & \text { COST } \\\hline \text { FORD } & 30 \\\hline \text { GM } & 40 \\\hline \text { NISSAN } & 30 \\\hline \text { TOYOTA } & 20 \\\hline \text { VOLVO } & 50 \\\hline\end{array}$$ In RENTCOST, COST shows the base cost of renting a given MAKE for one day.
CAR $$\begin{array} { | l | l | l | l | } \hline \text { VIN } & \text { MAKE } & \text { RENTED } & \text { AT } \\\hline \text { F1 } & \text { FORD } & \text { NO } & \text { SD } \\\hline \text { F2 } & \text { FORD } & \text { NO } & \text { SD } \\\hline \text { F3 } & \text { FORD } & \text { YES } & \text { IR } \\\hline \text { F4 } & \text { FORD } & \text { NO } & \text { LA } \\\hline \text { G1 } & \text { GM } & \text { YES } & \text { SD } \\\hline \text { G2 } & \text { GM } & \text { YES } & \text { SD } \\\hline \text { N1 } & \text { NISSAN } & \text { YES } & \text { LA } \\\hline \text { N2 } & \text { NISSAN } & \text { NO } & \text { LA } \\\hline \text { T1 } & \text { TOYOTA } & \text { NO } & \text { IR } \\\hline \text { T2 } & \text { TOYOTA } & \text { NO } & \text { IR } \\\hline \text { V1 } & \text { VOLVO } & \text { NO } & \text { LA } \\\hline \text { V2 } & \text { VOLVO } & \text { NO } & \text { LA } \\\hline\end{array}$$ The table CAR (primary key = VIN) provides information about each car, in particular if it is currently rented, and where its usual storage location is (attribute AT).
CREATE VIEW CUST_SD AS
SELECT * FROM CUSTOMER
WHERE Cregion = 'SD'
CREATE VIEW CUST_40D AS
SELECT CNAME, RENTALS.MAKE, RENTCOST.COST
FROM CUSTOMER, RENTALS, RENTCOST
WHERE CUSTOMER.CID = RENTALS.CID
AND RENTALS.MAKE = RENTCOST.MAKE
AND RENTCOST < 40
CREATE VIEW Make_View (Make, NumRentals, Cregion, Storageat) AS
SELECT RENTALS.MAKE, Count (RTN), Cregion, AT
FROM CUSTOMER, RENTALS, CAR
WHERE CUSTOMER.CID = RENTALS.CID
AND RENTALS.VIN = CAR.VIN
GROUP BY RENTALS.MAKE, Cregion, At

-SELECT CUST_SD.CNAME,RENTALS.DATE_OUT FROM CUST_SD,RENTALS
WHERE CUST_SD.CID = RENTALS.CID
AND CUST_SD.Cregion = RENTALS.RETURN
The number of rows displayed by this SELECT statement is:

A) 3
B) 4
C) 5 or more
D) 0

Question 36

Typically in a hierarchical form,data in the main form may be non-updatable.

Question 37

Figuer:
CUSTOMER (primary key = CID) $\begin{array}{|l|l|l|l|l|}\hline \text { CID } & \text { CNAME } & \text { AGE } & \text { RESID\_CITY } & \text { Cregion } \\\hline 10 & \text { BLACK } & 40 & \text { SD } & \text { SD } \\\hline 20 & \text { GREEN } & 25 & \text { SD } & \text { SD } \\\hline 30 & \text { JONES } & 30 & \text { La Mesa } & \text { SD } \\\hline 40 & \text { MARTIN } & 35 & \text { LA } & \text { LA } \\\hline 50 & \text { SIMON } & 22 & \text { Pomona } & \text { LA } \\\hline 60 & \text { VERNON } & 60 & \text { Poway } & \text { SD } \\\hline\end{array}$ RENTALS (primary key = RTN) $$\begin{array} { | l | l | l | l | l | l | l | l | } \hline \text { CID } & \text { MAKE } & \text { DATE\_OUT } & \text { PICKUP } & \text { RETURN } & \text { RTN } & \text { VIN } & \text { DATE-IN } \\\hline 10 & \text { FORD } & \text { 10-Oct-00 } & \text { SD } & \text { SD } & 1 & \text { F1 } & \text { 01-Dec-00 } \\\hline 10 & \text { GM } & 01 - \text { Jan-01 } & \text { SD } & \text { LA } & 2 & & \\\hline 10 & \text { FORD } & 01 - \text { Feb-01 } & \text { LA } & \text { SD } & 3 & & \\\hline 20 & \text { NISSAN } & 07 - J u l - 00 & \text { SD } & & 4 & \text { N1 } & \\\hline 30 & \text { FORD } & 01 - J u l - 00 & \text { SD } & \text { SD } & 5 & \text { F2 } & 30 \text {-Nov-00 } \\\hline 30 & \text { GM } & 01 - \text {-Dec-00 } & \text { SD } & \text { SD } & 6 & \text { G1 } & \\\hline 40 & \text { FORD } & 01 - \text { Aug-00 } & \text { LA } & \text { LA } & 7 & \text { F3 } & \\\hline 50 & \text { GM } & 01 - \text { Sep-00 } & \text { LA } & \text { SD } & 8 & \text { G2 } & \\\hline 60 & \text { NISSAN } & 02 \text {-Jan-01 } & \text { SD } & \text { LA } & 9 & & \\\hline\end{array}$$ In the table RENTALS, RTN provides the rental number (it is the primary key), CID refers to the CID in CUSTOMER, PICKUP is the city where the car was (or will be) picked up and Return is the city where the car was (or will be) returned, VIN is a foreign key that references the table CAR. The rental company has two branches, one in San Diego (SD) and one in Los Angeles (LA). Cars are picked up and returned by the customers at these two locations.
RENTCOST $$\begin{array} { | l | l | } \hline \text { MAKE } & \text { COST } \\\hline \text { FORD } & 30 \\\hline \text { GM } & 40 \\\hline \text { NISSAN } & 30 \\\hline \text { TOYOTA } & 20 \\\hline \text { VOLVO } & 50 \\\hline\end{array}$$ In RENTCOST, COST shows the base cost of renting a given MAKE for one day.
CAR $$\begin{array} { | l | l | l | l | } \hline \text { VIN } & \text { MAKE } & \text { RENTED } & \text { AT } \\\hline \text { F1 } & \text { FORD } & \text { NO } & \text { SD } \\\hline \text { F2 } & \text { FORD } & \text { NO } & \text { SD } \\\hline \text { F3 } & \text { FORD } & \text { YES } & \text { IR } \\\hline \text { F4 } & \text { FORD } & \text { NO } & \text { LA } \\\hline \text { G1 } & \text { GM } & \text { YES } & \text { SD } \\\hline \text { G2 } & \text { GM } & \text { YES } & \text { SD } \\\hline \text { N1 } & \text { NISSAN } & \text { YES } & \text { LA } \\\hline \text { N2 } & \text { NISSAN } & \text { NO } & \text { LA } \\\hline \text { T1 } & \text { TOYOTA } & \text { NO } & \text { IR } \\\hline \text { T2 } & \text { TOYOTA } & \text { NO } & \text { IR } \\\hline \text { V1 } & \text { VOLVO } & \text { NO } & \text { LA } \\\hline \text { V2 } & \text { VOLVO } & \text { NO } & \text { LA } \\\hline\end{array}$$ The table CAR (primary key = VIN) provides information about each car, in particular if it is currently rented, and where its usual storage location is (attribute AT).
CREATE VIEW CUST_SD AS
SELECT * FROM CUSTOMER
WHERE Cregion = 'SD'
CREATE VIEW CUST_40D AS
SELECT CNAME, RENTALS.MAKE, RENTCOST.COST
FROM CUSTOMER, RENTALS, RENTCOST
WHERE CUSTOMER.CID = RENTALS.CID
AND RENTALS.MAKE = RENTCOST.MAKE
AND RENTCOST < 40
CREATE VIEW Make_View (Make, NumRentals, Cregion, Storageat) AS
SELECT RENTALS.MAKE, Count (RTN), Cregion, AT
FROM CUSTOMER, RENTALS, CAR
WHERE CUSTOMER.CID = RENTALS.CID
AND RENTALS.VIN = CAR.VIN
GROUP BY RENTALS.MAKE, Cregion, At

-SELECT * FROM CUST_40D WHERE COST = 40 The number of rows displayed by this SELECT statement is:

A) 3 or more
B) 2
C) 1
D) 0

Question 38

(Access)When implementing a hierarchical report involving two tables in a 1-M relationship,a SQL query using a join on the 1-table and the M-table is formulated,but the linking column is not identified in the query.

Question 39

(Access)The following view is updatable:

A) CUST_SD
B) CUST_40D
C) Make_View
D) All of the above

Question 40

_____________ is a formatted display of a query using indentation to show grouping and sorting.

A) Hierarchical Report
B) Hierarchical Form
C) Updateable View
D) Virtual Table

Question 41

Figuer:
CUSTOMER (primary key = CID)
CNAME is NOT NULL $$\begin{array} { | l | l | l | l | l | } \hline \text { CID } & \text { CNAME } & \text { AGE } & \text { RESID\_CITY } & \text { Cregion } \\\hline 10 & \text { BLACK } & 40 & \text { SD } & \text { SD } \\\hline 20 & \text { GREEN } & 25 & \text { SD } & \text { SD } \\\hline 30 & \text { JONES } & 30 & \text { La Mesa } & \text { SD } \\\hline 40 & \text { MARTIN } & 35 & \text { LA } & \text { LA } \\\hline 50 & \text { SIMON } & 22 & \text { Pomona } & \text { LA } \\\hline 60 & \text { VERNON } & 60 & \text { Poway } & \text { SD } \\\hline\end{array}$$ RENTALS (primary key = RTN)
CID is a foreign key referencing CUSTOMER
CID is NOT NULL
MAKE is NOT NULL
MAKE is a foreign key referencing RENTCOST $$\begin{array} { | l | l | l | l | l | l | } \hline \text { CID } & \text { MAKE } & \text { DATE\_OUT } & \text { PICKUP } & \text { RETURN } & \text { RTN } \\\hline 10 & \text { FORD } & 10 - \text { Oct-00 } & \text { SD } & \text { SD } & 1 \\\hline 10 & \text { GM } & 01 - \text {-Jan-01 } & \text { SD } & \text { LA } & 2 \\\hline 10 & \text { FORD } & 01 - \text { Feb-01 } & \text { LA } & \text { SD } & 3 \\\hline 20 & \text { NISSAN } & 07 - J u l - 00 & \text { SD } & & 4 \\\hline 30 & \text { FORD } & 01 - J u l - 00 & \text { SD } & \text { SD } & 5 \\\hline 30 & \text { GM } & 01 - \text {-Dec-00 } & \text { SD } & \text { SD } & 6 \\\hline 40 & \text { FORD } & 01 - \text { Aug-00 } & \text { LA } & \text { LA } & 7 \\\hline 50 & \text { GM } & 01 - \text { Sep-00 } & \text { LA } & \text { SD } & 8 \\\hline 60 & \text { NISSAN } & 02 \text {-Jan-01 } & \text { SD } & \text { LA } & 9 \\\hline\end{array}$$ RENTCOST
MAKE is the primary key $$\begin{array} { | l | l | } \hline \text { MAKE } & \text { COST } \\\hline \text { FORD } & 30 \\\hline \text { GM } & 40 \\\hline \text { NISSAN } & 30 \\\hline \text { TOYOTA } & 20 \\\hline \text { VOLVO } & 50 \\\hline\end{array}$$ (Access) View1:
SELECT RTN, MAKE, PICKUP, RENTALS.CID,
CUSTOMER.CID, CNAME, AGE, Cregion
FROM CUSTOMER INNER JOIN RENTALS
ON CUSTOMER.CID = RENTALS.CID
(Access) View2:
SELECT RTN, MAKE, PICKUP, RENTALS.CID,
CNAME, AGE, Cregion
FROM CUSTOMER INNER JOIN RENTALS
ON CUSTOMER.CID = RENTALS.CID
(Access) View3:
SELECT RTN, RENTALS.MAKE, PICKUP, RENTALS.CID,
CNAME, AGE, Cregion, COST
FROM (CUSTOMER INNER JOIN RENTALS
ON CUSTOMER.CID = RENTALS.CID.
INNER JOIN RENTCOST
ON RENTALS.MAKE = RENTCOST.MAKE

-(Access)INSERT INTO View2 (RTN,MAKE,PICKUP,RENTALS.CID.
VALUES (10,'GM','SD',10)
Which of the following is not true?

A) This command inserts 1 row into RENTALS
B) This command inserts 1 row into CUSTOMER
C) After execution of this command, SELECT MAKE FROM View2 will show 5 rows where MAKE has 'GM' for value
D) The CUSTOMER table is not updated

Question 42

Figuer:
CUSTOMER (primary key = CID)
CNAME is NOT NULL $$\begin{array} { | l | l | l | l | l | } \hline \text { CID } & \text { CNAME } & \text { AGE } & \text { RESID\_CITY } & \text { Cregion } \\\hline 10 & \text { BLACK } & 40 & \text { SD } & \text { SD } \\\hline 20 & \text { GREEN } & 25 & \text { SD } & \text { SD } \\\hline 30 & \text { JONES } & 30 & \text { La Mesa } & \text { SD } \\\hline 40 & \text { MARTIN } & 35 & \text { LA } & \text { LA } \\\hline 50 & \text { SIMON } & 22 & \text { Pomona } & \text { LA } \\\hline 60 & \text { VERNON } & 60 & \text { Poway } & \text { SD } \\\hline\end{array}$$ RENTALS (primary key = RTN)
CID is a foreign key referencing CUSTOMER
CID is NOT NULL
MAKE is NOT NULL
MAKE is a foreign key referencing RENTCOST $$\begin{array} { | l | l | l | l | l | l | } \hline \text { CID } & \text { MAKE } & \text { DATE\_OUT } & \text { PICKUP } & \text { RETURN } & \text { RTN } \\\hline 10 & \text { FORD } & 10 - \text { Oct-00 } & \text { SD } & \text { SD } & 1 \\\hline 10 & \text { GM } & 01 - \text {-Jan-01 } & \text { SD } & \text { LA } & 2 \\\hline 10 & \text { FORD } & 01 - \text { Feb-01 } & \text { LA } & \text { SD } & 3 \\\hline 20 & \text { NISSAN } & 07 - J u l - 00 & \text { SD } & & 4 \\\hline 30 & \text { FORD } & 01 - J u l - 00 & \text { SD } & \text { SD } & 5 \\\hline 30 & \text { GM } & 01 - \text {-Dec-00 } & \text { SD } & \text { SD } & 6 \\\hline 40 & \text { FORD } & 01 - \text { Aug-00 } & \text { LA } & \text { LA } & 7 \\\hline 50 & \text { GM } & 01 - \text { Sep-00 } & \text { LA } & \text { SD } & 8 \\\hline 60 & \text { NISSAN } & 02 \text {-Jan-01 } & \text { SD } & \text { LA } & 9 \\\hline\end{array}$$ RENTCOST
MAKE is the primary key $$\begin{array} { | l | l | } \hline \text { MAKE } & \text { COST } \\\hline \text { FORD } & 30 \\\hline \text { GM } & 40 \\\hline \text { NISSAN } & 30 \\\hline \text { TOYOTA } & 20 \\\hline \text { VOLVO } & 50 \\\hline\end{array}$$ (Access) View1:
SELECT RTN, MAKE, PICKUP, RENTALS.CID,
CUSTOMER.CID, CNAME, AGE, Cregion
FROM CUSTOMER INNER JOIN RENTALS
ON CUSTOMER.CID = RENTALS.CID
(Access) View2:
SELECT RTN, MAKE, PICKUP, RENTALS.CID,
CNAME, AGE, Cregion
FROM CUSTOMER INNER JOIN RENTALS
ON CUSTOMER.CID = RENTALS.CID
(Access) View3:
SELECT RTN, RENTALS.MAKE, PICKUP, RENTALS.CID,
CNAME, AGE, Cregion, COST
FROM (CUSTOMER INNER JOIN RENTALS
ON CUSTOMER.CID = RENTALS.CID.
INNER JOIN RENTCOST
ON RENTALS.MAKE = RENTCOST.MAKE

-(Access)INSERT INTO View2 (RTN,MAKE,PICKUP,RENTALS.CID,CNAME)
VALUES (11,'GM','SD',70,'JORDAN' )
Which of the following is true?

A) This command inserts 1 row into RENTALS
B) This command inserts 1 row into CUSTOMER
C) After execution of this command, SELECT MAKE FROM View2 WHERE CNAME='JORDAN' will show 1 row
D) This command cannot execute

Question 43

Which of the following operations is typical in queries for hierarchical reports?

A) Join
B) Outer Join
C) Count
D) All of the above

Question 44

You want to create a form to process the rentals of a given customer.You would like to include the Cost of the Make.Select the most appropriate statement:

A) Cost should be in the main form
B) Cost should be in the subform
C) Either A or B
D) Cost should appear at the jointure of the two forms

Question 45

A view that can be used in SELECT,UPDATE,INSERT,and DELETE statements is a(n)_________________.

Question 46

In __________________,a query using a view is translated into a query using base tables by replacing references to the view with its definition.

Question 47

To process queries that reference a view,____________________ is not usually the best strategy because it requires the DBMS to execute two queries.

Question 48

Which of the following statements is not true about the fields in a form?

A) One field may contain the data from one table column
B) One field may combine the data from several columns in the same table
C) One field may contain computed data
D) One field may combine data from columns in the "1" table and in the "M" table

Question 49

Figuer:
CUSTOMER (primary key = CID)
CNAME is NOT NULL $$\begin{array} { | l | l | l | l | l | } \hline \text { CID } & \text { CNAME } & \text { AGE } & \text { RESID\_CITY } & \text { Cregion } \\\hline 10 & \text { BLACK } & 40 & \text { SD } & \text { SD } \\\hline 20 & \text { GREEN } & 25 & \text { SD } & \text { SD } \\\hline 30 & \text { JONES } & 30 & \text { La Mesa } & \text { SD } \\\hline 40 & \text { MARTIN } & 35 & \text { LA } & \text { LA } \\\hline 50 & \text { SIMON } & 22 & \text { Pomona } & \text { LA } \\\hline 60 & \text { VERNON } & 60 & \text { Poway } & \text { SD } \\\hline\end{array}$$ RENTALS (primary key = RTN)
CID is a foreign key referencing CUSTOMER
CID is NOT NULL
MAKE is NOT NULL
MAKE is a foreign key referencing RENTCOST $$\begin{array} { | l | l | l | l | l | l | } \hline \text { CID } & \text { MAKE } & \text { DATE\_OUT } & \text { PICKUP } & \text { RETURN } & \text { RTN } \\\hline 10 & \text { FORD } & 10 - \text { Oct-00 } & \text { SD } & \text { SD } & 1 \\\hline 10 & \text { GM } & 01 - \text {-Jan-01 } & \text { SD } & \text { LA } & 2 \\\hline 10 & \text { FORD } & 01 - \text { Feb-01 } & \text { LA } & \text { SD } & 3 \\\hline 20 & \text { NISSAN } & 07 - J u l - 00 & \text { SD } & & 4 \\\hline 30 & \text { FORD } & 01 - J u l - 00 & \text { SD } & \text { SD } & 5 \\\hline 30 & \text { GM } & 01 - \text {-Dec-00 } & \text { SD } & \text { SD } & 6 \\\hline 40 & \text { FORD } & 01 - \text { Aug-00 } & \text { LA } & \text { LA } & 7 \\\hline 50 & \text { GM } & 01 - \text { Sep-00 } & \text { LA } & \text { SD } & 8 \\\hline 60 & \text { NISSAN } & 02 \text {-Jan-01 } & \text { SD } & \text { LA } & 9 \\\hline\end{array}$$ RENTCOST
MAKE is the primary key $$\begin{array} { | l | l | } \hline \text { MAKE } & \text { COST } \\\hline \text { FORD } & 30 \\\hline \text { GM } & 40 \\\hline \text { NISSAN } & 30 \\\hline \text { TOYOTA } & 20 \\\hline \text { VOLVO } & 50 \\\hline\end{array}$$ (Access) View1:
SELECT RTN, MAKE, PICKUP, RENTALS.CID,
CUSTOMER.CID, CNAME, AGE, Cregion
FROM CUSTOMER INNER JOIN RENTALS
ON CUSTOMER.CID = RENTALS.CID
(Access) View2:
SELECT RTN, MAKE, PICKUP, RENTALS.CID,
CNAME, AGE, Cregion
FROM CUSTOMER INNER JOIN RENTALS
ON CUSTOMER.CID = RENTALS.CID
(Access) View3:
SELECT RTN, RENTALS.MAKE, PICKUP, RENTALS.CID,
CNAME, AGE, Cregion, COST
FROM (CUSTOMER INNER JOIN RENTALS
ON CUSTOMER.CID = RENTALS.CID.
INNER JOIN RENTCOST
ON RENTALS.MAKE = RENTCOST.MAKE

-(Access)INSERT INTO View1 (RTN,MAKE,PICKUP,RENTALS.CID.
VALUES (10,'GM','SD',10)
Which of the following is not true?

A) This command inserts 1 row into RENTALS
B) This command inserts 1 row into CUSTOMER
C) After execution of this command, SELECT MAKE FROM View1 will show 5 rows where MAKE has 'GM' for value
D) The CUSTOMER table is not updated

Question 50

A method to process a query on a view by executing the query directly on the stored view is known as _____________________.

Question 51

A view that can be used only in SELECT statements is a(n)______________________.

Question 52

Typically in a hierarchical form,which of the following statements is not true?

A) At a given time, the main form may show several records
B) At a given time, the main form may show one record
C) At a given time, the subform may show several records
D) At a given time, the subform may show one record

Question 53

(Access)When implementing a hierarchical form,which of the following statements is not true?

A) A SQL query using an inner join on the 1-Table and the M-table is formulated
B) A SQL query SELECTing all fields used in the main form is formulated
C) A SQL query SELECTing all fields used in the subform is formulated
D) The linking column is not identified using a SQL query

Question 54

Figuer:
CUSTOMER (primary key = CID)
CNAME is NOT NULL $$\begin{array} { | l | l | l | l | l | } \hline \text { CID } & \text { CNAME } & \text { AGE } & \text { RESID\_CITY } & \text { Cregion } \\\hline 10 & \text { BLACK } & 40 & \text { SD } & \text { SD } \\\hline 20 & \text { GREEN } & 25 & \text { SD } & \text { SD } \\\hline 30 & \text { JONES } & 30 & \text { La Mesa } & \text { SD } \\\hline 40 & \text { MARTIN } & 35 & \text { LA } & \text { LA } \\\hline 50 & \text { SIMON } & 22 & \text { Pomona } & \text { LA } \\\hline 60 & \text { VERNON } & 60 & \text { Poway } & \text { SD } \\\hline\end{array}$$ RENTALS (primary key = RTN)
CID is a foreign key referencing CUSTOMER
CID is NOT NULL
MAKE is NOT NULL
MAKE is a foreign key referencing RENTCOST $$\begin{array} { | l | l | l | l | l | l | } \hline \text { CID } & \text { MAKE } & \text { DATE\_OUT } & \text { PICKUP } & \text { RETURN } & \text { RTN } \\\hline 10 & \text { FORD } & 10 - \text { Oct-00 } & \text { SD } & \text { SD } & 1 \\\hline 10 & \text { GM } & 01 - \text {-Jan-01 } & \text { SD } & \text { LA } & 2 \\\hline 10 & \text { FORD } & 01 - \text { Feb-01 } & \text { LA } & \text { SD } & 3 \\\hline 20 & \text { NISSAN } & 07 - J u l - 00 & \text { SD } & & 4 \\\hline 30 & \text { FORD } & 01 - J u l - 00 & \text { SD } & \text { SD } & 5 \\\hline 30 & \text { GM } & 01 - \text {-Dec-00 } & \text { SD } & \text { SD } & 6 \\\hline 40 & \text { FORD } & 01 - \text { Aug-00 } & \text { LA } & \text { LA } & 7 \\\hline 50 & \text { GM } & 01 - \text { Sep-00 } & \text { LA } & \text { SD } & 8 \\\hline 60 & \text { NISSAN } & 02 \text {-Jan-01 } & \text { SD } & \text { LA } & 9 \\\hline\end{array}$$ RENTCOST
MAKE is the primary key $$\begin{array} { | l | l | } \hline \text { MAKE } & \text { COST } \\\hline \text { FORD } & 30 \\\hline \text { GM } & 40 \\\hline \text { NISSAN } & 30 \\\hline \text { TOYOTA } & 20 \\\hline \text { VOLVO } & 50 \\\hline\end{array}$$ (Access) View1:
SELECT RTN, MAKE, PICKUP, RENTALS.CID,
CUSTOMER.CID, CNAME, AGE, Cregion
FROM CUSTOMER INNER JOIN RENTALS
ON CUSTOMER.CID = RENTALS.CID
(Access) View2:
SELECT RTN, MAKE, PICKUP, RENTALS.CID,
CNAME, AGE, Cregion
FROM CUSTOMER INNER JOIN RENTALS
ON CUSTOMER.CID = RENTALS.CID
(Access) View3:
SELECT RTN, RENTALS.MAKE, PICKUP, RENTALS.CID,
CNAME, AGE, Cregion, COST
FROM (CUSTOMER INNER JOIN RENTALS
ON CUSTOMER.CID = RENTALS.CID.
INNER JOIN RENTCOST
ON RENTALS.MAKE = RENTCOST.MAKE

-(Access)Which of the following statements is true?

A) Data in RENTALS may be updated, using an UPDATE command on View3
B) Data in CUSTOMER may be updated, using an UPDATE command on View3
C) Data in RENTCOST may be updated, using an UPDATE command on View3
D) All of the above

Question 55

A virtual table whose rows do not exist until they are derived from the base table(s)is called a(n)___________.

Question 56

Figuer:
CUSTOMER (primary key = CID)
CNAME is NOT NULL $$\begin{array} { | l | l | l | l | l | } \hline \text { CID } & \text { CNAME } & \text { AGE } & \text { RESID\_CITY } & \text { Cregion } \\\hline 10 & \text { BLACK } & 40 & \text { SD } & \text { SD } \\\hline 20 & \text { GREEN } & 25 & \text { SD } & \text { SD } \\\hline 30 & \text { JONES } & 30 & \text { La Mesa } & \text { SD } \\\hline 40 & \text { MARTIN } & 35 & \text { LA } & \text { LA } \\\hline 50 & \text { SIMON } & 22 & \text { Pomona } & \text { LA } \\\hline 60 & \text { VERNON } & 60 & \text { Poway } & \text { SD } \\\hline\end{array}$$ RENTALS (primary key = RTN)
CID is a foreign key referencing CUSTOMER
CID is NOT NULL
MAKE is NOT NULL
MAKE is a foreign key referencing RENTCOST $$\begin{array} { | l | l | l | l | l | l | } \hline \text { CID } & \text { MAKE } & \text { DATE\_OUT } & \text { PICKUP } & \text { RETURN } & \text { RTN } \\\hline 10 & \text { FORD } & 10 - \text { Oct-00 } & \text { SD } & \text { SD } & 1 \\\hline 10 & \text { GM } & 01 - \text {-Jan-01 } & \text { SD } & \text { LA } & 2 \\\hline 10 & \text { FORD } & 01 - \text { Feb-01 } & \text { LA } & \text { SD } & 3 \\\hline 20 & \text { NISSAN } & 07 - J u l - 00 & \text { SD } & & 4 \\\hline 30 & \text { FORD } & 01 - J u l - 00 & \text { SD } & \text { SD } & 5 \\\hline 30 & \text { GM } & 01 - \text {-Dec-00 } & \text { SD } & \text { SD } & 6 \\\hline 40 & \text { FORD } & 01 - \text { Aug-00 } & \text { LA } & \text { LA } & 7 \\\hline 50 & \text { GM } & 01 - \text { Sep-00 } & \text { LA } & \text { SD } & 8 \\\hline 60 & \text { NISSAN } & 02 \text {-Jan-01 } & \text { SD } & \text { LA } & 9 \\\hline\end{array}$$ RENTCOST
MAKE is the primary key $$\begin{array} { | l | l | } \hline \text { MAKE } & \text { COST } \\\hline \text { FORD } & 30 \\\hline \text { GM } & 40 \\\hline \text { NISSAN } & 30 \\\hline \text { TOYOTA } & 20 \\\hline \text { VOLVO } & 50 \\\hline\end{array}$$ (Access) View1:
SELECT RTN, MAKE, PICKUP, RENTALS.CID,
CUSTOMER.CID, CNAME, AGE, Cregion
FROM CUSTOMER INNER JOIN RENTALS
ON CUSTOMER.CID = RENTALS.CID
(Access) View2:
SELECT RTN, MAKE, PICKUP, RENTALS.CID,
CNAME, AGE, Cregion
FROM CUSTOMER INNER JOIN RENTALS
ON CUSTOMER.CID = RENTALS.CID
(Access) View3:
SELECT RTN, RENTALS.MAKE, PICKUP, RENTALS.CID,
CNAME, AGE, Cregion, COST
FROM (CUSTOMER INNER JOIN RENTALS
ON CUSTOMER.CID = RENTALS.CID.
INNER JOIN RENTCOST
ON RENTALS.MAKE = RENTCOST.MAKE

-(Access)UPDATE View1 SET MAKE = 'NISSAN',Cregion = 'LA'
WHERE CNAME = 'BLACK'
Which of the following is not true?

A) This command updates 3 rows in RENTALS
B) This command updates 1 row in CUSTOMER
C) After execution of this command, SELECT MAKE FROM View1 will show 5 rows where MAKE has 'NISSAN' for value
D) The CUSTOMER table is not updated

Question 57

To use a view in a query instead of a base table,you would use the view name in the _____________ and the view columns in other parts of the query statement.

Question 58

To be updatable,a single-table view cannot contain the keywords ____________ or ____________ in the view definition.

Question 59

To define a view,SQL provides the __________________ statement,in which you identify the view name and the underlying SELECT statement.

Question 60

One of the advantages of views is __________________,meaning if an application accesses the database through a view,most changes to the conceptual schema will not affect the application.

Question 61

A formatted display of a query which uses indentation to provide a visually appealing format to the user is a(n)______________.

Question 62

The ________________ clause of the CREATE VIEW statement prevents UPDATE and INSERT statements that violate a view's WHERE clause from executing.

Question 63

A formatted window for data entry and display which is comprised of a fixed part and a variable part is called a(n)___________________.

Question 64

One of the rules for 1-M updatable queries states that the ________________ of the parent tables must be either a primary key or some other unique specification.

Question 65

The innermost line in a report is called the ______________.

Question 66

Some modifications to updatable views can be problematic because they can cause an unexpected ______________ to occur when rows of the view disappear.

Question 67

Depending on its definition,a view can be read-only or updateable.All views are at least __________.

Question 68

In Access,a(n)_____________________ query involves two tables,with one table playing the role of parent and another table playing the role of child.

Question 69

The variable part of a hierarchical form is known as the __________________.

Question 70

Views that can be used only with SELECT statements are known as __________ views.

Question 71

The major query formulation issue for hierarchical reports is the _______________ of output,i.e.whether the output should contain individual rows or groups of rows.

Question 72

A stylized presentation of data for a selected audience is known as a(n)_____________.

Question 73

A hierarchical report is also known as a(n)_________________.

Question 74

The fixed part of a hierarchical form is known as the __________________.

Question 75

A(n)__________________ is a document designed to support a business task,such as processing an order.