Exam 3: CIW v5 Database Design Specialist

The exhibit shows a table called Housing Relation that relates a unique student identification number with a dormitory building and a room fee for that building. Each building charges only one fee and a student can live in only one building. The key for the Housing Relation is Student_ID. This table is in which normal form?

Which of the following best describes the two-tier database architecture?

Consider the relation shown in the exhibit. Which of the following SQL statements would return a relation that excludes all customers with a Satisfaction_Rate of less than or equal to 80 unless the Sales_Office is located in Atlanta?

Which type of dependency occurs when one attribute of a composite key is removed and the dependency still exists?

Which of the following describes two desirable characteristics of a primary key?

Consider the following SQL statement and the Orders relation shown in the exhibit: How many records should be returned?

NULL) Primary Key Class_Num Consider the Information Engineering diagram shown in the exhibit. Which DBDL definition best describes this diagram?

Which term describes the management of simultaneous transactions to prevent conflicts?

In which situation would the DBMS use a serial schedule to execute the transactions?

Using the Customer and Sales_Rep relations shown in the exhibit, you must determine a relational algebraic expression that will result in the following relation: Which of the following relational algebraic expressions would result in this relation? Using the Customer and Sales_Rep relations shown in the exhibit, you must determine a relational algebraic expression that will result in the following relation: Which of the following relational algebraic expressions would result in this relation?

Which statement best describes a procedural data manipulation language command?

The exhibit shows a table called Recreation Relation that relates a unique student identification number and a sports activity with a fee for participating in that activity. The Student_ID and Activity columns in the table are used together as a composite key. Which statement about the relation is correct?

Which of the following occurs in a relation when records are added or removed?

Which process is used to prevent the current database operation from reading or writing a data item while that data item is being accessed by another operation?

Which of the following best describes a composite key?

Consider the Information Engineering diagram shown in the exhibit. Building_ID, R_ID, Room_Count and Room_Num are integer numbers, whereas Bldg_Name and Res_Name are represented by variable-length strings with a maximum of 20 characters. Location can be up to 50 characters long, and no building has more than 600 rooms. Which SQL statement best implements the BUILDING relation shown in this diagram?

What is a data dictionary?

The exhibit shows a table called Activity Relation that relates a unique student identification number with a sports activity and a fee for participating in that activity. A student can participate in only one activity. The key for the relation is Student_ID. What consequence would occur if the tuple for Student_ID 1001 were removed?

Consider the Project relation shown in the exhibit as well as the following SQL statement: Which of the following tables shows the Project relation after execution of this SQL statement?

Consider the entity-relation (ER) diagram shown in the exhibit. When the logical database design phase is completed, which of the following is a valid DBDL description of the base relations for the ER diagram?