PART I

1. The information stored in the ____ is used by the DBMS for a recovery requirement triggered by a ROLLBACK statement, a program's abnormal termination, or a system failure such as a network discrepancy or a disk crash
A. data dictionary
B. transaction log
C. metadata
D. rollback manager

2. Which of the following is an example of lost update?
A. The system assigns the last aisle seat on a flight to John whereas Peter receives a window seat, although both indicated preference for an aisle seat
B. John and Peter receive each a window seat as they both requested
C. John is initially assigned the last window seat, but this seat is later on assigned to Peter
D. None of the above

3. Julia has a lock on the savings account and Bill is trying to perform a conflicting action on the data:
A. Bill's transaction must wait until Julia's lock is released
B. Bill's transaction must wait until Julia has read the savings account value
C. Bill's transaction is granted permission to perform the desired action
D. None of the above

4. Julia has a shared lock on the savings account and Bill is trying to perform an action requesting a shared lock on the same data:
A. Bill's transaction must wait until Julia has read the savings account value
B. Bill's transaction must wait until Julia's lock is released
C. Bill's transaction is granted permission to perform the desired action
D. None of the above

5. Julia has an exclusive lock on the savings account and Bill is trying to perform an action requesting a shared lock on the same data:
A. Bill's transaction must wait until Julia's lock is released
B. Bill's transaction is granted permission to perform the desired action
C. Bill's transaction must wait until Julia has read the savings account value
D. None of the above

6. Which of the following will cause a deadlock?
A. Transaction T1 requests an exclusive lock on data A at the same time as transaction T2
B. Transaction T1 places an exclusive lock on data A, then transaction T2 requests a shared lock on data A
C. Transaction T1 and T2 modify data A without any lock
D. Transaction T1 places an exclusive lock on data A, then requests one on data B. Simultaneously, transaction T2 places an exclusive lock on data B, then requests one on data A

7. Consider a transaction that updates each of the 100 employees' salaries in a table (there is one row per employee) under the immediate update mode. The transaction terminates abnormally after 10 rows have been updated in the database (and the normal operations have taken place on the log). What should the recovery procedure perform to make sure the transaction completes as desired?

A. Redo
B. Redo followed by execution of the transaction again
C. Undo followed by execution of the transaction again
D. Undo

8. The _______________ query will output the table contents when the value of the character field P_CODE is alphabetically less than 1558-QW1.
A. SELECT P_CODE, P_DESCRIPT, P_QOH, P_MIN, P_PRICE
FROM PRODUCT
WHERE P_CODE <'1558-QW1';
B. SELECT P_CODE, P_DESCRIPT, P_QOH, P_MIN, P_PRICE
FROM PRODUCT
WHERE P_CODE = [1558-QW1]
C. SELECT P_CODE, P_DESCRIPT, P_QOH, P_MIN, P_PRICE
FROM PRODUCT
WHERE P_CODE = (1558-QW1)
D. SELECT P_CODE, P_DESCRIPT, P_QOH, P_MIN, P_PRICE
FROM PRODUCT
WHERE P_CODE = {1558-QW1}

9. The primary objective of database design is __________________.
A. To create the most efficient database possible
B. To create the fastest queries
C. To create complete, normalized, nonredundant, and fully integrated conceptual, logical, and physical database models
D. To be able to add data quickly

10. The command to join the P_DESCRIPT and P_PRICE fields from the PRODUCT table and the V_NAME, V_AREACODE, V_PHONE, and V_CONTACT fields from the VENDOR table where the value of V_CODE match is ________________.

A. SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE
FROM PRODUCT, VENDOR
WHERE PRODUCT.V_CODE <> VENDOR.V_CODE;
B. SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE
FROM PRODUCT, VENDOR
WHERE PRODUCT.V_CODE = VENDOR.V_CODE;
C. SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE
FROM PRODUCT, VENDOR
WHERE PRODUCT.V_CODE <= VENDOR.V_CODE;
D. SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE
FROM PRODUCT, VENDOR
WHERE PRODUCT.V_CODE => VENDOR.V_CODE;

11. User requirements, existing system evaluation, and logical system design are part of the __________ phase of the SDLC.
A. planning
B. analysis
C. detailed systems design
D. Implementation

12. The conceptual design steps determines end-user views, outputs, and transaction-processing requirements are _______________________.
A. Database analysis and requirements
B. Entity relationship modeling and normalization
C. Data model verification
D. Distributed database design

13. ___________________ is a disadvantage of a DDBMS.
A. Data is located near the "greatest demand" site
B. Growth facilitation
C. Danger of a single-point failure
D. Lack of standards

14. A consistent and well-designed database is _____________________.
A. One in which all tables have foreign keys
B. One in which all tables are de-normalized
C. One in which all data integrity constraints are satisfied
D. One in which all SQL statements only query one table at a time

15. The ____ contains business data extracted from the operational database and from external data sources.
A. Data warehouse
B. Data visualization tool
C. Data dictionary
D. ETL tool

16. The ________________ command is used to list a unique value for V_CODE, where the list will produce only a list of those values that are different from one another.
A. SELECT ONLY V_CODE
FROM PRODUCT;
B. SELECT UNIQUE V_CODE
FROM PRODUCT;
C. SELECT DIFFERENT V_CODE
FROM PRODUCT;
D. SELECT DISTINCT V_CODE
FROM PRODUCT;

17. All transactions must display _____________________.
A. atomicity, serializability, and durability
B. durability, atomicity, consistency, and isolation
C. serializability, durability, and isolation
D. durability and isolation

18. The ANSI has defined standards that govern SQL database transactions. Transaction support is provided by two SQL statements: ____________________ and ROLLBACK.
A. RETRIEVE
B. ASSIGN
C. UPDATE
D. COMMIT

19. Joe is working on the Course table and needs to know which courses have prerequisite. To find the information he would ____________.
A. create a new query
B. create a new table
C. create a new form
D. create a new constraint

Use the tables (in uploaded file) for a car rental database to answer questions 20-25:

20. SELECT DISTINCT CID, CNAME
FROM CUSTOMER
WHERE CID IN
(SELECT CID FROM RENTALS WHERE MAKE IN ('FORD', 'TOYOTA'))
The CNAMEs shown by the execution of this query are:
A. BLACK
B. BLACK, JONES
C. BLACK, JONES, MARTIN
D. BLACK, JONES, MARTIN, VERNON

21. SELECT DISTINCT CUSTOMER.CID, CNAME
FROM CUSTOMER, RENTALS, RENTCOST
WHERE CUSTOMER.CID = RENTALS.CID
AND RENTALS.MAKE = RENTCOST.MAKE AND NOT EXISTS
(SELECT * FROM RENTALS R, RENTCOST C
WHERE R.MAKE = C.MAKE
AND RENTALS.CID = R.CID
AND RENTCOST.COST <> C.COST)
The meaning of this query is:
A. List all customers with more than one car make rented
B. List all customers with one or more rentals for which the cost of each car make rented is the same
C. List all customers who have only rented one make
D. None of the above

22. SELECT MAKE
FROM RENTALS, CUSTOMER
WHERE RENTALS.CID = CUSTOMER.CID AND RESID_CITY = 'HEMET'
GROUP BY MAKE
HAVING COUNT (DISTINCT RENTALS.CID) =
(SELECT COUNT(*) FROM CUSTOMER
WHERE RESID_CITY = 'HEMET')
The execution of this query produces the following number of rows:
A. 0
B. 1
C. 2
D. 3

23. SELECT MAKE
FROM RENTALS, CUSTOMER
WHERE RENTALS.CID = CUSTOMER.CID AND RESID_CITY = 'HEMET'
GROUP BY MAKE
HAVING COUNT (DISTINCT RENTALS.CID) =
(SELECT COUNT(*) FROM CUSTOMER
WHERE RESID_CITY = 'HEMET')
The meaning of this query is the following:
A. List all makes of cars rented to customers residing in Hemet
B. List all makes of cars rented to at least one customer residing in Hemet
C. List all makes of cars rented to all customers residing in Hemet
D. None of the above

24. SELECT CID, CNAME FROM CUSTOMER
WHERE 0 =
(SELECT COUNT(*) FROM RENTALS
WHERE CUSTOMER.CID = RENTALS.CID)
What is the interpretation of this query?
A. List the customers who do not have rentals
B. List the customers who have one rental
C. List the customers who have 0 or more rentals
D. List the customers who have 1 or more rentals

25. SELECT CNAME, DATE_OUT, RTN
FROM CUSTOMER, RENTALS
WHERE CUSTOMER.CID = RENTALS.CID AND BIRTHPLACE IN ('ERIE', 'CARY') AND EXISTS
(SELECT * FROM RENTCOST
WHERE COST < 40 AND RENTALS.MAKE= RENTCOST.MAKE)
The CNAMEs shown by the execution of this query are:
A. SIMON
B. GREEN, BLACK, SIMON
C. GREEN
D. GREEN, SIMONDBST 651

PART II Question 1:

The diagram below (also attached in DBST651_final_part2_ERD.pdf ) is an ERD for an order entry database for a company. A customer orders products through company employee.

Study the ERD and answer the following questions:

I. Identify all relationships and specify cardinality and business rules. For example: 1:M between Customer and Orders: a customer can place many orders; an order will be placed by one and only one customer.

II. For each entity, identify primary key and foreign key if any. For foreign key, also specify parent entity and matching attribute in parent entity.

III. Write SQL DDL statements to create tables in Oracle and also implement primary key, foreign key, and NOT NULL constraint. Include all columns listed with the correct data type. This step is important as in Question 2 you will run INSERT statements to populate the tables you created and then write SELECT statement to query those tables.

IV. Write SQL statement for the following scenario:
a. Add a new customer John Smith with custono 1 and custzip 23456.
b. Save changes permanently.
c. Changer customer John Smith zip from 23456 to 20001.
d. Cancel the change made in step c, restore data to its original status prior to step c.
e. Delete customer John Smith.
f. Save changes permanently.

For SQL code, submit both source statements and results of running your statements.

Question 2:

Continue Question 1 above. It is important for you to create those tables with the exact table/column name and exact column data type/length as shown in the ERD.

Run the DBST651_final_Part2_q2.sql script attached and then respond the following SQL problems:

Create SQL statements for the following scenarios. Your response should include SQL statement, output and any other assumptions you have made to arrive at the solution. Please provide a single SQL query for each problem.

a. For Seattle, WA customers compute the average amount of their orders and the number of orders placed. The result should include the customer number, customer last name, average order amount and the number of orders placed. Show results in the order of average order amount from high to low.

b. For Seattle, WA customers compute the number of unique products ordered. If a product is purchased on multiple orders, it should be counted only one time. The result should include customer number, customer last name and the number of unique products ordered. Show results in the order of customer's last name.

c. For each employee with a commission less than 0.05, compute the number of orders taken and the average product total quantity per order. The results should include the employee number, employee last name, number of orders taken and the average product total quantity per order. Show results in the order of number of orders taken from low to high.

d. For each Connex product compute the number of unique customers who ordered the product in Jan 2007. The results should include the product number, product name and the number of unique customers. Show results in the order of product name.

Attachment:- Tables.rar