Database Study Cards

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:

0

Given the following query: SELECT CID FROM Customer WHERE CID IN(SELECT DISTINCT CID FROM Rentals WHERE Make = 'FORD') The execution of this query produces the following number of rows:

3

Using the tables listed above, a full outer join of the EventPlan and EventRequest tables contains how many rows?

4

Given the following ERD: How would you best classify the relationship pattern depicted above?

A generalization hierarchy

For the generalization hierarchy rule, which of the following is true?

All of the above

Which of the following are not ERD refinements in data modeling?

All of the above are ERD refinements

When trying to determine a primary key for an entity, what characteristics of an attribute make an attribute a potential primary key?

All of the above.

A table that satisfies 2NF:

Always satisfies 1NF

The Hollywood studio BestFlick wants to have a database designed to have access to the following information about all the movies it ever produced: title, yearreleased, director, actors, stuntmen, "Oscars" awarded. A movie has only one director. In this description, the gender is irrelevant. "Actor", "director", "stuntman" can be male or female. "Oscar for best acting" refers to an Oscar for best actor or best actress and also includes best supporting actor or actress. Oscar for best director should be:

An attribute of movie

Regarding connection rules for ERD's, which statement is not true:

B and C are not true

What does the "C" indicate in the ERD below?

Faculty members and administrators are the only types of employees in the system

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

List the event number, date requested, date authorized, plan number, work date, and name of the supervising employee of events meeting the following conditions: (1) the date requested and date authorized are in July 2007, (2) the event request has "Approved" status, and (3) there is at least one event plan. Include a row in the result even if there is no supervising employee for the event plan.

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:

List the unique CID of all customers who have rented a Ford.

In comparing ERD's to relational database diagrams, which of the following statements is true:

None of the above

Which of the following statements does not apply to relational databases?

Relational databases have a strong procedural orientation

What is the meaning of the following query? SELECT StdNo, StdFirstName, StdLastName, StdMajor FROM Student WHERE NOT EXISTS (SELECT * FROM Enrollment WHERE Enrollment.StdNo = Student.StdNo )

Show all of the students who do not have records in the Enrollment table

When converting the following ERD to a relational table, what would be the primary key of the Room table?

The combination of Room_Number and Building_Number

When applying the M-M relationship rule for converting an ERD into a relational table design, which of the following statements is true?

a and c.

Which of the following should be included in design documentation?

all of the above

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

includes a Type I nested query to implement a difference operation.

The combination of columns, EventPlan.PlanNo and EventPlan.EventNo, is a

superkey

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#) An alternative way to write the SELECT statement is to

use a Type II nested query with the NOT EXISTS keywords.