CSIS 325 Final Exam (2019)

IMPORTANT: AFTER PURCHASE, LOG IN TO YOUR ACCOUNT AND SCROLL DOWN BELOW THIS PAGE TO DOWNLOAD FILES WITH ANSWERS.

Question 1   Use the following tables to answer the question below

Given the following Access SQL   SELECT statement:

SELECT eventplan.eventno,   eventrequest.datereq, dateauth, planno,workdate, empname

FROM (EventRequest INNER JOIN   EventPlan

ON EventRequest.eventno =   EventPlan.eventno)

LEFT JOIN employee ON   EventPlan.empno = employee.empno

WHERE eventrequest.datereq   BETWEEN #7/1/2007# AND #7/31/2007#

AND dateauth BETWEEN #7/1/2007#   AND #7/31/2007# AND status = “Approved”;

The   meaning of the SELECT statement is

Question 2 The following table represents what type of operation that has been performed   on STUDENT_1 and STUDENT_2 tables?

Question 3 A table that satisfies 2NF:

Question 4 Use the following table definitions to answer the question   below.

customer(custno, name, address, city, state, zip, phone, income)

asset(assetno, assetdesc, assettype)

service(servno, servdesc, servrate)

employee(empno, empname, emptitle)

pricehistory(assetno, date, price)

FOREIGN KEY assetno REFERENCES   asset

charge(chargeno, custno, servno, starttime, endtime, empno, billed, amtpaid)

FOREIGN KEY custno REFERENCES   customer

FOREIGN KEY servno REFERENCES   service

FOREIGN KEY empno REFERENCES   employee

holding(custno, assetno, qty)

FOREIGN KEY custno REFERENCES   customer

FOREIGN KEY assetno REFERENCES   asset

trade(tradeno, custno, assetno, qty, price, date, type, status)

FOREIGN KEY custno REFERENCES   customer

FOREIGN KEY assetno REFERENCES   asset

What table contains a combined   primary key that represents a M-N relationship?

Question 5 Use the following tables to answer the question below

Given   the following Access SQL SELECT statement:

SELECT eventrequest.eventno,   custname, contact, dateauth

FROM eventrequest, customer

WHERE eventrequest.custno =   customer.custno AND status = “Approved”

AND eventno NOT IN

( SELECT eventno FROM eventplan

WHERE workdate BETWEEN   #10/1/2007# AND #10/31/2007#)

The SELECT statement

 

Question 6 Use the following tables to answer the question below

To load   the tables from text files, which load order will NOT cause a violation of   referential integrity

 

Question 7 Use the following tables to answer the question below

Given the following Access SQL   SELECT statement:

SELECT eventplan.eventno,   eventrequest.datereq, dateauth, planno, workdate, empname

FROM (EventRequest INNER JOIN   EventPlan

ON EventRequest.eventno =   EventPlan.eventno)

LEFT JOIN employee ON   EventPlan.empno = employee.empno

WHERE eventrequest.datereq   BETWEEN #7/1/2007# AND #7/31/2007#

AND dateauth BETWEEN #7/1/2007#   AND #7/31/2007# AND status = “Approved”;

The   FROM clause of the SELECT statement

Question 8 Which table below is union compatible with the Student_1 and Student_2 tables?

Question 9

CUSTOMER (primary key = CID)

RENTALS (primary key = RTN)

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

In RENTCOST, COST shows the   base cost of renting a given MAKE for one day.

CAR

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 maximum value in the column   Storageat of the view Make_View is:

 

Question 10 The following SQL query is an   example of a _____________________ join.

SELECT   FacNo, FacFirstName, FacLastName, FacSalary, StdNo

FROM Faculty RIGHT JOIN Student

ON Student.StdNo =   Faculty.FacNo

Question 11 The following SQL query is an   example of a _____________________ join.

SELECT FacNo, FacFirstName,   FacLastName, FacSalary, StdNo

FROM Faculty LEFT JOIN Student

ON Student.StdNo = Faculty.FacNo

 

Question 12 A table that satisfies BCNF:

 

Question 13 Use the following tables to answer the question below

List   the event number, customer name, plan number, employee name, facility name,   work date, and activity for event plans.  The result should show only   event plans with a work date in October 2007.
How   many tables appear in the FROM clause assuming that no nested queries are   used?

 

Question 14

CUSTOMER (primary key = CID)

RENTALS   (primary key = RTN)

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

In RENTCOST, COST shows the   base cost of renting a given MAKE for one day.

CAR

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

There   is a request to execute the two following SQL commands in the order shown:

CREATE VIEW CUST1 AS

SELECT CNAME, AGE FROM CUSTOMER

UPDATE CUST_SD

SET AGE = AGE +1

Select   the most appropriate statement:

 

Question 15 After execution of this SQL   query

SELECT CID, CName

FROM  Customer

WHERE  (SELECT COUNT(*)   FROM Rentals WHERE Customer.CID = Rentals.CID) = 0

The   execution of this query produces the following number of rows:

Question 16 The entity integrity rule

Question 17 The following SQL query is an   example of a _____________________ join.

SELECT   FacNo, FacFirstName, FacLastName, FacSalary, StdNo

FROM Faculty, Student

WHERE Student.StdNo =   Faculty.FacNo

Question 18 A table in 1NF in which the unique   candidate key consists of two of its three attributes:

Question 19 Use the following tables to answer the question below

Null   values in the column EventRequest.DateAuth represent

 

Question 20 Use the following tables to answer the question below The   combination of columns, EventPlan.PlanNo and EventPlan.EventNo, is a

Question 21 Given the following query:

SELECT CID FROM Customer

WHERE CID IN(SELECT DISTINCT   CID FROM Rentals WHERE Make = ‘FORD’)

The meaning of this query is   the following:

 

Question 22 Use the following tables to answer the question below

Using the tables listed above,   a natural join of the EventPlanLine and Location tables contains how many   rows?

 

Question 23 What query produces the same   results as the one below?

SELECT Student.StdNo,   Student.StdFirstName, Student.StdLastName, Student.StdMajor

FROM Student

WHERE EXISTS (SELECT * FROM   Enrollment

WHERE Enrollment.StdNo =    Student.StdNo )

 

Question 24 What field is redundant in the   Rentals table?

Question 25 For a violation of 2NF to occur in   a table in 1NF, the following condition(s) must exist:

CUSTOMER (primary key = CID)

RENTALS (primary key = RTN)

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

In RENTCOST, COST shows the   base cost of renting a given MAKE for one day.

CAR

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_SD

WHERE AGE < 25

The   number of rows displayed by this SELECT statement is:

Question 27 Under the MapReduce model,   the data processing primitives are called

Question 28

Use the following table definitions to answer the question   below.

customer(custno, name, address, city, state, zip, phone, income)

asset(assetno, assetdesc, assettype)

service(servno, servdesc, servrate)

employee(empno, empname, emptitle)

pricehistory(assetno, date, price)

FOREIGN KEY assetno REFERENCES   asset

charge(chargeno, custno, servno, starttime, endtime, empno, billed, amtpaid)

FOREIGN KEY custno REFERENCES   customer

FOREIGN KEY servno REFERENCES   service

FOREIGN KEY empno REFERENCES   employee
holding(custno, assetno, qty)

FOREIGN KEY custno REFERENCES   customer

FOREIGN KEY assetno REFERENCES   asset

trade(tradeno, custno, assetno, qty, price, date, type, status)

FOREIGN KEY custno REFERENCES   customer

FOREIGN KEY assetno REFERENCES   asset

Null values in the column   Charge.EmpNo represent

Question 29

After execution of this SQL   query

SELECT Customer.CName, SUM(Cost   * Days) AS TotalCost

FROM Customer, Rentals,   Rentcost

WHERE Customer.CID =   Rentals.CID

AND Rentals.Make =   RentCost.Make

AND Rentals.Make = ‘FORD’

GROUP BY CNAME

The   execution of this query lists the following TotalCost:

Question 30

CUSTOMER (primary key = CID)

RENTALS (primary key = RTN)

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

In RENTCOST, COST shows the   base cost of renting a given MAKE for one day.

CAR

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   view Make-View provides the following information:

Question 31 Which of the following will   usually not result in a faster execution of an SQL query?

Question 32 Since the objective of   physical database design is to minimize the combined demand of all   applications using the database, it is usually necessary to balance the   sometimes conflicting needs of both retrieval and update applications.

Question 33 Which of the following is not   a good candidate for a clustering index?

Question 34 The storage level is close   to:

Question 35 When a logical record   requested by an application is already in memory, which of the following is   not true?

Question 36 After the crash of a disk   containing the database, which of the following tools will need to be used   for recovery?

Question 37 An operating system failure   affects all active transactions, while a device failure affects all active   transactions plus all committed transactions that have been recorded on the   disk that failed.

Question 38 It is possible to detect   deadlocks in a database system by doing the following:

Question 39 Which one of the following   isolation levels does not prevent the uncommitted dependency problem?

Question 40 The redo operation:

 

Do you need a similar assignment done for you from scratch? Order now!
Use Discount Code "Newclient" for a 15% Discount!