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!
